I don't think your "crows foot" diagram supports the
other dependencies outlined in your question. How did you come up
with the 1:Many relationship between FILE and UNIT?
These are the functional dependencies that you describe...
- GROUP -> FILE
- BLOCK -> GROUP
- BLOCK -> UNIT
Also, I assume that each of the above attributes functionally determine some
additional attributes not appearing
on the left hand side of any other functional dependency. These would be:
- FILE -> other-file-attributes
- GROUP -> other-group-attributes
- BLOCK -> other-block-attributes
- UNIT -> other-unit-attributes
Constructing a set of 3NF relations from the above functional dependencies gives:
- FileRelation: (FILE, other-file-attributes)
- GroupRelation: (GROUP, FILE, other-group-attributes)
- UnitRelation: (UNIT, other-unit-attributes)
- BlockRelation: (BLOCK, GROUP, UNIT, other-block-attributes)
This pretty much corresponds to what you have described.
Determining which UNIT instances relate to a given FILE
requires a join of FileRelation to GroupRelation on FILE and then GroupRelation to
BlockRelation on GROUP then BlockRelation to UnitRelation on UNIT.
You want to avoid this multi-table join by inserting a new relationship somewhere in the model that
gives a direct mapping from UNIT to FILE. Such a relation implies the functional
dependency:
This looks like the bit you added to the "crows foot" diagram. Adding this introduces a logical
contradiction. The original schema supports having a given UNIT relating to
multiple FILE instances. as in:
- FileRelation(F1, ...)
- FileRelation(F2, ...)
- GroupRelation(G1, F1, ...)
- GroupRelation(G2, F2, ...)
- BlockRelation(B1, G1, U1, ...)
- BlockRelation(B2, G2, U1, ...)
- UnitRelation(U1, ...)
UNIT instance U1 relates to FILE instances F1 and F2. Given this situation either the UNIT -> FILE functional dependency cannot be supported
or the original set of functional dependencies were incomplete and the schema is not
in 3NF.
At this point you need to resolve whether the real world supports the FILE -> UNIT
dependency or not. If it does, then the original model is not in 3NF and a bit more
reworking of the schema is in order. If the dependency is not supported then the best you can say is:
and the corresponding relation:
is a de-normalization because its content may be derived through existing tables functional dependancies.
=================================================================================
EDIT
Based on a number of comments made to this and other answers, it appears that:
is a true functional dependency, the functional dependency:
while not incorrect, must be redundant. I believe the correct 3NF set of
relations for this model now is:
- FileRelation: (FILE, other-file-attributes)
- GroupRelation: (GROUP, FILE, other-group-attributes)
- UnitRelation: (UNIT, FILE, other-unit-attributes)
- BlockRelation: (BLOCK, GROUP, other-block-attributes)
Notice that the UNIT foreign key has dropped from the BlockRelation.
This is because the UNIT -> FILE FD made it redundant. The
(BLOCK, UNIT) relation is now formed by joining UnitRelation to FileRelation on FILE
then FileRelation to GroupRelation on FILE then GroupRelation to BlockRelation on GROUP.
The original schema was not in 3NF due to the unstated
functional dependency: UNIT -> FILE. The proposed set of relations above is
in 3NF.
Note: When normalizing a schema, every functional dependency needs to be stated
up front. Missing one can change the whole picture!