Lecture-3.1: Normalization of Database, Part-2 (DBMS)

  Normalization of Database

2NF: The following criteria’s must be satisfied for 2NF –

1)                 -Table is in 1NF.

2)        -2NF eliminates partial dependency. The partial dependency here means the proper subset of the candidate key should give a non-prime attribute.

3)            -All non-key attributes are fully functionally dependent on the primary key.

Example: Location Table,

Cust-id	Str-id	Str-loc
1	D1	Toronto
2	D3	Miami
3	T1	California
4	F2	Florida
5	H3	Texas

 

The Location table possesses a composite primary key cust_id, storeid. The non-key attribute is store_location. In this case, store_location only depends on storeid, which is a part of the primary key. Hence, this table does not fulfill the second normal form.

To bring the table to the Second Normal Form, you need to split the table into two parts. This will give you the tables below:

 

Cust-id	Str-id
1	D1
2	D3
3	T1
4	F2
5	H3

 

Str-id	Str-loc
D1	Toronto
D3	Miami
T1	California
F2	Florida
H3	Texas

 

Another example, Teacher Table

T-id	Subject	Age
25	Chemistry	30
25	Biology	30
47	English	34
83	Math	38
83	Computer	38

 

Non-prime attribute age is dependent on T-id which is a proper subset of a candidate key. It violates the rule of 2NF. Now, 2NF table is:

T-id	Age
25	30
47	34
83	38

  

T-id	Subject
25	Chemistry
25	Biology
47	English
83	Math
83	Computer

 

3NF: The following criteria’s must be satisfied for 2NF –

1)    Table must be in 2NF.

2)    There is no transitive functional dependency.

Transitive functional dependency should be removed from the table and also the table must be in 2NF.

Example: Student Table

Id

Name

DOB

Street

City

State

Zip

 

In this table, Id is a primary key but Street, City, State depend upon Zip.

The dependency between Zip and other fields are transitive dependency.

New Student Table,

Id

Name

DOB

Street

Zip

 

Address table,

Zip

City

State

Street

 

Another example:

Below is a student table that has student ID, student name, subject ID, subject name, and address of the student as its columns.

Std-id	name	Sub-id	sub	address
1	Raj	11	SQL	Dhaka
2	Saj	12	Java	Khulna
3	Aaj	13	C++	Dhaka
4	Laj	12	Java	Rajshahi

 

In the above student table, stu_id determines subid, and subid determines sub. Therefore, stu_id determines sub via subid. This implies that the table possesses a transitive functional dependency and does not fulfill the third normal form criteria.

Now, to change the table to the third normal form, you need to divide the table as shown below:

Std-id	name	Sub-id	address
1	Raj	11	Dhaka
2	Saj	12	Khulna
3	Aaj	13	Dhaka
4	Laj	12	Rajshahi

 

Sub-id	sub
11	SQL
12	Java
13	C++
12	Java

 

The Third Normal Form ensures the reduction of data duplication. It is also used to achieve data integrity.

BCNF: A relation is in BCNF if and only if every determinant is a candidate key.

Consider the following functional dependency:

where A and B is attributes in relation R. It says that B is functionally dependent on A. Here, A is referred as determinant and B is dependent and A should be super key of table.

BCNF is slightly stronger than 3NF.

Example: Consider the subject table below,

sid	sub	professor
1	SQL	Raj
2	Java	Mis
2	C++	Anand
3	Java	James
4	DBMS	Lokesh

 

The subject table follows these conditions:

• Each student can enroll in multiple subjects.
• Multiple professors can teach a particular subject.
• For each subject, it assigns a professor to the student.

In the above table, student_id and subject together form the primary key because by using student_id and subject, you can determine all the table columns.

Another important point is that one professor teaches only one subject, but one subject may have two professors.

This exhibits a dependency between the subject and the professor, i.e., the subject depends on the professor's name.

The table is in 1st Normal form as all the column names are unique, all values are atomic, and all the values stored in a particular column are of the same domain.

The table also satisfies the 2nd Normal Form, as there is no Partial Dependency.

There is no Transitive Dependency; hence, the table satisfies the 3rd Normal Form.

This table follows all the Normal forms except the Boyce Codd Normal Form.

As you can see, stuid and subject form the primary key, which means the subject attribute is a prime attribute.

However, there exists yet another dependency - professor → subject.

BCNF does not follow in the table as a subject is a prime attribute, and the professor is a non-prime attribute.

To transform the table into the BCNF, you will divide the table into two parts. One table will hold sid, which already exists, and the second table will hold a newly created column Pro-id

 

sid	Pro-id
1	101
2	102
2	103
3	102
4	104

 

      The second table will have profid, subject, and professor columns, which satisfies the BCNF.

 Read more Normalization