Note: when you are asked to analyze a given table , you should go through the following steps:
a. make the table in the format : table_name (attribute1, attribute2, ...).
b. defind the key (and candidate key if there is any) for this table. Also, underline the key.
c. List all functional dependencies (FDs)
d. state the normal form this table is currently in
e. if this table is not in 3NF or BCNF, then normalize it by decomposing the table into a set of tables that are in 3NF or BCNF
Above 5 steps are very important for normalizing any table and are neccesary steps in your assignments.
1. Give the following table, key and FDs, what normal form this table is in ? Why explain your reason. How do you normalize it. What normal form is each table in after you normalize the PRODCUT table ?
PRODUCT (SerialNumber, Type, TankCapacity, RepairDate, RepairInvoiceNumber, RepairCost)
Candidate Keys (CK): (RepairInvoiceNumber), (SerialNumber, RepairDate)
Functional Dependencies:
RepairInvoiceNumber → all other attributes
(SerialNumber, RepairDate) → all other attributes
SerialNumber → (Type, TankCapacity) (note, Type does not determine TankCapacity)
2. Given the following table, key and FDs, what normal form this table is in ? how do you normalize it. What normal form is each table in after you normalize the REPAIR table ?
REPAIR (RepairInvoiceNumber, RepairDate, RepairCost, RepairEmployeeName, RepairEmployeePhone, SerialNumber, Type, TankCapacity)
Candidate Keys:
RepairInvoiceNumber, and (RepairDate, SerialNumber)
Functional Dependencies:
RepairInvoiceNumber → all other attributes
(RepairDate, SerialNumber) → all other attributes
SerialNumber → Type, TankCapacity
RepairEmployeeName → RepairEmployeePhone
3. Show a table other than the one we discussed in class and text that is in 3NF but not in BCNF, give your justification.
4. Normalize the following table into BCNF.
EQUIPMENT (Manufacturer, Model, AcquisitionDate, BuyerName, BuyerPhone, PlantLocation, City, State, ZIP)
Functional dependencies (FD):
BuyerName → BuyerPhone, PlantLocation, City, State, Zip;
Zip → City, State;
(Manufacturer, Model, BuyerName) → AcqDate