3.

11 Normal Forms
 Normalization is the process of reorganizing data in a database so that it
meets
two basic requirements:
1) There is no redundancy of data (all data is stored in only one place),
and
2) data dependencies are logical (all related data items are stored
together)
 The normalization is important because it allows database to take up
less disk space.
 It also help in increasing the performance.

3.11.1 First Normal Form

The table is said to be in 1NF if it follows following rules -
i) It should only have single (atomic) valued attributes/columns.
ii) Values stored in a column should be of the same domain
iii) All the columns in a table should have unique names.
iv) And the order in which data is stored, does not matter.
Consider following Student table
Student
sid sname Phone
1 AAA 11111
22222

2 BBB 33333
3 CCC 44444
55555

As there are multiple values of phone number for sid 1 and 3, the above
table is not in 1NF. We can make it in 1NF. The conversion is as follows -
sid sname Phone
1 AAA 11111
1 AAA 22222
2 BBB 33333
3 CCC 44444
3 CCC 55555
 3.11.2 Second Normal Form
Before understanding the second normal form let us first discuss the
concept of partial functional dependency and prime and non prime attributes.

Concept of Partial Functional Dependency

Partial dependency means that a nonprime attribute is functionally
dependent on part of a candidate key.
For example : Consider a relation R(A,B,C,D) with functional
dependency
{AB->CD,A->C}
Here (AB) is a candidate key because
(AB)+ = {ABCD}={R}
Hence {A,B} are prime attributes and {C,D} are non prime attribute. In A-
>C, the non prime attribute C is dependent upon A which is actually a part of
candidate key AB. Hence due to A->C we get partial functional dependency.

Prime and Non Prime Attributes

 Prime attribute : An attribute, which is a part of the candidate-key,
is known as a prime attribute.
 Non-prime attribute : An attribute, which is not a part of the prime-
key, is said to be a non-prime attribute.
 Example : Consider a Relation R={A,B,C,D} and candidate key as
AB, the Prime attributes : A, B
Non Prime attributes : C, D
The Second Normal Form
For a table to be in the Second Normal Form, following conditions must be
followed
i) It should be in the First Normal form.
ii) It should not have partial functional dependency.
For example : Consider following table in which every information about a
the Student is maintained in a table such as student id(sid), student
name(sname), course id(cid) and course name(cname).

Student_Course
sid sname cid cname
1 AAA 101 C
2 BBB 102 C++
3 CCC 101 C
4 DDD 103 Java
This table is not in 2NF. For converting above table to 2NF we must follow
the following steps -
Step 1 : The above table is in 1NF.
Step 2 : Here sname and sid are associated similarly cid and cname
 are associated with each other. Now if we delete a record with sid=2,
then automatically the course C++ will also get deleted. Thus,
sid->sname or cid->cname is a partial functional dependency, because
{sid,cid} should be essentially a candidate key for above table. Hence
to bring the above table to 2NF we must decompose it as follows :

Student
Here candidate key is
sid sname cid (sid,cid)
and
1 AAA 101 (sid,cid)->sname
2 BBB 102
3 CCC 101
4 DDD 103

Course
cid cname
Here candidate key is
101 C cid
Here cid-
102 C++
>cname
101 C
103 Java

Thus now table is in 2NF as there is no partial functional dependency

3.11.3 Third Normal Form
Before understanding the third normal form let us first discuss the concept
of transitive dependency, super key and candidate key

Concept of Transitive Dependency

A functional dependency is said to be transitive if it is indirectly formed by
two functional dependencies. For example -
X -> Z is a transitive dependency if the following functional
dependencies hold true : X->Y
Y->Z
Concept of Super key and Candidate Key
Superkey : A super key is a set or one of more columns (attributes) to
uniquely identify rows in a table.
Candidate key : The minimal set of attribute which can uniquely
identify a tuple is known as candidate key. For example consider
following table
RegID RollNo Sname
101 1 AAA
 102 2 BBB
103 3 CCC
104 4 DDD

Superkeys
 {RegID}
 {RegID, RollNo}
 {RegID,Sname}
 {RollNo,Sname}
 {RegID, RollNo,Sname}

Candidate Keys
 {RegID}
 {RollNo}

Third Normal Form

A table is said to be in the Third Normal Form when,
i) It is in the Second Normal form.(i.e. it does not have partial functional
dependency)
ii) It doesn't have transitive
dependency. Or in other words
In other words 3NF can be defined as : A table is in 3NF if it is in 2NF and
for each functional dependency
X- > Y

at least one of the following conditions hold :

i) X is a super key of table
ii) Y is a prime attribute of table
 For example : Consider following table Student_details as follows -
sid sname zipcode cityname state
1 AAA 11111 Pune Maharashtra
2 BBB 22222 Surat Gujarat
3 CCC 33333 Chennai Tamilnadu
4 DDD 44444 Jaipur Rajastan
5 EEE 55555 Mumbai Maharashtra
Here
Super keys : {sid},{sid,sname},{sid,sname,zipcode}, {sid,zipcode,cityname}… and
so on.
Candidate keys : {sid}
Non-Prime attributes : {sname,zipcode,cityname,state}
The dependencies can be
denoted as sid->sname
sid->zipcode
zipcode-
>cityname
cityname->state
The above denotes the transitive dependency. Hence above table is not in 3NF.
We can convert it into 3NF as follows :
Student
sid sname zipcode
1 AAA 11111
2 BBB 22222
3 CCC 33333
4 DDD 44444
5 EEE 55555

Zip
zipcode cityname state
11111 Pune Maharashtra
22222 Surat Gujarat
33333 Chennai Tamilnadu
44444 Jaipur Rajasthan
55555 Mumbai Maharashtra
3.12 Boyce / Codd Normal Form (BCNF)
Boyce and Codd Normal Form is a higher version of the Third Normal
form. This form deals with certain type of anomaly that is not handled by 3NF.
A 3NF table which does not have multiple overlapping candidate keys
is said to be in BCNF.
Or in other words,
For a table to be in BCNF, following conditions must be satisfied :
i) R must be in 3rd Normal Form
ii) For each functional dependency ( X → Y ), X should be a super Key.
In simple words if Y is a prime attribute then X can not be non prime
attribute.
For example - Consider following table that represents that a Student
enrollment for the course -

Enrollment Table
sid course Teacher
1 C Ankita
1 Java Poonam
2 C Ankita
3 C++ Supriya
4 C Archana
 From above table following observations can be made :
 One student can enroll for multiple courses. For example student
with sid=1 can enroll for C as well as Java.
 For each course, a teacher is assigned to the student.
 There can be multiple teachers teaching one course for example
course C can be taught by both the teachers namely - Ankita and
Archana.
 The candidate key for above table can be (sid,course), because
using these two columns we can find
 The above table holds following dependencies
o (sid,course)->Teacher
o Teacher->course
 The above table is not in BCNF because of the dependency teacher-
>course. Note that the teacher is not a superkey or in other words,
teacher is a non prime attribute and course is a prime attribute and
non-prime attribute derives the prime attribute.
 To convert the above table to BCNF we must decompose above table
into Student and Course tables

Student
sid Teacher
1 Ankita
1 Poonam
2 Ankita
3 Supriya
4 Archana

Course
Teacher course
Ankita C
Poonam Java
Ankita C
Supriya C++
Archana C

Now the table is in BCNF