E-R diagrams and normalisation
| English | Chinese | Pinyin |
|---|---|---|
| entity-relationship diagram | 实体关系图 | shí tǐ guān xì tú |
| normalisation | 规范化 | guī fàn huà |
| entity | 实体 | shí tǐ |
| cardinality | 基数 | jī shù |
| one-to-many | 一对多 | yī duì duō |
| many-to-many | 多对多 | duō duì duō |
| link table | 连接表 | lián jiē biǎo |
| normal forms | 范式 | fàn shì |
| atomic | 原子 | yuán zi |
| transitive dependency | 传递依赖 | chuán dì yī lài |
Forty rows to change one phone number
- A school keeps its enrolments in one spreadsheet. Every row is one student on one course, and every row also carries the student's form tutor and the tutor's phone number.
- The tutor changes her number. Forty rows have to be edited. Thirty-nine are. For the rest of the year, one course list rings a stranger.
- Nothing was mistyped. The fault was in the design: a fact that belongs to the tutor was stored once per enrolment, so it could be true in one place and false in another.
- This lesson is the two design tools that prevent that: the entity-relationship diagram 实体关系图, which draws the structure, and normalisation 规范化, which removes the repetition.
Entity-relationship diagrams
- An entity-relationship diagram (E-R diagram) documents a database design: each entity 实体 is a rectangle, each relationship is a line between two rectangles, and the cardinality 基数 is marked at each end.
- In crow's-foot notation a single bar means "one" and a three-pronged foot means "many". Read each line in both directions: each customer places many orders; each order is placed by one customer.
- The diagram is drawn before any table is created, and the relationships on it become the foreign keys.
One rectangle per entity, one line per relationship
A bar for one, a foot for many
In an E-R diagram, the number of one entity that can relate to one of the other, marked at each end of the line, is the ____.
Cardinality is one or many at each end; crow's-foot notation draws a bar for one and a foot for many.
The three kinds of relationship
- One-to-one (1:1): each member has one library card and each card belongs to one member. Rare; the two entities are often merged into one table.
- One-to-many 一对多 (1:M): one customer places many orders; each order belongs to one customer. Implemented by putting the "one" side's primary key into the "many" side's table as a foreign key.
- Many-to-many 多对多 (M:N): a student takes many courses and a course has many students. It cannot be implemented directly; it needs a link table.
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).
Worked example: draw the E-R diagram
- A school has teachers, classes and students. Each teacher teaches many classes; each class is taught by one teacher. Each class has many students; each student is in one class. Students may join many clubs and each club has many students.
- Four rectangles:
TEACHER,CLASS,STUDENT,CLUB.TEACHER—CLASSis one-to-many, the foot atCLASS.CLASS—STUDENTis one-to-many, the foot atSTUDENT.STUDENT—CLUBis many-to-many, a foot at both ends. - The marks: every entity present, every relationship drawn, and the correct cardinality symbol at each end. A line with no symbols is half an answer.
Link tables
- A many-to-many relationship is broken into two one-to-many relationships through a link table 连接表 that holds the two foreign keys.
ENROLMENT(StudentID, CourseID, EnrolmentDate): one student has many enrolments, one course has many enrolments, and each row is one student on one course. Its primary key is the composite of the two foreign keys.- Data about the pairing itself, the date, a grade, goes in the link table; data about the student or the course stays in its own table.
One many-to-many becomes two one-to-many
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.
ENROLMENT(StudentID, CourseID, EnrolmentDate) is a link table. Which statements are true? Select all that apply.
The link table holds the pairing and facts about the pairing. The student's own data stays in STUDENT, or it would repeat on every enrolment.
Normalisation
- Normalisation organises the tables so that each fact is stored exactly once, cutting redundancy and inconsistency. It passes through the normal forms 范式 in order: first, second, third.
- The procedure: find the entities and their attributes; choose a primary key for each; remove repeating groups and non-atomic values (1NF); remove attributes that depend on only part of a composite key (2NF); remove attributes that depend on another non-key attribute (3NF); add foreign keys for the relationships.
- The cost is more tables and more joins. The exam asks for 3NF.
Database service lab
Watch how a DBMS turns a query into safe shared data access.
Put the normal forms in the order you apply them.
You reach 3NF by passing through 1NF then 2NF — each builds on the previous.
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).
First normal form
- A table is in 1NF when every field holds a single, atomic 原子 value, there are no repeating groups, and there is a primary key.
STUDENT(StudentID, Name, Phone)withPhoneholding0123, 0456is not atomic.STUDENT(StudentID, Name, Course1, Course2, Course3)has a repeating group.- Fix both by moving the repeated data to its own table with a row per value:
STUDENT_PHONE(StudentID, Phone),ENROLMENT(StudentID, CourseID).
A Phone field holds "0123, 0456" for one student. Which normal form does the table fail?
Two values in one cell is the 1NF failure. Move the numbers to STUDENT_PHONE(StudentID, Phone), one per row.
Worked example: second normal form
ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity)has the composite primary key(OrderID, ProductID). Is it in 2NF?- Test each non-key field against the whole key.
Quantitydepends on bothOrderIDandProductID: which order, which product. Fine. CustomerIDandCustomerNamedepend onOrderIDalone, only part of the key: a partial dependency, so the table is not in 2NF. Split it:ORDER(OrderID, CustomerID, CustomerName)andORDER_LINE(OrderID, ProductID, Quantity).
Worked example: third normal form
- Is
ORDER(OrderID, CustomerID, CustomerName)in 3NF? - A table is in 3NF when it is in 2NF and every non-key field depends only on the primary key, not on another non-key field.
CustomerNamedepends onCustomerID, which is not the key: a transitive dependency 传递依赖, so the table is not in 3NF. - Split again:
ORDER(OrderID, CustomerID)andCUSTOMER(CustomerID, CustomerName), withCustomerIDa foreign key. The name is now stored once, however many orders the customer places.
Each form removes one kind of dependency
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.
Saying why a table is, or is not, in 3NF
- Not in 3NF: name the dependency. "
TEACHERis not in 3NF because the non-key attributeDepartmentNamedepends on the non-key attributeDepartmentID, not on the primary key." - In 3NF: cover all three conditions. "Every attribute is atomic with no repeating groups; there is no partial dependency on part of the key; every non-key attribute depends only on the primary key, with no transitive dependency."
- Then, if asked, give the normalised tables in the standard notation with the foreign keys marked.
In TEACHER(TeacherID, Name, DepartmentID, DepartmentName), why is the table not in 3NF?
A transitive dependency. Split out DEPARTMENT(DepartmentID, DepartmentName) and keep DepartmentID in TEACHER as a foreign key.
Marks that slip away
- 2NF is only a question when the primary key is composite. A single-field key cannot have a partial dependency.
- 3NF requires 2NF. Say both when justifying.
- Atomic means one value per cell. "Two phone numbers in one field" is a 1NF failure, not a 3NF one.
- Naming the normal form is not the answer; naming the dependency is. More tables after normalising is the point, not a fault.
You've got it
- an E-R diagram shows entities as rectangles and relationships as lines with the cardinality at each end: 1:1, 1:M, M:N
- a many-to-many relationship is stored through a link table of the two foreign keys, its primary key their composite
- 1NF atomic values, no repeating groups, a primary key · 2NF no partial dependency on part of a composite key · 3NF no transitive dependency between non-key attributes
- justify by naming the dependency, then give the split tables with their foreign keys