Transcript lecture040505

DATABASE PROGRAMMING
Lecture on 04 – 05 – 2005
Daniel Adinugroho
Database Programming
1
PREVIOUS LECTURE
1. Introduction to Database Design
- 6 Steps Database Design
2. Normalization.
- 1NF, 2NF and 3NF and example of normalization.
- BCNF, 4NF and 5NF
Daniel Adinugroho
Database Programming
2
NEWS
1. All lecture materials are available on
http://192.168.1.1/teachers/adinugro/dp
2. Next Lecture will be QUIZ (06/05/2005)
- About Normalization
- OPEN BOOK
- DO NOT CHEAT!!!!!!!!!! or You will get 0.
- Exercise for exam, as the question is a typical exam questions,
- OK?
Daniel Adinugroho
Database Programming
3
REVIEW OF NORMAL FORMS
1NF -> eliminate repeating attributes
1. Identify repeating attributes.
2. All the key attributes are defined.
3. All attributes are dependent on the primary key.
2NF
1. It's in first normal form (1NF)
2. It includes no partial dependencies (where an attribute is
dependent on only a part of a primary key).
3NF
1. It's in second normal form (2NF)
2. It contains no transitive dependencies (where a non-key attribute is dependent on
another non-key attribute
Daniel Adinugroho
Database Programming
4
Project
number
1023
1056
Project name
Employee
number
Madagascar travel site
Online estate agency
Daniel Adinugroho
Employee name
Rate
Hourly
category
rate
11
Vincent Radebe
A
$60
12
Pauline James
B
$50
16
Charles Ramoraz
C
$40
11
Vincent Radebe
A
$60
17
Monique Williams
B
$50
Database Programming
5
st
1
Project
NORMAL FORM
Employee
Project name
number
number
Employee name
Rate
Hourly
category
rate
1023
Madagascar travel site
11
Vincent Radebe
A
$60
1023
Madagascar travel site
12
Pauline James
B
$50
1023
Madagascar travel site
16
Charles Ramoraz
C
$40
1056
Online estate agency
11
Vincent Radebe
A
$60
1056
Online estate agency
17
Monique Williams
B
$50
Daniel Adinugroho
Database Programming
6
nd
2
NORMAL FORM
Employee Table
Employee number
Employee name
Rate category
Hourly rate
11
Vincent Radebe
A
$60
12
Pauline James
B
$50
16
Charles Ramoraz
C
$40
11
Vincent Radebe
A
$60
17
Monique Williams
B
$50
Daniel Adinugroho
Database Programming
7
nd
2
NORMAL FORM
Project Table
Project Table
Project
number
Employee
Project name
Project Employee
number number
1023
Madagascar travel site
1023
11
1056
Online estate agency
1023
12
1023
16
1056
11
1056
17
Daniel Adinugroho
Database Programming
8
rd
3
NORMAL FORM
Employee Table
Employee
number
Rate Table
category
Rate
category
Hourly
rate
A
$60
B
$50
C
$40
Rate
Employee name
11
Vincent Radebe
A
12
Pauline James
B
16
Charles Ramoraz
C
11
Vincent Radebe
A
17
Monique Williams
B
Daniel Adinugroho
Database Programming
9
rd
3
NORMAL FORM
Project Table
Project Table
Project
number
Employee
Project name
Project Employee
number number
1023
Madagascar travel site
1023
11
1056
Online estate agency
1023
12
1023
16
1056
11
1056
17
Daniel Adinugroho
Database Programming
10
DENORMALIZATION
Normalization is:
●
Storing one fact in one place
●
Storing related facts about single entity together
●
every column of each entity refers non-transitively to only the unique identifier
for that entity.
Denormalization: is the reversal process of Normalization, which is a process to
store a fact in numerous places.
Daniel Adinugroho
Database Programming
11
DENORMALIZATION(2)
Sometimes we need to do denormalization to accomplish quick retrieval capability
for data stored in relational database.
Of course we should (whenever possible) normalize our design to provide optimum
environment . However, in real world, denormalization is necessary.
Some issues need to be considered before denormalization:
●
can the system achieve acceptable performance without denormalizating?
●
will the performance of the system after denormalizating still unacceptable?
●
will the system be less reliable due to denormalization?
If there is one 'yes' answer, we should avoid denormalization!
Daniel Adinugroho
Database Programming
12
DENORMALIZATION (EXAMPLE)
In order to justify denormalization we need to have a business reason for the alteration form
3rd NF. This example design is for a mail order company that has 120,000,000 customers to
whom they must send catalogs. The top design is in 3rd normal form because the attributes
for State, City, and Country are codependent on the PostalCode field. To fix that we created
the PostalCode entity and related it back to the Customer records on the PostalCode foreign
key in the Customer table.
However, during system testing it was discovered that the time required to produce
120,000,000 mailing labels through a two-table join was much longer than if the labels could
be produced from a single table. The table was then denormalized by reintroducing the City,
State, and Country attributes to the Customer table.
After doing this it would be heavily documented including the normal form that is violated,
the reason for the violation, and the consequences of the violation. Any violation of a fully
normalized design carries with it potential problems in the area of updates.
Daniel Adinugroho
Database Programming
13
REFERENCES
Craig, S. Mullin, Denormalization,
http://www.objectarchitects.de/ObjectArchitects/orpatterns/Performance/Denormaliz
ation/CraigMullinsGuidelines/i001fe02.htm
● Ian Gilfillan, Database Normalization,
http://www.databasejournal.com/sqletc/article.php/1428511
● Mike Hillyer, An Introduction to Database Normalization,
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
● David Faour, Database Normalization,
http://www.serverwatch.com/tutorials/article.php/1549781
●
Daniel Adinugroho
Database Programming
14