Normalization in Database Management System.pptx

Normalization
The biggest problem needed to be solved in database is
data redundancy.

Why data redundancy is the problem? Because it causes:

Insert Anomaly
Update Anomaly


 Delete Anomaly
2
Teacher Subject Teacher Degree Tel
Sok San Database Master's 012666777
Van Sokhen Database Bachelor's 017678678
Sok San E-Commerce Master's 012666777

Normalization (Cont.)
Normalization is the process of removing redundant data
from your tables to improve storage efficiency, data
integrity, and scalability.
Normalization generally involves splitting existing tables


into multiple ones, which must
each time a query is issued.
Why normalization?
be re-joined or linked

The relation derived from the user
likely be unnormalized.
view or data store will most

The problem usually happens when an existing system uses
unstructured file, e.g. in MS Excel.

3

Steps of Normalization
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)






In practice, 1NF, 2NF, and 3NF are enough for database.
4

First Normal Form (1NF)
The official qualifications for 1NF are:
1. Each
2. Each
3. Each
attribute
attribute
row must
name must be unique.
value must be single.
be unique.
4. There is no repeating groups.
Additional:

Choose a primary key.

Reminder:
primary key is unique, not null, unchanged.

A A primary
key can be either an attribute or combined attributes.
5

First Normal Form (1NF) (Cont.)
Example of a table not
in
1NF :

It violates the 1NF because:
Attribute values are not single.
Repeating groups exists.


6
Group Topic Student Score
Group A Intro MongoDB
Group B Intro MySQL
Sok San 18 marks
Sao Ry 17 marks
Chan Tina 19 marks
Tith Sophea 16 marks

First Normal Form (1NF) (Cont.)
After eliminating:

Now it is in 1NF.
However, it might

still violate 2NF and so on.
7
Group Topic Family Name Given Name Score
A Intro MongoDB Sok San 18
A Intro MongoDB Sao Ry 17
B Intro MySQL Chan Tina 19
B Intro MySQL Tith Sophea 16

Functional Dependencies
We say an attribute, B, has a functional dependency on another
attribute, A, if for any two records, which have
the same value for A, then the values for B in these two records
must be the same. We illustrate this as:
A B (read as: A determines B or B depends on A)
employee name email address
8
employee name project email address
Sok San POS Mart Sys soksan@yahoo.com
Sao Ry Univ Mgt Sys sao@yahoo.com
Sok San Web Redesign soksan@yahoo.com
Chan Sokna POS Mart Sys chan@gmail.com
Sao Ry DB Design sao@yahoo.com

Functional Dependencies (cont.)
If EmpNum is the PK
EmpNum
must exist.
then the FDs:
 EmpEmail, EmpFname, EmpLname
9
EmpNum EmpEmail EmpFname EmpLname
123
456
555
jdoe@abc.com John Doe
psmith@abc.com Peter Smith
alee1@abc.com Alan Lee
633 pdoe@abc.com Peter Doe
787 alee2@abc.com Alan Lee

Functional Dependencies (cont.)
EmpNum EmpEmail, EmpFname, EmpLname
3 different ways
you
FDs
might see
depicted
EmpEmail
EmpFname
EmpLname
EmpNum
10
EmpNum EmpEmail EmpFname EmpLname

Determinant
Functional Dependency
EmpNum EmpEmail
Attribute on the left hand side
determinant
is known as the
EmpNum is a determinant of EmpEmail
•
11

Second Normal Form (2NF)
The official qualifications for
2NF
1. A table is already in 1NF.
are
:
2. All nonkey attributes are fully dependent on the
primary
key.
Al
l
partia
l
dependenci
es
ar
e
remove
d
to plac
e
in anothe
r
table
.
12

Example of a table not in 2NF:
Primary Key
The Course Name depends on only CourseID, a part of the primary key
not the whole primary {CourseID, SemesterID}.It’s called partial
dependency.
Solution:
Remove CourseID and Course Name together to create a new table.
13
CourseID SemesterID Num Student Course Name
IT101 201301 25 Database
IT101 201302 25 Database
IT102 201301 30 Web Prog
IT102 201302 35 Web Prog
IT103 201401 20 Networking

Done? Oh no, it is still
not in 1NF yet.
Remove the repeating
groups too.
Finally, connect the
relationship.
14
CourseID SemesterID Num Student
IT101 201301 25
IT101 201302 25
IT102 201301 30
IT102 201302 35
IT103 201401 20
CourseID Course Name
IT101 Database
IT102 Web Prog
IT103 Networking
CourseID Course Name
IT101 Database
IT101 Database
IT102 Web Prog
IT102 Web Prog
IT103 Networking

Third Normal Form (3NF)
The official qualifications for 3NF
are:
2. Nonprimary key attributes do not depend on
other nonprimary key attributes
(i.e. no transitive dependencies)
All transitive
dependencies
ar
e
remove
d
to plac
e
in
anothe
r
table
.
15

Example of a Table not in 3NF:
Primary Key
The Teacher Tel is a nonkey attribute, and
the Teacher Name is also a nonkey atttribute.
But Teacher Tel depends on Teacher Name.
It is called transitive dependency.
Solution:
Remove Teacher Name and
to create a new table.
Teacher Tel together
16
StudyID Course Name Teacher Name Teacher Tel
1 Database Sok Piseth 012 123 456
2 Database Sao Kanha 0977 322 111
3 Web Prog Chan Veasna 012 412 333
4 Web Prog Chan Veasna 012 412 333
5 Networking Pou Sambath 077 545 221

Done?
Oh no, it is still not in 1NF yet.
Remove Repeating row.
Note about primary key:
-
Teacher Name to be a primary key.
-
Teacher ID as the primary key.
StudyID Course Name T.ID
1 Database T1
2 Database T2
3 Web Prog T3
Teacher Name Teacher Tel 4 Web Prog T3
Networking T4
Sok Piseth 012 123 456 5
Sao Kanha 0977 322 111
Chan Veasna 012 412 333
Pou Sambath 077 545 221
In theory, you can choose
But in practice, you should add
ID Teacher Name Teacher Tel
T1 Sok Piseth 012 123 456
T2 Sao Kanha 0977 322 111
T3 Chan Veasna 012 412 333
17 T4 Pou Sambath 077 545 221
Teacher Name Teacher Tel
Sok Piseth 012 123 456
Sao Kanha 0977 322 111
Pou Sambath 077 545 221

Boyce Codd Normal Form (BCNF) – 3.5NF
The official qualifications for BCNF
are:
2. All determinants must be superkeys.
All
determinants
thatar
e
no
t
superke
ys
ar
e
remove
d
to plac
e
in
anothe
r
table
.
18

Boyce Codd Normal Form (BCNF) (Cont.)
Exampl
e
of a tabl
e
not in BCNF
:

Key
:
{Student,
Course}

 Functional
Dependency:
{Student, Course} Teacher
Teacher Course


Problem: Teacher is not a superkey
 but determines Course.
19
Student Course Teacher
Sok DB John
Sao DB William
Chan E-Commerce Todd
Sok E-Commerce Todd
Chan DB William

contains Teacher and Course
20
Student Course Solution: Decouple a table
Sok DB from original table (Student,
Course). Finally, connect the new
and old table to third table
contains Course.
Sao DB
Chan E-Commerce
Sok E-Commerce
Course
Chan DB
DB
E-Commerce
Course Teacher
DB John
DB William
E-Commerce Todd

Forth Normal Form (4NF)
are:
1. A table is already in BCNF.
2. A table contains no multi-valued
dependencies.
Multi-valued dependency: MVDs occur
when
two

or more independent multi valued facts about
the
same attribute occur within the same table.
A B (B multi-valued depends on A)
21

Forth Normal Form (4NF) (Cont.)
Exampl
e
of a tabl
e
not in 4NF
:

Key:
{Student,
Major,
Hobby}

 MVD: Student Major, Hobby
22
Student Major Hobby
Sok IT Football
Sok IT Volleyball
Sao IT Football
Sao Med Football
Chan IT NULL
Puth NULL Football
Tith NULL NULL

23
Student Hobby
Sok Football
Sok Volleyball
Sao Football
Chan NULL
Puth Football
Tith NULL
Chan
Puth
Tith
Solution: Decouple to each
table contains MVD. Finally,
connect each to a third table
Student Major
Sok IT
Sao IT
contains Student.
Student
Sok
Sao
Sao Med
Chan IT
Puth NULL
Tith NULL

Fifth Normal Form (5NF)
are:
2.
The
attribute
s
of multi-
valued
dependenci
es
arerelated
.
24

Fifth Normal Form (5NF) (Cont.)
Exampl
e
of a tabl
e
not in 5NF
:

Key:
{Seller,
MVD:
Seller
Company, Product}
Company, Product


 Product is related to Company.
25
Seller Company Product
Sok MIAF Trading Zenya
Sao Coca-Cola Corp Coke
Sao Coca-Cola Corp Fanta
Sao Coca-Cola Corp Sprite
Chan Angkor Brewery Angkor Beer
Chan Cambodia Brewery Cambodia Beer

1
26
Seller Company Company
1 MIAF Trading
Seller 1 M Sok MIAF Trading
Coca-Cola Corp
Sok Sao Coca-Cola Corp
Angkor Brewery
Sao Chan Angkor Brewery
Cambodia Brewery
Chan Chan Cambodia Brewery
M
1
M Company Product
Seller Product 1
M
MIAF Trading Zenya
Sok Zenya
Coca-Cola Corp Coke
Sao Coke
Product Coca-Cola Corp Fanta
Sao Fanta
Zenya Coca-Cola Corp Sprite
Sao Sprite
Coke Angkor Brewery Angkor Beer
Chan Angkor
Beer
Fanta Cambodia Cambodia
Brewery Beer
Chan Cambodia
Beer
Sprite
Angkor Beer 1
M
Cambodia Beer

Normalization in Database Management System.pptx

More Related Content

Similar to Normalization in Database Management System.pptx

More from Roshni814224

Recently uploaded

Normalization in Database Management System.pptx