• Golfer22 (8/17/2013)


    1. If a table was in 1NF and had non-key attributes with trivial transitive dependencies on candidate keys, could it be in 3NF? I think it could if the non-key attributes were not dependent on a subset of any candidate key.

    2. Assuming the non-key attributes were not dependent on a candidate key, nor any subset of the candidate key, what is the highest normal form the table could be in?

    1. Yes. Any 3NF table with at least one non-prime attribute and at least two candidate keys has at least one trivial transitive dependency on the primary keys ("trivial" just means that either the target is included in some element of the dependency chain before the last element of the chain or every element of the chain except the last includes the whole of at least one candidate key).

    Here is an example table

    create table Example (

    Pkey varchar(32) primary key,

    Nickname varchar(8) not null unique,

    ExpiryDate date not null

    );

    This is in third normal form because the only non-prime attribute (ExpiryDate) is dependent on every candidate key, and there is a trivial transient dependency Pkey -> Nickname -> ExpiryDate.

    2. The definition of a candidate key is that it is a minimal key, the definition of a key is that it is a set of attributes such that every attribute (whether prime or not) is dependent on it. So the idea of a relation which has attribute which isn't dependent on a candidate key contradicts the definition of candidate key.

    Tom