Open In App
Related Articles

GATE | GATE CS 2018 | Question 64

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report
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

Answer: (B)

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

Last Updated : 22 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads