Why databases and the relational model
Introduced| English | Meaning |
|---|---|
| table/ˈteɪbl/ | A collection of data organised into rows and columns. |
| relational database/rɪˈleɪʃənl ˈdeɪtəbeɪs/ | A database that organises data into tables connected through matching key values. |
| flat files/flæt faɪlz/ | Files storing records without the relational structure and linked-table management provided by a relational database system. |
| data redundancy/ˈdeɪtə rɪˈdʌndənsi/ | data redundancy |
| data inconsistency/ˈdeɪtə ˌɪnkənˈsɪstənsi/ | data inconsistency |
| integrity/ɪnˈteɡrɪti/ | The accuracy, completeness and protection of data against unauthorised or unintended alteration. |
| field/fiːld/ | A named item of data within a database record. |
| entity/ˈentɪti/ | A thing about which data is stored, represented by one table. |
| record/ˈrekɔːd/ | A stored collection of information about an item, person or event. |
| tuple/ˈtuːpl/ | A row, an attribute is a column. |
| attribute/ˈætrɪbjuːt/ | A named property or characteristic associated with an object, element or record. |
| primary key/ˈpraɪməri kiː/ | A field or combination of fields that uniquely identifies each record in a database table. |
| composite key/ˈkɒmpəzɪt kiː/ | A primary key made of two or more fields. |
| candidate key/ˈkændɪdeɪt kiː/ | An attribute or combination that could be chosen as the primary key. |
| secondary key/ˈsekəndəri kiː/ | A non-primary field that is indexed for fast searching; indexing builds an index on a field so look-ups and joins run faster. |
| indexing/ˈɪndeksɪŋ/ | indexing |
| foreign key/ˈfɒrən kiː/ | A field or group of fields that refers to a candidate key in another or the same database table. |
| referential integrity/ˌrefəˈrenʃl ɪnˈteɡrɪti/ | Every foreign-key value refers to an existing primary key. |
| one-to-many/wʌn tə ˈmeni/ | A relationship in which one instance of an entity can be associated with several instances of another entity. |
| many-to-many/ˈmeni tə ˈmeni/ | A database relationship in which each record on either side may be associated with several records on the other side. |
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:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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.
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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.
Difficulty 2/5 · 140 coins + a small bonus on first completion today
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?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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.
Difficulty 2/5 · 140 coins + a small bonus on first completion today
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.
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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.
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
It prevents orphan records — you cannot reference a primary key that does not exist.
Match each relationship to its type.
Difficulty 2/5 · 140 coins + a small bonus on first completion today
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.
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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