Database application development. Overview of programs for creating databases Create application for one database table c

Personal Area

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists using the knowledge base in their studies and work will be very grateful to you.

Posted on http://www.allbest.ru/

Introduction

3. Data organization models

4. Relational databases

6. Infological model

7. Logical model

8. Structure of tables

12. Creating tables

16. Generating reports

17. Program Listing

Conclusion

Bibliography

Introduction

To make informed and effective decisions in production activities, in economic management and in politics, a modern specialist must be able to receive, accumulate, store and process data with the help of computers and communications, presenting the result in the form of visual documents. Therefore, it is so important to be able to work with databases.

A database is an organized structure for storing information. Modern databases store not only data, but also information.

Delphi is spoken of as a rapid application development framework. This is a visual programming technology, i.e. the user draws up his future program, and sees the results of his work even before the launch of the program itself. Basically, the process of writing an application itself is divided into two parts. The first - the programmer places the necessary elements on the windows of his program, positions, sets the required dimensions, changes properties. The second is actually writing program code, describing the properties of elements that are available only while the application is running, describing reactions to the event of a window, pressing a button, etc. change this property in the object inspector (the so-called property monitor of the selected item). This change will automatically add or modify the program code.

This is a big plus in visual programming technology. When creating or modifying his software product, the user, not knowing or not paying attention to some properties of the program element, but using only the necessary ones, writes a completely finished working product, sometimes acting on an equal footing in complexity, with those written in a non-visual editor.

We are faced with the task of compiling a database that would contain data on the curriculum of the educational process. Having filled the database, you need to conduct a selective analysis using Sql queries.

database infological table program

1. General requirements for the development of database applications

The database must contain

a. Tables for storing data, at least 3 tables. Each table must contain at least 10 records.

b. Forms for easy viewing, entering, editing and searching data, forming and displaying queries. The form must contain explanations, hints. When filling in form fields, for known sets of values, use the ability to select values \u200b\u200bfrom a list. Forms should be linked whenever possible to optimize post navigation. The form should contain as much information as possible for the user. Provide for handling of arising semantic errors.

c. Reports containing all tables, forms, queries

d. Menu for accessing various database objects

e. Help containing a complete description of the task

2. For database programming it is necessary to use additional literature on the SQL language, the DELPHI programming system.

3. List and methods of independently solved problems

1. Analysis of the problem statement and subject area.

2. Infological design, substantiation of information objects, domains, attributes, links, drawing up an infological model.

3. Logical design, construction and substantiation of basic relations, normalization.

4. Designing Sql-queries.

5. Programming the structure and general functions in the database.

6. Designing a database in a software environment.

7. Development of the program interface.

8. Execution of an explanatory note.

4. Criteria for assessing the acquired competencies for coursework

The criteria are filled in by the teacher when defending the work, given in table 1. Based on the average grade, the grade for the course work is given.

Table 1. Assessment of competencies

Competency name	Assessment object
Understand and follow requirements	Obtained results (DB) (volume, structure, compliance with the task)
Written communication	The quality of presentation in the explanatory note, clarity, volume, structure, compliance with the task.
Know and apply elements of the DELPHI programming system	Database application components, answers to questions about database implementation
Know and apply the elements of database technology	Answers to design-related questions, possibly in test format
Understand the need for database technology	Introduction of the explanatory note
Work planning, work organization	Turnaround time
Solve problems on your own	List and methods of independently solved problems
Oral communication	Public protection of the database
Ability to analyze, synthesize	Infological, logical database model
Commitment to quality results	Quality of work performed, ergonomic indicators, quality of functioning.
Ability to generate new ideas	The quality of the interface, additional functions of the database, not taken into account in the task.
Ability to manage (search) information	The number of information resources found and used in the course work, the number of consultants.

2. Basic concepts and classification of database management systems

Database (DB) is a collection of structured data stored in the memory of a computing system and reflecting the state of objects and their interrelationships in the subject area under consideration.

The logical structure of the data stored in the database is called the data presentation model. The main models of data presentation (data models) include hierarchical, network, relational.

A database management system (DBMS) is a complex of language and software tools designed for the creation, maintenance and sharing of databases by many users. Typically, a DBMS is distinguished by the data model used. So, DBMS based on the use of a relational data model are called relational DBMS.

To work with a database, DBMS tools are often sufficient. However, if it is required to ensure the convenience of working with the database for unskilled users or the DBMS interface does not suit users, then applications can be developed. Their creation requires programming. An application is a program or a set of programs that automate the solution of any applied problem. Applications can be created in the environment or outside the DBMS environment - using a programming system that uses database access tools, for example, Delphi or C ++ Build. Applications developed in a DBMS environment are often referred to as DBMS applications, and applications developed outside the DBMS are often referred to as external applications.

The data dictionary is a database subsystem designed for centralized storage of information about data structures, relationships of database files with each other, data types and formats of their presentation, data belonging to users, security codes and access control, etc.

Information systems based on the use of databases usually operate in a client-server architecture. In this case, the database is hosted on a server computer and shared.

The server of a certain resource in a computer network is a computer (program) that manages this resource, a client is a computer (program) that uses this resource. As a resource of a computer network, for example, databases, files, print services, postal services can act.

The advantage of organizing an information system on the client-server architecture is a successful combination of centralized storage, maintenance and collective access to general corporate information with individual user work.

According to the basic principle of client-server architecture, data is processed only on the server. A user or an application generates queries that come to the database server in the form of SQL instructions. The database server provides search and retrieval of the required data, which is then transferred to the user's computer. The advantage of this approach in comparison with the previous ones is a noticeably smaller amount of transmitted data.

The following types of DBMS are distinguished:

* full-featured DBMS;

* database servers;

* tools for developing programs for working with a database.

Full-featured DBMSs are traditional DBMSs. These include dBase IV, Microsoft Access, Microsoft FoxPro, and more.

Database servers are intended for organizing data processing centers in computer networks. Database servers provide processing of client program requests, usually using SQL statements. Examples of database servers are: MicrosoftSQL Server, Inter Base, etc.

In the general case, the role of client programs can be DBMS, spreadsheets, word processors, e-mail programs, etc.

Tools for developing programs for working with a database can be used to create the following programs:

* client programs;

* database servers and their individual components;

* custom applications.

By the nature of their use, DBMSs are divided into multiuser (industrial) and local (personal).

Industrial, DBMS represent a software basis for the development of automated control systems for large economic objects. Industrial DBMS must meet the following requirements:

* the ability to organize joint parallel work of many users;

* scalability;

* portability to various hardware and software platforms;

* stability in relation to failures of various kinds, including the presence of a multi-level backup system of stored information;

* ensuring the security of stored data and an advanced structured system of access to them.

Personal DBMS is software aimed at solving the problems of a local user or a small group of users and intended for use on a personal computer. This explains their second name - desktop. The defining characteristics of desktop systems are:

* relative ease of use, allowing you to create workable user applications on their basis;

* relatively limited hardware resource requirements.

According to the data model used, DBMS are divided into hierarchical, network, relational, object-oriented, etc. Some DBMS can simultaneously support several data models.

The following types of languages \u200b\u200bare used to work with data stored in the database:

* data description language - a high-level non-procedural language
declarative type, designed to describe a logical
data structures

* data manipulation language - a set of constructions that provide basic operations for working with data: input, modification and retrieval of data on request.

The named languages \u200b\u200bmay differ in different DBMS. The most widespread are two standardized languages: QBE - a query language based on the model and SQL - a structured query language. QBE mainly has the properties of a data manipulation language, SQL combines the properties of both types of languages.

The DBMS implements the following basic low-level functions:

* data management in external memory;

* management of RAM buffers;

* transaction management;

* keeping a log of changes in the database;

* ensuring the integrity and security of the database.

The implementation of the data management function in external memory ensures the organization of resource management in the OS file system.

The need to buffer data is due to the fact that the amount of RAM is less than the amount of external memory. Buffers are areas of main memory designed to speed up the exchange between external and main memory. The buffers temporarily store fragments of the database, the data from which are supposed to be used when accessing the DBMS or are planned to be written to the database after processing.

The transaction mechanism is used in the DBMS to maintain the integrity of the data in the database. A transaction is a certain indivisible sequence of operations on database data, which is tracked by the DBMS from start to finish. If for any reason (hardware failures and failures, errors in software, including the application) the transaction remains incomplete, then it is canceled.

There are three main properties inherent in transactions:

* atomicity (all operations included in the transaction are executed or none);

* serializability (there is no mutual influence of transactions executed at the same time);

* durability (even a system crash does not lead to the loss of the results of a committed transaction).

An example of a transaction is the operation of transferring money from one account to another in the banking system. First, money is withdrawn from one account, then they are credited to another account. If at least one of the actions is not successful, the result of the operation will be incorrect and the balance of the operation will be upset.

Change logging is performed by the DBMS to ensure the reliability of data storage in the database in the presence of hardware and software failures.

Ensuring the integrity of the database is a necessary condition for the successful functioning of the database, especially when it is used on a network. The integrity of the database is a property of the database, meaning that it contains complete, consistent and adequately reflecting the subject area information. The integral state of the database is described using integrity constraints in the form of conditions that must be met by the data stored in the database.

Security is achieved in the DBMS by data encryption, password protection, support for access levels to the database and its individual elements (tables, forms, reports, etc.).

3. Data organization models

In a hierarchical model, entity objects and domain relations are represented by data sets that have a tree-like (hierarchical) structure. The hierarchical data model was historically the first. On its basis, in the late 60s - early 70s, the first professional DBMS were developed.

The focus of integrity constraints in the hierarchical model is on the integrity of references between ancestors and descendants, subject to the basic rule: no descendant can exist without a parent.

The network data model allows you to display various relationships of data items in the form of an arbitrary graph. A network database consists of a set of records and a set of related links. There are no special restrictions on the formation of a connection. If in hierarchical structures a descendant record could have only one ancestor record, then in the network data model a descendant record can have an arbitrary number of ancestor records.

The advantage of the network data midsection is the possibility of its effective implementation. In comparison with the hierarchical model, the network model provides greater opportunities in the sense of the admissibility of the formation of arbitrary connections.

The disadvantage of the network data model is the high complexity and rigidity of the database schema built on its basis, as well as the complexity of its understanding by an ordinary user. In addition, in the network data model, the control of the integrity of links is weakened due to the admissibility of establishing arbitrary links between records.

Systems based on the network model are not widely used in practice.

The relational data model was proposed by IBM employee Edgar Codd and is based on the concept of relation.

A relationship is a set of elements called tuples. A two-dimensional table is a visual form of representing a relationship.

Using one table, it is convenient to describe the simplest type of relationships between data, namely: the division of one object, information about which is stored in the table, into many subobjects, each of which corresponds to a row or table record.

The main disadvantages of the relational model are the following: the lack of standard means of identifying individual records and the complexity of describing hierarchical and network relationships.

4. Relational databases

The relational data model (RDM) of a certain subject area is a set of relationships that change over time. When creating an information system, a set of relations allows you to store data about objects in the subject area and simulate connections between them. RMD terms are presented in table. 4.1

Table 4.1. The terms of the relational model

Term-relational dressed	Equivalent
Attitude
Relationship diagram	Table Column Headers Row (Table Header)
	Table row, record
The essence	Description of object properties
	Column, field
	Many valid values	attribute
Primary key	Unique identificator
Cardinality	Number of lines
	Number of columns

A relational database is a data warehouse that contains a set of two-dimensional tables. The data in the tables must comply with the following principles:

1. Attribute values \u200b\u200bmust be atomic (in other words,
each value contained at the intersection of a row and a column,
should not be split into several values).

2. The values \u200b\u200bof each attribute must be of the same type.

3. Each record in the table is unique.

4. Each field has a unique name.

5. The sequence of fields and records in the table is not essential.

Relationship is the most important concept and is a two-dimensional table containing some data.

An entity is an object of any nature, data about which is stored in a database. Entity data is stored in a relation.

Attributes are properties that characterize an entity. In the structure of the table, each attribute is named and it corresponds to the title of a certain column in the table.

The key of a relationship is a set of its attributes that uniquely identify each of the tuples of the relationship. In other words, the set of attributes K, which is the key of the relationship, has the property of uniqueness. The next property of a key is not redundancy. That is, none of the proper subsets of the set K possesses the uniqueness property.

Each relationship always has a combination of attributes that can serve as a key.

There may be cases where a relation has several attribute combinations, each of which uniquely identifies all tuples of the relation. All of these attribute combinations are possible relationship keys. Any of the possible keys can be selected as primary.

Keys are commonly used for the following purposes:

Elimination of duplicate values \u200b\u200bin key attributes (other attributes are not taken into account);

Tuple orderings. It is possible to order in ascending or descending order of the values \u200b\u200bof all key attributes, as well as mixed ordering (by one - increasing, and by others - decreasing);

Organizations of linking tables.

The concept of a foreign key is important. A foreign key can be defined as a set of attributes of one relation R2, the values \u200b\u200bof which must coincide with the values \u200b\u200bof a possible key of another relation R1.

Attributes of the K2 relationship that make up the foreign key are not key for this relationship.

Links between relationships are established using foreign keys.

Designing databases of information systems is a rather laborious task. It is carried out on the basis of formalizing the structure and processes of the subject area, information about which is supposed to be stored in the database. Distinguish between conceptual and schematic-structural design.

Conceptual design of an IS DB is largely a heuristic process. The adequacy of the infological model of the subject area built within its framework is verified empirically, in the process of IS functioning.

Conceptual design stages:

* study of the subject area to form a general idea of \u200b\u200bit;

* selection and analysis of the functions and tasks of the developed IS;

* definition of the main objects-entities of the subject area
and the relationship between them;

* formalized presentation of the subject area.

When designing a relational database schema, the following procedures can be distinguished:

* determination of the list of tables and links between them;

* determination of the list of fields, types of fields, key fields of each table (table schema), establishing links between tables through foreign keys;

* establishment of indexing for fields in tables;

* development of lists (dictionaries) for fields with enumeration
data;

* setting integrity constraints for tables and relationships;

* normalization of tables, correction of the list of tables and links. Database design is carried out at the physical and logical levels. Design at the physical level is carried out by means of a DBMS and is often automated.

Logical design consists in determining the number and structure of tables, developing queries to the database, reporting documents, creating forms for entering and editing data in the database, etc.

One of the most important tasks of logical database design is data structuring. The following approaches to the design of data structures are distinguished:

* combining information about entity objects within one table (one relationship) with subsequent decomposition into several interconnected tables based on the normalization of relations;

* the formulation of knowledge about the system (definition of the types of initial data and relationships) and requirements for data processing, using the CA5E-system to obtain a finished database schema or even a finished applied information system;

* implementation of system analysis and development of structural models.

5. Purpose and principle of SQL operation

SQL (often pronounced "sequel", short for Structured Query Language) stands for Structured Query Language.

SQL is a tool designed to process and read data contained in a computer database. It is a language that makes it possible to effectively create and work with relational databases.

The world of databases is becoming more and more unified, which has led to the need for a standard language that can function in a large number of different kinds of computing environments. The standard language will allow users who know one set of commands to use them to create, search, modify, and transmit information, whether they are running on a personal computer, networked workstation, or mainframe. In an increasingly interconnected computing world, a user equipped with such a language has a huge advantage in using and synthesizing information from a variety of sources in a variety of ways.

As the name suggests, SQL is a programming language used to organize user interaction with a database. In fact, SQL only works with relational databases.

6. Infological model

When creating the infological model, the subject area of \u200b\u200bthe given database "Curricula of the studied disciplines of the direction of the PMI" was analyzed. 4 objects were singled out: Curriculum, Discipline, Student, Teacher, as well as two additional tables that link students and disciplines, as well as between teachers and disciplines. The Curriculum object has the attributes: Creation year, Curriculum number. The Discipline object has the following attributes: Discipline name, Discipline code, Curriculum number, Number of hours of lectures, Number of practice hours, Number of hours for laboratory work, Total hours, Number of hours per week, Discipline reporting form, Study semester. The Sudent object has the following attributes: Gradebook number, full name. And the Teacher object has the attributes: name, personnel number, department, position, phone. Objects Curriculum and Discipline are related in a 1: n relationship, objects Discipline and Student are related in a 1: n relationship, and objects Discipline and Teacher are related in a 1: n relationship.

ER-diagrams were used to describe the infological model:

Picture 1

7. Logical model

The logical model describes the concepts of the subject area, their relationship, as well as the restrictions on the data imposed by the subject area.

The logical data model is the initial prototype for the future database. It is built in terms of information units, but without reference to a specific DBMS. Moreover, the logical data model does not have to be expressed in terms of the relational data model.

To create a logical model, each object was assigned a table with a specific set of fields. Since the Objects Discipline and Teacher are related in a 1: n relationship, an additional table appears to represent the relationship between the objects Discipline and Teacher: Teaches.

In total, we have 4 objects and therefore there will be 4 tables for their presentation, the Curriculum object is only informational, since the work of the database is carried out with only one curriculum:

But there is a 1: n relationship between the two objects, so we need to introduce another table to represent the relationships between these tables. This is the Disciplina-Prepodavatel table and the Disciplina-Student table.

The presented database can be attributed to the 5th normal form, because it is 3 normal form and the primary key is simple. The logic diagram is implemented in Microsoft Access.

Figure 2

8. Structure of tables

The original database consists of 5 tables (we do not count the Curriculum table, since one curriculum is used).

Decoding of fields:

vDisciplina.db

Ш Nazv- discipline name, field type: String;

Ш Kod - unique discipline code: LongInt;

Ш Semestr - the semester in which it is taught: String;

Ш KolLeKCh - the number of lectures in this discipline: LongInt;

Ш KolPraktCh - the number of practices in this discipline: LongInt;

Ш KolLabRabCh - the number of lectures in this discipline: LongInt;

Ш VsegoCh - total number of hours: LongInt;

Ш NomerYP - the number of the curriculum, which contains the discipline: LongInt.

vStudent.db

Ш NomerStudBileta - student ID number: LongInt;

Ш FIO - student surname: ShortInt;

vPrepodaet.db (Disciplina-Prepodavatel)

Ш TabNomerPrepod - personnel number of the teacher who teaches the corresponding discipline: LongInt;

Ш FIO- name of the teacher who teaches the relevant discipline: String.

vPrepod.db

Ш FIO - Teacher's full name: String;

Ш TabelNomerPrepodavatelya - teacher's unique personnel number: LongInt;

Ш Kafedra - the department in which he works: String;

Ш Dolshnost - Position of the teacher: String;

Ш Telefon - teacher's contact phone: String.

v Izuchaet.db (Disciplina- Student)

Ш KodDiscip- discipline code: LongInt;

Ш NomerStudBileta - student ID number of the student studying the discipline: LongInt;

Ш FIO- full name of the student who is studying the relevant discipline: String;

Ш Ocenka - student's assessment in the studied discipline: LongInt;

9. Designing SQL queries

1. Form a list of credits and exams for each semester.

select Nazv, FormaOtchet

where Semestr \u003d: s and

(Disciplina.FormaOtchet \u003d "Pass" or Disciplina.FormaOtchet \u003d "Exam");

2. To form examination and test sheets / main and additional / for each subject.

Main sheet:

select Prepodaet.FIO,

Disciplina.ObsheeKolChVNed, Disciplina.Semestr, Izuchaet.FIO, Izuchaet.

Ocenka, Disciplina.Nazv

from Disciplina, Prepodaet, Izuchaet

where Disciplina.KodDiscip \u003d Prepodaet.KodDiscip

and (Disciplina.FormaOtchet \u003d "Exam" or Disciplina.FormaOtchet \u003d "Pass")

Additional statement (for students with 2):

select Disciplina.Nazv, Prepodaet.FIO,

Disciplina.ObsheeKolChVNed, Izuchaet.FIO, Disciplina.Semestr, Izuchaet.Ocenka

from Izuchaet, Disciplina, Prepodaet

where Izuchaet.Ocenka \u003d "2"

and Disciplina.KodDiscip \u003d Izuchaet.KodDiscip

and Disciplina.KodDiscip \u003d Prepodaet.KodDiscip

and (Disciplina.FormaOtchet \u003d "Exam" or Disciplina.FormaOtchet \u003d "Pass");

Update Disciplina

set ObsheeKolChVNed \u003d VsegoCh / 17;

4. Prepare an insert for the diploma of each student:

select Disciplina.Nazv, Izuchaet.Ocenka, Izuchaet.FIO

from Izuchaet, Disciplina

where Disciplina.KodDiscip \u003d Izuchaet.KodDiscip

and Disciplina.FormaOtchet \u003d "Exam"

Select AVG (Ocenka) as SrBall

Order by SrBall desc;

5. List the group in descending order of the average score:

Select FIO, AVG (Ocenka) as SrBall

Order by SrBall desc;

10. System structure and functions

Course work consists of one project "Project1" and 13 modules.

1. Unit1 - the form is stored here, which is the title page. Components used: Memo, Button.

2. Unit2 - the form is stored here, which is the initial page of the database. Components are used here: Button, Memo.

3. Unit3 - the form is stored here, which contains all database tables in the form of tabs. Components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

4. Unit4 - the form on which the task is displayed is stored here. Components are used here: Memo, Button.

5. Unit5 - the form is stored here, which displays the additional examination sheet. Components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

6. Unit6 - a form is stored here, which displays a list of exams and tests. Components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

7. Unit7 - the form that displays the main exam sheet is stored here. The components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

8. Unit8 - the form is stored here, which displays the list of the group in descending order. The components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

9. Unit9 - here the form is stored, which displays the insert in the diploma. Components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

10. Unit10 - a form is stored here, which displays a form for filling out an electronic statement. The components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

11. Unit11 - the form on which the menu is displayed is stored here. Components are used here: Memo, Button, Label.

12. Unit12 - a form is stored here, which displays a report on the creation of an electronic examination sheet. The components used here are Memo and Button, RVProject, RVQueryConnnection and Query.

13. Unit13 - a form is stored here, which displays a report on the creation of an insert in a diploma. The components used here are Memo and Button, RVProject, RVQueryConnnection and Query.

11. User manual

1. Launch the project. Before us appears the title page of the course work

Figure 3

Here we can immediately enter the database, or we can look at the task and return to this form. We select "Show task"

2. Before us appears a window with information on the assignment of term paper

Figure 4

3. Go back by clicking on the appropriate button and start work by clicking on the button of the previous window "Continue"

Figure 5

4. After reviewing the information on this page, click on the "Login" button

Figure 6

A window appears in front of us, containing on tabs all the information contained in the database. Here we can view the list of disciplines, study group, teachers, what students are studying and what subject are taught by teachers.

5. Also from this window we can go to queries. Click on the corresponding button.

Figure 7

Here we can select any request of interest simply by clicking on the appropriate button. Also here we can return to the tables and proceed to the formation of the electronic version of the examination sheet.

6. Click on the button "View the list of credits and exams for each semester"

Figure 8

Here you need to enter the semester number and click on the button below, then a list of exams and tests for the semester you entered will appear in the table window. From this sheet, we can go back to queries.

7. Click on the button "Go to the main examination sheet"

Here you need to select the name of the discipline from the drop-down list, then enter this name in the box under the drop-down list and click on the "Execute" button. The request will show the total number of hours per week for studying the discipline you entered, as well as display a list of students studying this discipline and their grade for this discipline. Attention! The grade field is filled in on the form with tables inside the Student-Discipline tab. Also on this sheet you can form an additional examination sheet. For students with a grade 2. From this sheet we can go back to the queries.

Figure 9

8. Click on the button "Go to the additional examination sheet"

Figure 10

Here we can see students who have a grade of 2 in a particular discipline. From this sheet, we can go back to queries.

9. Click on the button "Create an insert for the diploma"

Figure 11

Here you need to enter the full name of the graduate by selecting the appropriate student from the drop-down list. Then press the key. And then, upon request, the "Discipline" column will be filled with a list of disciplines studied for 5 years of study, and the corresponding marks will appear. On the same sheet, you can view the electronic version of the insert by clicking on the "Print version" button. After viewing this version, you just need to close the window that opens with a red cross in the upper right corner of the screen.

Figure 12

10. Click on the button "View the group list in descending order of the average score"

Figure 13

Here we see a list of the group and the average grade corresponding to each student, in descending order. From this sheet, we can go back to queries.

Figure 14

Select the discipline we are interested in from the drop-down list, for example, economics and enter its name in the box under the drop-down list. Then press the execute button and see the name of the teacher of this discipline.

Figure 16

Here we see the name of the discipline we have chosen, the full name of the teacher of this discipline. And also the list of students who studied this discipline. To exit the report, simply click on the cross in the upper right corner of the screen. We return to the previous sheet. From this sheet, we can go back to queries.

12. Creating tables

To create tables, the Database Desktop utilities were used. You can run it - Start / Programs / Borland Delphi 7 / Database Desktop. The utility's working directory must be configured. Select the File / Working Directory command and set your working directory. To create a table, select the File / New / Table command. Then you need to select the type of table. PARADOX 7 type can be considered the best for file-server tables.

1. Create YchebPlan table (Curriculum):

Figure 17

5. Creation of table Disciplina (Discipline):

Figure 18

6. Creating the Student table:

Figure 19

7. Creating the Prepodaet table (Discipline-Teacher):

Figure 20

5. Creating a Prepod table (Teacher):

Figure 21

8. Creation of the Izuchaet table (Discipline-Student):

Figure 22

13. Creating an application in Delphi

In order to create a new application, select the New / Application item in the File menu. A form and a module appear (in general, this is called a project), now you can place the necessary components on the form. If necessary, you can create another form (and more than one), for this you need to select the New / Form item in the File menu.

1. Table. Filling with data. Data display.

In order to display a table on a form, you need to place components on it:

· Table (on the BDE tab) - In the object inspector, on the "Parameters" tab, in the Tablename property, select the required table.

Figure 23

· DBGrid (on the DataControls tab) - is required to display the table on the form, in the Object Inspector, in the DataSource property, specify the required data source.

Figure 24

· DBNavigator (on the DataControls tab) - required to navigate through table records. In the Object inspector, the DataSource property specifies the same data source as in the DBGrid. The functions of the navigator are available by clicking on its buttons while the application is running. The component contains 10 buttons.

Figure 25

· DataSource (Data Access tab) - an intermediate level component for data access. Serves as an intermediary between DBMS tables and on-screen controls (DBGrid, DBNavigator).

Figure 26

14. Creating a field with information (Memo) and buttons

The Memo component is placed on the form, which is located on the Standard tab.

Figure 27

In the object inspector on the "Parameters" tab, in the Lines property, enter the text required for display

Figure 28

Creation of buttons.

To close the form correctly, the Button component is placed on it, which is located on the Standard tab.

Figure 29

In order for the button to work, you must specify in the OnClick event handler:

procedure TForm1.N5Click (Sender: TObject);

begin

Form2.Show;

Form1.Close;

end;

15. Creating signatures for tables

To sign the table in the course work, the Lable component was used, located on the Standard tab. In the Object Inspector, in the Caption property, you just need to write the text.

Figure 30

16. Create a dropdown list

To select a command from the available list, use the ComboBox component (drop-down list). It can be filled in this way

In the Object Inspector, in the Items property, you need to write:

Figure 31

16. Generating reports

The report is created using the QReports tool, which must be connected from the beginning: Component-\u003e install packages-\u003e add open the bin folder select the dclqrt70.bpl file click OK and then a tab with QReport components will appear. Components I use:

table 2

17. Program Listing

Project Description

program Project1;

uses

Forms,

Unit1 in "Unit1.pas" (Form1),

Unit2 in "Unit2.pas" (Form2),

Unit3 in "Unit3.pas" (Form3),

Unit4 in "Unit4.pas" (Form4),

Unit5 in "Unit5.pas" (Form5),

Unit6 in "Unit6.pas" (Form6),

Unit7 in "Unit7.pas" (Form7),

Unit8 in "Unit8.pas" (Form8),

Unit9 in "Unit9.pas" (Form9),

Unit10 in "Unit10.pas" (Form10),

Unit11 in "Unit11.pas" (Form11),

Unit12 in "Unit12.pas" (Form12),

Unit13 in "Unit13.pas" (Form13),

Unit14 in "Unit14.pas" (Form14);

($ R * .res)

begin

Application.Initialize;

Application.CreateForm (TForm1, Form1);

Application.CreateForm (TForm2, Form2);

Application.CreateForm (TForm3, Form3);

Application.CreateForm (TForm4, Form4);

Application.CreateForm (TForm5, Form5);

Application.CreateForm (TForm6, Form6);

Application.CreateForm (TForm7, Form7);

Application.CreateForm (TForm8, Form8);

Application.CreateForm (TForm9, Form9);

Application.CreateForm (TForm10, Form10);

Application.CreateForm (TForm11, Form11);

Application.CreateForm (TForm12, Form12);

Application.CreateForm (TForm13, Form13);

Application.CreateForm (TForm14, Form14);

Application.Run;

end.

Unit1 module description

unit Unit1;

interface

uses

Dialogs, StdCtrls;

type

TForm1 \u003d class (TForm)

Memo1: TMemo;

Button1: TButton;

Button2: TButton;

Button3: TButton;

private

(Private declarations)

public

(Public declarations)

end;

var

Form1: TForm1;

implementation

uses Unit2, Unit4, Unit6, Unit7, Unit5, Unit8, Unit9, Unit10;

($ R * .dfm)

procedure TForm1.Button3Click (Sender: TObject);

begin

Form2.show;

end;

procedure TForm1.Button2Click (Sender: TObject);

begin

Form1.Close;

end;

procedure TForm1.Button1Click (Sender: TObject);

begin

Form4.show;

end;

end.

Unit2 module description

unit Unit2;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm2 \u003d class (TForm)

Memo1: TMemo;

GroupBox1: TGroupBox;

Button1: TButton;

Button2: TButton;

procedure Button1Click (Sender: TObject);

procedure Button2Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form2: TForm2;

implementation

uses Unit3;

($ R * .dfm)

procedure TForm2.Button1Click (Sender: TObject);

begin

Form3.show;

Form2.Close;

end;

procedure TForm2.Button2Click (Sender: TObject);

begin

Form2.Close;

end;

Unit3 module description

unit Unit3;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, ComCtrls, ExtCtrls, DBCtrls, Grids, DBGrids, DB, DBTables,

StdCtrls, QuickRpt, QRCtrls;

type

TForm3 \u003d class (TForm)

PageControl1: TPageControl;

TabSheet1: TTabSheet;

TabSheet2: TTabSheet;

TabSheet3: TTabSheet;

TabSheet4: TTabSheet;

TabSheet5: TTabSheet;

TabSheet6: TTabSheet;

DataSource1: TDataSource;

DataSource2: TDataSource;

DataSource3: TDataSource;

DataSource4: TDataSource;

Table1: TTable;

Table2: TTable;

Table3: TTable;

Table4: TTable;

DBGrid1: TDBGrid;

DBNavigator1: TDBNavigator;

DBGrid2: TDBGrid;

DBNavigator2: TDBNavigator;

DBGrid3: TDBGrid;

DBNavigator3: TDBNavigator;

DBGrid4: TDBGrid;

DBNavigator4: TDBNavigator;

DBGrid5: TDBGrid;

DBNavigator5: TDBNavigator;

DBGrid6: TDBGrid;

DBNavigator6: TDBNavigator;

Button1: TButton;

DataSource5: TDataSource;

DataSource6: TDataSource;

Table5: TTable;

Table6: TTable;

Query1: TQuery;

Button2: TButton;

Label1: TLabel;

Memo1: TMemo;

Label3: TLabel;

Button3: TButton;

procedure Button1Click (Sender: TObject);

procedure Button2Click (Sender: TObject);

procedure Button3Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form3: TForm3;

implementation

uses Unit5, Unit11;

($ R * .dfm)

procedure TForm3.Button1Click (Sender: TObject);

begin

Form11.show;

Form3.close;

end;

procedure TForm3.Button2Click (Sender: TObject);

begin

Query1.ExecSQL;

Form3.Refresh;

end;

procedure TForm3.Button3Click (Sender: TObject);

begin

Form3.close;

end;

Unit4 module description

unit Unit4;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm4 \u003d class (TForm)

Memo1: TMemo;

Button1: TButton;

procedure Button1Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form4: TForm4;

implementation

uses Unit1;

($ R * .dfm)

procedure TForm4.Button1Click (Sender: TObject);

begin

Form1.show;

end;

Unit 5 module description

unit Unit5;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls, Mask, DBCtrls, ExtCtrls;

type

TForm5 \u003d class (TForm)

DataSource1: TDataSource;

DBGrid1: TDBGrid;

Query1: TQuery;

DBEdit1: TDBEdit;

DBEdit2: TDBEdit;

DBEdit3: TDBEdit;

Label1: TLabel;

Label2: TLabel;

Label3: TLabel;

Label4: TLabel;

DBNavigator1: TDBNavigator;

Button1: TButton;

procedure ComboBox1Change (Sender: TObject);

procedure Edit1Change (Sender: TObject);

procedure Button1Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form5: TForm5;

implementation

uses Unit11;

($ R * .dfm)

procedure TForm5.ComboBox1Change (Sender: TObject);

begin

Query1.Active: \u003d true;

end;

procedure TForm5.Edit1Change (Sender: TObject);

begin

Query1.Open;

end;

procedure TForm5.Button1Click (Sender: TObject);

begin

Form11.show;

Form5.Close;

end;

Unit 6 description

unit Unit6;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls, ExtCtrls, DBCtrls;

type

TForm6 \u003d class (TForm)

Button1: TButton;

Edit1: TEdit;

DataSource1: TDataSource;

DBGrid1: TDBGrid;

Query1: TQuery;

Label1: TLabel;

DBNavigator1: TDBNavigator;

Label2: TLabel;

Memo1: TMemo;

Button2: TButton;

Label3: TLabel;

procedure Button1Click (Sender: TObject);

procedure Button2Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form6: TForm6;

implementation

uses Unit11;

($ R * .dfm)

procedure TForm6.Button1Click (Sender: TObject);

begin

Query1.Close;

if not Query1.Prepared then

Query1.Prepare;

if length (edit1.text)<>0 then

else

begin

Query1.Params.Value: \u003d 0;

end;

Query1.Open;

end;

procedure TForm6.Button2Click (Sender: TObject);

begin

Form11.show;

Form6.Close;

end;

Unit 7 module description

unit Unit7;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls, Grids, DBGrids, DBTables, DB, Mask, DBCtrls, ExtCtrls,

QRCtrls, QuickRpt;

type

TForm7 \u003d class (TForm)

Label1: TLabel;

Label2: TLabel;

DataSource1: TDataSource;

Query1: TQuery;

Edit2: TEdit;

Button1: TButton;

DBEdit1: TDBEdit;

DBEdit2: TDBEdit;

Label3: TLabel;

DBGrid1: TDBGrid;

Label4: TLabel;

Label5: TLabel;

DBNavigator1: TDBNavigator;

Button2: TButton;

Label6: TLabel;

Label7: TLabel;

Memo1: TMemo;

ComboBox1: TComboBox;

Label8: TLabel;

Button3: TButton;

procedure Button1Click (Sender: TObject);

procedure Button2Click (Sender: TObject);

procedure Button3Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form7: TForm7;

implementation

uses Unit5, Unit11;

($ R * .dfm)

procedure TForm7.Button1Click (Sender: TObject);

begin

Query1.Close;

if not Query1.Prepared then

Query1.Prepare;

if length (edit2.text)<>0 then

Query1.Params.Value: \u003d edit2.Text

else

begin

Query1.Params.Value: \u003d 0;

edit2.Text: \u003d "Please enter a title!";

end;

Query1.Open;

end;

procedure TForm7.Button2Click (Sender: TObject);

begin

Form5.show;

Form7.close;

end;

procedure TForm7.Button3Click (Sender: TObject);

begin

Form11.show;

Form7.close;

end;

Unit 8 module description

unit Unit8;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

type

TForm8 \u003d class (TForm)

Label4: TLabel;

DataSource1: TDataSource;

Query1: TQuery;

DBGrid1: TDBGrid;

DBNavigator1: TDBNavigator;

Button1: TButton;

Memo1: TMemo;

procedure Button1Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form8: TForm8;

implementation

uses Unit11;

($ R * .dfm)

procedure TForm8.Button1Click (Sender: TObject);

begin

Form11.show;

Form8.close;

end;

Unit 9 module description

unit Unit9;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, Grids, DBGrids, DB, DBTables, StdCtrls, Mask, DBCtrls, ExtCtrls;

type

TForm9 \u003d class (TForm)

Edit1: TEdit;

Query1: TQuery;

DataSource1: TDataSource;

DBGrid1: TDBGrid;

Button1: TButton;

Query2: TQuery;

DataSource2: TDataSource;

Button2: TButton;

DBEdit1: TDBEdit;

DBNavigator1: TDBNavigator;

Label1: TLabel;

Label2: TLabel;

Label3: TLabel;

Name: TComboBox;

Button3: TButton;

Memo1: TMemo;

Label4: TLabel;

Button4: TButton;

procedure Button1Click (Sender: TObject);

procedure Button2Click (Sender: TObject);

procedure Button3Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form9: TForm9;

implementation

uses Unit11, Unit13;

($ R * .dfm)

procedure TForm9.Button1Click (Sender: TObject);

begin

Query1.Close;

if not Query1.Prepared then

Query1.Prepare;

if length (edit1.text)<>0 then

Query1.Params.Value: \u003d edit1.Text

else

begin

Query1.Params.Value: \u003d 0;

edit1.Text: \u003d "Please enter the alumni name!";

end;

Query1.Open;

end;

procedure TForm9.Button2Click (Sender: TObject);

begin

Query2.Close;

if not Query2.Prepared then

Query2.Prepare;

if length (edit1.text)<>0 then

Query2.Params.Value: \u003d edit1.Text

else

begin

Query2.Params.Value: \u003d 0;

edit1.Text: \u003d "Please enter the semester number!";

end;

Query2.Open;

end;

procedure TForm9.Button3Click (Sender: TObject);

begin

Form11.show;

Form9.close;

end;

procedure TForm9.Button4Click (Sender: TObject);

begin

Form13.QuickRep1.Preview;

end;

Unit 10 module description

unit Unit10;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, ExtCtrls, QuickRpt, StdCtrls, DB, DBTables, Mask, DBCtrls,

Grids, DBGrids;

type

TForm10 \u003d class (TForm)

Button1: TButton;

Query1: TQuery;

DataSource1: TDataSource;

DBEdit1: TDBEdit;

DBEdit2: TDBEdit;

Label1: TLabel;

Label2: TLabel;

Edit1: TEdit;

Button2: TButton;

Label3: TLabel;

ComboBox1: TComboBox;

Label4: TLabel;

Label5: TLabel;

Memo1: TMemo;

Label6: TLabel;

Label7: TLabel;

Button3: TButton;

procedure Button1Click (Sender: TObject);

procedure Button2Click (Sender: TObject);

procedure Button3Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form10: TForm10;

implementation

uses Unit3, Unit7, Unit12, Unit11;

($ R * .dfm)

procedure TForm10.Button1Click (Sender: TObject);

begin

Form12.QuickRep1.Preview;

end;

procedure TForm10.Button2Click (Sender: TObject);

begin

Query1.Close;

if not Query1.Prepared then

Query1.Prepare;

if length (edit1.text)<>0 then

Query1.Params.Value: \u003d edit1.Text

else

begin

Query1.Params.Value: \u003d 0;

edit1.Text: \u003d "Please enter a title!";

end;

Query1.Open;

end;

procedure TForm10.Button3Click (Sender: TObject);

begin

Form11.show;

end;

Unit 11 module description

unit Unit11;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm11 \u003d class (TForm)

Button1: TButton;

Button2: TButton;

Button3: TButton;

Button4: TButton;

Button5: TButton;

Button6: TButton;

Memo1: TMemo;

Label1: TLabel;

Label2: TLabel;

Label3: TLabel;

Button7: \u200b\u200bTButton;

Label4: TLabel;

Label5: TLabel;

procedure Button2Click (Sender: TObject);

procedure Button1Click (Sender: TObject);

procedure Button4Click (Sender: TObject);

procedure Button3Click (Sender: TObject);

procedure Button5Click (Sender: TObject);

procedure Button6Click (Sender: TObject);

procedure Button7Click (Sender: TObject);

private

(Private declarations)

public

(Public declarations)

end;

var

Form11: TForm11;

implementation

About SQL

SQL is a programming language that is used to create databases. If you install it on your computer and start creating a database, it will not be very convenient. This is due to the fact that SQL itself does not have any graphical shell, and queries to the database must be sent in general via the command line. For this reason, various kinds of programs have appeared that simplify the development of databases. However, learning the basics of this language is still worth it. Suddenly you need to make a request, and the program does not work correctly.

Microsoft Access

This program for creating databases is exactly familiar to many. After all, it comes in the Microsoft Office suite of programs. This program is one of the easiest to learn, because knowledge of the SQL programming language is practically not needed there. You can only designate which query to make, and the program will compose the SQL query itself.

About the relevance of the program. Until now, the databases of many organizations have been made using Microsoft Access. Indeed, the program itself is very light, there is an intuitive interface. What's more, the basics of using Access are even taught in school and in early college!

PhpMyAdmin

Access, of course, is a good program, but if you need a database for a website, it won't do it. Then PhpMyAdmin comes to the rescue. It is a very useful program for creating databases. Installation on a computer takes some time, and during installation it is easy to do something wrong and will not work. Therefore, when installing this program to create databases, you must strictly follow the instructions. But another plus of PhpMyAdmin is that it can be accessed via the Internet as a website! For example, you have a website that is powered by WordPress. He will have a database. And if you have a site on some good hosting, then, most likely, work with databases will be carried out through PhpMyAdmin, and it will be possible to access it through the hosting control panel.

Another program for creating databases. It's free, but there is also a paid version with improved features. It is easy to create relationships with tables in this program, and in general, it is just convenient to work with. Also a plus is that you can show the database in graphical form. Most people when working with databases prefer this particular program. In principle, PhpMyAdmin is not inferior in capabilities, but still it is more intended for working with a database of sites.

This article has covered the basic programs for creating databases. In fact, there are a lot of them, so everyone chooses a tool for themselves, but if you are just getting started and want to study this area, then it is recommended to work with MySQL WorkBench. Once you learn the basics of SQL, it won't make much of a difference for you where to work, because the queries are the same everywhere. It is also convenient that, having created a database in one program, you can open it through another software, which is also designed to work with the database. When creating software with a database, you cannot do without this knowledge. Moreover, having mastered SQL, you can even create your own software for developing and editing databases.

DEVELOPMENT OF AN APPLICATION FOR WORKING WITH DATABASES

Roza Gaynanova

lecturer of the Department of General Educational Disciplines

Kazan national research Technological University

Russia, Kazan

ANNOTATION

The article discusses the methods of accessing databases and the programming interfaces used in these access methods. Integration of Visual C # applications with the Microsoft SQL Server 2012 DBMS is considered. Development of the information system "Travel Agency" is considered as an example.

ABSTRACT

The article examines the methods of access to databases and the software interfaces used in these access methods. We consider the integration of Visual C # applications with the Microsoft SQL Server 2012 database server. As an example the development of the "Tourist Agency" information system is considered.

Keywords: database, SQL Server, application, users, control, query.

Keywords: database, SQL Server, application, users, control element, query.

An information system is an interconnected set of tools, methods and personnel used to store, process and issue information in order to achieve a set goal. The developed information system will be built using client-server technology. In such systems, information is stored on a server, and the information system interface is stored on client computers, through which information system users gain access to data.

When developing an information system, two main tasks have to be solved:

the task of developing a database for storing information;
the task of developing a graphical user interface for client applications.

The database "Travel agency" is created on Microsoft SQL Server 2012. The database stores information about the clients of this travel agency (tourists), about the tours it offers, about the registration and payment of vouchers. At the stage of database design, tables "Tourists", "Tours", "Vouchers", "Seasons", "Payment" are created. Links are established between tables.

The travel agency application will be used by the travel agency manager, sales managers, accountant, cashier and travel agency office staff. One of the office employees is appointed by the system administrator. Only he will maintain user accounts. In addition to the five main tables, the "Users" table is created, which contains information about the database users. This table is not related to other tables. The structure of this table: user code, surname, position, login and password. Only a system administrator can make changes to this table.

SQL Server security is built on two concepts: authentication and authorization. The SQL Server security system administrator creates a separate login object for each user. This object contains the SQL Server user account name, password, fully qualified name, and other attributes that control access to SQL Server databases. By connecting to SQL Server, the user gains access to the databases in which his account is registered. To register an account with a specific database, the system administrator creates a database username there and associates it with a specific account. The system administrator gives users certain privileges. A sales manager can make changes to the tables "Tourists", "Tours" and change the column "Number of_seats" in the table "Tours" after the sale of the next tour. An employee of the travel agency can make changes to the tables "Seasons" and "Tours". Accountant and cashier - in the "Payment" table.

You can grant permissions in SQL Server Management Studio by opening the properties windows for the corresponding user. You can also represent permissions using the GRANT statement. Examples of granting authority to a manager. The following statement grants the Menedger user the right to view, modify the Tourists table, insert new rows, and delete obsolete data.

USE Travel Agency

GRANT SELECT, UPDATE, INSERT, DELETE

ON Tourists

A similar instruction is created for working with the "Tours" table. To give the manager the right to change only one column of the "Tours" table, the name of the variable column Tours (Number of places) is indicated in brackets after the table name. Provided SELECT, UPDATE operations.

There are four instructions in the Data Control Language (DCL): COMMIT, ROLLBACK, GRANT, REVOKE. All of these instructions are related to protecting the database from accidental or malicious damage. Databases are vulnerable exactly when changes are made to them. To protect the database, SQL is limited to operations. Which can change it, so that they are executed only within transactions. When multiple users try to use the same database table at the same time, a concurrent access situation is created. Concurrency problems arise even in relatively simple applications if the application is installed and running on a multi-user system that does not have sufficient concurrency control. No transaction conflict occurs if they are executed sequentially.

One of the main tools for maintaining database integrity is a transaction. A transaction encapsulates all SQL statements that can affect the database. The SQL transaction ends with one of two statements: COMMIT or ROLLBACK. If the transaction ends with a ROLLBACK statement, then all of its statements are canceled and the database is returned to its original state. A normal transaction can run in one of two modes: READ-WRITE (read-write) or READ-ONLY (read-only). The transaction can be set to one of the following isolation levels: SERIAIZABLE (sequential execution), REPEATABLE READ (repeated read), READ UNCOMMITED (read unconfirmed data). The default features are READ-WRITE and SERIAIZABLE. The default SQL transaction characteristics are generally appropriate for most users.

The application is created in Visual Studio 2012 using the C # programming language. Designing a software product begins with developing a user interface.

The main application window should call the main functions for working with the application (Figure 1). The menu is used to perform these operations. The menu consists of the following items: "Tables", "Queries", "Reports". Each of these points contains sub-points. Each function will be executed in its own window. The MenuStrip element is installed on the main application window, menu options are formed. The PictureBox element is placed on the form window. A picture is loaded into the element area. The drawing should cover the entire area. The SizeMode property sets the scaling of the picture. For this property, StretchImage is selected from the drop-down list, the drawing is scaled so that it occupies the entire surface of the object.

The comboBox control is installed to display a list of users who have the right to work with the Travel Agency database. The comboBox element binds to a data source. The window “ FROMomboBox Tasks", In which the" Use data-bound items "check box is selected, if this check box is selected, the data binding options open. The comboBox item is bound to the Users table, and Last Name is selected in the Display Member row. To enter the login, the textbox1 control is installed, to enter the password textBox2. For textBox1 and textBox2, the UsesSystemPasworChar property is set to true, which specifies whether the text in the text box should be displayed using the default password characters. Two command buttons “Login” and “Change user” are installed.

When you bind the comboBox element to the "Users" table, the Form1_Load event handler appears in the form's program code.

private void Form1_Load (object sender, EventArgs e)

this.usersTableAdapter1.Fill (this.tour agencyDataSet10.Users);

The menu is not available when starting the application. To enter the system, you need to enter user data and click the "Login" button. When the form loads, the user surnames contained in the Users table are loaded into the comboBox1 control. In this handler, lines are added that make menus inaccessible, the "Change user" button and in the comboBox1 element no item is selected:

menuStrip1.Enabled \u003d false; comboBox1.SelectedIndex \u003d -1;

button2.Enabled \u003d false;

Figure 1. View of the main application window

When you click the "Login" button, it is checked whether there is a user in the "Users" table with this last name, and whether the login and password are entered correctly. The form class description area describes the parameters passed to the sql command. These are three parameters: the user's last name, username and password.

private string parfam, parpasw, parlog;

The line is added to the namespace:

using System.Data.SqlClient;

// Event handler for clicking the "Login" button

string sql \u003d "";

string connstr \u003d @ "Data Source \u003d B302CN-8 \\ TEST_SQL; Initial Catalog \u003d Travel Agency; Integrated Security \u003d True";

SqlDataReader cmReader;

parfam \u003d comboBox1.Text; parlog \u003d textBox1.Text;

SqlConnection conn \u003d new SqlConnection (connstr);

sql \u003d "SELECT Surname, Login, Password FROM Users" +

"WHERE (Last name \u003d @fam) and (Password [email protected])";

SqlCommand cmdkod \u003d new SqlCommand (sql, conn);

cmdkod.Parameters.Add (new SqlParameter ("@ fam", SqlDbType.NChar, 25));

cmdkod.Parameters ["@ fam"]. Value \u003d parfam;

cmdkod.Parameters.Add (new SqlParameter ("@ pasw", SqlDbType.NChar, 10));

cmdkod.Parameters ["@ pasw"]. Value \u003d parpasw;

cmdkod.Parameters.Add (new SqlParameter ("@ log", SqlDbType.NChar, 15));

cmdkod.Parameters ["@ log"]. Value \u003d parlog;

if (! cmReader.Read ())

MessageBox.Show ("Wrong password!");

cmReader.Close (); conn.Close ();

menuStrip1.Enabled \u003d true; comboBox1.SelectedIndex \u003d -1;

button1.Enabled \u003d false; button2.Enabled \u003d true;

textBox1.Text \u003d ""; textBox1.Enabled \u003d false;

textBox2.Text \u003d ""; textBox2.Enabled \u003d false;

comboBox1.Enabled \u003d false;

cmReader.Close ();

private void button2_Click (object sender, EventArgs e)

menuStrip1.Enabled \u003d false; comboBox1.Enabled \u003d true;

textBox1.Enabled \u003d true; textBox2.Enabled \u003d true;

button1.Enabled \u003d true; button2.Enabled \u003d false;

Description of the operation of the event handler for pressing the "Enter" button.

The connstr string contains the connection string. The sql line contains the text of the generated query, starting with the select statement, after which the selectable fields from the tables that are specified after the from word are listed.

The handler creates a new instance of the SqlConnection object that provides a connection to the SQL server. The SqlCommand object contains a command with three parameters for searching the Users table for a user with the given last name, login, and password. The button1_Click handler opens a SqlConnection. The handler then executes the SQL command stored in the cmdkod object.

cmReader \u003d cmdkod.ExecuteReader ();

As a result of executing the ExecuteReader method, an object of the SqlDataReader class is created, which allows you to sequentially read all the lines of the SQL command execution. For selection, the SqlDataReader method is used. Read. If the "Users" table does not contain any records with the specified surname, login and password, then the cmReader.Read () method will return false. This means that you entered an incorrect username or password. In this case, a message about incorrect data entered is displayed, the cmReader and SqlConnection objects are closed. If the user data is entered correctly, the menu and the button "Change user" become available. The "Login" button becomes unavailable. The elements textBox1 and textBox2 are cleared and made inaccessible. The comboBox1 element also becomes inaccessible (Figure 2)

Figure 2. View of the main window after user login

The tables and query results will be displayed in the DataGridView controls. The main purpose of these elements is to link to tables of external data sources, primarily to database tables. For the convenience of viewing and entering new entries, the tables "Seasons", "Tours" and "Tours", "Payment" will be displayed two in one window. Each DataGridView control binds to a corresponding table in the Travel Agency database. In this window, the table "Tourists" is selected (Figure 3). After completing the connection (clicking Finish), the DataSet, BindingSource, and TableAdapter components appear on the form. These components are not visual, so they are displayed in the additional panel. DataSet is a specialized object containing a database image. To implement the interaction between the DataSet and the actual data source, an object of the TableAdapter type is used. The very name of this object - adapter, converter - indicates its nature. The TableAdapter contains Fill and Update methods that perform forward and backward data transfers between the DataSet and a table stored in the server's SQL database. The Fiil method fills the DataSet with data from the SQL Server, and Update updates the SQL Server database with data from the local DataSet. The BindingSource component makes it easy to bind controls on a form to data. The main property of the BindingSource component is the Data Source property, which points to the data source.

After the tables are connected to the data sources, the Form2_Load event handler appears in the form's program code.

private void Form2_Load (object sender, EventArgs e)

this.touristsTableAdapter.Fill (this.travel agencyDataSet9.Tourists);

When the form loads, the data contained in the Tourists table is displayed on the DataGridView control on the Form2 form window. You can make changes to the table and add new records. After making changes, click on the "Save Tourists" button. The event handler for clicking the "Save Tourists" button:

private void button1_Click (object sender, EventArgs e)

seasonsTableAdapter.Update (travel agencyDataSet9);

MessageBox.Show ("Data saved");

Figure 3. View of the window with the "Tourists" table

Each request is displayed in a separate window. On the Form1 window, a new item with the name of the request is added to the Queries menu. If the query has no parameters, a DataGridView control is installed on the form window to display the results of the query execution and binds to the appropriate procedure or database function.

This article describes some methods of developing applications that work with databases, a way to organize access to work with a limited circle of people, ways to integrate Visual C # applications with a Microsoft SQL Server 2012 DBMS. When you use the Visual C # programming language with SQL, you can create powerful applications with a wide range of capabilities. The main strength of SQL lies in data retrieval. No matter how many rows there are in the table, you can retrieve them with a single SELECT statement. At the same time, the main disadvantage of the SQL language is its underdeveloped user interface. Using procedural languages, you can create user-friendly interfaces for entering and viewing data. The most common technique for combining SQL with procedural languages \u200b\u200bis called SQL injection. The SQL statement is inserted at the desired location in the procedural program. Information must flow between a program written in a procedural language and the SQL code. Basic variables are used for this. For SQL to recognize these variables, they must be declared. Variables are declared in the class description area of \u200b\u200bthe form before the code description. In code, the newly created SqlConnection object provides a connection to the SQL server. The SqlCommand object provides execution of the embedded SQL command.

Bibliography:

Allen Taylor. SQL for Dummies, 8th Edition: Per. from English. - M .: LLC “I.D. Williams ”, 2014. - 416 p.
Gaynanova R.Sh. Development of applications for working with databases MS SQL Server 2012 // Fundamental and applied sciences today: Proceedings of the XI international practical conference (April 10-11, 2017 Noth Charleston, USA), volume 3 - p. 34-41.
Frolov A.V., Frolov G.V. Visual design of C # applications. - M .: KUDRITS-OBRAZ, 2003, - 512s.