ER modelling & keys
DBMS · 10 interview questions
An entity-relationship model describes data as entities (things you store), attributes (facts about them) and relationships (how they connect). It's the design step before tables exist, and it's where cardinality decisions — one-to-one, one-to-many, many-to-many — get made.
The part interviews actually test is keys. A candidate key is any minimal set of attributes that uniquely identifies a row; the primary key is the one you pick; the rest become alternate keys. A foreign key is an attribute referencing another table's primary key, and it's what makes referential integrity enforceable.
The recurring practical question is how to store a many-to-many relationship, because relational tables can't express one directly. The answer is always a junction table holding two foreign keys.
ER modelling & keys interview questions
- What is a candidate key?
- A minimal set of attributes that uniquely identifies every row. Minimal matters: if you can drop an attribute and it still identifies uniquely, the original set wasn't a candidate key.
- How does a primary key differ from a candidate key?
- The primary key is whichever candidate key you choose as the row's official identifier. The others become alternate keys. A table can have several candidate keys but only one primary key.
- What is a super key?
- Any set of attributes that uniquely identifies a row, minimal or not. Every candidate key is a super key; a super key with extra attributes bolted on is not a candidate key.
- What is a composite key?
- A primary key made of two or more columns, where no single column is unique on its own. Common in junction tables, where the pair of foreign keys is the key.
- What does a foreign key actually enforce?
- Referential integrity: a value in the referencing column must exist in the referenced table, and a referenced row can't be deleted while references remain — unless you've specified a cascade or set-null action.
- How do you model a many-to-many relationship?
- With a junction table holding a foreign key to each side. Students and courses become a StudentCourse table; its composite key is the pair, and it's also the natural place for relationship attributes like an enrolment date.
- Why they ask: Extremely common as a schema-design question.
- Surrogate key or natural key — what's the trade-off?
- A natural key uses real data (an email, an ISBN) and carries meaning, but real-world values change and that change propagates to every referencing row. A surrogate key is a meaningless generated id — stable and compact, at the cost of an extra join to see anything human-readable.
- Can a primary key column be NULL?
- No. NULL means unknown, and an unknown value can't identify a row. Unique constraints are more permissive — most databases allow one or more NULLs in a unique column.
- What is a weak entity?
- An entity that can't be identified by its own attributes alone and depends on an owner — an OrderLine that only makes sense within an Order. Its key includes the owner's key.
- How is a one-to-one relationship stored?
- A foreign key with a unique constraint on one of the two tables. Which side holds it is usually decided by which is optional, so the nullable column sits on the table where absence is normal.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days