With this rather large database in place and these helpful tools, do the following exercises.

Exercise 4.1

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

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 and then re-run your query on the view from the previous question. Do the results of the view query change? Why or why not?
3. Try to insert a new person using your new view. If this doesn’t work, explain (but do not implement) 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 inserted record in the base table not included in the view.
4. DROP your new view – does this affect your base tables in any way?

Exercise 4.2

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.