Practice: database design

Optional

50 min · Intermediate

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Good database design is a routine, not a talent: find the entities, give each a key, settle every relationship, check the normal forms, and let constraints block the bad data. This practice runs the whole routine on new problems.

Design from short requirements

Every design follows the same six steps in the same order. The hospital OPD requirement below is taken through them: first the ER diagram, then the schema with its key rule enforced.

Underline the nouns and sort them into entities, attributes and "not stored".
Give each entity a primary key, and UNIQUE on any natural key.
For each pair of related entities, ask "one or many?" from both sides.
Place the foreign keys: many side for 1:N, dependent side plus UNIQUE for 1:1, a join table for M:N.
Turn the business rules into NOT NULL, UNIQUE and CHECK constraints.
Write the CREATE TABLE script, add sample rows and run the questions the business will ask.

Example 1 · Requirement to ER diagram: a hospital OPD

Text

1Requirement:2  "Patients book OPD appointments with doctors. Each doctor belongs to one3   department. A patient can book many appointments and a doctor sees many4   patients. Each appointment is for a 15-minute slot, and a doctor can never5   have two appointments in the same slot. Patients are found by phone number."67+-----------------+          +---------------+          +------------------+          +---------------+8| opd_departments |          | opd_doctors   |          | appointments     |          | opd_patients  |9|-----------------|          |---------------|          |------------------|          |---------------|10| PK dept_id      | ||----o{ | PK doctor_id  | ||----o{ | PK appt_id       | o{----|| | PK patient_id |11|    name (UQ)    |          |    name       |          | FK doctor_id     |          |    name       |12+-----------------+          | FK dept_id    |          | FK patient_id    |          |    phone (UQ) |13                             +---------------+          |    slot_start    |          +---------------+14                                                        | UQ (doctor_id,   |15                                                        |     slot_start)  |16                                                        +------------------+

Design notes

Entities: departments, doctors, patients, appointments.
Department 1:N doctor (FK in doctors). Doctor M:N patient through appointments.
The pair (doctor, patient) can repeat on different days, so appointments has its own id.
The rule "no two appointments in one slot" becomes UNIQUE (doctor_id, slot_start).
"Found by phone" makes phone a natural key: UNIQUE.

What this code does

Every sentence of the requirement became something in the diagram: an entity, a line with marks, or a unique key.
A patient can see the same doctor many times, so (doctor_id, patient_id) cannot be the key; the real "never twice" rule is one doctor in one slot.

Example 2 · The OPD schema, with its rule enforced

SQL

1CREATE TABLE opd_departments (2  dept_id INT AUTO_INCREMENT PRIMARY KEY,3  name    VARCHAR(40) NOT NULL UNIQUE4);5CREATE TABLE opd_doctors (6  doctor_id INT AUTO_INCREMENT PRIMARY KEY,7  name      VARCHAR(40) NOT NULL,8  dept_id   INT         NOT NULL,9  FOREIGN KEY (dept_id) REFERENCES opd_departments (dept_id)10);11CREATE TABLE opd_patients (12  patient_id INT AUTO_INCREMENT PRIMARY KEY,13  name       VARCHAR(40) NOT NULL,14  phone      VARCHAR(10) NOT NULL UNIQUE15);16CREATE TABLE appointments (17  appt_id    INT AUTO_INCREMENT PRIMARY KEY,18  doctor_id  INT      NOT NULL,19  patient_id INT      NOT NULL,20  slot_start DATETIME NOT NULL,21  UNIQUE (doctor_id, slot_start),          -- one patient per doctor per slot22  FOREIGN KEY (doctor_id)  REFERENCES opd_doctors (doctor_id),23  FOREIGN KEY (patient_id) REFERENCES opd_patients (patient_id)24);25
26INSERT INTO opd_departments (name) VALUES ('Cardiology'), ('Orthopaedics');27INSERT INTO opd_doctors (name, dept_id) VALUES ('Dr Mehra', 1), ('Dr Pillai', 2);28INSERT INTO opd_patients (name, phone) VALUES29  ('Anand', '9000000001'), ('Bushra', '9000000002'), ('Charu', '9000000003');30INSERT INTO appointments (doctor_id, patient_id, slot_start) VALUES31  (1, 1, '2026-11-02 10:00:00'),32  (1, 2, '2026-11-02 10:15:00'),33  (2, 3, '2026-11-02 10:00:00');34
35-- Today's list per department and doctor36SELECT d.name AS department, doc.name AS doctor, p.name AS patient,37       TIME_FORMAT(a.slot_start, '%H:%i') AS slot38FROM appointments a39JOIN opd_doctors doc    ON doc.doctor_id = a.doctor_id40JOIN opd_departments d  ON d.dept_id = doc.dept_id41JOIN opd_patients p     ON p.patient_id = a.patient_id42WHERE DATE(a.slot_start) = '2026-11-02'43ORDER BY d.name, a.slot_start;44
45-- Double booking Dr Mehra at 10:00 is refused46INSERT INTO appointments (doctor_id, patient_id, slot_start)47VALUES (1, 3, '2026-11-02 10:00:00');

Output

+--------------+-----------+---------+-------+
| department   | doctor    | patient | slot  |
+--------------+-----------+---------+-------+
| Cardiology   | Dr Mehra  | Anand   | 10:00 |
| Cardiology   | Dr Mehra  | Bushra  | 10:15 |
| Orthopaedics | Dr Pillai | Charu   | 10:00 |
+--------------+-----------+---------+-------+

ERROR 1062 (23000): Duplicate entry '1-2026-11-02 10:00:00' for key 'appointments.doctor_id'

What this code does

Dr Pillai can see Charu at 10:00 while Dr Mehra sees Anand at 10:00: the unique key is on the doctor and slot together, not on the slot alone.
The last insert breaks UNIQUE (doctor_id, slot_start) and fails with error 1062, even if two receptionists try at once. The database, not the app screen, owns the rule.

Sentences such as "a doctor cannot see two patients at the same time" are constraints in disguise. Find them in the requirement and make the database enforce them.

Find and fix normalisation problems

A review asks four questions in order. The queries below run them with SQL on a hostel fee sheet, and the answers point to the fix.

Example 3 · Reviewing a hostel fee sheet with SQL

SQL

1CREATE TABLE hostel_fee_sheet (2  receipt_no INT PRIMARY KEY,3  roll_no    VARCHAR(10) NOT NULL,4  name       VARCHAR(30) NOT NULL,5  room_no    VARCHAR(5)  NOT NULL,6  block      CHAR(1)     NOT NULL,7  warden     VARCHAR(30) NOT NULL,8  month      CHAR(7)     NOT NULL,9  amount     INT         NOT NULL10);11INSERT INTO hostel_fee_sheet VALUES12  (501, '22CS041', 'Nikhil', 'A-101', 'A', 'Mr Rao',    '2026-07', 6500),13  (502, '22EC007', 'Sana',   'B-204', 'B', 'Ms Thomas', '2026-07', 6500),14  (503, '22CS041', 'Nikhil', 'A-101', 'A', 'Mr Rao',    '2026-08', 6500),15  (504, '23ME112', 'Varun',  'A-101', 'A', 'Mr Rao',    '2026-08', 6500),16  (505, '22EC007', 'Sana',   'B-204', 'B', 'Ms Thomas', '2026-08', 6500);17
18-- How often is each fact repeated?19SELECT 'roll_no -> name, room_no' AS dependency, COUNT(*) - COUNT(DISTINCT roll_no) AS extra_copies20FROM hostel_fee_sheet21UNION ALL22SELECT 'room_no -> block', COUNT(*) - COUNT(DISTINCT room_no) FROM hostel_fee_sheet23UNION ALL24SELECT 'block -> warden', COUNT(*) - COUNT(DISTINCT block) FROM hostel_fee_sheet;25
26-- Does each dependency hold in the data? (0 = no row breaks it)27SELECT28  (SELECT COUNT(*) FROM (SELECT roll_no FROM hostel_fee_sheet GROUP BY roll_no29                         HAVING COUNT(DISTINCT room_no) > 1) x) AS roll_breaks,30  (SELECT COUNT(*) FROM (SELECT block FROM hostel_fee_sheet GROUP BY block31                         HAVING COUNT(DISTINCT warden) > 1) y) AS block_breaks;

Output

+--------------------------+--------------+
| dependency               | extra_copies |
+--------------------------+--------------+
| roll_no -> name, room_no |            2 |
| room_no -> block         |            3 |
| block -> warden          |            3 |
+--------------------------+--------------+
+-------------+--------------+
| roll_breaks | block_breaks |
+-------------+--------------+
|           0 |            0 |
+-------------+--------------+

What this code does

The key is receipt_no, yet the student, room and warden facts are stored again on every receipt: 2 extra copies of student details, 3 of room facts and 3 of wardens in only five rows.
The data agrees with the rules (0 breaks), so the fix is a design one: fee_payments(receipt_no, roll_no, month, amount), residents(roll_no, name, room_no), rooms(room_no, block) and blocks(block, warden). The chain roll_no → room_no → block → warden is two transitive dependencies.

Does a cell hold a list, or are there numbered columns? Not 1NF: move the values to a child table.
Is the key composite, and does a column depend on only one part of it? Not 2NF: new table keyed by that part.
Does a non-key column decide another non-key column (pincode → city)? Not 3NF: new table keyed by it.
Is a column computable from others (age, grade, total)? Do not store it, unless you denormalise on purpose with a plan.

On real data, GROUP BY x HAVING COUNT(DISTINCT y) > 1 finds broken dependencies quickly. A result row means the same x already has two different y values.

Choose relationships and keys

A key is where a design stops bad data. The script below uses a composite key so one doctor cannot be booked twice in the same slot.

Example 4 · Choosing the key that stops double booking

SQL

1CREATE TABLE shows (2  show_id   INT AUTO_INCREMENT PRIMARY KEY,3  film      VARCHAR(40) NOT NULL,4  starts_at DATETIME    NOT NULL5);6CREATE TABLE seats (7  seat_id  INT AUTO_INCREMENT PRIMARY KEY,8  seat_row CHAR(1) NOT NULL,9  seat_no  TINYINT NOT NULL,10  UNIQUE (seat_row, seat_no)11);12CREATE TABLE seat_bookings (13  booking_id INT AUTO_INCREMENT PRIMARY KEY,14  show_id    INT         NOT NULL,15  seat_id    INT         NOT NULL,16  customer   VARCHAR(30) NOT NULL,17  UNIQUE (show_id, seat_id),               -- a seat once per show18  FOREIGN KEY (show_id) REFERENCES shows (show_id),19  FOREIGN KEY (seat_id) REFERENCES seats (seat_id)20);21
22INSERT INTO shows (film, starts_at) VALUES23  ('Dangal', '2026-11-07 18:30:00'), ('Dangal', '2026-11-07 21:45:00');24INSERT INTO seats (seat_row, seat_no) VALUES ('A', 1), ('A', 2), ('B', 1);25INSERT INTO seat_bookings (show_id, seat_id, customer) VALUES26  (1, 1, 'Pallavi'), (1, 2, 'Pallavi'), (2, 1, 'Joseph');27
28-- Free seats for show 129SELECT CONCAT(s.seat_row, s.seat_no) AS free_seat30FROM seats s31LEFT JOIN seat_bookings b ON b.seat_id = s.seat_id AND b.show_id = 132WHERE b.booking_id IS NULL33ORDER BY s.seat_row, s.seat_no;34
35-- A second booking of A1 for show 1 is refused36INSERT INTO seat_bookings (show_id, seat_id, customer) VALUES (1, 1, 'Rehan');

Output

+-----------+
| free_seat |
+-----------+
| B1        |
+-----------+

ERROR 1062 (23000): Duplicate entry '1-1' for key 'seat_bookings.show_id'

What this code does

The rule "a seat can be sold once per show" becomes UNIQUE (show_id, seat_id). Seat A1 is booked for both shows, which is fine, because the pairs differ.
The free-seat query puts the show filter inside the LEFT JOIN condition, so seats with no booking for show 1 survive with NULL. Rehan's insert for the taken seat fails with error 1062.

The key of a join table is the pair when each pair can happen once (a student in a course). When the pair can repeat, add what makes each row different (the date or the time slot), or give the table its own id and put UNIQUE on the real rule. Ask "what must never happen twice?" and turn the answer into a unique key.

Fix the error

Fix the key

The second insert fails. Pick the key line that fixes the table.

Round 1 of 2

One doctor must have many slots. Which key line fixes appointments?

CREATE TABLE appointments (

doctor_id INT NOT NULL,

slot_start DATETIME NOT NULL,

patient_id INT NOT NULL,

PRIMARY KEY (doctor_id)

);

INSERT INTO appointments VALUES (1, '2026-03-02 10:00:00', 7);

INSERT INTO appointments VALUES (1, '2026-03-02 10:30:00', 9);

Error

ERROR 1062 (23000): Duplicate entry '1' for key 'appointments.PRIMARY'

Mixed review

A design review looks at a whole schema the way an interviewer does. The write-up below lists the problems found in a submitted online store schema and the fix for each.

Example 5 · A design review, written up

Text

1Proposed schema for an online store (as submitted):23products(product_id PK, name, brand, tags)                 -- 'Mobiles, 5G'4reviews(product_id, reviewer, rating, product_name)        -- no primary key5orders(order_id PK, customer_phone, customer_name,6       item1, qty1, item2, qty2, total)7couriers(courier_id PK, name, current_order_id)

Review findings

1. products.tags is a list: tags table + product_tags join table (1NF).
2. reviews has no primary key, and product_name depends on product_id only: add review_id PK, drop product_name (2NF/3NF).
3. orders.item1/qty1/item2/qty2 is a repeating group: order_items(order_id, product_id, quantity, unit_price) (1NF).
4. customer_name depends on customer_phone: a customers table keyed by phone or an id (3NF).
5. total is derived from order_items: compute it, or keep it with a trigger and a check query.
6. couriers.current_order_id puts the link on the wrong side: orders.courier_id (nullable) instead.

What this code does

Each finding names the problem, the rule it breaks and the fix, in the order of the review questions.
Finding 6 is a relationship mistake, not a normal-form one: a courier delivers many orders over time, so the foreign key belongs on the many side, orders.

A list in a cell, or item1, item2, item3 columns.
A foreign key on the wrong side of a one-to-many link, or a missing join table for many-to-many.
A "one-to-one" or "one per slot" rule with no UNIQUE behind it.
Stored derived values (age, grade, totals) with no plan to keep them correct.

Module quiz

Questions from the whole module. Your first answer is your score.

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

For each pair, give the cardinality and where the foreign key (or join table) goes, in the shape shown: (a) country and capital city; (b) bank and IFSC branches; (c) movies and genres; (d) WhatsApp groups and members.

Expected output

(a) 1:1, capital_city_id UNIQUE in countries (or country_id UNIQUE in cities)
(b) 1:N, bank_id in branches
(c) M:N, join table movie_genres(movie_id, genre_id)
(d) M:N, join table group_members(group_id, member_id)

Exercise 2· Easy

Students can join many college clubs, and a club has many students; the joining date is stored. Create fest_clubs(club_id, name) and club_members(club_id, student_id, joined_on) linked to the practice students table, add the members shown, and list each club with its members by name.

SQL

-- clubs: 'Robotics', 'Music'-- Robotics: Aditya Joshi (2025-08-01), Chirag Patel (2025-08-03)-- Music: Aditya Joshi (2025-08-10)

Expected output

+----------+--------------+------------+
| club     | student      | joined_on  |
+----------+--------------+------------+
| Music    | Aditya Joshi | 2025-08-10 |
| Robotics | Aditya Joshi | 2025-08-01 |
| Robotics | Chirag Patel | 2025-08-03 |
+----------+--------------+------------+

Exercise 3· Medium

Tour guides store their languages as a list. Move them to guide_languages(guide_id, language) by inserting the rows by hand, then list the guides who speak Tamil.

SQL

1CREATE TABLE guides (2  guide_id  INT PRIMARY KEY,3  name      VARCHAR(30)  NOT NULL,4  languages VARCHAR(100) NOT NULL5);6INSERT INTO guides VALUES7  (1, 'Murugan', 'Tamil, English'),8  (2, 'Fatima',  'Urdu, Hindi, English'),9  (3, 'Selvi',   'Tamil, Malayalam');

Expected output

+---------+
| name    |
+---------+
| Murugan |
| Selvi   |
+---------+

Exercise 4· Medium

Course offerings are stored with key (semester, course_code). Find the 2NF and 3NF problems, split the sheet into co_courses, co_faculty and offerings, and list each offering with its course title and faculty name.

SQL

1CREATE TABLE offerings_v1 (2  semester     CHAR(7)     NOT NULL,   -- e.g. '2026-S1'3  course_code  CHAR(5)     NOT NULL,4  course_title VARCHAR(40) NOT NULL,   -- course_code -> course_title5  faculty_id   INT         NOT NULL,6  faculty_name VARCHAR(30) NOT NULL,   -- faculty_id -> faculty_name7  PRIMARY KEY (semester, course_code)8);9INSERT INTO offerings_v1 VALUES10  ('2026-S1', 'CS201', 'Data Structures',  7, 'Dr Kulkarni'),11  ('2026-S1', 'CS202', 'Database Systems', 9, 'Dr Fernandes'),12  ('2026-S2', 'CS201', 'Data Structures',  9, 'Dr Fernandes');

Expected output

+----------+------------------+--------------+
| semester | title            | faculty      |
+----------+------------------+--------------+
| 2026-S1  | Data Structures  | Dr Kulkarni  |
| 2026-S1  | Database Systems | Dr Fernandes |
| 2026-S2  | Data Structures  | Dr Fernandes |
+----------+------------------+--------------+

Exercise 5· Medium

A library lends books; returned_on stays NULL until a book comes back. Create lib_books(book_id, title) and loans(loan_id, book_id, member, issued_on, returned_on), add the data shown, and list the books that are out right now with who has them.

SQL

-- books: 'Wings of Fire', 'Godan', 'The Guide'-- loans: Wings of Fire to Ishaan on 2026-10-01, returned 2026-10-10--        Godan to Meher on 2026-10-05, not returned--        Wings of Fire to Tanya on 2026-10-12, not returned

Expected output

+---------------+--------+------------+
| title         | member | issued_on  |
+---------------+--------+------------+
| Godan         | Meher  | 2026-10-05 |
| Wings of Fire | Tanya  | 2026-10-12 |
+---------------+--------+------------+

Exercise 6· Medium

Using the OPD schema of Example 2, add the three appointments shown, then list each doctor with their number of appointments on 2026-11-02, including doctors with none. Finally, try to book Dr Pillai at 10:00 again and show the error.

SQL

-- departments: Cardiology, Orthopaedics, ENT-- doctors: Dr Mehra (Cardiology), Dr Pillai (Orthopaedics), Dr Sen (ENT)-- patients: Anand 9000000001, Bushra 9000000002-- appointments on 2026-11-02:--   Dr Mehra 10:00 Anand, Dr Mehra 10:15 Bushra, Dr Pillai 10:00 Bushra

Expected output

+-----------+--------------+
| doctor    | appointments |
+-----------+--------------+
| Dr Mehra  |            2 |
| Dr Pillai |            1 |
| Dr Sen    |            0 |
+-----------+--------------+

ERROR 1062 (23000): Duplicate entry '2-2026-11-02 10:00:00' for key 'appointments.doctor_id'

Exercise 7· Hard

Design and build bill splitting for a UPI app: a bill has a payer and an amount; it is shared among several users with different shares. Create split_users, split_bills(bill_id, paid_by, amount) and bill_shares(bill_id, user_id, share). Add the data shown, then (1) check that every bill's shares add up to its amount, and (2) show what each user owes in total for bills they did not pay.

SQL

-- users: Asha, Bilal, Chitra-- bill 1: dinner, paid by Asha, 1200.00 -> Asha 400, Bilal 400, Chitra 400-- bill 2: cab,    paid by Bilal, 450.00 -> Asha 150, Bilal 300

Expected output

+---------+--------+---------+--------------+----------+
| bill_id | title  | amount  | shares_total | balanced |
+---------+--------+---------+--------------+----------+
|       1 | dinner | 1200.00 |      1200.00 |        1 |
|       2 | cab    |  450.00 |       450.00 |        1 |
+---------+--------+---------+--------------+----------+
+--------+--------+
| name   | owes   |
+--------+--------+
| Asha   | 150.00 |
| Bilal  | 400.00 |
| Chitra | 400.00 |
+--------+--------+

Exercise 8· Hard

Design bus reservations: a bus has numbered seats, a trip is one run of a bus on a date, and a seat can be reserved once per trip. Create buses2(bus_id, reg_no), bus_seats(bus_id, seat_no), trips(trip_id, bus_id, trip_date) and reservations(trip_id, seat_no, passenger). Add a 4-seat bus, one trip and two reservations (seats 1 and 3), then list the free seats of the trip.

Expected output

+-----------+
| free_seat |
+-----------+
|         2 |
|         4 |
+-----------+

Interview questions

Questions interviewers ask on this topic.

Finish up

Mark complete & continue

Next: Mini project: courier delivery database

Practice: database design

Optional

50 min · Intermediate

Need help? Ask a tutor

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Design from short requirements

Every design follows the same six steps in the same order. The hospital OPD requirement below is taken through them: first the ER diagram, then the schema with its key rule enforced.

Underline the nouns and sort them into entities, attributes and "not stored".
Give each entity a primary key, and UNIQUE on any natural key.
For each pair of related entities, ask "one or many?" from both sides.
Place the foreign keys: many side for 1:N, dependent side plus UNIQUE for 1:1, a join table for M:N.
Turn the business rules into NOT NULL, UNIQUE and CHECK constraints.
Write the CREATE TABLE script, add sample rows and run the questions the business will ask.

Example 1 · Requirement to ER diagram: a hospital OPD

Text

1Requirement:2  "Patients book OPD appointments with doctors. Each doctor belongs to one3   department. A patient can book many appointments and a doctor sees many4   patients. Each appointment is for a 15-minute slot, and a doctor can never5   have two appointments in the same slot. Patients are found by phone number."67+-----------------+          +---------------+          +------------------+          +---------------+8| opd_departments |          | opd_doctors   |          | appointments     |          | opd_patients  |9|-----------------|          |---------------|          |------------------|          |---------------|10| PK dept_id      | ||----o{ | PK doctor_id  | ||----o{ | PK appt_id       | o{----|| | PK patient_id |11|    name (UQ)    |          |    name       |          | FK doctor_id     |          |    name       |12+-----------------+          | FK dept_id    |          | FK patient_id    |          |    phone (UQ) |13                             +---------------+          |    slot_start    |          +---------------+14                                                        | UQ (doctor_id,   |15                                                        |     slot_start)  |16                                                        +------------------+

Design notes

Entities: departments, doctors, patients, appointments.
Department 1:N doctor (FK in doctors). Doctor M:N patient through appointments.
The pair (doctor, patient) can repeat on different days, so appointments has its own id.
The rule "no two appointments in one slot" becomes UNIQUE (doctor_id, slot_start).
"Found by phone" makes phone a natural key: UNIQUE.

What this code does

Every sentence of the requirement became something in the diagram: an entity, a line with marks, or a unique key.
A patient can see the same doctor many times, so (doctor_id, patient_id) cannot be the key; the real "never twice" rule is one doctor in one slot.

Example 2 · The OPD schema, with its rule enforced

SQL

1CREATE TABLE opd_departments (2  dept_id INT AUTO_INCREMENT PRIMARY KEY,3  name    VARCHAR(40) NOT NULL UNIQUE4);5CREATE TABLE opd_doctors (6  doctor_id INT AUTO_INCREMENT PRIMARY KEY,7  name      VARCHAR(40) NOT NULL,8  dept_id   INT         NOT NULL,9  FOREIGN KEY (dept_id) REFERENCES opd_departments (dept_id)10);11CREATE TABLE opd_patients (12  patient_id INT AUTO_INCREMENT PRIMARY KEY,13  name       VARCHAR(40) NOT NULL,14  phone      VARCHAR(10) NOT NULL UNIQUE15);16CREATE TABLE appointments (17  appt_id    INT AUTO_INCREMENT PRIMARY KEY,18  doctor_id  INT      NOT NULL,19  patient_id INT      NOT NULL,20  slot_start DATETIME NOT NULL,21  UNIQUE (doctor_id, slot_start),          -- one patient per doctor per slot22  FOREIGN KEY (doctor_id)  REFERENCES opd_doctors (doctor_id),23  FOREIGN KEY (patient_id) REFERENCES opd_patients (patient_id)24);25
26INSERT INTO opd_departments (name) VALUES ('Cardiology'), ('Orthopaedics');27INSERT INTO opd_doctors (name, dept_id) VALUES ('Dr Mehra', 1), ('Dr Pillai', 2);28INSERT INTO opd_patients (name, phone) VALUES29  ('Anand', '9000000001'), ('Bushra', '9000000002'), ('Charu', '9000000003');30INSERT INTO appointments (doctor_id, patient_id, slot_start) VALUES31  (1, 1, '2026-11-02 10:00:00'),32  (1, 2, '2026-11-02 10:15:00'),33  (2, 3, '2026-11-02 10:00:00');34
35-- Today's list per department and doctor36SELECT d.name AS department, doc.name AS doctor, p.name AS patient,37       TIME_FORMAT(a.slot_start, '%H:%i') AS slot38FROM appointments a39JOIN opd_doctors doc    ON doc.doctor_id = a.doctor_id40JOIN opd_departments d  ON d.dept_id = doc.dept_id41JOIN opd_patients p     ON p.patient_id = a.patient_id42WHERE DATE(a.slot_start) = '2026-11-02'43ORDER BY d.name, a.slot_start;44
45-- Double booking Dr Mehra at 10:00 is refused46INSERT INTO appointments (doctor_id, patient_id, slot_start)47VALUES (1, 3, '2026-11-02 10:00:00');

Output

+--------------+-----------+---------+-------+
| department   | doctor    | patient | slot  |
+--------------+-----------+---------+-------+
| Cardiology   | Dr Mehra  | Anand   | 10:00 |
| Cardiology   | Dr Mehra  | Bushra  | 10:15 |
| Orthopaedics | Dr Pillai | Charu   | 10:00 |
+--------------+-----------+---------+-------+

ERROR 1062 (23000): Duplicate entry '1-2026-11-02 10:00:00' for key 'appointments.doctor_id'

What this code does

Dr Pillai can see Charu at 10:00 while Dr Mehra sees Anand at 10:00: the unique key is on the doctor and slot together, not on the slot alone.
The last insert breaks UNIQUE (doctor_id, slot_start) and fails with error 1062, even if two receptionists try at once. The database, not the app screen, owns the rule.

Sentences such as "a doctor cannot see two patients at the same time" are constraints in disguise. Find them in the requirement and make the database enforce them.

Find and fix normalisation problems

A review asks four questions in order. The queries below run them with SQL on a hostel fee sheet, and the answers point to the fix.

Example 3 · Reviewing a hostel fee sheet with SQL

SQL

1CREATE TABLE hostel_fee_sheet (2  receipt_no INT PRIMARY KEY,3  roll_no    VARCHAR(10) NOT NULL,4  name       VARCHAR(30) NOT NULL,5  room_no    VARCHAR(5)  NOT NULL,6  block      CHAR(1)     NOT NULL,7  warden     VARCHAR(30) NOT NULL,8  month      CHAR(7)     NOT NULL,9  amount     INT         NOT NULL10);11INSERT INTO hostel_fee_sheet VALUES12  (501, '22CS041', 'Nikhil', 'A-101', 'A', 'Mr Rao',    '2026-07', 6500),13  (502, '22EC007', 'Sana',   'B-204', 'B', 'Ms Thomas', '2026-07', 6500),14  (503, '22CS041', 'Nikhil', 'A-101', 'A', 'Mr Rao',    '2026-08', 6500),15  (504, '23ME112', 'Varun',  'A-101', 'A', 'Mr Rao',    '2026-08', 6500),16  (505, '22EC007', 'Sana',   'B-204', 'B', 'Ms Thomas', '2026-08', 6500);17
18-- How often is each fact repeated?19SELECT 'roll_no -> name, room_no' AS dependency, COUNT(*) - COUNT(DISTINCT roll_no) AS extra_copies20FROM hostel_fee_sheet21UNION ALL22SELECT 'room_no -> block', COUNT(*) - COUNT(DISTINCT room_no) FROM hostel_fee_sheet23UNION ALL24SELECT 'block -> warden', COUNT(*) - COUNT(DISTINCT block) FROM hostel_fee_sheet;25
26-- Does each dependency hold in the data? (0 = no row breaks it)27SELECT28  (SELECT COUNT(*) FROM (SELECT roll_no FROM hostel_fee_sheet GROUP BY roll_no29                         HAVING COUNT(DISTINCT room_no) > 1) x) AS roll_breaks,30  (SELECT COUNT(*) FROM (SELECT block FROM hostel_fee_sheet GROUP BY block31                         HAVING COUNT(DISTINCT warden) > 1) y) AS block_breaks;

Output

+--------------------------+--------------+
| dependency               | extra_copies |
+--------------------------+--------------+
| roll_no -> name, room_no |            2 |
| room_no -> block         |            3 |
| block -> warden          |            3 |
+--------------------------+--------------+
+-------------+--------------+
| roll_breaks | block_breaks |
+-------------+--------------+
|           0 |            0 |
+-------------+--------------+

What this code does

The key is receipt_no, yet the student, room and warden facts are stored again on every receipt: 2 extra copies of student details, 3 of room facts and 3 of wardens in only five rows.
The data agrees with the rules (0 breaks), so the fix is a design one: fee_payments(receipt_no, roll_no, month, amount), residents(roll_no, name, room_no), rooms(room_no, block) and blocks(block, warden). The chain roll_no → room_no → block → warden is two transitive dependencies.

Does a cell hold a list, or are there numbered columns? Not 1NF: move the values to a child table.
Is the key composite, and does a column depend on only one part of it? Not 2NF: new table keyed by that part.
Does a non-key column decide another non-key column (pincode → city)? Not 3NF: new table keyed by it.
Is a column computable from others (age, grade, total)? Do not store it, unless you denormalise on purpose with a plan.

On real data, GROUP BY x HAVING COUNT(DISTINCT y) > 1 finds broken dependencies quickly. A result row means the same x already has two different y values.

Choose relationships and keys

A key is where a design stops bad data. The script below uses a composite key so one doctor cannot be booked twice in the same slot.

Example 4 · Choosing the key that stops double booking

SQL

1CREATE TABLE shows (2  show_id   INT AUTO_INCREMENT PRIMARY KEY,3  film      VARCHAR(40) NOT NULL,4  starts_at DATETIME    NOT NULL5);6CREATE TABLE seats (7  seat_id  INT AUTO_INCREMENT PRIMARY KEY,8  seat_row CHAR(1) NOT NULL,9  seat_no  TINYINT NOT NULL,10  UNIQUE (seat_row, seat_no)11);12CREATE TABLE seat_bookings (13  booking_id INT AUTO_INCREMENT PRIMARY KEY,14  show_id    INT         NOT NULL,15  seat_id    INT         NOT NULL,16  customer   VARCHAR(30) NOT NULL,17  UNIQUE (show_id, seat_id),               -- a seat once per show18  FOREIGN KEY (show_id) REFERENCES shows (show_id),19  FOREIGN KEY (seat_id) REFERENCES seats (seat_id)20);21
22INSERT INTO shows (film, starts_at) VALUES23  ('Dangal', '2026-11-07 18:30:00'), ('Dangal', '2026-11-07 21:45:00');24INSERT INTO seats (seat_row, seat_no) VALUES ('A', 1), ('A', 2), ('B', 1);25INSERT INTO seat_bookings (show_id, seat_id, customer) VALUES26  (1, 1, 'Pallavi'), (1, 2, 'Pallavi'), (2, 1, 'Joseph');27
28-- Free seats for show 129SELECT CONCAT(s.seat_row, s.seat_no) AS free_seat30FROM seats s31LEFT JOIN seat_bookings b ON b.seat_id = s.seat_id AND b.show_id = 132WHERE b.booking_id IS NULL33ORDER BY s.seat_row, s.seat_no;34
35-- A second booking of A1 for show 1 is refused36INSERT INTO seat_bookings (show_id, seat_id, customer) VALUES (1, 1, 'Rehan');

Output

+-----------+
| free_seat |
+-----------+
| B1        |
+-----------+

ERROR 1062 (23000): Duplicate entry '1-1' for key 'seat_bookings.show_id'

What this code does

The rule "a seat can be sold once per show" becomes UNIQUE (show_id, seat_id). Seat A1 is booked for both shows, which is fine, because the pairs differ.
The free-seat query puts the show filter inside the LEFT JOIN condition, so seats with no booking for show 1 survive with NULL. Rehan's insert for the taken seat fails with error 1062.

Fix the error

Fix the key

The second insert fails. Pick the key line that fixes the table.

Round 1 of 2

One doctor must have many slots. Which key line fixes appointments?

CREATE TABLE appointments (

doctor_id INT NOT NULL,

slot_start DATETIME NOT NULL,

patient_id INT NOT NULL,

PRIMARY KEY (doctor_id)

);

INSERT INTO appointments VALUES (1, '2026-03-02 10:00:00', 7);

INSERT INTO appointments VALUES (1, '2026-03-02 10:30:00', 9);

Error

ERROR 1062 (23000): Duplicate entry '1' for key 'appointments.PRIMARY'

Mixed review

A design review looks at a whole schema the way an interviewer does. The write-up below lists the problems found in a submitted online store schema and the fix for each.

Example 5 · A design review, written up

Text

1Proposed schema for an online store (as submitted):23products(product_id PK, name, brand, tags)                 -- 'Mobiles, 5G'4reviews(product_id, reviewer, rating, product_name)        -- no primary key5orders(order_id PK, customer_phone, customer_name,6       item1, qty1, item2, qty2, total)7couriers(courier_id PK, name, current_order_id)

Review findings

1. products.tags is a list: tags table + product_tags join table (1NF).
2. reviews has no primary key, and product_name depends on product_id only: add review_id PK, drop product_name (2NF/3NF).
3. orders.item1/qty1/item2/qty2 is a repeating group: order_items(order_id, product_id, quantity, unit_price) (1NF).
4. customer_name depends on customer_phone: a customers table keyed by phone or an id (3NF).
5. total is derived from order_items: compute it, or keep it with a trigger and a check query.
6. couriers.current_order_id puts the link on the wrong side: orders.courier_id (nullable) instead.

What this code does

Each finding names the problem, the rule it breaks and the fix, in the order of the review questions.
Finding 6 is a relationship mistake, not a normal-form one: a courier delivers many orders over time, so the foreign key belongs on the many side, orders.

A list in a cell, or item1, item2, item3 columns.
A foreign key on the wrong side of a one-to-many link, or a missing join table for many-to-many.
A "one-to-one" or "one per slot" rule with no UNIQUE behind it.
Stored derived values (age, grade, totals) with no plan to keep them correct.

Module quiz

Questions from the whole module. Your first answer is your score.

Pick an answer to see if it is right. 0/12 answered.

Question 1 of 12

A requirement says "each bus has many seats; a seat can be reserved once per trip". Which constraint enforces the second part?

Question 2 of 12

Which table is not in 2NF?

Question 3 of 12

Which design puts the foreign key in the right place for "a courier delivers many orders; each order has at most one courier"?

Question 4 of 12

What does this query return?

Python

1-- seat_bookings (show 1 has seats A1 and A2 booked; show 2 has A1)2-- seats: A1, A2, B13SELECT COUNT(*) AS free_for_show_24FROM seats s5LEFT JOIN seat_bookings b ON b.seat_id = s.seat_id AND b.show_id = 26WHERE b.booking_id IS NULL;

Question 5 of 12

Same tables. What does this version return?

Python

SELECT COUNT(*) AS free_for_show_2FROM seats sLEFT JOIN seat_bookings b ON b.seat_id = s.seat_idWHERE b.show_id = 2 AND b.booking_id IS NULL;

Question 6 of 12

What does this query return?

Python

1-- fee_sheet2-- +------------+---------+-------+--------+3-- | receipt_no | roll_no | block | warden |4-- +------------+---------+-------+--------+5-- |        501 | 22CS041 | A     | Mr Rao |6-- |        502 | 22EC007 | B     | Ms Tom |7-- |        503 | 22CS041 | A     | Mr Rao |8-- |        504 | 23ME112 | A     | Mr Rao |9-- +------------+---------+-------+--------+10SELECT block, COUNT(*) AS warden_copies11FROM fee_sheet12GROUP BY block13ORDER BY block;

Question 7 of 12

What does the last statement do?

Python

1CREATE TABLE appointments (2  appt_id    INT AUTO_INCREMENT PRIMARY KEY,3  doctor_id  INT      NOT NULL,4  patient_id INT      NOT NULL,5  slot_start DATETIME NOT NULL,6  UNIQUE (doctor_id, slot_start)7);8INSERT INTO appointments (doctor_id, patient_id, slot_start)9VALUES (1, 1, '2026-11-02 10:00:00'), (2, 3, '2026-11-02 10:00:00');10INSERT INTO appointments (doctor_id, patient_id, slot_start)11VALUES (1, 3, '2026-11-02 10:00:00');

Question 8 of 12

In a review you find `employees(emp_id PK, name, ifsc, bank_name)`. What is the finding?

Question 9 of 12

A patient visits the same doctor every month. Which key for `visits(patient_id, doctor_id, visit_date, fee)` allows that?

Question 10 of 12

Which column should usually not be stored?

Question 11 of 12

An app lets a user follow other users (like social media). Which design fits?

Question 12 of 12

A team stores `delivery_address` on each order, copied from the customer's current address. A reviewer calls it redundancy. What is the best reply?

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

Expected output

(a) 1:1, capital_city_id UNIQUE in countries (or country_id UNIQUE in cities)
(b) 1:N, bank_id in branches
(c) M:N, join table movie_genres(movie_id, genre_id)
(d) M:N, join table group_members(group_id, member_id)

Exercise 2· Easy

SQL

-- clubs: 'Robotics', 'Music'-- Robotics: Aditya Joshi (2025-08-01), Chirag Patel (2025-08-03)-- Music: Aditya Joshi (2025-08-10)

Expected output

+----------+--------------+------------+
| club     | student      | joined_on  |
+----------+--------------+------------+
| Music    | Aditya Joshi | 2025-08-10 |
| Robotics | Aditya Joshi | 2025-08-01 |
| Robotics | Chirag Patel | 2025-08-03 |
+----------+--------------+------------+

Exercise 3· Medium

Tour guides store their languages as a list. Move them to guide_languages(guide_id, language) by inserting the rows by hand, then list the guides who speak Tamil.

SQL

1CREATE TABLE guides (2  guide_id  INT PRIMARY KEY,3  name      VARCHAR(30)  NOT NULL,4  languages VARCHAR(100) NOT NULL5);6INSERT INTO guides VALUES7  (1, 'Murugan', 'Tamil, English'),8  (2, 'Fatima',  'Urdu, Hindi, English'),9  (3, 'Selvi',   'Tamil, Malayalam');

Expected output

+---------+
| name    |
+---------+
| Murugan |
| Selvi   |
+---------+

Exercise 4· Medium

SQL

1CREATE TABLE offerings_v1 (2  semester     CHAR(7)     NOT NULL,   -- e.g. '2026-S1'3  course_code  CHAR(5)     NOT NULL,4  course_title VARCHAR(40) NOT NULL,   -- course_code -> course_title5  faculty_id   INT         NOT NULL,6  faculty_name VARCHAR(30) NOT NULL,   -- faculty_id -> faculty_name7  PRIMARY KEY (semester, course_code)8);9INSERT INTO offerings_v1 VALUES10  ('2026-S1', 'CS201', 'Data Structures',  7, 'Dr Kulkarni'),11  ('2026-S1', 'CS202', 'Database Systems', 9, 'Dr Fernandes'),12  ('2026-S2', 'CS201', 'Data Structures',  9, 'Dr Fernandes');

Expected output

+----------+------------------+--------------+
| semester | title            | faculty      |
+----------+------------------+--------------+
| 2026-S1  | Data Structures  | Dr Kulkarni  |
| 2026-S1  | Database Systems | Dr Fernandes |
| 2026-S2  | Data Structures  | Dr Fernandes |
+----------+------------------+--------------+

Exercise 5· Medium

SQL

-- books: 'Wings of Fire', 'Godan', 'The Guide'-- loans: Wings of Fire to Ishaan on 2026-10-01, returned 2026-10-10--        Godan to Meher on 2026-10-05, not returned--        Wings of Fire to Tanya on 2026-10-12, not returned

Expected output

+---------------+--------+------------+
| title         | member | issued_on  |
+---------------+--------+------------+
| Godan         | Meher  | 2026-10-05 |
| Wings of Fire | Tanya  | 2026-10-12 |
+---------------+--------+------------+

Exercise 6· Medium

SQL

-- departments: Cardiology, Orthopaedics, ENT-- doctors: Dr Mehra (Cardiology), Dr Pillai (Orthopaedics), Dr Sen (ENT)-- patients: Anand 9000000001, Bushra 9000000002-- appointments on 2026-11-02:--   Dr Mehra 10:00 Anand, Dr Mehra 10:15 Bushra, Dr Pillai 10:00 Bushra

Expected output

+-----------+--------------+
| doctor    | appointments |
+-----------+--------------+
| Dr Mehra  |            2 |
| Dr Pillai |            1 |
| Dr Sen    |            0 |
+-----------+--------------+

ERROR 1062 (23000): Duplicate entry '2-2026-11-02 10:00:00' for key 'appointments.doctor_id'

Exercise 7· Hard

SQL

-- users: Asha, Bilal, Chitra-- bill 1: dinner, paid by Asha, 1200.00 -> Asha 400, Bilal 400, Chitra 400-- bill 2: cab,    paid by Bilal, 450.00 -> Asha 150, Bilal 300

Expected output

+---------+--------+---------+--------------+----------+
| bill_id | title  | amount  | shares_total | balanced |
+---------+--------+---------+--------------+----------+
|       1 | dinner | 1200.00 |      1200.00 |        1 |
|       2 | cab    |  450.00 |       450.00 |        1 |
+---------+--------+---------+--------------+----------+
+--------+--------+
| name   | owes   |
+--------+--------+
| Asha   | 150.00 |
| Bilal  | 400.00 |
| Chitra | 400.00 |
+--------+--------+

Exercise 8· Hard

Expected output

+-----------+
| free_seat |
+-----------+
|         2 |
|         4 |
+-----------+

Interview questions

Questions interviewers ask on this topic.

7 questions. Answer out loud first, then compare.

Finish up

Mark complete & continue

Next: Mini project: courier delivery database

Design from short requirements

What this code does

What this code does

Find and fix normalisation problems

What this code does

Choose relationships and keys

What this code does

Mixed review

What this code does

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Design from short requirements

What this code does

What this code does

Find and fix normalisation problems

What this code does

Choose relationships and keys

What this code does

Mixed review

What this code does

A requirement says "each bus has many seats; a seat can be reserved once per trip". Which constraint enforces the second part?

Which table is not in 2NF?

Which design puts the foreign key in the right place for "a courier delivers many orders; each order has at most one courier"?

What does this query return?

Same tables. What does this version return?

What does this query return?

What does the last statement do?

In a review you find employees(emp_id PK, name, ifsc, bank_name). What is the finding?

A patient visits the same doctor every month. Which key for visits(patient_id, doctor_id, visit_date, fee) allows that?

Which column should usually not be stored?

An app lets a user follow other users (like social media). Which design fits?

A team stores delivery_address on each order, copied from the customer's current address. A reviewer calls it redundancy. What is the best reply?

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Q1Walk me through how you design a database from a requirement.

Q2How do you stop two people booking the same cinema seat for the same show?

Q3You see a table orders(order_id, item1, qty1, item2, qty2, item3, qty3). What do you say?

Q4When does a join table need its own id instead of a composite key?

Q5How do you review an existing schema quickly?

Q6Design a follow feature like on social media.

Q7What is the difference between a design mistake and a data mistake?

A requirement says "each bus has many seats; a seat can be reserved once per trip". Which constraint enforces the second part?

Which table is not in 2NF?

Which design puts the foreign key in the right place for "a courier delivers many orders; each order has at most one courier"?

What does this query return?

Same tables. What does this version return?

What does this query return?

What does the last statement do?

In a review you find employees(emp_id PK, name, ifsc, bank_name). What is the finding?

A patient visits the same doctor every month. Which key for visits(patient_id, doctor_id, visit_date, fee) allows that?

Which column should usually not be stored?

An app lets a user follow other users (like social media). Which design fits?

A team stores delivery_address on each order, copied from the customer's current address. A reviewer calls it redundancy. What is the best reply?

Q1Walk me through how you design a database from a requirement.

Q2How do you stop two people booking the same cinema seat for the same show?

Q3You see a table orders(order_id, item1, qty1, item2, qty2, item3, qty3). What do you say?

Q4When does a join table need its own id instead of a composite key?

Q5How do you review an existing schema quickly?

Q6Design a follow feature like on social media.

Q7What is the difference between a design mistake and a data mistake?

In a review you find `employees(emp_id PK, name, ifsc, bank_name)`. What is the finding?

A patient visits the same doctor every month. Which key for `visits(patient_id, doctor_id, visit_date, fee)` allows that?

A team stores `delivery_address` on each order, copied from the customer's current address. A reviewer calls it redundancy. What is the best reply?

In a review you find `employees(emp_id PK, name, ifsc, bank_name)`. What is the finding?

A patient visits the same doctor every month. Which key for `visits(patient_id, doctor_id, visit_date, fee)` allows that?

A team stores `delivery_address` on each order, copied from the customer's current address. A reviewer calls it redundancy. What is the best reply?