Report for database of maintenance car

Report for database of maintenance car 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).Advertising We will write a custom report sample on Report for database of maintenance car specifically for you for only $16.05 $11/page Learn More 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 informatio n 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 adminis trator who will be responsible for entering data into the database and querying the database for information. The main input data isAdvertising Looking for report on it? Let's see if we can help you! Get your first paper with 15% OFF Learn More 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 r eports 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)Advertising We will write a custom report sample on Report for database of maintenance car specifically for you for only $1 6.05 $11/page Learn More 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: 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:Advertising Looking for report on it? Let's see if we can help you! Get your first paper with 15% OFF Learn More 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 works year Worker far e 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 payme nt 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.