Create a view that for the CPDB “birthday czar” that includes each person’s full name, age (using TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate)/12) ) and birthdate (only), and then write commands that:

1. Retrieve the GenX people from the database (i.e., those born from 1961–1975).

2. Update the Person base table to include a GenX birthdate for some person who had a NULL birthdate before. Now, try to re-run your query on the view from the previous question. Do the results of the view query change? Why or why not?

3. Insert a new person using your new view. If this doesn’t work, explain the modifications you’d have to make to your view so that it does. Be sure that you understand what is required for a view to be updateable and what happens to the fields of the new record in the base table that are not included in the view.

4. Drop your new view. Does this affect your base tables in any way?

Store your results in lab07_1.sql .

Repeat the previous exercise, but this time use a materialized view. Pay particular attention to what changes in the view and in the table respectively.

Store your results in lab07_2.sql .

Do the following for the query on which the view in the previous exercises is based.

1. Write an equivalent query in the relational algebra.

2. Write an equivalent query in the tuple relational calculus.

Don’t worry about querying the concatenated full name or the age; just query the first/last name and birthdate. Store your results in lab07_3.txt (or an image file).