Relational databases: a key and a relationship
Introducido| English | Significado |
|---|---|
| relational database/rɪˈleɪʃənl ˈdeɪtəbeɪs/ | A database that organises data into tables connected through matching key values. |
| primary key/ˈpraɪməri kiː/ | clave primaria |
| foreign key/ˈfɒrən kiː/ | clave foránea |
| record/ˈrekɔːd/ | A stored collection of information about an item, person or event. |
| field/fiːld/ | A named item of data within a database record. |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | Organising relational database tables to reduce unnecessary duplication and avoid update, insertion and deletion anomalies. |
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?
Dificultad 1/5 · 100 monedas + un pequeño bono en el primer intento de hoy
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?
Dificultad 1/5 · 100 monedas + un pequeño bono en el primer intento de hoy
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.
Dificultad 1/5 · 100 monedas + un pequeño bono en el primer intento de hoy
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.
One source row, several matches
Each source row contributes one result per matching record. A left join retains an unmatched source row as one NULL-matched row.
What problem does normalisation prevent?
Dificultad 1/5 · 100 monedas + un pequeño bono en el primer intento de hoy
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?
Dificultad 2/5 · 140 monedas + un pequeño bono en el primer intento de hoy
0 + 2 + 2 + 3 = 7; the student with none is omitted.
Why is Ben missing from the join result?
Dificultad 1/5 · 100 monedas + un pequeño bono en el primer intento de hoy
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.
Dificultad 1/5 · 100 monedas + un pequeño bono en el primer intento de hoy
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.