Loan Star Auto Rental $($LSAR$),$ located near DFW airport, is in the business of renting and leasing automobiles. They have requested your help in computerizing their car rental and leasing business.

LSAR offers four categories of cars $$ compact, mid$-$size, large, and luxury $$ and two types of SUVs $$ mini and full size. Daily, weekly, and weekend rental rates are available for each vehicle category. LSAR caters to two types of customers $$ individual and corporate. Corporate customers receive special discounts on rentals and are also eligible for leasing vehicles. While individual customers pay using a credit card at the time of rental, corporate customers are assigned account numbers and are billed on a monthly basis.

A vehicle can be reserved up to three months in advance. Individual customers must provide their credit card details to hold their reservation. The rental rate is determined at the time of reservation. A reservation may be canceled at any time prior to the rental date. A no$-$show is treated as a cancellation. The system must keep track of cancellations for planning purposes. A rental contract is generated when a customer checks out a car. The system should keep track of checkout date and time, checkout mileage, return date and time, return mileage, and condition of the car. LSAR offers auto insurance to interested customers at an additional charge at the time of rental. The insurance rate depends on the vehicle category and the customer$$s age group $$ younger drivers $(25$ or less$)$ are charged a higher rate. Credit authorization is obtained for the estimated amount of the bill at the time of rental. An invoice is created when the vehicle is returned, and the customer$$s credit card is billed for the actual amount, which may be different from the estimate made at the time of rental. Due to its proximity to an international airport, a large number of LSAR rental customers$$are walk$-$in customers. Rental and reservation services are handled by customer service representatives. The system must keep track of rental$/$ reservation$/$ return requests processed by each employee.

Leasing is popular among companies, such as software consultants, that maintain a floating employee population in the DFW metroplex. A lease guarantees the customer the availability of a vehicle of a particular category$$during the lease period. Routine maintenance of the vehicle due to normal wear and tear during the lease period is the responsibility of LSAR. A lease, unlike a rental, has a mileage cap. If a vehicle requires maintenance during the lease period, the customer is provided with a replacement vehicle for the remaining lease time. A penalty is charged if the lease is terminated before the contracted period.

The Service Manager is responsible for the maintenance of vehicles owned by LSAR. He keeps track of the maintenance history of each vehicle and schedules maintenance services that are performed at every $3000-$mile interval. Vehicle maintenance service is provided by selected garages in the DFW metroplex. A job is opened for maintenance when a vehicle is released from operations. It records the garage assigned for the maintenance work, the services to be performed, and the dates and times of release and return of the vehicle. On completion of the required maintenance service, the job is closed, and the vehicle is returned back to operations.

$1$: Create a complete conceptual data model using the ER diagram.

Document all the business rules

ER diagram. VISIO or LucidChart clearly indicate the entities, relationships, key constraints, optionality$$s$,$ connectivity$$s$,$ and cardinalities.

$2$: $$Relational Schema and the Database Tables. All relations should be in third normal form. Indicate primary keys, foreign keys, and the constraints. Use the following format: TABLE$\_$NAME $($Primary$\_$Key $($s$),$ Non Key$\_$Fields, Foreign$\_$Key $($s$)$

$3.$ System implementation including designing necessary tables, queries and modifications $($insert$,$ delete & update$),$ views, triggers and procedures on the database. Design a variety of queries, involving different tables and significantly different conditions in your domain. Add additional features or capabilities such as views, triggers, stored procedures etc. The database you design should be sizeable enough to contain eight or more tables with at least one many to many relationships in the database system. You should also have at least ten to fifteen queries, two to three views, triggers, stored procedures and functions.

Create SQL scripts

All the code should be in one file.