Normalisation and ER Modelling

Database design questions show whether you can turn a real requirement into tables that stay correct as data grows. Interviewers typically give a messy table and ask you to normalise it, or describe a system and ask you to draw the entities and relationships.

1. What are entities, attributes and relationships in an ER model?

An entity is a thing you store data about (Student, Course). Attributes describe it (name, email). A relationship connects entities (a Student enrols in a Course). Weak entities depend on another entity for identity (an OrderLine depends on its Order).

2. Explain cardinality and how each type maps to tables.

• One-to-one (user ↔ profile): put a unique foreign key on one side, or merge the tables.
• One-to-many (department → employees): foreign key on the “many” side.
• Many-to-many (students ↔ courses): a junction table with both foreign keys, often plus extra columns such as enrolment date or grade.

CREATE TABLE enrolments (
  student_id  INT NOT NULL REFERENCES students(id),
  course_id   INT NOT NULL REFERENCES courses(id),
  enrolled_on DATE NOT NULL,
  grade       CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

3. What is a functional dependency?

A → B means the value of A determines exactly one value of B. In a students table, roll_no → name. Normal forms are defined in terms of functional dependencies: which columns depend on the key, on part of the key, or on other non-key columns.

4. Explain 1NF, 2NF and 3NF with an example.

1NF: atomic values, no repeating groups (no “phone1, phone2” or comma-separated lists). 2NF: 1NF plus no partial dependency — every non-key column depends on the whole composite key. 3NF: 2NF plus no transitive dependency — non-key columns depend only on the key, not on other non-key columns.

-- Unnormalised: orders(order_id, customer_name, customer_city, product_ids "P1,P2")
-- 1NF: one row per order line → order_lines(order_id, product_id, qty, customer_name, customer_city)
-- 2NF: customer details depend only on order_id (part of the key) → move to orders
--      orders(order_id, customer_id) ; order_lines(order_id, product_id, qty)
-- 3NF: customer_city depends on customer, not on the order → customers(customer_id, name, city)

5. What is BCNF and how is it different from 3NF?

Boyce–Codd Normal Form requires that for every functional dependency X → Y, X is a super key. 3NF allows an exception when Y is part of a candidate key; BCNF does not. Most real 3NF designs are already in BCNF; the difference appears with overlapping candidate keys.

6. What problems does normalisation solve?

It removes redundancy and the anomalies it causes: update anomalies (changing a customer's city in many rows, missing one), insert anomalies (cannot add a course without a student) and delete anomalies (deleting the last enrolment deletes the course's details).

7. When would you denormalise?

For read-heavy workloads where joins are too slow: reporting tables, cached counters (a comment_count column), search documents and analytics warehouses (star schemas). You trade storage and write complexity for faster reads, so keep the duplicated data in sync with triggers, application logic or scheduled jobs.

8. Design the tables for a simple online exam system.

Identify entities and relationships first, then keys and constraints.

CREATE TABLE users     (id INT PRIMARY KEY, name VARCHAR(80) NOT NULL, email VARCHAR(120) UNIQUE NOT NULL);
CREATE TABLE tests     (id INT PRIMARY KEY, title VARCHAR(150) NOT NULL, duration_min INT NOT NULL);
CREATE TABLE questions (id INT PRIMARY KEY, test_id INT NOT NULL REFERENCES tests(id), body TEXT NOT NULL, answer CHAR(1) NOT NULL);
CREATE TABLE attempts  (id INT PRIMARY KEY, user_id INT NOT NULL REFERENCES users(id), test_id INT NOT NULL REFERENCES tests(id),
                        started_at TIMESTAMP NOT NULL, score INT);
CREATE TABLE responses (attempt_id INT REFERENCES attempts(id), question_id INT REFERENCES questions(id), chosen CHAR(1),
                        PRIMARY KEY (attempt_id, question_id));