Search code examples
databaserelational-databasedatabase-normalizationfunctional-dependencies3nf

How does "OR if Y is a prime attribute" eliminate redundancy for 3NF?


Assuming the schema R(A,B,C,D,E) with the functional depencies AB -> ABCDE (AB is the superkey) and C -> B (B is a part of the key AB, so the schema is in 3NF for all dependencies).

What does it help that B is part of the key (AB)?

Still it would be possible to have AB -> ... C ... -> B, e.g. (1,2,3,4,5) (2,2,3,4,5) where e.g. C is a department number and B is the number of people working in this apartment. It does not eliminate redundancy, even if C is a prime attribute. The same would happen if C would not be a prime attribute.

So how does this definition eliminate redundancy?

How does the definition part "X -> Y is in 3NF, if ... OR if Y is a prime attribute" (the other preconditions are clear) eliminate redundancy at all?

E.g. having (1,2,3,4,5) and (1,x,3,4,6): To satisfy C -> B, x should be 2, but if x would be 2, AB -> ABCDE wouldn't be satisfied, a contradiction.


Solution

  • 3NF does not remove all update anomalies.

    3NF was discovered before higher NFs and was defined in terms of transitive FDs, not involving your clause.

    Dropping a clause in some 3NF definitions gives BCNF. BCNF does not remove all update anomalies. It is the lowest NF where all update anomalies due to problem FDs (functional dependencies) are removed.

    ETNF is the lowest NF where all update anomalies are removed. They remain absent in higher NFs. (Which go on through 5NF and end at 6NF). Update anomalies beyond BCNF are due to problem JDs (join dependencies).

    Update anomalies are not the only source of "redundancy". (A vague, general & multiply-defined term.)