An Idea can change your life.....

Thursday, October 29, 2009

Rules of Data Normalization - 1NF

Make a separate table for each set of related attributes, and give each table a primary key

Codd states that the "values in the domains on which each relation is defined are required to be atomic with respect to the DBMS.

atomic value are one that "cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions)

  • There are no duplicate rows.
  • Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
First Normal Form (1NF) sets the very basic rules for an organized database:

* Eliminate duplicative columns from the same table.

* Create separate tables for each group of related data and identify each row with a unique column (the primary key).

A table is in the first normal form if it contains no repeating columns.

single value restriction. The first involves assigning several values to the same attribute:

This should be redefined as a relation with attributes Emp ID, Child, and Gender — but with only one value per attribute. The resulting table should have four rows.

Similarly, each attribute should be separated from the other attributes. Thus, the following is not allowed (or, at least, not encouraged):

The Parents attribute should be broken up into two attributes, Father and Mother.
Another common (but incorrect) way of representing the employee and child information above is as follows:

The above series of attributes ChX and GenderX is known as a repeating group.

Fix this problem by simply creating a relation with Emp ID, Child, and Gender (and four rows).

Derived data are values that can be computed from values in other attributes.

A relation should not contain any derived data. For example, an attribute should not hold the multiplicative product of two other attributes or the grand total of some other attribute.

A table is in 1NF if it adheres to the single value restriction and disallows repeating groups and derived data.

Rules to get to 1NF

1 Break up all attributes so that they are single-valued.

2 Remove all derived data attributes.

3 Define a primary identifier for the relation.

4 Remove all repeating groups. Put them in another relation.

wikipedia source on 1nf
Problems on 1NF

No comments: