Skip to content
Related Articles

Related Articles

GATE | GATE CS 2018 | Question 64
  • Difficulty Level : Medium
  • Last Updated : 09 Mar, 2018

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 depedency (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 satifies 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 satifies 3 NF because nither marks is superkey nor grade is prime-attribute. So, aslo 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 satifies 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

sudo-gate-course

My Personal Notes arrow_drop_up
Recommended Articles
Page :