100 likes | 169 Vues
Discover how to identify repeated data, related information, and eliminate redundancy anomalies in your database. Redundant data can lead to various anomalies such as update, insertion, and deletion discrepancies, affecting data integrity. Learn to implement normalization techniques, which involve organizing data into separate tables, ensuring only related data are kept together.
E N D
Microsoft Access Removing Redundancy in a Database
Objectives • Identify repeated data • Identify related information • Redundant data anomalies • Removing redundancy
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
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
Redundant Data Anomalies • Redundant databases can have modifications anomalies: • Update anomaly • Insertion anomaly • Deletion anomaly
Update Anomaly • Update to an employee’s address information did not fully complete • The same employee has conflicting address information
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
Deletion Anomaly • When the faculty member temporarily stops teaching the course assigned, the entire record –including information about the faculty– has to be deleted
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