Part 6 of 6 · 4 min read
Exercises
Easy
Easy 1: Find the redundancy (15 minutes)
A school keeps students.txt (name, grade, home address) and library_loans.txt (student name, student address, book title, due date). Name every piece of data that is stored in more than one file, and say which file should be the one true copy.
Done when: at least two redundant fields are named, each with a stated "true copy" location.
Hint
Look for the same person's details appearing in both files.
Easy 2: Four things a DBMS gives you (15 minutes)
For each of the four things a DBMS provides (no needless redundancy, enforced integrity, safe concurrent access, durability after a crash), write one sentence describing what goes wrong in a plain-file system without it.
Done when: four sentences, each naming a concrete failure, not just repeating the DBMS's guarantee in reverse.
Hint
Think about what happens the moment two programs touch the same data at once, or the moment power is lost mid-write.
Easy 3: Read the bookstore tables (15 minutes)
Using the customers and orders tables from part 3, write in plain English what the row (order_id: 9001, customer_id: 107, placed_on: 2026-03-14) means, and name which table holds that customer's address.
Done when: one plain-English sentence for the row, and the correct table named for the address.
Hint
The address lives wherever customer_id points, not inside the order row itself.
Medium
Medium 1: Redo the saving at a bigger scale (25 minutes)
Redo part 4's calculation for a bookstore with 50,000 customers, each placing 6 orders on average. State the total copies of the address in the plain-file version, the total in the database version, and the percentage reduction.
Done when: both totals are computed, and the percentage is shown to one decimal place.
Hint
Multiply customers by average orders for the plain-file total, then just use the customer count for the database total.
Medium 2: Design two tables (25 minutes)
A small gym keeps members.txt (name, phone, membership type) and checkins.txt (member name, member phone, check-in time). Design two tables that fix the redundancy, following the customers/orders pattern, and write the CREATE TABLE statements including one REFERENCES line.
Done when: two CREATE TABLE statements, with a primary key in each and a REFERENCES line connecting them.
Hint
checkins needs a member_id column with a REFERENCES members(member_id) line, not a copy of the member's name or phone.
Medium 3: Where a DBMS would not help (25 minutes)
Describe a real situation where a single plain file is still the right choice, and explain using the four DBMS guarantees why none of them are needed there.
Done when: one concrete situation, with a sentence against each of the four guarantees explaining why it is not needed.
Hint
A single-user, single-program system rarely needs concurrent access or cross-program integrity enforcement.
Hard
Hard 1: Trace an inconsistency to its root cause (30 minutes)
A customer support ticket says: "This customer's two most recent orders show different addresses, and neither matches what they just told us on the phone." Using part 1's model, write the most likely sequence of events that produced this ticket in a plain-file system, and the one schema change from part 3 that would make the ticket impossible.
Done when: a plausible step-by-step sequence of events, and the specific schema change named.
Hint
Start from "two programs wrote two different values for the same fact, and nothing reconciled them."
Hard 2: Four guarantees, one small system (35 minutes)
Pick a small system you use (a notes app, a to-do list, a budgeting spreadsheet). For each of the four DBMS guarantees, state whether that system currently has it, and if not, describe the smallest concrete failure that could happen because it is missing.
Done when: all four guarantees addressed, each with a has/does-not-have judgement and, where missing, one concrete failure scenario.
Hint
A shared spreadsheet two people edit at once is a good place to look for the concurrency guarantee specifically.
Hard 3: Grow the bookstore schema (40 minutes)
The bookstore now needs to track which book each order contains, and books can have more than one author. Sketch the additional tables needed (you do not need exact CREATE TABLE syntax yet — Level 3 covers this formally), and explain in one sentence per table why a plain-file version of this feature would be especially redundant.
Done when: at least two additional tables sketched, each with a one-sentence redundancy argument for why files would be worse here.
Hint
One new table connects orders to books; a second connects books to authors, since one book can have more than one author and one author can write more than one book.