Transcript Level 3 Objectives: Identifying and Eliminating Database Anomalies by Normalizing Data •

Level 3 Objectives: Identifying and
XP
Eliminating Database Anomalies
by Normalizing Data
•
•
•
Learn the techniques for normalizing data
Evaluate fields that are used as keys
Test the database design
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
1
Normalizing the Tables in theXP
Database
•
Normalization
 Design process
 Goals
• Reduces space required to store data by eliminating
duplicate data in database
• Reduces inconsistent data in database by storing data
only once
• Reduces chance of deletion update and insertion
anomalies
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
2
Normalizing the Tables in theXP
Database (continued)
•
Deletion anomaly
 User deletes data from database
 Unintentionally deletes only occurrence of data in
database
•
Update anomaly
 Due to redundant data in database
 User fails to update some records or updates records
erroneously
•
Insertion anomaly
 User cannot add data to database unless preceded by
entry of other data
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
3
Normalizing the Tables in theXP
Database (continued)
•
Functional dependency
 Column in table considered functionally dependent on
another column
• If each value in second column associated with exactly
one value in first column
•
Partial dependency
 Field dependent on only part of primary key
•
Composite primary key
 Primary key uses two or more fields to create unique
records in table
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
4
Normalizing the Tables in theXP
Database (continued)
•
Determinant
 Field or collection of fields whose value determines
value in another field
 Inverse of dependency
•
Natural key
 Primary key that details obvious and innate trait of
record
•
Artificial key
 Field whose sole purpose is to create primary key
 Usually visible to users
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
5
Normalizing the Tables in theXP
Database (continued)
•
Surrogate key
 Computer-generated primary key
 Usually invisible to users
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
6
XP
First Normal Form
•
Repeating group
 Field contains more than one value
•
First normal form
 1NF
 Does not contain any repeating groups
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
7
XP
Second Normal Form
•
•
•
•
2NF
Table must be in 1NF
Must not contain any partial dependencies on
composite primary key
Tables in 1NF and contain primary key with only one
field
 Automatically in 2NF
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
8
XP
Third Normal Form
•
•
•
3NF
Only determinants must be candidate keys
Candidate key
 Field or collection of fields that could function as
primary key but was not chosen to do so
•
Transitive dependency
 Occurs between two nonkey fields both dependent on
third field
•
Tables in 3NF should not have transitive
dependencies
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
9
XP
Level 3 Summary
•
Normal forms
 First (1NF)
 Second (2NF)
 Third (3NF)
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
10
XP
Chapter Summary
•
Discovery:
 Identify existing and missing data
 Organize data into tables
 Determine data types for each field
•
Table relationships
 Established through common fields
 Types
• 1:M
• 1:1
• M:N
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
11
XP
Chapter Summary (continued)
•
Normalization
 Reduces duplication and inconsistency
 Forms:
• 1NF
• 2NF
• 3NF
Chapter 1
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
12