Question I (80 points total) Consider the following relational database for storing data about employees and projects: employee (employee_ID, name, annual salary) project (project_name, budget) participation employee_ID, project_name) (d) (10 points) Write a statement in SQL to update the annual salary of each employee as follows: If the current annual salary is less than $100,000, increase the salary by 4 percent. Otherwise, increase the salary by 3 percent. (e) (10 points) Write a statement in SQL to find the total amount (i.e., sum) of budget to support all projects in which employee 'El' participates. (1) (10 points) Write an expression in Relational Algebra to find the total amount (i.e., sum) of budget to support all projects in which employee 'El' participates. (g) (10 points) Write a statement in SQL to find the name of each project with the LARGEST number of members (i.e., each project that has no fewer members than any other projects). (h) (10 points) Two students attempted to write SQL statements to find the IDs of the employees participating in project 'Pl' but not 'P2' as follows: Q1: (select employee_ID from participation where project_name = '21') except (select employee_ID from participation where project_name = 'P2'); Q2: select distinct pi employee_ID from participation as pi, participation as p2 where p1. project_name = 'Pi' and p2.project_name = 'P2' and pi.employee_ID = P2. employee_ID: These students are not sure whether or not their statements are correct. Are their statements equivalent (i.e., guaranteed to always produce the same set of records)? If so, breifly explain the reason. If not, explain the reason using an example (i.e., an instance of the participation table on which the above queries will produce different results). Question I (80 points total) Consider the following relational database for storing data about employees and projects: employee (employee_ID, name, annual salary) project (project_name, budget) participation employee_ID, project_name) (d) (10 points) Write a statement in SQL to update the annual salary of each employee as follows: If the current annual salary is less than $100,000, increase the salary by 4 percent. Otherwise, increase the salary by 3 percent. (e) (10 points) Write a statement in SQL to find the total amount (i.e., sum) of budget to support all projects in which employee 'El' participates. (1) (10 points) Write an expression in Relational Algebra to find the total amount (i.e., sum) of budget to support all projects in which employee 'El' participates. (g) (10 points) Write a statement in SQL to find the name of each project with the LARGEST number of members (i.e., each project that has no fewer members than any other projects). (h) (10 points) Two students attempted to write SQL statements to find the IDs of the employees participating in project 'Pl' but not 'P2' as follows: Q1: (select employee_ID from participation where project_name = '21') except (select employee_ID from participation where project_name = 'P2'); Q2: select distinct pi employee_ID from participation as pi, participation as p2 where p1. project_name = 'Pi' and p2.project_name = 'P2' and pi.employee_ID = P2. employee_ID: These students are not sure whether or not their statements are correct. Are their statements equivalent (i.e., guaranteed to always produce the same set of records)? If so, breifly explain the reason. If not, explain the reason using an example (i.e., an instance of the participation table on which the above queries will produce different results)
