Re: 4NF and 5NF

Normalisation

Normalisation degrees of relational database tables have been defined and include:

First normal form (1NF). This is the "basic" level of normalisation and generally corresponds to the definition of any database, namely: It contains two-dimensional tables with rows and columns. Each row represents a unique instance and must be different in some way from any other row (that is, no duplicate rows are possible). All entries in any column must be of the same kind. For example, in the column labelled "Employee," only employee names or numbers are permitted.

Second normal form (2NF). At this level of normalisation, Each column (non-key column) should be fully dependent of the key-column For example, in a table with three columns containing employee name, rate category, and hourly rate are dependent of employee id not directly with project id.

Third normal form (3NF). At the second normal form, modifications are still possible where a non-key column is a function of another non-key column. For example, using the employee table, removing a row describing an employee hourly rate, will also remove the fact that the rate category has a certain hourly rate. In the third normal form, these tables would be divided into two tables so that rate category would be tracked separately.

Domain/key normal form (DKNF). A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification inconsistency.

De-Normalised Information

Employee_project table

Project number	Project name	Employee number	Employee name	Rate
category	Hourly rate
1023	Madagascar travel site	11	Vincent Radebe	A	$60
1023	Madagascar travel site	12	Pauline James	B	$50
1023	Madagascat travel site	16	Charles Ramoraz	C	$40
1056	Online estate agency	11	Vincent Radebe	A	$60
1056	Online estate agency	17	Monique Williams	B	$50


The definition of 1st normal form
1.	there are no repeating groups
2.	all the key attributes are defined
3.	all attributes are dependent on the primary key

Employee_project table (1NF)
Project number	Employee number	Project name	Employee name	Rate
category	Hourly rate
1023	11	Madagascar travel site	Vincent Radebe	A	$60
1023	12	Madagascar travel site	Pauline James	B	$50
1023	16	Madagascat travel site	Charles Ramoraz	C	$40
1056	11	Online estate agency	Vincent Radebe	A	$60
1056	17	Online estate agency	Monique Williams	B	$50

Definition of 2nd normal form
Table is in 1st normal form
It includes no partial dependencies (where an attribute is dependent on only a part of a primary key).

Project table (2NF)

Project number	Project name
1023	Madagascar travel site
1056	Online estate agency

Employee table (2NF)
Employee number	Employee name	Rate category	Hourly rate
11	Vincent Radebe	A	$60
12	Pauline James	B	$50
16	Charles Ramoraz	C	$40
17	Monique Williams	B	$40

The definition of 3rd normal form
Table is in 2nd normal form
It contains no transitive dependencies (where a non-key attribute is dependent on another non-key attribute).

Project table (3NF)

Project number	Project name
1023	Madagascar travel site
1056	Online estate agency


Employee table (3NF)
Employee number	Employee name	Rate category
11	Vincent Radebe	A
12	Pauline James	B
16	Charles Ramoraz	C
17	Monique Williams	B

Rate table (3NF)
Rate category	Hourly rate
A	$60
B	$50
C	$40

Mike MacSween wrote:
> Read Date and Pascal.
>
> Still can't really get a handle on the higher NFs. Anybody got a
succint way
> of explaining them?
>
> Mike
Received on Wed Jan 05 2005 - 09:08:36 CET