Database Normalization
#1

[attachment=11936]
Database Normalization
About Me

• Member of the MySQL AB documentation team
• MySQL Core and Pro Certified
• Top MySQL expert at experts-exchange.com
• Resident MySQL expert at SearchDatabase.com
• http://openwinmike/aboutme.php
About You
• Currently use MySQL?
• Another RDBMS?
• Are responsible for database design?
• Will be in the future?
• Know about database normalization?
About This Session
• http://openwinmike/presentations/
http://dev.mysqltech-resources/articles/intro-to-normalization.html
• Introduction
• What Is Database Normalization?
• What are the Benefits of Database Normalization?
• What are the Normal Forms?
• First Normal Form
• Second Normal Form
• Forming Relationships
• Third Normal Form
• Joining Tables
• De-Normalization
• Conclusion
What Is Database Normalization?
• Cures the ‘SpreadSheet Syndrome’
• Store only the minimal amount of information.
• Remove redundancies.
• Restructure data.
What are the Benefits of Database Normalization?
• Decreased storage requirements!
1 VARCHAR(20)
converted to 1 TINYINT UNSIGNED
in a table of 1 million rows
is a savings of ~20 MB
• Faster search performance!
– Smaller file for table scans.
– More directed searching.
• Improved data integrity!
What are the Normal Forms?
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
Our Table
First Normal Form

• Remove horizontal redundancies
– No two columns hold the same information
– No single column holds more than a single item
• Each row must be unique
– Use a primary key
• Benefits
– Easier to query/sort the data
– More scalable
– Each row can be identified for updating
One Solution
Satisfying 1NF
Forming Relationships

• Three Forms
– One to (zero or) One
– One to (zero or) Many
– Many to Many
• One to One
– Same Table?
• One to Many
– Place PK of the One in the Many
• Many to Many
– Create a joining table
Joining Tables
Our User Table
Second Normal Form

• Table must be in First Normal Form
• Remove vertical redundancy
– The same value should not repeat across rows
• Composite keys
– All columns in a row must refer to BOTH parts of the key
• Benefits
– Increased storage efficiency
– Less data repetition
Satisfying 2NF
Third Normal Form

• Table must be in Second Normal Form
– If your table is 2NF, there is a good chance it is 3NF
• All columns must relate directly to the primary key
• Benefits
– No extraneous data
Satisfying 3NF
Finding Balance
Joining Tables

• Two Basic Joins
– Equi-Join
– Outer Join (LEFT JOIN)
• Equi-Join
– SELECT user.first_name, user.last_name, email.address
FROM user, email
WHERE user.user_id = email.user_id
• LEFT JOIN
– SELECT user.first_name, user.last_name, email.address
FROM user LEFT JOIN email
ON user.user_id = email.user_id
De-Normalizing Tables
• Use with caution
• Normalize first, then de-normalize
• Use only when you cannot optimize
• Try temp tables, UNIONs, VIEWs, subselects first
Conclusion
http://dev.mysqltech-resources/articles/intro-to-normalization.html
• MySQL Database Design and Optimization
– Jon Stephens & Chad Russell
– Chapter 3
– ISBN 1-59059-332-4
– http://openwinmike/books
• http://openwinmike/presentations
Reply

Important Note..!

If you are not satisfied with above reply ,..Please

ASK HERE

So that we will collect data for you and will made reply to the request....OR try below "QUICK REPLY" box to add a reply to this page
Popular Searches: matlab code for normalization of fingerprint, normalization for online mobile shopping, normalization on project management system, the benefits of normalization ppt, seminar on normalization, normalization in database with example download, types of normalization in database with example ppt,

[-]
Quick Reply
Message
Type your reply to this message here.

Image Verification
Please enter the text contained within the image into the text box below it. This process is used to prevent automated spam bots.
Image Verification
(case insensitive)

Possibly Related Threads...
Thread Author Replies Views Last Post
  distributed database full report project report tiger 3 5,221 05-09-2012, 04:04 PM
Last Post: acceriott
  Relational Database Design seminar class 0 984 03-03-2011, 03:26 PM
Last Post: seminar class
  PARALLEL DATABASE SYSTEMS seminar class 0 2,268 19-02-2011, 04:12 PM
Last Post: seminar class
  Database Recovery seminar class 0 1,008 17-02-2011, 02:55 PM
Last Post: seminar class
  Parallel OLAP for Relational Database Environments seminar projects crazy 2 1,915 03-02-2011, 06:16 PM
Last Post: keerthysmile
  Fundamentals of Database Design seminar surveyer 0 1,007 16-10-2010, 05:03 PM
Last Post: seminar surveyer
  Distributed Database Architecture for Global Roaming in Next –Generation Mobile Netw seminar surveyer 0 1,303 01-10-2010, 11:07 AM
Last Post: seminar surveyer
  database of online recuritment system sheetal2912 1 1,297 14-05-2010, 11:44 PM
Last Post: Sidewinder
  Using a Hash-Based Method with Transaction Trimming and Database Scan Reduction seminar topics 0 1,002 24-03-2010, 07:21 PM
Last Post: seminar topics
  Efficient storage and querying of sequential patterns in database systems seminar topics 0 743 24-03-2010, 07:15 PM
Last Post: seminar topics

Forum Jump: