answer2.txt

ssd7考试exam2,绝对有用~欢迎下载

1.List the primary key. 

PK(PartID, SupplierID)

2.List all the FDs.

(PartID, SupplierID) -> PartName, SupplierName
PartID -> PartName
SupplierID -> SupplierName

3.What normal form is the relation in? Explain. 

First, each cell is not composite or multi-valued attribute and each cell is single atomic value. So It belongs to 1NF
Sencod, we see the primary key is (PartID, SupplierID) and the FD:(PartID, SupplierID)-> PartName, partID -> PartName, and we can conclude that PartName is partial dependency on the primary key. There is non-primary-key partially dependency on the primary key, so it not the 2NF. So the relation is in 1NF.


4.
Now the relation belongs to 1NF, fistly normalize it to 2NF.There are two partial depentdents. PartName and SupplierName is partially dependent on the primary key.

First, convert CAN_SUPPLY into a 2NF by breaking it into three smaller tables.

CAN_SUPPLY(PartID, SupplierID)
PK(PartID, SupplierID)
no FD

PART(PartID, PartName)
FK(PartID)
PartID -> PartName

Every non-primary-key attribute is fully functionally dependent on the primary key, so PART is in 2NF

SUPPLY(SupplierID, SupplierName)
FK(SuppliserID)
SuppliserID -> SupplierName

Every non-primary-key attribute is fully functionally dependent on the primary key, so SUPPLY is in 2NF

After breaking the original table to 3 smaller tables, all the relation in 3 table is in 2NF, at the same time ,each table dose not contain a non-primary-key attribute that is transitively dependent on the primary key. So, all the table above is in 3NF.