Ace Test Series: DBMS - Normalization

Question

 

Answer 1

Table contain three attributes emp_id, specialty, mgr_id

Every Manager has only one speciality ===> mgr_id -> specialty

Every Specialty has many managers ===> specialty -> mgr_id  DOESN'T hold

Employee has only one Manager for each Speciality and each employee has many specializations ====> ( emp_id , speciality )  ->  mgr_id

 

by these Dependencies you can conclude that table is in 3NF but not in BCNF

Comments

Brother Here why u did ( emp_id , speciality )  ->  mgr_id

We could have also Done only ( emp_id )  ->  mgr_id.

Is it wrong ???

---

Please Elaborate a Little Will ya ? :)

---

assume these tuples in the table...

1 java 2

1 php 3

2 php 3

3 java 2

from these you can conclude that ( emp_id , speciality )  ->  mgr_id

---

@Shaik Masthan , @amcodes18 : if a relation is in 3NF but not in BCNF then it should have atleast 2 compound keys? but here in this case, we don't have . only key is AB. Can anyone please explain this? i want to know the reason.

Answer 2

Just See The 1st ,3rd and 4th Statements Initially:

1. Employee has many specializations . So Emp_id cant be Primary key(as for 1 value of Emp_id there are many specializations).

3. Each Specialization has many managers . So Specialization cant be primary key.

4. But for each speciality of an employee , they have only 1 manager.

So emp_id+speciality = Primary key.

Now finally see the 2nd statement : Cleary states that mngr_id->speciality.

 

Lets assume A=emp_id,B=specialization,C=emp_id

So its like AB->C and C->B , where key = AB . So basically it is in 3NF, as AB=Superkey & B is prime attribute.

But not in BCnF as C is not a super key.(Or prime attribute B is transitively dependent on key=AB). so not bcnf.