Lecture_7_-_Normalization_of_Database_Tables

Lecture_7_-_Normalization_of_Database_Tables - CSCI-507...

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

View Full Document Right Arrow Icon
1 Lecture 7 Normalization of Database Tables CSCI-507 Database Theory 2 Objectives • In this lecture, students will learn: – What normalization is and what role it plays in the database design process – About the normal forms 1NF, 2NF, 3NF, BCNF, and 4NF – How normal forms can be transformed from lower normal forms to higher normal forms – That normalization and ER modeling are used concurrently to produce a good database design – That some situations require denormalization to generate information efficiently
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 3 Database Tables and Normalization Normalization – Process for evaluating and correcting table structures to minimize data redundancies • Reduces data anomalies – Series of stages called normal forms: • First normal form (1NF) • Second normal form (2NF) • Third normal form (3NF) 4 Database Tables and Normalization (cont’d.) • Normalization (continued) – 2NF is better than 1NF; 3NF is better than 2NF – For most business database design purposes, 3NF is as high as needed in normalization – Highest level of normalization is not always most desirable Denormalization produces a lower normal form – Increased performance but greater data redundancy
Background image of page 2
3 5 The Need for Normalization • Example: company that manages building projects – Charges its clients by billing hours spent on each contract – Hourly billing rate is dependent on employee’s position – Periodically, report is generated that contains information such as displayed in Table 6.1 6
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 7 8 The Need for Normalization (cont’d.) • Structure of data set in Figure 6.1 does not handle data very well • Table structure appears to work; report is generated with ease • Report may yield different results depending on what data anomaly has occurred • Relational database environment is suited to help designer avoid data integrity problems
Background image of page 4
5 9 The Normalization Process • Each table represents a single subject • No data item will be unnecessarily stored in more than one table • All nonprime attributes in a table are dependent on the primary key • Each table is void of insertion, update, deletion anomalies 10
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 11 The Normalization Process (cont’d.) • Objective of normalization is to ensure that all tables are in at least 3NF • Higher forms are not likely to be encountered in business environment • Normalization works one relation at a time • Progressively breaks table into new set of relations based on identified dependencies 12
Background image of page 6
7 13 Conversion to First Normal Form
Background image of page 7

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

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

This note was uploaded on 04/15/2011 for the course CSCI 507 taught by Professor Rashid during the Spring '11 term at George Mason.

Page1 / 27

Lecture_7_-_Normalization_of_Database_Tables - CSCI-507...

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

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