E-R diagrams and normalisation
| English | Chinese | Pinyin |
|---|---|---|
| normalisation | 规范化 | guī fàn huà |
| entity-relationship diagram | 实体关系图 | shí tǐ guān xì tú |
| cardinality | 基数 | jī shù |
| one-to-many | 一对多 | yī duì duō |
| many-to-many | 多对多 | duō duì duō |
| link table | 连接表 | lián jiē biǎo |
| atomic | 原子 | yuán zi |
| transitive dependency | 传递依赖 | chuán dì yī lài |
Designing a database
- Before building, you design the structure: which entities, and how they relate.
- An E-R diagram captures that; normalisation 规范化 then removes redundancy.
- A clean design stores each fact exactly once.
Database service lab
Watch how a DBMS turns a query into safe shared data access.
Entity-relationship diagrams 实体关系图
- Each entity is a rectangle; each relationship is a line, with the cardinality 基数 marked at each end:
- one-to-one (1:1), one-to-many 一对多 (1:M) — one customer has many orders; each order has one customer — and many-to-many 多对多 (M:N).
An E-R diagram: one class has many students.
Crow's-foot notation marks the cardinality — 1:1, 1:M or M:N — at each end of a relationship.
Each customer can place many orders, but each order belongs to one customer. This relationship is:
One customer → many orders, each order → one customer: a one-to-many relationship.
Match each relationship to its cardinality.
1:1 each side has one; 1:M one side has many; M:N both sides have many (needs a link table).
Link tables 连接表
- A many-to-many relationship cannot be stored directly.
- Break it into two one-to-many relationships through a link table holding the two foreign keys:
ENROLMENT(StudentID, CourseID, EnrolmentDate)
- Here one student has many enrolments, and one course has many enrolments.
A link table turns a many-to-many relationship into two one-to-many relationships
How is a many-to-many relationship implemented in a relational database?
A link (junction) table holds a foreign key to each side, turning M:N into two 1:M relationships.
Normalisation
- Normalisation organises tables to cut redundancy and inconsistency, through normal forms in order:
- 1NF — every field holds a single (atomic 原子) value, no repeating groups, and there is a primary key.
- 2NF — in 1NF, and every non-key field depends on the whole primary key (matters for composite keys).
- 3NF — in 2NF, and every non-key field depends only on the key, not on another non-key field (no transitive dependency 传递依赖).
- Aim for 3NF: each fact stored once, so update anomalies disappear (the cost is more tables and joins).
Normalisation moves from 1NF to 2NF to 3NF, splitting tables so each fact is stored only once
Put the normal forms in the order you apply them.
You reach 3NF by passing through 1NF then 2NF — each builds on the previous.
Normalising to 3NF stores each fact once and removes update anomalies, at the cost of more tables and joins.
That trade-off — cleaner data versus more joins — is why 3NF is the usual target.
The main aim of normalisation is to:
Normalising to 3NF stores each fact once, removing update/insert/delete anomalies (at the cost of more joins).
You've got it
- E-R diagrams show entities + relationships with cardinality (1:1, 1:M, M:N)
- a many-to-many relationship is implemented with a link table of two foreign keys
- 1NF atomic values · 2NF depends on the whole key · 3NF no transitive dependency
- aim for 3NF to store each fact once and avoid anomalies