The GATE Grind

GATE 2018 CS – Question 52

Databases · Integrity Constraints and Normal Forms · 2 marks · Multiple choice

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, courseid, 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?

  1. Schema I
  2. Schema II
  3. Schema III
  4. Schema IV

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (B) Schema II

Explanation

In Schema II, $email\to rollno$ has a non-key determinant, so it is not in BCNF, but $rollno$ is a prime attribute (part of a candidate key $\{email,courseid\}$), so it is in 3NF. Schema I has a non-atomic attribute, and in III and IV non-prime attributes depend on non-key attributes (violating 3NF).