Consider the following four relational schemas. For each schema, all…
2018
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed, and the primary key is stated explicitly.
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 functional dependency:
rollno -> courses
Schema II: Registration(rollno, courseid, email)
Primary key: (rollno, courseid)
Non-trivial functional dependencies:
rollno, courseid -> email
email -> rollno
Schema III: Registration(rollno, courseid, marks, grade)
Primary key: (rollno, courseid)
Non-trivial functional dependencies:
rollno, courseid -> marks, grade
marks -> grade
Schema IV: Registration(rollno, courseid, credit)
Primary key: (rollno, courseid)
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
Attempted by 172 students.
Show answer & explanation
Correct answer: B
Answer: Schema II is in 3NF but not in BCNF.
For Schema II:
FDs: rollno, courseid -> email; email -> rollno.
Candidate keys: {rollno, courseid} and {email, courseid}.
BCNF check: email -> rollno violates BCNF because email alone is not a superkey; it does not determine courseid.
3NF check: the same FD is allowed in 3NF because rollno is a prime attribute, as it appears in the candidate key {rollno, courseid}. The FD rollno, courseid -> email has a superkey on the left, so it also satisfies 3NF.
Other schemas are not the answer:
Schema I has a set-valued attribute as written, so it is not a proper 1NF relation.
Schema III violates 3NF because marks -> grade has a non-superkey determinant and grade is non-prime.
Schema IV violates 2NF/3NF because courseid -> credit is a partial dependency on part of the composite key.
Therefore, only Schema II is in 3NF but not in BCNF.