Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The bolded 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. There are no non-trivial functional dependencies. Schema II: Registration(rollno, courseid, email) Non-trivial functional dependencies: rollno -> email rollno, courseid -> email email -> rollno Schema III: Registration(rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid -> marks marks -> grade Schema IV: Registration(rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseid -> credit courseid -> credit Which of the following options is correct? A. Schema I is in 3NF but not BCNF B. Schema II is in 3NF but not BCNF C. Schema III is in 3NF but not BCNF D. Schema IV is in 3NF but not BCNF

GATE 2018 · Databases · Database Normalization · medium

Answer: B. Schema II is in 3NF but not BCNF — because email->rollno has a non-superkey determinant (email), but rollno is a prime attribute, satisfying 3NF while violating BCNF.

  1. Analyze Schema I: Schema I has 'courses' as a set-valued attribute. A set is not atomic. Therefore Schema I is NOT even in 1NF, let alone 3NF.
  2. Analyze Schema IV: Schema IV: Registration(rollno, courseid, credit). PK = {rollno, courseid}. FD: courseid -> credit. courseid is a PROPER SUBSET of PK, and credit is non-prime. This is a partial dependency. Schema IV is NOT in 2NF (hence not in 3NF). Option D is wrong.
  3. Analyze Schema III: Schema III: Registration(rollno, courseid, marks, grade). PK = {rollno, courseid}. FD: marks -> grade. marks is not a superkey. grade is NOT a prime attribute (not in any CK). This violates 3NF (transitive dependency through a non-key attribute). Schema III is NOT in 3NF. Option C is wrong.
  4. Analyze Schema II: Schema II: Registration(rollno, courseid, email). Bolded PK = {rollno, courseid}. FDs: rollno->email, rollno,courseid->email, email->rollno. Find all candidate keys: - CK1 = {rollno, courseid} (given as PK) - From email->rollno: if we have email and courseid, we can derive rollno (via email->rollno), then all other attributes. So CK2 = {email, courseid}. Prime attributes: rollno (in CK1), courseid (in CK1 and CK2), email (in CK2). ALL attributes are prime! Check 3NF for email->rollno: - email is not a superkey (email alone doesn't determine courseid). - But rollno IS a prime attribute (part of CK1). - 3NF condition satisfied: Y (rollno) is prime. Check BCNF for email->rollno: - email is NOT a superkey. - BCNF requires the determinant to be a superkey. - BCNF VIOLATED. So Schema II IS in 3NF but NOT in BCNF.