QUESTION
In this assignment you will identify the functional dependencies of a relation and decompose it to 3NF. Following is a Warehouse relation with information about the warehouse, manager, part and supplier.
WID 
WAddress 
MgrID 
MgrName 
PartNo 
PartDesc 
SuppID 
SuppName 
QoH 
D1001 
Dallas 
9001 
David 
P4001 
Computer 
S6001 
BestBuy 
3 
D1003 
Austin 
9002 
Sandra 
P4005 
Scanner 
S6002 
Fry’s 
5 
D1004 
Houston 
9001 
David 
P4004 
Printer 
S6002 
Fry’s 
0 
D1004 
Houston 
9001 
David 
P4001 
Computer 
S6001 
BestBuy 
4 
D1001 
Dallas 
9001 
David 
P4004 
Printer 
S6002 
Fry’s 
10 
D1002 
Cupertino 
9003 
Jesse 
P4005 
Scanner 
S6002 
Fry’s 
2 
D1002 
Cupertino 
9003 
Jesse 
P4002 
Fax 
S6001 
BestBuy 
2 
D1003 
Austin 
9002 
Sandra 
P4001 
Computer 
S6001 
BestBuy 
3 
D1005 
Arlington 
9005 
Boris 
P4002 
Fax 
S6001 
BestBuy 
2 

Find all composite candidate keys with at most two components in the Warehouse relation. If there exists multiple candidate keys then select any one as a primary key. (1 point)
 Identify the functional dependencies in the structure. Show all the dependencies using the dependency diagram. (3 points)
 In what normal form is this relation? Why? (1 points)
 Decompose the initial relation into 2NF, if required, and draw the relational schema. Using dependency diagram, show all transitive dependencies if exist. (3 points)
 Decompose the 2NF relation into 3NF, if required, and draw the relational schema. (2 points)
Note:
You may use any drawing tools for example PowerPoint, MS Word, LucidChart. You can either use short text statements or graphical representation for relations. The submission should be a Word or a PDF document.
ANSWER
1) Composite Candidate keys are
 WID, PartNo
 WID, SuppId
 WID, PartDesc
 WID, SuppName
 WAddress , PartNo
 WAddress, SuppId
 WAddress, PartDesc
 WAddress, SuppName
These are composite candidates keys as all distinct tuples have different values for these composite attributes.
Primary key – WID, PartNo
2)
Notation:
X==> Y means X functionally determines Y
WID ==> Waddress
WID ==> MgrId
WID ==> MgrName
MgrId ==> MgrName
PartNo ==> PartDesc
SuppId ==> SuppName
Waddress ==> WID
Waddress ==> MgrId
Waddress ==> MgrName
MgrName ==> MgrId
PartDesc ==> PartNo
SuppName ==> SuppId
WID, PartNo ==> MgrId, MgrName, SuppId, SuppName, QoH
WID, SuppId == > MgrId, MgrName, PartNo, PartDesc, QoH
WID, PartDesc == > MgrId, MgrName, SuppId, SuppName, QoH
WID, SuppName == > MgrId, MgrName, PartNo, PartDesc, QoH
WAddress, PartNo == > MgrId, MgrName, SuppId, SuppName, QoH
WAddress, SuppId == > MgrId, MgrName, PartNo, PartDesc, QoH
WAddress, PartDesc == > MgrId, MgrName, SuppId, SuppName, QoH
WAddress, SuppName == > MgrId, MgrName, PartNo, PartDesc, QoH
3) Relation is in 1^{st} NF as all cells have atomic values but as it contains partial dependency (that is a subset of candidate key determines a non prime attribute) hence it cannot be 2^{nd} and 3^{rd} NF.
Example of partial depenency –
WID, PartNo ==> MgrId
WID ==> MgrId
4)
Proposed relation schema for 2^{nd} NF.
PartNo  PartDesc  SuppId 
PartNo – is the primary key for this table
Candidate keys are :
PartNo
PartDesc
SuppId  SuppDesc 
SuppId – is the primary key for this table
Candidate keys are :
SuppDesc
SuppId
WID  Waddress  MgrId  MgrName 
WID – is the primary key for this table
Candidate keys are :
WID
Waddress
WID  PartNo  QoH 
WID, PartNo – is the composite primary key for this table
Candidate keys are :
WID, PartNo
Transitive dependencies which are present in this relation schema are
WID ==> MgrId ==> MgrName
WID ==> MgrName ==> MgrId
Waddress ==> MgrId => MgrName
Waddress ==> MgrName ==> MgrId
Dependency diagram showing transitive dependencies
5) Removing transitive dependencies (a nonprime attribute should not determine another nonprime attribute) we get following relation schema.
PartNo  PartDesc  SuppId 
PartNo – is the primary key for this table/relation
Candidate keys are :
PartNo
PartDesc
SuppId  SuppDesc 
SuppId – is the primary key for this table/relation
Candidate keys are :
SuppDesc
SuppId
MgrId  MgrName 
MgrId – is the primary key for this table/relation
Candidate keys are :
MgrId
MgrName
WID  Waddress  MgrId 
WID – is the primary key for this table/relation
Candidate keys are :
WID
Waddress
WID  PartNo  QoH 
WID, PartNo – is the composite primary key for this table/relation
Candidate keys are :
WID, PartNo
Looking for best Computer Science Assignment Help. Whatsapp us at +16469488918 or chat with our chat representative showing on lower right corner or order from here. You can also take help from our Live Assignment helper for any exam or live assignment related assistance