IS 342 - Enterprise Database Management (Quad Cities Campus)

Assignment 4 - Normalization

Due: 4/26/2010

For this assignment we will analyze a table design to:

1. Normalize the table to achieve a design where all tables are in third normal form (3NF).

2. Create functional dependency descriptions for all new tables created.

3. Create functional dependency diagrams for the final tables.

4. Create simple table descriptions for the final tables.

   NOTE: The functional dependency descriptions should be listed like this:
   
      Column A -> Column B, Column C, Column D
      
      or
      
      Column A, Column D -> Column E, Column F

      
   NOTE: The functional dependency diagrams should be listed like this:
   
      
      
   
      
      
   
   NOTE: The simple table descriptions should follow this form:
   
      Table A (Column A, Column B, Column D, Column E)
         FOREIGN KEY (Column E) REFERENCES Table D
      
      (Give the table name and a list of columns that compose the table.
       Designate the primary key by underlining the column name(s), and
       designate any foreign keys by listing them beneath the Table description)

   Problem Description:

CustOrder Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CustID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CustFName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
CustLName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
CustMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CustStreetAddress1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
CustStreetAddress2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
CustCity                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
CustStateCode                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
CustStateName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
CustPostalCode                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
CustCountryCode                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
CustCountryName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
OrdID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
OrdDate                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
ProdID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
ProdDescription                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
OrdProdPrice                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
ProdPrice                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
OrdQty                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
OrdAmount                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
OrdStreetAddress1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
OrdStreetAddress2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
OrdCity                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
OrdStateCode                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
OrdStateName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
OrdPostalCode                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
OrdCountryCode                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
OrdCountryName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
Functional Dependencies:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CustID, OrdID -> CustFName, CustLName, CustMI, CustStreetAddress1, CustStreetAddress2, 
CustCity, CustStateCode, CustStateName, CustPostalCode, CustCountryCode, CustCountryName, 
OrdID, OrdDate, ProdID, ProdDescription, OrdProdPrice, ProdPrice, OrdQty, OrdAmount, OrdStreetAddress1, 
OrdStreetAddress2, OrdCity, OrdStateCode, OrdStateName, OrdPostalCode, OrdCountryCode, OrdCountryName
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CustID -> CustFName, CustLName, CustMI, CustStreetAddress1, CustStreetAddress2, CustCity, 
CustStateCode, CustStateName, CustPostalCode, CustCountryCode, CustCountryName                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CustStateCode -> CustStateName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CustCountryCode -> CustCountryName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
OrdID -> CustID, OrdDate, ProdID, ProdDescription, OrdProdPrice, ProdPrice, OrdQty, 
OrdAmount, OrdStreetAddress1, OrdStreetAddress2, OrdCity, OrdStateCode, OrdStateName, OrdPostalCode, 
OrdCountryCode, OrdCountryName                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
OrdStateCode -> OrdStateName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
OrdCountryCode -> OrdCountryName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ProdID -> ProdDescription, ProdPrice    

       
   What to turn in:
   
   Print-outs showing your set of functional dependencies, functional dependency diagrams,
   and simple table descriptions.
   
   *** NOTE: *** Group the functional dependency descriptions, functional dependency diagram
   and simple table description for each table together.