2

(Primary keys in bold.)

In one of my lectures, we took the following schema

R(a, b, c, d, e)
a -> b
e -> b
c, d -> a
c, d -> b
c, d -> e

and took it to 2NF as follows:

R1(c, d, a, e)
c, d - > a and e

R2(a, e, b) (Not in 2NF)
a -> b
e -> b

Naturally, if I want to take my schema to 3NF this causes a problem, since b cannot be partially determined by a and e. What I want to do is simply create separate relations as follows:

R3(e, b)
e -> b

and

R4(a, b)
a -> b

In this instance b is fully functionally dependent the primary key, which brings me to 2NF and the transative dependencies are eleminated for relations 3 and 4, which are in 3NF. However I think it could be argued that this solution is not satisfactory as the value of b could potentially be different for each relation and as there could be anomalies when it is inevitably used as a foriegn key. Any thoughts on this?

philipxy
  • 14,867
  • 6
  • 39
  • 83
shockemc
  • 85
  • 1
  • 10
  • What do you mean, "took it to 2NF"? When we decompose a schema "to" a NF we are abusing language to mean "to a bunch of other schemas in" that NF. The original schema is gone. (So I edited "R(...)" to "R1(...)".) But since your first decomposition still has a component not in 2NF, you *didn't* "take it to 2NF". You seem to be thinking of R1 somehow as still being R, and R1's being in 2NF, or R1 having been named R, as justification for having "taken R to 2NF" but that does not make sense. – philipxy Apr 22 '17 at 00:27

1 Answers1

1

We seek decompositions "preserving" FDs and (this is usually not stated explicitly) not introducing other constraints. An FD is preserved when it holds in some component. The idea is that we can check that an FD holds in recompositions by just checking that it holds in the components, rather than having to join then check. We also prefer an FD and its attibutes to be in just one component, or we would need to add a constraint that where the determinant values agree the dependent values agree. There's always a 3NF schema preserving all FDs without introducing other constraints. When an FD cannot be preserved to get to BCNF, there is instead an "equality dependency" introduced that two components must have the same projection on the FD attributes.

We don't normalize to a given NF by moving through lower NFs. That can preclude good higher NF designs arising. We use an algorithm for a given NF.

When some FDs (functional dependencies) hold, others do, per Armstrong's axioms. We must look among all FDs for NF violators and FDs to preserve, not just some given ones that form a cover. Algorithms also take that into account.

See this recent answer.

PS PKs (primary keys) don't matter, CKs (candidate keys) do. There can be more than one and they can be composite. A PK is just some CK you decided to call PK. So highlighting attributes of a PK is in general inadequate. Just list the CKs.

PPS An (update) anomaly is a certain thing, and it's not what you are using "anomaly" for.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83