Designing the Database
1
2019-2020 Hou Keanan
Content
➢ Database Normalization
➢ Data Redundancy
2
2019-2020 Hou Keanan
Database Normalization
Database Normalization:
➢ ជាតំណ ើ រការនៃការណរៀបចំរចនាសម្ពៃ
័ ធណៅណ ើ relational database
➢ កាត់បៃថយភាពណ ើ សនៃទៃ
ិ នៃយ
័ (data redundancy)
➢ ណ្វណើ ោយប្បណសើណ ង
ើ ណៅdata integrity
➢ ណរៀបចំcolumns(attributes) and tables(relations)ណៅក្នុងdatabase
3
2019-2020 Hou Keanan
Characteristics of Normalization
The three characteristics are:
➢ ោចណោយណយង
ើ ណ្វកា
ើ រផ្លាសបតូរdata model(future data model change)
➢ ផ្ទុក្ភាពណ ើ សនៃទៃ
ិ នៃយ
័ តច
ិ បំផ្ុត
➢ ការពារភាពម្ៃ
ិ ប្បប្ក្តតព
ី កា
ី រinsertion, deletion ៃង
ិ update
4
2019-2020 Hou Keanan
Characteristics of Normalization
=>Normalization: គជា
ឺ technique ម្ួយក្នុងការណរៀបចំរចនាសម្ពៃ
័ ធនៃdatabase
ណោយណ្វយ
ើ ៉ា ងណាណោយមាៃភាពណ ើ សនៃទៃ
ិ នៃយ
័ (Data Redundancy)តច
ិ តួច
បំផ្ុត ៃង
ិ ការពារទៃ
ិ នៃយ
័ ពភា
ី ពម្ៃ
ិ ប្បប្ក្តតី ដែ ភាពម្ៃ
ិ ប្បប្ក្តតណី នាោះមាៃែូចជា៖
First Normal Form, Second Normal Form, Third Normal Form, Fourth
Normal Form, Boyce-Codd Normal Form,…
5
2019-2020 Hou Keanan
Data Redundancy
Data Redundancy
➢ Same piece of data is stored in two or more separate places
➢ វាោចណក្ត
ើ ណ ង
ើ ណោយនចែៃយ ឬក្៏ណោយណចតនា
➢ ប្តូវមាៃfieldក្ណា
ត (central field)ឬspace សប្មាប់data
➢ Master Data ៃង
ិ Database normalization
6
2019-2020 Hou Keanan
Data Redundancy
7
2019-2020 Hou Keanan
Data Redundancy
8
2019-2020 Hou Keanan
Data Redundancy
9
2019-2020 Hou Keanan
Why Normalization
➢ ុ បបំបាត់ភាពណ ើ សនៃទៃ
ិ នៃយ
័ (data redundancy)
➢ ណែម្
ើ បប្ី បាក្ែថាdata dependency មាៃទំនាក្់ទំៃងគ្ននប្តម្
ឹ ប្តូវ
=> ណបគ្ន
ើ ា ៃNormalizationណទ ណ យ
ើ ដបជាមាៃភាពណ ើ សនៃទៃ
ិ នៃយ
័ វញណនាោះ
ិ វាម្ៃ
ិ
ប្តម្
ឹ ដតណ្វណើ ោយណយង
ើ ខាត់memory space ណនាោះណទ វាដែម្ទំងណ្វើណោយណយង
ើ ពបា
ិ ក្
ក្នុងការណោោះប្ាយ(handle) update database ៃង
ិ ប្បឈម្ៃង
ិ ការបាតទិៃនៃយ
័ ដែម្
ណទៀតផ្ង។
10
2019-2020 Hou Keanan
Why Normalization?
[Link]
11
2019-2020 Hou Keanan
First Normal Form
First Normal Form
➢ Field ៃម្
ិ យួ ៗោចរក្ាទុក្តនម្ា(value)បាៃដតម្ួយគត់
➢ All Attribute values ទំងអស់ប្តូវដតមាៃតនម្ាតូចបំផ្ុត(atomic)
12
2019-2020 Hou Keanan
Second Normal Form(2NF)
Second Normal Form
➢ The table should be in the First Normal Form
➢ There should be no Partial Dependency.
13
2019-2020 Hou Keanan
What is Dependency?
Let's take an example of a Student table
[Link]
14
2019-2020 Hou Keanan
What is Partial Dependency?
[Link]
15
2019-2020 Hou Keanan
How to remove Partial Dependency?
Remove teacher’s name from Score Table
Add teacher’s name to Subject table
16
2019-2020 Hou Keanan
How to remove Partial Dependency?
Score table is now in the second normal form, with no partial dependency.
17
2019-2020 Hou Keanan
Third Normalization Form(3NF)
A table is said to be in the Third Normal Form when,
➢ It is in the Second Normal form.
➢ And, it doesn't have Transitive Dependency.
18
2019-2020 Hou Keanan
What is Transitive Dependency?
Transitive Dependency. When a non-prime attribute depends
on other non-prime attributes rather than depending upon the
prime attributes or primary key.
[Link]
19
2019-2020 Hou Keanan
How to remove Transitive Dependency?
Take out the columns exam_name and total_marks from Score table
and put them in an Exam table and use the exam_id wherever
required.
[Link]
20
2019-2020 Hou Keanan
How to remove Transitive Dependency?
Score Table: In 3rd Normal Form
[Link]
21
2019-2020 Hou Keanan
First Normal Form
22
2019-2020 Hou Keanan
Summarize
➢ Normalization
➢ Characteristics of Database Normalization
➢ Data Redundancy
➢ First Normal Form
23
2019-2020 Hou Keanan
Thank you
24