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

1Jul00
1Sep02

31Aug01
1Sep02

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

1Sep99
10Oct00
1Nov02

10Jun00
1Dec01
1Aug03

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

1Jul00

31Aug01

350

CO40

Tina Murphy

CR76

PG16

John
Kay

5 Novar Dr,
Glasgow

1Sep02

1Sep02

450

CO93

Tony Shaw

CR56

PG4

Aline
Stewart

6 lawrence
St,Glasgow

1Sep99

10Jun00

350

CO40

Tina Murphy

CR56

PG36

Aline
Stewart

2 Manor Rd,
Glasgow

10Oct00

1Dec01

370

CO93

Tony Shaw

CR56

PG16

Aline
Stewart

5 Novar Dr,
Glasgow

1Nov02

1Aug03

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

1Jul00

31Aug01

350

CO40

Tina Murphy

CR76

PG16

5 Novar Dr,
Glasgow

1Sep02

1Sep02

450

CO93

Tony Shaw

CR56

PG4

6 lawrence
St,Glasgow

1Sep99

10Jun00

350

CO40

Tina Murphy

CR56

PG36

2 Manor Rd,
Glasgow

10Oct00

1Dec01

370

CO93

Tony Shaw

CR56

PG16

5 Novar Dr,
Glasgow

1Nov02

1Aug03

450

CO93

Tony Shaw

Second Normal Form (2NF)
Second normal form (2NF) is a relation that is in first normal form and every nonprimarykey 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  PropertyNo  renStart  renFinish 
CR76  PG4  1Jul00  31Aug01 
CR76  PG16  1Sep02  1Sep02 
CR56  PG4  1Sep99  10Jun00 
CR56  PG36  10Oct00  1Dec01 
CR56  PG16  1Nov02  1Aug03 
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 nonprimarykey 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  PropertyNo  renStart  renFinish 
CR76  PG4  1Jul00  31Aug01 
CR76  PG16  1Sep02  1Sep02 
CR56  PG4  1Sep99  10Jun00 
CR56  PG36  10Oct00  1Dec01 
CR56  PG16  1Nov02  1Aug03 
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

BoyceCodd 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 primarykey 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 13May02.
Client inerview
ClientNo 
InterviewDate

InterviewTime

StaffNo

RoomNo

CR76

13May02

10.30

SG5

G101

CR76

13May02

12.00

SG5

G101

CR74

13May02

12.00

SG37

G102

CR56

1Jul02

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 BoyceCodd normal form and contains no nontrivial multivalued dependencies.
Multivalued 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 multivalued 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.
Losslessjoin 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