CMPT 308 Lab 7: Normalization One Solution   Lab 7: Normalization One - 20 points

CMPT 308 Lab 7: Normalization One Solution Lab 7: Normalization One - 20 points

To begin delving into the art and science of normalization.
You have been hired as a database consultant by Tycho Manufacturing. They wish to
track the software packages installed on their station computers. Each computer is
identiEied by an asset tag number. Each software package has a package ID. They would
also like to track the installation date of each package on each computer, as well as the
cost of that software for that computer at install time.
Ini-al Data












Part One: Tycho CEO Fred Johnson has put together a spreadsheet of all the data he has
so far, which he personally collected.
As he shows you the spreadsheet, having just signed your consulting agreement, he
asks what you think of it. How do you reply?
Put his data in 1NF and display it. (Show me the table; no SQL.)
What is the primary key?
Part Two: Add two columns of new data: one column for software package name (e.g.,
Zork, Portal, etc.) and one for computer model (e.g., IBM, Apple, etc.). Be sure that your
new data is consistent with the original data. Do not add any additional columns.
Display the new table.
Identify and document all functional dependencies.
Explain why this new table is not in third normal form.
Part Three: Decompose your 1NF table into a set of tables that are in at least third
normal form. (BCNF would be better.) Remember that it’s wrong to add artiEicial keys to
associative entities. Actually, as I said before, do not add any additional columns.
Identify all primary keys (determinants) for all tables.
Identify all functional dependencies for all tables.
Explain why the new tables are in third normal form.
10. Draw a beautiful E/R diagram.

Chapter 3 in our text

Stack OverElow -
•  Microsoft on Normalization -
                                                                           Page 1? of 1?
Powered by