Do the following exercises on the HR database (see HR and OE Schemas):

1. Create a view of all employees and their department; include the employee ID, name, email, hire date and department name. Then write SQL commands to do the following:

   1. Get the name and ID of the newest employee in the “Executive” department.
   2. Change the name of the “Administration” department to “Bean Counting”.
   3. Change the name of “Jose Manuel” to just “Manuel”
   4. Insert a new employee in the “Payroll” department (make up appropriate data for this record).

   If any of these view-based queries won’t work, show the query and explain why it generates an error.

2. Redo the previous exercise with a materialized view.

3. Write the following queries as specified:

   1. The query on which your view from exercise 1 is based - Write this query using both the relational algebra and tuple relational calculus, with respect to the original HR relations.
   2. The query from exercise 1.a - Write this query using (only) the relational calculus, with respect to DeptView.

   You may hand write this and turn in an image of your solution, or turn it in in ASCII format, e.g.:

   • PI_firstName,lastName(SIGMA_gender=f(Student)) for the algebra
   • {s1 | Student(s) ^ s.gender='female'} for the calculus.

4. Complete the programming elements for the lab exercises.

Checking in

We will grade your work according to the following criteria:

• 30% — Exercise 1
• 30% — Exercise 2
• 30% — Exercise 3
• 10% — Exercise 4