Name and explain what are the 3 problems caused by the design of the table shown
ID: 2246910 • Letter: N
Question
Name and explain what are the 3 problems caused by the design of the table shown in the figure below. The types of problems I'm referring to regard to the general design of the database, such as data redundancy. Interpret the question at face value.
ID amesalarydept namebuilding budget dept naie 22222 Einstein 95000 Physics 12121 Wu 32343 El Said 45565 Katz 98345 Kim 76766 Crick 10101 Srinivasan 65000Comp. Sci.Taylor100000 58583Califie 62000History 83821 Brandt 15151Mozart 33456 Gold 76543 Singh Watson 70000 Painter 120000 Painter 90000 Finance 60000 History 75000 Comp. Sci.Taylor 100000 80000 E 72000 Biology lec. Eng. Taylor 85000 Watson 90000 C1. Painter 92000 Comp. SciTaylor100000 40000 Music 87000 Physics 80000 Finance Packard 80000 Watson 70000 Painter 120000Explanation / Answer
The three problems in the design of this database are:
(i) DATA REDUNDANCY: Each person works for some department. Hence for each person the department tuple is being repeated.For eg. for id=22222 and for id=33456, the department data of physics department is being repeated.Therefore, it should be divided in 2 schemas, employee and department and dept_name should be place as a foreign key in employee.
(ii) REDUNDANT EFFORTS: If we want to change any atrribute related to department, then it will have to be changed in all the tuples of that paticular department. Hence the decompostion suggested in (i) should be used.
(iiI) DELETION ANOMALIES: If the employee working in elec. eng. leaves then the data associated with elec. eng will be lost.Hence, decomposition suggested in (i) should be applied.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.