Database Management Systems Final Project of About Rental Cars
Before Netflix existed, there were stores where you rented a movie in either a video cassette (VHS) format or a compact disc format (DVD). But then, in 1997, Netflix was founded by Reed Hastings and Marc Randolph as a DVD-by-mail rental service. The company initially offered a subscription service where users could rent DVDs and receive them by mail. Netflix distinguished itself from traditional video rental stores by eliminating the need for customers to leave their homes to rent movies.
Imagine you are back in the nineties and the early 2000s; imagine that VideoFlick is a video rental store that rents out movies in VHS tapes format. You are given a dataset of video rental records kept by several employees. After each employee rents out a movie/movie to a customer, they enter the transaction information into an Excel sheet. Even though VideoFlick is a one- branch store, they are trying to expand by opening more stores. The company’s leadership realized that keeping records using an Excel sheet is not an efficient way of storing information. So, they decided to hire programmers to design and build an information system that would help employees enter the information for each transaction quickly and help in business decision-making by applying data mining algorithms to the database. The programmers decided first to build a relational SQL database that would allow the creation of a graphical user interface and a data analysis application.
The programming team was given the Excel sheet attached to this document as an example of how employees used to keep records. Each movie has a number as a unique identifier. Also, each movie has multiple copies with copy numbers. The combination of movie number and copy number creates a unique identifier for each VHS tape in the store. Each customer might rent multiple movies, and they would all be counted as one transaction. The rental fee for each movie rented is per day. If the return date is the same as the rental date, this is considered one day. If it is the next day, this is regarded as two days. The customer must pay the fee when they return the movie or movies they rented. The transaction is closed only after the customer has returned the movie. If the movie were returned late, an additional fee would be applied. Each transaction has a unique identifier. Also, each transaction stores employee information, customer information, and rented movies information.
Your task
As one of the programming team, you are tasked with:
1- Studying the dataset and exploring it.
2- Designing a relational data model that would replace the Excel sheet that the employees used before to record information.
3- Implementing the ER model you created using the MySQL database management system and MySQL Workbench.
4- Answering the queries provided below using SQL select and join statements.
Queries
1- Find all the movies with the word “earth” in their titles.
2- Find all transactions that happened on April 18th, 2011
3- Find all transactions that happened after April 18th, 2011
4- Find how many transactions each movie has in the database.
5- For each customer, find out how much they spent.
6- Find how many transactions each employee has in the database.
7- Find the customer with the most rented movies.
8- What is the employee job description with the most employees?
9- Who is the newest employee?
10- What is the most profitable movie/movies in the store?
https://www.studypool.com/questions/download?id=2816613&path=uploads/questions/5828273/20230420203924attachment_2__5_.xlsx&fileDownloadName=attachment_1
