# GATE | GATE CS 2018 | Question 64

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 â†’ courses
• Schema II: Registration (rollno, coursid, email) Non-trivial functional dependencies: rollno, courseid â†’ email email â†’ rollno
• Schema III: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid, â†’ marks, grade marks â†’ grade
• Schema IV: Registration (rollno, courseid, credit) 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

Explanation:
• 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 â†’ courses Since, rollno is primary key, so this relation is in BCNF as well as 3 NF.
• Schema II: Registration (rollno, coursid, email) Non-trivial functional dependencies: rollno, courseid â†’ email email â†’ rollno Since, {rollno, coursid} is primary key so rollno and coursid are prime attributes. email is non-prime attribute. Functional dependency (FD) rollno, courseid â†’ email is in BCNF and 3NF, but FD email â†’ rollno violates the rule of BCNF because email is not superkey. But it satisfies rule of 3 NF because rollno is prime-attribute. So, overall this relation is in 3 NF but not in BCNF.
• Schema III: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid, â†’ marks, grade marks â†’ grade Since rollno, courseid is primary key, so rollno and courseid are prime attributes and marks and grade are non-prime attributes. FD rollno, courseid, â†’ marks, grade satisfies BCNF as well as 3 NF. FD marks â†’ grade does not satisfies 3 NF because neither marks is superkey nor grade is prime-attribute. So, also can not be in BCNF. So, overall this relation is not in 3 NF and not in BCNF but it does not violates rule of 2 NF, so can be only in 2 NF.
• Schema IV: Registration (rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseid â†’ credit courseid â†’ credit Since, rollno, courseid is primary key, so rollno and courseid are prime-attributes and credit is non-prime attribute. FD rollno, courseid â†’ credit satisfies BCNF as well as 3 NF. FD courseid â†’ credit violates rule of 2 NF, so can not be in 2NF. So, overall this is not in 2 NF, 3 NF, and BCNF. But it is only in 1 NF.
Therefore only schema-II is in 3 NF but not in BCNF. Option (B) is correct.

Quiz of this Question

Previous
Next