Designing a database
Introduzido| English | Significado |
|---|---|
| tables/ˈteɪblz/ | tabelas |
| relational database/rɪˈleɪʃənl ˈdeɪtəbeɪs/ | a database that stores data in tables linked by matching key values |
| entity/ˈentɪti/ | A thing you store data about: a student, a course, a room. |
| primary key/ˈpraɪməri kiː/ | chave primária |
| foreign key/ˈfɒrən kiː/ | chave estrangeira |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | normalização |
Tables instead of trees, 1970
- In June 1970 Edgar F. Codd, a mathematician at IBM, published "A Relational Model of Data for Large Shared Data Banks". Databases then stored records as linked trees, and programs had to know the path to each one. Codd proposed plain tables 表 linked by matching values.
- Almost every relational database 关系型数据库 today — the kind behind a college's student records — follows his idea.
What did Codd propose in 1970?
Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje
The relational model.
Entities and tables
- An entity 实体 is a thing you store data about: a student, a course, a room. Each entity gets a table; each row is one item; each column is one attribute.
- Students(student_id, name, email); Courses(course_id, title, credits).
In a college database, which is an entity?
Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje
The others are attributes.
Primary keys
- A primary key 主键 uniquely identifies each row: student_id 1057 is one student only.
- Names are poor keys: two students can share a name.
Which is the best primary key for Students?
Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje
It is unique.
Foreign keys and links
- A foreign key 外键 is a column that refers to a primary key in another table.
- A student takes many courses, and a course has many students. A link table Enrolments(student_id, course_id) holds one row per pair; its key is the pair.

In Enrolments, student_id is a foreign key referring to Students.
Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje
It links to the primary key.
Three students each take four courses. How many rows does Enrolments have?
Dificuldade 2/5 · Moedas 140 + pequeno bônus na primeira conclusão hoje
One row per student-course pair.
Worked example: fixing a repeated table
One table stores: student_id, name, email, course_id, course_title. Mei takes three courses, so her email appears three times.
- Problem: if Mei changes her email, three rows must change; miss one and the data disagree.
- Split: Students(student_id, name, email); Courses(course_id, course_title); Enrolments(student_id, course_id).
- Result: each fact is stored once; Enrolments links them with foreign keys.
Why is storing Mei's email in three rows a problem?
Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje
An update anomaly.
Normalisation
- Normalisation 规范化 is this process of splitting tables so each fact is stored once and depends only on its table's key.
- It prevents update errors, at the cost of joining tables when you query.
Normalisation stores each fact once.
Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje
Splitting tables.
A repeated value is a warning sign. If the same email or title appears in many rows, the design probably needs another table.
A relational database stores each entity in a table with a primary key, links tables with foreign keys, uses a link table for many-to-many relationships, and normalises so each fact appears once.
Design tables for a library where members borrow many books and each book is borrowed by many members over time. Give keys.
Entities, keys, link.