Handbook of Computer Science(cs) and IT

sql

Normal forms/Normalization

In relation database desing, the normalization is the process for organizing data to minimize redundancy. Normalization usually involves dividing a data base into two or more tables and defining relationship between the tables.The  normal  forms define the status of the relation about the individuated attributes. There are five of normal forms.

 

First Normal Form (INF)

Relation should not contain any contain any multivalued attributes or relation should contain atomic attributes.

 

 

 

 

 

 

 

 

The above  relation is in 1 NF but now Stud_Id is no more a primary key. Now in the above case, we need to modify our primary key which is (Stud_Id, Course-Name)

Note The main disadvantage of 1NF is high redundancy.

Second Normal Form (2NF)

Relation R is in 2NF  if and only if

• R should be in 1NF.
• R should not contain any partial dependency.

 

Partial Dependency

 

Let R be the relational schema having X, Y, A, which are non- empty set of attributes ,where

X= Any candidate key of the relation .

Y= Proper subset of any candidate key

A = Non- prime attribute (i.e., A doesn’t belong to any candidate key)

In the above example, X A already exists and if Y -> A will exist  then it  will become a partial dependency, if and only if

 

 

• Y is a proper subset of candidate key.
• A should be non-prime attribute.

If any of the above two conditions fail, then Y -> A will also become fully functional dependency

 

Full Functional Dependency

A functional dependency P is said to be fully functional dependency, if

removal of any attribute S from P means that the dependency doesn’t hold any more.

(Student_Name, College_Name –> College_Address)

Suppose, the above functional dependency is • a full functional

dependency, then we must ensure that there are no FDs as below. (Student_Name —> College_Address)

or                         (College_Name -> Collage_Address)

Third Normal Form (3NF)

R be a relational schema, then any non-trivial FD X –>Y over R is in 3NF, if

• X should be a candidate key or super key.

or

• Y should be a prime attribute.
• Either both of the above conditions should be true or one of them should be true.
• R should not contain any transitive dependency.
• For a relation schema R to be a 3NF, it is necessary to be in 2NF.

 

Transitive Dependency

A FD, Pà Q in a relation schema R is a transitive if

 

• There is a set _of attributes Z that is not a subset of any key of R.

 

• Both X à Z and Z à Y hold

 

 

 

• The above relation is in 2NF.
• In relation R, C is not a candidate key and D is non-prime attribute. Due to this, R₁ fails to satisfy 3NF condition. Transitive dependency is present

here.

AB —-> C and C    D, then AB —-> D will be transitive.

Boycee Codd Normal Form (BCNF)

Boycee Codd Normal Form (BCNF) Let R be the relation schema and X –>Y be the any  non-trival FD over R is in BCNF if and only if X is the candidate key or super key.

X –>Y                                                                                If R satisfies this dependency, then of course it satisfy 2NF and                                                                                         3NF.

candidate / super key

Hierarchy of normal forms

 

 

Fourth Normal Form (4NF)

4NF is mainly concerned with multivalued dependency. A relation is in 4NF if and only if for every one of its non-trivial multivalued dependencies X ààH Y, X is a super key (i.e., X is either a candidate key or a superset).

Fifth Normal Form (5NF)

It is also known as Project Join Normal From (PJ/NF). 5NF reduces redundancy in relational database recording multivalued facts by isolating semantically related multiple relationships.

A table or relation is said to be in the 5NF, if and only if every join dependency in it, is implied by the candidate keys.

 

Key Points

• The Normal Forms (NF) of relational database theory provide criteria fo^rr
• determining a table’s degree of vulnerability to logical inconsistencies an anomalies
• Databases intended for Online Transaction Processing (OLTP) are typically more normalized than databases intended for Online Analytical Processing (OLAP).
• OLTP applications are characterized by a high volume of small transactions such as updating a sales record at a supermarket checkout counter.
• The expectation is that each transaction will leave the database in a consistent state.