The second task is to perform functional dependency analysis of each mapped relation and normalize it into third normal form (3NF) relations. If the ER model mapping is done correctly, the mapped relations should already be in 3NF. Thus, this task would simply verify the fact. The University Application Example (Logical Design) is such an example. It simply validates that the relations are in 3NF based on functional dependencies. For each mapped relation resulted from

Task 1, perform the following normalization steps:

(1) List all candidate keys, the PK and non-prime attributes.

(2) Identify all full functional dependencies and provide a brief explanation for each of them.

(3) Determine whether the relation is in second normal form (2NF) or not based on the functional dependencies. Stating a normal form without being supported by functional dependencies receives no credit.

(4) If the relation is not in 2NF, explain your conclusion and skip the steps (5) and (6) below and move on to the next relation.

(5) If the relation is in 2NF, identify all transitive dependencies if any in the relation and then determine whether the relation is in 3NF or not based on the transitive dependencies.

Product a Supplier ProductCode Supplierld Package Order Item Include BarCode Orderitemt DO HHHO Date OfExpiration Quantity Saleindicator UnitPrice ProductName Name Emai Phones Description (Productimage) QuantityOnHand QuantityOnOrder DateOfOrder Address (Street, Street, City, State, ZipCode) Contain Credit Card Lead Time PaymentCode Sales Order UnitCost UnitSalesPrice Charge And SalesOrderk DiscountRate CreditCardNumber CreditCardType Expiration Date ContactName (FirstName LastName) Date OfPurcahse (Quantity Sold ToDate) TOrder Total Customer Account Email (ContactPhone (AreaCode, PhoneNumber, Type)? Name (FirstName, LastName BillingAddress (Name, Street Address, Apartment, City, State, Zip Code) Password Product a Supplier ProductCode Supplierld Package Order Item Include BarCode Orderitemt DO HHHO Date OfExpiration Quantity Saleindicator UnitPrice ProductName Name Emai Phones Description (Productimage) QuantityOnHand QuantityOnOrder DateOfOrder Address (Street, Street, City, State, ZipCode) Contain Credit Card Lead Time PaymentCode Sales Order UnitCost UnitSalesPrice Charge And SalesOrderk DiscountRate CreditCardNumber CreditCardType Expiration Date ContactName (FirstName LastName) Date OfPurcahse (Quantity Sold ToDate) TOrder Total Customer Account Email (ContactPhone (AreaCode, PhoneNumber, Type)? Name (FirstName, LastName BillingAddress (Name, Street Address, Apartment, City, State, Zip Code) Password