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

Goals
To begin delving into the art and science of normalization.
Scenario
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
 


 

 

 

 

 

 

 

 

 

 

 

Deliverables
Part One: Tycho CEO Fred Johnson has put together a spreadsheet of all the data he has
 
so far, which he personally collected.
 
1.
As he shows you the spreadsheet, having just signed your consulting agreement, he
 
 
asks what you think of it. How do you reply?
 
2.
Put his data in 1NF and display it. (Show me the table; no SQL.)
 
3.
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.
 
4.
Display the new table.
 
5.
Identify and document all functional dependencies.
 
6.
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.
 
7.
Identify all primary keys (determinants) for all tables.
 
8.
Identify all functional dependencies for all tables.
 
9.
Explain why the new tables are in third normal form.
 
10. Draw a beautiful E/R diagram.
Resources

Chapter 3 in our text
 

Stack OverElow - http://stackoverElow.com/questions/tagged/normalization
 
•  Microsoft on Normalization - http://support.microsoft.com/kb/283878
                                                                           Page 1? of 1?
Powered by