?? normalization.txt
字號:
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.
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -