Car Maintenance Company Relational Database System Report

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

1. Employee details
2. Customer details
3. Car details
4. Payment details

The main processes include:

1. Tracking employee details
2. Tracking customer details
3. Tracking car details
4. Determining the amount of work done in a given period
5. Tracking payment details
6. Tracking the earnings of the company
7. Computing the company’s earnings for a given period
8. Report generation

The main output and outcomes are:

1. Reports on the amount of work done in a given period and the subsequent earnings accrued
2. Reports on the amount of work to be done and its value.
3. Customized reports that serve as invoices for customers
4. Customized reports that serve as receipts for customers
5. Contact lists for workers
6. 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.

Relationships and business rules

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

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

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. Payment method can be either cash or credit.
7. Contact numbers should be telephone numbers only.
8. A customer can bring in as many cars as he or she wishes to be serviced.
9. Multiple workers can work on a single car.
10. A single worker can work on multiple cars.
11. Cars dropped in the company for servicing cannot be picked on the same date they were brought in.

Data dictionary

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.