Assignment: Triggers, Stored Procedures and Views
This assignment should be completed individually. For each problem, submit your SQL statement and a screen shot of the SQL results in a single Word document or pdf file. Submit the file via eLearning.
I recommend creating a new user and workspace named after your netid, log in as that user and load the database script (provided in this week's assignment folder). Before you attempt to write any SQL queries, familiarize yourself with the database structure and data. I have provided a relational diagram and sample data for this database.
Write queries to address each of the problems below. Submit both the SQL statements and the screen prints of the outputs from Oracle. Be sure the workspace name is included in your screen shots!!!
OurVideo is a small movie rental company with a single store. OurVideo needs a database system to track the rental of movies to its members. OurVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie "Twist in the Wind". "Twist in the Wind" would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in the Figure below.
OurVideo ERD
1. Alter the VIDEO table to include an attribute named VID_STATUS to store character data up to 4 characters long. The attribute should not accept null values. The attribute should have a constraint to enforce the domain ("IN", "OUT", and "LOST"), and have a default value of "IN".
2. Create a trigger named trg_videorental_up that will update the correct value VID_STATUS in the VIDEO table whenever a video is checked out (OUT) or returned (IN). The trigger should execute as an AFTER trigger when the DETAIL_RETURNDATE attribute is updated in the DETAILRENTAL table. The trigger should satisfy the following conditions:
a. If he DETAIL_RETURNDATE in the detail rental table is set to NULL, the VID_STATUS should be set to "OUT".
b. If the DETAIL_RETURNDATE in the detail rental table is set to > than the current date, the VID_STATUS should be set to "OUT".
c. If the DETAIL_RETURNDATE in the detail rental table is set to < or = to the current date, the VID_STATUS should be set to "IN".
d. If the DETAIL_RETURNDATE in the detail rental table is set to "01/01/01", the VID_STATUS should be set to "LOST".
--After you have created the trigger, test the trigger. Insert or Update a record for each scenario in the detail rental table. Show the insert and update statements. When checking out a video, a new row is insert into the rental and detailrental tables. When checking in a video the return date is updated. When a video is lost the returndate is set to 01/01/01. To show that the trigger has run, show the output from the following query:
select dr.rent_num, dr.vid_num, v.movie_num, m.movie_title, v.vid_status, dr.detail_duedate, dr.detail_returndate
from detailrental dr, video v, movie m
where dr.vid_num = v.vid_num and m.movie_num = v.movie_num
3. Create a stored procedure named sp_overduefees that will return the overdue fees for an overdue video. It will accept 2 parameters - the video number and the member id. I have provided you with the query that will return the overdue fees for the video number and member id.
SELECT (detail_returndate-detail_duedate), detail_dailylatefee, detail_fee,
to_char(((detail_returndate-detail_duedate)*detail_dailylatefee)+detail_fee, '$999.99'
FROM rental r, detailrental dr, video v, movie mv
WHERE r.rent_num = dr.rent_num AND
dr. vid_num = v.vid_num AND
mv.movie_num = v.movie_num AND
to_date(detail_returndate) > to_date(detail_duedate) AND
v.vid_num = #### AND
r.mem_num = ###;
--After you have created the stored procedure, test the procedure. To run the procedure, execute the following statement:
begin
sp_overduefees(110, 34367);
end;
When you run the procedure, you should receive the following output:
4. Create a view that will show all the videos that are checked out. Call the view overdue_vw.
--After you have created the view, test the view by selecting all rows and columns from the view.
EC1: Create a stored procedure called sp_rental_members that accepts the member number as a parameter. This procedure should print all the videos that this member has checked out. The output should display the member number, the video number, title, rent date, due date, and return date. The output should look like the following:
Hint: You will need a single row SELECT statement to display the member name and a cursor to display the rest.
Attachment:- SQL-Assignment.rar