SQL Server Technologies

Re: SQL Question of the Day: 010 - Database Design

CalmYourself — Thu, 03 May 2007 12:38:41 GMT

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?

Re: SQL Question of the Day: 010 - Database Design

CalmYourself — Thu, 03 May 2007 06:32:53 GMT

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)

There are no repeating groups, these were resolved by splitting service into two entries per company and area.
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.

There are no non-prime attributes in the table

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

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.

Re: SQL Question of the Day: 010 - Database Design

Wildcat — Thu, 03 May 2007 06:22:34 GMT

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.

Re: SQL Question of the Day: 010 - Database Design

SilverFox — Wed, 02 May 2007 12:48:46 GMT

No 2 : 1st Normal form

Re: SQL Question of the Day: 010 - Database Design

SlowPoke — Wed, 02 May 2007 12:19:27 GMT

1nf

Re: SQL Question of the Day: 010 - Database Design

fridgi — Wed, 02 May 2007 10:42:17 GMT

[comments removed] - sticking with No.1

Re: SQL Question of the Day: 010 - Database Design

fridgi — Wed, 02 May 2007 10:38:50 GMT

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?

Re: SQL Question of the Day: 010 - Database Design

Snorlax — Wed, 02 May 2007 10:08:52 GMT

I'm going with 1st Normal Form.

SQL Question of the Day: 010 - Database Design

CalmYourself — Wed, 02 May 2007 06:58:24 GMT

Consider the following table:

Company	Area	Service
Green Pastures	Sandton	Carpeting
Green Pastures	Sandton	Cleaning
Green Pastures	Fourways	Carpeting
Green Pastures	Fourways	Cleaning
Green Pastures	Midrand	Carpeting
Green Pastures	Midrand	Cleaning
Strong Arm Security	Seapoint	Armed Response
Strong Arm Security	Seapoint	Neighborhood Patrol
Strong Arm Security	Camps Bay	Armed Response
Strong Arm Security	Camps Bay	Neighborhood Patrol
Strong Arm Security	Stellenbosch	Neighborhood Patrol
Strong Arm Security	Stellenbosch	Armed Response

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