Scribd
Upload
6 views16 pages

Understanding Database Normalization

This document provides references and an outline for a lesson on database normalization. It discusses normalization goals such as minimizing duplicate data, modifying data in one place, and accessing data quickly. It also defines first, second, and third normal forms. Functional dependency is explained using an example of a student ID determining other attributes. Set operations and SQL queries are introduced as ways to retrieve data from multiple database tables.

Uploaded by

Dhani Alif Srinata
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
6 views16 pages

Understanding Database Normalization

This document provides references and an outline for a lesson on database normalization. It discusses normalization goals such as minimizing duplicate data, modifying data in one place, and accessing data quickly. It also defines first, second, and third normal forms. Functional dependency is explained using an example of a student ID determining other attributes. Set operations and SQL queries are introduced as ways to retrieve data from multiple database tables.

Uploaded by

Dhani Alif Srinata
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

Lesson 4: Database

Normalization

References
Books:
Hoffer, Jeff. Modern Database Management, 12th Edition. Prentice
Hall, 2016. VitalBook file.
Neeraj Sharma, [Link]. Database Fundamentals. 2014.
[Link] [accessed 02-17-2016]
Tim Wahls. Database System. Lecture Notes.
[Link]

Useful Links:
MySQL: [Link]
MongoDB: [Link]

Outline
Database normalization
De-normalization
Set operation and SQL query
CRUD operation

Well structured relation: construct your


table well!

Well structured relation


"A relation that contains minimal redundancy and allows
users to insert, modify, and delete the rows in a table
without errors or inconsistencies. (Hoffer 164)
How? Normalization

About Normalization
Database normalization, or data normalization, is a technique to organize the
contents of the tables for transactional databases and data warehouses.
Normalization is part of successful database design; without normalization,
database systems can be inaccurate, slow, and inefficient, and they might not
produce the data you expect.
When you normalize a database, you have four goals: arranging data into
logical groupings such that each group describes a small part of the whole;
minimizing the amount of duplicate data stored in a database; organizing the
data such that, when you modify it, you make the change in only one place; and
building a database in which you can access and manipulate the data quickly
and efficiently without compromising the integrity of the data in storage.
Michelle A. Poolet ([Link]

Normalization to obtain Normal


Form
1NF / First Normal Form
2NF / Second Normal Form
3NF / Third Normal Form
BCNF / Boyce-Codd Normal Form

Understanding functional
dependency
Student ID

Name

Gender

Address

DOB

Student ID Name, Gender, Address,


DOB
Name, Gender, Address, DOB
are functionally dependent to
Student ID.
Student ID uniquely
determine Name, Gender,
Address, DOB.

Other example:
ZIP Code City,
Province,

First Normal Form


Solve multivalued attribute(s)
Determine the primary key of the relation

Second Normal Form


First Normal Form
No partial functional dependency

Third Normal Form


Second Normal Form
No transitive dependency

Database with so many tables. Need a way to retrieve data from multiple tables.
Question: HOW?

Set Operation and SQL Query: So


what?
1

3
2

5
6

AB=?
AB=?
Ac = ?
A-B=?

Lets take a look at a simple case


Display all reviews for product no 03!

No
01
02
03

Product
Kantong ajaib
Underwater camera
Semarang T-shirt

Prod_
No
02
01
01

User_I Review
D
4963
Recommended
gan..!
2712
Pengiriman cepat,
ok.
3221
Lebih bagus dari yg
di

Lets take a look at another simple


case
Display all products (both in warehouse A and B)!
Products in
Warehouse
A
No
Product

Products in
Warehouse
B
No
Product

08CA
09AB
33DE

11AC

Air conditioner

08CA
63DE

Orange mango
Blank paper

Orange mango
Smart Phone 99
Golf stick model x

How to perform such operation in


SQL?
UNION
INTERSECT (supported in MySQL?)
EXCEPT (supported in MySQL?)
INNER JOIN
OUTER JOIN (supported in MySQL?)
LEFT JOIN
RIGHT JOIN

You might also like