Relational Model, Keys & Integrity

1. What is the difference between a Primary Key, a Foreign Key, and a Candidate Key?

All three are kinds of keys, but they play different roles.

• Primary Key — a column (or set of columns) that uniquely identifies every row. It is unique and cannot be NULL. Each table has exactly one primary key.
• Foreign Key — a column in one table that points to the primary key of another table. It creates a link between the tables and enforces referential integrity.
• Candidate Key — any column (or combination) that could be a primary key because it’s unique and non-NULL. The primary key is just one candidate key that you chose; the others are called alternate keys.

Example: In an employees table, both employee_id and email are unique, so both are candidate keys. You pick employee_id as the primary key, making email an alternate key. In an orders table, customer_id is a foreign key pointing back at employees.employee_id.

Key	Unique?	NULL allowed?	Purpose
Primary	Yes	No	Identifies a row
Foreign	No	Yes	Links tables
Candidate	Yes	No	Potential primary key

2. What is a Surrogate Key?

A surrogate key is a system-generated identifier that has no meaning in the real world. It’s usually an auto-incrementing integer or a UUID.

Example: A customers table might have a customer_id that’s just an increasing number. It carries no business meaning — it doesn’t represent an email, a phone number, or anything real. Its only job is to identify the row.

This is different from a natural key, which comes from real business data (like an Aadhaar number or an email). The benefit of a surrogate key: it never changes. Natural keys can change over time (someone changes their email), but a surrogate ID stays stable forever.

3. What does Referential Integrity enforce in a relational model?

Referential integrity enforces the rule that a foreign key value must match an existing primary key value in the referenced table (or be NULL).

Example: In an orders table, customer_id references customers.customer_id. Referential integrity means you can’t create an order for customer 999 if no customer 999 exists. And you typically can’t delete a customer who still has orders.

This is what keeps the database free of orphan records — rows that reference things that don’t exist. It’s enforced automatically by the database whenever you define a FOREIGN KEY constraint.

4. What is Cardinality in database design?

Cardinality describes the numerical relationship between rows in two related tables.

The common types:

• One-to-one (1:1) — each row in table A matches at most one row in table B. E.g., a person and their passport.
• One-to-many (1:N) — one row in A matches many rows in B. E.g., one customer, many orders.
• Many-to-many (M:N) — rows on both sides can match many rows on the other. E.g., students and courses — needs a junction table.

Cardinality is a design decision you capture early in the entity-relationship model, and it drives how the foreign keys are laid out.

5. What is a Default Constraint?

A default constraint sets a value that gets used automatically when an INSERT doesn’t supply one.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'active'
);

Insert a row without mentioning status, and it’s stored as 'active'. The constraint isn’t about uniqueness or NULL prevention — it’s just a fallback value so a column never ends up unexpectedly empty.

6. What is an Alternate Key?

An alternate key is a candidate key that you didn’t choose as the primary key.

In the students example, if you pick student_id as the primary key, then email becomes the alternate key. It still uniquely identifies rows and is still unique and non-NULL — it just didn’t get the top job.

So the family tree is: candidate keys → primary key + alternate keys.

7. What is a Super Key?

A super key is any set of attributes that uniquely identifies rows in a relation. Unlike a candidate key, it may contain extra attributes that aren’t needed.

Example: In employees(EmpID, Email, Name), {EmpID}, {Email}, {EmpID, Email}, {EmpID, Name} are all super keys — each set uniquely identifies a row.

A candidate key is a minimal super key — one where removing any attribute breaks uniqueness. So every candidate key is a super key, but not every super key is a candidate key.

8. What is the difference between a Total Participation and a Partial Participation constraint in an ER Diagram?

This is about whether every instance of an entity must take part in a relationship.

• Total participation — every entity instance must participate. Represented by a double line in the ER diagram. E.g., every student must be enrolled in a course.
• Partial participation — only some instances participate. Represented by a single line. E.g., not every employee manages a project — only some do.

Example: “Each Employee must work in exactly one Department” is total participation — no employee can exist without a department. “Each Department has a Manager” is partial — most departments have one, but a newly created department might not yet.

9. What is an ER model and what are its components?

The ER (Entity-Relationship) model is a conceptual design tool that pictures the database as entities connected by relationships, before any tables exist. The three building blocks:

• Entity — a real-world object about which we store data (Employee, Department, Order). In the ER diagram it’s a rectangle; each entity becomes a table.
• Attribute — a property of an entity (Employee’s Name, Salary). Drawn as ovals connected to the entity; attributes become columns. The primary key attribute is underlined.
• Relationship — an association between entities (Employee works in Department). Drawn as a diamond; becomes a foreign key when mapped to tables.

Three things to know for interviews:

• Cardinality — the numeric nature of a relationship: 1:1 (one employee, one badge), 1:N (one department, many employees), M:N (many students, many courses — needs a junction table).
• Weak entity — an entity that can’t exist without its owner, identified by the owner’s key plus a partial key (e.g. a Room only exists under its Building). Drawn as a double rectangle, with a double diamond to its owner.
• Total vs partial participation — total (double line): every instance must participate in the relationship; partial (single line): optional.

ER diagrams are the “blueprint” stage — they’re mapped to relations via rules (entity → table, M:N → junction table, weak entity → table with combined key). The interview one-liner: ER model is what the data looks like conceptually; the relational schema is how it’s stored.