# 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:
• 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

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 dependency (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 satisfies 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:
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 satisfies 3 NF because neither marks is superkey nor grade is prime-attribute. So, also 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 satisfies 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