Database Design and Implementation for a Vehicle Service Business - Desklib

COVER PAGE

Contents
Case study...................................................................................................................................................3
Conceptual design.......................................................................................................................................4
EER Diagram............................................................................................................................................4
Enterprise rules.......................................................................................................................................4
Logical database design and oracle SQL implementation/querying........................................................4
Logical database design...........................................................................................................................4
Creating tables using oracle DBMS..........................................................................................................7
Create indexes.......................................................................................................................................12
Data population.....................................................................................................................................12
Query Writing........................................................................................................................................19

Case study
The proposed case study is for a vehicle service business. The vehicle service business is in need of a
database system that will help it to shift from the manual methods that are currently in use by the
business. The normal business process of the business involves customers making calls to make
appointments for booking vehicle repair services. The business provides different types of services and a
customer can book an appointment for one or more services. On booking for a service, the business
maintains customer records to make it easier to identify new and existing customers. Customer records
are kept in a spreadsheet located in the business’s only computer. The appointments made by the
customers are recorded in post cards. The business also maintain staff records of all the staff members
working at the business premises. For each staff, a set of qualifications is recorded. The qualifications
include the services that the staff can perform. Each service booked for in an appointment is taken care
of by a number of staff who are qualified to perform that service on the vehicle. After the service is
done, the staff can write some notes concerning the service and the status of the service is updated to
complete. After all services booked for a specific appointment are completed, the customer can proceed
to make the payment using any of the preferred payment methods accepted by the business. The car is
then released to the customer and the release date is recorded. The payment done for an appointment
is supposed to be paid in full installments and the customer is not allowed to have a balance before the
vehicle is released.
The business is finding it hard to manage all this information using spreadsheets and documents since
it’s very tedious to track and manage the data for report generation. For this reason, the business
believes that a well-designed database system can be a solution to all their problems and will help them
run the business more efficiently.
Assumptions
The case study is specified based on the following assumptions;
 A customer makes appointments for services that are offered at the business. The customer
cannot make an appointment for a service not offered by the business.
 The customer has to drop their car after making an appointment and pick their car at the
business premises after the car has been fully serviced.
 The customer cannot pick their car if they have not paid total money accrued as a result of
servicing their vehicle. The payment should be a one of payment and should only use one
payment method accepted by the business.

Conceptual design
EER Diagram
Enterprise rules
The EER Diagram models the following enterprise rules;
 A car owner can own one or more cars. A car can only be owned by one and only one car owner.
 A car can be scheduled for one more appointments.
 An appointment is made for one or more services. Each of the services making an appointment
must be offered by the business.
 A staff has one or more qualifications. A qualification by a staff is for one and only one service.
 An appointment is dealt with by one or more staff.
 An appointment results to one and only one payment.
 A payment can only be done using one and only one payment method accepted by the business.
Logical database design and oracle SQL implementation/querying
Logical database design
The following tables derived from the EER Diagram will make up the database.
CAR_OWNERS
Column Name Key Null/Not Null Type Description

OwnerID Primary key Not null Integer Unique primary key that will be used to
identify each car owner
firstName Not null Varchar2(50) First name of the car owner
lastName Not null Varchar2(50) Last name of the car owner
phoneNO Not null Varchar2(25) The phone number of the car owner
email Not null Varchar2(100) The email of the car owner through which
the business can reach him through
STAFF
Column Name Key Null/Not Null Type Description
staffID Primary key Not null Integer Unique primary key that will be used to
identify each staff
firstName Not null Varchar2(50) First name of the staff
lastName Not null Varchar2(50) Last name of the staff
address Not null Varchar2(25) The address of the staff. It should include
the street and the posal code
email Not null Varchar2(100) The email of the taff through which the
business can reach him through
phoneNO Not null Varchar2(25) Phone number of the staff
STAFF_QUALIFICATIONS
Column Name Key Null/Not Null Type Description
StaffID Primary key
Foreign key
references
Staff
(StaffID)
Not null Integer Part of the composite primary key used to
identify a staff qualification. It’s also a
foreign key referencing a certain staff
serviceID Primary key
Foreign key
references
service
(serviceID
Not null integer Part of the composite primary key used to
identify a staff qualification. It’s also a
foreign key referencing a certain service
SERVICE
Column Name Key Null/Not Null Type Description
serviceID Primary key Not null Integer Unique primary key that will be used to
identify each service offered by the
business
Type Not null Varchar(50) The type of the service
Name Not null Varchar(50) Name used to identify the service
Price Not null decimal The price charged to conduct the service
CAR

Column Name Key Null/Not Null Type Description
carID Primary key Not null Integer Unique primary key that will be used
to identify each car
regNO Not null Varchar(50) The official registration number of the
car
model Not null Varchar(50) The model of the car for example
Mercedes Benz
type Not null Varchar(50) The type of the car for example sedan
ownerID Foreign key
references
car_owner(ownerID
)
Not null integer The owner of the car. This is the
foreign key that references the
unique ownerID who owns the car
APPOINTMENT
Column Name Key Null/Not Null Type Description
AppointmentID Primary key Not null Integer Unique primary key that will be used to
identify each appointment
carID Foreign key
referencing
car (carID)
Not null Integer Foreign key referencing the specific car for
which the appointment is meant for
appointmentDate Not null Date The date the car is booked for service
releaseDate null Date The date the car is released and picked by
the customer
APPOINTMENT_SERVICES
Column Name Key Null/Not Null Type Description
AppointmentID Primary key
Foreign key
referencing
appointment
(appointmentID)
Not null Integer Part of the composite primary key used
to identify a specific appointment
service. Its also the foreign key
referencing the specific appointment
serviceID Primary key
Foreign key
referencing
Service
(serviceID)
Not null Integer Part of the composite primary key used
to identify a specific appointment
service. This is also a foreign key
referencing the specific service
notes Varchar2(500) Notes taken by the staff regarding the
service of the vehicle for the specific
service
status Not null Varchar2(15) The status of the job. It can either be
complete or pending.
APPOINTMENT_STAFF
Column Name Key Null/Not Null Type Description