Database Fundamentals

1Introduction

This lecture explains the fundamental concepts through which a database management system manages data persistently and consistently. In particular, it introduces the components of the relational model, keys and integrity constraints, the purpose of normalization, and the ACID properties of transactions.

An HTTP server processes requests and generates representations when needed, but data concerning users, products, and orders must remain available after individual communications end. Sharing such data safely among multiple programs and users requires mechanisms that manage not only storage formats but also constraints, concurrency, and recovery from failures.

2Databases and DBMSs

A databaseDatabase is a collection of data that is organized for a particular purpose and managed persistently.

A database management systemDatabase management system; DBMS is software that provides database definition, storage, querying, updating, access control, concurrency control, failure recovery, and related functions. A database is the data being managed, whereas a DBMS is the mechanism that manages it.

3Components of the Relational Model

The relational modelRelational model represents data as relations. Relations are often displayed as tables in implementations, but a relation is not merely a file arranged in rows and columns; it is a mathematical structure consisting of attributes and tuples.

• An attributeAttribute is a named component that characterizes the data.
• A domainDomain is the set of values that an attribute may take.
• A tupleTuple is one data item containing a value corresponding to each attribute.
• A relation schemaRelation schema specifies a relation name and its attribute structure.
• A relation instanceRelation instance is the set of tuples that conforms to a schema at a particular time.

For example, a relation schema representing students can be written as follows.

Here, student_id, name, and department_id are attributes, and each collection of values corresponding to one student is a tuple.

4Keys

A superkeySuperkey is a set of attributes constrained to identify every tuple uniquely in every instance permitted by a relation schema. A candidate keyCandidate key is a minimal superkey: no proper subset of it is itself a superkey.

A primary keyPrimary key is the candidate key selected as the principal identifier of tuples. A relation may have multiple candidate keys, but only one is selected as its primary key.

A foreign keyForeign key is a set of attributes in one relation whose values reference a candidate key in the same or another relation. For example, if Student.department_id references Department.department_id in a department relation, it associates each student with a department.

5Integrity Constraints

An integrity constraintIntegrity constraint is a condition that database states must satisfy. Representative constraints include the following.

• A domain constraint restricts an attribute value to its specified domain.
• A key constraint requires candidate-key values to identify tuples uniquely.
• Entity integrity requires the primary-key attributes to be present so that each tuple can be identified.
• Referential integrityReferential integrity requires every non-null foreign-key value to exist in the referenced relation.

A DBMS rejects updates that violate these constraints or updates related data according to declared referential actions. Constraints expressed in a schema cannot, however, fully guarantee that data accurately reflects the real world.

6Purpose of Normalization

Recording the same fact in several locations can cause inconsistency when only one occurrence is updated, inability to insert data, or loss of a separate fact during deletion. These problems are called update, insertion, and deletion anomaliesAnomaly, respectively.

NormalizationNormalization is the design process of decomposing relation schemas according to dependencies among attributes in order to reduce undesirable redundancy and update anomalies. Normalization does not mean eliminating every duplication; its purpose is to represent dependencies appropriately. DenormalizationDenormalization may deliberately introduce redundancy in response to performance requirements or access patterns.

7Transactions and ACID

A transactionTransaction is a sequence of operations that a DBMS treats as one logical unit of work. A funds transfer, for example, must treat the debit from one account and the credit to another as one unit.

Four representative transaction properties are collectively called ACID.

• AtomicityAtomicity: either all operations of a transaction take effect, or none do.
• ConsistencyConsistency: when a transaction executes correctly, it moves the database from one constraint-satisfying state to another.
• IsolationIsolation: interference among concurrent transactions is controlled to provide observations corresponding to a specified isolation level.
• DurabilityDurability: the effects of a committed transaction remain preserved despite subsequent failures.

ACID describes desired properties of transaction processing rather than a particular implementation. Consistency is not obtained automatically from the DBMS alone; it also requires appropriate constraints and correct transaction logic.

8Key Points

• A database is organized data, whereas a DBMS defines, operates on, and protects that data.
• A relation schema specifies an attribute structure, while a relation instance is the set of tuples present at a particular time.
• Candidate keys, primary keys, foreign keys, and integrity constraints express identification and relationships among data.
• Normalization reduces undesirable redundancy and update anomalies, while transactions manage several operations as a logical unit.

9Next Lecture