Car Maintenance Company Relational Database System

NB: We do not resell your papers. Upon ordering, we do an original paper exclusively for you.

NB: All your data is kept safe from the public.

Table of Contents

Executive Summary
Introduction
Input, process and output
Entity-relationship diagram
Relationships and business rules
Current Implications and Future Expansion to Database

Executive Summary

This report is on a proposed relational database system for a car maintenance company. The proposed relational database system is designed to track employee, customer, car and payment records of the company. The system is to be built using Microsoft Access relational database management system (RDBMS).

Therefore, the report highlights the need for a database system and includes the proposed design for a Microsoft Access database, which comprises organization processes, details on data inputs and information outputs, an Entity Relationship Diagram, a Data Dictionary, and possible report designs.

Introduction

As mentioned in the executive summary the relational database system is to be built using Microsoft Access. Relational database management systems (RDBMS) like Microsoft Access make information management for low and high volumes of data easy, safe and less costly compared to traditional book-keeping methods that are characterized by a lot of manual involvement and that take up a significant portion of office space.

Therefore, functional requirements (benefits) presented by the relational database system to the car maintenance company are that information management is going to be easy, the information will be safe and in effect the company is going to enjoy a reduced cost of operation.

The relational database for the car maintenance company comprises of four main tables namely Worker, Customer, Car and Payment. The Worker table tracks the company’s employee details, the Customers table tracks the company’s customer details, the Car table tracks the details of the cars serviced by the company and the Payment table tracks the payments made by customers to the company.

Input, process and output

The relational database is to be maintained by a database administrator who will be responsible for entering data into the database and querying the database for information.

The main input data is

Employee details
Customer details
Car details
Payment details

The main processes include:

Tracking employee details
Tracking customer details
Tracking car details
Determining the amount of work done in a given period
Tracking payment details
Tracking the earnings of the company
Computing the company’s earnings for a given period
Report generation

The main output and outcomes are:

Reports on the amount of work done in a given period and the subsequent earnings accrued
Reports on the amount of work to be done and its value.
Customized reports that serve as invoices for customers
Customized reports that serve as receipts for customers
Contact lists for workers
Contact list for customers

The first and second reports are generated from a query that extracts its data from the Car and Payment table. The third and fourth reports are based on a query that extracts data from the Customer, Car and Payment tables. The fifth report is based on a query extracting its data from the Worker table and the sixth report is based on a query extracting its data from the Customer table.

Entity-relationship diagram

The relational database for the car maintenance company comprises of four main entities, namely, Worker, Customer, Car and Payment. The following is the schema of the relational database:

Worker (Worker ID, Worker name, Contact NO, Expertness, Worker fare, Qualification)

Customer (Customer ID, FirstName, LastName, Street Address, Suburb, Post Code, Contact NO)

Car (Plate number, Make, Model, Colour, Year, Check-in time, Check-in date, Check-out time, Check-out date)

Payment (Invoice Number, Customer Name, payment Method, amount, Time of payment, Date of Payment)

Car_Worker ( Record ID, Plate Number, Worker ID)

The relation Car_Worker is a junction-table, which breaks down the many to many-to-many relationship that exists between the car and Worker relations.

The following is the entity relationship diagram for the proposed relational database system.

the entity relationship diagram for the proposed relational database system

Relationships and business rules

The main relationships between the entities can be shown in the following rules:

One customer can bring in multiple cars to be serviced.
One customer can make multiple payments.
One worker can work on multiple cars
One car can be worked on by multiple workers

These are the main business rules that will influence directly on the database design:

The database administrator is in charge of the whole database. The database administrator is the only one mandated to make changes to the structure of the database and the data therein.
For each worker in the company, the following details should be available: the workers name, contact number, expertness, fare and academic qualification. Additionally, the worker should be assigned an identification that is unique to him or her.
For each customer the following details should be available: the customer’s first name, last name, street address, Suburb, postal code and contact number. Additionally, the customer should be assigned an identification that is unique to him or her.
For each car being serviced in the company, the following details should be available: The car’s plate number, make, model, colour, year it was manufactured, and time and date it was brought in and collected. By default, no two cars can have the same number plate.
For each payment made by a customer to the company, the following details should be supplied: an invoice number, name of customer who made the payment, the payment method, amount that was paid, the time and date the payment was made.
Payment method can be either cash or credit.
Contact numbers should be telephone numbers only.
A customer can bring in as many cars as he or she wishes to be serviced.
Multiple workers can work on a single car.
A single worker can work on multiple cars.
Cars dropped in the company for servicing cannot be picked on the same date they were brought in.

Data dictionary

Worker
Attributes	DataType	Format	Rule	Description	Sample Value
Worker ID	\|Number	Auto Numbering	Start from 1 and increment by 1	This is the unique identification for the worker. This the PrimaryKey	1
Worker Name	Text	n/a	Starts with Capital letter	Description for the name	Bedoor fahad
Contact NO	Text	(999) 9999- 9999	n/a	TEL No	(061) 9733- 3222
Expertness s	Text	50 Characters	Letter	NO of work’s year
Worker fare	Currency	$9999.99			$3500.00
Qualification	Text	50 cCharacters	Letter	Study	Bach

Customer
Attributes	DataType	Format	Rule	Description	Sample Value
customerID	\|Number	Auto Numbering	Start from 1 and increment by 1	This is the unique identification for the customer. This the PrimaryKey	1
FirstName	Text	Starts with Capital	Field size maximum of 20 characters	First name of the Customer	Bedoor
LastName	Text	Starts with Capital	Field size maximum of 20 characters	Surname of the customer	Fahad
Gender	Text	Value List – Defaults with	n/a	Gender for the customer	Male
Street Address	Text	n/a	50 Characters	Street Address of customer	1011 Sydney Road
Suburb	Text	Select from a combo list	Able to add new suburb to list	Suburb of customer	Melbourne Coburg
PostCode	Number	4 Numbers	Within in the 3000 range	Postcode of customer	3000
Contact NO Contact NO	Text	(999) 9999- 9999	n/a	TEL No	(061) 9733- 3222

Car
Attributes	DataType	Format	Rule	Description	Sample Value
Plate number	Text	n/a	10 Character	Plate number of car This the PrimaryKey	RRR 343
Make	Text	n/a	10 Characters	Kind of car	Ford
Model	Text	n/a	10 Characters	Kind of car	Falcon
colour	Text	n/a	10 Characters	Colour of car	Black
year	Number	4 number		Year of car	2003
Check-in time	Text	n/a	50 Characters	Time entering of car	05:10 pm
Check- in date	Date	Dd/mm/yyyy	Cannot be current date	This is the date entering of car	14/08/1995
Check-out time	Text	n/a	50 Characters	Time out of car	05:10 am
Check-out date	Date	Dd/mm/yyyy	Cannot be current date	This is the date out of car	14/08/2000

Payment
Attributes	DataType	Format	Rule	Description	Sample Value
Invoice Number	Number	10 number		Number of payment invoice This the PrimaryKey	123456
Customer Name	Text	n/a	Starts with Capital letter	Description for the name	Bedoor fahad
Payment Method	Text	n/a	10 Characters	Payment by credit card or cash	Cach or VISA
amount	Currency	$9999.99			$350.00
Time of payment	Text	n/a	50 Characters	Time of payment	05:10 am
Date of payment	Date	Dd/mm/yyyy	Cannot be current date	This is the date of payment	14/08/1995

Car_Worker
Attributes	Datatype	Format	Rule	Description	Sample value
Record ID	Number	Auto Numbering	Start from 1 and increases by 1	Uniquely identifies each record. This is the primary key	1
Plate number	Text	n/a	10 Characters	Plate number of car This a foreign Key	RRR 343
Worker ID	Number	Auto Numbering	Start from 1 and increases by 1	This is the unique identification for the worker. This a foreign Key	1

Current Implications and Future Expansion to Database

The proposed relational database system will make information management for the company easy. Reports and lists generated from the system capture the information needs of the company at any level of specification desired by the company.

Even with these benefits, the system’s full potential has not yet been realized. The following paragraph captures additional information that can be extracted from the database in its future. This information is useful in the management of the company.

With sufficient data in the system and by running appropriate queries against the database, the company can determine the effectiveness of its employees as well as determine its customers’ loyalty.

Additionally, the system can enable the company rate its services by expanding it to accommodate details relating to customer satisfaction with the job done on his or her car. The system can be expanded to accommodate data on car problems. With this data, the company can determine which problem it encounters frequently enabling it to tell which kind of experts it needs.

Click Here To Order Now!