Part I. Description of the model
A car rental company has several outlets (i.e., locations) with cars/vehicles that are rented for various
periods of time. For each vehicle, we record its license plate number, make, model, year, last
inspection date, the daily rate, etc. For the clients who rent vehicles/cars only basic information is
ecorded.
Each outlet has several employees working at the outlet (e.g., sales reps, mechanics, and administrative
assistants). The information stored about the employees includes name, gender, date of birth, contact
information, hire date, etc. Each employee is working at only one outlet. A few of the employees may
supervise other employees (e.g., one sales rep would supervise other sales reps). For each supervised
employee, we need to identify which other employee supervises them. Some employees may manage up
to 3 outlets. Each outlet has only one manager responsible for the operation of the outlet.
For each rental agreement! (agreement with a client who rents a vehicle) we need to record the rental
date, rental time, and the number of miles driven before a rental agreement is signed. When the rented
vehicle is returned the date and time as well as the mileage (i.e., odometer reading after the vehicle is
eturned) is recorded. The rental agreement must also include the insurance type used.
When a rented vehicle is returned, it is checked by one of the employees (typically a mechanic on duty at
the time) for any problems. When it is needed, the employee prepares a report describing the problems
found with the vehicle. The fault report is created only when there are issues/problems with the returned
vehicle. The fault report is very simple and includes only date, time, and comments (describing and
evaluating the issues/problems). Once a vehicle has accumulated 5 fault reports, that vehicle is put out
of service and is no longer available for rentals until a complete check is done (and all issues are
esolved). We also need to link each fault report to individual rental agreement, in case a follow up is
needed. The ERD and schema for the model are shown below.
! The entity name is shortened to “RAGREEMENT? in the ERD and schema.
ER Diagram:
1S
associated
with
CLIENT RAGREEMENT
(1,1) (1,N)
(1,1)
Schema:
EMPLOYEE (EmpNo, FName, LName, Position, Phone, Email, DOB, Gender, Salary, HireDate,
OutNo@, Super No®)
FAULTREPORT (ReportNum, DateChecked, Comments, EmpNo@, LicenseNo@, RentalNo@) 2
OUTLET (outNo, Street, City, State, ZipCode, Phone, ManagerNo®)
VEHICLE (LicenseNo, Make, Model, Color, Year, NoDoors, Capacity, DailyRate, InspectionDate,
outNo@)
CLIENT (clientNo, FName, LName, Phone, Email, Street, City, State, ZipCode)
RAGREEMENT (RentalNo, StartDate, ReturnDate, MileageBefore, MileageAfter, InsuranceType,
ClientNo@, LicenseNo@) ?
2 DateChecked attribute is of “Date” datatype and includes both the date and the time
3 Both StartDate & ReturnDate attribute are of “Date” datatype and include both the date and the time.
2
Part II.
tables, data integrity constraints,
for creating all necessary
abase as defined above.
les to implement the dat
Lave the necessary integrity constraints including
he script with a set of “Drop Table” statements, that will allow
ting it (very useful when you recreate the database using the
ou to clean up the database before cred tel
: : as the names of attributes exactly as provided in the conceptual
forced within the “Create Table”
script). Use the names of tables as well 2 ns
model above. All constraints, except ONE, must be created ana en :
vention discussed in class (lecture 1 2). Run the script to
t). You
statements and must be named using the con i : i
create the DB tables and to create the missing constraint (as the last statement in your SCrip
CREATE TABLE statements should also include the following:
a. All date attributes must have a default value that is the cu
ent date.
. All email addresses must conform to a validation rule )
i. For employees of the rental company, ensure that the domain name is used properly
ii. For client, email addresses conform to a simpler rule, and J
c. Three more appropriate and useful check constraints of your choice (but not the “Not Null o
“Unique” constraints). Make sure that you clearly identify each of the three constraints.
A. Create two separate SQL scripts
and for inserting data into the ta
1. Script #1: Write all CREATE TABLE statements that |
primary keys, foreign keys, etc. Start t
aints are created, insert about 10-15 rows of
de data that would allow you to test all queries as defined in section B below
(each query must give you results). It may be required to add more than 15 rows in some tables in orde
to show that your queries work as intended. Outlet table can have just a few rows. Run the script #2 to
perform the task of populating the database tables. (Note: All constraints must be enabled and enforced
— not defe
ed — before you populate your tables.)
2. Script #2: After the DB tables and all data integrity const
data into each table. Provi
3. After all tables are populated, list the complete content of each table. Format each table’s contents to
make sure it is readable.
B. Create SQL queries to answer the following questions
1. For each rental agreement started in previous month, the company needs a detailed report that includes
start date and time, return date and time, vehicle information
the following information: rental number,
(license number, outlet number, make, model, and year). If the rental required a fault report, also include
the date when the vehicle was checked.
the third quarter of cu
ent year? Create a “pivot query” that
lists number of rentals started in each outlet and each month of the third quarter. The last column should
provide the total number of rentals started in the outlet. As the last row, provide total number of rentals
started in each month of the 3rd quarter. Include zero in each cell where we do not have any value.
2. How well did each outlet do each month of
List details of vehicles that are at least 5 years old. Include the vehicle license number, vehicle make,
model, age, miles driven, cu
ent reading of the odometer, and number of fault reports generated.
How do we assess the likelihood of getting a fault report for each of the vehicle’s “make” we have?
Calculate the likelihood for each quarter of this year and for each “make” combination. Rank each make
within each quarter when the vehicles were rented. The higher the chance of getting a fault report, the
higher the rank. Also, include the number of rentals of each make within each quarter of this year.
How much each vehicle model contributes (in percent points) to the total revenue for the 3" quarter of
this year? Rank vehicles from the highest revenue earning percentage to the least. With each model, list
the number of vehicles we have of that model, and the revenue earning percentage.
3
6. List employee ID and name, his/her title, the street address of the outlet where he or she works, and the
number of fault reports prepared by the employee in the past 90 days. For each employee show the “level”
in the “supervising hierarchy” and indent the name accordingly to the level so that the name of a person
who is lower in the hierarchy is indented more than their supervisor.
7. For each outlet, provide number of vehicles at the outlet, number of rentals from the outlet started within
the past 60 days, average distance driven per rental from the outlet started within the past 60 days,
number of employees working at the outlet, and the number of rentals in the past 60 days per employee.
8. Identify customers that produced the greatest revenue this year (provide names of the customers and the
evenue).
9. We want to evaluate vehicle rentals in the context of locations. For each outlet, list the number of
customers (who rented cars from the outlet) who live in the same state as the location of the outlet. Also,
include the outlet number and state where the outlet is located, and the number of rentals by those
customers. In addition, include for each outlet, the proportion of the customers from within the state
compared with all customers in the outlet and the proportion of rentals from those customers compared
with all rentals in the outlet.
10. What types of clients do we have? Based on the “web address” information, group the client by the
type of client (“.edu” indicates an educational institution, “.gov” a government agency, “.org” a non-for-
profit organization, and “.com” a for-profit company) and provide how many clients we have of each
type/category and number of rental we had in the past quarter from each type of client. If the we
address does not match any of the four major types, then use “Other” for the client type. If we do not
have a web address for a client, then the “client type” should be “Not Available”.