Putting It Together

At Riverside Public Library in York, staff typed one mixed afternoon sheet: member names, phones, book titles, locker locations, and loan dates — all on one list.

Read it carefully. Your job is to decide what kinds of thing the library actually has, and which facts belong together. The finished tables and diagrams come only at the end, after you have worked through the steps.

An entity is what a table is about — one type of thing. Members are people who joined. Books are titles on the shelf. Loans are borrows. Lockers are storage spaces assigned to members.

A chat thread or George's mixed sheet is not an entity. The sheet is the problem you are fixing.

Each table needs a primary key: a field that uniquely identifies every row. Names fail when two people share a name — there are two Sophie Walshes, in York and London. Phones can change.

The library assigns its own numbers: MemberID, BookID, LoanID, LockerID.

Where a fact on one list must match a fact on another, you store a foreign key and draw a line on purpose.

• Loans.MemberID matches Members.MemberID — ONE member, MANY loans.
• Loans.BookID matches Books.BookID — ONE book, MANY loan records over time.
• Lockers.MemberID matches Members.MemberID — ONE member, ONE locker.

On the afternoon sheet, Sophie's phone appears on every loan line. Her locker location is copied twice. Those are redundant copies — the same fact stored more than once.

If Sophie changes her phone, staff must find every copy. Miss one, and the sheet disagrees with itself. That is an update anomaly.

First normal form (1NF): one value per box — no Book1, Book2, Book3 columns. Extra borrows are extra rows on Loans.

Second normal form (2NF): facts on a row depend on the whole key. Phone depends on MemberID, not on LoanID.

Third normal form (3NF): no fact hanging off another non-key fact. If a fine depends on membership type, store the type list separately.

Riverside Public Library is opening a small branch in Bristol. The transfer pack lists what the new site must store — members across York, Bristol, Manchester, Leeds, and London; books including The River Path and Bristol Harbour; loans with dates; lockers with locations.

Work through the questions below without scrolling to the finished tables and diagrams at the end. Same method as York. Different building.

If your answers matched the steps above, you already hold the whole design in your head. Here is the official picture — after the work, not before.

Four tables. Numbers identify. Numbers point. No copied phones on loan rows. The same design serves York and Bristol.