Level 3 Objectives: Identifying and Eliminating Database Anomalies by Normalizing Data •
Download
Report
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