Demo: normalizing a library spreadsheet into tables

Written Explanation

The starting point

The library has kept its loans in a spreadsheet, one row per loan. The columns are the loan number, the member's name and phone, the book title, the authors (separated by semicolons), and the due date.

The people, books, and authors are invented for the exercise.

The method

We use the procedure from the previous lesson:

1. Understand what one row means and choose a key.
2. Fix 1NF: no lists in cells.
3. Fix 2NF: partial dependencies on a composite key.
4. Fix 3NF: dependencies between non-key columns.
5. Add keys and types, draw the diagram, and verify.

Scope note: the sheet says nothing about physical copies, so "a book" means a title.

Step 1: what does one row mean?

A row currently means "one loan, with all the details about the member, the book, and the authors copied in". The loan number is unique, so it is a natural starting key. Apply the "one noun per row" test from lesson L01: the row is about a loan, a member, a book, and authors all at once. That tells us to expect several tables.

Step 2: fix 1NF by splitting the authors list

The authors cell holds a list. Rows 1 to 3 each hold two names. We cannot count Sami Hadad's books or find "Hadad" reliably.

The simplest repair puts one author on each row:

Every cell now has one value, so the sheet is in 1NF, but the loan number repeats and can no longer be the key; the key is now the pair (loan, author). Even the key has become awkward and the data more repetitive: a sign that we should split rather than merely reshape.

Step 3: the planned mistake (an update anomaly)

Imagine we stop here, satisfied that the lists are gone. A week later Layla gets a new phone number, 0509998888. She appears on four rows. Suppose the librarian changes the phone on the first row only: rows 2 to 4 of her entries still show 0501234567. What is Layla's phone number? The sheet answers both ways, and nothing in it says which is right. That is the update anomaly: one fact, stored in many places, changed in only some of them.

The others are visible too. Insert: a new member who has not borrowed anything cannot be entered without inventing a loan. Delete: deleting Omar's only loan would lose Omar's name and phone.

All three problems come from the same flaw: the member's facts live in the loan rows. The fix is to move each kind of fact to its own table.

Step 4: identify the entities and their facts

Look at each column and ask what it describes: loan and due_date describe the loan; member and member_phone describe the member; book describes the book; author describes the author. We have four entities (loans, members, books, authors) and a relationship, books and authors, with many on both sides. Layla's loans are a one-to-many relationship (a member has many loans), as are the loans of a book (a book appears in many loans). Both foreign keys therefore go in loans.

The 2NF and 3NF checks give the same split: member and book depend on only part of the (loan, author) key (2NF), and the phone depends on the member rather than directly on the loan (3NF). Moving each group of columns that depends on the same thing into its own table resolves both.

Step 5: build the tables

Create one table per entity, each with a generated integer id as its primary key (lesson L02), and replace each copied fact by a foreign key (lesson L03).

members(id PK, name, phone)
authors(id PK, name)
books(id PK, title)
book_authors(book_id FK -> books.id, author_id FK -> authors.id, PK(book_id, author_id))
loans(id PK, member_id FK -> members.id, book_id FK -> books.id, due_on)

Choose the types (lesson L01): ids are integers; name, phone, and title are text (phone is text, because it starts with 0); due_on is a date. Mark name, title, and due_on as required.

Fill in the rows:

Layla's new number appears in exactly one place.

Step 6: the diagram and the check

The relationships as text, with cardinalities:

members 1 ---- * loans * ---- 1 books 1 ---- * book_authors * ---- 1 authors

Read it left to right: a member has many loans; a book is in many loans; a book has many book_authors rows; an author has many book_authors rows. In a drawing you would use five boxes and four lines, with the fork on the "many" ends.

Check against the forms. 1NF: the author list became rows in book_authors. 2NF: book_authors has no non-key columns and the other keys are single columns. 3NF: phone depends on the member, title on the book, due_on on the loan. Every FK points at a primary key.

Finally, verify by rebuilding: can you recreate the original sheet from the new tables? Take loan 3. Its member_id is 2, which gives Omar Said and his phone. Its book_id is 1, which gives "Algorithms Made Simple", and book_authors rows for book 1 give authors 1 and 2, Nora Khalil and Sami Hadad. The due date is 2025-03-23. That is row 3 of the original sheet, with no information lost. If you cannot rebuild every row, you removed something you should not have.