Part VIII — Laboratory Exercises and Projects

Chapter 29. Relational Database Case Studies

Eight case studies put the whole book to work. Each follows the full cycle the TOC promised — requirements, ER diagrams, relational schemas, normalization, SQL implementation, test data, queries, and validation — in a different domain, and each is designed to be worked, not read: the deliverables section of each study is an assignment. The university system (29.1) is the book's running example, summarized; the other seven are new domains chosen to exercise different design muscles — weak entities, audit-grade referential actions, deliberate redundancy, self-references, temporal constraints, and bridge-table analysis.

The method in every case is the book's method: Chapter 5's nouns-and-arrows, Chapter 6's mapping rules, Chapter 7's anomaly check, Chapter 9's constraint-first DDL, and Chapter 28's predict-run-verify on the test data. Schema sketches are given in compact form (tables with keys and actions); full DDL is your deliverable.

After working these case studies you will be able to:

• Run the complete design cycle unaided in eight different domains.
• Choose referential actions per business meaning, not habit.
• Defend one deliberate redundancy per case where the domain demands it.
• Specify deterministic test data and validate implementations against it.
• Write the queries each domain's users actually ask.

29.1 University student information system

Requirements. Departments, students, instructors, courses, sections, enrollments; one grade per enrollment; seats per section; transcripts, departmental course lists, instructor workload, seat availability.

ERD and schema. The book's running example (Chapters 5–9): DEPARTMENT offers COURSE (1:N); DEPARTMENT majors STUDENT; INSTRUCTOR teaches COURSE_SECTION; COURSE runs COURSE_SECTION; STUDENT enrolls-in COURSE_SECTION (M:N) with attribute grade. The relational schema is the canonical six tables of Appendix H — the mapping rules of Chapter 6 applied verbatim, including the enrollment bridge with composite PK.

Normalization. The schema is BCNF; Chapter 7 normalized it from a report table, and its design history is the book's design curriculum.

Implementation, data, queries, validation. The canonical DDL, dataset (5, 6, 12, 10, 13, 28), the Chapter 12 query library, and the six-count verification. The extension assignment: add waitlist, prerequisites, and meetings (the Chapter 5 mini-project's EERD) with the Chapter 28 Lab 2 actions.

Deliverables: the extension's DDL with referential actions justified per FK; three new queries with predicted outputs; the parity note of Chapter 28 Lab 10 applied to the extension.

29.2 Library management system

Requirements. Books with ISBNs; several physical copies per book; members may hold several copies at once and many over time; loans track due and return dates; overdue loans accrue daily fines; reports on overdue lists and popular titles.

ERD. BOOK is strong (ISBN — a good natural key, Chapter 6); COPY is weak under BOOK (discriminator copy_no — the identifying key is (book_id, copy_no)); MEMBER is strong; LOAN resolves the MEMBER–COPY M:N (with due/returned attributes); FINE hangs 1:N off LOAN (audit line: RESTRICT — fines never lose their loan).

 BOOK ──< COPY >── LOAN ──< FINE
             MEMBER ──┘

Schema and normalization. book(book_id PK, isbn UNIQUE, title, author, year), copy(book_id, copy_no, status; PK(book_id, copy_no), FK book ON DELETE CASCADE), member(member_id PK, name, joined_at), loan(loan_id PK, member_id FK, book_id, copy_no FK→copy, borrowed_at, due_at, returned_at; UNIQUE(member_id, book_id, copy_no, borrowed_at)), fine(fine_id PK, loan_id FK RESTRICT, days_late, amount CHECK(amount >= 0)). BCNF throughout; the weak entity and the bridge are the teaching points.

Test data. 8 books, 12 copies, 6 members, 10 loans (2 open/overdue), 2 fines.

Queries. (1) Overdue list: open loans past due, with member and title (join + IS NULL on returned_at + date comparison). (2) Most-borrowed titles: COUNT over loans grouped by book, ORDER BY ... DESC LIMIT 3 — with the fan-out rule applied if authors join in.

Validation. The UNIQUE loan key prevents duplicate identical loans; a RETURN is an UPDATE (returned_at set) with a fine INSERT in one transaction — the Chapter 18 unit; the cascade rule: withdrawn books take their copies, copies RESTRICT their open loans (a book with open loans cannot be withdrawn — resolve loans first).

29.3 Hospital management system

Requirements. Patients visit doctors; each visit records diagnosis and fee; visits may issue prescriptions (drug, dose, days); patient histories must survive; prescription records are legal documents.

ERD and schema. PATIENT and DOCTOR (PERSON subtypes in a fuller model — Chapter 5's specialization), VISIT (patient FK, doctor FK, visit_at, diagnosis, fee), PRESCRIPTION (visit FK RESTRICT, drug, dose, days). visit(visit_id PK, patient_id FK CASCADE?, ...) — the audit asymmetry is the case's lesson: a patient's visits cascade only with an explicit archival policy (Chapter 6's audit line says RESTRICT here too, with a deletion-by-retention design instead), while a doctor's departure must never delete visits (doctor_id is not even deletable — deactivate).

Normalization. BCNF; the deliberate consideration: doctor specialty lives on DOCTOR, not VISIT (temporal correctness: the specialty as it was — a candidate Type-2 dimension, Chapter 25).

Test data. 6 patients, 4 doctors, 12 visits, 9 prescriptions across them.

Queries. (1) A patient's full history (visits + prescriptions, chronological — LEFT JOIN so prescription-free visits appear). (2) Doctor workload and revenue by month (GROUP BY with EXTRACT/TIMESTAMPDIFF — the Chapter 17 function map).

Validation. The RESTRICT pair (prescription→visit, visit→patient) enforced by attempted deletes; the fee CHECK (fee >= 0); the deletion-policy paragraph — what "delete a patient" means legally, written as schema decisions.

29.4 Banking and financial transaction system

Requirements. Customers own accounts; accounts have balances; transactions (deposits, withdrawals, transfers) are append-only; no negative balances; statements; daily totals.

ERD and schema. CUSTOMER, ACCOUNT (customer FK, type, balance CHECK (balance >= 0)), txn(txn_id PK, account_id FK, kind CHECK (deposit/withdrawal/transfer), amount CHECK (amount > 0), counter_account_id NULL, posted_at, balance_after). The design decisions that make it a case study: append-only ledger (no UPDATEs — the history is the table; the balance is either maintained transactionally or derived), balance_after as a deliberate, auditable redundancy, and the transfer as one transaction touching two accounts.

Normalization and the ACID showcase. The ledger is BCNF; the transfer is Chapter 18's whole subject in one procedure: BEGIN; UPDATE both balances (or INSERT both ledger rows); check constraints at COMMIT; deadlock retry in the application — the Chapter 22/23 pattern, on the one domain where everyone accepts it is mandatory.

Test data. 5 customers, 7 accounts, 20 transactions including 3 transfers; balances reconciling (sum of ledger = balances).

Queries. (1) A monthly statement (account's transactions in a period, opening/closing balance — window functions). (2) Daily totals by kind (GROUP BY date + kind — the Chapter 11/13 machinery).

Validation. The reconciliation query (ledger sum = balance sum — a standing invariant, checked as a test); an attempted overdraw (CHECK rejection); a concurrent transfer pair (retry observed).

29.5 E-commerce and inventory management

Requirements. Customers browse a catalog; orders contain lines with quantities; the price at time of order is historical; stock decrements on order; revenue and product reports.

ERD and schema. CUSTOMER, PRODUCT (sku natural key, price current, stock_qty), orders(order_id PK, customer_id FK, placed_at, status), order_line(order_id, product_id, quantity, unit_price; PK(order_id, product_id), FKs orders CASCADE, product RESTRICT) — the canonical deliberate redundancy: unit_price is copied at order time (Chapter 6's temporal-correctness case), and stock is maintained in the order transaction with a guard (stock_qty >= quantity — optimistic or FOR UPDATE, the Chapter 18 patterns).

Test data. 8 products, 5 customers, 10 orders, 22 lines; stock figures reconciling after the order set.

Queries. (1) Monthly revenue (SUM over lines with the historical price — the redundancy earning its keep as the price changes). (2) Top products by units and revenue (GROUP BY, ORDER BY, LIMIT — and the fan-out rule if categories join).

Validation. Change a product's price and prove old orders keep theirs; attempt an over-stock order (guard rejection); the stock reconciliation query.

29.6 Employee and payroll system

Requirements. Employees belong to departments, report to managers (also employees); salary grades define bands; payroll runs monthly, recording per-employee gross and deductions; history matters (who earned what, when).

ERD and schema. employee(emp_id PK, name, dept_id FK, manager_id FK→employee NULL, grade_id FK, hired_at), department(dept_id PK, ...), salary_grade(grade_id PK, min_salary, max_salary CHECK(min < max)), payslip(pay_period, emp_id, gross, deductions, net; PK(pay_period, emp_id)). The teaching points: the self-referencing FK (manager_id — Chapter 6), the grade band constraint, and payslip's composite PK making re-running a period an upsert, not a duplicate.

Test data. 10 employees in 3 departments with a 3-level management tree, 4 grades, 2 periods of payslips (20 rows).

Queries. (1) The org chart via recursive CTE (manager → reports, with depth — Chapter 12's WITH RECURSIVE on its home turf). (2) The payroll register per period with grade validation (net = gross − deductions, and gross within the grade band — a query that is a control).

Validation. Band violations rejected; a circular-management attempt (employee A manages B manages A — impossible by update order, but detect by the recursive CTE with a cycle guard); period re-run idempotent (the composite PK's upsert).

29.7 Hotel reservation system

Requirements. Guests book rooms for date ranges; bookings must not overlap for the same room; rates vary by room; occupancy and revenue reports; free rooms on a date.

ERD and schema. GUEST, room(room_no PK, type, rate), booking(booking_id PK, guest_id FK, room_no FK, check_in, check_out, CHECK (check_out > check_in), status). The case's headline is the temporal constraint — no two active bookings overlap for one room. PostgreSQL's declarative answer is the star: EXCLUDE USING gist (room_no WITH =, daterange(check_in, check_out) WITH &&) WHERE (status = 'confirmed') — an exclusion constraint (Chapter 15's GiST, doing temporal work). MySQL's answer is procedural: a BEFORE INSERT trigger querying for overlaps (or an application-side check inside the booking transaction).

Test data. 8 rooms, 6 guests, 12 bookings (with one deliberate overlap attempt), 60% occupancy over a sample week.

Queries. (1) Free rooms on a date (anti-join: rooms with no active booking covering the date — Chapter 12's NOT EXISTS, temporally shaped). (2) Occupancy and revenue by week (GROUP BY week — window/period machinery).

Validation. The overlap attempt rejected by the exclusion constraint (or trigger), with the error captured; the free-rooms cross-check against the occupancy report (the two queries must agree — a validation query that validates queries).

29.8 Online examination system

Requirements. Exams contain questions with points; students attempt exams, once per exam (a stated policy — relax it to multiple attempts and the attempt key changes); answers are graded per question; scores, ranks, and item analysis (which questions were hard) are reported.

ERD and schema. exam(exam_id PK, title, duration), question(exam_id, question_no, text, points; PK(exam_id, question_no)), student(...) — reuse a person table, attempt(attempt_id PK, student_id FK, exam_id FK, started_at, submitted_at NULL, CHECK (attempt-window), UNIQUE(student_id, exam_id)), answer(attempt_id, question_no FK→question composite, response, is_correct, points_awarded; PK(attempt_id, question_no)). The teaching points: the composite FK across two columns (answer → question needs (exam_id, question_no), so attempt carries exam_id too — the composite-key propagation Chapter 6 warned about), and the denormalized-but-derived is_correct/points_awarded (grade-time facts, audit-kept).

Test data. 3 exams, 24 questions, 8 students, 20 attempts, 150 answers with a known score distribution.

Queries. (1) Score report with rank (SUM(points_awarded) per attempt, RANK() — ties possible, Chapter 13). (2) Item analysis: per-question correctness rate (AVG(is_correct::int) or the MySQL CASE idiom) — the teacher's query, and a conditional-aggregation showcase.

Validation. The score cross-check (attempt total = SUM of its answers — reconciliation as a standing test); double-submission rejected (the UNIQUE); points-awarded ≤ points CHECK.

Chapter Summary

• Eight domains, one cycle each: requirements → ERD → mapping → normalization → DDL → test data → queries → validation — the book's method, end to end.
• The library exercises weak entities (copy under book) and RESTRICT on audit lines; the hospital makes referential actions legal decisions and specialty a Type-2 candidate.
• The bank is the ACID showcase: an append-only ledger, balance_after as auditable redundancy, transfers as Chapter 18/22 transactions with retry.
• The e-commerce case plants the deliberate historical price and the stock guard; the payroll case walks self-references, grade bands, and idempotent period keys.
• The hotel case's overlap constraint is the temporal star: PostgreSQL's EXCLUDE (GiST) versus MySQL's procedural guard.
• The exam case propagates composite keys across tables and lands item analysis as conditional aggregation.
• Every case's validation is reconciliation: standing queries that assert the invariants, plus the rejected illegal operations as evidence.

Key Terms

Review Questions and Exercises

1. Why is COPY weak and LOAN strong in the library case? Copies are numbered within books (no independent identity); loans have their own loan_id and exist independent of any single owner relationship.
2. Which two decisions in the hospital case are legal rather than technical, and how do they reach the schema? Prescriptions survive their visits (RESTRICT) and patients are deactivated, not deleted (retention policy) — reaching the schema as referential actions and a status column.
3. Why is the bank's ledger append-only, and what does balance_after buy? History is the record — UPDATEs would destroy it; balance_after makes each row self-auditing (the running state, provable without replay).
4. The e-commerce unit_price is stored though products carry prices. State the FD it violates and the business rule that justifies it. A transitive dependency (order → product → price) — justified by temporal correctness: the order must remember the price at sale time.
5. How does the payroll case make re-running a pay period safe? The composite PK (pay_period, emp_id) turns the re-run into an upsert — no duplicate slips.
6. Write the PostgreSQL answer and the MySQL answer to "no overlapping confirmed bookings per room." PG: EXCLUDE USING gist (room_no WITH =, daterange(...) WITH &&) WHERE status='confirmed'; MySQL: a BEFORE INSERT trigger (or transaction-guarded check) querying for overlapping active ranges.
7. Why must the exam's answer table carry exam_id? Its FK to question is composite (exam_id, question_no) — the composite key propagates through the referencing table (Chapter 6's cost, in practice).
8. In which two cases does a reconciliation query serve as validation, and what does each assert? Banking (ledger totals = balances) and exams (attempt score = SUM of answers) — standing invariant tests.
9. Which case makes the fan-out rule bite hardest, and why? The library's popular-titles report (loans × copies × books × authors) or e-commerce's revenue by category — joins through 1:N children multiply rows and demand COUNT(DISTINCT).
10. Why does the hotel case validate its free-rooms query against the occupancy report? Two independent phrasings of the same truth must agree — the query that validates queries, catching boundary-condition disagreements (check-in day, check-out day).
11. Which case would you hand a new team member first, and why? The library — weak entity, bridge, audit action, and reports, with the smallest domain vocabulary; every mapping rule appears once.
12. Name the one deliberate redundancy you would add to the university extension, with its justification. e.g., capacity_snapshot on waitlist rows (the seat count when queued — temporal correctness for fairness disputes), or enrolled_count on section (guarded by trigger, for fast availability) — any one, with the temporal/audit rule stated.

Mini-Project

Choose two case studies (not 29.1) and deliver each completely, as production-shaped artifacts: (1) the requirements specification (one page, with the constraint rules as rules); (2) the ERD (any notation, with cardinality and participation marked); (3) the mapping worksheet — every table, key, FK, and referential action with one-line justifications; (4) the normalization paragraph — the form you stopped at, the one deliberate redundancy, and its defense; (5) full DDL (both platforms — the Chapter 17 type map applied) with named constraints; (6) the deterministic test-data script with its documented counts; (7) the query suite — at least six queries, each stated in English with predicted outputs before running; (8) the validation script — the reconciliation queries, the five illegal operations each with its expected rejection, and the parity run on both platforms. Deliver as case_<n>/ with schema.sql, data.sql, queries.sql, validation.sql, and a README that a grader can follow without asking you anything. These two cases are half of Chapter 30's evidence base; build them as though the capstone's grade depends on them, because it does.