Description
- Let’s design an Entity-Relationship (ER) model for a Car Rental System with ten entities, considering various relationships, attributes, and constraints:
This Car Rental System scenario involves entities representing customers, cars, car categories, rentals, employees, branches, insurance, payments, pickup locations, and drop-off locations. The relationships between these entities capture the complexities of a real-world car rental system, considering factors such as customer rentals, car categories, employee management, branch oversight, insurance coverage, and payment transactions.
Customer: Attributes: CustomerID (Primary Key), Name, ContactNumber, Email
Customer may have many rentals.
Car: Attributes: CarID (Primary Key), Model, Manufacturer, Year, RentalRate
One car can be rented many times by many customers in each instance.
Car belongs to a one car category and many cars can be in one car category.
CarCategory: Attributes: CategoryID (Primary Key), CategoryName
CarCategory can have many cars in it. Each rental has a car category.
Rental: Attributes: RentalID (Primary Key), RentalDate, ReturnDate, TotalCost
Customer may have many care rentals. Rental may have one or many cars.
Employee: Attributes: EmployeeID (Primary Key), Name, Position
Employee processes the rental. One employee may process many rentals. One employee manages several branches. Some employees do not manage any branch.
Branch: Attributes: BranchID (Primary Key), Location
One branch can store (park) many cars. In a branch, there can be many employees working for that branch.
Insurance: Attributes: InsuranceID (Primary Key), PolicyNumber, CoverageDetails
Insurance can cover many rentals.
Payment: Attributes: PaymentID (Primary Key), PaymentDate, Amount
Customer may have many payments. One rental can be made in many payments.
PickupLocation: Attributes: PickupLocationID (Primary Key), Location
Many rentals can be picked up from a single PickupLocation
DropoffLocation: Attributes: DropoffLocationID (Primary Key), Location
Many rentals can be returned to a DropoffLocation
- Answer the following questions based on the above ER model.
- Write an SQL query to perform the following tasks.
- To create the table Customer-10
- To retrieve the rental history of a given customer (Customer Id is provided)-10
- Write an SQL query to perform the following tasks.
- To find the employee details with the branch he/she is working at and the rentals that he/she has processed so far. The employee Id is provided.-10
- The amount of money each customer spent on rentals for the past month. -15
- Install and run your queries on MySQL –5 Marks
steps
- Install MySQL server and MySQL Workbench.
- Load the sample dataset from https://www.db-book.com/.
- Run this SQL statement. select * from instructor;
- Submit the result table as a screenshot.
You must state the assumptions that you made during the design process. But you must design all the requirements mentioned above.
Note: Plagiarism is strictly enforced, and identical solutions get ZERO marks and no negotiations.
You Must use a software (Lucid Chart or equivalent) to create ER diagram. Handwritten diagrams will not be graded.
Please contact graders if you have any questions regarding the E-R diagrams, mapping into relations, or normalization.