This page needs a newer browser to run its tools. You can still read everything here.

Database Management Systems (DBMS) Module 3

Conceptual design with the ER model interview questions

Interview questions on conceptual design with the ER model: warm-ups, then what each harder question tests and how a good answer starts.

15 questions: 5 warm-ups and 10 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What is the difference between a composite, a multivalued and a derived attribute?

Answered in the lesson ER model: entities, attributes and key attributes
Fresher

What is the difference between a relationship's cardinality ratio and its participation?

Answered in the lesson ER relationships: cardinality and participation
Fresher

What makes an entity weak, and what is its partial key?

Answered in the lesson Weak entities, specialisation and aggregation
Fresher

In a 1:N relationship, which table gets the foreign key, and why?

Answered in the lesson Mapping ER diagrams to relational tables
Fresher to mid

Why are journeys stored as half-open stop ranges [from, to)?

Answered in the lesson ER case study: a train reservation system

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to mid

A gym's members table has an integer age column that the sign-up form fills in. Two years later, trainers say the ages are wrong and age-based offers reach the wrong people. What went wrong in the design, how do you change the schema, and what do you do with the rows already stored?
What it tests

Telling stored from derived attributes, choosing what to record instead, and migrating old rows honestly when the input the design needs was never collected.

First step

Name the mistake first: age is derived from a birth date and the day you ask, so a stored age is right only on the day it was typed. Record the birth date, compute the age when a query needs it, and then decide what to do for old rows that hold only an age and a sign-up date.
Follow-ups
- How do you compute an exact age in SQL, birthdays and leap days included?
- Old members gave only an age. What can you store for them without inventing a birth date?
- When is storing a derived value the right choice?
From the lesson ER model: entities, attributes and key attributes
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; nothing renews unless you choose to. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A customers table keeps all of a customer's phone numbers in one text column, separated by commas. Support cannot find customers by phone reliably, and the same number appears twice for some customers. What does the design get wrong in ER terms, and which schema would you move to?
What it tests

Recognising a multivalued attribute, giving it a table with the right key, and the practical costs of packed lists: searching, uniqueness, validation and changes to one value.

First step

Say what kind of attribute it is: phone is multivalued, so a column that holds a list breaks the rule of one value per column. Give it a table of its own, customer_phone (customer_id, phone), keyed on both columns, and store every number in one normalised format.
Follow-ups
- How do you move the existing comma-separated values into the new table?
- Should a phone number be unique across all customers?
- When could a JSON array column still be acceptable?
From the lesson ER model: entities, attributes and key attributes
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to senior

A social app lets users follow other users. Model FOLLOWS in ER terms, give its ratio and participation, and turn it into a table. Which constraints stop duplicate follows and self-follows, and how do you find mutual follows?
What it tests

Recursive many-to-many relationships with roles, the key of a relationship table, a CHECK across two columns, the indexes each direction needs, and self-joins.

First step

Start from the roles: FOLLOWS relates USER to USER as follower and followed, many to many, with partial participation on both sides. That gives a table follows (follower_id, followed_id) whose primary key is the pair, with both columns referencing the users table.
Follow-ups
- How do you stop a user from following themselves?
- Write the query for mutual follows, listing each pair once.
- What changes when a follow needs the other user's approval?
From the lesson ER relationships: cardinality and participation
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A clinic's database records prescriptions as three binary tables: doctor–patient, doctor–medicine and patient–medicine. A pharmacist asks which doctor prescribed a given medicine to a given patient. What can the design answer, what can it get wrong, and what would you change?
What it tests

Ternary against binary relationships, the combinations a join of pairs invents, choosing the key of a ternary table, and when an entity models a fact better than a relationship.

First step

Point out that each table stores pairs while the question is about a triple. Then build a counterexample with two doctors, two patients and two medicines in which the join of the three tables names a doctor who never prescribed that medicine to that patient.
Follow-ups
- Which key does the ternary table need if the same doctor can prescribe the same medicine to the same patient twice?
- When is an entity better than a relationship for a prescription?
- Is there a rule under which fewer tables than a ternary would be enough?
From the lesson ER relationships: cardinality and participation
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to senior

A shop numbers order lines 1, 2, 3 within each order. Should ORDER_LINE be a weak entity of ORDER or a strong entity with its own identifier? Defend your choice, given that returns and invoices must refer to individual lines.
What it tests

Weak against strong entities, identification by an owner plus a partial key, what each choice does to the foreign keys of other tables, and what renumbering and deletion do.

First step

Say what each choice means for keys. As a weak entity, a line is identified by (order_id, line_no), and every table that refers to a line carries both columns. With its own identifier, references need one column, but nothing stops two lines numbered 3 in one order unless a UNIQUE constraint on (order_id, line_no) is added.
Follow-ups
- What happens to returns if lines are renumbered after one is deleted?
- Which constraints does each design need?
- How does deleting an order behave in each design?
From the lesson Weak entities, specialisation and aggregation
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A billing system's customers are individuals, identified by a government ID number, or companies, identified by a registration number. Invoices refer to the customer. Model this with a superclass or with a union type, and compare what each does to keys, foreign keys and queries.
What it tests

Generalisation against categories, keys when the kinds have different natural keys, foreign keys that must reach one of two tables, and the queries each design makes easy or hard.

First step

Start from the keys. Individuals and companies have different natural keys, so neither can be the key of the other, and an invoice needs one column to point at. Both designs therefore introduce a surrogate key; they differ in whether CUSTOMER is a superclass that holds shared attributes or a category that only unites the two sets.
Follow-ups
- How do you stop one customer from being both an individual and a company?
- Write the query that lists invoices with each customer's display name.
- What changes when a third kind of customer, government agencies, is added?
From the lesson Weak entities, specialisation and aggregation
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

Every parking permit belongs to exactly one employee, and an employee holds at most one permit. Map the relationship: where does the foreign key go, which constraints do you add, and when would you merge the two tables?
What it tests

Mapping 1:1 relationships: choosing the side from participation, the UNIQUE that keeps the relationship 1:1, NOT NULL for total participation, and when two tables should become one.

First step

Read the participation first: every permit has an employee, so the permit's side is total, while most employees have no permit. Put the foreign key in the permit table, as employee_id NOT NULL UNIQUE REFERENCES employee: NOT NULL for the total side, UNIQUE so that no employee holds two permits.
Follow-ups
- What goes wrong if the foreign key goes into the employee table instead?
- What happens if the UNIQUE constraint is forgotten?
- When is merging the two tables a good idea?
From the lesson Mapping ER diagrams to relational tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A ledger stores card payments, bank transfers and wallet payments, about 50 million a year. Most reports read all payments of one customer, and each kind has two or three fields of its own. Which specialisation layout do you choose, and how do you make sure each payment is exactly one kind?
What it tests

The trade-offs between the four specialisation layouts with numbers: NULLs, joins, constraints that can be enforced, the queries that matter and how the design grows.

First step

Start from the queries: most read every payment of a customer, so a layout that keeps all payments in one table avoids a UNION or a join on the busiest path. Then count the kind-specific columns: with only a few, one table with a type column costs little in NULLs, and the type column itself enforces exactly one kind.
Follow-ups
- Write the CHECK constraints that keep each kind's columns consistent.
- What changes if wallet payments gain twenty columns of their own?
- How would you add a fourth kind of payment later?
From the lesson Mapping ER diagrams to relational tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

Two requests for the same berth with overlapping journeys arrive at the same moment. The allocation table has a BEFORE INSERT trigger that refuses overlapping journeys. What can go wrong on PostgreSQL, and how do you make the database refuse the second booking?
What it tests

What a trigger's query can see under Read Committed, check-then-insert races, exclusion constraints and how they wait, and the alternatives: locking, serializable transactions and per-segment rows.

First step

Explain what each trigger sees. Under Read Committed a query reads only rows committed before it began, so two transactions that insert at the same time each find no clash and both commit. The rule needs something that also sees other transactions' uncommitted rows, which the index of an exclusion constraint does.
Follow-ups
- Would running the trigger version at the Serializable isolation level fix it?
- How would you get the same guarantee without an exclusion constraint?
- What should the booking service do when the second insert fails?
From the lesson ER case study: a train reservation system
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A train has 18 coaches of 72 berths and 25 stops, runs every day and opens bookings 120 days ahead. Estimate the most allocation rows one run can hold and the rows across all open runs, and say which index the free-berths query needs.
What it tests

Estimating table sizes from the data model, the worst case of selling berths by segment, and choosing the index that serves the free-berths query.

First step

Count berths first: 18 × 72 = 1,296 per run. A berth can carry at most one journey per segment, and a route of 25 stops has 24 segments, so one run holds at most 1,296 × 24 = 31,104 allocations; then multiply by the 120 runs that are open at once.
Follow-ups
- Roughly how many bytes do those rows and their index take?
- Which index serves "free berths of this run and class between two stops"?
- How do you keep the runs that have departed from slowing the table down?
From the lesson ER case study: a train reservation system
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

ER model: entities, attributes and key attributes Free lesson, Pro answers: 25 minutes
ER relationships: cardinality and participation Free lesson, Pro answers: 25 minutes
Weak entities, specialisation and aggregation Free lesson, Pro answers: 25 minutes
Mapping ER diagrams to relational tables Free lesson, Pro answers: 30 minutes
ER case study: a train reservation system Free lesson, Pro answers: 35 minutes

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track

Warm-up questions

What is the difference between a composite, a multivalued and a derived attribute?

What is the difference between a relationship's cardinality ratio and its participation?

What makes an entity weak, and what is its partial key?

In a 1:N relationship, which table gets the foreign key, and why?

Why are journeys stored as half-open stop ranges [from, to)?

Questions with model answers

A gym's members table has an integer age column that the sign-up form fills in. Two years later, trainers say the ages are wrong and age-based offers reach the wrong people. What went wrong in the design, how do you change the schema, and what do you do with the rows already stored?

A customers table keeps all of a customer's phone numbers in one text column, separated by commas. Support cannot find customers by phone reliably, and the same number appears twice for some customers. What does the design get wrong in ER terms, and which schema would you move to?

A social app lets users follow other users. Model FOLLOWS in ER terms, give its ratio and participation, and turn it into a table. Which constraints stop duplicate follows and self-follows, and how do you find mutual follows?

A clinic's database records prescriptions as three binary tables: doctor–patient, doctor–medicine and patient–medicine. A pharmacist asks which doctor prescribed a given medicine to a given patient. What can the design answer, what can it get wrong, and what would you change?

A shop numbers order lines 1, 2, 3 within each order. Should ORDER_LINE be a weak entity of ORDER or a strong entity with its own identifier? Defend your choice, given that returns and invoices must refer to individual lines.

A billing system's customers are individuals, identified by a government ID number, or companies, identified by a registration number. Invoices refer to the customer. Model this with a superclass or with a union type, and compare what each does to keys, foreign keys and queries.

Every parking permit belongs to exactly one employee, and an employee holds at most one permit. Map the relationship: where does the foreign key go, which constraints do you add, and when would you merge the two tables?

A ledger stores card payments, bank transfers and wallet payments, about 50 million a year. Most reports read all payments of one customer, and each kind has two or three fields of its own. Which specialisation layout do you choose, and how do you make sure each payment is exactly one kind?

Two requests for the same berth with overlapping journeys arrive at the same moment. The allocation table has a BEFORE INSERT trigger that refuses overlapping journeys. What can go wrong on PostgreSQL, and how do you make the database refuse the second booking?

A train has 18 coaches of 72 berths and 25 stops, runs every day and opens bookings 120 days ahead. Estimate the most allocation rows one run can hold and the rows across all open runs, and say which index the free-berths query needs.