IndeMovie is a small movie rental company with a single store. IndeMovie needs a database system to track the rental of movies to its members. IndeMovie owns 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.Arental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). Avideo can be rented many times over its lifetime, therefore, there is an M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in figure below. see image.
Create a folder for this assignment and name it as Yourusername_CSIS2300_AS3. For example, in my case, the name of the project would be: Otims_CSIS2300_AS3.All the files you create for this assignment should be placed in this folder
1. The following tables provide sample data that will be kept in IndeMovies database. Corresponding Excel files for these sample data are provided for you.
2. Begin by saving the supplied Excel files in comma separated values (CSV) format. Then to create the database table structure and populate it with the sample data, use the CSV to SQL converter tool used in class exercise (http://www.convertcsv.com/csv-to-sql.htm).
a). Use DDL component of SQL to create the database (name it indemoviedb) and its table structures (refer to the ERD also). The table structures should contain the attributes specified in the ERD. 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.
b). Use DML component of SQL to insert sample data provided into the respective tables of the database.
Note: Steps a) and b) can be done jointly using the CSV to SQL converter tool above. After you have created a SQL script for each table, combine the SQL scripts into one SQL dump file (save the combined script as indemovedb_sql_dump). Thereafter, start PHPMyAdmin and import the SQL dump script to create the database in MySQL DBMS.
Based on the referential integrity constraints, you should be able to identify a correct sequence in which to insert the data into the tables. You should create the parent table first (the one contributing its primary key to another table) before creating the child table (the one receiving primary key from parent table/entity as foreign key) in a relationship.
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-16, 103
1002, 01-MAR-16, 105
1003, 02-MAR-16, 102
1004, 02-MAR-16, 110
1005, 02-MAR-16, 111
1006, 02-MAR-16, 107
1007, 02-MAR-16, 104
1008, 03-MAR-16, 105
1009, 03-MAR-16, 111
DETAILRENTAL
Rent_Num, Vid_Num, Detail_Fee, Detail_DueDate, Detail_ReturnDate, Detail_DailyLateFee
1001, 34342, 2, 04-MAR-16, 02-MAR-16, 1.00
1001, 61353, 2, 04-MAR-16, 03-MAR-16, 1.00
1002, 59237, 3.5, 04-MAR-16, 04-MAR-16, 1.50
1003, 54325, 3.5, 04-MAR-16, 09-MAR-16, 1.50
1003, 61369, 2, 06-MAR-16, 09-MAR-16, 1.00
1003, 61388, 0, 06-MAR-16, 09-MAR-16, 1.00
1004, 44392, 3.5, 05-MAR-16, 07-MAR-16, 1.50
1004, 34367, 3.5, 05-MAR-16, 07-MAR-16, 1.50
1004, 34341, 2, 07-MAR-16, 07-MAR-16, 1.00
1005, 34342, 2, 07-MAR-16, 05-MAR-16, 1.00
1005, 44397, 3.5, 05-MAR-16, 05-MAR-16, 1.50
1006, 34366, 3.5, 05-MAR-16, 04-MAR-16, 1.50
1006, 61367, 2, 07-MAR-16, , 1.00
1007, 34368, 3.5, 05-MAR-16, , 1.50
1008, 34369, 3.5, 05-MAR-16, 05-MAR-16, 1.50
1009, 54324, 3.5, 05-MAR-16, , 1.50
1001, 34366, 3.5, 04-MAR-16, 02-MAR-16, 1.50
VIDEO
Vid_Num, Vid_Indate, Movie_Num
54321, 18-JUN-08, 1234
34342, 22-JAN-07, 1235
54324, 18-JUN-08, 1234
54325, 18-JUN-08, 1234
34341, 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
Please implement the following queries.
1. 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.
2. Write a query to display the movie number, movie title, movie cost, and movie genre for all movies that are either action or comedy movies and that have a cost that is less than $50. Sort the results in ascending order by genre.
3. Write a query to display the movie genre the number of movies, and average cost of movies in each genre.
4. Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code.
5. Write a query to display the movie title, movie year, and the movie cost divided by the price rental fee (stored as Breakeven Rentals) 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.
6. Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental.
7. 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 (stored as Days Past Due) that the video was returned for each video that was returned after the due date. Sort the results by rental number and movie title.
8. . Write a query to display the membership number, last name, and total rental fees earned from that membership (stored as Rental Fee Revenue). 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.