Introduction
Welcome to our blog post! Today, we're excited to discuss another new project requirement titled "RentalAVideo SQL Database Management Project." In this post, we'll explore the details of this project requirement, outlining what it entails and how we plan to tackle it.
Project Requirement
Description :
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.
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.
This is just a few example of 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 |
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 |
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 |
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 |
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 |
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 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.
Write the INSERT commands necessary to place the provided data into the tables that were created in problem 1.
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.
Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending order.
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.
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”.
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.
Write a query to display the movie title, movie year, and movie genre for all action movies.
Write a query to display the movie number, movie title, and movie cost for all movies with a cost greater 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.
Write a query to display the movie genre and the number of movies in each genre.
Write a query to display the average cost of all of the movies.
Write a query to display the movie genre and average cost of movies in each genre.
Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code.
Write a query to display the movie genre and average price rental fee for movies in each genre that have a price.
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.
Write a query to display the movie title and movie year for all movies that have a price code.
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.
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.
Write a query to display the movie number, movie title, and movie year for all movies that do not have a video.
Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental.
Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental.
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.
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.
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.
Write a query to display the membership number, last name, and total rental fees earned from that membership. 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.
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.
Solution Approach:
In this section, we will delve into the solution approach adopted to complete the project tasks outlined above. We'll discuss the methods, techniques, and strategies employed to design and implement the RentalAVideo SQL Database Management Project.
Database Design:
Analyze the given problem statement and design the appropriate database schema based on the provided entity-relationship diagram (ERD).
Create tables for entities such as MEMBERSHIP, RENTAL, DETAILRENTAL, VIDEO, and MOVIE, ensuring to include primary keys, foreign keys, and appropriate data types for each attribute.
Data Population:
Utilize SQL INSERT commands to populate the created tables with the provided sample data.
Verify the integrity of the inserted data to ensure accuracy and completeness.
SQL Queries:
Address each of the 30 questions systematically, focusing on writing efficient SQL queries to retrieve the required information from the database.
Utilize appropriate SQL commands such as SELECT, UPDATE, and JOIN to manipulate and extract data as per the given requirements.
Ensure to consider sorting, filtering, grouping, and aggregation as necessary to fulfill the query criteria.
Testing and Validation:
Execute each SQL query against the database to verify its correctness and to ensure that it produces the expected results.
Output :
In this section, we will showcase Some output screenshots obtained after completing the aforementioned project tasks. These screenshots will demonstrate the successful execution and outcomes of the RentalAVideo SQL Database Management Project.
Q19
Q24
Q25
Q28
Q30
At CodersArts, we specialize in crafting tailored solutions to meet the unique needs of businesses, and our latest endeavor revolves around optimizing the operations of RentalAVideo, a leading movie rental store. Our project centers on developing a robust SQL database management system to streamline rental transactions and enhance customer experiences. Through meticulous database design and implementation, we ensure seamless tracking of movie rentals, member information, and transaction details.
From conceptualization to execution, our team at CodersArts excels at every stage of the project, employing industry best practices and cutting-edge technologies. We meticulously design database schemas, enforce primary key constraints, and establish foreign key relationships to maintain data integrity and facilitate efficient data retrieval. With our expertise in SQL programming, we create and execute complex queries to address diverse business requirements, from inventory management to customer relationship tracking.
Our commitment to excellence extends beyond database development, as we strive to deliver tangible results that drive business growth. Through rigorous testing and validation, we ensure the reliability and scalability of our solutions. By providing actionable insights derived from comprehensive data analysis, we empower RentalAVideo to make informed decisions and stay ahead in the competitive market landscape. Trust CodersArts to revolutionize your rental business operations with our data-driven solutions and expertise in SQL database management.
If you require any assistance with the project discussed in this blog, or if you find yourself in need of similar support for other projects, please don't hesitate to reach out to us. Our team can be contacted at any time via email at contact@codersarts.com.