1. Answer to the questions using the following table (20 points) EMP NAME Je cOD
ID: 3869779 • 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.
1. EMP_NUM should be the identifier of the given table because as per the definition of primary key or identofier of the table, its value should be unique and not null. These both conditions are satisfied by the attribute EMP_NUM.
2. Insertion anomaly occurs when some attributes in the table cannot be inserted without the presence of some other attributes. The insertion anomaly does not guarentee that the value of identifier is NOT NULL.
3. Scenarios
a)
If a new Project has been created but has not been assigned to any employee. In this case we have PROJ_NUM,PROJ_NAME,PROJ_HOURS are know but EMP_NUM,EMP_NAME,EMP_PHONE are not known. But the value of EMP_NUM cannot be left NULL as it is identifier key.
b)
If a new employee joined the company, but has not been assigned any project, The values of EMP_NUM, EMP_NAME ,EMP_PHONE are known but PROJ_NUM,PROJ_NAME,PROJ_HOURS are not known .
2. Normalized tables
EMPLOYEE(EMP_NUM,EMP_NAME,EMP_PHONE)
PROJECT(PROJ_NUM,PROJ_NAME,PROJ_HOURS)
JOB(JOB_CODE,JOB_CHG_HOUR)
underlined attributes are primary keys
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.