Need help with sql transaction question. Please explain how to reach the solutio

Question

Need help with sql transaction question. Please explain how to reach the solutions for the questions below.

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 functional dependencies:

ID->First

ID->Last

First, Last->ID

Last->Team

ID->Dept

Id->Salary

Salary->Dept

To find candidate key, write all the attribute

ID, First, Last, Team, Dept, Salary

Mark, attribute if it is in right side of the functional dependencies

ID, First, Last, Team, Dept, Salary

Everything is there in this example,

Now check, if with any attribute can we find all other attributes?? That is closure of the attribute.

If yes, that is the candidate key

Now check with ID, from ID, can we find all other attributes , First, Last, Team, Dept, Salary ?

ID->First

ID->Last

ID->Last->Team

ID->Dept

ID->Salary

Closure of ID:

(ID)*={ID,First,Last,Team,Dept,Salary}

With, ID we are getting all other attributes, so ID is the candidate key.

But check, if there is any other candidate key. That means check with another attribute so that we can get all other attirbutes.

Check with First.

First->---X

(First)*={First}

With First, we are not getting all other attributes, so it is not candidate key.

Check with Last

Last->Team

Last->Team->----X

(Last)*={Last,Team}

With Last, we are not getting all other attributes, so it is not candidate key.

Check with Team,

Team->-----X

(Team)*={Team}

With Team, we are not getting all other attributes, so it is not candidate key.

Check with Dept,

Dept->----X

(Dept)* = { Dept}

With Dept, we are not getting all other attributes, so it is not candidate key.

Check with Salary,

Salary->Dept

Salary->Dept->----X

(Salary)*={ Dept, Salary}

With Salary, we are not getting all other attributes, so it is not candidate key

Till now we checked with only one key attribute, Therefore, the candidate key is ID.

Now check with 2 key attributes, except adding ID as it’s part. That means we can not check with (ID,First)* or (ID,Last)* or (ID,Team)* or (ID,Salary)* or (ID,Dept)*

So we have (First,Last)*={First,Last,ID,Salrary,Team,Dept}

First,Last is another candidate key.

(First,Salary)*={First,Salary,Dept} not candidate key

(Last,Dept)*={Last,Dept,Team} not Candidate key

Now check with 3 attribute keys except adding ID, First, Last, so the remaining are Team,Dept,Salary only

(Team, Salary, Dept)*={Team, Salary, Dept} not candidate key

Therefore we have 2 candidate keys, those are

(ID) and (First,Last).

B)

Partial dependency:

No Part of the candidate key will determine non key attribute

Transitive:

No none key attribute will determine a non key attribute

To get 3NF, it should satisfy Transitive property.

Now we have table with (ID, First, Last, Team, Dept, Salary)

Now decompose the table with (ID,First,Last,Dept,Salary) as one table and (First,Last,Team) as another table.

For table (ID,First,Last,Dept,Salary), the functional dependencies are

ID->First

ID->Last

ID->Dept

Id->Salary

Salary->Dept

For table (First,Last,Team), the functional dependencies are

First, Last->ID

Last->Team

This decomposition is lossless (there are common attributes in both the tables)

And this decomposition is dependency preserving ( functional dependencies are divided evenly for both tables)

Table (ID,First,Last,Dept,Salary) is obeying Transitive property that is No Non key attribute is determining Non key attribute.

But table (First,Last,Team) is not satisfying Transitive property and partial dependency.

So decompose table (First,Last,Team) into (First,Last) and (Last,Team)

So First, Last->ID comes under ( First,Last) and Last->Team comes under (Last,Team)

In This table (Last,Team) Last is candidate key.

The decomposition is dependency preserving and lossless.

C) this decompostion is BCNF also.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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