prime to prime transitivity in 3nf normal form

292 Views Asked by At

I am stuck in this question. What should be the answer to this:

The relation scheme Student Performance (name, courseNo, rolINo, grade) has the following functional dependencies:
name, courseNo, -> grade
rolINo, courseNo -> grade
name -> rolINo
rolINo -> name

The highest normal form of this relation scheme is

(a) 2 NF (b) 3 NF (c) BCNF (d) 4 NF

2

There are 2 best solutions below

2
On

Relation has 2 overlapping candidate-key dependencies so it is in 3nf.

1
On

The Answer is 3NF

  1. name, courseNo, -> grade,
  2. rolINo, courseNo -> grade,
  3. name -> rolINo,
  4. rolINo -> name, first 2 i.e 1 and 2 follows BCNF as LHS is superkey in both. but 3rd and 4th do no follow BCNF so 3 NF as rollNo and name both in RHS are prime attributes. they follow 3NF.

Now, answer for the comments about transitivity, definition of transitive dependency is if A -> B and B -> C

then A -> C is the transitive dependency and our goal is remove such, i.e we want to keep A->B and B->C in different tables, so that there will be no transitive dependency for more clarification refer