Database Design for Automotive Service Business

This assignment focuses on designing a relational database for an automotive service business. It defines various entities like Employees, Consumables, Service History, Suppliers, Order Details, and Invoices with their attributes and primary/foreign keys. The assignment also outlines use cases, activity diagrams, system sequence diagrams, and CRUD analysis to demonstrate how the database supports different functionalities within the business.

System Analysis 1
INFS 1021: Systems Analysis (SA)
Submitted By
Course
Professor
Date

System Analysis 2
Introduction
AAA Mechanics Ltd. Wants to develop the system in order to automate all processes of the
organization. The framework is to enable the organization to record customer data – their details
like name, address, contact details etc. and vehicle details - make, model, color, registration, and
service history - service dates and odometer readings, details of the services performed
separated into labor tasks and parts used, the mechanics involved. The framework should
likewise keep records of the mechanics utilized by the organization – their own subtle elements
such as their qualifications and special licenses, time sheet information, emergency contact
details, supervisor details. AAA Mechanics Ltd needs to deal with a stock of consumables that
the mechanics use for servicing. The purchase officer for AAA Mechanics Ltd should have the
capacity to check stock levels whenever and order extra stock.
1. System Requirement
Stakeholders – The main stakeholders for AAA mechanics include:
Customer, mechanics, suppliers, finance manager, marketing manager, IT administrator
Success stories
1. Customer – “As a Customer, I want to service my car, so that I can drive car without any
problem.”
The system is useful for the customers as they can get accurate information at any time. Also
they can get aware of whether their car service is due or not. When the customer wants to rent a
car from AAA mechanics they may have accurate information regarding which model the
organization own.

System Analysis 3
2. Mechanics – “As a mechanic, I can get accurate inventory of the consumable stock from the
system, so that I can work without waiting for any one on the duty.”
As we know that, the system will save all the inventory of the consumables. Therefore,
mechanics can get entire detail about the consumable. Also, it will save all the details of
customers and it can help mechanics to get the customer details.
3. Suppliers – “As a supplier, system will help me to get the new order for consumables and
payments which I will get from AAA Mechanics will be saved in the system.”
As system will store all the consumable details, the supplier will get order for new consumables
at right time and also there will be no doubt for the payments. All the details will be saved in the
system.
4. Finance Manager – “As a finance manager, I will get all the update regarding payments,
customers and suppliers which will help me to process all the accounts including payroll for
employees.”
The finance managers are main stakeholders as the system is extremely beneficial for them. The
system will help to generate all accounts and ledger per month. The employees can get salary at
right time without any error.
5 Marketing Manager – “As a marketing manager, I will get all the information of customers and
report of their vehicle service date, so that I can email or call them for the next due service.”
The marketing manager can get all the contact details of the customer in order to inform them
and also the marketing manager can get all the details regarding vehicle purchase, service dates
so that they can be informed at right time. (https://www.stakeholdermap.com, 2017).

System Analysis 4
Functional Requirements
Functional necessities are articulations of the capacities that a framework must have
("capacities"), for addressing the business needs that a framework must fulfill. Business needs
are mission-oriented goals of the association for which the framework is developed. (Mitretek
Systems, Inc., 2002).
We can get functional requirement by taking interview from the staff like mechanics, finance,
admin, and marketing manager. The interview questions are as following:
 What is the overall process of AAA Mechanics?
 How the customer details helps in processing?
 How inventory of AAA Mechanics is managed?
 How marketing manager knows that stock needs to be ordered from supplier?
 How finance manager get aware about invoices that are paid to supplier and how he then
processes the accounts?
The main functional requirements for the system are as following:
 The customer details will be saved in the system.
 The system will be able to maintain all the inventory of consumables.
 The system will be able to detail about the vehicle whose service is due.
 It must save all the details about suppliers.
All the information regarding invoice which is given to supplier must be saved in the system and
finance manager can access the system so that monthly ledger can be prepared. Also finance
team must be able to enter all the details of leaves of employees so that payroll can be processed
at each month end.

System Analysis 5
Non- Functional Requirements
Non-functional requirements are technical requirements. It incorporates different requirements
and how the framework will carry on. The transaction speed of information framework ought to
be quick with the goal that it can rapidly process the user requirement. The framework ought to
be easy to utilize. It should be secure so as to maintain a strategic distance from unauthorized
access. Every one of the reports ought to be viewed in just single click.
Actors who will use the system
The main actors which will be responsible to use the framework are:
Mechanics, finance manager, marketing manager, IT administrator and front office staff.
Use case
User Login the system User will enter user name and password for logging into the
system. If user name is valid and matches with password, user
get prompted to next screen.
Customer Details Add Screen Admin get prompted for customer detail. When new customer
arrives AAA Mechanics, all the details are entered into the
system and saved.
Vehicle Information All the details of vehicle are entered into the system
All the jobs done and consumable
used are entered into the system.
All the mechanics jobs which are done on the vehicle and
which part is exchanged is entered into the system
New stock get entered Purchase manager enter all the details of stock that arrives at
AAA Mechanics
Invoice are updated into the system Finance manager is able to view all the details of invoice in
order to process monthly ledgers.

System Analysis 6
Domain Modelling
Domain Classes
1. Customer  CustomerID, Name, Address, contact_number, email.
Primary key  CustomerID
2. Vehicle  VehicleID, Name, make, model, color, registration,
Primary key  VehicleID
3. Employee  EmployeeID, name, address, qualification, experience, designation, timesheet,
emergency_contact, supervisor, license
Primary key  EmployeeID

System Analysis 7
4. Consumables  ConsumableID, name, qty, price
Primary key  ConsumableID
5. service_history  ServiceID, service_date, odometer_reading, service_detail, labor_task,
ConsumableID, EmployeeID, customerid, vehicleid
Primary key  ServiceID
Foreign key  EmployeeID references Employee, ConsumableID references consumables,
customerid references customer, vehicleid references vehicle
6. Supplier  SupplierID, sup_name, sup_address, sup_phone
Primary key  SupplierID
7. Order_details  OrderID, consumableid, supplierid, qty, order_date
Primary key  OrderID
Foreign key  ConsumableID references consumables, SupplierID references supplier
8. Invoice  InvoiceID, customerid, orderid, invoice_date, order_value, recipt_value
Primary key  invoiceid
Foreign key  customerid refernces customer, ordered references order_details.

System Analysis 8
A customer can have more than one vehicle and a vehicle can have more than one service.
Number of items can be used in service. An order can have more than one item and a item can
be ordered more times.
Use Case
In the use case of Book service, marketing manager calls customer whose vehicle service is due.
When the marketing manager calls the customer after their purchase of vehicle, the marketing

System Analysis 9
manager enter all the details of customer as a new customer. When the customer confirms the
service date to marketing manager, he enters all the details about the problem of vehicle and date
when the customer wants to come for service.
Activity Diagram
System Sequence Diagram for Use Case

System Analysis 10
CRUD Analysis
Entity Admin Mechanics Marketing
Manager
Finance
manager
Purchase manager
Add customer U, C, D R C, U R R
Add Item R R, C, D R R C, R, U, D
Add Employee R, C, D
Service details R, C, D R R
Order details R, U C, R, D
Invoice Details C, R, D U, D

System Analysis 11
References
Stakeholder Analysis, Project Management, templates and advice. Retrieved from -
https://www.stakeholdermap.com/stakeholder-analysis.htmlhttps://www.stakeholdermap.
com/stakeholder-analysis.html
Developing Functional Requirements for ITS Projects. Mitretek Systems, Inc. (2002). Retrieved
from - https://ntl.bts.gov/lib/jpodocs/repts_te/13621.html