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