DBMS First Normal Form (1NF)

DBMS First Normal Form (1NF)

A relation is sent to be in particular normal form if it satisfies a certain specified set of constraints.

Database Normalization is classified into the following Normal forms:

First Normal Form (1NF)

A relation is, in first normal form if and only if all underlying domains contain atomic values only.

The aim of the first normal form (1NF) is to make the structure of a relation simple by making sure that it does not have data aggregates or repeating groups. This means that none of the attribute values can contain a collection of values.

It specifies that the domains of attributes are inclusive of only atomic values and that any attribute value in a tuple must be an individual value from the domain of that attribute.

Normalization Procedure

The following table is 1NF.

Transformation to 1NF

Method1: By splitting the relation the relations into new relations.

Method2: By adding new tuples in the same relation.

Method3: By adding new attributes with NULL values.

In the following example, anyone employee has more than one telephone number.

EMP

This is thus not in 1NF. This can be managed by ensuring that every tuple defines a single entity by containing only atomic values. One can either reorganize into one relation as in:

EMP

Or split into multiple relations as in:

EMP

EMP-TEL

Reduction to 1NF

Example

Consider the relation FIRST (S#, STATUS, CITY, P#, QTY) as shown in table.

FIRST

Here we assume that each supplier has a unique supplier number (S#), precisely one status code and location. A supplier can supply different parts. Further, we assume that STATUS is functionally dependent on CITY. Therefore all Delhi suppliers must have a status of 40.

Figure display the functional dependency structure for this relation. The key to FIRST relation is the combination of (S#, P#).

It is clear that the relation FIRST is in the first normal form. The relation FIRST has the following modification anomalies.

1. Deletion Anomaly

   If we delete the FIRST tuple with S# value S3 and P# value P1, we lose the information that S3 is located in Karnal. Hence, the relation FIRST has a deletion anomaly.

2. Insertion Anomaly

   We cannot enter the fact that a particular supplier is located in a specific supplier city until the supplier supplies at least one part. So, we cannot enter the fact that supplier S5 is located in Rohtak until S5 supplies at least one part.

3. Updation Anomaly

   It supplies S1 moves from Delhi to Chandigarh, and we are faced with either the problem of searching the FIRST relation to find every tuple connecting S1 and Delhi and change it or the possibility of producing an inconsistent result (the city for S1 may be given as Delhi in one place and Chandigarh in another).

   The solution to these problems is to restore the relation FIRST by two relation SECOND (S#, STATUS, CITY) and SP (S#, P#, QTY). The figure shows sample tabulation corresponding to the data values.

SECOND

SP