Task:
The Philadelphia
Library has the below table (with associated functional dependencies). What
level of normalization is the table in? (the best level it is in). Produce a
new design that is in 3NF, and show all
of the records in the resulting tables.
BorrowerID Name BookCode Title DateBorrowed DateDue
1 Rizzo 12 War and Peace 12/01/68 12/15/68
1 Rizzo 19 Animal Farm 01/14/69 01/28/69
7 Goode 12 War
and Peace 03/21/80 04/21/80
7 Goode 27 Bombs over London 04/30/80 05/30/80
10 Rendell 35 40 Days to Financial … 11/01/89 11/15/89
14 Street 35 40 Days to Financial … 05/02/95 05/16/95
14 Street 29 Working Within the Syst 02/01/78 03/01/78
Primary Key:
BorrowerID, BookCode, DateBorrowed
Functional Dependencies:
BorrowerID
à Name
BookCode
à Title
BorrowerID,BookCode,
DateBorrowed à
DateDue
ANSWER:
1NF
Design:
BORROWER BOOK
BorrowerID Name BookCode Title
1
Rizzo 12 War and Peace
7
Goode 19 Animal Farm
10
Rendell 27 Bombs over London
14 Street 29 Working
Within the System
35 40 Days to Financial …
BORROWING
BorrowerID BookCode DateBorrowed DateDue
1 12 12/01/68 12/15/68
1 19 01/14/69 01/28/69
7 12 03/21/80 04/21/80
7 27 04/30/80 05/30/80
10 35 11/01/89 11/15/89
14 35 05/02/95 05/16/95
14 29 02/01/78 03/01/78