db-fds.pdf - Databases Redundancy Jianxin Li School of...

This preview shows page 1 - 7 out of 50 pages.

Databases - Redundancy Jianxin Li School of Computer Science & Software Engineering University of Western Australia Jianxin Li (UWA) Redundancy 1 / 41
This lecture Redundancy in a DBMS refers to the storage of the same piece of data in multiple places. While controlled redundancy (for example, system backups) are necessary, dealing with uncontrolled redundancy is a major issue in any database management system. The concepts of functional dependencies and the associated theory of normalization is a mathematical theory dealing with redundancy. Jianxin Li (UWA) Redundancy 2 / 41
Redundancy One of the main reasons for using relational tables for data is to avoid the problems caused by redundant storage of data. For example, consider the sort of general information that is stored about a student: Student Number Name Address Date of Birth Different parts of the university may keep different additional items of data regarding students, such as grades, financial information and so on. Jianxin Li (UWA) Redundancy 3 / 41
Repeating Data Suppose that marks are kept in the following format: Student Number Name Unit Code Mark 14058428 John Smith CITS1402 72 14058428 John Smith CITS1401 68 14058428 John Smith CITS2200 68 15712381 Jill Tan CITS1401 88 15712381 Jill Tan CITS1402 82 Then this table contains redundant data , because the student’s name is repeated in numerous different rows. If the financial system also stores student numbers and names, then there is redundancy between tables as well as within tables. Jianxin Li (UWA) Redundancy 4 / 41
Problems with redundancy Apart from unnecessary storage, redundancy leads to some more significant problems: Update Anomalies If one copy of a data item is updated — for example, a student changes his or her name — then the database becomes inconsistent unless every copy is updated. Insertion Anomalies A new data item — for example, a new mark for a student — cannot be entered without adding some other, potentially unnecessary, information such as the student’s name. Deletion Anomalies It may not be possible to delete some data without losing other, unrelated data, as well (an example is on the next slide). Jianxin Li (UWA) Redundancy 5 / 41
Deletion Anomalies A deletion anomaly occurs when a table storing redundant information becomes a proxy for storing that information properly. For example, suppose that a company pays fixed hourly rates according to the level of an employee: Name Level Rate Smith 10 55.00 Jones 8 30.00 Tan 10 55.00 White 9 42.00 . . . . . . . . . This table contains not only the employee data, but also the association between the level of an employee and the rate for that level. Jianxin Li (UWA) Redundancy 6 / 41

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture