GATE | GATE CS 2018 | Question 64

Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed, The underlined attributes are the respective primary keys.

- Schema I:
*Registration(*__rollno__, courses)

Field*‘courses’*is a set-valued attribute containing the set of courses a student has registered for.

Non-trivial functional dependency*rollno → courses* - Schema II:
*Registration (*__rollno, coursid__, email)

Non-trivial functional dependencies:*rollno, courseid → email**email → rollno* - Schema III:
*Registration (*__rollno, courseid__, marks, grade)

Non-trivial functional dependencies:*rollno, courseid, → marks, grade**marks → grade* - Schema IV:
*Registration (*__rollno, courseid__, credit)

Non-trivial functional dependencies:*rollno, courseid → credit**courseid → credit*

Which one of the relational schemas above is in 3NF but not in BCNF?

**(A)** Schema I**(B)** Schema II**(C)** Schema III**(D)** Schema IV**Answer:** **(B)****Explanation:**

- Schema I:
*Registration(*__rollno__, courses)

Field*‘courses’*is a set-valued attribute containing the set of courses a student has registered for.

Non-trivial functional dependency*rollno → courses*

Since, rollno is primary key, so this relation is in BCNF as well as 3 NF. - Schema II:
*Registration (*__rollno, coursid__, email)

Non-trivial functional dependencies:*rollno, courseid → email**email → rollno*

Since, {__rollno, coursid__} is primary key so rollno and coursid are prime attributes. email is non-prime attribute.

Functional depedency (FD)*rollno, courseid → email*is in BCNF and 3NF, but FD*email → rollno*violates the rule of BCNF because email is not superkey. But it satifies rule of 3 NF because rollno is prime-attribute.

So, overall this relation is in 3 NF but not in BCNF. - Schema III:
*Registration (*__rollno, courseid__, marks, grade)

Non-trivial functional dependencies:*rollno, courseid, → marks, grade**marks → grade*

Since__rollno, courseid__is primary key, so rollno and courseid are prime attributes and marks and grade are non-prime attributes.

FD*rollno, courseid, → marks, grade*satisfies BCNF as well as 3 NF.

FD*marks → grade*does not satifies 3 NF because nither marks is superkey nor grade is prime-attribute. So, aslo can not be in BCNF.

So, overall this relation is not in 3 NF and not in BCNF but it does not violates rule of 2 NF, so can be only in 2 NF. - Schema IV:
*Registration (*__rollno, courseid__, credit)

Non-trivial functional dependencies:*rollno, courseid → credit**courseid → credit*

Since,__rollno, courseid__is primary key, so rollno and courseid are prime-attributes and credit is non-prime attribute.

FD*rollno, courseid → credit*satifies BCNF as well as 3 NF.

FD*courseid → credit*violates rule of 2 NF, so can not be in 2NF.

So, overall this is not in 2 NF, 3 NF, and BCNF. But it is only in 1 NF.

Therefore only schema-II is in 3 NF but not in BCNF.

Option (B) is correct.

Quiz of this Question