When is a relation in first normal form? Explain with example.

A relation is in first normal form (1NF) if every intersection of row and column contains atomic values only. It means that the relation does not contain any repeating group. A repeating group is a set of one or more data items that may occur a variable number of times in a duple. Each cell in a relation should contain only one value.

A relation can be converted into 1NF using two methods:

Method 1: In this method, the repeating groups are removed by entering proper data in blank fields that contain repeating data.

Method 2: In this method, the repeating groups are removed by creating a separate relation for the repeating group. The repeating data is stored in the new relation.

Example

Suppose we have the following un-normalized relation DEPARTMENT:

DEPARTMENT (Dept No, Dept_Name, Emp_No, Emp_Name)

Suppose the data in the relation is as follows:

 Dept_No Dept_Name Emp_No Emp_Name 10 Management E01 Usman Khalil abdullah 20 Finance E10E11 Ali Ahmed Mahmood Abbas 30 IT E25 Hamid Ali

Table 4.1: The DEPARTMENT relation

Applying Method 1

The above relation contains repeating groups. The attributes Emp_Noand Emp_Name are being repeated for single occurrence of Dept_No. The repeating groups are removed by entering proper data in the blank fields as follows:

 Dept_No Dept_Name Emp_No Emp_Name 10 Management E01 Usman Khalil 10 Management E02 Abdullah 20 Finance E10 Ali Ahmed 20 Finance E11 Mahmood Abbas 30 IT E25 Hamid Ali

The above relation is now in 1NF as each field contains single value. It will have a composite key that consist of Dept_No and Emp_No attributes.

Applying Method 2

The relation in Table 1.4 contains repeating groups. The attributes Emp_No and Emp_Name are being repeated for single occurrence of Dept_No.

The Following steps are taken to remove the repeating groups:

1.  The repeating group must be removed from the relation. It can be stored in a separate relation to represent an entity. It means that the above relation will be divided into two relations.
2. Create a relationship between the new relations.

The attributes Emp_No and Emp_Name represent EMPLOYEE entity. They can be stored in a separate relation. This process will convert the above relation in two relations DEPT and EMP as follows:

 Dept_No Dept_Name 10 Mangement 20 Finance 30 IT
 Dep_No Emp_No Emp_Name 10 E01 Usman Khalil 10 E02 Abdullah 20 E10 Ali Ahmed 20 E11 Mahmood Abbas 30 E25 Hamid Ali

Figure 4.3(a): The DEPT relation

Figure 4.3 (b): The EMP relation

The relationship between the above two relations is 1:M. It means that one department has many employees but one employee has only one department.