Database normalization. Module 9

1.

Database
normalization
• Module 9

2.

Agenda
• What is normalization?
• First normal form
• Second normal form
• Third normal form
• Denormalization
2

3.

What is normalization?
The process of organizing the columns and
tables of a relational database to minimize
data redundancy
“Abnormal” DB
(unnormalized form ,
UNF)
normalization
“Normal” DB
(normalized form, NF)
3

4.

Data redundancy
The existence of data that is an additional
copy of the actual data or a small part of
them. Data redundancy leads to the
modification anomalies.
ID
FIRSTNAME
1 Яськів
2 Мельник
3 Коваленко
4 Олійник
LASTNAME
Антон
Ольга
Тарас
Василь
POSITION
Boss
Manager
Manager
Manager
DATE_EMPLOYMENT ID_DEPARTMENT
10/11/1998
03/09/2011
04/15/2008
11/25/2010
ID_BOSS
1
1
2
3
RATE BONUS
8600
1
5400
1
4800
1
6000
NAME
Sun
Sun
Moon
Saturn
CITY
Lviv
Lviv
Kyiv
Lviv
4

5.

Modification anomalies
• Insert anomalies
• Update anomalies
• Delete anomalies
5

6.

Goals of the normalization
• Free the DB of modification anomalies
• Minimize redesign when extending the DB
structure
• Make the DB more informative
• Make the DB suitable for querying
6

7.

Normal forms
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
• Other normal forms
Normal form of a DB is a set of rules which
the DB has to meet for decreasing the data
redundancy.
7

8.

First normal form (1NF)
• No duplicate rows – row uniqueness
• No repeating groups of columns – column
uniqueness
• Every row-and-column intersection
contains exactly one value from the
applicable domain – data atomicity
8

9.

Row uniqueness
• Duplicates of rows should be eliminated
• In case when we really need the same
values in different rows, we can use an
additional field and nominate it as a
primary key
ID_EMPLOYEE VISIT_DATE
ROOM_NAME
1
10/20/2015
215
2
10/21/2015
309
1
10/21/2015
302
2
10/21/2015
309
ID
ID_EMPLOYEE
1
2
3
4
1
2
1
2
VISIT_DATE
ROOM_NAME
10/20/2015
215
10/21/2015
309
10/21/2015
302
10/21/2015
309
9

10.

Column uniqueness
• Repeating groups of columns should be
eliminated
ID_EMPLOYEE VISIT_DATE ROOM_NAME1 ROOM_NAME2 ROOM_NAME3
1 10/20/2015
215
2 10/21/2015
309
507
215
1 10/21/2015
302
ID
ID_EMPLOYEE
1
2
3
5
6
VISIT_DATE
ROOM_NAME
1
10/20/2015
215
2
10/21/2015
309
1
10/21/2015
302
2
10/21/2015
507
2
10/21/2015
215
10

11.

Data atomicity
• There is a single value in each field of each
row.
• This value is from selected domain
• NULL values could be also allowed
ID_EMPLOYEE VISIT_DATE
ROOM_NAME
1
10/20/2015
215
2
10/21/2015 309, 507, 215
1
10/21/2015
302
ID
ID_EMPLOYEE
1
2
3
5
6
1
2
1
2
2
VISIT_DATE
ROOM_NAME
10/20/2015
215
10/21/2015
309
10/21/2015
302
10/21/2015
507
10/21/2015
215
11

12.

UNF->1NF
• Eliminate duplicative columns from the
same table.
• Create separate tables for each group of
related data and identify each row with a
unique column or set of columns (the
primary key).
12

13.

Functional dependency
• Describes relationship between columns in
a table.
• If A and B are columns of the table R, B is
functionally dependent on A (denoted
A→B), if each value of A in R is associated
with exactly one value of B in R.
ID→LASTNAME
ID→FIRSTNAME
LASTNAME → FIRSTNAME
13

14.

Second normal form (2NF)
• 1NF
• No partial functional dependencies
ID FIRSTNAME LASTNAME POSITION ID_DEPARTMENT ID_BOSS RATE BONUS NAME CITY PROJECT_ID PROJECT_NAME
1 Яськів
Антон
Boss
1
8600
Sun
Lviv
1 Delta
2 Мельник
Ольга
Manager
1
1 5400
Sun
Lviv
1 Delta
3 Коваленко Тарас
Manager
2
1 4800
Moon Kyiv
2 Alpha
3 Коваленко Тарас
Manager
2
1 4800
Moon Kyiv
3 Omega
4 Олійник
Василь
Manager
3
1 6000
Saturn Lviv
1 Delta
ID FIRSTNAME LASTNAME POSITION ID_DEPARTMENT ID_BOSS RATE BONUS NAME CITY
1 Яськів
Антон
Boss
1
8600
Sun
Lviv
2 Мельник
Ольга
Manager
1
1 5400
Sun
Lviv
3 Коваленко Тарас
Manager
2
1 4800
Moon Kyiv
4 Олійник
Василь
Manager
3
1 6000
Saturn Lviv
ID
PROJECT_ID
1
1
2
1
3
2
3
3
4
1
PROJECT_ID PROJECT_NAME
1 Delta
2 Alpha
3 Omega
14

15.

1NF->2NF
• Identify functional dependencies in the
table.
• If partial dependencies exist on the primary
key remove them by placing them in a new
table along with copy of their determinant.
15

16.

Third normal form (3NF)
• 2NF
• No transitive dependencies, i.e. no non-key
field depends upon another.
ID FIRSTNAME LASTNAME POSITION ID_DEPARTMENT ID_BOSS RATE BONUS NAME CITY
1 Яськів
Антон
Boss
1
8600
Sun
Lviv
2 Мельник
Ольга
Manager
1
1 5400
Sun
Lviv
3 Коваленко Тарас
Manager
2
1 4800
Moon Kyiv
4 Олійник
Василь
Manager
3
1 6000
Saturn Lviv
ID FIRSTNAME LASTNAME POSITION ID_BOSS RATE BONUS ID_DEPARTMENT
1 Яськів
Антон
Boss
8600
1
2 Мельник Ольга
Manager
1 5400
1
3 Коваленко Тарас
Manager
1 4800
2
4 Олійник
Василь
Manager
1 6000
3
ID_DEPARTMENT NAME CITY
1 Sun
Lviv
2 Moon Kyiv
3 Saturn Lviv
16

17.

2NF->3NF
• Identify functional dependencies in the
table.
• If transitive dependencies exist on the
primary key remove them by placing them
in a new table along with copy of their
determinant.
17

18.

Normalization: question
How does a normalization affect the
productivity of such operations:
• Insert
• Update
• Delete
• Select
?
18

19.

Denormalization
• Denormalized databases fair well under
heavy read-load and when the application
is read intensive
• But because the data is duplicated, the
updates and inserts become complex and
costly
19

20.

More information
• http://databases.about.com/od/specificpro
ducts/a/normalization.htm
• http://holowczak.com/databasenormalization/
• http://www.essentialsql.com/get-ready-tolearn-sql-database-normalizationexplained-in-simple-english/
• http://www.studytonight.com/dbms/datab
ase-normalization.php
20

21.

Thank you!
US OFFICES
EUROPE OFFICES
Austin, TX
Fort Myers, FL
Lehi, UT
Newport Beach, CA
Waltham, MA
Bulgaria
Germany
Netherlands
Poland
Russia
Sweden
Ukraine
United Kingdom
www.softserveinc.com