Why databases and the relational model
| English | Chinese | Pinyin |
|---|---|---|
| table | 表 | biǎo |
| relational database | 关系数据库 | guān xì shù jù kù |
| flat files | 平面文件 | píng miàn wén jiàn |
| data redundancy | 数据冗余 | shù jù rǒng yú |
| data inconsistency | 数据不一致 | shù jù bù yī zhì |
| integrity | 完整性 | wán zhěng xìng |
| field | 字段 | zì duàn |
| entity | 实体 | shí tǐ |
| record | 记录 | jì lù |
| tuple | 元组 | yuán zǔ |
| attribute | 属性 | shǔ xìng |
| primary key | 主键 | zhǔ jiàn |
| composite key | 复合键 | fù hé jiàn |
| candidate key | 候选键 | hòu xuǎn jiàn |
| secondary key | 次键 | cì jiàn |
| indexing | 索引 | suǒ yǐn |
| foreign key | 外键 | wài jiàn |
| referential integrity | 参照完整性 | cān zhào wán zhěng xìng |
| one-to-many | 一对多 | yī duì duō |
| many-to-many | 多对多 | duō duì duō |
The paper IBM did not want
- In 1970 an IBM researcher called Edgar Codd published a twelve-page paper proposing that data be stored in simple tables, linked by shared values, and asked for in a language that said what you wanted rather than where it was on the disk.
- IBM already sold a database that stored data as trees tied to the file layout, so it ignored him for years. Every program written against those files had to be rewritten whenever the layout changed.
- By the 1980s every bank, airline and government department was moving to Codd's tables. Half a century later they still run on them.
- This lesson is the problems of the file-based approach, how a relational database 关系数据库 solves them, and the words you must use exactly.
The file-based approach
- Before databases, each program kept its own flat files 平面文件: the sales program had a customer file, the accounts program had another, the delivery program a third.
- Each file had a fixed layout that the program's code depended on, and nothing outside the program knew what was in it.
- It works for one small program. The trouble starts when the second program needs the same data.
Three programs, three copies of the customer
The limitations
- Data redundancy 数据冗余: the same data, a customer's address, is held in several files, wasting storage and effort.
- Data inconsistency 数据不一致: the copies are updated separately, so they drift apart and nobody knows which is right.
- Data dependence: programs are tied to the file format, so a change to the layout means rewriting every program that uses the file.
- Integrity 完整性 is hard to enforce, data is hard to share safely, searching across files is slow, and security cannot be set per field.
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.
Which are limitations of the file-based approach? Select all that apply.
Redundancy, inconsistency and data dependence are the three named limitations. Tables and joins belong to the relational approach that replaces it.
How a relational database answers them
- A relational database stores the data in tables 表 managed by one piece of software, the DBMS, which every program uses.
- Each fact is stored once, so redundancy and inconsistency disappear: an address is changed in one place and every program sees the change.
- Programs ask the DBMS for data by name, so the storage can change without the programs changing: data independence.
- Integrity rules, access rights and backups are enforced centrally, and any table can be searched or joined with any other.
One copy of the data, one gatekeeper
How does a relational database remove data inconsistency?
One copy, one update, no drift. The DBMS gives every program the same current value.
The vocabulary: tables, rows and columns
- A table (a relation) is a grid of rows and columns, one table for each type of entity 实体, a thing about which data is stored:
CUSTOMER,ORDER,PRODUCT. - A record 记录 is one row, one instance of the entity; the formal word is tuple 元组.
- A field 字段 is one column, one piece of information about each record; the formal word is attribute 属性.
Rows are records, columns are fields, and one column reaches into the other 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.
The vocabulary: keys
- A primary key 主键 is a field, or combination of fields, that uniquely identifies each record; it is never null and never duplicated. A composite key 复合键 is a primary key made of two or more fields.
- A candidate key 候选键 is any field or combination that could serve as the primary key. A secondary key 次键 is a non-primary field that is indexed for fast searching; indexing 索引 builds an index on a field so look-ups and joins run faster.
- A foreign key 外键 is a field whose value matches the primary key of another table, linking the two. In shorthand, the primary key is underlined and the foreign key noted:
CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate) -- CustomerID is a foreign key → CUSTOMER
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.
Worked example: name the parts of a library database
- A library records its books, its members and each loan. Identify the entities, a primary key for each, and the foreign keys.
- Entities:
BOOK,MEMBER,LOAN. Primary keys:BookID,MemberID,LoanID, each chosen because it is unique for every record and never blank; a title or a name would not do, since two books can share a title. LOAN(LoanID, BookID, MemberID, DateOut, DateDue):BookIDandMemberIDare foreign keys, each matching the primary key of its own table. A member's phone number belongs inMEMBER, not in every loan.
In LOAN(LoanID, BookID, MemberID, DateOut), BookID and MemberID are ____ keys.
Each matches the primary key of another table, BOOK and MEMBER, and links the loan to them.
Relationships and referential integrity
- A relationship links two entities. One-to-one: each member has one library card. One-to-many 一对多: one member has many loans; each loan belongs to one member. Many-to-many 多对多: a book has many authors and an author writes many books.
- Referential integrity 参照完整性 means every foreign-key value must match an existing primary key in the table it refers to: no loan for a member who does not exist, no orphan records.
- The DBMS enforces it: it refuses an insert with an unknown foreign key, and refuses to delete a record that other records still refer to.
Referential integrity ensures that:
It prevents orphan records — you cannot reference a primary key that does not exist.
Match each relationship to its type.
Count how many of each entity can be linked to one of the other. Many-to-many needs a link table to store.
Worked example: what referential integrity stops
- A customer with three outstanding orders is deleted from the
CUSTOMERtable. Explain how referential integrity applies. - Each order's
CustomerIDis a foreign key that must match an existing customer. Deleting the customer would leave three orders pointing at a record that no longer exists: orphan records. - The DBMS therefore refuses the deletion until the orders are deleted or reassigned, or, if set up to cascade, deletes the orders too. Either way no order ever refers to a customer who is not there.
- Say what the rule is, what would break it, and what the DBMS does.
Referential integrity allows a customer to be deleted while orders in another table still refer to that customer.
That would create orphan records. The DBMS refuses the deletion, or cascades it to the orders, so every foreign key keeps pointing at an existing record.
Definitions the examiner accepts
| Term | Definition |
|---|---|
| entity | a thing about which data is stored, represented by one table |
| attribute | one item of data about an entity, a column |
| tuple | one row of a table, one record |
| primary key | an attribute or combination of attributes that uniquely identifies each tuple |
| candidate key | an attribute or combination that could be chosen as the primary key |
| secondary key | an indexed attribute used to search the table quickly |
| foreign key | an attribute in one table that is the primary key of another, forming the link |
| referential integrity | every foreign-key value refers to an existing primary key |
Marks that slip away
- A tuple is a row, an attribute is a column. Swapping them costs both marks.
- "Unique" alone does not define a primary key; it identifies each record and is never null.
- A foreign key may repeat: one customer, many orders. It is the primary key that may not.
- A secondary key is for fast searching, not for identification. Do not call it "a second primary key".
You've got it
- the file-based approach suffers redundancy, inconsistency and data dependence, with weak integrity, sharing, searching and security
- a relational database stores each fact once in tables managed by one DBMS, so programs are independent of the storage and rules are enforced centrally
- entity → table · tuple → record, row · attribute → field, column · primary key identifies, foreign key links, candidate could identify, secondary is indexed for searching
- referential integrity: every foreign key matches an existing primary key, so no orphan records