1. Answer to the questions using the following table (20 points) EMP NAME Je cOD
ID: 3891041 • Letter: 1
Question
1. Answer to the questions using the following table (20 points) EMP NAME Je cODE 101 John D. Newson EE 105 David F. Schwann CT 110 Anne R.Ramoras CT 101 JohD Newson 8500 6000 60.00 85.00 85.00 6200 26.00 85.00 85.00 Hurricne 162 653-234-1123 Humicane Hurricane 2 Coast 19.8 653-234-3254 175 905-554-7812 Coast 3 Satelite 3 Setelite 3 Satelite 3 Satelite 110 Anne R. Ramoras CT 105 David F. Schwann CT 123 Mary D. Chen 112 Alecia R. Smth BE 234 653-234-1123 (c) What is the identifier in the above table? Justify your answer based on the definition of the identifier. (5 (2) What is insertion anomaly? How is it related with the (3) How can you describe the insertion anomaly in the points) identifier? (5 points) above table using the identifier you have found in (1)? Use some specific examples or business scenarios. (10 points) 2. Suggest a set of new tables (with sample data set) that can resolve the redundancy and anomalies found in the table in Q1. (20 points)Explanation / Answer
1. The identifier in the table is combination of (Proj_num and Emp_num)
2)
An Insert Anomaly occurs when certain identfier cannot be inserted into the database without the presence of other identfier.
3)
In the above table, we just can't have project_num or emp_num seperately.. THey both must be associated. In a real life scenario, a new employee may not be assigned any project, in that case it can not be inserted in this table, because for insertion, the proj_um and emp_num both olumns are required. This is insertion anomaly.
New set of tables:
Projects: Project_num(PK), Project_name
Employee: Emp_num(PK), Emp_name, JOB_CODE, JOB_CHG_HOUR, EMP_PHONE
Emp_IN_Projects: Emp_num(FK), Proj_num(FK), Proj_hours
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.