Checking if a transitive key dependency is in 3rd normal form

database-designdenormalizationnormalizationschema

I'm currently trouble determining if one of my schemas is in third normal form.

I have a table with columns image_id (A), upload_time (B), uploader_id (C), and image_file (D). A is a key (unique identifier for every row in the table), and BC is also a key (every pair of uploader and upload time is unique). Thus, we have A -> BCD, and BC -> A (and transitively BC -> A through A -> BCD).

Is this table in 3rd normal form? I'm not entirely sure, because I know 3NF allows for a partial key transitive dependency (e.g. XY -> Z, Z -> X), but I'm not sure if it allows for a total key transitive dependency like I have above.

Best Answer

A simple check to see if a relation schema is in Third Normal Form is to see if every left hand side of non-trivial functional dependencies is a superkey (and this test can be used also to check if a relation is in Boyce-Codd Normal Form), or, if this is not the case, if every attribute on the right part is a prime attribute (i.e. part of any candidate key).

So in you case both the dependencies A → BCD and BC → A (and also BC → D) have a candidate key as left part, so the schema is in Third Normal Form (and in this case also in the more strict Boyce-Codd Normal Form).

Related Solutions

Database normalization 2nf, 3nf

A simple mnemonic:

The key (1NF - no data items repeated in more than one place within the row)
The whole key (2NF - nothing relates to just a part of a composite key)
Nothing but the key (3NF - nothing is related to a non-key attribute)

. . . So help me Codd.

Why is this relation in 3NF

The mistake is in your understanding of transitive dependency. From wikipedia: Transitive dependency

In mathematics, a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or distinct collections of attributes) in the relation. Suppose all three of the following conditions hold:

1. A → B
2. It is not the case that B → A
3. B → C

Then the functional dependency A → C (which follows from 1 and 3 by the axiom of transitivity) is a transitive dependency.

In your case though, (2) does not hold:

1. U → T                                  -- correct
2. It is not the case that T → U          -- wrong
3. T → V                                  -- correct

Therefore {V} is NOT transitively dependent on {U}.

Best Answer

Related Solutions

Database normalization 2nf, 3nf

Why is this relation in 3NF

Related Question