ER Model and ER Diagram Interview Questions

The entity-relationship model designs a database before the tables: the entities, their attributes and the relationships between them. In Chen notation a rectangle is an entity (double for weak), an ellipse an attribute (underlined for the key, double for multivalued, dashed for derived), a diamond a relationship (double for identifying), and a double line total participation.

Three independent splits: simple or composite (roll_no vs address with street and city; a composite gets one column per part), single-valued or multivalued (date_of_birth vs phone_numbers, which needs its own table) and stored or derived (date_of_birth vs age, computed on read). Store the birth date, not the age: age changes every year without a write.

Cardinality is how many entities on one side relate to one on the other: 1:1 (person and passport), 1:N (department and employees), M:N (students and courses). Participation is total (double line: every employee works in a department) or partial (single line: not every employee manages one). In tables, total participation on the many side becomes a NOT NULL foreign key, and partial a nullable one.

A weak entity cannot be identified by its own attributes; it needs its owner's key plus a partial key, and it cannot exist without the owner. An order line is one: line 1 appears in every order.

PRAGMA foreign_keys = ON;
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer TEXT);
CREATE TABLE order_lines (
    order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    line_no  INTEGER,
    product  TEXT,
    qty      INTEGER,
    PRIMARY KEY (order_id, line_no)
);
INSERT INTO orders VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO order_lines VALUES (1, 1, 'pen', 2), (1, 2, 'book', 1), (2, 1, 'pen', 5);
DELETE FROM orders WHERE order_id = 1;
SELECT order_id, line_no, product FROM order_lines;

The key is (order_id, line_no), and ON DELETE CASCADE encodes "no line without its order", so deleting order 1 removes its two lines. Chen notation draws it as a double rectangle.

Each strong entity becomes a table. A 1:N relationship becomes a foreign key on the N side, a 1:1 a foreign key with UNIQUE, and an M:N a junction table keyed by both foreign keys. A multivalued attribute gets its own table, a composite one a column per part, and a weak entity a key of owner key plus partial key.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses  (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE enrolls (
    student_id INTEGER REFERENCES students(id),
    course_id  INTEGER REFERENCES courses(id),
    enrolled_on TEXT,
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO courses  VALUES (10, 'DBMS'), (20, 'OS');
INSERT INTO enrolls VALUES (1, 10, '2026-07-01'), (1, 20, '2026-07-02'), (2, 10, '2026-07-03');

SELECT c.title, count(*) AS students
FROM enrolls e JOIN courses c ON c.id = e.course_id
GROUP BY c.title ORDER BY c.title;

Attributes of the relationship, like enrolled_on, go in the junction table because they describe the pair.

Specialization splits an entity into subtypes (Employee into Engineer and Manager); generalization merges entities into a supertype (Car and Truck into Vehicle). A hierarchy is disjoint or overlapping, and total or partial. Aggregation treats a relationship as an entity, so a manager can monitor one works_on assignment. To map a hierarchy, use one table with a type column (many NULLs), a table per type sharing the key (one join per read), or a table per concrete subclass (UNION for "all employees").

An entity is one real thing, such as the student Asha. An entity type is the definition entities share: Student with roll_no and name. An entity set is all entities of that type stored at a moment. In tables, the entity type is the table's schema, an entity is a row, and the entity set is the rows present now. Relationships split the same way: one enrollment is a relationship, all enrollments form a relationship set.

The ER model is a conceptual design: entities, attributes and relationships drawn as a diagram, with no tables yet. The relational model is the logical design a DBMS implements: relations (tables) of tuples with primary and foreign keys, queried with relational algebra or SQL. You design in ER, then map it to tables. Some ER ideas need translating because a table cannot hold them directly: a multivalued attribute becomes its own table, and an M:N relationship a junction table.

A recursive relationship links an entity type to itself, each side playing a different role: an employee manages other employees. A 1:N recursive relationship becomes a foreign key pointing at the same table.

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER REFERENCES employees(id)   -- the "manages" relationship
);
INSERT INTO employees VALUES (1, 'Asha', NULL), (2, 'Ravi', 1), (3, 'Meera', 1), (4, 'John', 2);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

Ravi and Meera report to Asha and John reports to Ravi. Asha's manager is NULL because she has none, so the column must allow NULL. An M:N recursive relationship, such as course prerequisites, becomes a junction table with two foreign keys to the same table.

The degree is the number of entity types taking part: unary, binary or ternary. A ternary relationship usually cannot be split into binary ones without losing facts, because pairs do not record which combinations happened together.

CREATE TABLE supplies (
    supplier TEXT, part TEXT, project TEXT,
    PRIMARY KEY (supplier, part, project)
);
INSERT INTO supplies VALUES ('S1', 'bolt', 'Bridge'), ('S1', 'nut', 'Tower'), ('S2', 'bolt', 'Tower');
-- try to rebuild it from three binary relationships
WITH sp AS (SELECT DISTINCT supplier, part FROM supplies),
     pj AS (SELECT DISTINCT part, project FROM supplies),
     sj AS (SELECT DISTINCT supplier, project FROM supplies)
SELECT sp.supplier, sp.part, pj.project
FROM sp
JOIN pj ON pj.part = sp.part
JOIN sj ON sj.supplier = sp.supplier AND sj.project = pj.project
ORDER BY sp.supplier, sp.part, pj.project;

The table holds three facts, but rebuilding it from the supplier and part, part and project, and supplier and project pairs returns four rows: S1 never supplied bolts to Tower. Map a ternary relationship to one table with three foreign keys.