Description
RENTRACK Company, a young Montreal firm, specialized in the rental of trucks with
driver. It has a fleet of a hundred trucks of all types, and currently has a thousand regular
customers, mostly businesses. Its business is growing strongly. As the CEO he decided to
automate gradually the management of the company.
Description of the different activities related to the rental of tracks.
A. MISSIONS
The rentals have a variable duration. The drivers return necessarily to the garage with
their truck at the week end. Each mission cannot exceed 5 days (Monday morning to
Friday evening). So a rental is treated as N missions for a maximum duration of five days
each.
A mission begins when the truck leaves the garage (even if the appointment is set at
another location) and ends when the truck returns to the garage.
The number of missions generally varies from 100 to 800 per week.
The driver goes with the rented truck to the appointment fixed by the customer, and is
available to him for the whole duration of the mission (until Friday evening at the latest).
A driver can drive any vehicle if he has the corresponding driving license: tourism,
heavyweight, super heavyweight.
2
B. RESERVATIONS
Reservations are made by phone with the sales department. A customer can book a truck
several weeks in advance for a period of maximum one year. He may of course modify or
cancel all or part of a reservation, if he does it no later than one week before starting a
mission.
The form used by RENTRACK for reservations is designed to decompose easily a
reservation to missions. Thus the information provided by the client can establish for
each mission a record including:
Number, name or business name and address of the client;
Type of vehicle desired;
The place of rendezvous;
The date and time of appointment;
The expected duration of making disposal of vehicle and driver.
The portfolio of missions may include up to 10,000 missions to realize.
C. PREPARATION OF MISSIONS
The staff of the sales department then performs the following tasks:
It determines the date and time of start and end of missions, from information
provided by the client when booking;
It performs the assignment of drivers and vehicles for each mission. This
assignment is currently done manually, using schedules wall. The procedure,
though heavy, will be kept for some time: the computerization will be the subject
of further study.
This allows it to harmonize booking requests and opportunities for the company’s service,
for each mission.
They plan to computerize the subsequent procedure.
Thus the “mission sheets” definitely established will be daily entered on the terminals in
order to create for each mission a record in the database “Missions”. They seek to
minimize the volume of the seizure.
In week end, they plan to publish “mission sheets” for drivers. These documents define
the missions they will be performed in next week, due to a mission per sheet. They will
also be used for billing of rentals.
We will not deal, in this study, the case of bookings made in midweek for the current
week.
3
D. BILLING
A weekly invoice is sent to customers.
A customer can rent several vehicles simultaneously or successively. In this case, the
invoice includes a line by mission.
Invoices are partly compiled from the “mission sheet” completed by drivers. They enroll
in effect:
Actual dates and hours of start and end of the mission, if they differ from what
was expected;
The value of the vehicle odometer at the start of the garage and return.
The data necessary for billing on the “mission sheet” are daily entered from terminals.
We look again to minimize the volume of the seizure.
The rental cost charged includes:
A proportional fraction to the duration of the mission;
And a proportional part to the browsed kilometers.
These two costs obviously depend on the rented vehicle.
The invoice payment is being made by credit card, by cash or check.
4
MARKING GUIDELINES OF THIS PROJECT
DISTRIBUTION OF MARKS
Activities Marks
Conceptual Diagram 10 pts.
Logical Diagram 10 pts.
Relational Diagram 10 pts.
Normalization of Relation (3NF) into logical diagram 10 pts.
Constraints 10 pts.
SQL Script of the creation of tables 10 pts.
Insertion of data (10 rows minimum for each STRONG table
and 5 minimum for each WEAK table)
10 pts.
Implementation of 9 Queries given below
#1 : 2 pts.
#2 : 2 pts.
#3 : 3 pts.
#4 : 3 pts.
#5 : 4 pts.
#6 : 2 pts.
#7 : 5 pts.
#8 : 3 pts.
#9 : 6 pts.
30 pts.
TOTAL 100%
GUIDELINE FOR PREPARING PROJECT REPORT
Your project report MUST:
Be properly formatted;
Have group name, official names of the team members, and student ID’s
clearly printed on the cover;
Inappropriate submission will be marked zero (0).
The document report MUST contain the following sections:
1. Conceptual Diagram.
2. Logical Diagram of your conceptual diagram provided in the section 1.
3. Relational diagram of your logical diagram provided in the section 2
5
4. Explain in plain English the normalization applied to the logical diagram.
5. Explain in plain English the different constraints used to the attributes of tables
(NOT NULL, CHECK, primary key, foreign key, UNIQUE, etc.)
6. SQL Script of the creation of tables
7. Insertion of data for each table (10 rows minimum for each STRONG table and
5 minimum for each WEAK table)
8. Queries (Simples with different tables and Complex with inner SQL) that have
been implemented to satisfy project requirements as follows :
a. List of customers that are businesses (Enterprises or Companies).
b.List of reservations whose reservation number is greater than 1.
c. List of drivers and vehicles having participated in at least one mission.
d.List of missions between March 11, 2023 and March 18, 2023 as well as
the drivers and vehicles participating in these missions.
e. The list of customers who have not paid their invoices.
f. List of drivers who have driven ‘GMC’ brand vehicles.
g.Which customers have invoices greater than $1000?
h.List of customers with their number of associated invoices.
i. What are the last names and first names of the drivers who have a mission
between the following dates: February 1, 2023 and March 31, 2023 whose
mileage (number of kilometers traveled) is more than 7000 km?
