Home > Free Essays > Tech & Engineering > Data > Report for database of maintenance car
6 min
Cite This

Report for database of maintenance car Report

StarStarStarStarStar

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.

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

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.

This report on Report for database of maintenance car was written and submitted by your fellow student. You are free to use it for research and reference purposes in order to write your own paper; however, you must cite it accordingly.
Removal Request
If you are the copyright owner of this paper and no longer wish to have your work published on IvyPanda.
Request the removal

Need a custom Report sample written from scratch by
professional specifically for you?

Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar

certified writers online

Cite This paper

Select a website referencing style:

Reference

IvyPanda. (2019, December 18). Report for database of maintenance car. Retrieved from https://ivypanda.com/essays/report-for-database-of-maintenance-car-report/

Work Cited

"Report for database of maintenance car." IvyPanda, 18 Dec. 2019, ivypanda.com/essays/report-for-database-of-maintenance-car-report/.

1. IvyPanda. "Report for database of maintenance car." December 18, 2019. https://ivypanda.com/essays/report-for-database-of-maintenance-car-report/.


Bibliography


IvyPanda. "Report for database of maintenance car." December 18, 2019. https://ivypanda.com/essays/report-for-database-of-maintenance-car-report/.

References

IvyPanda. 2019. "Report for database of maintenance car." December 18, 2019. https://ivypanda.com/essays/report-for-database-of-maintenance-car-report/.

References

IvyPanda. (2019) 'Report for database of maintenance car'. 18 December.

More related papers
Psst... Stuck with your
assignment? 😱
Hellen
Online
Psst... Stuck with your assignment? 😱
Do you need an essay to be done?
What type of assignment 📝 do you need?
How many pages (words) do you need? Let's see if we can help you!