Question 4. (20 points) Consider the table below where a certain firm is keeping

Question

Question 4. (20 points) Consider the table below where a certain firm is keeping track of temporary worker assignments. This is their only database, they store all relevant information in its single table. Each row corresponds to a single assignment, which is performed by a temporary worker who is identified by "Temp ID" and with name "Temp Name". Each assignment is done for a certain department identified by "Dept Code" and with name "Dept Name", and it consists on doing a certain job identified by "Job Code" and "Job Description". Each assignment also has a "Date", a certain number of hours ("Hours Spent"), and it is paid at a certain rate per hour ("Rate/Hour"). The rate per hour is negotiated separately for each assignment. Your goal is to redesign their single table database Below you have some rows of the table to help you understand the kind of data it stores, and how the data relates with itself. Assignment Temp Temp ID Job Job Dept Code Name Dept Hours Rate Spent /hour Code Description Date Consulting Consulting ID Name 1013 Bill WongENGEngineering TC Technica 2/27/2011 |3 $100 11765 1015 Melanie EMKTMarketing TC Technical 1/25/2011 |4 120 ast 11767 11768 11769 2051 Ed North ACT 2051 Ed North MKT 3356 John Accounting CLR Clerical Marketing CLR Clerical Sales 1/31/2011 |2 2/10/2011 4 SC Strategic 2/15/20115 $50 $55 $90 SLS Edokway Consulting $60 S65 $110 $70 3356 John MKT Marketing EDT Editing 3/20/2011|6 Edokway Estevez Goldberg Goldberg 4076Marissa ENG Engineering EDT Editing 2/26/2011 |7 11766 4079 HannahENGEngineering TC Technical 1/25/2011 |4 Consulting 4079 Hannah MKT MarketingEDT Editing 2/25/2011 5 a) What can go wrong with storing such information in a single table like the one above? Give one example of an insertion anomaly and of an update anomaly that can happen when using the table above

Explanation / Answer

In databases, we perform Normalization to normalize our data. It helps in maintaing the data integrity and reduces the redundancy of the data. In the table above, anomalies are easy to happen. Anomalies are a problem for any data administrator. The anomalies that can be faced in above table are:

Update Anomaly: If the data items of a particular data set are scattered here and there, then it gets difficult to update a record as it might lead to update of one but not of the other. Thus, linking of data sets is very important.

Delete Anomaly: We have to delete a data but by mistake we deleted a part of data while some part is still not deleted. Thus, it might lead to Delete anomaly.

Insert Anomaly: This can also be reffered to as reverse of Delete Anomaly, It usually occurs when some of the attributes cannot be inserted while some can be.

Example:: Insert Anomaly:: In the table above we cannot assign the Assignment ID unless we have a TEMP ID of the employee already. Therefore, temp id being an important constaint cannot vbe added.

Update Anomaly:: If in the table we update the Dept Name of Ed North to Accounting, it would lead to data redundancy due to which same Employee would be having same department name and is taking different salaries. Thus, update anomaly is important as if data is modified once, the changes are reflected in the other databases as well.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.