IMAT5103 Database Systems and Design: EER Modeling and SQL

This assignment presents a comprehensive case study for designing a database system for a vehicle service business, aiming to replace manual data management with an efficient database solution. The document includes a detailed analysis of the business processes, assumptions, and enterprise rules. It features an Entity-Relationship Diagram (EER) illustrating the database structure and relationships between entities such as car owners, staff, services, appointments, and payments. The core of the assignment involves logical database design, with table definitions using Oracle DBMS, including primary and foreign keys, and data types. The solution also covers creating indexes to optimize query performance and data population. Furthermore, the assignment showcases SQL query writing for various data retrieval and manipulation tasks, providing a practical implementation of database concepts. This assignment serves as a valuable resource for students studying database systems and design, offering a complete solution with practical examples and detailed explanations.

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

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
StaffID Primary key
Foreign key
referencing staff
(staffID)
Not null Integer Part of the composite primary key used
to identify a specific staff. It’s also a
foreign key referencing the specific staff
working on the appointment
PAYMENT
Column Name Key Null/Not Null Type Description
PaymentID Primary key Not null Integer Unique primary key that will be used to
identify each payment
appointmentID Foreign key
referencing
Appointment
(AppointmentID)
Not null Integer Foreign key referencing the specific
appointment for which the payment is
being made for
PaymentDate Not null Date The date the payment is made
amount Not null Decimal The total amount paid
methodID Foreign key
referencing
Payment_Method
(MethodID)
Not null Integer Foreign key used to reference the
method used to make payment
PAYMENT_METHOD
Column Name Key Null/Not Null Type Description
MethodID Primary key Not null Integer Unique primary key that will be used to
identify each payment method
name Not null Varchar2(50) The name of the payment method for
example cash.
Creating tables using oracle DBMS
create table car_owners(
ownerID integer primary key,
firstName varchar2(50) not null,
lastName varchar2(50) not null,
phoneNo varchar2(25) not null,
email varchar2(100) not null

);
create table staff(
staffID integer primary key,
firstName varchar2(50) not null,
lastName varchar2(50) not null,
address varchar2(100) not null,
email varchar2(100) not null,
phoneNo varchar2(25) not null
);
create table service (
serviceID integer primary key,
type varchar2(50) not null,
name varchar2(50) not null,
price decimal not null
);

create table staff_qualifications(
staffID integer not null,
serviceID integer not null,
primary key (staffID,serviceID),
Foreign key (staffID) references staff (staffID),
Foreign key (serviceID) references service (serviceID)
);
create table car (
carID integer primary key,
regNO varchar2(50) not null,
model varchar2(50) not null,
type varchar2(50) not null,
ownerID integer not null,
foreign key (ownerID) references car_owners (ownerID)
);
create table appointment (

appointmentID integer primary key,
carID integer not null,
appointmentDate date not null,
releaseDate date null,
foreign key (carID) references Car (carID)
);
create table appointment_services(
appointmentID integer not null,
serviceID integer not null,
notes varchar2(500) null,
status varchar2(15) not null,
primary key (appointmentID,serviceID),
Foreign key (appointmentID) references appointment (appointmentID),
Foreign key (serviceID) references service (serviceID)
);
create table appointment_staff(
appointmentID integer not null,
staffID integer not null,
primary key (appointmentID,staffID),

Foreign key (staffID) references staff (staffID),
Foreign key (appointmentID) references appointment (appointmentID)
);
create table payment_method(
methodID integer primary key,
name varchar2(50) not null
);
create table payment (
paymentID integer primary key,
appointmentID integer not null,
paymentDate date not null,
amount decimal not null,
methodID integer not null,
foreign key (methodID) references payment_method (methodID)
);

Create indexes
The following are the most important indexes to the database based on the type of queries to be
written;
 Payment_method (name) index
create index payment_method_index on payment_method(name);
This index is created for the payment_method table for the name column. This index is
important to the database because it will be helpful in determining the most preferred payment
method by customers.
 Appointment_services (status)
create index appointment_services_index on
appointment_services(status);
This index is created for the appointment_services table specifically for the status column. It will
be helpful for the database especially in writing a query to determine which appointments are
completed and which ones are still pending.
 Appointment(appointmentDate)
create index appointments_index on appointment(appointmentDate);
This index is created for the appointment table for the appointmentDate column. This index will
be useful especially for the queries that is needed to sort the appointments by the date the
appointment was made and this can also be used to limit the result of the appointments based
on the appointment date.
 Service(type)
create index service_index on service(type);
This index is created for the service table on the type column. This index will be useful to the
database when writing a query to get the service type that has the most bookings thus this can
be useful in knowing which service type generates the most money for the business.
Data population
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- CAR_OWNERS TABLE
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
insert into car_owners (ownerID, firstname, lastname, phoneno, email)
values (1 , 'Peter' , 'McKnight' , '[phone]' , '[e-mail]');
insert into car_owners (ownerID, firstname, lastname, phoneno, email)
values (2 , 'Lucy','Clarkson', '[phone]','[e-mail]');
insert into car_owners (ownerID, firstname, lastname, phoneno, email)
values (3,'Lee','Deakins','[phone]','[e-mail]');