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 • 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 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 employee’s 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