ER Models

📘 Learn ER Models from zero

Start from zero. Before you ever write a SQL table, you need a blueprint of what your data describes and how the pieces connect. An Entity-Relationship (ER) model is that blueprint: a conceptual, technology-neutral description of the real-world "things" your system tracks and the associations between them.

Analogy: think of an architect's floor plan before a house is built. The plan names the rooms (entities), describes each room's features like square footage and window count (attributes), and shows which rooms connect through doorways (relationships). You design the plan on paper first; only later do you pour concrete (create tables).

The three core building blocks:

• Entity type — a category of thing (drawn as a rectangle), e.g. Employee, Department. A single member is an entity instance.
• Attribute — a property of an entity (an oval), e.g. name, salary. The attribute (or set of attributes) that uniquely identifies each instance is the key attribute, drawn underlined, e.g. SSN. A key can be composite (several attributes together). Attributes can also be composite (e.g. address), multivalued (double oval), or derived (dashed oval, e.g. age from birthdate).
• Relationship type — an association between entity types (a diamond), e.g. WORKS_FOR connecting Employee and Department.

Worked example. Requirement: "Each employee works for exactly one department; a department employs many employees." Scan it: nouns Employee, Department become two rectangles. The verb works for becomes a diamond WORKS_FOR. "Exactly one" on the department side and "many" on the employee side make this a 1:N relationship (one department, many employees). Give Employee attributes SSN (key), name, salary; give Department dept_no (key), dept_name. That picture is the ER diagram, and it maps mechanically to two tables where the "N"-side table, Employee, carries a dept_no foreign key.

Key insight: an ER model turns an ambiguous English requirement into a precise, drawable structure — entity types, attributes, relationship types, and cardinality/participation constraints — that then translates almost mechanically into relational tables.

✨ Added by the guide to build intuition — not from the source course.

🎯 Guided practice

Problem 1 (Easy) — Identify and classify. Requirement: "A library has Books and Members. A Member can borrow many Books, and a Book can be borrowed by many Members over time. Each borrowing has a due date." Draw the ER model.

1. Find entities (nouns): Book and Member are the two thing-categories → two rectangles.
2. Find the relationship (verb): borrows → a diamond BORROWS connecting them.
3. Determine cardinality: "a Member can borrow many Books" and "a Book can be borrowed by many Members" → both sides are "many" → this is an M:N relationship.
4. Place the relationship attribute: due_date describes a specific borrowing, not the book and not the member, so it attaches to the BORROWS diamond — a relationship attribute, not an entity attribute.
5. Map to tables: an M:N relationship always becomes a junction table. Note the "over time" wording: the same member can borrow the same book on different occasions, so (book_id, member_id) alone is not unique — the key must include the borrowing occasion. Result: Book(book_id, ...), Member(member_id, ...), and Borrows(book_id, member_id, borrow_date, due_date) with primary key (book_id, member_id, borrow_date) (or a surrogate loan_id), where book_id and member_id are foreign keys. Core pattern learned: M:N ⇒ separate junction table; relationship attributes live there; if the pairing repeats over time, the key must include a distinguishing attribute (or surrogate).

Problem 2 (Medium) — Weak entity + participation. Requirement: "Every Employee may have Dependents (e.g. children). A dependent is identified only by their first name, but only within the scope of that employee; a dependent cannot exist in the system without an employee." Model this.

1. Spot the weakness signal: a Dependent has no key of its own — two different employees could each have a child named "Alex." So Dependent is a weak entity type (drawn as a double rectangle).
2. Identify the owner: Dependent depends on Employee via an identifying relationship HAS_DEPENDENT (double diamond). Employee is the identifying / owner entity.
3. Find the partial key: first_name alone isn't unique globally, but it is unique within one employee. It's the partial key (dashed underline). The full key of Dependent = owner's key + partial key = (SSN, first_name).
4. Set participation: "cannot exist without an employee" ⇒ Dependent has total participation in HAS_DEPENDENT (double line to the diamond) — in fact every weak entity has total participation in its identifying relationship by definition. Employee's participation is partial (the word "may").
5. Map to tables: Dependent(emp_ssn, first_name, ...) with composite primary key (emp_ssn, first_name), where emp_ssn is a foreign key to Employee(SSN). As a primary-key column emp_ssn is already NOT NULL; add ON DELETE CASCADE on that foreign key so deleting an employee removes their dependents, enforcing the existence dependency. Core pattern learned: no own key + existence-dependent ⇒ weak entity; its primary key is always owner key + partial key, and the identifying FK is mapped with ON DELETE CASCADE.

✨ Added by the guide — work these before the full problem set.