# Normalization

The normalization is especially to eliminate the following anomalies:

Insertion anomaly

Deletion anomaly

Update anomaly

Join anomaly

Integrity

Maintainability

Reduced storage space required ( usually, but it could increase).

Simpler queries ( Sometimes, but some could be more complex).

Simpler updates ( sometimes, but some could be more complex).

Relation should not contain any multi-valued, attribute.

Relation R is in 2NF iff

R should be in 1NF

R should not contain any partial dependency.

Let R be the relation schema and X,Y,A are non empty set of attributes.

X : Any candidate key

A : Non-prime attribute

Y : Proper subset of any candidate key.

Y→A is said to be partial dependency iff

Y is proper subset of candidate key.

A should be non-prime attribute.

Let R be the relation schema X→Y any non-trivial functional dependency over R is in 3NF iff

R should be in 2NF

X should be candidate key or super key, or

Y should be prime attribute.

Let R be relational schema X→Y be any non-trivial functional dependency over R is BCNF iff X is candidate key / Super Key.

X : candidate key or super key or

X ⋃ Y = R

R be the relational schema x,y,z attribute sets over R and t1,t2,t3,t4 are tuples over R.

X→→Y only if

1. t1.X = t2.X = t3.X = t4.X

2. t1.Y = t2.Y = t3.Y = t4.Y

3. t1.Z = t2.Z = t3.Z = t4.Z

If X → → Y

then X → → R - ( X ⋃ Y )

X → → Z

A multivalued dependency X → → Y is said to be trivial multivalued dependency:

if X⊇ Y or X⊎Y ≡ R

Otherwise non-trivial multivalued dependency

Equality of FD Set

FD sets F and G are said to be equal iff

(a) F covers G, and

(b) G covers F

Minimal cover or canonical cover is the one in which,

Every FD is simple ( RHS of any FD should have single attribute)

Example: F = { A→BC }, here F = ( A→B , A→C} is simple.

It is left reduced ( removal of extraneous symbols )

Example: F = { AB→C , B→C}, here B is extraneous attribute.

So F = { A→C, B→C}.

It is non-redundant (eliminating unnecessary FDs)

Example: F = { A→B , B→C , A→C }, here A→C is redundant,

Hence, F = { A→B , B→C}.

Foreign Key is a set of attributes that reference primary key or alternative key of the same relation or other relation.

(a) On delete no action : Means if it causes problem on delete then not allowed to delete.

(b) On delete cascade : If we want to delete primary key value from referenced table it will delete that value from referencing table also.

(c) On delete set null : If we want to delete primary key from referenced table then it will try to set the null values in place of that value in referencing table.

(a) On update no action

(b) On update cascade

(c) On update set null

Insertion: May cause violation

Deletion: No violation

Updation: May cause violation

Example:

C is foreign key referencing A

Delete ( 2,4 ) and on delete cascade.

Insertion anomaly

Deletion anomaly

Update anomaly

Join anomaly

**Goals of Normalization**Integrity

Maintainability

**Side Effects of Normalization**Reduced storage space required ( usually, but it could increase).

Simpler queries ( Sometimes, but some could be more complex).

Simpler updates ( sometimes, but some could be more complex).

**First Normal From**Relation should not contain any multi-valued, attribute.

**Second Normal Form**Relation R is in 2NF iff

R should be in 1NF

R should not contain any partial dependency.

**Partial Dependency**Let R be the relation schema and X,Y,A are non empty set of attributes.

X : Any candidate key

A : Non-prime attribute

Y : Proper subset of any candidate key.

Y→A is said to be partial dependency iff

Y is proper subset of candidate key.

A should be non-prime attribute.

**Third Normal From**Let R be the relation schema X→Y any non-trivial functional dependency over R is in 3NF iff

R should be in 2NF

X should be candidate key or super key, or

Y should be prime attribute.

**BCNF ( Boyce Codd Normal Form )**Let R be relational schema X→Y be any non-trivial functional dependency over R is BCNF iff X is candidate key / Super Key.

**4NF :**Let R be the relational schema. F be the single and multivalued dependency X→→Y is in 4NF iffX : candidate key or super key or

X ⋃ Y = R

**Multivalued Dependency**R be the relational schema x,y,z attribute sets over R and t1,t2,t3,t4 are tuples over R.

X→→Y only if

1. t1.X = t2.X = t3.X = t4.X

2. t1.Y = t2.Y = t3.Y = t4.Y

3. t1.Z = t2.Z = t3.Z = t4.Z

**complement Rule**If X → → Y

then X → → R - ( X ⋃ Y )

X → → Z

A multivalued dependency X → → Y is said to be trivial multivalued dependency:

if X⊇ Y or X⊎Y ≡ R

Otherwise non-trivial multivalued dependency

Equality of FD Set

FD sets F and G are said to be equal iff

(a) F covers G, and

(b) G covers F

**MINIMAL COVER OR CANONICAL COVER**Minimal cover or canonical cover is the one in which,

Every FD is simple ( RHS of any FD should have single attribute)

Example: F = { A→BC }, here F = ( A→B , A→C} is simple.

It is left reduced ( removal of extraneous symbols )

Example: F = { AB→C , B→C}, here B is extraneous attribute.

So F = { A→C, B→C}.

It is non-redundant (eliminating unnecessary FDs)

Example: F = { A→B , B→C , A→C }, here A→C is redundant,

Hence, F = { A→B , B→C}.

**REFERENTIAL INTEGRITY CONSTRAINTS****Foreign Key**Foreign Key is a set of attributes that reference primary key or alternative key of the same relation or other relation.

**Referenced Relation****1. Insertion :**no violation.**2. Deletion:**(a) On delete no action : Means if it causes problem on delete then not allowed to delete.

(b) On delete cascade : If we want to delete primary key value from referenced table it will delete that value from referencing table also.

(c) On delete set null : If we want to delete primary key from referenced table then it will try to set the null values in place of that value in referencing table.

**3. Updation :**(a) On update no action

(b) On update cascade

(c) On update set null

**Referencing Relation**Insertion: May cause violation

Deletion: No violation

Updation: May cause violation

Example:

C is foreign key referencing A

Delete ( 2,4 ) and on delete cascade.

## No comments