Why databases and the relational model
| English | Chinese | Pinyin |
|---|---|---|
| flat files | 平面文件 | píng miàn wén jiàn |
| relational database | 关系数据库 | guān xì shù jù kù |
| data redundancy | 数据冗余 | shù jù rǒng yú |
| data inconsistency | 数据不一致 | shù jù bù yī zhì |
| entity | 实体 | shí tǐ |
| primary key | 主键 | zhǔ jiàn |
| foreign key | 外键 | wài jiàn |
| composite key | 复合键 | fù hé jiàn |
| referential integrity | 参照完整性 | cān zhào wán zhěng xìng |
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.
Flat files are like papers in a filing cabinet — hard to search, and the same fact ends up duplicated across files.
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.
In a flat-file system the same data is often duplicated across files, which can become inconsistent when only one copy is updated.
That redundancy and the resulting inconsistency is the core problem the relational model solves.
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.
Two tables linked by a foreign key: an order's CustomerID matches the primary key in the customer table
Read a relational table with SELECT
A relational table is just rows (records) and columns (fields). WHERE keeps the rows that match a condition; SELECT then keeps only the columns you asked for.
A primary key:
The primary key uniquely identifies each row. A foreign key is the one that links to another table.
Match each kind of key to what it is.
The primary key identifies a row; a foreign key links to another table; composite = several fields together; candidate = a possible primary key.
A field whose value matches the primary key of another table is a ______ key.
The foreign key is what creates the relationship between two tables.
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
- Terms: a tuple (row) and attribute (column); a secondary key with indexing speeds searches; normalisation goes through First/Second/Third Normal Form.
Referential integrity ensures that:
It prevents orphan records — you cannot reference a primary key that does not exist.
You've got it
- 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