normalization(2).txt

exercise6的大难

1.List the primary key:TrackingNum

2.List all the FDs:
FD0: { TrackingNum } -> { EmpID, EmpName, OrderNo, ShipToAddr, ShippedDate } 
FD1: { EmpID } -> { EmpName }
FD2: { OrderNo } -> { ShipToAddr, ShippedDate }

3.List all the update anomalies and provide an example of each:

A modification anomaly typically leads to inconsistencies because 
of missed updates. For example, since the employee's EmpID and EmpName 
are repeated for each OrderNo, when modifying the EmpName from Joe
to John for 1234,there is a possibility that some row might 
not be modified. In that case the table would show that EmpID 1234 names
both Joe and John.

An insertion anomaly occurs when we are prevented from inserting 
information that we want to keep track of. For example, we cannot
insert the EmpID and EmpName of a new employee in the SHIPMENT
until he or she  handle an order. 

A deletion anomaly occurs when a deletion leads to an unintended 
drop of data. For example, information about a employee is lost, 
when the order he has handle is over, and that information
is deleted. This will happen to Joe if the order that he handles has
been over and the order is deleted from the table.


4.What normal form is the relation in? Explain:
The nomal form is relation in 2NF.For that every non-primary-key attribute is fully functionally dependent on the primary key. 
And the relation contains non-primary-key attribute that is transitively dependent on the primary key. for example:
{ TrackingNum } -> { EmpID },{ EmpID } -> { EmpName } ,So { TrackingNum } -> { EmpName }.


5.Apply normalization to it incrementally, bringing the relation to 3NF. That is, if the relation is unnormalized, bring it to first normal form, then bring the first normal form you've just created to second normal form, and then bring the second normal form to third normal form:
Since the relation belongs to second normal form,so we should eliminate the transitively dependent to transfer it to the 
third normal form:
 Split original relations into three new relations:
	SHIPMENT(TrackingNum, EmpID, OrderNo, ShippedDate)
	Employee(EmpID, EmpName)
	Order(OrderNo, ShipToAddr)
for the SHIPMENT:
 	{ TrackingNum } -> { EmpID,OrderNo } 
for the Employee:
	{ EmpID } -> { EmpName }
for the Order:
	{ OrderNo } -> { ShipToAddr, ShippedDate }
All the three relations does not contain transitively dependent.