FIT5132 Database
Technology
Semester 2, 2014
Assignment 2
Due Date: Friday, 26th September, 10 PM.
Place: Moodle submission page. Assignment Weighting: 15%
In this assignment you will use the same database model for the DVD rental business that you used in assignment 1.
The schema file and insert data has been provided on Moodle.
Develop and document the SQL statements to answer the following tasks along with the results they return.
Task 1: SQL Queries (75 marks):
These tasks must be carried out in a single SQL script file called FIT5132-a2-task1.sql. The file must include a set echo on statement as the first line and a set echo off statement as the last line. You must ensure that the file has an appropriate header comment containing your name and student id. Each part of the question should be indicated by an appropriate comment (the part number eg -- (i) is sufficient). When the completed script is run you are required to save the output into a file called FIT5132-a2-task1-output.txt
(i) Display the full details for all members - the name details (given and family) should be shown in one column called 'Member Name' and the address details (street, city, state and postcode) in one column called 'Member Address' (2 marks)
(ii) Display the full details for all members who live in a particular postcode selected by the user at query execution time (this may be achieved using a substitution variable for the postcode). You should display the data in member number order. A substitution variable may be used in SQL Developer to prompt the user for a value at query execution time by using a place holder such as &myvalue.
select * from product where prod_no=&myvalue
or
select * from product where prod_name = '&myvalue'
(in the first example prod_no is numeric, in the second prod_name is character). The user entered value is then substituted for the placeholder and the query executed with this value. (3 marks)
(iii) Display the full details for all movies in the movie table, which have the word 'Ring' in their title (the word may be present in any case). (4 marks)
(iv) Display the movie title, release date, running time and rating code for all movies which are currently available in stock and which have a running time less than 90 minutes, order the list such that the longest movie is first. (6 marks)
(v) Display all movie copies which are currently available for rent - display the movie title, the copy id, the movie release date and the rating code. This display should be in copy id order within title order. (6 marks)
(vi) Report the average number of rentals per member to two decimal places. (6 marks)
(vii) Provide a list of all movies which have been returned in the last 30 days. Display the movie title, copy id and the member number of the member who returned the DVD. (6 marks)
(viii) For all movies currently in the system display details about the current stock and the disposed of stock:
• for each movie which has copies currently in stock: display the string 'Currently in Stock', the movie id, movie title, the total purchase cost of the in stock copies, the number of in stock copies and the number of times these in stock copies of this movie have been borrowed, and
• for each movie which has copies which have been disposed of: display the string 'Disposed of Stock', the movie id, movie title, the total cost of the disposed of copies, the number of disposed of copies and the number of times these disposed of copies of this movie have been borrowed.
The results from this listing should be displayed in a single result output. (8 marks)
(ix) For all genres in the genre table, list the number of movie titles in each genre classification currently in the Movie table, show the genre id, genre classification and the count of titles in this classification. (8 marks)
(x) A list of all movies which are overdue showing the member name details, the members phone number, the movie title, copy id and the number of days late the movie currently is. This display should be ordered with the most overdue movie at the top of the list. (8 marks)
(xi) For a given member, identified by a member number and selected by the user, display the full details of all rentals that this member has completed (rental date, return date, days late, copy id and movie title). If a movie has not been returned at this point in time it should be listed in the days late column as 'Not Returned'. (8 marks)
(xii) The management would like to reward their most active customers by asking them to special limited screenings. To assist the company in being able to identify these active customers create a view of the member details (member number, member name as one attribute, phone contact and total number of rentals) of those members with a total number of rentals within 20% (inclusive) of the members(s) with the maximum number of rentals. The members should be listed in descending order of the number of rentals they have completed. For example if the maximum number of rentals completed by any customer is 300, this report will show those customers who have a total number of rentals between 240 and 300 inclusive.
After generating the view, show the contents of the view. (10 marks)
Task 2: PL/SQL (25 marks):
This task should only be carried out after you have completed task 1. Testing of your code for this task will make changes to the data in the tables.
These tasks must be carried out in a single script file called FIT5132-a2-task2.sql. The file must include a set echo on statement as the first line and a set echo off statement as the last line. You must ensure that the file has an appropriate header comment containing your name and student id. Each part of the question should be indicated by an appropriate comment (the part number eg -- (i) is sufficient). When the completed script is run you are required to save the output into a file called FIT5132-a2-task2-output.txt
As well as the PL/SQL required to carry out these tasks you must also provide SQL statements which prove that your PL/SQL actually works (note this will require update commands which will change the data).
(i) Write a trigger, which will implement an update cascade referential integrity rule between MOVIE and COPY (10 marks)
(ii) Every 4 completed rentals the store clean and polish DVDs before a customer takes them. Write a trigger, which will ensure that a message (using DBMS_OUTPUT.PUT_LINE) is displayed if a COPY about to be borrowed (ie. rented) requires cleaning. (15 marks)
Submission Requirements:
The following files are required to be submitted as part of assignment 2:
• FIT5132-a2-task1.sql
• FIT5132-a2-task1-output.txt
• FIT5132-a2-task2.sql
• FIT5132-a2-task2-output.txt
The two SQL script files, must be able to be loaded into SQL Developer and produce the output you submitted. If you wish you may also submit a further document called README (as text or MS Word) for any additional comments that you wish your marker to note.
You must submit a single zip file in Moodle named a2- yourauthcateid .zip e.g., a2-xyz123.zip containing the above-mentioned files before the assignment due date.
Late Penalty:
Any submission after the due date will receive a deduction of 5 marks per day, which includes weekends.