E-R diagrams and normalisation
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.
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).
Practice
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.
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.
Practice
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).
Practice
A table is in first normal form (1NF) when:
1NF requires atomic values (no repeating groups) and a primary key.
You've got it
Key idea
- 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
Practice
Third normal form (3NF) removes:
3NF (building on 2NF) ensures non-key fields depend only on the key, not on each other.
Practice
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).