The Entity-Relationship (ER) Model

What this lesson covers

Before you write a single CREATE TABLE, you need to decide what the database should store and how the pieces relate. The Entity-Relationship model, or ER model, is the standard tool for that step. Proposed by Peter Chen in 1976, it describes a system as a set of entities (things), their attributes (properties) and the relationships between them. You draw it as an ER diagram, discuss it with people who know the business, and then convert it into tables using a fixed set of mapping rules.

Interviewers probe the ER model in three ways. First, definitions: "What is a weak entity?", "What is the difference between total and partial participation?". Second, mapping: "How do you convert a many-to-many relationship into tables?", "How many tables does this diagram need?". Third, a small design task: "Draw an ER diagram for a library" or "for a hospital". This lesson prepares you for all three and ends with a full worked example from requirements to tables.

Since this page cannot show images, ER diagrams here are drawn as text using the notation explained in the next section.

Notation used in this lesson

Chen's original notation draws entities as rectangles, attributes as ovals and relationships as diamonds. We will mimic it in text:

+----------+      entity set (rectangle)
| STUDENT  |
+----------+

+==========+      weak entity set (double rectangle)
|| ROOM   ||
+==========+

  < ENROLLS >      relationship set (diamond)
 << HAS >>        identifying relationship (double diamond)

  (name)          attribute (oval)
  (_roll_no_)     key attribute (underlined)
  ((phone))       multi-valued attribute (double oval)
  [age]           derived attribute (dashed oval)

  ----            partial participation (single line)
  ====            total participation (double line)

Cardinality is written as 1, N or M next to the lines. Other notations exist, notably crow's foot notation used by most database tools, where a three-pronged "foot" marks the "many" side. The concepts are identical; only the drawing differs.

Entities and entity sets

An entity is a real-world thing or concept that can be distinctly identified and about which you want to store data. It can be physical (a student, a book, a doctor) or abstract (a course, a bank account, an appointment).

An entity type describes the structure shared by a group of entities: their name and attributes. An entity set is the collection of all entities of one type that exist in the database at a moment. In practice people say "entity" loosely for all three, but in an interview it helps to be precise:

• Entity: the student Asha with roll number 101.
• Entity type: STUDENT, with attributes roll number, name, date of birth.
• Entity set: all students currently stored.

When you map to tables, an entity set becomes a table and each entity becomes a row.

Attributes

An attribute is a property that describes an entity, such as a student's name. Each attribute takes values from a domain, the set of allowed values: for example, CGPA comes from real numbers between 0 and 10. Attributes are classified along several independent axes.

Simple versus composite

A simple (atomic) attribute cannot be meaningfully split: roll_no, gender.

A composite attribute is made of smaller parts that are each meaningful: name = first name + middle name + last name; address = street + city + state + PIN code. You keep it composite when you sometimes need the whole and sometimes a part, such as searching by city.

              (address)
            /    |     \     \
      (street) (city) (state) (pin)

Single-valued versus multi-valued

A single-valued attribute has one value per entity: a student has one date of birth.

A multi-valued attribute can have several values for one entity: a student can have several phone numbers or email addresses. In a diagram it is a double oval. As you will see, a multi-valued attribute becomes its own table when mapped, because a relational column holds one value.

Stored versus derived

A stored attribute is saved in the database: date_of_birth.

A derived attribute can be computed from other data, so it usually is not stored: age from date_of_birth and today's date; number_of_students in a course from counting enrollments. Storing a derived value risks it going stale; if you store it for speed, you must keep it up to date.

Key attributes

A key attribute has a value that is unique for every entity in the entity set, so it identifies the entity: roll_no for STUDENT, isbn for BOOK. It is underlined in a diagram. An entity type can have more than one key, and a key can be composite (several attributes together). Keys are covered fully in relational model and keys.

Null values

An attribute value can be NULL, meaning "unknown" or "not applicable": a student with no middle name, or a new employee whose phone is not yet known. Key attributes may not be NULL.

Complex attributes

Composite and multi-valued can nest: a person can have several addresses, each composite. Such complex attributes are written with braces and parentheses in some textbooks, for example {Address(street, city, pin)}. They map to a separate table whose columns are the parts.

Putting it together

               (_roll_no_)
                   |
 (first) (last)    |      ((phone))
     \    /        |         |
     (name) ---+----------+--+
               | STUDENT  |
     [age] ----+----------+
                   |
                 (dob)

STUDENT has a key roll_no, a composite name, a multi-valued phone, a stored dob and a derived age.

Relationships

A relationship is an association between entities, such as "Asha enrolls in DBMS". A relationship type (or relationship set) is the set of all such associations of one kind, for example ENROLLS between STUDENT and COURSE.

Relationships can have their own attributes. The grade a student gets in a course belongs neither to the student nor to the course alone; it belongs to the pair. So grade is an attribute of ENROLLS.

+---------+   M      < ENROLLS >      N  +---------+
| STUDENT |----------<        >---------| COURSE  |
+---------+            |                +---------+
                    (grade)

Roles and recursive relationships

Each entity set taking part in a relationship plays a role. Usually the roles are obvious from the entity names. They matter when the same entity set appears twice, which is a recursive (or unary) relationship. Example: an EMPLOYEE supervises other EMPLOYEEs. One side plays "supervisor", the other "subordinate". Another example: a COURSE requires another COURSE as a prerequisite.

           supervisor (1)
        +---------------+
        |               |
  +----------+     < SUPERVISES >
  | EMPLOYEE |          |
  +----------+          |
        |               |
        +---------------+
           subordinate (N)

Degree of a relationship

The degree is the number of entity sets participating.

Most relationships are binary. A ternary relationship is needed when the fact only makes sense with all three together. "Supplier S supplies part P to project J" is not the same as three separate binary facts. Knowing that S supplies P, P is used in J, and S works with J does not tell you that S supplies P to J. If you break a genuine ternary relationship into three binaries, you can no longer reconstruct which triples are true. This is a favorite follow-up question.

Cardinality constraints

The cardinality ratio (or mapping cardinality) of a binary relationship says how many entities on one side can be associated with one entity on the other side.

One-to-one (1:1)

Each entity on either side is linked to at most one entity on the other. Example: a DEPARTMENT is headed by one PROFESSOR, and a professor heads at most one department.

DEPARTMENT                 PROFESSOR
   d1 -------------------- p1
   d2 -------------------- p4
                            p2   (heads nothing)

One-to-many (1:N)

One entity on the "1" side can be linked to many on the "N" side, but each entity on the "N" side links to at most one on the "1" side. Example: a DEPARTMENT employs many PROFESSORs; each professor belongs to one department.

DEPARTMENT                 PROFESSOR
   d1 ---+---------------- p1
         +---------------- p2
   d2 -------------------- p3

Many-to-one (N:1) is the same thing read from the other side.

Many-to-many (M:N)

An entity on either side can link to many on the other. Example: a STUDENT enrolls in many COURSEs and a COURSE has many STUDENTs.

STUDENT                    COURSE
   s1 ---+---------------- c1
         +-------+
   s2 ---+-------+-------- c2
         +---------------- c3

Cardinality is a property of the business rules, not of the data you happen to have today. If the college might one day let a professor head two departments, then HEADS is 1:N, even if currently every professor heads at most one.

Min-max notation

A more precise alternative writes (min, max) on each side: how many relationships each entity must and can take part in. For PROFESSOR in EMPLOYS: (1, 1) means every professor works in exactly one department. For DEPARTMENT in EMPLOYS: (0, N) means a department can have zero or many professors. Min-max captures both cardinality and participation in one notation. Be careful: different books place the pair on opposite sides of the diamond, so state your convention when you use it.

Participation constraints

Participation says whether every entity in an entity set must take part in the relationship.

• Total participation (mandatory, existence dependency): every entity must participate. Drawn with a double line. Example: every PROFESSOR must belong to a DEPARTMENT.
• Partial participation (optional): some entities may not participate. Drawn with a single line. Example: not every PROFESSOR heads a department.

The double line goes on the side of the entity set whose members must all participate. For "every department has exactly one head, and most professors head nothing", it looks like this:

+-----------+ 1           1 +-----------+
|DEPARTMENT |====< HEADS >---| PROFESSOR |
+-----------+               +-----------+
 total: every dept           partial: most profs
 has a head                  head nothing

Participation matters for mapping: total participation on the "N" side of a 1:N relationship lets you declare the foreign key NOT NULL.

Weak entities

A strong entity set has a key of its own. A weak entity set does not: its entities can only be identified by combining some of their own attributes with the key of another entity, called the owner (or identifying) entity.

The classic example is a DEPENDENT of an EMPLOYEE (for insurance). Two employees may each have a dependent called "Ravi", so name alone is not unique. But (employee_id, name) is unique.

Other examples:

• A ROOM in a BUILDING: room "101" exists in many buildings.
• An INSTALLMENT of a LOAN: installment number 3 exists for every loan.
• A SECTION of a COURSE in a semester: section 1 of DBMS in Odd 2026.

Terms you must know:

• The partial key (or discriminator) is the set of the weak entity's own attributes that distinguishes it among the entities of the same owner. It is drawn with a dashed underline. For DEPENDENT it is name.
• The identifying relationship links the weak entity to its owner. It is drawn as a double diamond.
• A weak entity always has total participation in the identifying relationship (it cannot exist without an owner), and the relationship is usually 1:N from owner to weak entity.
• The key of a weak entity = owner's primary key + partial key.

+----------+ 1              N +==============+
| EMPLOYEE |----<< HAS >>=====|| DEPENDENT  ||
+----------+                  +==============+
     |                         |      |
 (_emp_id_)              (-name-)  (relation)
                         partial key

When the owner is deleted, its weak entities lose their meaning, so the mapping uses ON DELETE CASCADE.

Extended ER features

The basic model struggles with type hierarchies, such as "a person is either a doctor, a nurse or a patient, and each kind has extra attributes". The Enhanced ER (EER) model adds three constructs.

Specialization and generalization

Specialization is top-down: start with a general entity set and define sub-groups that have extra attributes or relationships. Start with EMPLOYEE, then define FACULTY (with designation, research_area) and STAFF (with role, shift).

Generalization is bottom-up: notice that CAR and TRUCK share attributes (reg_no, model, price) and pull them into a common VEHICLE superclass.

Both produce the same structure, a superclass/subclass hierarchy, also called an IS-A hierarchy because "a FACULTY is an EMPLOYEE". A subclass inherits every attribute and relationship of its superclass.

                +----------+
                | EMPLOYEE |  emp_id, name, salary
                +----------+
                     |
                   ( d )      d = disjoint, o = overlapping
                 /   ||   \   double line = total
         +---------+ +-------+
         | FACULTY | | STAFF |
         +---------+ +-------+
       designation    role, shift

Two constraints describe a specialization:

• Disjointness: disjoint (d) means an entity belongs to at most one subclass (an employee is faculty or staff, not both). Overlapping (o) means it can belong to several (a PERSON can be both STUDENT and EMPLOYEE, say a teaching assistant).
• Completeness: total means every superclass entity must belong to some subclass (every employee is faculty or staff). Partial means some belong to none (a VEHICLE that is neither CAR nor TRUCK, like a bus).

That gives four combinations: disjoint-total, disjoint-partial, overlapping-total, overlapping-partial.

Aggregation

Plain ER cannot express a relationship about a relationship. Suppose a STUDENT works on a PROJECT, and each such (student, project) pairing is monitored by a PROFESSOR. The professor monitors the pairing, not the student alone or the project alone.

Aggregation treats a relationship together with its participating entities as a higher-level entity, which can then take part in another relationship.

 +--------------------------------------------+
 |  +---------+               +---------+     |
 |  | STUDENT |--< WORKS_ON >-| PROJECT |     |  aggregated
 |  +---------+               +---------+     |  as one unit
 +--------------------------------------------+
                      |
                 < MONITORS >
                      |
                +-----------+
                | PROFESSOR |
                +-----------+

Why not a ternary relationship STUDENT-PROJECT-PROFESSOR instead? Because some (student, project) pairs exist with no monitor. A ternary relationship would force a professor into every triple, or you would have to invent NULL professors. Aggregation keeps WORKS_ON independent and adds monitoring only where it exists.

Categories (union types)

Briefly: a category is a subclass of a union of different superclasses. An OWNER of a vehicle can be a PERSON, a BANK or a COMPANY. It appears in some syllabi; map it with a surrogate key in the category table that each superclass row can point to.

ER-to-relational mapping rules

This is the most testable part of the topic. Each rule says how one ER construct becomes tables. Below, "PK" is primary key and "FK" is foreign key (a column that must match the primary key of another table; see the next lesson).

Rule 1: Strong entity set

Create a table with all simple attributes. For a composite attribute, keep only its simple parts as columns. Choose one key as the PK. Skip derived attributes (or compute them in a view).

STUDENT(roll_no, name(first, last), dob, [age]) becomes:

student(roll_no PK, first_name, last_name, dob)

Rule 2: Weak entity set

Create a table with the weak entity's attributes plus the owner's PK as an FK. The PK is (owner PK, partial key). Use ON DELETE CASCADE on the FK.

dependent(emp_id FK -> employee, name, relation)
          PK (emp_id, name)

Rule 3: Binary 1:1 relationship

Choose one side, preferably the side with total participation, and add the other side's PK as an FK there, with a UNIQUE constraint so it stays one-to-one. Put relationship attributes in the same table.

For DEPARTMENT ==== HEADS ---- PROFESSOR (every department has a head):

department(dept_id PK, name, head_prof_id FK UNIQUE NOT NULL)

Putting the FK on the professor side instead would leave the column NULL for most professors. Alternatives: if both sides are total, you can merge the two entity sets into one table; or you can create a separate relationship table with a UNIQUE constraint on each FK.

Rule 4: Binary 1:N relationship

Put the PK of the "1" side as an FK in the table of the "N" side. Relationship attributes go there too. If the N side has total participation, make the FK NOT NULL.

DEPARTMENT 1 --- EMPLOYS --- N PROFESSOR:

professor(prof_id PK, name, dept_id FK NOT NULL, joined_on)

No extra table is needed. (A separate relationship table also works, and is sometimes used when the relationship is rare and the FK would be mostly NULL.)

Rule 5: Binary M:N relationship

Always create a new table (a junction, bridge or associative table). Its columns are the PKs of both sides as FKs, plus any relationship attributes. Its PK is the combination of the two FKs (when a pair can occur at most once).

STUDENT M --- ENROLLS(grade) --- N COURSE:

enrollment(roll_no FK, course_code FK, grade)
          PK (roll_no, course_code)

You cannot avoid the extra table: putting course_code into student would allow only one course per student, or force repeated student rows.

Rule 6: Multi-valued attribute

Create a new table with the owning entity's PK plus a column for the value. The PK is both columns.

student_phone(roll_no FK, phone)  PK (roll_no, phone)

If the multi-valued attribute is composite, the new table holds all its parts.

Rule 7: n-ary relationship (degree 3 or more)

Create a new table with the PKs of all participating entity sets as FKs, plus relationship attributes. The PK is normally the combination of all FKs. If one participant has cardinality 1 (each pair of the others determines one of it), its FK can be left out of the PK.

SUPPLIES(SUPPLIER, PART, PROJECT, quantity):

supplies(supplier_id FK, part_id FK, project_id FK, quantity)
         PK (supplier_id, part_id, project_id)

Rule 8: Recursive relationship

Same rules as binary, but both FKs point to the same table, with role names as column names.

• 1:N EMPLOYEE supervises EMPLOYEE: add manager_id FK -> employee(emp_id) to employee (NULL for the top boss).
• M:N COURSE requires COURSE: new table prerequisite(course_code FK, prereq_code FK) with PK on both.

Rule 9: Specialization / generalization

There are three common options. For EMPLOYEE with subclasses FACULTY and STAFF:

Option A, one table per class (table-per-type):

employee(emp_id PK, name, salary, emp_type)
faculty(emp_id PK FK -> employee, designation, research_area)
staff(emp_id PK FK -> employee, role, shift)

Works for every combination of constraints. Reading a full faculty record needs a join.

Option B, one table per subclass only (table-per-concrete-class):

faculty(emp_id PK, name, salary, designation, research_area)
staff(emp_id PK, name, salary, role, shift)

Only correct if the specialization is total (otherwise employees in no subclass have nowhere to go) and preferably disjoint (otherwise an overlapping entity is stored twice with duplicated inherited attributes). Querying "all employees" needs a UNION.

Option C, a single table (table-per-hierarchy):

employee(emp_id PK, name, salary, emp_type,
         designation, research_area, role, shift)

No joins and simple queries, but many NULL columns, and subclass attributes cannot be declared NOT NULL without a check constraint tied to emp_type. For overlapping specialization, use one boolean (or flag) column per subclass instead of a single type column.

Rule 10: Aggregation

The aggregated relationship already has a table (by rule 4 or 5). The relationship that uses the aggregate gets a table (or FK) that references the aggregated table's PK.

works_on(roll_no FK, project_id FK)   PK (roll_no, project_id)
monitors(roll_no, project_id, prof_id FK)
         PK (roll_no, project_id)
         FK (roll_no, project_id) -> works_on

The PK of monitors is (roll_no, project_id) because each pair has at most one monitor. If several professors could monitor a pair, add prof_id to the PK.

Summary of mapping rules

Full worked example: a college academic system

Let us go from a written brief to an ER design to tested SQL tables.

Step 1: Read the requirements

The college has departments, each with a unique name and a building. Each professor works in exactly one department. Each department is headed by exactly one professor, and a professor heads at most one department. Students belong to one department and may have a professor as an advisor. A student has a roll number, a name (first and last), a date of birth and one or more phone numbers; we also want to show age. Courses have a code, title and credits, and are offered by a department. A course may have other courses as prerequisites. Each semester, a course is offered in one or more numbered sections; each section has a room and is taught by one professor. Students enroll in sections and receive a grade.

Step 2: Find entities, attributes and keys

Nouns usually become entities or attributes. Ask: "Do we store several facts about it, and does it have its own identity?" If yes, it is an entity.

Why is SECTION weak? "Section 1" is meaningless on its own; it is section 1 of DBMS in Odd 2026. Its identity depends on its course.

Step 3: Find relationships, cardinality and participation

Verbs usually become relationships.

Step 4: Draw the ER diagram

             1 +------------+ 1
   +---------==| DEPARTMENT |==--------+
   | HEADS     +------------+  OFFERS  |
   | (1:1)      1 |      | 1   (1:N)   |
   |    WORKS_IN  |      | BELONGS_TO  |
   |       N      |      |    N        | N
+-----------+     |      |      +-----------+
| PROFESSOR |=====+      +=====| STUDENT   |
+-----------+                   +-----------+
  1 |     1 |  ADVISES (1:N)     N |  |  ((phone))
    |       +--------------------- +  |  [age]
    | TEACHES                         |
    | (1:N)                           | ENROLLS(grade)
    |     N +==============+   N   M  |  (M:N)
    +-----==|| SECTION    ||==--------+
            +==============+
                  || N
            << HAS_SECTION >>
                  | 1
            +------------+  REQUIRES (M:N,
            |   COURSE   |--+  recursive)
            +------------+--+

Double lines mark total participation. Text diagrams get crowded; in an interview, draw the entities first, then add relationships one at a time, writing the cardinality on each.

Step 5: Apply the mapping rules

1. Strong entities DEPARTMENT, PROFESSOR, STUDENT, COURSE: four tables (rule 1). Name splits into first_name, last_name; age is derived, so not stored.
2. Weak entity SECTION: table with course_code FK; PK (course_code, sec_no, semester, year) (rule 2).
3. Multi-valued phone: table student_phone (rule 6).
4. HEADS (1:1, department total): head_prof_id UNIQUE in department (rule 3).
5. WORKS_IN, BELONGS_TO, OFFERS (N:1, total): NOT NULL FKs professor.dept_id, student.dept_id, course.dept_id (rule 4).
6. ADVISES (1:N, partial): nullable FK student.advisor_id (rule 4).
7. TEACHES (1:N, section total): NOT NULL FK section.prof_id (rule 4).
8. ENROLLS (M:N with grade): junction table enrollment (rule 5).
9. REQUIRES (recursive M:N): junction table prerequisite (rule 8).

That is 9 tables: 4 strong entities + 1 weak entity + 1 multi-valued attribute + 2 M:N relationships + 1 recursive M:N. Every 1:1 and 1:N relationship became a foreign key column.

Step 6: Write the SQL

The following was run in SQLite with PRAGMA foreign_keys = ON; (SQLite does not enforce foreign keys unless you turn this on for each connection).

PRAGMA foreign_keys = ON;

CREATE TABLE department (
  dept_id      INTEGER PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE,
  building     TEXT,
  head_prof_id INTEGER UNIQUE REFERENCES professor(prof_id)
);

CREATE TABLE professor (
  prof_id    INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  dob        DATE NOT NULL,
  dept_id    INTEGER NOT NULL REFERENCES department(dept_id)
);

CREATE TABLE student (
  roll_no    INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  dob        DATE NOT NULL,
  dept_id    INTEGER NOT NULL REFERENCES department(dept_id),
  advisor_id INTEGER REFERENCES professor(prof_id)
);

CREATE TABLE student_phone (
  roll_no INTEGER NOT NULL REFERENCES student(roll_no) ON DELETE CASCADE,
  phone   TEXT    NOT NULL,
  PRIMARY KEY (roll_no, phone)
);

CREATE TABLE course (
  course_code TEXT PRIMARY KEY,
  title       TEXT NOT NULL,
  credits     INTEGER NOT NULL CHECK (credits BETWEEN 1 AND 6),
  dept_id     INTEGER NOT NULL REFERENCES department(dept_id)
);

CREATE TABLE section (
  course_code TEXT    NOT NULL REFERENCES course(course_code) ON DELETE CASCADE,
  sec_no      INTEGER NOT NULL,
  semester    TEXT    NOT NULL,
  year        INTEGER NOT NULL,
  room        TEXT,
  prof_id     INTEGER NOT NULL REFERENCES professor(prof_id),
  PRIMARY KEY (course_code, sec_no, semester, year)
);

CREATE TABLE enrollment (
  roll_no     INTEGER NOT NULL REFERENCES student(roll_no),
  course_code TEXT    NOT NULL,
  sec_no      INTEGER NOT NULL,
  semester    TEXT    NOT NULL,
  year        INTEGER NOT NULL,
  grade       TEXT,
  PRIMARY KEY (roll_no, course_code, sec_no, semester, year),
  FOREIGN KEY (course_code, sec_no, semester, year)
    REFERENCES section (course_code, sec_no, semester, year)
);

CREATE TABLE prerequisite (
  course_code TEXT NOT NULL REFERENCES course(course_code),
  prereq_code TEXT NOT NULL REFERENCES course(course_code),
  PRIMARY KEY (course_code, prereq_code),
  CHECK (course_code <> prereq_code)
);

Notes on choices that are not forced by the rules:

• department.head_prof_id is nullable here even though HEADS is total for departments. There is a chicken-and-egg problem: a department must exist before its first professor, and the professor must exist before being made head. You insert the department, then the professor, then set the head. In PostgreSQL you could make the column NOT NULL and use a DEFERRABLE INITIALLY DEFERRED foreign key so the check runs at commit time; SQLite and MySQL handle this differently. This is a good example of a conceptual rule that needs care in the physical design.
• SQLite accepts the reference to professor before that table exists, because it checks foreign keys when rows are written. In PostgreSQL or MySQL, create both tables first and add that foreign key afterwards with ALTER TABLE department ADD FOREIGN KEY (head_prof_id) REFERENCES professor(prof_id);.
• Age is not stored. Compute it when needed, for example in SQLite: CAST((julianday('now') - julianday(dob)) / 365.25 AS INTEGER).

Step 7: Try it

INSERT INTO department (dept_id, name, building) VALUES (1, 'CSE', 'Block A');
INSERT INTO professor VALUES (10, 'Anil', 'Rao', '1975-03-02', 1);
UPDATE department SET head_prof_id = 10 WHERE dept_id = 1;
INSERT INTO student VALUES (101, 'Asha', 'K', '2004-05-01', 1, 10);
INSERT INTO student_phone VALUES (101, '98450 11111'), (101, '98450 22222');
INSERT INTO course VALUES ('CS201', 'DBMS', 4, 1), ('CS101', 'Programming', 4, 1);
INSERT INTO prerequisite VALUES ('CS201', 'CS101');
INSERT INTO section VALUES ('CS201', 1, 'Odd', 2026, 'A-101', 10);
INSERT INTO enrollment VALUES (101, 'CS201', 1, 'Odd', 2026, NULL);

SELECT s.first_name, c.title, sec.room, p.last_name AS taught_by
FROM enrollment e
JOIN student s    ON s.roll_no = e.roll_no
JOIN section sec  ON sec.course_code = e.course_code
                 AND sec.sec_no = e.sec_no
                 AND sec.semester = e.semester
                 AND sec.year = e.year
JOIN course c     ON c.course_code = e.course_code
JOIN professor p  ON p.prof_id = sec.prof_id;

first_name | title | room  | taught_by
-----------+-------+-------+----------
Asha       | DBMS  | A-101 | Rao

If you now run DELETE FROM section WHERE course_code = 'CS201';, SQLite refuses with FOREIGN KEY constraint failed, because an enrollment still refers to that section. The design protects you from orphan rows.

Common design decisions

Entity or attribute?

Make something an entity when it has its own attributes, its own identity, or relationships to other things. "Department" stored as a text column in student cannot hold a building or a head; as an entity it can. Conversely, do not make color an entity unless you need to store facts about colors.

Entity or relationship?

If a "thing" exists only to connect two others and has its own attributes, model it as a relationship with attributes; but if it has its own identity used elsewhere (for example an ORDER that has line items, a payment and a delivery), it is an entity. A useful test: does anything else need to point at it?

Binary or ternary?

Use a ternary relationship only when the fact genuinely involves three participants at once, as in supplier-part-project. If the fact is really two independent binary facts, a ternary relationship creates redundancy.

Where to put relationship attributes?

For M:N, always on the relationship (junction table). For 1:N, they can move to the N-side entity, because each N-side entity takes part at most once. For example, joined_on for WORKS_IN can sit in the professor table.

A second quick example: hospital

Requirements in one paragraph: patients (id, name, dob, phones) are treated by doctors (id, name, specialization); a doctor treats many patients and a patient may see many doctors, each visit having a date and diagnosis. Each patient admission is to a ward bed; beds are numbered within a ward. Doctors and nurses are both staff with a staff id, name and salary.

Design highlights:

• STAFF generalizes DOCTOR and NURSE (disjoint, total). Using option A: staff, doctor(staff_id PK FK, specialization), nurse(staff_id PK FK, shift).
• VISIT between PATIENT and DOCTOR is M:N with attributes. Because the same patient can see the same doctor many times, (patient_id, doctor_id) is not unique; include visit_date in the PK or, more simply, give visit a surrogate visit_id and treat it as an entity.
• BED is weak, owned by WARD; PK (ward_id, bed_no).
• ADMISSION links PATIENT to BED with admitted_on and discharged_on.
• phones is multi-valued: patient_phone(patient_id, phone).

This kind of quick sketch, with one reason per decision, is what an interviewer wants to hear.

Interview questions

Q1. What is the difference between an entity, an entity type and an entity set?

An entity is one distinct thing, such as the student Asha. An entity type is the description shared by a group of similar entities: its name and attributes, such as STUDENT with roll number, name and date of birth. An entity set is the collection of all entities of that type stored at a given time. When mapped, the type becomes a table definition, the set becomes the table's rows, and each entity is one row.

Q2. Explain the types of attributes with examples.

Simple attributes cannot be divided, like roll number; composite ones have meaningful parts, like address made of street, city and PIN. Single-valued attributes hold one value, like date of birth; multi-valued ones hold several, like phone numbers. Stored attributes are saved, while derived ones are computed, like age from date of birth. Key attributes uniquely identify an entity, like roll number.

Q3. What is a weak entity? Give an example.

A weak entity has no key of its own and can only be identified by combining its partial key with the key of an owner entity. A dependent of an employee is the standard example: dependents' names are unique only within one employee. A weak entity has total participation in an identifying relationship with its owner, and its table's primary key is the owner's key plus the partial key, usually with ON DELETE CASCADE.

Q4. What is the difference between cardinality and participation?

Cardinality gives the maximum number of related entities: one or many, as in 1:1, 1:N or M:N. Participation gives the minimum: total means every entity must take part (at least one), partial means some may not. For example, every order belongs to exactly one customer (N:1, total for orders), while a customer may have zero orders (partial).

Q5. How do you map a many-to-many relationship to tables?

Create a separate junction table containing the primary keys of both participating tables as foreign keys, plus any relationship attributes. Its primary key is usually the pair of foreign keys. For students and courses with a grade, that is enrollment(roll_no, course_code, grade) with primary key (roll_no, course_code). You cannot store an M:N relationship as a single foreign key column on either side.

Q6. How do you map a one-to-one relationship?

Add the primary key of one side as a foreign key in the other side's table, with a UNIQUE constraint to enforce one-to-one. Prefer the side with total participation so the column is never NULL. If both sides have total participation, you can merge the two entities into one table. Relationship attributes go wherever the foreign key goes.

Q7. Why can a ternary relationship not always be replaced by three binary relationships?

Three binary relationships record only pairs, so they lose which triples actually occurred. If supplier S1 supplies part P1, P1 is used in project J1, and S1 supplies something to J1, you still cannot conclude S1 supplies P1 to J1. A ternary relationship records the exact triple. Replace it with binaries only when the fact really is a set of independent pairwise facts.

Q8. What are generalization and specialization?

Both create a superclass-subclass (IS-A) hierarchy. Specialization is top-down: split a general entity, such as employee, into subclasses like faculty and staff that have extra attributes. Generalization is bottom-up: combine entities with shared attributes, such as car and truck, into a superclass like vehicle. Subclasses inherit all superclass attributes and relationships.

Q9. Explain disjoint versus overlapping and total versus partial specialization.

Disjoint means an entity can belong to at most one subclass; overlapping means it can belong to several, like a person who is both student and employee. Total means every superclass entity must belong to at least one subclass; partial means some may belong to none. These constraints decide which mapping option is valid, for example subclass-only tables require total specialization.

Q10. What is aggregation and when do you need it?

Aggregation treats a relationship and its entities as a single higher-level entity so that it can participate in another relationship. You need it when a relationship is about another relationship, such as a professor monitoring a particular student-project assignment. A ternary relationship would force a professor for every pair, while aggregation lets monitoring exist only for some pairs.

Q11. What are the options for mapping a specialization hierarchy to tables?

One table per class, with subclass tables sharing the superclass key, works for any constraints but needs joins. Tables only for subclasses, each with inherited columns, avoids joins but needs total and ideally disjoint specialization. A single table with a type column and all attributes needs no joins but has many NULLs and weaker constraints. The choice depends on query patterns and how different the subclasses are.

Q12. What is a recursive relationship and how is it mapped?

It is a relationship between entities of the same type, with each side playing a different role, such as an employee supervising other employees. A 1:N recursive relationship maps to a foreign key in the same table, like manager_id referencing employee(emp_id). An M:N recursive relationship, like course prerequisites, needs a junction table with two foreign keys to the same table.

Q13. What is the minimum number of tables for an ER diagram with two strong entities joined by an M:N relationship, where one entity has a multi-valued attribute?

Four. One table per strong entity, one junction table for the M:N relationship, and one table for the multi-valued attribute. If the relationship were 1:N instead, it would become a foreign key and the answer would be three.

Q14. Should derived attributes be stored?

Usually not, because they can be computed and storing them risks inconsistency when the source changes. Age, for example, changes every day while date of birth does not. Store a derived value only for a proven performance need, and then keep it correct with triggers, application code or a materialized view.

Key takeaways

• The ER model describes a system as entities, attributes and relationships before any tables exist.
• Attributes are simple or composite, single- or multi-valued, stored or derived, and key or non-key.
• Cardinality (1:1, 1:N, M:N) is the maximum; participation (total, partial) is the minimum.
• A weak entity is identified by its owner's key plus its partial key and depends on the owner for existence.
• EER adds specialization, generalization (IS-A hierarchies with disjoint/overlapping and total/partial constraints) and aggregation.
• Mapping: entities and weak entities become tables; 1:1 and 1:N become foreign keys; M:N, n-ary relationships and multi-valued attributes become new tables.
• Ternary relationships hold facts that three binaries cannot reconstruct.
• Explain every design decision with the business rule behind it.

Next lesson

Continue with The relational model and keys.