ER Modelling to Relations
Entities, relationships and cardinality — and the mechanical rules that turn a diagram into tables.
Skip to the animationAn ER model describes the world as entities, the relationships between them, and the attributes of each — and a short list of mechanical rules turns that diagram into tables.
The three pieces
- Entity
- Something that exists independently and is worth storing — a Student, a Course. If it only makes sense in the presence of something else, it is probably not an entity.
- Attribute
- A fact belonging to one entity. Attributes may be simple, composite (an address made of parts), multivalued (several phone numbers) or derived (age, from date of birth).
- Relationship
- An association between entities. Crucially it can carry attributes of its own — a grade belongs to the enrolment, not to the student and not to the course.
- Cardinality
- How many of each side may participate: 1:1, 1:N or M:N. This single annotation decides how many tables the design produces.
- Weak entity
- One with no key of its own, identified only in combination with an owner — an OrderLine within an Order. It takes the owner's key as part of its own.
The vocabulary
- Functional dependency (X → Y)
- Knowing X pins down Y. If two rows agree on X they must agree on Y. This is a statement about the world being modelled, not about the rows that happen to be in the table today.
- Superkey and candidate key
- A superkey determines every attribute in the relation. A candidate key is a superkey with nothing spare in it — remove any attribute and it stops working.
- Prime attribute
- One that appears in some candidate key. The normal forms are mostly rules about how non-prime attributes are allowed to depend on prime ones.
- Partial dependency
- A non-prime attribute determined by only part of a composite key. Forbidden by 2NF, and the direct cause of the update, insert and delete anomalies.
- Transitive dependency
- Key → X → Y where X is not a key. Forbidden by 3NF: the fact really belongs in a table keyed by X.
- Lossless decomposition
- Splitting a relation so that re-joining it gives back exactly the original rows — no more, no fewer. Guaranteed when the shared attribute is a key of one of the two pieces.
The mapping rules
| ER construct | Becomes | Key |
|---|---|---|
| Entity | a table | its own primary key |
| 1:1 relationship | a foreign key on either side | unchanged |
| 1:N relationship | a foreign key on the N side | unchanged |
| M:N relationship | a table of its own | both foreign keys together |
| Multivalued attribute | a table of its own | owner key + the value |
| Weak entity | a table | owner key + partial key |
Only M:N relationships and multivalued attributes create extra tables. Everything else is absorbed as a foreign key — which is why cardinality is the annotation worth arguing about.
Why 1:N needs no table
If each course has one department but a department has many courses, then every course row has exactly one department to name — so dept_id fits as a column on Course. A separate table would add a join and buy nothing.
M:N is different: a student row cannot hold "all their courses" in one column without breaking 1NF. The pairing has to live somewhere, and the only place it fits is a table of its own — which is also the only place an attribute like grade can go.
Good modelling is normalisation you never had to do
Applying these rules to the diagram gives Student(sid, sname), Course(cid, cname) and Enrol(sid, cid, grade) — which is exactly what the normalisation topic arrives at after several decomposition steps.
That is not a coincidence. Separating entities from relationships is the same discipline as separating facts that depend on different keys. Normalisation is the repair procedure for a design that skipped this step; it is not an alternative to it.
Advantages and disadvantages
Advantages
- Captures the structure of a problem before any thought about tables or SQL.
- The mapping to relations is mechanical enough for a tool to do it.
- A careful ER model usually lands in BCNF with no normalisation required.
Disadvantages
- Notation varies wildly between textbooks — Chen, crow's foot, UML all differ.
- Cannot express every constraint, so some rules still end up in application code.
- It is easy to model something as an entity that should have been an attribute, and the error is expensive later.
Watch it work
Check yourself
question 1 / 4
One question at a time. Pick an answer to see why it is right or wrong, then move on — there is no score to keep and nothing is saved.