normalization.txt

ssd7 exe6 my answer of this exercise

answer:
1.the primary key TrackingNum.

2.the FDs:
	EmpID --> EmpName  
	OrderNo --> ShipToAddr
  	TrackingNum --> EmpID  EmpName 
	TrackingNum--> OrderNo  ShipToAddr  ShippedDate

3.the update anomalies:

the Insertion anomalies:

	when you insert a new SHIPMENT you must insert the information of the employee and the order. for example when you insert a TrackingNum is 123456987, you should insert all the correct information of the employee and order, include the EmpID, OrderNo,EmpName, ShioToAddr and ShippedDate

	when you insert a new information for a employee, this employee may have not handles a shipment, the TrackingNum is null. for example when you insert a employee EmpID is 1455, because he is a new employee, he handles no shipment, but the TrackingNum is primary key so it could not be null, so there is a insertion anomaly.

the Deletion anomalies:

	when you delete a shipment, you also delete the information of the employee handles it. if the employee now handles the only one shipment, after you delete the shipment information, the imformation of this employee is miss. it is same to the order.

Modification anomalies:

	when you rework the information of order you must rework the information in all shipment belongs to this order. if you do not change all there may cause the shipment in same order have different address and date. it is same to the employee.

4.it is in 2NF.
  it is in 1NF because the domain of every attribute allows a single atomic value, every cell in the table contains one and only one value. the primary key is TrackingNum. the attributes in this table there is not composite or multi-value attributes.
  and it is defined in terms of partial and full dependencies and is associated with modification anomalies. the relation contains non-primary-key attribute that is transitively dependent on the primary key. so it is in 2NF.
  but there still have non-primary-key attribute that is transitively dependent on the primary key so it is not in 3NF.

5.for it is in 2NF, we just need to bring the relation to 3NF.
  because {EmpID --> EmpName} and {TrackingNum --> EmpID  EmpName} so the attribute EmpName transitively dependent on TrackingNum so split the original relations into two new relations Employee and Shipment.
  because {OrderNo --> ShipToAddr} and {TrackingNum--> OrderNo  ShipToAddr  ShippedDate} so the attribute ShipToAddr transitively dependent on TrackingNum so split the Shipment relations into two new relations Order and Shipment.

  so at the end split original relations into three new relations:
	SHIPMENT(TrackingNum(PK), EmpID, OrderNo, ShippedDate)
	Employee(EmpID(PK), EmpName)
	Order(OrderNo(PK), ShipToAddr)
  Employee
   	EmpID→EmpName

  Order
 	OrderNo→ShipToAddr
 
  Shipment 
 	TrackingNum→EmpID
 	TrackingNum→OrderNo
 	TrackingNum→ShippedDate
 
because it is in 2NF and does not contain a non-primary-key attribute that is transitively dependent on the primary key so it is 3NF.