Normalisation of something i dont know and will never know

presentation

1. 2
nd
Normal Form
A relation schema is in 2NF if it
satisfies 1NF and every non key
attribute is fully functionally
dependent on the primary key of
Relation.

2. Example of 2NF
EMP_PROJ is 1NF with Key SSN, PNUMBER but…

SSN  ENAME - ENAME, a Non-Prime Attribute, Depends Partially
on SSN, PNUMBER, i.e., Depends on Only SSN and not Both

PNUMBER  {PNAME, PLOCATION} - PNAME, PLOCATION, two
Non-Prime Attributes, Depends Partially on SSN, Pno i.e., Depends
on Only PNo &not Both

3. Another Example of 2NF

4. Third Normal Form (3NF)
It is based on the concept of
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.( Book Def)

5. EMP_DEPT
ENAME SSNBDATEADDRESS DNUMBER DNAME DMGRSS
N

6. Here, SSNDNAME
Since SSNDNUMBER and
DNUMBERDNAME holds.

7. A relation schema R is said to be in 3NF if it
satisfies 2NF and no nonprime attribute of R is
transitively dependent on the Primary key. To
normalize from 2NF to 3NF, decompose and
set up a relation that includes the non-prime
attribute that functionally determine other
non-prime attribute and remove this attribute
from the original relation.
Relation Schema EMP_DEPT is not in 3NF but it
is in 2NF as no partial dependency on a key
exists.

8. To normalize EMP_DEPT into 3NF we
decompose it into two 3NF relation
schemas as shown:

9. ENAME SSNBDATEADDRESS DNUMBER DNAME DMGRSS
N
ENAME SSNBDAT
E
ADDRESS DNUMBE
R
DNUMBE
R
DNAME DMGRSS
N
ED1
ED2

10. Consider a LOTS relation schema that
describes plots for sale in various states of
countries .
It has two candidate keys PROPERTY_ID#
and {COUNTRY_NAME,LOT#}.
Also we assume that tax rate is determined
by country name. Also price of a lot is
determined by it’s area.

11. PROPERTY
_ID#
COUNTRY_N
AME
LOT#AREAPRICE TAX_RATE
Determine all FDs.
And Normalise the
relation schema to
3
rd
Normal Form.

12. PROPERTY
_ID#
COUNTRY_N
AME
LOT#AREAPRICE TAX_RATE
FD1
FD2
FD3
FD4

13. Decomposition into 2NF
LOTS1
PROPERTY
_ID#
COUNTRY_N
AME
LOT#AREAPRICE
LOTS2
COUNTRY_NAM
E
TAX_RATE

14. Normalizing LOTS1 into 3NF
(lots 2 will remain as it is)
PROPERTY
_ID#
COUNTRY_NAME LOT#AREA
LOTS1A
LOTS1B
AREA PRICE

15. Boyce-Codd Normal Form
A relation is in BCNF if and only if every
nontrivial, left-irreducible FD has a
candidate key as it’s determinant. Hence,
for every nontrivial, left-irreducible FD
XA, X should be a candidate key. The
difference between 3NF and BCNF is that
3NF allows A to be a prime attribute.
Every relation that in BCNF is also in 3NF,
however a relation in 3NF is not necessarily
in BCNF.For example,

16. In simple terms

18. Superkey

22. 4 NF
Fourth normal form (4NF) is a level of
database normalization where there
are no non-trivial multivalued
dependencies other than a candidate
key. It builds on the first three normal
forms (1NF, 2NF and 3NF) and the
Boyce-Codd Normal Form (BCNF

23. Properties :
 A relation R is in 4NF if and only if the
following conditions are satisfied:
1.It should be in the Boyce-Codd
Normal Form (BCNF).
2.the table should not have any Multi-
valued Dependency.

24. A table with a multivalued dependency
violates the normalization standard of
Fourth Normal Form (4NK) because it
creates unnecessary redundancies and
can contribute to inconsistent data. To
bring this up to 4NF, it is necessary to
break this information into two tables.

25. example
SID SNAME
S1 A
S2 B
Table – R1(SID, SNAME)
Table – R2(CID, CNAME)

26. CID CNAME
C1 C
C2 D

27. SID SNAME CID CNAME
S1 A C1 C
S1 A C2 D
S2 B C1 C
S2 B C2 D
Multivalued dependencies (MVD) are:
SID->->CID; SID->->CNAME; SNAME->->CNAME

28. Normal Form (5NF):
A relation R is in 5NF if and only if
every join dependency in R is implied
by the candidate keys of R. A relation
decomposed into two relations must
have loss-less join Property, which
ensures that no spurious or extra
tuples are generated, when relations
are reunited through a natural join.

29. Properties – A relation R is in 5NF if
and only if it satisfies following
conditions:
1.R should be already in 4NF.
2.It cannot be further non loss
decomposed (join dependency)

30. Agent Company Product
A1 PQR Nut
A1 PQR Bolt
A1 XYZ Nut
Table – ACP

31. The relation ACP is again decompose into 3 relations. Now, the natural
Join of all the three relations will be shown as:
Agent Company
A1 PQR
A1 XYZ
A2 PQR
Table – R1

32. Agent Product
A1 Nut
A1 Bolt
A2 Nut
able – R2

33. Table R3
Company Product
PQR Nut
PQR Bolt
XYZ Nut
XYZ Bolt

34. Result of Natural Join of R1 and R3 over
‘Company’ and then Natural Join of R13 and
R2 over ‘Agent’and ‘Product’ will be
table ACP.
Hence, in this example, all the redundancies
are eliminated, and the decomposition of
ACP is a lossless join decomposition.
Therefore, the relation is in 5NF as it does
not violate the property of lossless join.