GATE CS 2018 Q.52 (CS Q.42): Functional Dependencies and Normalization

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 $\to$ courses

Schema II: Registration (rollno, courseid, email)
Non-trivial functional dependencies:
rollno, courseid $\to$ email
email $\to$ rollno

Schema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid $\to$ marks, grade
marks $\to$ grade

Schema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid $\to$ credit
courseid $\to$ credit

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

Schema I

Schema II

Schema III

Schema IV

Official answer and solution

Official answer: B
From: Official GATE 2018 answer key

Solution

Determine candidate keys and test the 3NF and BCNF conditions for each functional dependency.

Analyze Schema II: Registration(rollno, courseid, email)
Functional dependencies:

rollno, courseid -> email
email -> rollno

Find candidate keys:

$\{\text{rollno}, \text{courseid}\}^+ = \{\text{rollno}, \text{courseid}, \text{email}\}$ , so $\{\text{rollno}, \text{courseid}\}$ is a candidate key.
With email -> rollno, $\{\text{email}, \text{courseid}\}^+ = \{\text{email}, \text{courseid}, \text{rollno}\}$ , so $\{\text{email}, \text{courseid}\}$ is also a candidate key.
The prime attributes are $\{\text{rollno}, \text{courseid}, \text{email}\}$ (all attributes are prime!).

Check normal forms for Schema II:

rollno, courseid -> email: LHS is a candidate key (superkey), satisfying both 3NF and BCNF.
email -> rollno: LHS email is not a superkey, which violates BCNF. However, RHS rollno is a prime attribute, which satisfies the 3NF condition.

Therefore, Schema II is in 3NF but not in BCNF, matching option B.

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2018 Computer Science and Information Technology (CS)
Question: 52 of 65, printed as CS Q.42
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source