IAE Academy

GATE 2018 CS Q.42: solution and answer

GATE 2018 · CSQ.42 · 2 marks · MCQ

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). Primary key: rollno. Field 'courses' is a set-valued attribute containing the set of courses a student has registered for. Non-trivial FD: rollno -> courses.

Schema II: Registration(rollno, courseid, email). Primary key: (rollno, courseid). Non-trivial FDs: rollno, courseid -> email; email -> rollno.

Schema III: Registration(rollno, courseid, marks, grade). Primary key: (rollno, courseid). Non-trivial FDs: rollno, courseid -> marks, grade; marks -> grade.

Schema IV: Registration(rollno, courseid, credit). Primary key: (rollno, courseid). Non-trivial FDs: rollno, courseid -> credit; courseid -> credit.

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

  1. (A)

    Schema I

  2. (B)

    Schema II

  3. (C)

    Schema III

  4. (D)

    Schema IV

See the question in the paper

Answer and solution

ANSWER

Option (B): Schema II is in 3NF but not in BCNF.

Official answer: BSource: Official answer key, GATE 2018 CS (IIT Guwahati)

  1. A relation is in 3NF if, for every non-trivial FD , either is a superkey or is a prime attribute (part of some candidate key). It is in BCNF if is a superkey for every non-trivial FD. So we need a schema where every violating FD has a prime right-hand side.

  2. Schema I: courses is a set-valued attribute, so the relation is not even in 1NF. It cannot be in 3NF.

  3. Schema II: because and , the set determines and then everything else. The candidate keys are and . Every attribute (rollno, courseid, email) is prime.

  4. Schema II, FD : email alone is not a superkey (it does not determine courseid), so BCNF is violated. The right side rollno is prime, so 3NF still holds. The other FD has a key on its left side. Schema II is in 3NF but not in BCNF.

  5. Schema III: the only candidate key is , so marks and grade are non-prime. In , marks is not a superkey and grade is non-prime, so 3NF fails (a transitive dependency).

  6. Schema IV: the only candidate key is . In , courseid is a proper subset of the key and credit is non-prime, so this is a partial dependency. The relation is not even in 2NF, so it is not in 3NF.

  7. Only Schema II is in 3NF but not in BCNF, which is option (B).

KEY IDEA

3NF allows a violating FD when is prime; BCNF does not. A schema with overlapping candidate keys, here and because , is the classic way to be in 3NF but not BCNF.

WATCH OUT FOR

Taking as the only key of Schema II. That makes rollno look non-prime and the schema look like it fails 3NF. Finding the second candidate key shows rollno is prime.

From the GATE 2018 CS paper. Every question of this paper · Download the full paper · All 19 Normal forms questions, with notes

More Normal forms questions