Determine the highest normal form for the foll...

Determine the highest normal form for the following instance.

STUDENT COURSE INSTRUCTOR
Narayan Database Mark
Smith Database Navethe
Smith Operating Systems Ammar
Smith TOC Schulman
Wallace Database Mark
Wallace Operating Systems Ahamad
Wong Database Omiecinsky
Zelaya Database Navathe

(A) 1NF

(B) 2NF

(C) 3NF

(D) BCNF

14Comments
Vishwajit kumar vishnu @vishwajitvis 25 Oct 2016 05:54 pm

How is this in 3nf, plz explain? instructor-> course is a fd and there seems no other fd

Hradesh @hradeshpatel 25 Oct 2016 06:18 pm

 i try here if we carefully see that the dependency : 1. student course -------> Instructor

                                                                                  2.  Instructor-------->  course

   here candidate key : {student course}  and prime attribute: {student, course}

   so we find highest so start with BCNF :   1.  follow

                                                                      2.  not follow   

 then comes to  3NF  :    1.  follow 

                                        2. follow

   hence highest normal form is 3NF.

Ranita Biswas @ranita 25 Oct 2016 06:29 pm

The two FDs in this instance are
{STUDENT, COURSE} → INSTRUCTOR,
INSTRUCTOR → COURSE
So, candidate keys are {STUDENT, COURSE} and {STUDENT, INSTRUCTOR}.
Therefore, all are prime attributes.
Now, as all the values are atomic, the instance is in 1NF.
2NF rule is that all non-prime attributes should be fully-functionally depend on each of the candidate keys.
As there are no non-prime attribute, the instance is in 2NF.
3NF rule is for each functional dependency left hand side should be a super-key and if not right hand side should be a prime attribute.
As all are prime attributes, so 3NF rule is also satisfied.
BCNF rule is for each functional dependency left hand side should be a super-key, which is not the case for FD: INSTRUCTOR → COURSE
Therefore, the instance is not in BCNF.
So, the highest normal form for the instance is 3NF.

Pratyush Madhukar @pratyush 26 Oct 2016 09:33 am

Ma'am, is it correct to assume the functional dependencies like this? Because later there might be entries which violate these FDs. So looking at an instance we can't be sure which FDs hold, but we can only be sure which FDs do not hold.

 

Pratyush Madhukar @pratyush 26 Oct 2016 09:47 am

from Navathe:

A functional dependency is a property of the relation schema R, not of a particular legal relation state r of R. Therefore, an FD cannot be inferred automatically from a given relation extension r but must be defined explicitly by someone who knows the semantics of the attributes of R.