Question: Question I (70 points total) Consider the following relational database for storing data about employees and projects: employee (employee_id, name, annual salary) project (project_name, budget)

Question I (70 points total) Consider the following relational database for storing data about employees and projects: employee (employee_id, name, annual salary) project (project_name, budget) participates (employee_id, project_name) Assume that (i) each employee is assigned a unique ID, (i) each project is assigned a unique name, (iii) an employee may participate in multiple projects and multiple employees may participate in the same project, and (iv) some employees may not participate in tiny project. (e) (10 points) Write an expression in SQL to find, for each project, the ID(s) of the participant(s) that earn(s) the highest annual salary, as well as their salary. For example, if employees '1111', '1112' and '1113' participate in project 'X' and they make $100,000, $90,000 and $100,000, respectively, the query result must include ('X', '1111', 100000) and ('X', '1113', 100000). The query result must include similar records for every other project. (f) (10 points) Write an expression in relational algebra to find the total amount of the budget to support projects in which employee '1111' participates. (g) (10 points) Write an expression in relational algebra to find the names of the projects in which at least 10 employees whose annual salary is greater than $100,000 participate
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
