Autocoder

ER Model Interview Questions

Last updated 2026-11-19

ER diagram questions in interviews come in two shapes: definitions (weak entity, participation, specialization) and a short design task, such as modelling a library, hospital or online shop and then converting the diagram to tables. This page covers both, with the mapping rules written out so you can apply them without a textbook.

Mapping ends in relational tables, so normalization is the natural next check, and DBMS keys explains the keys used below. For the generic revision plan, see how to crack a DBMS interview.

Core symbolsEntity, attribute, relationship, cardinality
ExtensionsWeak entity, specialization, aggregation
OutputRelational schema by mapping rules

Mapping rules in one place

Design walk-through: hospital appointments

Entities: Patient(patient_id, name, dob, phones multivalued), Doctor(doctor_id, name, specialization) and Department(dept_id, name). A doctor belongs to exactly one department (many-to-one). A patient books appointments with doctors, and a patient can see many doctors many times, so Appointment is a many-to-many relationship with attributes date_time and status. Because the same patient and doctor can meet repeatedly, (patient_id, doctor_id) cannot be the key; add appointment_id, or include date_time in the key.

The resulting tables are patient, patient_phone(patient_id, phone), department, doctor(doctor_id, dept_id, ...) and appointment(appointment_id, patient_id, doctor_id, date_time, status). Four foreign keys carry all the relationships.

ER Model Interview Questions: questions with worked answers

  1. 1

    What is an ER model and what are its basic building blocks?

    easy

    The entity-relationship model is a conceptual way to describe data before choosing a database. An entity is a distinguishable thing such as Student; an attribute is a property such as name; a relationship associates entities, such as Student enrols in Course. Attributes can be simple or composite, single-valued or multivalued, stored or derived. A key attribute uniquely identifies an entity, drawn underlined. The diagram is then translated into tables.

  2. 2

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

    easy

    An entity is one specific object, such as the student with roll number 21. An entity set is the collection of all entities of one type present at a point in time. An entity type is the schema description: its name and attributes. In diagrams a rectangle represents the entity type or set, and the distinction matters mainly for textbook definitions.

  3. 3

    Explain cardinality ratios with examples.

    easy

    One-to-one: each person has at most one passport and each passport belongs to one person. One-to-many: one department has many employees, each employee has one department. Many-to-one is the same seen from the other end. Many-to-many: students and courses, since a student takes many courses and a course has many students. Cardinality decides where foreign keys go during mapping; many-to-many always needs a junction table.

  4. 4

    What are total and partial participation?

    medium

    Total participation, drawn with a double line, means every entity in the set must take part in the relationship: every employee must belong to a department. Partial participation means some entities may not: not every employee manages a department. Total participation on the many side of a one-to-many relationship lets you declare the foreign key NOT NULL, enforcing the rule in the schema.

  5. 5

    What is a weak entity? How is it mapped to a table?

    medium

    A weak entity has no key of its own and exists only in relation to an owner entity, linked through an identifying relationship with total participation. It has a partial key (discriminator) that is unique only within one owner. Example: Dependent(name) of Employee. Mapping gives dependent(emp_id, name, relation) with primary key (emp_id, name) and emp_id as a foreign key with ON DELETE CASCADE, because a dependent cannot outlive its employee.

    sql
    CREATE TABLE dependent (
      emp_id   INT REFERENCES employee(emp_id) ON DELETE CASCADE,
      name     VARCHAR(60),
      relation VARCHAR(20),
      PRIMARY KEY (emp_id, name)
    );
  6. 6

    How do you map a many-to-many relationship with attributes?

    medium

    Create a table whose columns are the primary keys of both entities and the relationship's own attributes. For enrolment between student and course with a grade attribute: enrolment(student_id, course_id, grade), primary key (student_id, course_id), both columns foreign keys. If a student can retake a course, the pair is no longer unique, so add a term column to the key or use a surrogate key.

  7. 7

    How do you map a multivalued attribute such as phone numbers?

    medium

    Create a separate table with the owner's primary key and the attribute value as a composite key: person_phone(person_id, phone). Storing a comma-separated list in one column breaks 1NF and makes searching by number inefficient. For composite attributes such as address, either flatten into street, city and pin columns or keep them in one table; derived attributes such as age are computed from date of birth rather than stored.

  8. 8

    Explain generalization, specialization and the three ways to map them.

    medium

    Specialization splits an entity into sub-types, such as Employee into Engineer and Manager; generalization is the reverse, combining entities with common attributes. Constraints are disjoint or overlapping, and total or partial. Mapping options: one table per entity with the subtype table keyed by the parent key (flexible, needs joins); one table per concrete subtype with all attributes repeated (no joins, awkward for parent-level queries); or one single table with a type column and nullable subtype columns (fast, but many NULLs).

  9. 9

    What is aggregation in the ER model, and when do you need it?

    hard

    A plain ER relationship cannot relate another relationship to an entity. Aggregation treats a relationship set as an abstract entity so it can take part in a higher-level relationship. Example: Employee works on Project, and the manager monitors that works-on assignment. Monitoring links Manager to the whole (Employee, Project) pair, not to employee or project alone. Mapped, monitor gets a foreign key to the works_on composite key.

  10. 10

    How do you model a recursive (unary) relationship such as employee and manager?

    medium

    The same entity set plays two roles. One employee manages many employees, so it is one-to-many: add manager_id to employee as a foreign key referencing emp_id, nullable because the top manager has none. For a many-to-many unary relationship such as prerequisite courses, use a junction table prerequisite(course_id, prereq_id) with both columns referencing course.

  11. 11

    Design a library schema from an ER diagram and list the tables and keys.

    hard

    Entities: Member(member_id, name, email), Book(isbn, title, publisher_id), Copy(isbn, copy_no, shelf) as a weak entity of Book, Author, Publisher. Relationships: Book written-by Author is many-to-many, so book_author(isbn, author_id). Publisher publishes Book is one-to-many, so publisher_id in book. Member borrows Copy is many-to-many over time with attributes borrow_date and return_date, so loan(member_id, isbn, copy_no, borrow_date, return_date). The primary key of loan is (isbn, copy_no, borrow_date), since a copy cannot be lent twice on the same date; member_id is a plain foreign key. That gives seven tables.

  12. 12

    How does a ternary relationship differ from three binary relationships?

    hard

    A ternary relationship such as Supplier supplies Part to Project records a fact that involves all three together: supplier S supplies part P to project J. Three binary relationships (supplier-part, part-project, supplier-project) would lose that, because knowing each pair exists does not tell you the triple exists. Map the ternary relationship to one table with the three keys as its primary key, unless a participation constraint (an arrow) says one entity is determined by the other two, in which case it drops out of the key.

  13. 13

    Natural key or surrogate key for an entity?

    medium

    A natural key is a real-world identifier such as an ISBN or email address; a surrogate key is a meaningless generated number. Natural keys can change (email), be reused or be long, which then propagates into every foreign key. Surrogate keys are stable and compact, but they do not stop duplicate real-world entities, so keep a UNIQUE constraint on the natural key as well.

Frequently asked questions

Is the ER model the same as a relational schema?

No. The ER model is conceptual and independent of the DBMS. A relational schema is the implementation after applying mapping rules.

Do I need to know Chen and crow's foot notation?

Know the Chen notation used in textbooks, and recognise crow's foot, which most modelling tools use. Interviewers care more about correct cardinality than symbols.

How many tables does a many-to-many relationship add?

One. The junction table, with both entities' keys as its primary key and any relationship attributes as columns.

Where does ER design fit in low-level design rounds?

It overlaps when you design persistence for a system; see low-level design questions.

Related guides

More in CS Fundamentals Interview Questions

See all in this section