Database Normalization

Database Normalization - DatabaseNormalization...

Info iconThis preview shows pages 1–14. Sign up to view the full content.

View Full Document Right Arrow Icon
Click to edit Master subtitle style  11/2/09 Database Normalization OPIM 3103, Fall 2009 Dmitry Zhdanov
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
 11/2/09 Why normalize? Modification anomalies in databases Data dependencies Normalization process Topics for today
Background image of page 2
 11/2/09 Normalization is the process of decomposing  large, inefficiently structured tables into smaller,  more efficient tables, without any data loss on the  way Remember three goals of database design Store all data Avoid redundant storage of data Preserve relationships What is Normalization?
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
 11/2/09 Is it possible to have a database which is not  normalized,  but still works? Yes, sometimes database designers do this for multiple  reasons. However, it is not a good practice Is it possible to do normalization without going  through the design process? Yes, sometimes you work with databases that were  created before your arrival and have data in them. You  can still improve the efficiency of design by normalization Normalization in real world
Background image of page 4
 11/2/09 To ensure solid, efficient table structures.  Improperly designed tables are usually subject to  modification anomalies and data dependency  problems. If you do not address these problems,  your information will be inconsistent, inaccurate  and sometimes totally invalid So, why normalize?
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
 11/2/09 Things that can go wrong when you try to  Insert Delete Update data in your database Modification anomalies
Background image of page 6
 11/2/09 It may be of two types: Unnecessary constraint Usually it is a subtle problem and harder to spot Unreasonable redundancy Usually more obvious Insert anomaly 
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
 11/2/09 Unnecessary redundancy When we try to update the sales rep info for  customer 1005,  we need to repeat all the information
Background image of page 8
 11/2/09 Unnecessary constraint  It is impossible to enter information about a new  department,  unless we immediately assign an employee to that  department
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
 11/2/09 In this case, you lose data which was not meant  for deletion. It may affect A single record Multiple records Delete anomaly
Background image of page 10
 11/2/09 Delete anomaly Deleting information about Employee 705 will destroy the  only record  about the Information Services department
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
 11/2/09 Delete anomaly Deleting information about Mike Hernandez will result in  loss of data  for three orders!
Background image of page 12
It exists if changes to a specific value requires to  update several records Update anomaly Changes to the name of Lone Star Distributors propagate to  three records Is it a big deal? Can you write a script do 
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 14
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 36

Database Normalization - DatabaseNormalization...

This preview shows document pages 1 - 14. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online