RELATIVELY EASY NORMALIZATION PRACTICE PROBLEM

We want to normalize these data items for our university library:

Student ID

Student Name

Number of books that student currently has checked out

Book Call Number (which is unique)

Book Title

Date checked out

Date due

Date actually returned

The date actually returned can be different from the date due, but all books are due 14 days after they are checked out. We want to be able to query our database to find out the history, for all students, which books they checked out and when during their time at the university, and to find out the history, for all books, when they were checked out and by whom.

The best way to normalize un-normalized data is to first draw an entity-relationship diagram: figure out what the entities are (the rectangle), which ones are related to which (the lines) and the max cardinalities at both ends of all lines. This logical model will allow you to construct the physical database, because you will know where foreign keys must be and where there must be linking (bridge) tables.

Table Name Primary Key Foreign Key(s)(?) Other non-key attributes