Normalization

NORMALIZATION

Normalization  is a technique for producing a set of relations with desirable properties, given the data requirements of an enterprise.

The process of normalization is a formal method that identifies relations based on their primary or candidate / foreign keys and the functional dependencies among their attributes.

 The Process of Normalization

•Normalization is often executed as a series of steps. Each step corresponds to a specific normal form that has known properties.


•As normalization proceeds, the relations become progressively more restricted in format, and also less vulnerable to update anomalies.


•For the relational data model, it is important to recognize that it is only first normal form (1NF) that is critical in creating relations. All the subsequent normal forms are optional.


Unnormalized form (UNF)
A table that contains one or more repeating groups

 ClinetRental

ClientNo
cName
propertyNo
pAddress
rentStart
rentFinish
rent
ownerNo
oName
CR76
John
kay
PG4

PG16
6 lawrence
St,Glasgow

5 Novar Dr,
Glasgow
1-Jul-00


1-Sep-02

31-Aug-01


1-Sep-02

350


450

CO40


CO93

Tina Murphy

Tony Shaw


CR56

Aline
Stewart

PG4


PG36


PG16

6 lawrence
St,Glasgow

2 Manor Rd,
Glasgow

5 Novar Dr,
Glasgow

1-Sep-99

10-Oct-00

1-Nov-02

10-Jun-00

1-Dec-01

1-Aug-03

350


370


450

CO40


CO93


CO93

Tina Murphy

Tony Shaw

Tony Shaw









Repeating group = (propertyNo, pAddress, rentStart, rentFinish, rent, ownerNo, oName)

 

Definition of 1NF

First Normal Form is a relation in which the intersection of each row and column contains one and only one value.

There are two approaches to removing repeating groups from unnormalized tables:

1.Removes the repeating groups by entering appropriate data in the empty columns of rows containing the repeating data.

2.       Removes the repeating group by placing the repeating data, along with a copy of the original key attribute(s), in a separate relation. A primary key is identified for the new relation

1NF ClientRental relation with the first approach

 With the first approach, we remove the repeating group (property rented details) by entering the appropriate client data into each row.

The ClientRental relation is defined as follows:

ClientRental ( clientNo, propertyNo, cName, pAddress, rentStart, rentFinish, rent,         ownerNo, oName)








ClientRental:

ClientNo
propertyNo
cName
pAddress
rentStart
rentFinish
rent
ownerNo
oName
CR76
PG4
John
Kay

6 lawrence
St,Glasgow

1-Jul-00

31-Aug-01

350

CO40

Tina Murphy

CR76
PG16
John
Kay
5 Novar Dr,
Glasgow
1-Sep-02

1-Sep-02

450

CO93

Tony Shaw

CR56
PG4
Aline
Stewart
6 lawrence
St,Glasgow
1-Sep-99
10-Jun-00
350
CO40
Tina Murphy
CR56
PG36
Aline
Stewart

2 Manor Rd,
Glasgow
10-Oct-00
1-Dec-01

370

CO93

Tony Shaw

CR56
PG16
Aline
Stewart
5 Novar Dr,
Glasgow
1-Nov-02
1-Aug-03
450
CO93
Tony Shaw


1NF ClientRental relation with the second approach


With the second approach, we remove the repeating group (property rented details) by placing the repeating data along with a copy of the original key attribute (clientNo) in a separte relation.


Client (clientNo, cName)
PropertyRentalOwner (clientNo, propertyNo, pAddress, rentStart,rentFinish, rent, ownerNo, oName)



Client:

ClientNo
Cname
CR76
John Kay
CR56
Aline Stewart


PropertyRentalOwner:

ClientNo
propertyNo
pAddress
rentStart
rentFinish
rent
ownerNo
oName
CR76
PG4
6 lawrence
St,Glasgow

1-Jul-00

31-Aug-01

350

CO40

Tina Murphy

CR76
PG16
5 Novar Dr,
Glasgow
1-Sep-02

1-Sep-02

450

CO93

Tony Shaw

CR56
PG4
6 lawrence
St,Glasgow
1-Sep-99
10-Jun-00
350
CO40
Tina Murphy
CR56
PG36
2 Manor Rd,
Glasgow
10-Oct-00
1-Dec-01

370
CO93

Tony Shaw

CR56
PG16
5 Novar Dr,
Glasgow
1-Nov-02
1-Aug-03
450
CO93
Tony Shaw


Second Normal Form (2NF)


Second normal form (2NF) is a relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key.

The normalization of 1NF relations to 2NF involves the removal of partial dependencies. If a partial dependency exists, we remove the function dependent attributes from the relation by placing them in a new relation along with a copy of their determinant.



The ClientRental relation has the following functional dependencies:


fd1     clientNo, propertyNo & rentStart, rentFinish         (Primary Key)
fd2     clientNo & cName                                  (Partial dependency)
fd3     propertyNo & pAddress, rent, ownerNo, oName     (Partial dependency)
fd4     ownerNo & oName                        (Transitive Dependency)
fd5     clientNo, rentStart & propertyNo, pAddress,  rentFinish, rent, ownerNo, oName           (Candidate key)
fd6     propertyNo, rentStart & clientNo, cName, rentFinish        (Candidate key)



2NF ClientRental relation

After removing the partial dependencies, the creation of the three
new relations called Client, Rental, and PropertyOwner


Client                  (clientNo, cName)
Rental       (clientNo, propertyNo, rentStart, rentFinish)
PropertyOwner  (propertyNo, pAddress, rent, ownerNo, oName)
 

Client:                                                 Rental:


ClientNo
Cname
CR76
John Kay
CR56
Aline Stewart

ClientNo

Property

No

renStart

renFinish

CR76

PG4

1-Jul-00

31-Aug-01

CR76

PG16

1-Sep-02

1-Sep-02

CR56

PG4

1-Sep-99

10-Jun-00

CR56

PG36

10-Oct-00

1-Dec-01

CR56

PG16

1-Nov-02

1-Aug-03

PropertyOwner:

propertyNo
pAddress
rent
ownerNo
oName
PG4
6 lawrence St,Glasgow
350
CO40
Tina Murphy
PG16
5 Novar Dr, Glasgow
450
CO93
Tony Shaw
PG36
2 Manor Rd, Glasgow
370
CO93
Tony Shaw


Third Normal Form (3NF)

A relation that is in first and second normal form, and in which no non-primary-key attribute is transitively dependent on the primary key.

Transitive dependency:
A condition where A, B, and C are attributes of a relation such that if A & B and B & C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C).

The normalization of 2NF relations to 3NF involves the removal of transitive dependencies by placing the attribute(s) in a new relation along with a copy of the determinant.

The functional dependencies for the Client, Rental and PropertyOwner relations are as follows:

Client
fd2     clientNo & cName (Primary Key)


Rental
fd1     clientNo, propertyNo & rentStart, rentFinish                   (Primary Key)
fd5     clientNo, rentStart & propertyNo, rentFinish                   (Candidate key)
fd6     propertyNo, rentStart & clientNo, rentFinish                   (Candidate key)

PropertyOwner
fd3     propertyNo & pAddress, rent, ownerNo, oName               (Primary Key)
fd4     ownerNo & oName                        (Transitive Dependency)

3NF ClientRental relation


The resulting 3NF relations have the forms:

Client               (clientNo, cName)
Rental              (clientNo, propertyNo, rentStart, rentFinish)
PropertyOwner  (propertyNo, pAddress, rent, ownerNo)
Owner              (ownerNo, oName)









 Client:                                                 Rental:

ClientNo
Cname
CR76
John Kay
CR56
Aline Stewart

ClientNo

Property

No

renStart

renFinish

CR76

PG4

1-Jul-00

31-Aug-01

CR76

PG16

1-Sep-02

1-Sep-02

CR56

PG4

1-Sep-99

10-Jun-00

CR56

PG36

10-Oct-00

1-Dec-01

CR56

PG16

1-Nov-02

1-Aug-03

                                                                

PropertyOwner:                                                                            Owner:

propertyNo
pAddress
rent
ownerNo
PG4
6 lawrence St,Glasgow
350
CO40
PG16
5 Novar Dr, Glasgow
450
CO93
PG36
2 Manor Rd, Glasgow
370
CO93
OwnerNo
ownerName
CO40
Tina Murphy
CO93
Tony Shaw

 
Boyce-Codd normal form (BCNF)

A relation is in BCNF, if and only if, every determinant is a candidate key.
The difference between 3NF and BCNF is that for a functional dependency A à B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key, whereas BCNF insists that for this dependency to remain in a
relation, A must be a candidate key.


Example of BCNF


fd1   clientNo, interviewDate à interviewTime, staffNo, roomNo    (Primary Key)
fd2   staffNo, interviewDate, interviewTimeà clientNo                (Candidate key)
fd3   roomNo, interviewDate, interviewTime à clientNo, staffNo    (Candidate key)
fd4   staffNo, interviewDate à roomNo                 (not a candidate key)

As a consequece the ClientInterview relation may suffer from update anmalies.
For example, two tuples have to be updated if the roomNo need be changed for staffNo SG5 on the 13-May-02.


Client inerview

 

ClientNo

InterviewDate
InterviewTime
StaffNo
RoomNo
CR76
13-May-02
10.30
SG5
G101
CR76
13-May-02
12.00
SG5
G101
CR74
13-May-02
12.00
SG37
G102
CR56
1-Jul-02
10.30
SG5
G102


Example of BCNF(2)



To transform the ClientInterview relation to BCNF, we must remove the violating functional dependency by creating two new relations called Interview and SatffRoom as shown below,

Interview (clientNo, interviewDate, interviewTime, staffNo)
StaffRoom(staffNo, interviewDate, roomNo)

 
Fourth Normal Form (4NF)

A relation that is in Boyce-Codd normal form and contains no nontrivial multi-valued dependencies.


Multi-valued dependency (MVD)

It represents a dependency between attributes (for example, A, B and C) in a relation, such that for each value of A there is a set of values for B and a set of value for C. However, the set of values for B and C are independent of each other.


A multi-valued dependency can be further defined as being trivial or nontrivial. A MVD A à> B in relation R is defined as being trivial if
• B is a subset of A
or
• A U B = R 
A MVD is defined as being nontrivial if neither of the above two conditions is satisfied.



Fifth Normal Form (5NF)


A relation that has no join dependency.


Lossless-join dependency

A property of decomposition, which ensures that no spurious tuples are generated when relations are reunited through a natural join operation.
 Join dependency

Describes a type of dependency. For example, for a relation R with subsets of the attributes of R denoted as A, B, …, Z, a relation R satisfies a join dependency if, and only if, every legal value of R is equal to the join of its projections on A, B, …, Z.

0 comments:

Post a Comment