2NF and composite keys (1 Viewer)

Hi

I have a doubt about Second Normal Form. I understand that a table satisfies 2NF if it has no partial key dependencies. I believe that if a table has only a single attribute, non-composite primary key then it automatically satisfies 2NF. My course tutor says that isn't always the case. Does a non-composite actually key satisfy 2NF?

rgros said:

Hi

I have a doubt about Second Normal Form. I understand that a table satisfies 2NF if it has no partial key dependencies. I believe that if a table has only a single attribute, non-composite primary key then it automatically satisfies 2NF. My course tutor says that isn't always the case. Does a non-composite actually key satisfy 2NF?

Click to expand...

As well as the conditions you mention, 2NF has to also satisfy the following:

• Must be in 1NF
• All the non-key columns must be functionally dependent on the entire primary key

2NF, in common with all normal forms, is concerned with all keys (the candidate keys) of a relation and not just any one "primary" key. The choice of primary key, whether composite or not, is unimportant.

So the answer is no. A relation with a simple (single attribute) key doesn't necessarily satisfy 2NF. There could still be other composite keys with partial dependencies. There can also be partial dependencies even for a simple key. Partial dependency on a simple key occurs when an attribute has the same fixed value in every tuple and is therefore determined by the empty set: {}, which is a proper subset of the key. Such dependencies are fairly unusual in practice because they are normally so "obvious" they are frequently not considered worthy of inclusion in the database at all. They are still a very real possibility that shouldn't be ignored however. One blatant example of a dependency on the empty set in violation of 2NF would be the employer's name in the employee table of a single employer's personnel database. The offending dependency would be {}->{EmployerName}, where {} is a proper subset of the key {EmployeeNum} (the empty set of attributes is of course a subset of every key).