Open In App

GATE | GATE CS 2018 | Question 64

Like Article
Like
Save
Share
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