normalization.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.
   There are 3 Normal Forms Definition:
   1)1NF: First Normal Form
     A relation schema is in 1NF, if the domain of every attribute allows a single atomic value. 
     That is, every cell in the table contains one and only one value.
   2)2NF: Second Normal Form
     A relation is in 2NF, if it is in 1NF and every non-primary-key attribute is fully functionally 
     dependent on the primary key. 
     In other words, in a relation that is in 2NF there are no partial dependencies on the primary key.
   3)3NF: Third Normal Form
     A relation is in 3NF, if it is in 2NF and does not contain a non-primary-key attribute that is 
     transitively dependent on the primary key.

   It is in the first normal form,there are two reasons:
	1)every attribute is atomic.
	2)there are patial dependent, such as:(SupplierID)->(SupplierName)
					      (PartID -> PartName).
	
4.Apply normalization to it incrementally, carrying the normalization process through each of the higher normal forms       possible up to 3NF. That is, if the relation were 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. 

1)1NF
 It is the first normal form ,because not a attribute has multiple values

2)1NF to 2NF
 Because there are two partial dependency to the primary attribute.
 The attribute "PartName" is partially dependent on the  primary attribute "PartID" and 
 also the attribute "SupplierName" is partially dependent on the primary attribute  "SupplierID". 
 So we transform this table to three tables.

Supply(PartID,SupplierID)
PK:PartID,SupplierID
FDs:(PartID,SupplierID)->(PartID,SupplierID)

Part(PartID,PartName)
PK:PartID
FDs:(PartID)->(PartName)

Supplier(SupplierID,SupplierName)
PK:SupplierID
FDs:(SupplierID)->(SupplierName)

There is no partial dependency to primary attribute ,so it is 2NF.

3)2NF to 3NF 
It has been 3NF for not a non-primary-attribute is transitively dependent on the priamry attribute.