Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Database Normalization: I'm Not Weird and Neither is My Database!

(Updated June 9, 2014) - This Prezi discusses database normalization, redundancy, anomalies and the first three norm forms including an example set.
by

Misty Kitzul

on 29 June 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Database Normalization: I'm Not Weird and Neither is My Database!

Anomalies?

I didn't know I had any
critters in my database!

What kinds are there
and what damage can they do?

You may not be normal, but here's why your database should be normalized:
Get rid of redundancies
- repetition breeds data errors and makes your database bigger
Squash anomalies
- get rid of insertion, update, and deletion anomalies that can cause errors
Database Normalization:
I'm Not Weird and Neither
is My Database!
Insertion Anomaly

This is when you can't
enter data because some
other data is missing.
(Ex. - Hey, I can't add your address to the
system until you create an account.)
(Ex. - Company that has two different spellings
of your name because there are multiple tables
with copies of your data.)
Update Anomaly

An instance where the
same information must
be updated in several
different places
Deletion Anomaly

Where deleting one piece
of data inadvertently
causes other data
to be lost
(Ex. - You mean I can't delete the patient
records but still keep their emails?
So What Exactly is a Norm Form?

A normal form, or "norm form" is a set of rules designed to reduce or eliminate various anomalies.

The main norm forms we need to worry about are the first three.

An entity is in First Normal Form if:
Every attribute represents only 1 value
There are no repeating groups or arrays
Each row is unique
(1NF)
First Norm Form

Removes all repeating
groups or arrays
(Yes, There ARE more than 3 norm forms.)
Example
On to
the 2NL -->
An entity is in Second Normal Form if:
Each field is "functionally dependent"
on the ENTIRE KEY (not partial key)
Each entity is only about 1 thing
It fulfills 1NF
(2NF)
Second Norm Form

1NF AND all members are
dependent on the key,
not each other
Example
On to
the 3NL -->
An entity is in Third Normal Form if:
There are no transient dependencies (where 1
attribute depends on another attribute for
its meaning and not the key)
It fulfills 2NF
(3NF)
Third Norm Form

2NF AND removes "transient dependencies"
Example
2NF: Each of the fields that isn't a part of the key
is functionally dependent on the entire key.
CHECK IT OUT: If a single-field key is
used, a 1NF table is ALREADY in 2NF
3NF: Transient dependencies need to go find their own homes in other tables or entities where they belong.
Created by Misty Melody Kitzul
The Million Dollar 3NF Question:
Are any of the non-key columns dependent
on any other non-key columns?
Yeah!
We're #3!
(Now what do we do?)
Functional Dependencies: Groups of
related attributes that form
subthemes within an entity.
Re: They're like subplots
that detract from the
main plot of a movie.
First Norm Form Example
Second Norm Form Example
Third Norm Form Example
Un-normalized Data:
First Norm Form (1NF):
Happier Data in 1NF,
But there's more to do -->
BAD DATA!
(No doughnut!)
Attributes should
only represent
1 value! -->
BAD DATA!
<-- No repeating lists or arrays allowed!
Un-normalized Data:
Second Norm Form (2NF):
LOOK OUT! -->
There's two themes in this entity - tutor sessions and instructors. Each entity needs to be about one topic.
Breaking each
sub-theme into its own entity gets rid of functional dependencies
But wait, there's more! -->
Congratulations! You're Normal!
Un-normalized Data:
Third Norm Form (3NF):
LOOK OUT! -->
("Thar be dragons, arrh!")
Matey, there's
data here that
does not depend on
the key for its meaning!
Removing non-key dependent data gets rid of transient dependencies
Repetition and anomalies can cause database errors which must be squashed by the normalization process!
Insertion anomalies - Can't insert data before other data
Update anomalies - Duplicated data in multiple spots
Deletion anomalies - Deleting one item deletes another

The first three norm forms help to remove potential errors.
1NF: Remove repeating lists and arrays
2NF: Remove functional dependencies - get the sub-themes to their own table - "One Table, One Theme"
3NF: Remove transient dependencies - anything that does not depend on the key needs to find its own home in another entity/table
REVIEW TIME:
Full transcript