MCS-274 Lab 2 (Spring 2005)

Objective

You will modify your movie database in ways that probe some additional integrity-constraint issues.

Foreign key triggers

In the previous lab, your create table statements should have included some foreign key constraints. If not, add them now. You might, however, have left out the triggers that specify what should be done on deletions and updates. Decide what the right actions are and add the on delete and on update triggers, unless you already had them in lab 1. (In the the latter case, your report should show again what you had.)

Additional requirements

Modify both your E-R model and your SQL create table and select statements to accommodate the following two new requirements.

• Individual copies of movies may be withdrawn from circulation. You should retain records of prior rentals of the withdrawn copies. However, the withdrawn copies should not be considered available for rental, when a query is made regarding movies with a particular actor. Also, decide whether it should be legal to withdraw a movie that is out on rental at the time. If so, what should happen to the record of that rental? Take these decisions into account in your design and implementation.
• Customers have accounts that can be deactivated and reactivated. All rentals need to be to active accounts. Decide exactly what this means and how it should be enforced

Lab report

Write a report that is concise and assumes an audience familiar with your assignment and the technology, but which addresses all the above topics in an organized fashion.