Entity-relationship modelling

Why it matters

Most bad schemas are bad because nobody drew the data first. The entity-relationship model is the standard way to capture what the data is about (things, their properties, how they relate) before deciding on tables. It is a staple of DBMS exams and a frequent interview exercise ("design the schema for a library"), and the cardinality and participation decisions you make here turn directly into foreign keys, junction tables and NOT NULL constraints in the next lessons.

The idea

The ER model describes a domain with three building blocks: entities (things that exist independently: a student, a subject offering, a book), attributes (properties of entities or relationships: name, date of birth), and relationships (associations between entities: a student enrols in an offering). An entity set is all entities of one type; in a diagram it is a rectangle, attributes are ovals, and relationships are diamonds.

Kinds of attributes

Relationships: cardinality and participation

A relationship's cardinality ratio says how many entities on each side can be associated: one-to-one (a department has one head), one-to-many (a department has many professors, each professor belongs to one department), or many-to-many (students enrol in many subjects, subjects have many students). Participation says whether every entity must take part: total participation (every professor must belong to a department; drawn as a double line) or partial (not every student must hold a scholarship). Relationships can have attributes too: the grade belongs to the enrols relationship, not to the student or the subject alone.

Weak entities

A weak entity cannot be identified by its own attributes alone; it depends on an owner entity through an identifying relationship. A room is identified by its number within a building (room 101 exists in many buildings); a dependant is identified by name within an employee's record. Its partial key (room number) plus the owner's key forms its full key, and it always has total participation in the identifying relationship. Diagrams draw weak entities and identifying relationships with double outlines.

Generalisation and specialisation

When entity sets share attributes, the model can arrange them in a hierarchy: specialisation splits a general entity into subtypes (an Employee is a Professor or a Staff member, each with extra attributes), generalisation is the same idea built bottom-up. Constraints describe the hierarchy: disjoint or overlapping (can one entity be in two subtypes?) and total or partial (must every entity belong to some subtype?). Aggregation lets a relationship itself take part in another relationship, such as a supervises relationship attached to a works on project assignment.

A method for interview exercises

1. 1

   List the nouns

   Read the requirements and underline nouns: candidate entities (Book, Member, Loan, Author).
2. 2

   Decide entity versus attribute

   If it has its own attributes or relationships, it is an entity (Author has a name and writes many books); otherwise it is an attribute (a book's title).
3. 3

   List the verbs

   Verbs between nouns become relationships: a member borrows a copy; an author writes a book.
4. 4

   Fix cardinality and participation for each

   Ask both directions: can a copy be borrowed by many members at once? Must every book have an author?
5. 5

   Choose keys and spot weak entities

   Every entity needs a key; copies of a book might be weak entities identified by copy number within the book.
6. 6

   Put attributes in the right place

   Loan date and due date belong to the borrow relationship, not to the member or the copy.

Trade-offs

ER diagrams are excellent for agreeing on meaning with non-engineers and for catching design errors before any data exists, but they omit performance concerns, and the notation varies (Chen's diamonds, crow's foot, UML class diagrams). Over-modelling (hierarchies and aggregation everywhere) produces schemas that are correct and awkward; most production schemas use a small part of the notation well.

In an interview

Talk through decisions, not shapes: "Book and Copy are separate entities, since a library owns several copies of one title. Copy is weak, identified by copy number within its book. Member borrows Copy is one-to-many over time but one active loan per copy, so the loan becomes its own entity with dates, which keeps history." That level of reasoning is what the exercise tests.