Ex No: 10(b) NORMALIZATION
NORMALIZATION
Normalization of data is a process of analyzing the given relation schemas based on their FDs and primary keys to achieve the desirable properties of
minimizing redundancy
minimizing the insertion, deletion, and update anomalies
Database normalization is a design technique by which relational database tables are structured in such a way as to make them less vulnerable to certain types of logical inconsistencies and anomalies.
FIRST NORMAL FORM or 1NF
1NF states that the domain of the attribute must include only atomic values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.
A relation which has multi valued composite attribute will not be in 1NF. It does not allow nested relation.
The criteria for first normal form (1NF) are:
· A table must be guaranteed not to have any duplicate records; therefore it must have at least one candidate key.
· There must be no repeating groups, i.e. no attributes which occur a different number of times on different records.
SECOND NORMAL FORM or 2NF
2NF is based on the concept of full functional dependency.
Full functional dependency
A functional dependency XàY is a full functional dependency if removal of any attribute A from X means that the dependency does not hold anymore.
Partial functional dependency
A functional dependency XàY is a partial functional dependency if removal of any attribute A from X means that the dependency still holds.
General Definition:
A relation schema R is in second normal form if every nonprime attribute A in R is not partially dependent on any key of R.
Strict Definition:
A relation schema R is in second normal form if every nonprime attribute A in R is fully functionally dependent on the primary key of R.
The criteria for second normal form (2NF) are:
· The table must be in 1NF.
· None of the non-prime attributes of the table are functionally dependent on a part (proper subset) of a candidate key; in other words, all functional dependencies of non-prime attributes on candidate keys are full functional dependencies.
Note that if none of a 1NF table's candidate keys are composite – i.e. every candidate key consists of just one attribute – then we can say immediately that the table is in 2NF
THIRD NORMAL FORM or 3NF
3NF is based on the concept of transitive dependency.
Transitive Dependency – A functional dependency XàY in a relation schema R is a transitive dependency if there is a set of attributes Z that is neither a candidate key nor a subset of any key of R, and both XàZ and ZàY hold.
General Definition:
A relation schema R is in 3NF if, whenever a nontrivial functional dependency XàA holds in R, either
(a) X is a superkey of R
(b) A is a prime attribute of R
Strict Definition:
A relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key.
The criteria for third normal form (3NF) are:
· The table must be in 2NF.
· There are no non-trivial functional dependencies between non-prime attributes. A violation of 3NF would mean that at least one non-prime attribute is only indirectly dependent (transitively dependent) on a candidate key, by virtue of being functionally dependent on another non-prime attribute.
BOYCE- CODD NORMAL FORM or BCNF
A relation schema R is in BCNF if whenever a nontrivial functional dependency XàA holds in R, then X is a superkey of R.
The only difference between the definitions of BCNF and 3NF is that condition (b) of 3NF, which allows A to be prime, is absent from BCNF.
1NF:
LOT
PropertyID Country_name Lot Area Price Tax rate
2NF:
LOT1
PropertyID Country_name Lot Area Price
LOT2
Country_name Taxrate
3NF:
Country_name Taxrate
LOT1A
Area Price
LOT1B
PropertyID Country_name Lot Area
BCNF
LOT1AX
PropertyID Lot Area
LOT1AY
Area countryname
FIRST NORMAL FORM:
SQL> create table fnf(pid number(5),cname varchar(15),lot number(3),area varchar(10),price number(5),taxrate number(5));
Table created.
SQL> select * from fnf;
PID CNAME LOT AREA PRICE TAXRATE
-------------- ---------- ---------- ----------
123 india 90 ch 4500 20
456 us 75 cali 8000 10
789 nl 45 ambs 7000 15
100 us 25 ny 9000 10
200 india 75 ch 4500 20
300 india 55 mumb 6000 20
6 rows selected.
SECOND NORMAL FORM:
SQL> create table ct(cname varchar(15),taxrate number(5));
Table created.
SQL> create table tnf(pid number(5),cname varchar(15),lot number(3),area varchar(10),price number(5));
Table created.
SQL> select * from ct;
CNAME TAXRATE
--------------- ----------
india 20
us 10
nl 15
SQL> select * from tnf;
PID CNAME LOT AREA PRICE
---------- ---------- ----------
123 india 90 ch 4500
456 us 75 cali 8000
789 nl 45 ambs 7000
100 us 25 ny 9000
200 india 75 ch 4500
300 india 55 mumb 6000
6 rows selected.
SQL> select pid,tnf.cname,lot,area,price,taxrate from tnf,ct where tnf.cname=ct.cname;
PID CNAME LOT AREA PRICE TAXRATE
-------- --------------- ---------- ----------
123 india 90 ch 4500 20
200 india 75 ch 4500 20
300 india 55 mumb 6000 20
789 nl 45 ambs 7000 15
456 us 75 cali 8000 10
100 us 25 ny 9000 10
6 rows selected.
THIRD NORMAL FORM:
SQL> create table ap(area varchar(10),price number(5));
Table created.
SQL> create table thnf(pid number(5),cname varchar(15),lot number(3),area varchar(10));
Table created.
SQL> select * from ap;
AREA PRICE
---------- ----------
ch 4500
cali 8000
ambs 7000
ny 9000
mumb 6000
SQL> select * from thnf;
PID CNAME LOT AREA
---------- --------------- ---------- ----------
123 india 90 ch
456 us 75 cali
789 nl 45 ambs
100 us 25 ny
200 india 75 ch
300 india 55 mumb
6 rows selected.
SQL> select pid,thnf.cname,lot,thnf.area,price,taxrate from thnf,ap,ct where thnf.cname=ct.cname and thnf.area=ap.area;
PID CNAME LOT AREA PRICE TAXRATE
---------- ------------ ---------- ----------
789 nl 45 ambs 7000 15
456 us 75 cali 8000 10
123 india 90 ch 4500 20
200 india 75 ch 4500 20
300 india 55 mumb 6000 20
100 us 25 ny 9000 10
6 rows selected.
0 comments:
Post a Comment