Consider the universal relation EMPLOYEE(ID, First, Last, Team, Dept, Salary) wi

Question

Consider the universal relation

EMPLOYEE(ID, First, Last, Team, Dept, Salary)

with the following set F of functional dependencies:

ID -> First

ID -> Last

First, Last -> ID

Last -> Team

ID -> Dept

ID -> Salary

Salary -> Dept

a. Identify candidate keys of EMPLOYEE.

b. Construct a decomposition of EMPLOYEE into relations in 3NF that preserves dependencies. How can you be sure that your decomposition is dependency-preserving?

c. Are all of the relations in your decomposition in BCNF? Either explain why they are, or identify one that is not and explain why it is not. (Note that for a relation to be in BCNF, the determinants of all functional dependencies in the relation must be superkeys of that relation – not superkeys of the original universal relation.)

Explanation / Answer

a) The candidate keys are ID,(first,last). because id->first ,id->last ,last-> team -->> id->team ,id->dept, id->salary which implies id contains { first,last,team,dept,salary}

and (first,last)->id so all the attributes of ID are involved in (first,last) making it also a candidate key.

b)There is no possibility for transitive dependency or partial dependency in the table..but for the relation in 3NF it should satisify transitive dependency so we need to decompose the attributes

Let us decompose the table into 2 parts one table as (first,last,dept,salary) and (first,last,team) as another table

These 2 tables have common attributes which makes it lossless decomposition but the (first,last,team) table does not satisify transitive dependency so we again decompose this table as (first,last) and (last,team) now it satisifies transitive dependency. so there we proved that EMPLOYEE is dependency preserving .

c)The above considered decomposition is BCNF.As we already knew that Id,first,last are the candidate keys and after decomposition we have id,first and last as super key which is uniquely defined in the decomposed tables as (id,first,last,salary,dept) , (first,team) and (last,team)

ID is super key in table1 and FIRST in table 2 and LAST in table 3 respectively where ID,FIRST,LAST are together candidate keys for non-decomposed table

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.