DBMS Normal Forms

DBMS Normal Forms

Database normalisation or DBMS Normal forms are the processes to reduce or minimise the redundancy in a table or relation of a database. Redundancy simply means the duplication of records in relation. Redundancy in a relation may cause insertion, deletion, updating anomalies. So, Normalisation helps in minimising the redundancy in a relational database. Normal forms are used to eliminate duplicate data from a table in a database.

Types of DBMS Normal forms

Basically, there are three types of normal forms in the database.

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

First normal form (1NF)

If a table or relation contains composite or multi-valued attribute/column, then it violets the first normal form, In other words,

A table is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute of that table is singled valued attribute.

Example 1 - Table STUDENT in the table1 violets first normal form because it contains composite attribute STD_PHONE. STUDENT table1 has been converted to the first normal form in STUDENT table2.

STD_NO	STD_NAME	STD_PHONE	STD_STATE	STD_COUNTRY
1	Ravi	9716271721, 9871717178	Assam	India
2	Ravi	9932874201	Delhi	India
3	Suresh		Punjab	India

Table1

Conversion to the first normal form ↓

STD_NO	STD_NAME	STD_PHONE	STD_STATE	STD_COUNTRY
1	Ravi	9716271721	Assam
1	Ravi	9871717178	Assam	India
2	Ravi	9932874201	Delhi	India
3	Suresh		Punjab	India

Table2

Example 2 -

ID	NAME	COURSE
1	A	C1, C2
2	B	C3
3	C	C2, C3

↓ Table after normalisation bellow:

ID	NAME	COURSE
1	A	C1
1	A	C2
2	B	C3
3	C	C2
3	C	C3

Second Normal Form (2NF)

Now, before moving to the second normal form let us know a term related to second DBMS normal forms.

Partial Dependency: If a proper subset of the candidate key determines non-prime attribute, it is called partial dependency.

A database table is in second normal form if it satisfies the following two conditions-

• The table is already in first normal form.
• All the non-key attribute are fully dependent on the primary key, in other words, no partial dependency.

Example-

STUDENT

STD_ID	COURSE_ID	SUBJECT
1	C1	Computer Networking
1	C3	Mathematics
2	C1	Computer Networking
3	C2	DBMS
4	C3	Mathematics

This table has a composite primary key [STD_ID, COURSE_ID]. The non-key attribute is [SUBJECT]. In this case, [SUBJECT] only depends on [STD_ID], which is only part of the primary key. Therefore, this table does not satisfy the conditions of the second normal form.

To convert the table into second normal form, we need to break the table into two tables, and now we have the following-

Student1

STD_ID	COURSE_ID
1	C1
1	C3
2	C1
3	C2
4	C3

Student2

COURSE_ID	SUBJECT
C1	Computer Networking
C2	DBMS
C3	Mathematics

Here we have removed the partial functional dependency that the table initially had. Now, in the table [Student2], the column [SUBJECT] is fully dependent on the primary key of the Student2 table which is [COURSE_ID].

Third Normal Form (3NF)

A database table is in third normal form if it is in second normal form and there is no transitive functional dependency.

What is the transitive functional dependency in DBMS normal forms?

Transitive functional dependency: Transitive functional dependency, we mean we have the following relationship in the table T. Attribute A of table T is functionally dependent on attribute B, and B is functionally dependent on C. In such case, C is transitively dependent on A via B.

Example 3NF-

Item_Price_Detail

ITEM_ID	ITEM_TYPE	ITEM_CLASS	PRICE
1	1	Pulses	70.00
2	2	Wheat	60.00
3	1	Pulses	30.00
4	3	Rice	35.00
5	2	Wheat	65.00

In the above Item_price_Detail table, [ITEM_ID] determines [ITEM_TYPE], and [ITEM_TYPE] determines [ITEM_CLASS]. Therefore, [ITEM_ID] determines [ITEM_CLASS] through [ITEM_TYPE] and transitive functional dependency which deos not satisfy the third normal form.

To convert this table into the third normal form we break it into two different tables-

Item_Price

ITEM_ID	ITEM_TYPE	PRICE
1	1	70.00
2	2	60.00
3	1	30.00
4	3	35.00
5	2	65.00

Item_Detail

ITEM_TYPE	ITEM_CLASS
1	Pulses
2	Wheat
3	Rice

Here, all non-key attributes are fully dependent on the primary key. In table Item_Price both [ITEM_TYPE] and [PRICE] are only dependent on [ITEM_ID]. In table Item_Detail[ITEM_CLASS] is fully dependent on [ITEM_TYPE].

Please like and share if this article is helpful. Also, like our facebook page https://www.facebook.com/technologies4web/