RentalAVideo is a movie rental store. It needs a database system to track the rental of movies to its members. RentalAVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Gone with the Wind”. “Gone with 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 RDM is provided below.
RentalAVideo ERD (Figure 1)
- Write the SQL code to create the table structures for the entities shown in Figure 1. The structures should contain the specified attributes. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD.
- The following tables provide a very small portion of the data that will be kept in the database. This data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in problem 1.
Note: For each table, you can write insert statements for 2 rows. If it works, you can copy these data to Excel file. Then, you can import the Excel file to MS Access using the method provided in Access tutorial slide # 10. But please make sure to create primary key for each table and relationships between tables shown in Figure 1 after you import data
MEMBERSHIP
Mem_ Num Mem_ Fname Mem_ Lname Mem_Street Mem_City Mem_ State Mem_Zip Mem_ Balance
102 Tami Dawson 2632 Takli Circle Norene TN 37136 11
103 Curt Knight 4025 Cornell Court Flatgap KY 41219 6
104 Jamal Melendez 788 East 145th Avenue Quebeck TN 38579 0
105 Iva Mcclain 6045 Musket Ball Circle Summit KY 42783 15
106 Miranda Parks 4469 Maxwell Place Germantown TN 38183 0
107 Rosario Elliott 7578 Danner Avenue Columbia TN 38402 5
108 Mattie Guy 4390 Evergreen Street Lily KY 40740 0
109 Clint Ochoa 1711 Elm Street Greeneville TN 37745 10
110 Lewis Rosales 4524 Southwind Circle Counce TN 38326 0
111 Stacy Mann 2789 East Cook Avenue Murfreesboro TN 37132 8
112 Luis Trujillo 7267 Melvin Avenue Heiskell TN 37754 3
113 Minnie Gonzales 6430 Vasili Drive Williston TN 38076 0
RENTAL
Rent_Num Rent_Date Mem_Num
1001 01-MAR-09 103
1002 01-MAR-09 105
1003 02-MAR-09 102
1004 02-MAR-09 110
1005 02-MAR-09 111
1006 02-MAR-09 107
1007 02-MAR-09 104
1008 03-MAR-09 105
1009 03-MAR-09 111
DETAILRENTAL
Rent_Num Vid_Num Detail_Fee Detail_Duedate Detail_Returndate Detail_Dailylatefee
1001 34342 2 04-MAR-09 02-MAR-09 1
1001 61353 2 04-MAR-09 03-MAR-09 1
1002 59237 3.5 04-MAR-09 04-MAR-09 3
1003 54325 3.5 04-MAR-09 09-MAR-09 3
1003 61369 2 06-MAR-09 09-MAR-09 1
1003 61388 0 06-MAR-09 09-MAR-09 1
1004 44392 3.5 05-MAR-09 07-MAR-09 3
1004 34367 3.5 05-MAR-09 07-MAR-09 3
1004 34341 2 07-MAR-09 07-MAR-09 1
1005 34342 2 07-MAR-09 05-MAR-09 1
1005 44397 3.5 05-MAR-09 05-MAR-09 3
1006 34366 3.5 05-MAR-09 04-MAR-09 3
1006 61367 2 07-MAR-09 1
1007 34368 3.5 05-MAR-09 3
1008 34369 3.5 05-MAR-09 05-MAR-09 3
1009 54324 3.5 05-MAR-09 3
1001 34366 3.5 04-MAR-09 02-MAR-09 3
VIDEO
Vid_Num Vid_Indate Movie_Num
54321 18-JUN-08 1234
54324 18-JUN-08 1234
54325 18-JUN-08 1234
34341 22-JAN-07 1235
34342 22-JAN-07 1235
34366 02-MAR-09 1236
34367 02-MAR-09 1236
34368 02-MAR-09 1236
34369 02-MAR-09 1236
44392 21-OCT-08 1237
44397 21-OCT-08 1237
59237 14-FEB-09 1237
61388 25-JAN-07 1239
61353 28-JAN-06 1245
61354 28-JAN-06 1245
61367 30-JUL-08 1246
61369 30-JUL-08 1246
MOVIE
Movie_Num Movie_Name Movie_Year Movie_Cost Movie_Genre Price_Code
1234 The Cesar Family Christmas 2007 39.95 FAMILY 2
1235 Smokey Mountain Wildlife 2004 59.95 ACTION 1
1236 Richard Goodhope 2008 59.95 DRAMA 2
1237 Beatnik Fever 2007 29.95 COMEDY 2
1238 Constant Companion 2008 89.95 DRAMA 2
1239 Where Hope Dies 1998 25.49 DRAMA 3
1245 Time to Burn 2005 45.49 ACTION 1
1246 What He Doesn't Know 2006 58.29 COMEDY 1
PRICE
Price_Code Price_Description Price_Rentfee Price_Dailylatefee
1 Standard 2 1
2 New Release 3.5 3
3 Discount 1.5 1
4 Weekly Special 1 .5
For questions 3– 31, use the tables that were created in Problem 1 and the data that was loaded into those tables in Problem 2.
- Write the SQL command to change the movie year for movie number 1245 to 2006.
- Write the SQL command to change the price code for all Action movies to price code 3.
- Write a single SQL command to increase all price rental fee values by $0.50.
- Write a query to display the movie title, movie year, and movie genre for all movies (result shown in Figure 2).
Figure 2 All Movies
- Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending order (result shown in Figure 3)
Figure 3 Movies by year
- Write a query to display the movie title, movie year, and movie genre for all movies sorted by movie genre in ascending order, then sorted by movie year in descending order within genre (result shown in Figure 4).
Figure 4 Movies with multicolumn sort
- Write a query to display the movie number, movie title, and price code for all movies with a title that starts with the letter “R” (result shown in Figure 5).
Figure 5 Movies starting with R
- Write a query to display the movie title, movie year, and movie cost for all movies that contain the word “hope” anywhere in the title. Sort the results in ascending order by title (result shown in figure 6).
Figure 6 Movies with “Hope” in the title
- Write a query to display the movie title, movie year, and movie genre for all action movies (result shown in Figure 7).
Figure 7 Action movies
- Write a query to display the movie number, movie title, and movie cost for all movies with a cost greater than $40 (result shown in Figure 8).
P8 Movies costing less than $40
- Write a query to display the movie number, movie title, movie cost, and movie genre for movies that are either action or comedy movies or movies that have a cost that is less than $50. Sort the results in ascending order by genre. (Result shown in Figure 9.)
Figure 9 Action or comedy movies costing less than $50 - Write a query to display the movie genre and the number of movies in each genre (result shown in Figure 10).
Figure 10 Number of movies in genre
- Write a query to display the average cost of all of the movies (result shown in Figure 11).
Figure 11 Average movie cost
- Write a query to display the movie genre and average cost of movies in each genre (result shown in Figure 12).
Figure 12 Average movie cost by genre
- Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code (result shown in Figure 13).
Figure 13 Rental fees for movies
Movie_Title Movie_Genre Price_Descriptioin Price_Rentfee
What He Doesn't Know COMEDY Standard 2.5
The Cesar Family Christmas FAMILY New Release 4
Richard Goodhope DRAMA New Release 4
Beatnik Fever COMEDY New Release 4
Constant Companion DRAMA New Release 4
Smokey Mountain Wildlife ACTION Discount 2
Where Hope Dies DRAMA Discount 2
Time to Burn ACTION Discount 2
- Write a query to display the movie genre and average price rental fee for movies in each genre that have a price (result shown in Figure 14).
Figure 14 Average rental fee by genre
Movie_Genre AVERAGE_RENTAL_FEE
FAMILY 4
COMEDY 3.25
ACTION 2
DRAMA 3.33 - Write a query to display the movie title, movie year, and the movie cost divided by the price rental fee for each movie that has a price to determine the number of rentals it will take to break even on the purchase of the movie (result shown in Figure 15).
Figure 15 Breakeven rentals
MOVIE_TITLE MOVIE_YEAR BREAKEVEN_RENTALS
What He Doesn't Know 2006 23.32
Richard Goodhope 2008 14.99
The Cesar Family 2007 9.99
Beatnik Fever 2007 7.49
Constant Companion 2008 22.49
Where Hope Dies 1998 12.75
Time to Burn 2006 22.75
Smokey Mountain Wildlife 2004 29.98 - Write a query to display the movie title and movie year for all movies that have a price code (result shown in Figure v 16).
Figure 16 Movies with a price
MOVIE_TITLE MOVIE_YEAR
Beatnik Fever 2007
Constant Companion 2008
Richard Goodhope 2008
Smokey Mountain Wildlife 2004
Time to Burn 2005
What He Doesn't Know 2006
Where Hope Dies 1998
The Cesar Family Christmas 2007
- Write a query to display the movie title, movie year, and movie cost for all movies that have a cost between $44.99 and $49.99 (result shown in Figure 17).
Figure 17 Movies costs within a range
- Write a query to display the movie title, movie year, price description, and price rental fee for all movies that are in the genres Family, Comedy, or Drama (result shown in Figure 18).
Figure 18 Movies with specific genres
Movie_Title Movie_Year Price_Desciption Price_Rentfee Movie_Genre
The Cesar Family 2007 New Release 4 FAMILY
Richard Goodhope 2008 New Release 4 DRAMA
Beatnik Fever 2007 New Release 4 COMEDY
Constant Companion 2008 New Release 4 DRAMA
Where Hope Dies 1998 Discount 2 DRAMA
What He Doesn't Know 2006 Standard 2.5 COMEDY
- Write a query to display the movie number, movie title, and movie year for all movies that do not have a video (result shown in Figure 19).
Figure 19 Movies without videos
- Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental (result shown in Figure 20).
Figure 20 Balances of memberships with rentals
- Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental (result shown in Figure 21).
Figure 21 Minimum, maximum, and average balances
MINIMUM_BALANCE MAXIMUM_BALANCE AVERAGE_BALANCE
0 15 6.43
- Write a query to display the rental number, rental date, video number, movie title, due date, and return date for all videos that were returned after the due date. Sort the results by rental number and movie title (result shown in Figure 22).
Figure 22 Late video returns
- Write a query to display the rental number, rental date, video number, movie title, due date, return date, detail fee, and number of days past the due date that the video was returned for each video that was returned after the due date. Sort the results by rental number and movie title. (Result shown in Figure 23.)
Figure 23 Number of days late
- Write a query to display the rental number, rental date, movie title, and detail fee for each movie that was returned on or before the due date (result shown in Figure 24).
Figure 24 Actual rental fees charged
- Write a query to display the membership number, last name, and total rental fees earned from that membership (result shown in Figure 25). The total rental fee is the sum of all of the detail fees (without the late fees) from all movies that the membership has rented.
Figure 25 Total rental fees paid by membership
- Write a query to display the movie number, movie genre, average movie cost of movies in that genre, movie cost of that individual movie, and the percentage difference between the average movie cost and the individual movie cost (result shown in Figure 26). Note: the percentage difference is calculated as the cost of the individual movie minus the average cost of movies in that genre, divided by the average cost of movies in that genre multiplied by 100. For example, if the average cost of movies in the “Family” genre is $25, if a given Family movie cost $26, then the calculation would be ((26 – 25) / 25 * 100), which would work out to be 4.00%. This indicates that this movie costs 4% more than the average Family movie.
Figure 26 Movie difference from genre average
Sample Solution