Programs working with database. Overview of programs for creating databases

Almost every organization has its own database. Why, even websites use them to make working with information easier and simpler. Indeed, they allow you to make calculations without any problems, quickly find the necessary data, and in general, they simply create order in any information.

Often programmers are involved in their creation, because this is a complex process that is taught in higher educational institutions. There are also many lessons, courses and software packages for creating database software. There is really a great variety, it can be easy to get confused. This article will focus on some of the main programs for developing databases.

About SQL

SQL is a programming language that is used to create databases. If you install it on a 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 database queries must be sent at all through 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 some kind of request, but the program does not work correctly.

Microsoft Access

This program for creating databases is definitely familiar to many. After all, she comes in a package Microsoft programs office. This program is one of the easiest to learn, because knowledge of the SQL programming language is practically not needed there. You can only indicate which query to make, and the program itself will make an SQL query.

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 easy, there is an intuitive interface. Moreover, the basics of working in Access are even taught in school and in elementary college courses!

PhpMyAdmin

Access, of course, is a good program, but if you need a database for a site, it will not cope. Then PhpMyAdmin comes to the rescue. This is very useful program to create databases. Installation on a computer takes some time, and during installation it is easy to do something wrong, and it will not work. Therefore, when installing this program to create databases, you must clearly follow the instructions. But another plus of PhpMyAdmin is that it can also be accessed via the Internet in the form of a website! For example, you have a website that is powered by WordPress. It 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 is free, but there is also a paid version with better features. It is easy to create links with tables in this program, and in general, it is just convenient to work with. It is also a plus that you can show the database in a graphical form. Most people when working with databases prefer this particular program. In principle, PhpMyAdmin is not inferior in terms of capabilities, but still it is more designed to work with site databases.

In this article, the main programs for creating databases were considered. In fact, there are a huge number of them, so everyone chooses a tool for themselves, but if you are just getting used to it and want to study this area, then it is recommended to work with MySQL Workbench. After you study SQL basics, for you there will no longer be a significant difference where to work, because the requests 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.

Let's create a simple database application that displays information from the Tourists table and the Tourist Info table entry associated with the current record in the Tourists table from a Microsoft Access database.

To do this, we will create an empty Windows application. Appearance of the environment

development is shown in Figure 39.

Rice. 39. Empty application

Figure 39 highlights the group of components "Data" ("Data"), which contains components for accessing and manipulating data.

The binding of the database data to the form is carried out by the "Binding Source" component. Let's transfer it to the form. After placing it on the form, the development environment takes the following form (Fig. 40).

Rice. 40. The Binding Source component on the form

The component is non-visual, so it is displayed on additional panel. The main property of the component is the DataSource property, which points to the data source. By default, the property is empty, so you need to form its value. When this property is selected, the following window appears in the properties window (Fig. 41).

Rice. 41. List of data sources

The list is currently empty, so you need to create a new data source by selecting the "Add Project Data Source" command to create a new data source and connect to it. The following dialog box appears (Fig. 42).

Rice. 42. List of data sources

This dialog provides the following selection of data sources:

Database - Database;

Service - Service, this is some service that provides data. Most often this is a Web service;

Object - An object for selecting an object that will generate data and objects for working with them.

In our case, you need to select the "Database" item. The data connection selection window appears (Fig. 43).

Rice. 43. Selecting a data connection

The purpose of this dialog is to create a connection string that describes the connection parameters for the ADO engine, such as the type of database, its location, usernames, security features, and so on.

The drop-down list of the dialog contains all previously created connections. If the required connection is not in the list, then the "New connection" button should be used. Pressing the button leads to the appearance of the following dialog (Fig. 44).

This dialog selects the type of data source (in this case Microsoft Access), the name of the database (in this case the name and location of the database file), the username and password used to connect to the database. The "Advanced" button allows you to set a large number of parameters related to various parts of the ADO engine. Using the "Test Connection" button will make sure that the entered parameters are correct and the connection is working.

Rice. 44. Create a new connection

The last step of the dialog is the selection of those tables or other database objects that are needed in given source data. The selection window is shown in Figure 45.

Rice. 45. Selecting the necessary tables

In this window, the tables "Tourists" and "Information about tourists" are selected. Since no objects other than tables have been created in the database, only tables are shown in Figure 45. This completes the creation of the data source. After clicking the "Finish" button next to the BindingSource component, the DataSet component appears on the form.

Now the data connected above needs to be displayed on the form. The simplest way to display data is to use the DataGridView component from the Data component group. The component is visual and looks like this on the form (Fig. 46).

Rice. 46. ​​DataGridView Component

The component settings window immediately appears, which determines its data editing capabilities: “Enable editing” (“Enable Adding”), “Enable editing” (“Enable Editing”), “Enable deletion” (“Enable Deleting”); the ability to change the sequence of columns: "Enable the ability to change the order of columns" ("Enable Column Reordering"); as well as the ability to pin in the parent container.

In order for the component to be able to display data, you must select the data source in the drop-down list. Selecting the drop-down list leads to the appearance of the following dialog (Fig. 47).

Rice. 47. Selecting a Data Source for the DataGridView

In this case, we have chosen the “Tourists” table as the data source. This choice changes the screen form as follows (Fig. 48).

Rice. 48. The DataGridView component displays the structure of the table

The figure shows that there is another BindingSource component and a TableAdapter component that works with the "Tourists" table. Please note that in design-time or during development, the data from the table is not displayed.

Now you need to display data from the related table "Tourists Information". To do this, let's place another DataGridView component on the form and select the following as the data source (Fig. 49).

Rice. 49. Selecting a data source for the second DataGridView

Here, the data source is not the “Tourist Information” table itself, but the link (Binding Source) between the “Tourists” and “Tourist Information” tables. This choice ensures that only those rows from the Tourist Information table that are associated with current line in the "Tourists" table. It also ensures that related data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.

Rice. 50. Database application in action

Navigating through data using the arrow keys is inconvenient. To simplify data navigation, there is a BindingNavigator component. Let's place it on the form (Fig. 51).

Rice. 51. The BindingNavigator component on the form

This component allows you to navigate between table entries, add and delete table rows. Opportunities and appearance The component is configurable because it is a ToolStripContainer menu strip.

The property that defines the table to navigate through is the BindingSource property. Set the value of this property to "touristsBindingSource". In operation, the component looks like this (Fig. 52).

Rice. 52. The BindingNavigator component at work

Editing data in the cells of the DataGridView component with the appropriate settings is possible, but inconvenient and not rational. In particular, it is difficult to check entered values ​​for errors. Therefore, for the “Tourists” table, we will make a screen form that allows you to display data in TextBox components and edit them. To do this, let's place a Panel type container on the form, and three TextBox components on it as follows (Fig. 53).

Rice. 53. Screen panel for editing records of the table "Tourists"

Now you need to bind the TextBox components to the corresponding fields of the "Tourists" table. To do this, use the property from the DataBindings - Advanced group, shown in Figure 54.

Rice. 54. "DataBindings - Advanced" property

Selecting this property leads to the appearance of the dialog shown in Figure 55. This dialog allows you to not only bind data, but also set an event within which the data will be updated, as well as data formatting when they are displayed.

For the upper TextBox component in the Binding drop-down list, select the data source "touristsBmdmgSource" and the source field - "Last name". For the middle and bottom components of the TextBox, we will select the same data source and the fields "First Name" and "Patronymic", respectively.

The developed application in operation looks like this (Fig. 56).

Rice. 55. Dialog box for the "DataBindings - Advanced" property

Rice. 56. Binding Data to Visual Components

However, when changes are made, all new data remains only on the form. They are not stored in the database, and when the application is called again, of course, they will be absent. This is because the data has been loaded into a DataSet object, which is an in-memory copy of the table. All actions are performed on this copy. In order for the changes to be reflected in the database, you must execute the Update method of the TableAdapter class. Thus, in the application being developed, it is necessary to place the “Update” button and write the following program code to the Click event handler:

touristsTableAdapteGUpdate(bDTur_firmDataSet); tourist_informationTableAdapter.Update(bDTur_firmDataSet);

This code updates the information in the "Tourists" and "Tourist Information" tables provided by the data source. Note that this method is overloaded, and its variants allow you to update both a single table row and a group of rows.

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

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

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

Introduction

3. Models of data organization

4. Relational databases

6. Infological model

7. Logic model

8. Structure of tables

12. Create tables

16. Create reports

17. Program Listing

Conclusion

Bibliography

Introduction

To make sound 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 using 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 talked about as a fast application development environment. This is a visual programming technology, i.e. the user draws up his future program, and sees the results of his work even before launching the program itself. In principle, the process of writing an application is divided into two parts. The first - the programmer places the necessary elements on the windows of his program, positions, sets the required sizes, changes properties. The second one is actually writing the program code, describing the properties of elements that are available only while the application is running, describing reactions to the event of the appearance of a window, pressing a button, etc. To set any properties for an element of the application being developed, it is not at all necessary to write massive text lines, it is enough change this property in the Object Inspector (so-called property monitor of the selected element). 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 fully finished working product, sometimes acting on equal terms 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.

base 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 entries.

b. Forms for easy viewing, entering, editing and searching for data, generating and displaying queries. The form should contain explanations, hints. When filling in form fields, for known sets of values, use the ability to select values ​​from 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 the handling of emerging semantic errors.

c. Reports containing all tables, forms, queries

d. Menu to access 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 tasks

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 justification 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. Making an explanatory note.

4. Criteria for assessing the acquired competencies in term paper

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

Table 1. Assessment of competencies

2. Basic concepts and classification of database management systems

A database (DB) is a collection of structured data stored in memory computing system and reflecting the state of objects and their relationships in the considered subject area.

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

A database management system (DBMS) is a set of language and software tools, designed to create, maintain and share a database with many users. RDBMS are usually distinguished by the data model used. So, DBMS based on the use relational model data 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 an applied task. Applications can be created in or outside the DBMS environment - using a programming system that uses database access tools, for example, Delphi or C ++ Вuildeg. Applications developed in the DBMS environment are often referred to as DBMS applications, while 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 between database files, data types and presentation formats, data ownership by users, security and access control codes, etc.

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

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

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

According to the basic principle of the client-server architecture, data is processed only on the server. The user or application forms queries that come to the database server in the form of SQL statements. The database server provides search and extraction of the necessary 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.

There are the following types of DBMS:

* full-featured DBMS;

* database servers;

* tools for developing programs for working with the database.

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

Database servers are designed to organize data processing centers in computer networks. Database servers provide processing of requests from client programs, usually with the help of SQL statements. Examples of database servers are: Microsoft SQL Server, Inter Base, etc.

In the role of client programs, in the general case, DBMS can be used, spreadsheets, word processors, programs Email and etc.

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

* client programs;

* database servers and their individual components;

* custom applications.

According to the nature of the use of DBMS, they are divided into multi-user (industrial) and local (personal).

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

* the possibility of organizing 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 for stored information;

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

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

* relative simplicity operation, allowing you to create workable user applications on their basis;

* relatively limited requirements for hardware resources.

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 ​​are used to work with data stored in the database:

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

* data manipulation language -- a set of structures that ensure the implementation of basic operations for working with data: input, modification and data selection on request.

Named languages ​​in different DBMS may have differences. The most widely used are two standardized languages: QBE -- a patterned query language and SQL -- a structured query language. QBE basically 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 during external memory;

* RAM buffer management;

* transaction management;

* logging 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 file system OS.

The need for data buffering is due to the fact that the amount of RAM is less than the amount of external memory. Buffers are areas of RAM designed to speed up the exchange between external and RAM. Buffers temporarily store database fragments, data from which is supposed to be used when accessing the DBMS or is 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 some indivisible sequence of operations on database data, which is tracked by the DBMS from beginning to end. If for any reason (failures and equipment failures, errors in software, including the application) the transaction remains incomplete, then it is canceled.

Transactions have three main properties:

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

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

* durability (even the crash of the system 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 completed successfully, 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 necessary condition successful operation of the database, especially when it is used on a network. The integrity of the database is a property of the database, which means that it contains complete, consistent and adequately reflecting the subject area information. The integrity of the state of the database is described using integrity constraints in the form of conditions that must be satisfied 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. Models of data organization

In the hierarchical model, entity objects and domain relations are represented by data sets that have a tree (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 referential integrity between ancestors and descendants, subject to the basic rule that no child can exist without a parent.

The network data model allows you to display a variety of relationships between data elements in the form of an arbitrary graph. A network database consists of a set of records and a set of corresponding 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 middel is the possibility of its efficient implementation. In comparison with the hierarchical model, the network model provides more opportunities in terms of the admissibility of the formation of arbitrary links.

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 difficulty of understanding it 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 relation is a set of elements called tuples. A visual representation of a relationship is a two-dimensional table.

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

The main disadvantages of the relational model are the following: lack of standard means identification of 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 relationships allows you to store data about the objects of the subject area and model the relationships between them. RMD terms are presented in Table. 4.1

Table 4.1. Terms of the relational model

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

1. Attribute values ​​must be atomic (in other words,
each value contained at the intersection of a row and a column,
should not be split into multiple values).

2. The values ​​of each attribute must be of the same type.

3. Each entry 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.

A relation 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 relation.

Attributes are properties that characterize an entity. In the structure of the table, each attribute is named and the heading of some table column corresponds to it.

The key of a relation is the set of its attributes that uniquely identify each of the tuples of the relation. In other words, the set of attributes K, which is the key of the relation, 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 has the uniqueness property.

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

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

Keys are usually used to achieve the following goals:

Exclusion of duplication of values ​​in key attributes (other attributes are not taken into account);

Ordering of tuples. It is possible to sort in ascending or descending order of the values ​​of all key attributes, as well as mixed ordering (by one - ascending, and by others - descending);

Table linking organizations.

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 ​​of which must match the values possible key another relation R1.

The attributes of the K2 relation that make up the foreign key are not key to this relation.

Foreign keys establish relationships between relationships.

Designing databases of information systems is a rather time-consuming task. It is carried out on the basis of the formalization of the structure and processes of the subject area, information about which is supposed to be stored in the database. There are conceptual and schematic-structural design.

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

Stages of conceptual design:

* study of the subject area for the formation general idea about her;

* 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 representation of the subject area.

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

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

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

* setting indexing for fields in tables;

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

* establishing 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 implemented 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. There are the following approaches to designing data structures:

* combining information about entity objects within one table (one relation) with subsequent decomposition into several interrelated tables based on the procedure for normalizing relations;

* formulating knowledge about the system (determining the types of initial data and relationships) and requirements for data processing, obtaining a ready-made database schema or even a ready-made applied information system using the CA5E system;

* implementation of system analysis and development of structural models.

5. Purpose and principle of operation of SQL

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

SQL is a tool for processing and reading data contained in a computer database. This is a language that makes it possible to effectively create relational databases and work with them.

The database world is becoming more and more unified, which has led to the need for a standard language that can function in large numbers. various kinds computer environments. The standard language will allow users who know one set of commands to use them to create, retrieve, modify, and communicate information whether they are working on a personal computer, network workstation, or mainframe. In an increasingly interconnected computer world, the user equipped with such a language has a huge advantage in using and summarizing information from a number of sources using a large number ways.

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

6. Infological model

When creating an infological model, the subject area of ​​the given database "Curricula, studied disciplines of the PMI direction" was analyzed. 4 objects were singled out: Curriculum, Discipline, Student, Teacher, as well as two additional tables that link between students and disciplines, as well as between teachers and disciplines. The Curriculum object has the following attributes: Year of creation, Number curriculum. The Discipline object has the following attributes: Discipline name, Discipline code, Curriculum number, Number of lecture hours, Number of practice hours, Number of hours for laboratory works, Total hours, Number of hours per week, Reporting form by discipline, Study semester. The Sudent object has the following attributes: Gradebook number, full name. And the Teacher object has the following attributes: Full name, Personnel number, Department, Position, Phone. The Curriculum and Course objects are related in a 1:n relationship, the Course and Student objects are related in a 1:n relationship, and the Course and Teacher objects are related in a 1:n relationship.

When describing the infological model, ER diagrams were used:

Picture 1

7. Logic model

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

The logical data model is the initial prototype of 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 by means 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 to represent them, the Curriculum object is only informational, since the database works 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 will be the table Teaches (Disciplina-Prepodavatel) and the table Learns (Disciplina-Student).

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

Figure 2

8. Structure of tables

The initial database consists of 5 tables (the Curriculums table is not considered, since one curriculum is used).

Explanation of fields:

v discipline.db

Ш Nazv- discipline name, field type: String;

Ш Kod - a unique code of the discipline: LongInt;

Ш Semestr - 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 - number of the curriculum, which contains the discipline: LongInt.

v Student.db

Ш NomerStudBileta - student ID number: LongInt;

Ш FIO - student's last name: ShortInt;

v Prepodaet.db (Disciplina-Teacher)

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

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

v Prepod.db

Ш FIO - teacher's full name: String;

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

Ш Kafedra - department where he works: String;

Ш Dolshnost - Teacher's position: String;

Ш Telefon - teacher's contact phone number: String.

v Izuchaet.db(Disciplina-Student)

Ш KodDiscip - discipline code: LongInt;

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

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

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

9. Designing SQL queries

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

select Nazv,FormaReport

where Semester=:s and

(Disciplina.FormaOtchet="Pass" or Disciplina.FormaOtchet="Exam" );

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

Main listing:

select Teacher.FIO,

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

Ocenka,Disciplina.Nazv

from Disciplina, Prepodaet,Izuchaet

where Disciplina.KodDiscip=Prepodaet.KodDiscip

and (Disciplina.FormaOtchet="Exam" or Disciplina.FormaOtchet="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.Evaluation="2"

and Disciplina.KodDiscip=Izuchaet.KodDiscip

and Disciplina.KodDiscip=Prepodaet.KodDiscip

and (Disciplina.FormaOtchet="Exam" or Disciplina.FormaOtchet="Pass");

Update Discipline

set ObsheeKolChVNed=VsegoCh/17;

4. Prepare an insert for each student's diploma:

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

from Izuchaet, Disciplina

where Disciplina.KodDiscip=Izuchaet.KodDiscip

and Disciplina.FormaRetchet="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. Structure and functions of the system

The course work consists of one project “Project1” and 13 modules.

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

2. Unit2 - the form is stored here, which is start page Database. The following components are used here: Button, Memo.

3. Unit3 - a form is stored here, which contains all the database tables in the form of tabs. The following 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. The following components are used here: Memo, Button.

5. Unit5 - a form is stored here, which displays an additional examination sheet. The following 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. The following components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

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

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

9. Unit9 - this is where the form is stored, which displays the insert in the diploma. The following 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 following 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. The following 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, RVQueryConnection and Query.

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

11. User manual

1. We start 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. Select "Show task"

2. A window appears in front of us with information on the assignment of the term paper

Figure 4

3. Go back by clicking on the appropriate button and start working 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 the tabs all the information contained in the database. Here we can view the list of disciplines, study group, teachers, what these or those students study and what subject the teachers teach.

5. Also from this window, we can go to requests. Click on the appropriate button.

Figure 7

Here we can select any request of interest by simply clicking on the appropriate button. Also here we can return to the tables and proceed to the formation of an 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 credits 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 "Run" button. The query 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 evaluation field is filled in on the form with tables inside the Student-Subject tab. Also on this sheet, you can create an additional examination sheet. For students with a grade of 2. From this sheet, we can go back to the requests.

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 "Generate an insert for the diploma"

Figure 11

Here you must enter the full name of the graduate by selecting the appropriate student from the drop-down list. Next, 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 grades 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 on the red cross in the upper right corner of the screen.

Figure 12

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

Figure 13

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

Figure 14

Select from the drop-down list the discipline of interest to us, for example, economics, and enter its name in the box below the drop-down list. Next, 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. As well as a 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. Create tables

The Database Desktop utilities were used to create the tables. It can be launched - Start/Programs/Borland Delphi 7/ Database Desktop. The working directory of the utility 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 table type. The PARADOX 7 type can be considered the best for file server tables.

1. Create a YchebPlan table (Curriculum):

Figure 17

5. Create table Disciplina (Discipline):

Figure 18

6. Create table Student:

Figure 19

7. Creation of table Prepodaet (Discipline-Teacher):

Figure 20

5. Create table Prepod (Teacher):

Figure 21

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

Figure 22

13. Creating an application in Delphi

To create a new application, select New/Application from 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 desired table.

Figure 23

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

Figure 24

· DBNavigator (on the DataControls tab) - needed to navigate through the table records. In the Object Inspector, the DataSource property specifies the same data source as in the DBGrid. Navigator functions 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

A 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

Creating buttons.

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

Figure 29

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

procedure TForm1.N5Click(Sender: TObject);

begin

Form2.Show;

Form1.Close;

end;

15. Create captions 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 existing list, the ComboBox component (drop-down list) is used. It can be filled in like this

In the Object Inspector, in the Items property, write:

Figure 31

16. Create reports

The report is created using the QReports tool, which must first be connected: Component->install packages->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.

Description of Unit1 module

unit Unit1;

interface

uses

Dialogs, StdCtrls;

type

TForm1 = 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.

Description of Unit2 module

unit Unit2;

interface

uses

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

Dialogs, StdCtrls;

type

TForm2 = 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;

Description of Unit3 module

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 = 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: T Label;

Memo1:TMemo;

Label3: T Label;

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;

Description of Unit4 module

unit Unit4;

interface

uses

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

Dialogs, StdCtrls;

type

TForm4 = 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;

Description of the Unit 5 module

unit Unit5;

interface

uses

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

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

type

TForm5 = class(TForm)

DataSource1:TDataSource;

DBGrid1: TDBGrid;

Query1: TQuery;

DBEdit1: TDBEdit;

DBEdit2: TDBEdit;

DBEdit3: TDBEdit;

Label1: T Label;

Label2: T Label;

Label3: T Label;

Label4: T Label;

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:=true;

end;

procedure TForm5.Edit1Change(Sender: TObject);

begin

Query1.Open;

end;

procedure TForm5.Button1Click(Sender: TObject);

begin

Form11.show;

Form5.Close;

end;

Description of the Unit 6 module

unit Unit6;

interface

uses

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

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

type

TForm6 = class(TForm)

Button1: TButton;

Edit1: TEdit;

DataSource1:TDataSource;

DBGrid1: TDBGrid;

Query1: TQuery;

Label1: T Label;

DBNavigator1: TDBNavigator;

Label2: T Label;

Memo1:TMemo;

Button2: TButton;

Label3: T Label;

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:=0;

end;

Query1.Open;

end;

procedure TForm6.Button2Click(Sender: TObject);

begin

Form11.show;

Form6.Close;

end;

Description of the Unit 7 module

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 = class(TForm)

Label1: T Label;

Label2: T Label;

DataSource1:TDataSource;

Query1: TQuery;

Edit2: TEdit;

Button1: TButton;

DBEdit1: TDBEdit;

DBEdit2: TDBEdit;

Label3: T Label;

DBGrid1: TDBGrid;

Label4: T Label;

Label5: T Label;

DBNavigator1: TDBNavigator;

Button2: TButton;

Label6: T Label;

Label7: T Label;

Memo1:TMemo;

ComboBox1: TComboBox;

Label8: T Label;

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:=edit2.Text

else

begin

Query1.Params.Value:=0;

edit2.Text:="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;

Description of the Unit 8 module

unit Unit8;

interface

uses

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

type

TForm8 = class(TForm)

Label4: T Label;

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;

Description of the Unit 9 module

unit9;

interface

uses

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

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

type

TForm9 = class(TForm)

Edit1: TEdit;

Query1: TQuery;

DataSource1:TDataSource;

DBGrid1: TDBGrid;

Button1: TButton;

Query2: TQuery;

DataSource2: TDataSource;

Button2: TButton;

DBEdit1: TDBEdit;

DBNavigator1: TDBNavigator;

Label1: T Label;

Label2: T Label;

Label3: T Label;

Name: TComboBox;

Button3: TButton;

Memo1:TMemo;

Label4: T Label;

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:=edit1.Text

else

begin

Query1.Params.Value:=0;

edit1.Text:="Enter the graduate's 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:=edit1.Text

else

begin

Query2.Params.Value:=0;

edit1.Text:="Enter 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;

Description of the Unit 10 module

unit Unit10;

interface

uses

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

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

Grids, DBGrids;

type

TForm10 = class(TForm)

Button1: TButton;

Query1: TQuery;

DataSource1:TDataSource;

DBEdit1: TDBEdit;

DBEdit2: TDBEdit;

Label1: T Label;

Label2: T Label;

Edit1: TEdit;

Button2: TButton;

Label3: T Label;

ComboBox1: TComboBox;

Label4: T Label;

Label5: T Label;

Memo1:TMemo;

Label6: T Label;

Label7: T Label;

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:=edit1.Text

else

begin

Query1.Params.Value:=0;

edit1.Text:="Enter a title!";

end;

Query1.Open;

end;

procedure TForm10.Button3Click(Sender: TObject);

begin

Form11.show;

end;

Description of the Unit 11 module

unit Unit11;

interface

uses

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

Dialogs, StdCtrls;

type

TForm11 = class(TForm)

Button1: TButton;

Button2: TButton;

Button3: TButton;

Button4: TButton;

Button5: TButton;

Button6: TButton;

Memo1:TMemo;

Label1: T Label;

Label2: T Label;

Label3: T Label;

Button7: ​​TButton;

Label4: T Label;

Label5: T Label;

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

Similar Documents

Creating tables and designing database management systems. Infological design. Relational database schema. Applied value of systems: a report on suppliers and goods supplied by them. Statement of the availability of goods in the store.

term paper, added 12/01/2008


Development of a database with information about employees, goods, with a directory of types of goods using the database management system MySQL data using SQL queries. Development of an infological model of the subject area. The structure of tables, database fields.

test, added 04/13/2012


The process of designing a database, developing its logical structure in accordance with the infological model of the subject area. Working with the program DBMS Access, properties of tables and their fields, creation of inter-table links; infological design.

term paper, added 12/17/2009


Basic concepts of database and database management systems. Data types to work with Microsoft bases access. Classification of DBMS and their main characteristics. Post-relational databases. Trends in the world of modern information systems.

term paper, added 01/28/2014


Features of developing an infological model and creating a structure relational database data. Database Design Fundamentals. Development of tables, forms, queries to display information about the corresponding model. Working with databases and their objects.

term paper, added 11/05/2011


Examining the characteristics and functionality of the Microsoft database management system Office Access. Definition of the main classes of objects. Development of the database "Office work". Creation of tables, forms, queries, reports and data schemas.

abstract, added 12/05/2014


The trend in the development of database management systems. Hierarchical and network models of DBMS. Basic requirements for distributed database data. Processing of distributed requests, interoperability. Data replication technology and multi-tier architecture.

abstract, added 11/29/2010


Theoretical information and basic concepts of databases. Database management systems: composition, structure, security, modes of operation, objects. Working with databases in OpenOffice.Org BASE: creating tables, relationships, queries using the Query Wizard.

term paper, added 04/28/2011


Designing a database for a system for accepting, processing and accounting applications to the information technology department; development of infological and datalogical models, implementation physical model. Creation of applications for visualization of work with the database.

thesis, added 01/25/2013


Selection of information objects and their infological model. The logical structure of a relational database. Development of tables in the database management system Access data. Creation of requests, forms and reports in DBMS Access. User application development.

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 database access methods and the programming interfaces used in these access methods. Considers integrating Visual C# applications with the server DBMS Microsoft SQL Server 2012. As an example, the development of the information system "Travel Agency" is considered.

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 means, methods and personnel used to store, process and issue information in order to achieve a set goal. The developed information system will be built on client-server technology. In such systems, information is stored on the server, and the interface of the information system is stored on client computers, through which users of the information system gain access to data.

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

• the task of developing a database designed to store 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 offered by it, about registration and payment for tours. At the design stage of the database, the tables "Tourists", "Tours", "Permits", "Seasons", "Payment" are created. Links are established between tables.

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

The SQL Server security system is based on two concepts: authentication and authorization. System Administrator, which is responsible for SQL Server security, creates a separate login object for each user. This object contains the SQL Server user account name, password, full name, and other attributes used to control access to SQL Server databases. By connecting to SQL Server, the user gets access to those databases in which his account is registered. To register an account in a particular database, the system administrator creates a database username in it and associates it with a particular database. account. The system administrator grants users certain powers. The sales manager can make changes to the "Tourists" and "Permits" tables and change the "Number of places" column in the "Tours" table after selling the next voucher. An employee of the travel agency can make changes to the tables "Seasons" and "Tours". Accountant and cashier - in the table "Payment".

Granting of permissions can be done in SQL Server Management Studio by opening the properties windows of the corresponding user. You can also grant permissions using the GRANT statement. Examples of granting authority to a manager. Next instruction grants the right to the Menedger user 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, after the table name in brackets, the name of the changed column "Tours" (Number of_seats) is indicated. Provided SELECT, UPDATE operations.

There are four instructions in Data Control Language (DCL): COMMIT (complete), ROLLBACK (rollback), GRANT (grant), REVOKE (revoke). All of these instructions are related to protecting the database from accidental or intentional damage. Databases are vulnerable precisely when changes are made to them. To protect the database, SQL provides for limiting 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 contention 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. Transactions do not conflict if they are executed sequentially.

One of the main tools for maintaining the integrity of the database is a transaction. A transaction encapsulates all SQL statements that can affect the database. An SQL transaction ends with one of two statements: COMMIT (complete) or ROLLBACK (rollback). If a 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 be executed in one of two modes: READ-WRITE (read-write) or READ-ONLY (read-only). You can set one of the following isolation levels for a transaction: SERIAIZABLE (serial execution), REPEATABLE READ (repeated read), READ UNCOMMITED (read uncommitted data). By default, the READ-WRITE and SERIAIZABLE characteristics are in effect. The default characteristics of an SQL transaction are usually suitable for most users.

The application is created in the environment visual studio 2012 using the C# programming language. Design software product starts with the design of the 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 items contains sub-items. Each function will be executed in its own window. The MenuStrip element is set on the main application window, menu options are formed. A PictureBox element is placed on the form window. An image is loaded into the element area. The drawing must cover the entire area. The SizeMode property sets the scaling of the drawing. 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.

To display a list of users who have the right to work with the "Travel Agency" database, a comboBox control is installed. The comboBox element is bound to a data source. The window " WITHomboBoxTasks”, in which the checkbox “Use data-bound elements” is checked, if this checkbox is checked, the data-binding options open. The comboBox element is bound to the "Users" table, in the "Display member" line, "Last name" is selected. To enter a login, a textbox1 control is set, to enter a password, textBox2. For the textBox1 and textBox2 elements, the UsesSystemPasworChar property is set to true, which specifies whether the text in the text box should be displayed with password characters by default. Two command buttons "Login" and "Change user" are set.

When binding the comboBox element to the "Users" table, in program code form, the Form1_Load event handler appears.

private void Form1_Load(object sender, EventArgs e)

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

When starting the application, the menu is not available. To log in, enter your user information and click the "Login" button. When the form is loaded, the last names of the users contained in the Users table are loaded into the comboBox1 control. Lines are added to this handler that make the menu, the Change User button inaccessible, and in the comboBox1 element, not a single item is selected:

menuStrip1.Enabled = false; comboBox1.SelectedIndex = -1;

button2.Enabled=false;

Figure 1. View of the main application window

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

private string parfam, parpasw, parlog;

The following line is added to the namespace:

using System.Data.SqlClient;

// "Login" button click event handler

string sql = "";

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

SqlDataReader cmReader;

parfam = comboBox1.Text; parlog = textBox1.Text;

SqlConnection conn = new SqlConnection(connstr);

sql = "SELECT Last Name, Username, Password FROM Users " +

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

SqlCommand cmdkod = new SqlCommand(sql, conn);

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

cmdkod.Parameters["@fam"].Value = parfam;

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

cmdkod.Parameters["@pasw"].Value = parpasw;

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

cmdkod.Parameters["@log"].Value = parlog;

if (!cmReader.Read())

MessageBox.Show("Wrong password!");

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

menuStrip1.Enabled = true; comboBox1.SelectedIndex = -1;

button1.Enabled=false; button2.Enabled = true;

textBox1.Text = ""; textBox1.Enabled = false;

textBox2.Text = ""; textBox2.Enabled = false;

comboBox1.Enabled = false;

cmReader.Close();

private void button2_Click(object sender, EventArgs e)

menuStrip1.Enabled = false; comboBox1.Enabled = true;

textBox1.Enabled = true; textBox2.Enabled = true;

button1.Enabled = true; button2.Enabled=false;

Description of how the "Login" button click event handler works.

The connstr line contains the connection string. The sql line contains the text of the generated query, starting with the select statement, after which the selected 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 to search the Users table for a user with the given last name, username, and password. The button1_Click handler opens a SqlConnection. Next, the handler executes the SQL command stored in the cmdkod object.

cmReader = cmdcode.ExecuteReader();

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

Figure 2. View of the main window after user login

Tables and query results will be displayed on the DataGridView controls. The main purpose of these elements is to link to tables of external data sources, primarily database tables. For the convenience of viewing and entering new entries, the tables "Seasons", "Tours" and "Permits", "Payment" will be displayed two in one window. Each DataGridView control is associated with a corresponding table in the Travel Agent database. In this window, the "Tourists" table is selected (Figure 3). After the connection is completed (clicking the "Finish" button), the DataSet, BindingSource and TableAdapter components appear on the form. These components are non-visual, so they are displayed in an additional panel. The DataSet is a specialized object that contains an image of a database. To implement the interaction between the DataSet and the actual data source, an object of type TableAdapter is used. The very name of this object - adapter, converter - indicates its nature. The TableAdapter contains the Fill and Update methods, which perform a forward and backward transfer of data between the DataSet and the table stored in the SQL Server database. The Fiil method fills the DataSet with data from the SQL server, and Update updates the server database SQL 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 connection of tables to data sources is completed, the Form2_Load event handler appears in the program code of the form.

private void Form2_Load(object sender, EventArgs e)

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

When the form is loaded, 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 the "Save Tourists" button click:

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 "Requests" menu. If the query has no parameters, a DataGridView control is installed on the form window to display the query results and bound to the appropriate database procedure or function.

This article provides some methods for 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 the Microsoft SQL Server 2012 DBMS server. When sharing a language Visual programming C# with SQL allows you to create powerful applications with a wide range of features. The main strength of SQL lies in extracting data. No matter how many rows there are in a table, they can be retrieved with a single SELECT statement. At the same time, the main disadvantage of the SQL language is its underdeveloped user interface. With procedural languages, you can create convenient interfaces entering and viewing data. The most common method of combining SQL with procedural languages ​​is called SQL code injection. The SQL statement is inserted at the desired location in the procedural program. Information must be passed between a program written in a procedural language and SQL code. For this, base variables are used. In order for SQL to recognize these variables, they must be declared. Variables are declared in the description area of ​​the form class before the description of the program code. In code, the newly created instance of the SqlConnection object provides a connection to the SQL server. The SqlCommand object provides execution of the embedded SQL command.

Bibliography:

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