Normalization Exercise

Normalization Exercise - Normalization Exercise Motes Notes...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Normalization Exercise Mote’s Notes 2/7/2011 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, 1998 CheckOutPatrons Billy Jones, Mary Smith, Billy Jones, Kevin White, …, John Smith CheckOutDates 1/13/1999, 2/12/1999, 6/1/1999, 12/23/1999, …, 9/3/2010 ReturnDates 1/19/1999, 2/20/1999, 7/28/1999, 1/6/2000, …, --- TotalDaysCheckedOut 147 TotalTimesCheckedOut 14 TotalPatronsCheckedOut 13 MostRecentCheckOut 9/3/2010 CurrentlyCheckedOut Yes CurrentPatron John Smith
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
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, 1998 CheckOutPatrons Karl Greene, Jessica Long, Amy Turner, Henry King, …, Richard Hernandez CheckOutDates 4/16/2001, 7/20/2001, 3/5/2002, 3/30/2002, …, 8/12/2010 ReturnDates 5/3/2001, 8/11/2001, 3/28/2002, 4/9/2002, …, 8/23/2010 TotalDaysCheckedOut 96 TotalTimesCheckedOut 8 TotalPatronsCheckedOut 8 MostRecentCheckOut 8/12/2010 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, 1972 CheckOutPatrons Buddy Wilson CheckOutDates 7/9/1974 ReturnDates 7/11/1974 TotalDaysCheckedOut 2 TotalTimesCheckedOut 1 TotalPatronsCheckedOut 1 MostRecentCheckOut 7/9/1974 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. Present your solution in the form of a crow’s foot ER Diagram. Indicate the names of all of the fields in each table, not just the “important” fields. Feel free to rename the fields if necessary. Feel free to create new fields if necessary. Indicate the primary key (PK) and foreign keys (FK) of each table. The Solution Approach There are three basic normalization conditions (or rules) that will be considered in this example: Data Accessibility: Every table must have a primary key and all compound and repeating fields must be eliminated. Functional Dependence:
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/26/2011 for the course MIS 325 taught by Professor Mote during the Spring '08 term at University of Texas.

Page1 / 9

Normalization Exercise - Normalization Exercise Motes Notes...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online