Why databases and the relational model
Why we need databases
- Early programs stored data in flat files — one file per program.
- That's fine for small data, but it breaks down badly at scale.
- The relational database fixes it by storing data in linked tables.
The limits of flat files
- Data redundancy — the same data (a customer's address) is held in several files.
- Data inconsistency — those copies get updated separately and drift out of sync.
- Data dependence — programs are tied to the file format; change it and every program must be rewritten.
- It's also hard to enforce integrity, share safely, or query well.
Practice
Storing a customer's address in several separate files leads to:
The same data held in many places (redundancy) can be updated separately and become inconsistent.
The relational model
- Table (relation) — a grid of rows and columns; one table per entity (e.g.
CUSTOMER). - Record (row) = one instance; field (column) = one piece of information.
- Primary key — a field (or fields) that uniquely identifies each record (never null or duplicated).
- Foreign key — a field whose value matches the primary key of another table, linking them.
- Composite key = a primary key of two+ fields; candidate key = any field(s) that could be the primary key.
Practice
A primary key:
The primary key uniquely identifies each row. A foreign key is the one that links to another table.
Practice
A foreign key is a field whose value:
A foreign key references another table's primary key, creating the relationship between tables.
Practice
A composite key is:
When no single field is unique, two or more fields together form a composite primary key.
Referential integrity
- Referential integrity means every foreign-key value must match an existing primary key — no "orphan" records.
- Tables are written in shorthand (primary key underlined, foreign keys noted):
CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate) -- CustomerID is a FK → CUSTOMER
Practice
Referential integrity ensures that:
It prevents orphan records — you cannot reference a primary key that does not exist.
You've got it
Key idea
- flat files suffer redundancy, inconsistency and data dependence
- primary key uniquely identifies a record; foreign key links to another table's PK
- composite key = several fields together; candidate key = a possible PK
- referential integrity: every foreign key must match an existing primary key