SQL Question of the Day: 010 - Database Design

Reply

CalmYourself Posted: Wed, May 2 2007 8:58

rated by 0 users

Consider the following table:

Each row in the table is unique and there are no non-key attributes.

What is the highest (in the order they appear in the list) normal form that the table satisfies?

1.      None, it is not normalized at all

2.      1^st Normal Form

3.      2^nd Normal Form

4.      3^rd Normal Form

5.      Boyce-Codd Normal Form

6.      4^th Normal Form

7.      5^th Normal Form

Reply

Snorlax replied on Wed, May 2 2007 12:08

rated by 0 users

I'm going with 1st Normal Form.

Reply

fridgi replied on Wed, May 2 2007 12:38

rated by 0 users

Urghhhh thats an ugly table. I am going with No. 1

If you say its different I will not agree because I would never implement a table like that and I dont care how normal it is 

Please mark me right... next question please?

Reply

fridgi replied on Wed, May 2 2007 12:42

rated by 0 users

[comments removed] - sticking with No.1

Reply

SilverFox replied on Wed, May 2 2007 14:48

rated by 0 users

No 2 : 1st Normal form

Reply

Wildcat replied on Thu, May 3 2007 8:22

rated by 0 users

I think that I would go with :

4.      3^rd Normal Form

My reasoning: "there are no non-key attributes" = the 3 columns form the primary key.

Reply

CalmYourself replied on Thu, May 3 2007 8:32

rated by 0 users

First normal form (1NF or Minimal Form) is a normal form used in database normalization. First normal form excludes the possibility of repeating groups by requiring that each field in a database hold an atomic value, and that records be defined in such a way as to be uniquely identifiable by means of a primary key.

Second normal form (2NF) is a normal form used in database normalization. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. A non-prime attribute is one that does not belong to any candidate key.

The third normal form (3NF) is a normal form used in database normalization to check if all the non-key attributes of a relation depend only on the candidate keys of the relation. This means that all non-key attributes are mutually independent or, in other words, that a non-key attribute cannot be transitively dependent on another non-key attribute.

A relation in 3NF must also be in 2NF. The 3NF differs from the second normal form in that all non-key attributes in 3NF are required to be directly dependent on each candidate key of the relation.

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). Boyce-Codd normal form follows the same rules as 3NF, but only allows functional dependencies where the left side of a functional dependency, , is a superset of a candidate key.

Fourth normal form (4NF) is a normal form used in database normalization. 4NF ensures that independent multivalued facts are correctly and efficiently represented in a database design. 4NF is the next level of normalization after Boyce-Codd normal form (BCNF).

The definition of 4NF relies on the notion of a multivalued dependency. A table with a multivalued dependency is one where the existence of more than one independent many-to-many relationships in a table causes redundancy; and it is this redundancy which is removed by fourth normal form.

(Source: Wikipedia)

1. There are no repeating groups, these were resolved by splitting service into two entries per company and area.
   
   
2. Keeping that in mind, there are no non-key attributes (meaning every column is part of the primary key) in the table above.  Each record is identifiable by means of the primary key.

These two ensure that the table is at least First Normal Form.

1. There are no non-prime attributes in the table

This ensures that the table is in Second, Third AND Boyce-Codd Normal Form.

1. The table contains only key attributes which implies that the table is a 3-way associative entity.  This means that there are multivalued dependencies.

This statement proves that the table does NOT satisfy Fourth Normal form.

Answer: 5. Boyce-Codd normal form.  The table satisfies all requriements up to and including Boyce-Codd.

Reply

CalmYourself replied on Thu, May 3 2007 14:38

rated by 0 users

I wrote a brief blog post explaining what 4th normal form is and how to normalize your Boyce-Codd tables into 4th normal form.

Take a look at it here: What exactly is 4th normal form?