Welcome to my “Management database software.”
1. The main tasks of database software
When working with relational databases, we can conditionally distinguish two main tasks:
The actual work with the database, including the creation and maintenance of a database (creating table structures, adding a record to the table, deleting the record, updating, selecting the required record);
creating custom applications, including the development of a user interface for working with the database.
To solve these problems, modern DBMSs in their composition can contain the following software tools: procedural step-by-step programming languages, visual programming tools (graphical interface, project dispatcher, wizards, and builders), object-oriented application creation tools. In addition, the development of user programs in many databases allows the use of other programming languages, as well as the use of libraries of all kinds. For example, when working with DBMS ACCESS, you can use the ACCESS programming language, the ACCESS master, and the VISUAL BASIC programming language.
When working with client-server systems, the situation is slightly more complicated. Here, two types of computers (server and client) participate in the work and, accordingly, distinguish between client and server software. Server software includes a programming language that supports the creation and maintenance of a database, as well as the implementation of customer requests from the clients to the database. User applications are created and run on client computers. These computers should have, along with the means of forming queries to the database, interface development tools. In this regard, for client-server DBMS software is divided into two parts: software - client and software - server. Note that along with the client software, other programming languages, special libraries, other programming systems (defined for this DBMS) can be used to develop custom programs in a particular DBMS. As an example, the table shows possible options for using the software to organize client-server interaction in the Microsoft SQL Server database.
When working with relational databases, we can conditionally distinguish two main tasks:
The actual work with the database, including the creation and maintenance of a database (creating table structures, adding a record to the table, deleting the record, updating, selecting the required record);
creating custom applications, including the development of a user interface for working with the database.
To solve these problems, modern DBMSs in their composition can contain the following software tools: procedural step-by-step programming languages, visual programming tools (graphical interface, project dispatcher, wizards, and builders), object-oriented application creation tools. In addition, the development of user programs in many databases allows the use of other programming languages, as well as the use of libraries of all kinds. For example, when working with DBMS ACCESS, you can use the ACCESS programming language, the ACCESS master, and the VISUAL BASIC programming language.
When working with client-server systems, the situation is slightly more complicated. Here, two types of computers (server and client) participate in the work and, accordingly, distinguish between client and server software. Server software includes a programming language that supports the creation and maintenance of a database, as well as the implementation of customer requests from the clients to the database. User applications are created and run on client computers. These computers should have, along with the means of forming queries to the database, interface development tools. In this regard, for client-server DBMS software is divided into two parts: software - client and software - server. Note that along with the client software, other programming languages, special libraries, other programming systems (defined for this DBMS) can be used to develop custom programs in a particular DBMS. As an example, the table shows possible options for using the software to organize client-server interaction in the Microsoft SQL Server database.
2. The problems of creating and maintaining relational databases
When creating a database and organizing work with it, there are three main problems:
the actual creation of a database (the creation of tables, indexes, integrity constraints);
security and access control;
organization of access to the elements of the table (selection, editing, deletion, addition).
The first problem can be solved by creating in each particular DBMS some utility that allows the user at a certain moment to perform all the necessary actions to create a database. However, this does not completely solve the problem. Such a utility does not allow you to create a table dynamically during the operation of the application program, it does not allow, for example, to add to the table the column generated during the operation of the user program. Tools are needed to enable the request to change the structure and content of the database during the operation of the application program. The same can be said about the solution of the second problem.
Let us consider in more detail the possible way of solving the third problem. The organization of access to the database is the most important function of the information system. Users are constantly working with data. Let's consider a simple example. Suppose we have a STUDENT table that stores information of the following kind:
STUDENT (Student's code, Surname, First name, Patronymic, Date of admission).
Now we want to execute some query to the database, the result of which should be those rows of the STUDENT table, for which the date of receipt will be greater than 01.06.2006. Consider the sequence of actions to implement this query.
We get access to the STUDENT table and set the current line pointer to the first row of the table.
We analyze the "Date of arrival" field in the current line.
If the value "Date of receipt"> "01.06.2006", print out the information about the applicant on the screen.
If the table does not end, move the pointer of the current line to the next line and go to step 2, otherwise finish the work.
When creating a database and organizing work with it, there are three main problems:
the actual creation of a database (the creation of tables, indexes, integrity constraints);
security and access control;
organization of access to the elements of the table (selection, editing, deletion, addition).
The first problem can be solved by creating in each particular DBMS some utility that allows the user at a certain moment to perform all the necessary actions to create a database. However, this does not completely solve the problem. Such a utility does not allow you to create a table dynamically during the operation of the application program, it does not allow, for example, to add to the table the column generated during the operation of the user program. Tools are needed to enable the request to change the structure and content of the database during the operation of the application program. The same can be said about the solution of the second problem.
Let us consider in more detail the possible way of solving the third problem. The organization of access to the database is the most important function of the information system. Users are constantly working with data. Let's consider a simple example. Suppose we have a STUDENT table that stores information of the following kind:
STUDENT (Student's code, Surname, First name, Patronymic, Date of admission).
Now we want to execute some query to the database, the result of which should be those rows of the STUDENT table, for which the date of receipt will be greater than 01.06.2006. Consider the sequence of actions to implement this query.
We get access to the STUDENT table and set the current line pointer to the first row of the table.
We analyze the "Date of arrival" field in the current line.
If the value "Date of receipt"> "01.06.2006", print out the information about the applicant on the screen.
If the table does not end, move the pointer of the current line to the next line and go to step 2, otherwise finish the work.
In this case, the application developer himself organizes the work of sampling data, programming each movement on the table (navigates the table). This approach to data processing, oriented to sequential work with individual records, is called navigational. Here, in a specific programming language, we describe the procedure - the sequence of actions necessary to obtain the result. The languages in which this approach is used are called procedural. Obviously, as the complexity of the query increases, the complexity of the procedure increases significantly and, correspondingly, the volume of the text of the program.
When working with the information system, the user implements his queries to the database with the help of application programs developed by programmers. When navigating, all possible requests must be programmed. Obviously, it is impossible to anticipate all the requests in advance, the need for which can arise and program them. Given that the vast majority of users do not have programming skills, this means that the scope of their actions will be limited to the scope of written programs, namely, those requests, the implementation of which is provided in advance.
In addition, the mechanism of interaction within the framework of the client-server architecture should be taken into account. The user program runs on the client computer. The request to the database is realized by the computer-server. A mechanism is needed to create a query in the user's client program for the server database. In this case, the navigation approach is unacceptable. In this regard, to work with databases, another approach is developed and used based on the use of so-called query languages, which specify not the sequence of necessary actions, but the conditions that the result must satisfy (when adding a column, selecting an entry, adding an entry, and t . P.). This approach solves all three of the above problems.
For this purpose, developed and actively used in all databases - a special language for SQL queries. We should especially note that the basis of the language is the operations of relational algebra.
The SQL language (Structured Query Language) is used to communicate the user to a relational database and consists of three parts.
DDL (Data Definition Language) is a data definition language. It is intended for creation of a database (tables, indexes, etc.) and editing of its scheme.
DCL (Data Control Language) - the language of data management. Contains operators to distinguish between user access to database objects.
DML (Data Manipulation Language) - the language of data processing. Contains operators for making changes to the contents of database tables.
As you can see from the above, SQL solves all the issues discussed earlier, providing the user with a fairly simple and understandable mechanism for accessing data, not related to the design of the algorithm and its description in the high-level programming language. So, instead of indicating how to act, the user, using SQL statements, explains the DBMS, what he needs to do. Next, the DBMS itself analyzes the query text and determines how to execute it.
In the client-server architecture, the SQL language is very important. It is used as the language of communication of the client software with a server DBMS located on a remote computer. So, the client sends a request to the server in the SQL language, and the server parses it, interprets it, selects the execution plan, executes the query, and sends the result to the client.
Let's see how the query in the SQL language looks like, which solves the problem of selecting students by the date of admission.
It may be a false impression that the emergence of the SQL language is an alternative to high-level programming languages. This is not true. The execution of the query by means of SQL, all the same, boils down to working with individual records, and this does not go anywhere. It is important to understand that the emergence of the SQL language gave at least two new possibilities.
A new level of abstraction has emerged between the user and the DBMS. This level is closer to the user than the level of programming in a high-level language, which reduces the requirements for user qualification.
Many typical tasks that arise when working with databases, and previously solved by each programmer in their own way (often duplicating the actions of another programmer) are solved by the implementation of the SQL language. Thus, the need to solve many of the problems solved in the DBMS in an appropriate manner has disappeared. The SQL language provides the means to access these typical database capabilities.
When working with the information system, the user implements his queries to the database with the help of application programs developed by programmers. When navigating, all possible requests must be programmed. Obviously, it is impossible to anticipate all the requests in advance, the need for which can arise and program them. Given that the vast majority of users do not have programming skills, this means that the scope of their actions will be limited to the scope of written programs, namely, those requests, the implementation of which is provided in advance.
In addition, the mechanism of interaction within the framework of the client-server architecture should be taken into account. The user program runs on the client computer. The request to the database is realized by the computer-server. A mechanism is needed to create a query in the user's client program for the server database. In this case, the navigation approach is unacceptable. In this regard, to work with databases, another approach is developed and used based on the use of so-called query languages, which specify not the sequence of necessary actions, but the conditions that the result must satisfy (when adding a column, selecting an entry, adding an entry, and t . P.). This approach solves all three of the above problems.
For this purpose, developed and actively used in all databases - a special language for SQL queries. We should especially note that the basis of the language is the operations of relational algebra.
The SQL language (Structured Query Language) is used to communicate the user to a relational database and consists of three parts.
DDL (Data Definition Language) is a data definition language. It is intended for creation of a database (tables, indexes, etc.) and editing of its scheme.
DCL (Data Control Language) - the language of data management. Contains operators to distinguish between user access to database objects.
DML (Data Manipulation Language) - the language of data processing. Contains operators for making changes to the contents of database tables.
As you can see from the above, SQL solves all the issues discussed earlier, providing the user with a fairly simple and understandable mechanism for accessing data, not related to the design of the algorithm and its description in the high-level programming language. So, instead of indicating how to act, the user, using SQL statements, explains the DBMS, what he needs to do. Next, the DBMS itself analyzes the query text and determines how to execute it.
In the client-server architecture, the SQL language is very important. It is used as the language of communication of the client software with a server DBMS located on a remote computer. So, the client sends a request to the server in the SQL language, and the server parses it, interprets it, selects the execution plan, executes the query, and sends the result to the client.
Let's see how the query in the SQL language looks like, which solves the problem of selecting students by the date of admission.
It may be a false impression that the emergence of the SQL language is an alternative to high-level programming languages. This is not true. The execution of the query by means of SQL, all the same, boils down to working with individual records, and this does not go anywhere. It is important to understand that the emergence of the SQL language gave at least two new possibilities.
A new level of abstraction has emerged between the user and the DBMS. This level is closer to the user than the level of programming in a high-level language, which reduces the requirements for user qualification.
Many typical tasks that arise when working with databases, and previously solved by each programmer in their own way (often duplicating the actions of another programmer) are solved by the implementation of the SQL language. Thus, the need to solve many of the problems solved in the DBMS in an appropriate manner has disappeared. The SQL language provides the means to access these typical database capabilities.
No comments:
Post a Comment