Normalization Exercises

1. Choose a key and write the dependencies for the following GRADES
relation:

GRADES (Student_ID, Course#, Semester#, Grade)




2. Choose a key and write the dependencies for the LINE_ITEMS relation:

LINE_ITEMS (PO_Number, ItemNum, PartNum, Description, Price, Qty)

3. What normal form is the above LINE_ITEMS relation in ?




4. What normal form is the following relation in:

   STORE_ITEM (SKU, PromotionID, Vendor, Style, Price)

   SKU, PromotionID -> Vendor, Style, Price
   SKU -> Vendor, Style

5. Normalize the above relation into the next higher normal form:




6. Choose a key and write the dependencies for the following 
   SOFTWARE relation (assume all of the vendor's products have the same 
   warranty):

SOFTWARE (SoftwareVendor, Product, Release, SystemReq, Price, Warranty)

SoftwareVendor, Product, Release -> SystemReq, Price, Warranty


7. Normalize the above SOFTWARE relation into 4NF


8. What normal form is the following relation in 
   (only H,I can act as the key):

   STUFF (H, I, J, K, L, M, N, O)

     H, I -> J, K, L
     J -> M
     K -> N
     L -> O

9. What normal form is the following relation in:
   MORE_STUFF (D, O, N, T, C, R, Y)

     D, O -> N, T, C, R, Y
     C, R -> D
     D -> N

10. Consider the following relation:
   Shipping (ShipName, ShipType, VoyageID, Cargo, Port, Date)
     Hint: Date is the date the ship arrives in the given Port

   With the functional dependencies:
       ShipName       -> ShipType
       VoyageID       -> ShipName, Cargo
       ShipName, Date -> VoyageID, Port

    (a) Identify the candidate keys.
    (b) Normalize to 2NF
    (c) Normalize to 3NF
    (d) Normalize to BCNF


11. Given the following relation and example data:


PartNumber 
Description      
Supplier    
SupplierAddress 
Price
 10010     
 20 GB Disk      
 Seagate    
Cuppertino, CA   
 $100
 10010     
 20 GB Disk      
 IBM        
Armonk, NY       
 $90
 10220     
 256 MB RAM card 
 Kensington 
San Mateo, CA    
 $220
 10220     
 256 MB RAM card 
 IBM        
Armonk, NY       
 $290
 10220     
 256 MB RAM card 
 Sun Microsystems 
Palo Alto, CA 
 $310
 10440     
 17" LCD Monitor 
 IBM        
Armonk, NY    
 $2,100

 
      List the functinoal dependencies and 
      Normalize this relation into BCNF.

Answers

 
 Key is Student_ID, Course#, Semester#
 Dependency is: Student_ID, Course#, Semester# -> Grade 
 GRADES is in 4NF

 
 Key can be: PO_Number, ItemNum
 Dependencies are:   
   PO_Number, ItemNum -> PartNum, Description, Price, Qty
   PartNum -> Description, Price
 
 First off, LINE_ITEMS could not be in BCNF because 
 not all determinants are keys
 Next, it could not be in 3NF because there is a transitive 
 dependency: PO_Number, ItemNum -> PartNum   and  PartNum -> Description
 Therefore, it must be in 2NF. We can check this is true because 
 the key of PO_Number, ItemNum determines all of the 
 non-key attributes however, PO_Number by itself and ItemNum 
 by itself can not determine any other attributes.

 
 STORE_ITEM is in 1NF (non key attribute (vendor) is dependent 
 on only part of the key)

 
 STORE_ITEM (SKU, PromotionID, Price)
 VENDOR_ITEM (SKU, Vendor, Style)

 
 Key is SoftwareVendor, Product, Release
 SoftwareVendor, Product, Release -> SystemReq, Price, Warranty
 SoftwareVendor -> Warranty
 SOFTWARE is in 1NF

 
 SOFTWARE (SoftwareVendor, Product, Release, SystemReq, Price)
 WARRANTY (SoftwareVendor, Warranty)

 
 2NF (Transitive dependencies exist)

 
 1NF (Partial key dependency exists)

  
 (a) Key is ShipName and Date
 (b) Starting with the initial relation:
   Shipping (ShipName, ShipType, VoyageID, Cargo, Port, Date)
   We have a partial key dependency as ShipName alone can 
   determine ShipType. So we normalize to:
    SHIPS( ShipName, ShipType) 
           ShipName       -> ShipType

    VOYAGES (ShipName, VoyageID, Cargo, Port, Date)
            ShipName, Date -> VoyageID, Port
            VoyageID       -> ShipName, Cargo

 (c) As above, VOYAGES has a transitive dependency:
               ShipName, Date -> VoyageID 
               VoyageId -> Cargo
     So normalize VOYAGES:
    SHIPPORTS (ShipName, VoyageID, Port, Date)
            ShipName, Date -> VoyageID, Port
            VoyageID       -> ShipName

    CARGO (VoyageID, Cargo) 
           VoyageId -> Cargo

    SHIPS( ShipName, ShipType) 
           ShipName       -> ShipType

 (d) SHIPPORTS is not in BCNF since it has VoyageID as
     a determinent but VoyageID is not a candidate key.

        SHIPDATES (ShipName, Port, Date)
                ShipName, Date -> Port

        SHIPVOYAGE (VoyageID, ShipName)
               VoyageID -> ShipName

        CARGO (VoyageID, Cargo) 
               VoyageId -> Cargo

        SHIPS( ShipName, ShipType) 
               ShipName       -> ShipType

  Functional dependencies are:
       PartNumber           -> Description
       PartNumber, Supplier -> Price
       Supplier             -> SupplierAddress
 
  1NF:  Suggest PartNumber, Supplier as the key so we are in 1NF
  2NF:  We have a partial key dependency in that Supplier -> SupplierAddress
        so normalize:

      R1(PartNumber, Description, Supplier, Price)
          PartNumber -> Description
          PartNumber, Supplier -> Price

      R2(Supplier, SupplierAddress)
          Supplier -> SupplierAddress

       We still have a problem with R1 so normalize again:
      R3 (PartNumber, Supplier, Price)
          PartNumber, Supplier -> Price

      R4 (PartNumber, Description
          PartNumber -> Description

      R2 (Supplier, SupplierAddress)
          Supplier -> SupplierAddress

Thanks to Shaoqing Yu for corrections and suggestions on these exercises.