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.
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:
|10||Management||E01||Usman Khalil abdullah|
|20||Finance||E10E11||Ali Ahmed Mahmood Abbas|
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:
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:
- 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.
- 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:
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.