Why databases, and the relational model

Why files are not enough

Picture a company where sales, billing and shipping each keep their own files. The customer's address is stored three times, so when it changes in one file and not the others the company holds three versions of the truth: redundancy breeds inconsistency. A question nobody planned for (which customers in Elche ordered twice this month?) needs a new program. Rules such as “stock can never be negative” live scattered in application code. Two clerks updating the same record at once can overwrite each other, and a crash halfway through an update leaves the files half changed.

A database management system (DBMS) solves these problems in one place. It holds a single description of the data, the schema; it enforces integrity constraints on every write; it lets many users work concurrently without corrupting each other's changes; it recovers to a consistent state after a crash; and it controls who may read or change what.

Data independence

Codd's central aim was data independence. Physical independence means the storage can change (a new index, a different file layout, a faster disk) without rewriting the programs that use the data. Logical independence means the conceptual schema can grow, say a new column, without breaking views that existing applications depend on. The classic picture has three levels: external views for each user group, one conceptual schema, and the internal storage underneath.

Relations, tuples and keys

In the relational model a relation is a set of tuples over named attributes, and each attribute takes its values from a domain. In everyday terms: a table, its rows and its columns. The number of attributes is the relation's degree; the number of tuples is its cardinality. Because a relation is a set, it has no duplicate tuples and no built-in order of rows. Keys identify tuples:

• Superkey: any set of attributes whose values are unique for every tuple.
• Candidate key: a minimal superkey; remove any attribute and it stops being unique.
• Primary key: the candidate key chosen as the main identifier; it may not be NULL (entity integrity). The other candidates are alternate keys.
• Foreign key: attributes that must match a key value in another relation, or be NULL (referential integrity).

-- Each department appears once
CREATE TABLE department (
  dept_id  INTEGER PRIMARY KEY,
  name     TEXT NOT NULL UNIQUE
);

-- Each employee belongs to an existing department
CREATE TABLE employee (
  emp_id   INTEGER PRIMARY KEY,            -- primary key
  nif      CHAR(9) NOT NULL UNIQUE,        -- alternate (candidate) key
  name     TEXT NOT NULL,
  dept_id  INTEGER NOT NULL
           REFERENCES department(dept_id)  -- foreign key
);