Transcript Normalization - PostgreSQL wiki

Normalization:
A workshop for everybody!
Database Normalization:
A workshop for everybody!
Part 2
Contents copyright Brent Friedman, 2013. Released under LGPL version 3.
Normalization:
A workshop for everybody!
Welcome to PostgresOpen 2013
Chicago
Sept. 16-18, 2013
Normalization:
A workshop for everybody!
Brent Friedman
CTO for a small data-mining start-up
Doctoral candidate (DPA) – Valdosta State Univ.
Open source contributor/advocate since the 1980s
Normalization:
A workshop for everybody!
Some questions:
1. What is a database?
2. Why do we need to organize data?
3. Can't Excel do everything I need?
Normalization:
A workshop for everybody!
A mnemonic for the normal forms:
The truth,
The whole truth,
And nothing but the truth,
So help me, Codd.
Normalization:
A workshop for everybody!
Normalization – a definition
A technique for designing relational databases to
minimize duplication of data, and to enforce logical
separation of data points into related groups (tables)
Normalization helps to organize data efficiently, and to
make databases easier to maintain.
Normalization:
A workshop for everybody!
Each step in the normalization process builds on the
previous step. This means that you must have your
data in first normal form before it can be organized in
second normal form, and so on.
Normalization:
A workshop for everybody!
First Normal Form
1. Eliminate duplicate columns from tables.
2. Create separate tables for each group of related
data.
3. Identify each row (tuple) with a unique identifier
(primary key).
Normalization:
A workshop for everybody!
Second Normal Form
1. Place subsets of data across multiple rows into
separate tables.
2. Link the new tables with existing data via foreign
keys.
Normalization:
A workshop for everybody!
Third Normal Form
1. Remove columns that are not fully dependent upon
the table's primary key for their definition.
Normalization:
A workshop for everybody!
Denormalization
Denormalization is the opposite of normalization – to
put redundant data into a table structure. The primary
uses for denormalization are for speed enhancement
(to avoid multiple JOIN/UNION operations), or for ease
of use in areas like data warehousing.
You should ALWAYS design from a normalization
perspective, and only consider denormalization if the
task at hand requires it.
Normalization:
A workshop for everybody!
Normal Form – live walk-through/demonstration
Normalization:
A workshop for everybody!
End of Part Two