databasedatabase-normalizationfunctional-dependencies3nf

How can this be in 3NF when through both keys the relation contains a partial functional dependency?


Relation schema: R (ABCD)
Functional dependencies:
AB -> D
CB -> D
A -> C
C -> A

What is the highest normal form?

My understanding:

Candidate keys are AB and BC.

While creating the table both AB and BC cannot both be primary key.

For key AB:
AB -> D (full functional dependency, so no problem)
CB -> D (?)
A -> C (partial functional dependency, as its left side contains only part of key)
C -> A (functional dependency, so no problem)

For key BC:
AB -> D (?)
CB -> D (full functional dependency, so no problem)
A -> C (functional dependency)
C -> A (partial functional dependency, as its left sideis part of key)

Through both keys the relation contains a partial functional dependency. So it is not in 2NF.

But the answer is 3NF. Why?


Solution

  • While creating table both AB and BC can not consider as Primary key. So let's take one by one.

    No. You can take them one by one, but you have to consider every candidate key. The relational model offers no theoretical basis for labeling one candidate key "primary". There might be good practical reasons for that in a SQL database, but there's no theoretical justification for it solely within the relational model.

    The notion of "partial functional dependency" applies to non-prime attributes. The only non-prime attribute is D. There's no partial dependency here.