CODEWITHSUNDEEP
databasedatabase-normalizationfunctional-dependencies3nf
tushar
tushar

Reputation: 489

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?

Upvotes: 0

Views: 1345

Answers (3)

Jaykumar
Jaykumar

Reputation: 413

I can prove 3NF. The reason of taking both CK -> AB and BC simultaneously given by Mike above So, Now

(A,B,C) are Prime Attribute (P.A) (i.e part of key)

&

only D is Non-Prime Attribute (N.P.A) (i.e not part of key)

Checking for 2NF. there should not be any P.A --> N.P.A (i.e Partial F.D)

1)AB -> D (Key --> N.P.A)
2)CB -> D (Key --> N.P.A)
3)A-> C   (P.A --> P.A)
4)C -> A  (P.A --> P.A)

So, This is in 2NF.

Checking for 3NF. there should not be any N.P.A --> N.P.A (i.e Transitive F.D)

1)AB -> D (Key --> N.P.A)
2)CB -> D (Key --> N.P.A)
3)A-> C   (P.A --> P.A)
4)C -> A  (P.A --> P.A)

So, This is in 3NF

Checking for BCNF. there should not be any P.A --> P.A (i.e LHS only Key allow)

1)AB -> D (Key --> N.P.A)  ok
2)CB -> D (Key --> N.P.A)  ok 
3)A-> C   (P.A --> P.A)    not a key
4)C -> A  (P.A --> P.A)    not a key

So, This is not BCNF

So, Highest is indeed 3NF.

Upvotes: 0

philipxy
philipxy

Reputation: 15118

  1. When some FDs hold, others must hold, per Armstrong's axioms. But you are only looking at given FDs.

  2. "Partial FD" is not defined in terms of CKs (candidate keys). 2NF is defined in terms of partial FDs and CKs. 2NF holds when there are no partial FDs of non-prime attributes on any CK. Not when there are no partial FDs.

  3. PKs (primary keys) are irrelevant. A PK is just some CK you decided to call "the PK".

  4. Y is partially functionally dependent on X when Y is functionally dependent on a smaller/proper subset S of X. But the partial dependency so identified is X -> Y, not S -> Y.

(See this answer.)

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95552

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.

Upvotes: 2

Related Questions