0

Relation schema R (ABCD)
Functional Dependencies are :
AB -> D
CB -> D
A-> C
C -> A
Highest Normal Form ???

My understanding :

Candidate key = AB and BC

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

For Key AB :
AB -> D ( Fully Functional Dependency , so no problem )
CB -> D ( ??? )
A -> C (partial Functional Dependency , as left side contains only part of key)
C -> A ( Functional Dependency , So no problem )

For Key BC 

AB -> D ( ???? )  
CB -> D ( Fully Functional Dependency , so no problem )  
A  -> C  (Functional Dependency )  
C  -> A  ( Partial Functional Dependency , as it's LHS is part of Key )

Now through both key's , relation contains Partial Functional Dependency.
Then it should not be in 2NF.
But answer is 3NF.
Please correct me.

philipxy
  • 14,867
  • 6
  • 39
  • 83
user1745866
  • 89
  • 1
  • 11
  • "relation contains Partial Functional Dependency" is not enough to violate 2NF. Find & quote & use definitions of partial FD & CK. – philipxy Apr 07 '19 at 00:02

2 Answers2

1

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • A FD that holds is partial when dropping a determining attribute gives another FD that holds. That does not involve CKs or prime attributes. Every FD that has proper superset of a CK as determinant is partial; so there are partial FDs holding here, eg ABC->D. The partial FDs that matter to 2NF are ones where CKs determine non-prime attributes. – philipxy Apr 07 '19 at 00:08
1

  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.)

philipxy
  • 14,867
  • 6
  • 39
  • 83