Database Access Car service is designed to automate the work of a car repair company. The tables in the database are filled with data, simple and cross-reference queries, as well as adding, updating and deleting, are performed. Also made forms for working with data and reports that can be printed.
Access Database Car Dealership contains 6 tables, 9 requests, 7 forms + main button form, 5 reports. This Access database is optimally suited for further optimization and refinement for your own needs.

ATTENTION! There is an explanatory note (21 pages)

Access database Autoservice allows you to add and edit information about current repairs, customers of autoservice, spare parts, storage warehouses and cars being repaired. Also in the Autoservice database there are requests for displaying a certain type of spare parts, calculating the total cost of repairs, including the cost of work and the cost of a spare part, the total amount for each client, etc. Implemented query for updating, query for deleting, query for adding, for creating a table, cross.

The purpose of practical exercises is to acquire skills in domain analysis, database design, and its physical implementation in the Access DBMS.
The result of the work is presented in the form of an Access database, which should contain:
the structure of the designed tables,
data schema with links between tables,
examples of forms that provide a user interface,
queries (in Design mode and in SQL),
reports (in report mode and in Design mode),
main button form.

Table "Cars" - Access Database Car Service

Table "Wizards" - Access Database Car Service

Query "Cost of work" - Access database Car service

Cross Query - Access Database Auto Service

Form "Clients" - Database Access Car Service

Form "Warehouses" - Database Access Car service

Amount with Spare and Job Report - Access Database Car Service

Main Button Form - Access Database Car Service

A ready-made database Access Database Autoservice is available for download at the link below.

. Ready-made Access database "Car Service"

Download the MS Access database (DB); DB Access Car service; sale of cars access; access database; obd access; subd access; access databases; access example; programming access; ready database; creating a database; DBMS database; access coursework; database example; access program; access description; access abstract; access requests; access examples; download database access; access objects; db in access; download subd access; ms access database; subd access abstract; subd ms access; the benefits of access; database; download the database to access; Database; relational database; database management systems; exchange rate database; download the database; access database download; access databases download; car repair; auto repair; car salon; car repair service

Creation technology Database "Autoservice"

To create the database, the goals and objectives of the Autoservice database were set:

? to ensure customer satisfaction with both the service and the firm;
? provide the best service in the area
? warranty repair of new and used cars sold;
? commercial preventive maintenance (adjustments, etc.);
? commercial rehabilitation services (repairs);
? repair of used components and assemblies for the stock of remanufactured spare parts.

? people,
? materials,
? computers,
? machine tools,
? building.

The developed and created Database "Autoservice" is a set of interconnected components and displays various directions of car repair.

Figure 14. Database "Car service"

The system is divided into two subsystems and one extension:

? Repair of the technical part of the car.
? Expansion - car interior repair.

The main system "Repair of the technical part of the car" consists of four tables (see Fig. 15):

« Order»- which includes the necessary information about the order for the repair and diagnostics of the car, that is:

? Automobile.
? Owner.
? The reason for contacting the service station.

« Repair»- a table describing the process of repairing technical parts of a car, namely, parts that need to be repaired in the near future. This table includes items:

? Engine repair.
? Gearbox repair.
? Running gear repair.
? Fuel system repair.

Figure 15. Repair order for technical parts

Table " Diagnostics"Associated with" By order»And distributes cars for diagnostics of certain parts of the car, ie. engine, gearbox, chassis and fuel system.

V " Diagnostics»Information is stored about cars that need diagnostics of one or another part.

? Engine diagnostics.
? Diagnostics of the checkpoint.
? Running gear diagnostics.
? Fuel system diagnostics.

The main system working on the basis "Cascade models " and refers on the standard GOST 21624 -76

GOST 18507 -73

The work of the system comes from collecting information about the order, then diagnostics takes place, which determines the need to repair the machine. Each stage (except the initial one) cannot start until another is completed, unless the car does not need repairs.

The IT service subsystem was created to provide a repair warranty, claim warranty claims, and purchase spare parts for repairs.

1) filing a claim,
2) registration of the guarantee,
3) ordering spare parts, and includes 11 tables, one of which is common for an IT service. (see fig. 16).

Figure 16. IT service

IT service - divides the entire service into 3 parts:

? claim under warranty,
? registration of a guarantee,
? spare parts order.

Data 1 and 2 - contain information about customers.

Receiving 1 - the table contains data on the turnaround time and the price of the services provided.

Reason for contacting - a table that contains information about the reason for contacting the service station under warranty. It has a connection with the tables: agreement of STO 1 and Outcome 1, where data on the agreement of STO with the claim and the possibility of solving the problem are noted, respectively.

The expansion represents an increase in car repair services. Now the system has body repair and interior repair, which are also handled by the service station.

The extension subsystem consists of two tables and affects the 2nd tables from the main system. (see fig. 17)

Figure 17. Extension

The tables "body repair and interior repair" include information about the types of services.

Body repair:

? Replacement of parts.
? Putty.
? Painting.
? Varnishing.
? Polishing.

Interior repair:

? Replacement of components.
? Repair of components.

Links with the table " Price»To fix prices for services.

Functional:

? outfit orders,
? work,
? services,
? brigades,
? rate-hours.

Database resources:

? people,
? equipment,
? materials,
? computers,
? machine tools,
? building.

The waterfall model shown in Figure 18 provides for the sequential execution of all stages of the project in a strictly fixed order. The transition to the next stage means the complete completion of the work at the previous stage.

This is represented in the database as follows:

? acceptance of a repair order,
? Car diagnostics,
? car repair,
? car release from service station.

Figure 18. Database model

Analysis phase

Here, an application for car repairs at a service station is carried out. The customer fills out a document where the customer specifies the service that he needs.

Design phase

At this stage, the car is sent for diagnostics, which determines the cause of the car breakdown. In the future, at the option of the customer, the machine is sent for repair.

Implementation and implementation phase

At this stage, repairs take place, parts of the car, which, according to the results of diagnostics, need to be repaired or replaced. Also, without any checks, at this stage, repairs can be made to the exterior of the car and the interior.

Maintenance phase

At this stage, a guarantee for repairs is written out and the funds that were spent on diagnostics and repair of the car are calculated, after which the car is returned to the owner.

System properties

Integrability- the system is integrable, since it has the ability to interact with various banks (payment for services through these banks), with a tax company (sale of spare parts outside the region). The system is also connected with various car dealerships (under a contract) and insurance companies that insure the car service itself, as well as the company where the purchase of spare parts takes place.

Divisibility- the system consists of many subsystems that perform certain functions and have the ability to work in an autonomous mode.

Integrity- despite the fact that the system is divisible, when fully operational, it will not work if the functionality of one of its subsystems is violated.

Structurality- distribution by levels and hierarchies of system elements, i.e. the system will not be able to continue working if you skip one of the stages (without a guarantee, the customer will not be able to file a claim with the service station).

Standards

GOST 21624 -76 - this standard establishes requirements for products to ensure a given level of operational manufacturability (ET) and maintainability (RP), as well as the values of ET and RP indicators provided for by GOST 20334-81, for automotive products - all-wheel drive and part-drive vehicles (trucks, cars and buses), trailers and semi-trailers (hereinafter referred to as products).

GOST 18507 -73 - this standard applies to buses and cars (hereinafter referred to as automobiles) and establishes methods for their control tests after major repairs carried out by auto repair enterprises.

The standard does not apply to passenger cars that have been overhauled on the orders of individual owners.

Technical assignments

1. Make a common base of all services at the service station for a specific car.

Figure 19. General base of all services at the workshop

2. Data on the required tools and materials.

Figure 20. Data on tools and materials

3. Links with third party systems.

Figure 21. Third-party systems

Figure 22. Autocenters

Figure 23. Insurers

Figure 24. Field Insurers

4. Comments on the quality of service.

Figure 25. Comments

Figure 26. Visitor reviews

Figure 27. Reviews

In the course of the work, a database was created in the MS Access database management system. The work shows the step-by-step technology of creating a Database. An example of the "Autoservice" database is given. This base has been tested at the service station. The system has been tested. In the course of the work, adjustments were made and the final version of the "Autoservice" database was presented in the work.

Automation of technology for the formation of documents on graduation from the university in the framework of the ACS MIIT

Database "Car service"

Table relationships: The custumers table is linked to the masters table with a 1: N relationship on the vin_number field The custumers table is linked to the calculation table using a 1: 1 relationship on the ...

Database "Students"

The program begins with the inclusion of libraries necessary for the operation of certain functions. #include - to work with files, structures and functions. #include - for the strcmp () function. #include - for the screen cleaning function. ...

Traffic police database

A table is in first normal form if and only if none of its rows contains more than one value in any of its fields and none of its key fields are empty. The relationship is in second normal form ...

Steel products accounting database on the SQL Server platform

The process of designing an automated system is to build a model written in graphical notation. At the same time, the general principles of structural design are respected: top-down development, hierarchical model building ...

Organization of the implementation of the information system LLC "MensFormat"

Designing a data processing unit in the structural basis of the K1804VS2 series

The control unit (CU) is a combinational circuit with seven inputs. It converts external control signals and an internal signal from the FPN into a set of control signals for the microcircuit blocks ...

Development of automated information systems for accounting of calculations for killing oil wells

To create a database, the MySQL DBMS manager is used. Since we live in Russia, it was decided to choose the cp_1251 encoding. In order to be able to use foreign keys, the InnoDB engine will be used ...

Development of the information and reference system "Human Resources Department of Sharkovshchinsky Regional Public Education Organization"

The department of education, sports and tourism of the Sharkovshchina regional executive committee is located in the urban village of Sharkovshchina, st. Komsomolskaya, 15. Department of Education ...

Development of the software product "Plant personnel department"

Structural diagram - a diagram that reflects the composition and interaction for the management of parts of the product being developed. With object decomposition, such parts are objects (Figure 6.1). Figure 6 ...

Development of a system of accounting and movement of personnel at the enterprise

Before starting to use the program, it is necessary to carry out the data initialization process, which can be divided into several stages: 1. Filling in information about the organization ...

Development of a system for accounting for student fees

To create the database, the Microsoft SQL Server 2005 Express Edition DBMS will be used. We carry out the following actions: This stage will be carried out using Microsoft Visual Studio 2005. When you click on the Tools button in the menu bar, a list of commands will drop out ...

Creation of a site on the example of the registry office of the Elovsky district

There are several basic site structures: - linear - web pages are arranged in a specific order. The transition from one web page to another is strictly defined. Such a structure is justified, for example, when teaching ...

Electronic classroom journal

In the database designed according to the task of the technical task, 3 tables turned out: Questionnaire, Progress, Subject ...

Introduction 3
SECTION 1. Database Development 4

SECTION 2. Modeling Data Structures 7
2.1. Developing a Conceptual Database Model 7
2.2. Designing a logical data model 9
2.3. Converting an entity-relationship model to a relational model
data model 10
SECTION 3. Database Design 12
3.1. Development of tables 12
3.2. Designing Data Entry Forms 17
3.3. Designing Database Queries 21
3.4. Report Development 27
CONCLUSION 30
REFERENCES 31
APPENDICES 32

INTRODUCTION

To date, the design of databases (hereinafter referred to as DB) has become important for many organizations that use computer technology to improve the productivity of their work. Databases have become the basis of information systems, and their use is becoming an integral part of the functioning of any enterprise.
The object of the course work is the study of technologies for designing a relational database.
The subject of the course work is the study of the principles of developing relational databases on the example of designing and creating a database "Autoservice".
The purpose of the database design is to display the process of repair activities of a small enterprise.
To achieve this goal, the following tasks were set:

There are many different sources of information regarding relational database design and applications. Of all the resources offered, those that are suitable for database design in the OpenOffice.org Base environment were selected. For example, the books cover the basic techniques and principles of working and creating databases using Base, which is part of OpenOffice.org. The sources provide basic information about creating tables, forms, queries and reports. The books describe guidelines for the design and implementation of databases.

SECTION 1. Database Development

This database is intended for organizations engaged in any type of vehicle maintenance services.
The main functions of the database relate to the accounting of all cars that have ever been in a car service, storing complete information about each car (brand, series and technical passport number, chassis number and engine number, color, year of manufacture, etc.).
The database should also store information about each owner who at least once used the services of a car service. It should be possible to store not only basic and most necessary information, but also notes, clarifications, descriptions and those. characteristics of the installed spare parts and a lot of other useful information.
The car service administration may require the following data:

The DBMS operator can make the following changes:

In the reports, it is necessary to provide for the possibility of issuing a certificate of a malfunction of the car of this owner and a report on the work of a car service (the number of cars being repaired, the full name of the employee who repaired them).

The "Autoservice" database has been developed for the administrator and employees of the autoservice who receive and place orders for repairs and maintenance of cars.
The subject area in the assignment is data on malfunctions, car owners and car service workers.
The developed information system should perform the following functions:

Information output in the form of reports:

The following requirements are imposed on the developed database: data integrity, absence of duplication, absence of many-to-many relationships, absence of recursive relationships, relationships with attributes, multiple attributes.
The following requirements are imposed on the information contained in the database:
significance, completeness, reliability, comprehensibility, efficiency.
Such a view improves the usability of the database, in this case, the information input will be reduced to the selection of the necessary information from the list, where possible, which, of course, will increase the speed of information input and help to avoid incorrect input of parameters.
As a result of the creation and implementation of this database, it is required to obtain the following performance indicators: reducing the time when entering new data and changing old ones and, consequently, increasing labor productivity, as well as timely and complete receipt of information by the necessary administration of the car service.

SECTION 2. Modeling data structures

2.1. Developing a conceptual database model

When building a conceptual database model, we will use the recommendations of I.P. Karpova. ... As the author notes, the conceptual database model is a high-level object-oriented model of the domain, representing the object domain as a set of objects that have certain properties and are in certain relationships. The main goal of developing a high-level data model is to create a user experience model for data and to agree on a large number of technical aspects associated with database design. The conceptual data model is not tied to a specific physical database implementation and does not depend on a specific DBMS. The conceptual model is created on the basis of ideas about the subject area of each type of user, which is a set of data that the user needs to solve his problems.
The conceptual model for the Autoservice base was designed as an entity-relationship model.
The basic concepts of the model include concepts such as entity (object), relationship (relationship), entity types, relationship types, and attributes.
Entity - a real or representable object, information about which must be stored and available. In ER model diagrams, an entity is represented as a rectangle containing the entity's name. Each entity is defined by a set of attributes.
Attribute is a named characteristic of an entity. Its name must be unique for a specific type of entity, but can be the same for different types of entities. An attribute of an entity is any detail that serves to clarify, identify, classify, quantify, or express the state of an entity. The names of the attributes will be entered into the rectangle denoting the entity and will be written under the entity's name.
Relationships are established between entities.
A link is a graphical association established between two entities. This association is always binary and can exist between two different entities or between an entity and itself (recursive relationship). Links - denote by lines.
Thus, from the description of the subject area, we extract all types
entities:
- Customers;
- Orders;
- Masters;
- List of works.
Each of the entities will define its own set of attributes.
The Customer entity is defined by the following set of attributes:

The attributes of the Orders entity are defined as follows:

The entity of the Master is documented based on the following attributes:

Entity A work list is defined by the following set of attributes:

In accordance with the domain model, the following conceptual model of the "Autoservice" database is presented (Fig. 1).
Fig.1 Conceptual model of the "Autoservice" database.

2.2. Development of a logical data model

Converting a local conceptual data model to a local logical model is to remove unwanted elements from conceptual models and transform the resulting models into local logical models. Undesirable elements include:
- many-to-many relationships;
- recursive links;
- links with attributes.
In the created conceptual model, the above undesirable elements were not found.
The data logic diagram is shown in Fig. 2.

Rice. 2. Logical data schema.

Converting an entity-relationship model to a relational data model
is carried out by sequentially performing a series of steps:
- each entity is assigned a relation of the relational data model;
- each attribute of the entity becomes an attribute of the corresponding relationship;
- the primary key of the entity becomes the primary key of the corresponding relationship. The attributes included in the primary key of the relationship are automatically assigned the required property (NOT NULL). A set of attributes of the main entity, which is the primary key of the main entity, is added to each relationship corresponding to the subordinate entity. In a relation corresponding to a subordinate entity, this set of attributes becomes a foreign key.
This process is discussed below.

SECTION 3. Database Design

A table is an object designed to store data in the form of records (rows) and fields (columns).
OpenOffice.org Base provides three different ways to create a database table:

In this work, the tables were created using the wizard.
For each relational database table, its structure is given: the composition of fields, their names, data type and size of each field, table keys and other properties of the fields.
Database tables are developed sequentially:

The table is the basis of the database, therefore, when developing tables, it is recommended to be guided by the following basic principles:

Each table contains information on a specific topic, and each field in the table contains a specific fact on the topic of the table. For each table in the database, you must define the properties they contain.
The Autoservice database contains four tables:

Rice. 3. Customers table.

Rice. 4. Table Orders.

This table contains the following attributes:

Rice. 5. List of works.

Rice. 6. Masters.

Let's define a primary key for each entity, while taking into account that strong entities have only one key field, and weak entities have the same number of relationships. When choosing a primary key, we will be guided by the following rules:
- the key must contain a minimum set of attributes;
- the key should be used, the probability of changing the values of which is minimal;
- the key value must have a minimum length.
Based on the foregoing, we will define the following key fields for the existing entities:

After breaking down the information into tables and defining the key fields, you need to choose how the DBMS will combine the related information. To do this, you need to define relationships between the database tables.
OpenOffice.org BASE supports four types of relationships between tables:
- one-to-one (each record in one table matches only one record in another table);
- one-to-many (each record in one table corresponds to many records in another table);
- many-to-one (similar to one-to-many);
- many-to-many (one record from the first table can be linked to more than one record from the second table, or one record from the second table can be linked to more than one record from the first table).
The links established in the "Autoservice" database have already been presented in the previous section in Fig. 2.

Form is an object designed to enter, edit and view tabular data in a convenient form.
Forms contain so-called controls that are used to access data in tables. Control elements are text fields for entering and editing data, buttons, checkboxes, radio buttons, lists, labels. Creating forms that contain the necessary controls greatly simplifies the data entry process and helps prevent errors.
OpenOffice.org Base Forms provide functionality to perform many tasks that cannot be done by other means; they allow you to validate data as you enter, perform calculations, and provide access to data in related tables using subforms.
OpenOffice.org Base offers several ways to create forms. The simplest of these is to use automatic form creation tools based on a table or query.
There are four simple forms and three subforms for the "Autoservice" database.
Examples of simple shapes are shown in Figure 7-10.

Fig. 7. Customer form.

Fig. 8. Form Orders.

Fig. 9. List of works.

Fig. 10. Masters.
The composite form contains the main form and its subordinate form - the subform. A subform is in its content the same form, but it is not used independently, but is always loaded from some form when opening or creating a document. You can do almost everything in a subform as in a form, with the exception that you cannot insert another subform into it.
When creating fields in subforms, be sure to keep in mind that the names of all fields must be unique within the form along with all subforms that are used in it at the same time.
Thanks to compound forms, it becomes possible to fill in different tables at the same time.
Examples of subforms are shown in Fig. 11-13.

Rice. 11. Form Customer with subform Orders.
Customer form with subform Orders - provides the input of the necessary data to identify the customer and view the work performed on this order. This form allows you to enter information into the Customer and Orders tables.

Rice. 12. Form Orders with subform Repair work.
This form allows you to enter information into the Orders and Repairs tables.

Rice. 13. Form of the Master with subform Orders.
The Master form with the Orders subform allows you to control the execution of work by a specific master.

Query is an object that allows you to get the required data from one or more tables.
Queries are used to retrieve data from tables and present them to the user in a convenient form. With their help, data selection, sorting and filtering are performed. You can transform data according to a given algorithm, create new tables, automatically populate tables with data imported from other sources, perform simple calculations on tables, and much more.
The peculiarity of queries is that they scoop data from base tables and create on their basis a temporary result table (snapshot) - an image of fields and records selected from the base tables. Working with the image is faster and more efficient than with tables stored on the hard disk.
Upon request, you can get sorted and filtered data as needed. Queries can also be used as record sources for forms, reports, and data access pages.
There are several types of requests:

A database is, first of all, a storage of data objects, i.e. a set of possible concepts or events described by the database, with the ability to search for these objects by attributes. A database can be considered not only tables indexing files with knowledge of different formats, but also these files themselves, because they are not typed repositories of knowledge in such a database. Databases can be used as an aid to implement some useful function.

Share your work on social media

If this work did not suit you at the bottom of the page there is a list of similar works. You can also use the search button

Ministry of Education and Science of the Russian Federation

Federal State Budgetary Educational Institution

higher professional education

Ryazan State University named after S.A. Yesenin

Faculty of Physics and Mathematics

Specialty Mathematical support and administration
information systems

Department of Informatics and Computer Science

Coursework by discipline

"Databases and DBMS"
on the topic of:

"Database design

"Car service station" "

Completed by a 3rd year student of FMF

Dmitry Makarov

Scientific adviser:

Bogdanova N Atalya Vladimirovna

Ryazan 2015

Introduction

In connection with the increase in the volume of information and the need to transform it, there is a growing need to automate existing data processing processes. These processes include analyzing, organizing, displaying and editing information.

Modern technologies of application development make building databases fast and of high quality. A skilled user using Microsoft Access today can create on a personal computer in one evening what took months of work on early computers. In addition, it has now become much easier to find errors, fix them and change the design directly during the creation of the database.

The projected database "Car service station" allows you to systematize a quick search for the necessary information in a given subject area.

The database should store information about cars: manufacturer, model, state. number, year of issue, country of origin, owner's passport number, gas equipment; information about the owners: full name, address, phone number, as well as passport number; information about employees: full name of the employee, employee identification number; information about the work: work code, description, date of completion, duration, state. room.

The purpose of this course workis the design of the database "Car service station".

The most important tasks we are facing in the process of doing the work are as follows:

· Study of the peculiarities of the subject area "Car service station";

· Development of a database schema;

· Implementation of the developed scheme in a specific DBMS (MS Access);

· Creation of forms for data entry, reports, inquiries.

The creation of any database begins with the choice of the database structure. In our case, it is more convenient to use five data tables. Next, we will make several requests for a selection by various parameters, as well as reports to them. For the convenience of working with data, we will create several forms and buttons for transitions between them.

The course work consists of an introduction, two chapters, a conclusion, a list of used literature.

CHAPTER 1. Database Design

In the database " Car service station "must have the following attributes:

Manufacturer
Model
Year of issue
Gas equipment
Manufacturer country
State car number
Full name of the owner
Owner's passport number
Owner address
Owner's phone
Full name of the Employee
Job code
Work description
Date of completion of work
Duration of work

Let's select 4 entities: "Auto", "Owners", "Workers", "Works".

Essence "Auto" has the following attributes:

Manufacturer

Model

State room

Manufacturer country

Gas equipment

Year of issue

Auto "Can have duplicate values, then add the attribute of the individual movie number, which should not have duplicate values.

Entity "Owners" has the following attributes:

Full name of the owner

Owner address

Owner's phone

Owner's passport number

Since all the original attributes of the "Directors" entity can have duplicate values, we will add an individual director number attribute, which should not have duplicate values.

Entity "Workers" has the following attributes:

Full name of the employee

Since all the original attributes of the entity " Workers »May have duplicate values, then add the attribute of the individual employee number, which should not have duplicate values.

The essence of "Work" has the following attributes:

Work description

Work completion date

Duration of work

Since all the original attributes of the entity " Work "Can have duplicate values, then we add the attribute code of work, which should not have duplicate values.

Let's accept the agreements.

Agreement 1:

Each owner can have several cars, therefore, the degree of connection for the "Auto" entity is N ... In turn, any car belongs to one owner, therefore, the degree of connection for the Owners entity is 1.

Agreement 2:

Each car must belong to the owner, therefore, the belonging class for the "Auto" entity is required. Each owner must own at least one car, therefore, the ownership class for the Owners entity is required.

Figure 1.1 ER - diagram of the relationship of entities Auto and Owners

Thus, we have a binary one-to-many relationship with a mandatory class of belonging for both entities, for its implementation it is necessary to create two relationships (one for each entity), and in the relation for the multi-connected entity "Auto" it is necessary to add a primary key to establish a relationship the simply connected entity "Owners" is the passport number.

Agreement 3:

Only one work can be performed on one car, therefore, the degree of connection for the entity "Auto" is 1. In turn, each work can be performed on several cars, therefore, the degree of connection for the entity "Work" is N.

Agreement 4:

Work is being done on the car. Work is being done on cars.

Fig 1.2 ER - diagram of the relationship of entities Auto and Work

Thus, we have a binary one-to-many relationship with a mandatory class of belonging for both entities, for its implementation it is necessary to create two relationships (one for each entity), moreover, in the relation for the multi-connected entity "Works", it is necessary to add a primary key to establish the relationship simply connected entity "Auto" state. room.

Agreement 5:

Any employee can perform any work, therefore, the degree of connection for the "Work" entity is N ... In turn, any work can be performed by any worker, therefore, the degree of connection for the "Workers" entity is N.

Agreement 6:

Workers perform work. Work is performed by employees.

Figure 1.3 ER - diagram of the relationship of the entities Workers and Jobs

Thus, there is a binary many-to-many relationship, for its implementation it is necessary to create three relationships: one for each entity with the corresponding attributes and primary keys, and 3e - for the relationship; as attributes - primary keys of both entities; the primary key is composite.

Communication table (work code, individual employee number)

Functional dependence of the entity "Auto"

Fig.1.4 Functional dependence of the entity "Auto"

State number  Manufacturer

State number  Model

State number  Year of issue

State number  Manufacturer country

State number  Gas

State number  Passport number

State number - determinant, state number is a possible key, which means the relation "Auto" is in the BCNF.

Functional dependency of the "Owners" entity

Fig.1.5 Functional dependence of the "Owners" entity

Passport number  Full name

Passport number  Address

Passport number  Phone

The passport number is a determinant, the passport number is a possible key, which means the "Owners" relation is in the BCNF.

Functional dependence of the entity "Works"

Fig. 1.6 Functional dependence of the "Works" entity

Job Code  Description

Job Code  Completion Date

Job code  Duration

Job code  State room

Job code - determinant; The work code is a possible key, so the Work relation is in the BCNF.

Functional dependence of the "Workers" entity

Fig. 1.7 Functional dependence of the "Owners" entity

An identification number Full name

Identification number is a determinant, Identification number is a possible key, which means the relation “Workers” is in the BCNF.

Consider the implementation of the database by means MS ACCESS.

"Auto" (manufacturer, model, state number, year of manufacture, gas equipment, country of origin, owner's passport number)

AVTO "

Fig. 1.8 Table constructor “ AVTO ”.

Fig.1.9 Table of the entity "Auto"

"Owners" (Name, address, phone number, passport number).

The relation in the relational database corresponds to the table “ VLADELCY "

Fig.1.10 Table constructor “ VLADELCY ”.

Fig. 1.11 Table of the "Owners" entity

"Works" (Job code, job description, date of completion, state number).

The relation in the relational database corresponds to the table “ RABOTU ”.

Figure 1.12 Table constructor“RABOTU”.

Fig. 1.13 Table of the entity "Jobs"

Communication table (Job code, employee ID number).

The relation in the relational database corresponds to the table"DLYSVYZI"

Fig. 1.14 Table constructor “ DLYSVYZI ”.

Figure 1.15 Table with ligature

"Workers" (Name, employee identification number).

The relation in the relational database corresponds to the table “ RABOTNIKI ”.

Figure 1.16 Table constructor“RABOTNIKI”.

Fig. 1.17 Table of the entity "Workers"

Data schema

Fig 1.18 Data schema

CHAPTER 2. Description of OBD and control system

2.1 Requests

Lexus car models

SELECT MODEL FROM AVTO

WHERE PROIZV = "Lexus";

Car manufacturers and all models

SELECT PROIZV, MODEL

FROM AVTO;

Manufacturer, model and state. number of a car belonging to Valery Valentinovich Kuzin

SELECT AVTO.PROIZV, AVTO.MODEL, AVTO.GOSNOMER

FROM VLADELCY INNER JOIN AVTO ON VLADELCY.PASPORTNOMER = AVTO.PASPORTNOMER

WHERE VLADELCY. FIO = "Kuzin Valery Valentinovich";

Manufacturer, model, year of manufacture and state number of a car produced before 2005 sorted by release date

SELECT PROIZV, MODEL, GOSNOMER, GODVIPUSKA

FROM AVTO

WHERE GODVIPUSKA< 2005 order by GODVIPUSKA;

Date of completion and description of the work performed by Eduard Viktorovich Smenov.

SELECT RABOTU.DATAV, RABOTU.OPISANIE

FROM RABOTU INNER JOIN (RABOTNIKI INNER JOIN DLYSVYZI ON RABOTNIKI.IDR = DLYSVYZI.IDR) ON RABOTU.KODRABOTU = DLYSVYZI.KODRABOTU

WHERE RABOTNIKI. FIO = "Smenov Eduard Viktorovich";

List of auto brands, state. numbers and works that were carried out on them

SELECT AVTO.PROIZV, AVTO.GOSNOMER, RABOTU.OPISANIE

FROM AVTO INNER JOIN RABOTU ON AVTO.GOSNOMER = RABOTU. GOSNOMERAVTO;

Manufacturers, year of manufacture and models of the newest cars (by year of manufacture)

SELECT PROIZV, MODEL

FROM AVTO

WHERE GODVIPUSKA = (SELECT MAX (GODVIPUSKA) AS MAXGV FROM AVTO);

Display all information about the 3 longest jobs

SELECT TOP 3 *

FROM RABOTU

ORDER BY PRODOLG DESC;

Names of owners, manufacturers and state. car numbers belonging to them

SELECT VLADELCY.FIO, AVTO.PROIZV, AVTO.GOSNOMER

FROM VLADELCY INNER JOIN AVTO ON VLADELCY.PASPORTNOMER = AVTO.PASPORTNOMER;

All information about all employees

SELECT *

FROM RABOTNIKI;

Full name, phone number and address of car owners from Ryazan

SELECT FIO, TELEFON, ADRES

FROM VLADELCY

WHERE ADRES LIKE "* Ryazan *";

List of countries producing cars

SELECT DISTINCT STRANA

FROM AVTO;

Name of the owner who has the largest number of cars, and this number

SELECT Temp.FIO, Temp.MaxAVTO

FROM. AS Temp INNER JOIN. AS Temp0 ON Temp.MaxAVTO = Temp0.Maxim;

The number of hours spent working on certain days

TRANSFORM SUM (PRODOLG)

SELECT KODRABOTU

FROM RABOTU

GROUP BY KODRABOTU

PIVOT DATAV;

Description and duration of the shortest work

SELECT OPISANIE, PRODOLG

FROM RABOTU

WHERE PRODOLG = (SELECT MIN (PRODOLG) FROM RABOTU);

Withdraw all car manufacturers

SELECT PROIZV

FROM AVTO;

Manufacturer and year of production of a car with gas equipment

SELECT PROIZV, GODVIPUSKA

FROM AVTO

WHERE GAZ;

Add information about a new employee in a car service.

INSERT INTO RABOTNIKI

VALUES ("Jason Statham", 7);

Before adding:

Fig. 2.18 Table “ RABOTNIKI ”Before adding a new entry

Inquiry:

After adding:

Fig. 2.20 Table “ RABOTNIKI ”After adding a new entry

Change the address of Loginov Yegor Yurievich

UPDATE VLADELCY SET ADRES = "Ryazan, Moscow highway, 15"

WHERE PASPORTNOMER = "34 88 336882";

Before the change:

Fig. 2.21 Table “ VLADELCY ”Before changing the record

Inquiry:

After the change:

Fig. 2.24 Table “ VLADELCY ”After changing the record

Delete a record about a car with license plate e244bv 23.

DELETE *

FROM AVTO

WHERE GOSNOMER = "e 244 cc 23";

Before deletion:

Fig. 2.25 Table “ AVTO ”Before deleting the entry

Inquiry:

After deletion:

Fig. 2.28 Table “ AVTO ”After deleting the entry

2.2. Forms

General form of the database "Car service station"

The form contains buttons for opening subordinate forms (Auto, Owners, Jobs, Employees), buttons for executing requests, as well as a button for closing the main form.

In the "Shape" mode

Fig. 2.29 General form of the database "Car service station"

In the "Constructor" mode

Fig. 2.30 General form of the database "Car service station" in the constructor mode

Form "Auto"

Fig.2.31 Form "Auto"

In the "Constructor" mode

Fig.2.32 Form "Actors" in design mode

Queries for combo boxes

"Owners" form

Figure 2.36 "Owners" form

In the "Constructor" mode

Fig. 2.37 "Owners" form in design mode

Form "Jobs"

Fig. 2.38 Form "Work"

In the "Constructor" mode

Fig. 2.39 Form "Jobs" in design mode

Queries for combo boxes

Communication form "Work-Workers"

Fig. 2.41 Form of communication "Work-Workers"

In the "Constructor" mode

Fig. 2.42 Form of communication "Work-Workers" in the design mode

Queries for combo boxes

Conclusion

In this project, a relational database "Car service station" was created, which contains five tables with data: a table for cars, a table for owners, a table for jobs, a table for workers and a table for linking jobs and workers.

The main stages of development:

Determining the Purpose of Creating a Database
Determining the required fields in the database
Determining the tables that the database should contain.
Define the tables to which the fields belong.
Defining primary keys.
Determination of relationships between tables.
Improvement of the database structure.
Entering data and creating other database objects (such as forms and queries).

The database ensures efficient work, creates ease of use. To obtain information about cars, owners, workers and jobs, the user performs a minimum of actions, which reduces the time of working with the database.

Even a novice programmer can figure out the work of this database, since it is made as convenient and understandable as possible.

List of used literature

1. Bekarevich Y., Pushkina N. Microsoft Access for 21 lessons. - M .: Olma-Press, 2006 .-- 544s.

2. Laurie Ulrich Fuller, Ken Cook, John Kaufeld. Microsoft Office Access 2007 for dummies. - M .: Williams, 2007 .-- 384s.

3. Mikheeva V., Kharitonova I. Microsoft Access 2003. - M .: Nova, 2005. - 1072s.

4. Khomonenko A.D., Tsygankov V.M., Maltsev M.G. Database. Textbook for universities / ed. prof. HELL. Khomonenko // SPb.: CORONAprint, 2000 .-- 416 p.

5. Khomonenko A., Gridin V. V. Microsoft Access. Fast start. - M., 2008 .-- 304s.

6. Korneev V.V. and other databases. Intellectual information processing M.: Knowledge, 2000. - 352 p.

Auto

N: 1

Owners

Auto

1: N

Work

Workers

N: N

Work

State room

Manufacturer

Model

Year of issue

Manufacturer country

Gas

Passport ID

Full name

The address

Telephone

The code

work

Description

date

fulfillment

Duration

State

room

An identification number

Full name

Other similar works that may interest you. Wshm>
18542.		Passenger car service station	786.59 KB
	The decisive factor for the development of infrastructure is the car park and the tendency of its growth. These are absolutely irreparable losses for us for the future of the country. To solve this problem, special attention should be paid to vehicles belonging to individuals, since the owner is responsible for the technical condition of the vehicle. In second place are the former state-owned service stations in the third place, newly created independent private service stations in the fourth, motor transport enterprises performing technical ...
13718.		Organization of technical maintenance of Mitsubishi cars in the conditions of LLC "Transtekhservice"	363.83 KB
	The aim of the thesis is to organize the maintenance of Mitsubishi cars in the conditions of Transtekhservice LLC. To achieve this goal, the following tasks have been identified: Mitsubishi has won and maintains a reputation as a manufacturer of high quality cars; expansion of the Mitsubishi car model range; consider the technical characteristics of Mitsubishi cars by model range; Mitsubishi TO card: a brief description of the regulations; execution sequence ...
4523.		Organization of a roadside service station for routine car repairs	369.01 KB
	The features and advantages of road transport, which predetermine a fairly high rate of development, are associated with the mobility and flexibility of delivery of goods and passengers "door-to-door", "just in time" and adherence, if necessary, to the schedule.
17752.		Organization of a motor section at the "KRYMDIZELSERVICE" car service station	649.78 KB
	In the further development and intensification of the work of motor transport, the key problem has become a more complete use of the production potential of enterprises and the identification of reserves for increasing production efficiency. Typically, these carriers do not have their own base for proper maintenance and repair of vehicles. This is due to the fact that car owners either do not have or have to a limited extent the material resources and labor skills to maintain and repair their car. Rapid pace of development ...
4622.		Design of a section for diagnostics of corporate service of passenger cars YSU	2.74 MB
	Khanty-Mansiysk Autonomous Okrug - Yugra is one of the most dynamically developing regions of the Russian Federation. Our district is the main oil and gas region in Russia and one of the largest oil producing regions in the world. In Russia, Khanty-Mansi Autonomous Okrug-Yugra is leading in a number of key economic indicators:
4606.		Design of the aggregate section of corporate service for YSU cars	1.86 MB
	Check the condition of the cab of the platform of the rear-view mirrors of the sun visors of the license plate plumage mechanisms of the door mechanisms of the locks of the sides of the hood platform of the trunk lid of the towing support unit Check the operation of the windshield wiper and washers of the windshield and headlights; the operation of the heating and heating system in the cold season of the ventilation system. Engine including lubrication cooling systems Check by inspection the tightness of the lubrication systems of the power supply and engine cooling, including ...
20665.		Design and implementation of a pharmacy database	2.55 MB
	Novokuznetsk assignment for term paper It is necessary to design a database that includes information presented in the form of a group of attributes: Pharmacy Drug name; annotation; storage; receipt date; coming; balance at the end of the month; company manufacturer; supplier, etc. The task is as follows: Create a database. Organize persistent relationships between tables to ensure the integrity of your database.
20182.		Database design college daytime	2.59 MB
	Database design for the day department of the college Completed by: student gr. In the course work, the task is set - to develop a database project for the accumulation of the necessary information in the organization to create a fill in the database. The database should be designed taking into account the implementation of requests of various types for obtaining information. When designing the database, consideration should be given to the possibility of issuing a paper report.
20025.		Designing the database of the insurance company OJSC "Sogaz-Med"	448.12 KB
	Insurance companies are financial intermediaries that specialize in providing insurance services. Their activity consists in the formation on the basis of contracts with legal entities and individuals (through the sale of insurance policies) of special funds, from which payments are made to the policyholders of funds in specified amounts in the event of certain events (insured events).
10007.		Designing the database "Catalog of car spare parts"	182.36 KB
	Initially, for the accumulation and storage of information on a computer, local arrays (or files) were used, while for each of the functional tasks to be solved, its own files of initial and result information were created. This led to significant duplication of data, complicated their updating, and made it difficult to solve interrelated problematic tasks.