normalization.txt

开耐饥梅陇大学 网上教程 ssd7 exercise6答案。 绝对 正确。 满分的答案。

Normalization:

1. Primary Key(EmpID,OrderNo,TrackingNum)

2. FDs:
          FD1: {EmpID,OrderNo,TrackingNum}->{EmpName,ShipToAddr,ShippedDate}
          FD2: EmpID->EmpName
          FD3: OrderNo->ShipToAddr

3. Update anomalies and examples:
    a. Modification Anomaly
        Example: The EmpName is repeated when the employee handle shipments each time. And when modifying the EmpName
                      "Joe" to "John" for the EmpID "1234", there is possibility that the other rows are not modified. In that case, the
                      EmpID "1234" would show that there are two people have this ID.
    b. Insertion Anomaly
        Example: We have a new employee to add to the list of employees. But he hasn't handle any shipment, so we can't add
                      him to the table until he handle a shipment.
    c. Deletion Anomaly
        Example: If "Joe" with EmpID"1234" has only handled this shipment, in other words, his name and id don't appear in other 
                      rows. Then we want to delete this shipment, so "Joe" will handle none shipment, and he can't be in the table, his
                      information will be lost until he handles a shipment.

4. The relation is in an unnormalized form. Because the attribute "ShipToAddr" and "ShippedDate" are composite attribute.

5. Normalize the relation from unormalized form to 3NF:
 
  Step 1(From unormalized form to 1NF):
            Since the attributes "ShipToAddr(Street,City,State,PostalCode)" and "ShippedDate(Month,Day,Year)" are composite values.
            Change the composite attributes into character strings. In other words, "ShipToAddr" and "ShippedDate" are strings.
  Step 2(From 1NF to 2NF):
            Make every non-primary-key attribute to be fully functionally dependent on the primary key. In other words, break the
            relation into three small relations: Employee, Order, Shipment
          1. Employee(EmpID,EmpName)
               PK(EmpID)
               FD: EmpID->EmpName
          2. Order(OrderNo,ShipToAddr)
               PK(OrderNo)
               FD: OrderNo->ShipToAddr
          3. Shipment(EmpID,OrderNo,TrackingNum,ShippedDate)
               PK(EmpID,OrderNo,TrackingNum)
               FD:{EmpID,OrderNo,TrackingNum}->ShippedDate
            So every non-primary-key attribute to be fully functionally dependent on the primary key.
  Step 3(From 2NF to 3NF):
            Since the relation is already in 2NF, the rest task to make the relation not contain a non-primary-key attribute that is 
            transitively dependent on the primary key.
            Let's see the 3 relations above, Employee,Order and Shipment. Obviously, there is no transitively dependency between
            a non-primary key and the primary key. Because the functional dependency are:
          1. Employee(EmpID,EmpName)
               PK(EmpID)
               FD: EmpID->EmpName
          2. Order(OrderNo,ShipToAddr)
               PK(OrderNo)
               FD: OrderNo->ShipToAddr
          3. Shipment(EmpID,OrderNo,TrackingNum,ShippedDate)
               PK(EmpID,OrderNo,TrackingNum)
               FD:{EmpID,OrderNo,TrackingNum}->ShippedDate
            So the relation is already in 3NF after the projection occured when it entered 2NF.