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:
- 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:
| 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.