Databases
Database management systems final project of about rental cars
Dr. Islam Ebeid MCIS 5133 Database Management Systems Final Project
MCIS 5133 Database Management
Systems Final Project
Description
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
2
Dr. Islam Ebeid MCIS 5133 Database Management Systems Final Project
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.
What to submit
1- A Conceptual Level model: An ER diagram describing your entities, relationships,
cardinalities, and attributes.
2- A Logical Level model: A Class diagram describing your entities as classes with
attributes and methods and the relationships between them, including inheritance. For
example, your database might have 2 separate tables: employee and customer. Yet, in
your class diagram, you might have a third class called a person from which both
customer and employee inherit, even though there might not be a table person in your
database. The class diagram will be used to build other applications on top of the
database in the future.
3- A Physical Level model: A Database ER Schema diagram generated using MySQL
Workbench after you create your tables describing the tables, attributes, relationships,
and cardinality that corresponds to your ER diagram. This diagram should include
primary and foreign keys, data types, data sizes, and resolved many-to-many
relationships.
4- A separate description of each table and its attributes and relationships in the ER and
the Database Schema diagrams
3
Dr. Islam Ebeid MCIS 5133 Database Management Systems Final Project
5- A separate description for each class and its relationships in your Class diagram
6- The DDL Create tables statements as a separate .sql file
7- Insert the data provided in the Excel sheet into your newly created database, then give
the insert statements as a separate .sql file
8- Your SQL answers to each of the queries below
9- The results of your SQL queries as images, as some of you have done in the previous
assignment.
Important notes
1- The ER diagram should be at least in the 3rd Normal Form
2- Please use Chen notation for the ER diagram
3- Please use UML notation for the Class diagram
4- Please use UML notation for the Database ER Schema diagram describing your tables
5- You can insert the data in the Excel sheet using import in MySQL Workbench, but you
will have to preprocess the data a bit.
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