$1.$ 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

$2.$ Answer the following questions based on the above ER model.

a$.$ Write an SQL query to perform the following tasks.

i$.$ To create the table Customer$-10$

ii$.$ To retrieve the rental history of a given customer $($Customer Id is provided$)-10$

iii. 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$

iv$.$ The amount of money each customer spent on rentals for the past month. $-15$