The Employee Report below contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic. Sample EMPLOYEE Records Attribute Name EMP NUM EMP LNAME EMP EDUCATION JOB CLASS EMP DEPENDENTS ain 1003 Smith BBA SLS McGuire BBA, MBA SLS Gerald (spouse), McGuire BS, MS, Ph.D DBA INT JoAnne (spouse) George (spouse) Mary (daughter), Jill (daughter) DEPT CODE DEPT NAME DEPT MANAGER EMP TITLE EMP DOB EMP HIRE DATE EMP TRAINING EMP BASE SALARY EMP COMMISSION RATE 0.015 John (son MKTG Marketing Jill H. Martin Sales Agent 23-Dec-1968 14-Oct-1997 L1. L2 $38,255.00 MKTG Marketin Jill H. Martin Sales Agent 28-Mar-1979 15-Jan-2006 SVC General Service Info. Systems Hank B. Jones Janitor 18-May-1982 21-Apr-2003 L1 $19.750.00 INFS CarloS U DB Admin 20-Jul-1959 15-Jul-1999 L1. L3. L8. L15 $127,900.00 $30,500.00 0.010 a. Convert the report to a relation in First Normal Form and write the relational schema. Take note of all transitive and/or partial dependencies. b. Develop the relational schema to take the Draw the dependency diagrams that are in 3NF. What steps need to be taken to convert the relation to Second Normal Form? Write the relational schema for the relation in Second Normal Form. Write the relational schemas that meet 3NF requirements. Rename attributes to meet the naming conventions, and create new entities and attributes as necessary. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, that the naming conventions are met, and so on.) c. The Employee Report below contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic. Sample EMPLOYEE Records Attribute Name EMP NUM EMP LNAME EMP EDUCATION JOB CLASS EMP DEPENDENTS ain 1003 Smith BBA SLS McGuire BBA, MBA SLS Gerald (spouse), McGuire BS, MS, Ph.D DBA INT JoAnne (spouse) George (spouse) Mary (daughter), Jill (daughter) DEPT CODE DEPT NAME DEPT MANAGER EMP TITLE EMP DOB EMP HIRE DATE EMP TRAINING EMP BASE SALARY EMP COMMISSION RATE 0.015 John (son MKTG Marketing Jill H. Martin Sales Agent 23-Dec-1968 14-Oct-1997 L1. L2 $38,255.00 MKTG Marketin Jill H. Martin Sales Agent 28-Mar-1979 15-Jan-2006 SVC General Service Info. Systems Hank B. Jones Janitor 18-May-1982 21-Apr-2003 L1 $19.750.00 INFS CarloS U DB Admin 20-Jul-1959 15-Jul-1999 L1. L3. L8. L15 $127,900.00 $30,500.00 0.010 a. Convert the report to a relation in First Normal Form and write the relational schema. Take note of all transitive and/or partial dependencies. b. Develop the relational schema to take the Draw the dependency diagrams that are in 3NF. What steps need to be taken to convert the relation to Second Normal Form? Write the relational schema for the relation in Second Normal Form. Write the relational schemas that meet 3NF requirements. Rename attributes to meet the naming conventions, and create new entities and attributes as necessary. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, that the naming conventions are met, and so on.) c
