Data Base Management System Solved Assignment Solution Sample

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

1. 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)

2. Identify the functional dependencies in the structure. Show all the dependencies using the dependency diagram. (3 points)
3. In what normal form is this relation? Why? (1 points)
4. Decompose the initial relation into 2NF, if required, and draw the relational schema. Using dependency diagram, show all transitive dependencies if exist. (3 points)
5. 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 non-prime attribute should not determine another non-prime 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