Microsoft Access

1
Microsoft Access

• Removing Redundancy in a Database

2
Objectives

• Identify repeated data
• Identify related information
• Redundant data anomalies
• Removing redundancy

3
Identifying Repeated Data
ID Number Name Amount Paid Trans. Date
93500 Phillip, Doe 15.75 4/12/2009
93500 Phillip, Doe 20.35 4/18/2009
93500 Phillip, Doe 15.75 5/23/2009
97583 James, Elsa 25.00 5/23/2009

• Repeated data for person with ID Number 93500 has
  to be kept in this table, in order to track
  amount paid and transaction date
• Some repeated data may seem redundant (for
  example, Transaction Date). But should actually
  be treated as separate fields

4
Identifying Related Information
ID Number Name Amount Paid Trans. Date
93500 Phillip, Doe 15.75 4/12/2009
93500 Phillip, Doe 20.35 4/18/2009
93500 Phillip, Doe 15.75 5/23/2009
97583 James, Elsa 25.00 6/17/2009

• ID Number and Name data describe information
  relevant to a person. Thus, ID Number and Name
  with (93500, Phillip Doe), should to kept in a
  Persons table
• The Amount Paid and Transaction Date describe
  transaction data, and should be kept in a
  Transactions table, linked to the Persons table
  through the ID Number

5
Redundant Data Anomalies

• Redundant databases can have modifications
  anomalies
• Update anomaly
• Insertion anomaly
• Deletion anomaly

6
Update Anomaly

• Update to an employees address information did
  not fully complete
• The same employee has conflicting address
  information

7
Insertion Anomaly

• Suppose a table stores information about faculty
  and their courses
• New faculty that has not been assigned to teach
  any courses, cannot be inserted into the database

8
Deletion Anomaly

• When the faculty member temporarily stops
  teaching the course assigned, the entire record
  including information about the faculty has to
  be deleted

9
Removing Redundancy

• Normalization increases efficiency by
  eliminating redundant data, while ensuring that
  only related data are kept together
• Separate tables are kept for each group of
  related data
• Sets of related data are uniquely identified with
  a primary key
• Relationships between tables are established via
  link fields

10
Faculty and Courses
Faculty ID Faculty Name Hire Date
389 Dr. Giddens 10-Feb-1985
407 Dr. Saperstein 19-Apr-1999
424 Dr. Newsome 29-Mar-2007
Course Code Faculty ID
ENG-206 389
CMP-101 407