E-R diagrams and normalisation · ⁨E-R 图和规范化⁩

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

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.

Worked example: draw the E-R diagram

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

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.

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) with Phone holding 0123, 0456 is 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).

Worked example: second normal form

Worked example: third normal form

Each form removes one kind of dependency

Saying why a table is, or is not, in 3NF

• Not in 3NF: name the dependency. "TEACHER is not in 3NF because the non-key attribute DepartmentName depends on the non-key attribute DepartmentID, 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.

Marks that slip away

You've got it