(Database Design ERDs) You can use the draw.io website to complete Task2 and save your lab file as a PDF file and attached to your zip file.. Inclding this word doc and draw.io result you can combine and zip. Or alternatively draw on piece of paper take a picture and upload to your computer and combine with this word doc . You need to submit either a single PDF file for this lab or single zip file

Tasks:

1-1.Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure Pl.X (One of the many problems with data redundancy is the likely occurrence of data inconsistencies - that two different initials have been entered for the teacher named Maria Cordoza.) FIGURE P1.X The File Structure for Problems A1-A2 A-1) Given the file structure shown in Figure Pl.X, what problem(s) might you encounter if building KOM were deleted? A-2) Using your school's student information system, print your class schedule. The schedule probably would contain the student identification number, student name, class code, class name, lass credit hours, class instructor name, the class meeting days and times, and the class room number. Use Figure P1.11 as a template to complete the following actions. FIGURE P1.11 STUDENT SCHEDULE DATA FORMAT a. Create a spreadsheet using the template shown in Figure P1.11 and enter your current class schedule. b. Enter the class schedule of two of your classmates into the same spreadsheet. c. Discuss the redundancies and anomalies caused by this design. Task 2) Suppose you are working within the framework of the conceptual model in Figure 1 below. Figure 1 The Conceptual Model Car Maintenance Services Given the conceptual model in Figure 1: a. Write the business rules that are reflected in it. Even a simple ERD such as the one shown in Figure Q4.5 is based on many business rules. Make sure that each business rule is written on a separate line and that all of its details are spelled out. In this case, the business rules are derived from the ERD in a "reverse-engineering" procedure designed to document the database design. In a real world database design situation, the ERD is generated on the basis of business rules that are written before the first entity box is drawn. (Remember that the business rules are derived from a carefully and precisely written description of operations.) Here are business rules: 1. 2. 3. 4. 3) Use the database shown in Figure P3.10 to work Problems 3-1 to 3-6. Note that the database is composed of four tables that reflect these relationships: - An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs. - An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs. Note also that the M:N relationship has been broken down into two 1:M relationships for which the BENEFIT table serves as the composite or bridge entity. FIGURE P3.10 BeneCo Database Tables 3-1 ) For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the assigned space provided. 3-2) Create the ERD to show the relationship between EMPLOYEE and JOB. The ERD is shown in Figure P3.11. Note that the JOB_CODE =1 occurs twice in the EMPLOYEE table, as does the JOB_CODE =2, thus providing evidence that a JOB can be assigned to many EMPLOYEEs. But each EMPLOYEE has only one JOB_CODE, so there exists a 1:M relationship between JOB and EMPLOYEE. Draw ERD for the EMPLOYEE-JOB Relationship here 3-3) Create the relational diagram to show the relationship between EMPLOYEE and JOB. The relational schema is shown in Figure P3.12. Draw Relational Diagram or ERD here 3-4) Do the tables exhibit entity integrity? Answer yes or no and then explain your answer. 3-5) Do the tables exhibit referential integrity? Answer yes or no and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. 3-6) Create the ERD to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN. (Full ERD) Please draw BeneCo Crow's Foot ERD here