The Problem
Design a normalized database that can be used to keep track of the books in a small library as they are checked out and returned by the library patrons. One non-normalized way to represent this data is with the following single table with fourteen fields. The table has one record for each book in the library.
Note that the library may have multiple copies of popular books. Each copy will have its own unique book identification number (BookID).
Three sample records are provided to help illustrate this single table database.
Field Sample Record 1
BookID 1001
Author John Adams
Title The History of Our Nation
ISBN 14-230-100-34J
Publisher McGraw-Hill, Boston, 2003
CheckOutPatrons Billy Jones, Mary Smith, Billy Jones, Kevin White, ..., John Smith
CheckOutDates 1/13/2005, 2/12/2005, 6/1/2005, 12/23/2005, ..., 1/28/2013
ReturnDates 1/19/2005, 2/20/2005, 7/28/2005, 1/6/2006, ..., ---
TotalDaysCheckedOut 189
TotalTimesCheckedOut 24
TotalPatronsCheckedOut 22
MostRecentCheckOut 1/28/2013
CurrentlyCheckedOut Yes
CurrentPatron John Smith 2
Field Sample Record 2
BookID 1002
Author John Adams
Title The History of Our Nation
ISBN 14-230-100-34J
Publisher McGraw-Hill, Boston, 2003
CheckOutPatrons Karl Greene, Jessica Long, Amy Turner, Henry King, ..., Richard Hernandez
CheckOutDates 4/16/2007, 7/20/2007, 3/5/2008, 3/30/2008, ..., 1/12/2013
ReturnDates 5/3/2007, 8/11/2007, 3/28/2008, 4/9/2008, ..., 1/23/2013
TotalDaysCheckedOut 203
TotalTimesCheckedOut 18
TotalPatronsCheckedOut 18
MostRecentCheckOut 1/12/2013
CurrentlyCheckedOut No
CurrentPatron ---
Field Sample Record 3
BookID 1003
Author Marcus Danielson
Title Raising Chinchillas for Fun and Profit
ISBN 23-20930123-22
Publisher Simon Press, London, 1992
CheckOutPatrons Buddy Wilson
CheckOutDates 7/9/1994
ReturnDates 7/11/1994
TotalDaysCheckedOut 2
TotalTimesCheckedOut 1
TotalPatronsCheckedOut 1
MostRecentCheckOut 7/9/1994
CurrentlyCheckedOut No
CurrentPatron ---
Unfortunately, the data in this non-normalized table may be difficult to maintain. Make all of the modifications that are necessary to normalize the database.
Class tasks
- Process the data fields through 2NF and 3NF if needed
- Create an initial ER diagram (M-N relationships are acceptable)
- Update the ER diagram to remove M-N relationships as needed