Real-life analogy: The architect blueprint

An architect draws a blueprint before constructing a building. The ER diagram is the blueprint of a database. Entities are rooms, attributes are room dimensions, and relationships are doors connecting rooms. You design on paper first — catching flaws cheaply before writing a single SQL statement.

Core ER components

-- ER: Student(StudentID PK, Name, DOB)
-- ER: Course(CourseID PK, Title, Credits)
-- ER: Enrolls_In = M:N relationship with attribute Grade

CREATE TABLE Student (
    StudentID   INT          PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL,
    DOB         DATE
);

CREATE TABLE Course (
    CourseID    VARCHAR(10)  PRIMARY KEY,
    Title       VARCHAR(200) NOT NULL,
    Credits     INT          CHECK (Credits BETWEEN 1 AND 6)
);

-- M:N relationship becomes a junction table
CREATE TABLE Enrolls_In (
    StudentID   INT         REFERENCES Student(StudentID),
    CourseID    VARCHAR(10) REFERENCES Course(CourseID),
    Grade       CHAR(2),
    EnrollDate  DATE        DEFAULT CURRENT_DATE,
    PRIMARY KEY (StudentID, CourseID)  -- Composite PK
);

Cardinality and participation constraints

Total participation (double line) means every instance must participate. Partial participation (single line) means participation is optional. In SQL, total participation on a FK side means NOT NULL.

1. Strong entity: Becomes a table. Key attribute becomes PRIMARY KEY.
2. Weak entity: PK = owner PK + partial key. Has FK to owner.
3. 1:1 relationship: Add FK to either side (prefer total-participation side).
4. 1:N relationship: FK goes on the N-side (many) table.
5. M:N relationship: Creates a separate junction table with composite PK.
6. Multi-valued attribute: Separate table with entity PK + the attribute.
7. Derived attribute: NOT stored — computed via SQL when needed.

Specialization, generalization, and aggregation (Extended ER)

The extended ER (EER) model adds three concepts the basic model lacks. Specialization splits an entity into subclasses top-down (Employee → Engineer, Manager); generalization merges similar entities into a superclass bottom-up (Car, Truck → Vehicle). Both are drawn as an ISA triangle. Subclasses inherit all attributes and relationships of the superclass and add their own. Aggregation treats a whole relationship as a single higher-level entity so it can participate in another relationship — needed when a relationship must relate to an entity (e.g., a Manager MONITORS the (Employee WORKS_ON Project) relationship itself).

• Mapping ISA to tables, option A: one table per subclass carrying the superclass PK as PK+FK — clean, needs joins to reassemble.
• Option B: subclass tables repeat all superclass attributes — no joins, but fails for overlapping specialization (data duplicated).
• Option C (single-table): one wide table with a type discriminator column and NULLs for inapplicable attributes — fast, denormalized; common in ORMs as "single-table inheritance."

Ternary and recursive relationships — where designs go wrong

A ternary relationship connects three entities simultaneously: Supplier SUPPLIES Part TO Project. The crucial GATE insight: a ternary relationship is not equivalent to three binary relationships. "Supplier S supplies part P to project J" carries information (which part went to which project from which supplier) that the three pairwise facts cannot reconstruct. A ternary relationship maps to a table with FKs to all three entities, PK typically the combination of all three.

A recursive (unary) relationship relates an entity to itself, with role names distinguishing the two sides: Employee SUPERVISES Employee (roles: supervisor, supervisee). In SQL this is simply a self-referencing foreign key: ManagerID INT REFERENCES Employee(EmpID). Org charts, bill-of-materials, category trees, and social-network follower graphs are all recursive relationships.

GATE: counting tables from an ER diagram: The standard question gives an ER diagram and asks the minimum number of tables. Rules of thumb: strong entity → 1 table; weak entity → 1 table; 1:1 relationship → 0 extra tables (merge FK into one side); 1:N → 0 extra tables (FK on N-side); M:N → 1 extra table; multi-valued attribute → 1 extra table; ternary → 1 extra table. Count carefully — 1:1 with total participation on both sides can even merge two entities into a single table.

Worked design: hospital appointments in four steps

How a real design session goes, start to finish. Requirement: "Patients book appointments with doctors; each appointment has a date and diagnosis; doctors belong to one department; a patient can see many doctors and vice versa."

1. Identify entities: Patient (PatientID, Name, DOB), Doctor (DoctorID, Name), Department (DeptID, DeptName).
2. Identify relationships and cardinalities: Patient—BOOKS—Doctor is M:N (many patients, many doctors); Doctor—BELONGS_TO—Department is N:1 with total participation (every doctor has a department).
3. Attach relationship attributes: ApptDate and Diagnosis describe the booking, not the patient or the doctor — they live on the M:N relationship.
4. Map to tables: Patient, Doctor (with NOT NULL DeptID FK), Department, and junction table Appointment(PatientID, DoctorID, ApptDate, Diagnosis) — PK (PatientID, DoctorID, ApptDate), because the same pair can meet on different dates.

The step everyone misses: Choosing the junction table's PK. (PatientID, DoctorID) alone forbids repeat visits — a silent business-rule bug baked into the schema. Adding ApptDate to the key (or using a surrogate AppointmentID) fixes it. ER modeling errors surface as impossible-to-express data, not as error messages.

Practice questions (GATE-style)

1. Student has StudentID, Name, PhoneNumbers (multi-valued), DOB, Age (derived). How many tables result from correct ER mapping? (Answer: 2 tables — Student(StudentID, Name, DOB) and StudentPhone(StudentID, PhoneNumber). Age is derived so not stored.)
2. Weak entity Dependent has partial key DepName, owner Employee has PK EmpID. What is the PK of Dependent table? (Answer: (EmpID, DepName) — composite PK using owner PK + partial key.)
3. In M:N between Student and Course with relationship attribute Grade, where does Grade go? (Answer: In the junction table Enrolls_In(StudentID, CourseID, Grade).)
4. Total participation on a FK side translates to SQL as: (Answer: NOT NULL constraint on the foreign key column.)
5. Employee MANAGES Department is 1:1. Where should the FK go? (Answer: On the side with total participation, or merge into one table if both sides are total participation.)
6. ER diagram: strong entities A and B, M:N relationship R between them, multi-valued attribute on A, and a 1:N relationship S from B to A. Minimum tables? (Answer: 4 — A, B, junction table for R, and a table for the multi-valued attribute. S needs no table: FK on the N-side (A).)
7. Specialization of Person into Student and Employee is overlapping and total. What does this mean? (Answer: Overlapping — a person may be both a student and an employee simultaneously. Total — every person must be at least one of the two. Mapping: separate subclass tables sharing Person's PK handle overlap cleanly; the single-table option needs two boolean discriminators.)
8. Why can't ternary relationship SUPPLIES(Supplier, Part, Project) be replaced by three binary relationships? (Answer: The three binary facts "S supplies P", "P is used by J", "S supplies to J" cannot reconstruct which specific (supplier, part, project) triples occurred — the ternary fact carries joint information. This is the same reason a 3-attribute table cannot always be losslessly decomposed into its 2-attribute projections.)