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 symbols | Entity, attribute, relationship, cardinality |
|---|---|
| Extensions | Weak entity, specialization, aggregation |
| Output | Relational schema by mapping rules |
Mapping rules in one place
- Strong entity: one table, with its key as primary key. Composite attributes are flattened to their parts. Derived attributes are not stored.
- Multivalued attribute: a separate table holding the owner's key plus the value.
- One-to-many: put the key of the one side as a foreign key in the many side.
- Many-to-many: a new table with both keys as a composite primary key, plus any relationship attributes.
- One-to-one: a foreign key on either side, preferably the side with total participation, with a UNIQUE constraint.
- Weak entity: a table whose primary key is the owner's key plus the partial key.
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 What is an ER model and what are its basic building blocks?
easyThe 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 What is the difference between an entity, an entity set and an entity type?
easyAn 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 Explain cardinality ratios with examples.
easyOne-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 What are total and partial participation?
mediumTotal 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 What is a weak entity? How is it mapped to a table?
mediumA 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.
sqlCREATE TABLE dependent ( emp_id INT REFERENCES employee(emp_id) ON DELETE CASCADE, name VARCHAR(60), relation VARCHAR(20), PRIMARY KEY (emp_id, name) );6 How do you map a many-to-many relationship with attributes?
mediumCreate 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 How do you map a multivalued attribute such as phone numbers?
mediumCreate 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 Explain generalization, specialization and the three ways to map them.
mediumSpecialization 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 What is aggregation in the ER model, and when do you need it?
hardA 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 How do you model a recursive (unary) relationship such as employee and manager?
mediumThe 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 Design a library schema from an ER diagram and list the tables and keys.
hardEntities: 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 How does a ternary relationship differ from three binary relationships?
hardA 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 Natural key or surrogate key for an entity?
mediumA 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
- File System Interview Questions: Inodes, Allocation and Disk Scheduling
- Git Interview Questions and Answers with Commands
- HTTP and HTTPS Interview Questions: Methods, Status Codes, TLS and Caching
- Inheritance and Polymorphism Interview Questions
- IP Addressing and Subnetting Interview Questions with Worked Solutions
- Linux Commands Interview Questions with Examples
- Linux Interview Questions and Answers: Concepts, Permissions and Processes
- Load Balancing Interview Questions and Answers
- Low-Level Design Interview Questions with Code
- Memory Management Interview Questions with Numericals
- Message Queue and Kafka Interview Questions
- Microservices Interview Questions and Answers