The Entity–Relationship Model

Entities, entity sets and attributes (simple, composite, multivalued, derived, key), relationships with degree, cardinality ratios and participation constraints, weak entity sets, specialisation, generalisation and aggregation, ER notation, and the rules for converting an ER diagram into relational tables (including the minimum number of tables).

Purpose

The ER model is a conceptual design tool: before choosing tables, describe the real-world things (entities), their properties (attributes) and the associations among them (relationships). The diagram is then mapped mechanically to a relational schema.

Entities and attributes

An entity is a distinguishable object (a student, a course); an entity set is a collection of similar entities.

Attribute type	Example	Diagram (Chen)
Simple (atomic)	`roll_no`	ellipse
Composite	`name` = (`first`, `last`)	ellipse with sub-ellipses
Single-valued	`dob`	ellipse
Multivalued	`phone_numbers`	double ellipse
Derived	`age` (from `dob`)	dashed ellipse
Key	`roll_no`	underlined

Keys: a superkey uniquely identifies an entity; a candidate key is a minimal superkey; the primary key is the candidate key chosen by the designer.

Relationships

A relationship set associates entities from entity sets; it may have descriptive attributes (e.g. grade on Enrolls).

Degree: number of participating entity sets (unary/recursive, binary, ternary).
Cardinality ratio (for binary relationships): 1:1, 1:N, M:N — the maximum number of relationship instances an entity can take part in.
Participation: total (every entity must participate; double line) or partial.
Roles label the ends of recursive relationships (an employee manages another employee).

Students enrol in many courses; each course has many students; grade belongs to the relationship.

Weak entity sets

A weak entity set has no key of its own. It is identified by:

an owner (identifying) entity set,
an identifying relationship (double diamond) in which it participates totally, and
a partial key (discriminator, dashed underline).

Example: Payment(payment_no, amount) of a Loan: the key of Payment is (loan_id, payment_no).

Extended ER features

Specialisation (top-down): sub-groups of an entity set with extra attributes (Employee → Engineer, Manager). Generalisation is the bottom-up view. Constraints: disjoint vs overlapping, total vs partial. Subclasses inherit attributes and relationships.
Aggregation: treat a relationship as an entity so it can participate in another relationship (a Works_on relationship monitored by a Manager).

From ER diagram to tables

ER construct	Relational mapping
Strong entity set	table with its simple attributes; composite attributes flattened
Weak entity set	table with its attributes + owner's key; PK = owner key + partial key
1:1 relationship	foreign key on either side (prefer the side with total participation); with total participation on both sides, merge into one table
1:N relationship	foreign key on the N side (plus relationship attributes)
M:N relationship	separate table with both keys (PK = the pair) + attributes
$n$ -ary relationship	separate table with all participating keys
Multivalued attribute	separate table (entity key, value)
Derived attribute	usually not stored
Specialisation	a table per subclass with the superclass key (or one table with a type column)