1 / 21

Update Anomalies

Update Anomalies. CS157B By : Anh Ngo. Data Redundancy. The duplication of information within a table Example: Duplicated information: Information: Branch [B1] located at [6 Lanark Square]. Update Anomalies.

alfonso-gomez
Download Presentation

Update Anomalies

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Update Anomalies CS157B By: Anh Ngo

  2. Data Redundancy • The duplication of information within a table • Example: • Duplicated information: • Information: Branch [B1] located at [6 Lanark Square]

  3. Update Anomalies • A situation, usually caused by redundancy in the schema, in which an update to one value affects another value. • The problems exist in an un-normalized database • Data updating procedures such as insertion, deletion, and modification might result in • Insertion Anomalies • Deletion Anomalies • Modification Anomalies

  4. Insertion Anomalies • A situation, usually cased by redundancy in the schema, in which the insertion of a row in a table creates an inconsistency with other rows

  5. Insertion Anomalies Example • Primary key = ‘StaffID’ • Situation: • Cooperation decides to build a new branch [B5] at [115 Nola Drive] • [B5] currently has no staff member • Action: • Insert into table values (null, null, null, null, null, B5, 115 Nola Drive) • Problems: • Primary key: ‘StaffID’ of this new row = null (not allow)

  6. Deletion Anomalies • A situation, usually caused by redundancy in the schema, in which the deletion of one row of a tables results in the deletion of an unintended information

  7. Deletion Anomalies Example • Primary key = ‘StaffID’ • Situation: • Cooperation decides to fire staff [Juha] • Action: • Delete from table where Name = ‘Juha’ • Problems: • Since [Juha] was the last staff of branch [B3] • Deleting [Juha] cause deletion of branch [B3] (data lost)

  8. Modification Anomalies • A situation, usually caused by redundancy in the schema, in which the modification of a row creates an inconsistency with another row

  9. Modification Anomalies Example • Primary key = ‘StaffID’ • Situation: • Cooperation moves staff [Alex] and branch [B1] to [10 Erie] • Action: • Update table set Br_Address = ‘10 Erie’ Where BranchID = ‘B1’ and Name = ‘Alex’; • Problems: • [Paul] doesn’t know [B1] moves to [10 Erie] (inconsistency)

  10. Decompositions • Decompose a single entity into many smaller entities • Definition: • Let R be a relation schema • A set of relation schemas {R1, R2, …, Rn} is the decomposition of R • R = R1 U R2 U … U Rn • Each Ri is a subset or R • Goal: • Eliminate redundancy and data anomalies by decomposing a relation into several smaller relations with higher normal form

  11. Lossly Decomposition • Instances of the decomposed relations are unable to reconstruct the corresponding instance of the original relation – information loss

  12. Lossly Decomposition Example

  13. A decomposition of a relation is called a lossless decomposition of that relation if the natural join of the decomposition produces exactly the original relation Lossless Decomposition

  14. Lossless Decompositions Example

  15. Insertion Anomalies • Primary key = ‘StaffID’ • Situation: • Cooperation decides to build a new branch [B5] at [115 Nola Drive] • [B5] currently has no staff member • Action: • Insert into table values (null, null, null, null, null, B5, 115 Nola Drive) • Problems: • Primary key: ‘StaffID’ of this new row = null (not allow)

  16. Insertion Anomalies Solution • Primary key = ‘BranchID’ • Situation: • Cooperation decides to build a new branch [B5] at [115 Nola Drive] • [B5] currently has no staff member • Action: • Insert into Branch values (B5, 115 Nola Drive)

  17. Deletion Anomalies • Primary key = ‘StaffID’ • Situation: • Cooperation decides to fire staff [Juha] • Action: • Delete from table where Name = ‘Juha’ • Problems: • Since [Juha] was the last staff of branch [B3] • Deleting [Juha] cause deletion of branch [B3] (data lost)

  18. Deletion Anomalies Solution • Primary key = ‘StaffID’ • Situation: • Cooperation decides to fire staff [Juha] • Action: • Delete from Staff where Name = ‘Juha’

  19. Modification Anomalies • Primary key = ‘StaffID’ • Situation: • Cooperation moves staff [Alex] and branch [B1] to [10 Erie] • Action: • Update table set Br_Address = ‘10 Erie’ Where BranchID = ‘B1’ and Name = ‘Alex’; • Problems: • [Paul] doesn’t know [B1] moves to [10 Erie] (inconsistency)

  20. Modification Anomalies Solution • Primary key = ‘BranchID’ • Situation: • Situation: • Cooperation moves staff [Alex] and branch [B1] to [10 Erie] • Action: • Update Brach set Br_Address = ‘10 Erie’ Where BranchID = ‘B1’

  21. THE END THE END

More Related