Project ForgeConsider the following four relational schemas (underlined attributes are primary keys):
Schema I: Registration(rollno, courses) — FD: rollno → courses Schema II: Registration(rollno, courseid, email) — FDs: (rollno,courseid) → email; email → rollno Schema III: Registration(rollno, courseid, marks, grade) — FDs: (rollno,courseid) → marks, grade; marks → grade Schema IV: Registration(rollno, courseid, credit) — FDs: (rollno,courseid) → credit; courseid → credit
Which one of the relational schemas above is in 3NF but not in BCNF?
- ASchema I
- Schema II
- CSchema III
- DSchema IV
Explanation
Schema II has two candidate keys: {rollno,courseid} (obvious) and {email,courseid} (since email→rollno, combining email with courseid determines everything). The FD email→rollno violates BCNF because email alone is NOT a superkey (it doesn't determine courseid) — but it does NOT violate 3NF, because rollno IS a prime attribute (it's part of the candidate key {rollno,courseid}), and 3NF explicitly permits a non-superkey determinant when the dependent attribute is prime. So Schema II is in 3NF but not BCNF. Schema I has a single trivial key and is trivially in BCNF. Schemas III and IV both have a non-superkey (marks; courseid) determining a NON-prime attribute (grade; credit), which violates even 2NF-adjacent transitive-dependency conditions for 3NF itself, so neither reaches 3NF at all.
Source: GATE CS 2018 (2018)
This question is part of a 15-day revision cycle — sign in and Project Forge will bring it back the day before you'd forget it.