Development of applications for working with databases. Development of applications for working with databases Description of the Unit4 module

Almost every organization has its own database. Well, 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 sets of programs for creating software for database development, a really great variety, you can easily get confused. This article will cover some basic database development software.

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 entirely convenient. This is due to the fact that SQL itself does not have any graphical shell, and queries to the database must be sent generally via command line. For this reason, various kinds of programs have appeared that simplify the development of databases. However, it is still worth learning the basics of this language. Suddenly you need to make some kind of request, but the program does not work correctly.

Microsoft Access

This database creation program is definitely familiar to many. After all, it 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. You can only indicate which query to make, and the program itself will create an SQL query.

Regarding the relevance of the program. Until now, the databases of many organizations are made with 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 junior college courses!

PhpMyAdmin

Access, of course, is a good program, but if you need a database for a website, it won’t 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’s easy to do something wrong and it won’t work. Therefore, when installing this program to create databases, you must strictly follow the instructions. But another advantage of PhpMyAdmin is that you can access it via the Internet as a website! For example, let's say you have a website that runs on WordPress. It will have a database. And if you have a website on some good hosting, then most likely, work with databases will be carried out through PhpMyAdmin, and it can be accessed through the hosting control panel.

Another program for creating databases. It is free, but there is also a paid version with improved features. In this program it is easy to create connections with tables, and in general, it is simply convenient to work with. Another advantage is that you can display the database graphically. Most people prefer this program when working with databases. In principle, PhpMyAdmin is not inferior in capabilities, but still it is more designed for working with website databases.

This article discussed the main programs for creating databases. 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 explore this area, then it is recommended to work with MySQL WorkBench. Once you learn the basics of SQL, there will be no significant difference for you where you 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.

Let's create a simple database application that displays on the screen information from the "Tourists" table and the "Tourist Information" table record from the database associated with the current record of the "Tourists" table Microsoft data Access.

To do this, let's create an empty Windows application. Environment appearance

development is shown in Figure 39.

Rice. 39. Blank application

Figure 39 highlights the “Data” component group, which contains components for accessing and manipulating data.

The binding of 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. Binding Source component on the form

The component is not visual, so it is displayed in an 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 configure its value. When you select this property in the properties window, the following window appears (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 choice of data sources:

Database - Database;

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

Object - Object for selecting an object that will generate data and objects to work with it.

In our case, you need to select the “Database” item. A window for selecting a data connection 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 database type, its location, user names, security features, etc.

The dialog drop-down list contains all previously created connections. If the required connection is not in the list, then you should use the “New connection” button. Pressing the button causes the following dialog to appear (Fig. 44).

In this dialog, you select the data source type (in this case, Microsoft Access), the database name (in this case, the name and location of the database file), and 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 ensure that the entered parameters are correct and the connection is working.

Rice. 44. Creating a new connection

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

Rice. 45. Selecting the necessary tables

In this window, the “Tourists” and “Tourist Information” tables are selected. Since no objects other than tables were created in the database, only tables are displayed in Figure 45. This completes the creation of the data source. After clicking the “Finish” button, a DataSet component appears on the form next to the BindingSource component.

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 Adding”, “Enable Editing”, “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 be attached to the parent container.

In order for the component to display data, you must select a data source in the drop-down list. Selecting the drop-down list causes the following dialog to appear (Fig. 47).

Rice. 47. Selecting a data source for DataGridView

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

Rice. 48. The DataGridView component displays the table structure

The figure shows that another BindingSource component and a TableAdapter component have appeared, working with the “Tourists” table. Please note that in design-time or during the development process, the data from the table is not displayed.

Now you need to display the data from the linked table “Tourist Information”. To do this, place another DataGridView component on the form and select the following as a 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 connection (Binding Source) between the “Tourists” and “Tourist Information” tables. This selection ensures that only those rows from the Tourist Information table that are associated with the current row in the Tourists table are selected. This choice also ensures that associated data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.

Rice. 50. Database application at work

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

Rice. 51. BindingNavigator component on the form

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

The property that determines the table through which navigation is performed is the BindingSource property. Let's set the value of this property to "touristsBindingSource". In operation, the component looks like this (Fig. 52).

Rice. 52. BindingNavigator component at work

Editing data in the cells of the DataGridView component with appropriate settings is possible, but it is 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, place a container of the Panel type on the form, and on it three TextBox components as follows (Fig. 53).

Rice. 53. Screen panel for editing entries in the “Tourists” table

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

Rice. 54. Property “DataBindings - Advanced”

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

For the top TextBox component, in the Binding drop-down list, select “touristsBmdmgSource” as the data source and the source field as “Last Name”. For the middle and bottom TextBox components, select the same data source and the “Name” and “Patronymic” fields, respectively.

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

Rice. 55. Dialog window for the “DataBindings - Advanced” property

Rice. 56. Data binding to visual components

However, when changes are made, all new data remains only on the form. They are not saved in the database, and when the application is called again, of course, they will be missing. This occurs because the data was loaded into a DataSet object, which is an in-memory copy of the table. All actions are performed with this copy. In order for 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 in the Click event handler:

touristsTableAdapterUpdate(bDTur_firmDataSet); information_about_touristsTableAdapter.Update(bDTur_firmDataSet);

This code updates 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 an individual table row and a group of rows.

Submitting your good work to the knowledge base is easy. 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. Data organization models

4. Relational databases

6. Infological model

7. Logic model

8. Table structure

12. Creating tables

16. Generating reports

17. Program listing

Conclusion

References

Introduction

To make informed and effective decisions in production activities, in economic management and in politics, a modern specialist must be able to use computers and communications to receive, accumulate, store and process data, 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 designed to store information. Modern databases store not only data, but also information.

Delphi is spoken of as an environment quick creation applications. This is a visual programming technology, i.e. the user designs his future program and sees the results of his work even before launching the program itself. Basically, the process of writing an application itself is divided into two parts. The first is that the programmer places the necessary elements on the windows of his program, positions them, sets the required sizes, and 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 the appearance of a window, pressing a button, etc. To assign any properties to an element of the application being developed, it is not at all necessary to write massive text strings, it is enough change this property in the object inspector (the 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, without knowing or not paying attention to some of the properties of the program element, but using only the necessary ones, writes a completely finished working product, sometimes equal in complexity to those written in a non-visual editor.

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

database information table program

1. General requirements for developing 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 convenient viewing, entering, editing and searching data, generating and displaying queries. The form should contain explanations and tips. When filling out form fields, for known sets of values, use the ability to select values ​​from a list. Forms should be linked whenever possible to optimize navigation through records. If possible, the form should contain as much information as possible for the user. Provide for handling emerging semantic errors.

c. Reports containing all tables, forms, queries

d. Menu for accessing various database objects

e. Help containing full description assignments

2. To program the database, you must use additional literature on the SQL language and the DELPHI programming system.

3. List and methods of independently solved problems

1. Analysis of the problem statement and subject area.

2. Infological design, justification of information objects, domains, attributes, connections, drawing up an infological model.

3. Logical design, construction and justification of basic relationships, 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. Drawing up an explanatory note.

4. Criteria for assessing acquired competencies in course work

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

Table 1. Competency assessment

Name of competencies	Object of assessment
Understand and follow the 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	Components of a database application, answers to questions about database implementation
Know and apply elements of database technology	Answers to questions related to design, possibly in test format
Understand the application needs of database technology	Introduction of an explanatory note
Work planning, work organization	Deadlines for completion of work
Solve problems independently	List and methods of independently solving problems
Oral communication	Public database protection
Ability to analyze, synthesize	Infological, logical database model
Striving for quality results	Quality of work performed, ergonomic indicators, quality of functioning.
Ability to generate new ideas	Interface quality, additional features DBs not taken into account in the task.
Ability to manage (search) information	The number of information resources found and used in course work, the number of consultants.

2. Basic concepts and classification of database management systems

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

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

A database management system (DBMS) is a complex of language and software, designed for creating, maintaining and sharing a database among many users. Typically, DBMSs are distinguished by the data model used. Thus, DBMSs based on the use of a relational data model are called relational DBMSs.

To work with a database, DBMS tools are often sufficient. However, if it is necessary 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 provides automation of the solution of any application problem. Applications can be created in or outside the DBMS environment - using a programming system that uses database access tools, for example, Delphi or C++ Buyer. Applications developed in the DBMS environment are often called DBMS applications, and applications developed outside the DBMS are called external applications.

A data dictionary is a database subsystem designed for centralized storage of information about data structures, relationships of database files with each other, types of data and formats for their presentation, ownership of data by 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 located on a server computer and shared access is provided.

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

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

According to the basic principle of client-server architecture, data is processed only on the server. The user or application generates queries that are sent to the database server in the form of SQL instructions. The database server searches and retrieves the required data, which is then transferred to the user's computer. The advantage of this approach in comparison with the previous one is a noticeably smaller volume of transmitted data.

The following types of DBMS are distinguished:

* full-featured DBMS;

*database servers;

* tools for developing programs for working with databases.

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 using SQL statements. Examples of database servers are: MicrosoftSQL Server, Inter Base, etc.

In the general case, DBMS, spreadsheets, word processors, programs can be used as client programs email etc.

Database program development tools can be used to create the following programs:

* client programs;

* database servers and their individual components;

* custom applications.

Based on the nature of their use, DBMSs are divided into multi-user (industrial) and local (personal).

Industrial, DBMS are software basis for development automated systems management of 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;

* resistance to failures of various kinds, including the presence of a multi-level backup system for stored information;

* ensuring the security of stored data and a developed structured system for accessing it.

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

* relative ease of operation, allowing you to create workable user applications based on them;

* relatively limited requirements for hardware resources.

Based on the data model used, DBMSs are divided into hierarchical, network, relational, object-oriented, etc. Some DBMSs 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 constructs that ensure the execution of basic operations for working with data: input, modification and retrieval of data on request.

The named languages ​​may differ in different DBMSs. The most widely used are two standardized languages: QBE - a pattern-based query language 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 main functions low level:

* data management in external memory;

* management of RAM buffers;

* transaction management;

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

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 RAM designed to speed up exchanges between external and RAM. Buffers temporarily store database fragments, the data from which is intended to be used when accessing the DBMS or is planned to be written to the database after processing.

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

A transaction has three main properties:

* atomicity (all operations included in the transaction are performed 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, they withdraw money from one account, then credit it to another account. If at least one of the actions fails, 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 used on a network. DB integrity is a property of a database, meaning that it contains complete, consistent and adequately reflecting information about the subject area. The integrity 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 ensured 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 the hierarchical model, entity objects and domain relationships are represented by data sets that have a tree (hierarchical) structure. The hierarchical data model was historically the first. On its basis, the first professional DBMS were developed in the late 60s and early 70s.

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

The network data model allows you to display various 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 relationships. There are no special restrictions imposed 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 a network data midsection is the possibility of its effective implementation. Compared to the hierarchical model, the network model provides greater opportunities in terms 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 difficulty of its understanding by the average user. In addition, in the network data model, control of the integrity of connections is weakened due to the permissibility of establishing arbitrary connections between records.

Systems based on a 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 relationship.

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

Using one table, it is convenient to describe the simplest type of relationships between data, namely: dividing 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: lack 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 objects of the subject area and model the connections between them. RMD terms are presented in table. 4.1

Table 4.1. Terms of the relational model

Term relational model	Equivalent
Attitude
Relationship diagram	Table column header row (table header)
	Table row, record
Essence	Description of object properties
	Column, field
	Multiple Valid Values	attribute
Primary key	Unique identifier
Cardinality	Number of lines
	Number of columns

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

1. Attribute values ​​must be atomic (in other words,
each value contained at the intersection of a row and a column,
must not be divisible into several 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 important.

A relation is an essential 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 relationship.

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

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 relationship, has the property of uniqueness. The next property of a key is non-redundancy. That is, none of the proper subsets of the set K has the property of uniqueness.

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

There may be cases where 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 selected as the primary key.

Keys are typically used to achieve the following purposes:

Elimination of duplicate values ​​in key attributes (other attributes are not taken into account);

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

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 ​​of a possible key of another relation R1.

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

Foreign keys are used to establish connections between relationships.

Designing information system databases is a rather labor-intensive 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. There are conceptual and schematic-structural design.

Conceptual design of an IS database is largely a heuristic process. The adequacy of the informationological model of the subject area built within its framework is verified experimentally during the functioning of the IS.

Conceptual design stages:

* studying the subject area to form a general idea of ​​it;

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

* determination of the main objects-entities of the subject area
and the relationships between them;

* formalized representation of the subject area.

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

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

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

* establishing indexing for fields in tables;

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

* establishing integrity constraints for tables and relationships;

* normalization of tables, adjustment of the list of tables and relationships. Database design is carried out at the physical and logical levels. Design on physical level implemented using DBMS and often automated.

Logical design consists of determining the number and structure of tables, developing database queries, 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 designing data structures are distinguished:

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

* formulating knowledge about the system (determining the types of source data and relationships) and requirements for data processing, obtaining a ready-made database scheme or even a ready-made application 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 designed to process and read data contained in a computer database. This 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 to create a standard language that can function in a large number of different types of computer environments. A standard language will allow users who know one set of commands to use them to create, find, modify, and communicate information regardless of whether they are working on a personal computer, a network workstation, or a mainframe. In an increasingly interconnected computer world, a user equipped with such a language has a tremendous advantage in using and summarizing information from a range of sources in a large number of 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 the information model, the subject area of ​​the given database “Curriculums, studied disciplines of the PMI direction” was analyzed. 4 objects were identified: Curriculum, Discipline, Student, Teacher, as well as two additional tables that communicate between students and disciplines, as well as between teachers and disciplines. The Curriculum object has the following attributes: Year of creation, Curriculum number. The Discipline object has the following attributes: Discipline name, Discipline code, Curriculum number, Number of lecture hours, Number of practice hours, Number of hours laboratory work, Total hours, Number of hours per week, Discipline reporting form, Semester of study. The Sudent object has the following attributes: Gradebook number, full name. And the Teacher object has the attributes: Full name, Personnel number, Department, Position, Telephone. The Curriculum and Discipline objects are related in a 1:n relationship, the Discipline and Student objects are related in a 1:n relationship, and the Discipline and Teacher objects are related in a 1:n relationship.

When describing the information model, ER diagrams were used:

Figure 1

7. Logic model

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

The logical data model is the initial prototype of the future database. It is constructed in terms of information units, but without reference to a specific DBMS. Moreover, the logical data model does not necessarily 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 Discipline and Teacher objects are connected in a 1:n relationship, an additional table appears to represent the relationship between the Discipline and Teacher: Teaches objects.

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 two objects, so we need to introduce another table to represent the relationships between these tables. This will be the Teaches table (Disciplina-Prepodavatel) and the Study table (Disciplina-Student).

The presented database can be classified as 5th normal form, because it is in 3rd normal form and the primary key is prime. The logical diagram is implemented in Microsoft Access.

Figure 2

8. Table structure

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

Explanation of fields:

v Disciplina.db

Ш Nazv- name of the discipline, field type: String;

Ш Kod - unique discipline code: LongInt;

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

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

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

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

Ш VsegoCh - total number of hours: LongInt;

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

v Student.db

Ш NomerStudBileta - student card number: LongInt;

Ш FIO - student's last name: ShortInt;

v Prepodaet.db (Disciplina-Prepodavatel)

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

Ш Full name - Full name of the teacher who teaches the corresponding discipline: String.

v Prepod.db

Ш FIO - full name of the teacher: String;

Ш TabelNomerPrepodavatelya - unique personnel number of the teacher: LongInt;

Ш Kafedra - the department where he works: String;

Ш Dolshnost - Teaching position: String;

Ш Telefon- contact phone number teacher: String.

v Izuchaet.db(Disciplina-Student)

Ш KodDiscip- discipline code: LongInt;

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

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

Ш Ocenka - student’s grade in the discipline being studied: LongInt;.

9. Designing SQL Queries

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

select Nazv,FormaReport

where Semester=:s and

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

2. Create examination and test reports / 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=Prepodaet.KodDiscip

and (Disciplina.FormaOtchet="Exam" or Disciplina.FormaOtchet="Test")

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="Test");

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.FormaOtchet="Exam"

Select AVG(Evaluation) as SrBall

Order by SrBall desc;

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

Select FIO, AVG(Evaluation) as SrBall

Order by SrBall desc;

10. System structure and functions

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

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

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

3. Unit3 - a form is stored here that 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 - the form on which the additional examination sheet is displayed is stored here. The following components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

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

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

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

9. Unit9 - the form on which the diploma insert is displayed is stored here. The following components are used here: Memo, Button, TabSheet, Table, DBGrid, DBNavigator, Label, Query.

10. Unit10 - a form is stored here on which the form for filling out the electronic statement is displayed. 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 - the form is stored here, which displays the report on the creation of the electronic examination sheet. The components used here are: Memo and Button, RVProject, RVQueryConnnection and Query.

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

11.User manual

1. Launch the project. The title page of the term paper appears before us.

Figure 3

Here we can immediately enter the database, or we can view 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 course work

Figure 4

3. Go back by clicking on the corresponding button and start working by clicking on the button of the previous window “Continue”

Figure 5

4. After reading 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 certain students study and what subject the teachers teach.

5. Also from this window we can go to requests. 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 an electronic version of the examination sheet.

6. Click on the button “View the list of tests 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 “Run” button. The request will show the total number of hours per week for studying the discipline you entered, and will also 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. You can also create an additional examination sheet on this sheet. For students with a grade of 2. From this sheet we can go back to the queries.

Figure 9

8. Click on the button “Go to 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 “Generate diploma insert” button

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 also appear. On the same sheet you can view an 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 using the red cross in the upper right corner of the screen.

Figure 12

10. Click on the button “View group list in descending order of 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

Let's select from the drop-down list the discipline that interests us, for example, economics, and enter its name in the box under the drop-down list. Next, click 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. Creating tables

The Database Desktop utility was used to create the tables. You can launch it - Start/Programs/Borland Delphi 7/Database Desktop. You need to configure the utility's working directory. 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. Creating a table YchebPlan (Curriculum):

Figure 17

5. Creating the Disciplina table:

Figure 18

6. Creating the Student table:

Figure 19

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

Figure 20

5. Creating a Prepod table:

Figure 21

8. Creating the Izuchaet table (Discipline-Student):

Figure 22

13. Creating an application in Delphi

In order to create a new application, you need to 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), to do this, select New/Form from the File menu.

1. Table. Filling with data. Data display.

In order to display a table on a form, you need to place the following 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; in the Object Inspector, in the DataSource property, specify the desired data source.

Figure 24

· DBNavigator (on the DataControls tab) - required for moving through table records. In the Object Inspector, the DataSource property specifies the same data source as in the DBGrid. The 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 accessing data. 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

Creating buttons.

To close the form correctly, place a Button component 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 table captions

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

Figure 30

16. Create a drop-down list

To select a command from an existing list, use the ComboBox component (drop-down list). You can fill it out like this:

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

Unit1 module description

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.

Unit2 module description

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;

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

end;

procedure TForm5.Edit1Change(Sender: TObject);

begin

Query1.Open;

end;

procedure TForm5.Button1Click(Sender: TObject);

begin

Form11.show;

Form5.Close;

end;

Unit 6 module description

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

Description of the Unit 9 module

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

else

begin

Query1.Params.Value:=0;

edit1.Text:="Enter the name of the graduate!";

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

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

Label2: TLabel;

Label3: TLabel;

Button7: ​​TButton;

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

Similar documents

Creating tables and designing database management systems. Infological design. Relational database schema. Application value of systems: a report on suppliers and the goods they supply. Statement of the availability of goods in the store.

course work, added 12/01/2008


Development of a database with information about employees, products, with a directory of product types using a database management system MySQL data using SQL queries. Development of an information model of the subject area. Structure of tables, database fields.

test, added 04/13/2012


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

course work, added 12/17/2009


Basic concepts of database and database management systems. Types of data they work with Microsoft database Access. Classification of DBMS and their main characteristics. Post-relational databases. Trends in the world of modern information systems.

course work, added 01/28/2014


Features of the development of an information model and the creation of a relational database structure. Database design basics. Development of tables, forms, queries to display information about the corresponding model. Working with databases and their objects.

course work, added 11/05/2011


Characteristics and functionality database management systems Microsoft Office Access. Definition of the main classes of objects. Development of the "Office Management" database. Create tables, forms, queries, reports and data diagrams.

abstract, added 12/05/2014


Development trend of database management systems. Hierarchical and network models of DBMS. Basic requirements for a distributed database. Distributed query processing, interoperability. Data replication technology and multi-tier architecture.

abstract, added 11/29/2010


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

course work, added 04/28/2011


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

thesis, added 01/25/2013


Identification of information objects and their information model. Logical structure of a relational database. Development of tables in a database management system Access data. Creating queries, forms and reports in the Access DBMS. 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 methods of accessing databases and the program interfaces used in these access methods. The integration of Visual C# applications with the Microsoft SQL Server 2012 DBMS server is considered. The development of the Travel Agency information system 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.

Key words: 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 given goal. The information system being developed will be built using client-server technology. In such systems, information is stored on the server, and the information system interface is stored on client computers, through which users of the information system gain access to data.

When developing an information system, you have to solve two main problems:

• the task of developing a database designed to store information;
• development task GUI user of client applications.

The “Travel Agency” database is created on Microsoft SQL Server 2012. The database stores information about the clients of this travel agency (tourists), about the tours offered to them, about the registration and payment of vouchers. At the database design stage, the tables “Tourists”, “Tours”, “Vouchers”, “Seasons”, “Payment” are created. Relationships between tables are established.

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, a “Users” table is created, which contains information about database users. This table is not related to other tables. The structure of this table is: user code, last name, position, login and password. Only the system administrator can make changes to this table.

SQL Server security is built around two concepts: authentication and authorization. System administrator SQL Server security manager creates a separate login object for each user. This object contains the SQL Server user account name, password, full name and other attributes designed to 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 in a specific database, the system administrator creates a database username in it and associates it with a specific account. The system administrator grants users certain powers. The sales manager can make changes to the tables “Tourists”, “Vouchers” and change the column “Number of seats” in the “Tours” table after selling the next tour. A travel agency employee can make changes to the “Seasons” and “Tours” tables. Accountant and cashier – in the “Payment” table.

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

USE Travel Agency

GRANT SELECT, UPDATE, INSERT, DELETE

ON Tourists

A similar instruction is created for working with the “Vouchers” table. To give the manager the right to change only one column of the “Tours” table, after the table name, the name of the modified Tours (Number of_seats) column is indicated in parentheses. Provided operations SELECT, UPDATE.

There are four instructions in the Data Control Language (DCL): COMMIT, ROLLBACK, GRANT, and 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 operation restrictions. 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. Transaction conflict does not occur 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. An SQL transaction ends with one of two statements: COMMIT (commit) or ROLLBACK (roll back). If a transaction ends with a ROLLBACK statement, all of its statements are canceled and the database is returned to its original state. A normal transaction can execute in one of two modes: READ-WRITE (read-write) or READ-ONLY (read-only). A transaction can be set to one of the following isolation levels: SERIAIZABLE (sequential execution), REPEATABLE READ (repeated read), READ UNCOMMITED (read uncommitted data). The defaults are READ-WRITE and SERIAIZABLE. The default SQL transaction characteristics are generally suitable for most users.

The application is created in the environment Visual Studio 2012 using the C# programming language. Design software product begins with the development 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 points contains sub-points. Each function will be executed in its own window. A MenuStrip element is installed on the main application window, and menu options are generated. A PictureBox element is placed on the form window. The drawing is loaded into the element area. The drawing should occupy the entire area. The SizeMode property sets the image scaling. For this property, StretchImage is selected from the drop-down list, and the image 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 “Use data-bound elements” checkbox is checked; if this checkbox is checked, data binding options open. The comboBox element 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, and textBox2 is installed to enter the password. For the textBox1 and textBox2 elements, the UsesSystemPasworChar property is set to true, which specifies whether the text in the text box should be rendered as password characters by default. Two command buttons are installed: “Login” and “Change user”.

When you bind a comboBox to the Users table, in program code The Form1_Load event handler appears on the form.

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, you need to enter user information and click the “Login” button. When the form is loaded, the user last names contained in the Users table are loaded into the comboBox1 control. Lines are added to this handler that make the menu unavailable, the “Change User” button, and in the comboBox1 element no element 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 this last name in the “Users” table, 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, his login 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 = "";

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, Login, Password FROM Users " +

" WHERE (Last Name = @fam) and (Password = @pasw)";

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 the operation of the “Login” button click event handler.

The connstr line contains the connection string. The text of the generated query is written in the sql line, starting with the select statement, after which the selected fields from the tables that are indicated after the word from 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, login and password. The button1_Click handler opens a SqlConnection. Next, the handler executes the SQL command stored in the cmdkod object.

cmReader = 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. The SqlDataReader method is used for sampling. 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 login or password was entered. In this case, a message about invalid data entered is displayed, and the cmReader and SqlConnection objects are closed. If the user information 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 unavailable. The comboBox1 element also becomes unavailable (Figure 2)

Figure 2. View of the main window after the user logs in

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

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

This article describes some methods for developing applications that work with databases, a way to organize access to work with the system for a limited number of people, and ways to integrate Visual C# applications with the Microsoft SQL Server 2012 DBMS server. sharing Using the Visual C# programming language with SQL, you can create powerful applications with a wide range of capabilities. The main strength of SQL is in data retrieval. No matter how many rows there are in a table, they can be retrieved using 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 convenient interfaces for entering and viewing data. The most common method of combining SQL with procedural languages ​​is called SQL 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 the SQL code. Basic variables are used for this. In order for SQL to recognize these variables, they must be declared. Variables are declared in the form class description area before the program code description. In the program code, a new instance of the SqlConnection object is created to provide a connection to the SQL server. The SqlCommand object enables execution of an embedded SQL command.

References:

1. Allen Taylor. SQL for dummies, 8th edition: Transl. from English – M.: LLC “I.D. Williams”, 2014. – 416 p.
2. Gainanova R.Sh. Development of applications for working with MS SQL Server 2012 databases // Fundamental and applied sciences today: Proceedings 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 of C# applications. - M.: KUDRITS-OBRAZ, 2003, - 512 p.

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

To do this, let's create an empty Windows application. Environment appearance

development is shown in Figure 39.

Rice. 39. Blank application

Figure 39 highlights the “Data” component group, which contains components for accessing and manipulating data.

The binding of 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. Binding Source component on the form

The component is not visual, so it is displayed in an 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 configure its value. When you select this property in the properties window, the following window appears (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 choice of data sources:

Database - Database;

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

Object - Object for selecting an object that will generate data and objects to work with it.

In our case, you need to select the “Database” item. A window for selecting a data connection 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 database type, its location, user names, security features, etc.

The dialog drop-down list contains all previously created connections. If the required connection is not in the list, then you should use the “New connection” button. Pressing the button causes the following dialog to appear (Fig. 44).

In this dialog, you select the data source type (in this case, Microsoft Access), the database name (in this case, the name and location of the database file), and 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 ensure that the entered parameters are correct and the connection is working.

Rice. 44. Creating a new connection

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

Rice. 45. Selecting the necessary tables

In this window, the “Tourists” and “Tourist Information” tables are selected. Since no objects other than tables were created in the database, only tables are displayed in Figure 45. This completes the creation of the data source. After clicking the “Finish” button, a DataSet component appears on the form next to the BindingSource component.

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 Adding”, “Enable Editing”, “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 be attached to the parent container.

In order for the component to display data, you must select a data source in the drop-down list. Selecting the drop-down list causes the following dialog to appear (Fig. 47).

Rice. 47. Selecting a data source for DataGridView

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

Rice. 48. The DataGridView component displays the table structure

The figure shows that another BindingSource component and a TableAdapter component have appeared, working with the “Tourists” table. Please note that in design-time or during the development process, the data from the table is not displayed.

Now you need to display the data from the linked table “Tourist Information”. To do this, place another DataGridView component on the form and select the following as a 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 connection (Binding Source) between the “Tourists” and “Tourist Information” tables. This selection ensures that only those rows from the Tourist Information table that are associated with the current row in the Tourists table are selected. This choice also ensures that associated data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.

Rice. 50. Database application at work

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

Rice. 51. BindingNavigator component on the form

This component allows you to navigate between table records, add and delete table rows. The functionality and appearance of the component can be customized because it is a ToolStripContainer menu strip.

The property that determines the table through which navigation is performed is the BindingSource property. Let's set the value of this property to "touristsBindingSource". In operation, the component looks like this (Fig. 52).

Rice. 52. BindingNavigator component at work

Editing data in the cells of the DataGridView component with appropriate settings is possible, but it is 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, place a container of the Panel type on the form, and on it three TextBox components as follows (Fig. 53).

Rice. 53. Screen panel for editing entries in the “Tourists” table

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

Rice. 54. Property “DataBindings - Advanced”

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

For the top TextBox component, in the Binding drop-down list, select “touristsBmdmgSource” as the data source and the source field as “Last Name”. For the middle and bottom TextBox components, select the same data source and the “Name” and “Patronymic” fields, respectively.

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

Rice. 55. Dialog window for the “DataBindings - Advanced” property

Rice. 56. Data binding to visual components

However, when changes are made, all new data remains only on the form. They are not saved in the database, and when the application is called again, of course, they will be missing. This occurs because the data was loaded into a DataSet object, which is an in-memory copy of the table. All actions are performed with this copy. In order for 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 in the Click event handler:

touristsTableAdapterUpdate(bDTur_firmDataSet); information_about_touristsTableAdapter.Update(bDTur_firmDataSet);

This code updates 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 an individual table row and a group of rows.