Entity-Relationship (ER) Diagrams and Schema Design: 20 Real Interview Questions

Entity-Relationship (ER) model fundamentals provide a visual blueprint for database design before implementation. An ER diagram shows entities (tables), their attributes (columns), and relationships (foreign keys) in a standardized notation. Mastering ER models allows architects to design scalable, maintainable databases that grow with business needs.

Understanding Entity-Relationship Model Fundamentals

The ER model fundamentals answer three critical questions: What data does the system need? How does that data relate? How do entities depend on each other? Developed by Peter Chen in 1976, ER modeling bridges the gap between business requirements and database implementation, making it essential for database architects and senior engineers.

Entities and Attributes: The Building Blocks

ER model fundamentals start with entities—objects of interest (Customer, Order, Product). Each entity has attributes describing it (Customer name, email, phone). Attributes can be: simple (single value), composite (address = street + city + zip), derived (age calculated from birth_date), or multi-valued (phone numbers). Understanding attribute types guides normalization decisions.

Cardinality and Relationships

ER model fundamentals include relationship cardinality: 1:1 (one customer has one primary address), 1:N (one customer has many orders), M:N (many students enroll in many courses). Crows foot notation shows cardinality visually. Understanding cardinality prevents design flaws: misidentifying M:N as 1:N causes data loss or impossible constraints.

Primary Keys and Foreign Keys

ER model fundamentals require identifying primary keys (unique identifier for each entity) and foreign keys (references to other entities). Composite keys (student_id + semester) solve many-to-many relationships. Natural keys (email) vs surrogate keys (auto-increment id) involve trade-offs: natural keys are meaningful but change; surrogate keys are stable but arbitrary.

Weak Entities and Identifying Relationships

ER model fundamentals include weak entities—entities that cannot exist without a parent (Order_Item cannot exist without Order). Identifying relationships connect weak to strong entities. These require careful attention: deletion of parent cascades to weak children.

Inheritance and Specialization

ER model fundamentals include entity inheritance: Employee specializes into Manager and Individual Contributor. Design choices: shared primary key (same id in employee and manager tables), or surrogate key (separate ids). Each approach has trade-offs for queries and constraints.

5 Complete Interview Questions with Solutions

Question 1: Identifying Entities and Relationships

Design an ER model for a university: students, courses, professors, departments. Identify entities, attributes, and relationships with cardinality.

Entities: Students, Courses, Professors, Departments

Student (student_id, name, email, enrollment_date)
Course (course_id, title, credits, department_id)
Professor (professor_id, name, department_id)
Department (department_id, name, budget)

Relationships (Cardinality):
Student ENROLLS_IN Course (M:N) → junction table
Course TAUGHT_BY Professor (1:N)
Course BELONGS_TO Department (M:1)
Professor WORKS_IN Department (M:1)

Explanation: Identify what entities the system needs. Cardinality: students take multiple courses, courses have multiple students (M:N requires junction table). ER model fundamentals: one professor teaches multiple courses, but each course has one primary professor. Design before coding prevents structural mistakes.

Google – Senior Database ArchitectMicrosoft – Data ArchitectAmazon – Senior Data Engineer

Question 2: M:N Relationships and Junction Tables

A student enrolls in multiple courses, and each course has multiple students. Why cant you store course_id directly in the Student table, and how does a junction table solve this?

-- Wrong: Cant store multiple course_ids
Student (student_id, name, course_id_1, course_id_2, ...)
-- Repeating groups violate 1NF

-- Correct: Junction table
Student (student_id, name)
Course (course_id, title)
Enrollment (student_id, course_id) -- Primary key = (student_id, course_id)
Can add enrollment_date, grade here

Explanation: M:N relationships cant be represented directly—one table would need repeating columns. Junction table (Enrollment) stores the relationship. ER model fundamentals: junction tables have composite primary keys from both parent entities. Optional attributes (grade) go in junction table.

Meta – Senior Database EngineerOracle – Data ArchitectIBM – Senior DBA

Question 3: Weak Entities and Identifying Relationships

An Order_Item cannot exist without an Order. Why is Order_Item a weak entity, and how does this affect cascading delete operations?

Order (order_id, customer_id, order_date)
Order_Item (order_id, item_sequence, product_id, quantity)
-- Composite key: (order_id, item_sequence)
-- order_id is foreign key AND part of primary key
-- This makes Order_Item weak (depends on Order)

Foreign key constraint: ON DELETE CASCADE
-- Deleting Order automatically deletes Order_Items

Explanation: Weak entities depend on parent entities for existence. Order_Item has no meaning without Order. Identifying relationships use parents key as part of weak entitys key. ER model fundamentals: weak entities simplify queries (no orphaned records) but require cascade delete policies.

Stripe – Senior Data EngineerLinkedIn – Database Architect

Question 4: Natural Keys vs Surrogate Keys

Should you use email (natural key) or user_id (surrogate key) as the primary key for Users table? What are the trade-offs?

-- Natural Key (email)
Users (email PRIMARY KEY, name, ...)
Advantage: Meaningful, no joins needed for email lookups
Disadvantage: Email can change, foreign keys are long

-- Surrogate Key (user_id)
Users (user_id PRIMARY KEY, email UNIQUE, name, ...)
Advantage: Stable, short, efficient
Disadvantage: Less meaningful, requires index on email for lookups

Explanation: Natural keys are intuitive but volatile. Surrogate keys are stable but arbitrary. ER model fundamentals: most databases use surrogate keys for PKs, but unique constraints on natural keys (email UNIQUE). Best practice: surrogate PK + natural key unique constraint.

Google – Senior Data ArchitectAmazon – Database Engineer

Question 5: Entity Inheritance in ER Models

Design an ER model where Employee specializes into FullTimeEmployee and ContractEmployee. Compare table-per-type vs single-table inheritance approaches.

-- Table-per-type (Separate tables)
Employee (employee_id, name)
FullTimeEmployee (employee_id, salary, benefits)
ContractEmployee (employee_id, hourly_rate, contract_end_date)

-- Single-table inheritance (One table)
Employee (employee_id, name, employee_type, salary, benefits, 
  hourly_rate, contract_end_date)
Where employee_type = FT or CT
Use CHECK constraint to validate nullable columns

Explanation: Table-per-type queries are type-specific but require joins. Single-table has nullable columns and needs CHECK constraints. ER model fundamentals: inheritance choice depends on frequency of type-specific vs generic queries. Shared primary key inheritance also exists (less common).

Microsoft – Senior Database ArchitectMeta – Data Engineer

15 Practice Questions (Test Yourself)

Question 6: When designing an ER model, how would you distinguish between an attribute that should be its own entity versus a simple attribute, such as deciding whether department should be an entity or just a column in the employee table?

Google – Senior Data EngineerAmazon – Database Architect

Question 7: In an ER diagram, if two entities have a 1:1 relationship, where should you place the foreign key—in the first table, the second, or both—and what factors determine your decision?

Microsoft – Senior Database EngineerMeta – Data Architect

Question 8: When a many-to-many relationship needs to store additional attributes like a students grade in a course, where would you add those attributes in a junction table, and why is this the correct location?

Oracle – Senior Data ArchitectIBM – Database EngineerLinkedIn – Senior DBA

Question 9: In designing an ER model for a social network where users follow other users, would you use a direct M:N relationship or create a separate Follow entity, and what are the advantages of each approach?

Stripe – Senior Data EngineerGoogle – Database Engineer

Question 10: When defining a composite primary key in an ER model, how would you ensure that the combination of attributes is truly unique, and what happens if the cardinality of one component changes over time?

Amazon – Senior Data ArchitectMicrosoft – Database EngineerMeta – Senior Database Architect

Question 11: If you have a weak entity that depends on two parent entities, how would you design the primary key of the weak entity, and what would be the implications for deletion cascades?

Flipkart – Senior Database EngineerLinkedIn – Data Architect

Question 12: When converting an ER model to a relational schema, how would you handle a recursive relationship where an employee reports to another employee within the same entity?

Google – Senior Data ArchitectOracle – Senior DBAIBM – Data Architect

Question 13: In an ER model for an e-commerce system, would you model a product review as an attribute, a separate entity, or a relationship entity, and what factors would influence your decision?

Stripe – Data EngineerAmazon – Senior Database Engineer

Question 14: When using table-per-type inheritance with specialized employee types, how would you write a query to retrieve all employees regardless of type, and what performance implications arise?

Microsoft – Senior Data ArchitectMeta – Database EngineerGoogle – BI Architect

Question 15: If you need to add a new entity type to an existing ER model that was already implemented in production, what would be the safest approach to extend the schema without breaking existing applications?

LinkedIn – Senior Data EngineerFlipkart – Data Architect

Question 16: When an ER model shows that two entities have both a 1:1 relationship and share a common parent entity, how would you resolve potential redundancy in the design?

Stripe – Senior Data ArchitectAmazon – Database EngineerOracle – Senior DBA

Question 17: In designing an ER model for a healthcare system with multiple types of medical professionals, how would you represent specializations while maintaining a consistent query interface for all professionals?

Google – Senior Data EngineerIBM – Data Architect

Question 18: When converting a complex M:N relationship into a junction table, what additional attributes or keys might you need to add to maintain referential integrity across multiple parent tables?

Meta – Senior Data ArchitectMicrosoft – Database EngineerLinkedIn – Senior BI Engineer

Question 19: If your ER model uses a natural key like email as the primary key, how would you handle a scenario where the business rules change and users can now have multiple emails, and what migration strategy would you use?

Stripe – Database EngineerAmazon – Senior Data Architect

Question 20: When documenting an ER model for a large enterprise system with dozens of entities and complex relationships, what are the most effective ways to represent cardinality and ensure the diagram remains readable and maintainable?

Google – Senior Database ArchitectOracle – Senior Data ArchitectIBM – Database Engineer

Ready to Master ER Diagrams & Schema Design?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.