Question

Given the following table:

library(book_id,book_title,person_id,due_date,first_name,last_name,returned_date,late_fee)

With the following assumptions: -

One copy of each book is held by the library -

Person_id is unique for each person, book_id is unique for each book -

The library table is meant to store information about loans of books made by the library.

Here are the functional dependencies for the above table:

Book_id → book_title

Person_id → first_name,last_name

Book_id,person_id,due_date → returned_date,late_fee

Give an example of update anomaly for this table

ANY HELP PLEASE...ANY EXPLANATION WOULD BE REALLY HELPFUL :) [ 2 tables ] one as example and one after anomaly update please

Answer 1

An update anomaly is a data inconsistency that results from data redundancy and a partial update. For example, each employee in a company has a department associated with them as well as the student group they participate in.

Suppose You have below entries in your table:

book id book title person_id due_date first_name 1 Bookl 2 Book2 3 Book3 last_name LName1 15/2/2007 Name2 15/2/2008 LNamel 15/2/2009 returned_date late_fee 1 12/2/2007 Name1 2 12/3/2007 Name2 1 12/4/2007 Namel 12/2/2007 Namel 4
This is how the table looks like before anomaly.


Now suppose, later at point, you realise that the name of the person with person id 1 is wrong in the tables and his name is "NameX" instead of "Name1". Then you need to update the table whereever the "Name1" is present as the firstname corresponding to personId 1. If the person who is updating the data, don't know that the redundant data is lying around your table, he may just go and update a single occurence of firstname, which can cause data anomaly.

After Anomaly table may loook like below:

1 book_id book title person_id due_date first_name last_name returned_date late_fee 2 1 Bookl 2 Book2 3 Book3 1 12/2/2007 NameXLName1 15/2/2007 Name2 15/2/2008 LNamel 15/2/2009 12/3/2007 Name2 1 12/4/2007 Name1 4 4
You see that in one row, the first name corresponding to person_id 1 is "NameX" while in other it is "Name1". This is data anomaly and is caused by incorrect update operation, which was caused due to duplicate data lying in the table. Hence it is always recommended to keep the data at one place only and refer to data from there itself.