(1) Given the following Company Database, answer questions 1.a & 1.b (16 Points) EMPLOYEE JOB EmployeeCode EmployeeLastName JobCode JobCode Job Description 14 Rudell 1 Clerical 15 McDade 1 2 Technical 16 Ruellardo 1 3 Managerial 17 Smith 3 20 Smith 2 ENROLLMENT EmployeeCode PlanCode PLAN 15 2 PlanCode PlanDescription 15 3 1 Term life 16 1 2 Stock purchase 17 1 3 Long-term disability 17 3 4 Dental 17 20 3 4 a) Identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. Primary Key Foreign Key(S) Table EMPLOYEE JOB PLAN ENROLLMENT b) Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. If Yes, Why? Otherwise write NA Table EMPLOYEE JOB PLAN ENROLLMENT Q2) Given the following database, identify the primary key and foreign key for each table. If a table does not have a foreign key, write None in the space provided (12 points) EMPLOYEE (SSN, Employee Name, BirthDate, Address, Department Number) DEPRTMENT (Department Number, DepartmentName, Department Manager SSN) DEPARTMENT_LOCATION (Department Number, Department Location) WORKS_ON (SSN, ProjectNumber, Hours) PROJECT (ProjectNumber, ProjectName, ProjectLocation, Department Number) Primary Key Foreign Key Table EMPLOYEE DEPARTMENT DEPARTMENT-LOCATION WORKS-ON PROJECT Q3) Create the table that results from applying a SELECT relational operator to the PRODUCT table. The SELECT operator is as follow: Price = 3.75 (ITEM) (4 points) ITEM Item Price Flash Drive 8 Mouse 3 Keyboard 7.5 USB Cable 1.75 Speaker 13 Wireless Mouse 4.75 Q4) Create the table that results from applying a PROJECT relational operator to the PRODUCT table. The PROJECT operator is as follow: * Price (ITEM) (4 points) ITEM Item Price Flash Drive 8 Mouse 1.75 Keyboard 7.5 USB Cable 1.75 Speaker 13 Wireless Mouse 7.5 | (5) Create the table that results from applying a UNION relational operator to the following tables. The UNION operator is as follow: PI U P2 (3.5 points). Pl Item Price Flash Drive 8 Mouse 3 Keyboard 7.5 P2 Item Price Mouse 3 Keyboard 7.5 USB Cable 1.75 Speaker 13 Wireless Mouse 4.75 6) Create the table that results from applying an INTERSECT relational operator to the following tables. The INTERSECT operator is as follow: P1 P2 (3.5 points). P1 Item Price Flash Drive 8 Mouse Keyboard 3 7.5 P2 Item Price Mouse 3 Keyboard 7.5 USB Cable 1.75 Speaker 13 Wireless Mouse 4.75 27) Create the table that results from applying a DIFFERENCE relational operator to the following tables. The DIFFERENCE operator is as follow: P1 P2 (3.5 points) P1 Item Price SSD 46 Mouse 1 3 Keyboard 7.5 Cable 1.75 P2 Item Price Mouse 2 3 Keyboard 7.5 USB Cable 1.75 Speaker 13 Wireless Mouse 4.75 Q8) Create the table that results from applying a PRODUCT relational operator to the following tables. The PRODUCT operator is as follow: T1 x T2 (3.5 points) TI Student ID 1001 1002 1003 Name Allan Mike Sarah Luca Mike Doe T2 Department Computer Science Biology Art English
