Relational databases: a key and a relationship
引入| English | 释义 | 拼音 |
|---|---|---|
| relational database/rɪˈleɪʃənl ˈdeɪtəbeɪs/ | 关系数据库 | guān xì shù jù kù |
| primary key/ˈpraɪməri kiː/ | 主键 | zhǔ jiàn |
| foreign key/ˈfɒrən kiː/ | 外键 | wài jiàn |
| record/ˈrekɔːd/ | 记录 | jì lù |
| field/fiːld/ | 字段 | zì duàn |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | 规范化 | guī fàn huà |
Codd's relational model, 1970
- In 1970 Edgar F. Codd, a researcher at IBM, published "A Relational Model of Data for Large Shared Data Banks". He proposed storing data in tables (relations) linked by values, so programs no longer depended on how data was physically stored.
- Almost every business database since — banks, airlines, school systems — follows his idea. A relational database 关系数据库 stores data in tables linked by a primary key 主键 and a foreign key 外键.
What did Codd propose in 1970?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
The relational model.
Tables, records and fields
- A table holds one kind of entity: Students, Courses. Each row is a record 记录; each column is a field 字段 with a data type.
- A primary key uniquely identifies each record: StudentID, not Name.
Which field is the best primary key for Students?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
It is unique and does not change.
Linking tables
- A foreign key is a field that holds another table's primary key. Enrolment(StudentID, CourseID) links Students to Courses.
- This replaces a many-to-many relationship with two one-to-many relationships.
A foreign key holds the primary key of another table.
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
It links the tables.
Normalisation
- Normalisation 规范化 removes repeated data: store each teacher's email once, not in every lesson row.
- It prevents update anomalies — changing an email in one row but not another.
一个客户可以产生多行匹配结果
每个客户的每笔匹配订单各产生一行;左连接还会为没有订单的客户保留一行NULL匹配结果。
What problem does normalisation prevent?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
Store each fact once.
Worked example: a join
Students: (1, Ana), (2, Ben), (3, Chen). Enrolments: (1, CS), (1, Maths), (3, CS).
SELECT Students.Name, Enrolments.CourseID
FROM Students
JOIN Enrolments ON Students.StudentID = Enrolments.StudentID
WHERE Enrolments.CourseID = 'CS';
- Join: each enrolment row is matched to the student with the same StudentID — 3 rows in total.
- Filter: only CourseID = 'CS' → Ana, CS and Chen, CS. Ben has no enrolment, so an inner join omits him.
Four students have 0, 2, 2 and 3 enrolments. How many rows does an inner join of Students and Enrolments return?
难度 2/5 · 今日首次完成可获 140 金币及少量额外奖励
0 + 2 + 2 + 3 = 7; the student with none is omitted.
Why is Ben missing from the join result?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
An inner join returns only matches.
Integrity and transactions
- Referential integrity: an enrolment cannot refer to a StudentID that does not exist.
- A transaction is all-or-nothing (atomic): moving a student between classes must not leave them in both or neither.
An atomic transaction either completes fully or not at all.
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
No half-moved records.
A name is not a key. Two students can share a name, and names change. Use a unique identifier that never changes as the primary key.
A relational database stores entities in tables of records and fields. A primary key identifies each record; a foreign key links tables; normalisation removes repetition; an inner join returns only matching rows; integrity rules and atomic transactions keep data consistent.
Design tables for a school library (books, members, loans) with primary and foreign keys, and write a query listing the titles borrowed by member 42.
Tables, keys, query.