Q51608 What do you understand by First Normal Form?

Answer:

The First Normal Form states that:

There should be no repeating groups in a column. In other words, columns in the table should have atomic values.

A relation R is said to be in the first normal form (1NF) if and only if every attribute contains atomic values only.

The table contains atomic values if there is one and only one data item for any given row and column intersection. Non-atomic values result in repeating groups. A repeating group is the reoccurrence of a data item or group of data items within records.

Example: Consider an Employee table with an attribute Dependents as shown below:

ID	Name	DeptNo	Sal	Mgr	Dependents
131	Ram	20	10000	134	Father, Mother, Sister
132	Kiran	20	7000	136	Wife, Son
133	Rajesh	20	5000	136	Wife
134	Padma	10	20000		Son, Daughter
135	Devi	30	3000	137	Father, Mother
136	Satish	20	6000		Father, Mother
137	V.V. Rao	30	10000		Wife, First Son, Second Son

Here, the dependents column has non-atomic values. To make the relation in INF, we have to convert the non-atomic values into atomic values as follows:

ID	Name	DeptNo	Sal	Mgr	Dependents
131	Ram	20	10000	134	Father
131	Ram	20	10000	134	Mother
131	Ram	20	10000	134	Sister
132	Kiran	20	7000	136	Wife
132	Kiran	20	7000	136	Son
133	Rajesh	20	5000	136	Wife
134	Padma	10	20000		Son
134	Padma	10	20000		Daughter
135	Devi	30	3000	137	Father
135	Defi	30	3000	137	Mother
136	Satish	20	6000		Father
137	V.V. Rao	30	10000		Wife
137	V.V. Rao	30	10000		First Son
137	V.V. Rao	30	10000		Second Son

The dependents column in the above table is having atomic values. Observe that for each dependent the other employee details such as ID, Name, Dept No, Sal and Mgr are repeated and will form as a repeating group. As per the definition of INF, the above relation is in INF. However, it is best practice to remove the repeating groups in the table.

Repeating groups refers to any set of columns whose values are related and repeat in the table. According to the rule of first normal form, the table should not have repeating groups of column values.

If there are such groups in the table, the table should be decomposed and the associated columns will form their own table while at the same time ensuring a link of this table with the original table (from where it was decomposed). Thus, the Employee relation can be divided into two relations namely Emp and Emp_Depend as follows:

After first normal form, this table is divided into two tables: Customer and Customer Tran.

Table before First Normal Form Reports Table

Cust_ id	Name	Address	Acc_ id	Acc_ type	Min_ bal	Tran_ id	Tran_ type	Tan_ mode	Amount	Balance
001	Ravi	Hyd	994	SB	1000	14300		B/F	1000	1000
001	Ravi	Hyd	994	SB	1000	14301	Deposit	Bycash	1000	2000
001	Ravi	Hyd	994	SB	1000	14302	Withdrawal	ATM	500	1500
110	Tim	Sec’bad	340	CA	500	14303		B/F	3500	3500
110	Tim	Sec ‘bad	340	CA	500	14304	Deposit	Payroll	3500	7000
110	Tim	Sec’bad	340	CA	500	14305	Withdrawal	ATM	1000	6000
420	Kavi	Vizag	699	SB	1000	14306		B/F	6000	6000
420	Kavi	Vizag	699	SB	1000	14307	Credit	Bycash	2000	8000
420	Kavi	Vizag	699	SB	1000	14308	Withdrawal	ATM	6500	1500

Tables after First Normal Form

Cust_id	Name	Address
001	Ravi	Hyd
110	Tim	Sec’bad
420	Kavi	Vizag

Tran_id	Cust_id	Acc_id	Acc_type	Min_bal	Tran_type	Tan_mode	Amount	Balance
14300	001	994	SB	1000		B/F	1000	1000
14301	001	994	SB	1000	Deposit	Bycash	1000	2000
14302	001	994	SB	1000	Withdrawal	ATM	500	1500
14303	110	340	CA	500		B/F	3500	3500
14304	110	340	CA	500	Deposit	Payroll	3500	7000
14305	110	340	CA	500	Withdrawal	ATM	1000	6000
14306	420	699	SB	1000		B/F	6000	6000
14307	420	699	SB	1000	Credit	Bycash	2000	8000
14308	420	699	SB	1000	Withdrawal	ATM	6500	1500

Since Cust_id, Name and Address form a repeating group and hence the Reports table is decomposed into Customer and Customer_Tran tables. (The primary key columns of each table are indicated in bold in figures).

The requirements for a table to be INF is exactly what relational database theory specifies as essential. That is, a table in a relational database must always be in INF.