7.2. The main objectives of System R and their connection to the system architecture

The main goals of the System R developers were the following:

1. provide a high-level, user-friendly interface with the system, allowing to achieve data independence and enable users to work as efficiently as possible;
2. provide a variety of acceptable ways to use the DBMS, including programmable transactions, online transactions and report generation;
3. maintain a dynamically changing database environment in which relationships, indexes, views, transactions, and other objects can be easily added and destroyed without interrupting the normal functioning of the system;
4. to provide the possibility of parallel work with one database of many users with the assumption of parallel modification of database objects in the presence of the necessary means of protecting the integrity of the database;
5. provide a means of restoring a consistent state of databases after various kinds of hardware or software failures;
6. provide a flexible mechanism that allows you to define various representations of stored data and limit these views to user access to the database by sampling and modification based on an authorization mechanism;
7. ensure system performance when performing the above functions, comparable to the performance of existing low-level DBMS.

First of all, we note that basically the goals set for the development of System R were achieved. We now consider the means by which these goals were achieved, and how we can more accurately interpret them in the context of System R.

The basis of System R is the relational language SQL. Sometimes it is called a query language or a data manipulation language, but in fact its capabilities are much wider. Using SQL tools (with appropriate system support) many of the goals are solved. The SQL language includes tools for dynamic query compilation, on the basis of which it is possible to build interactive query processing systems. Dynamic parameterization of statically compiled queries is allowed, with the result that it is possible to build effective (not requiring dynamic compilation) dialog systems with standard sets of (parameterized) queries.

SQL tools determine all database objects available to a user: tables, indexes, views. There are means to destroy any such object. The corresponding language operators can be executed at any time, and the ability to perform an operation by this user depends on the rights previously granted to him.

With regard to database integrity, in System R, the integrity of the database is understood to be a state that satisfies the set of integrity predicates stored in the database. These predicates, called in System R integrity conditions (assertions), are also defined by means of the SQL language. Any language clause is executed within a certain transaction — indivisible in the sense of the state of the database of the sequence of language sentences. Incompleteness means that all changes made within one transaction are either completely displayed in the database state or completely absent in it. The latter possibility arises when a transaction is rolled back, which can occur at the initiative of the user (when executing the corresponding SQL statement) or at the initiative of the system.

One of the reasons for the rollback of a transaction initiated by the system is the violation of the integrity of the database as a result of the actions of this transaction (we will consider other possible conditions for rolling back a transaction initiated by the system later). The SQL language contains a means of setting the so-called save points. With a user-initiated transaction rollback, you can specify the number of the savepoint above which the rollback does not apply. The transaction initiated by the system is rolled back to the nearest save point, in which the condition that caused the rollback is no longer present. In particular, a rollback initiated due to a violation of the integrity condition is performed to the nearest save point, in which the integrity conditions are met. (Note that the save point installation tools are missing in the commercial extensions System R).

Naturally, in order to actually roll back a transaction, it is necessary to memorize some information about the progress of the transaction. In System R, for these and other purposes, a special data set is used - a log in which records of all transactions changing the state of the database are placed. When a transaction is rolled back, a process of reversing the transaction (undo) occurs, during which all the changes stored in the log are performed in the reverse order.

In SQL, there is a means of determining the so-called conditional effects (triggers), allowing you to automatically maintain the integrity of the database when modifying its objects. A conditional action is a cataloged modification operation for which a condition for its automatic execution is set. The presence of such a device is especially significant due to the presence of the database views discussed below, which may restrict database access for a number of users. It is possible that such users simply cannot maintain the integrity of the database without automatically executing conditional actions, since they simply do not “see” the entire database and, in particular, cannot imagine all the limitations of its integrity. Note that, with the exception of the early publications on System R, the implementation of the mechanism of conditional effects was not described anywhere, although in principle the approaches to implementation are quite understandable. This mechanism is not implemented in commercial systems arising on the basis of System R. Apparently, this is due to the additional overhead costs that are unpredictable for users when performing transactions.

The SQL language contains view definitions. A view is a memorized, named query for fetching data (from one or more tables). Since SQL is a relational language, the result of executing any query on a sample is a table, and therefore you can conceptually treat any view as a table (when defining a view, you can, in particular, assign names to the fields of this table). In the language, the use of previously defined representations is allowed almost everywhere where the use of tables is allowed (with some restrictions on the possibility of modification through representations). Having the ability to define views in conjunction with a developed authorization system allows you to restrict the access of some users to the database by a dedicated set of views.

Database access authorization is also based on SQL tools. When creating any database object, the user performing this operation becomes the absolute owner of this object, i.e. can perform with respect to this object any function from a predefined set. Further, this user can execute an SQL statement, meaning the transfer of all his rights to this object (or their subsets) to any other user. In particular, this user may be transferred the right to transfer all rights transferred to him (or their parts) to a third user, etc. One of the rights of the user in relation to the object is the right to withdraw from other users all or some of the rights that were previously transferred to them. This operation extends transitively to all subsequent heirs of these rights.

The presence in the language of means for determining views and authorization, in principle, makes it possible to use System R without a traditional database administrator, since almost all system actions are based on SQL tools. However, if an organizational database administrator is required, then its work is rather simplified due to a unified set of management tools. In addition, in System R, database directories are also supported in the form of tables, and all SQL queries are applied to them. Note that a number of additional utilities that are not related to the SQL language (for example, utilities for collecting statistics or mass database loading) have appeared in commercial DBMS, and in these systems, apparently, a database administrator is indispensable.

In terms of ensuring the parallel operation of many users with one database, the basic approach of System R is that the user does not have to be aware of the presence of other users competing with him for access to the database, i.e. the system is responsible for ensuring the isolation of users with the guarantee of the absence of their mutual influence within the limits of transactions. From this it follows, firstly, that the user interface with the system (that is, SQL language) should not have means for regulating interactions with other users and, secondly, that the system should provide automatic serialization of a set of transactions, i.e. . to provide the execution mode of this set of transactions, equivalent in the final result to some sequential execution of these transactions. This problem is solved in System R due to the automatic execution of synchronization captures in relation to all changeable database objects. There are a number of subtleties associated with such synchronization, which we dwell below.

One of the main requirements for DBMS in general and for System R in particular is to ensure the reliability of databases with respect to various types of failures. Such failures may include software errors of the application and system level, processor failures, breakdowns of external media, etc. In particular, the above-mentioned database integrity breaches can be attributed to one of the types of failures, and automatic system-initiated transaction rollback is a system tool for restoring the database after such failures. As we noted, such a recovery occurs by reversing the transaction based on the information about the changes it has made, which is stored in the log. Based on the information of the journal database recovery and after failures of another kind. Management of logging and recovery in System R is very interesting, the methods used in some cases differ from the methods used in other DBMS.

As for the natural requirements for system efficiency, here the main decisions are related to the specifics of the physical organization of databases on external memory, the buffering of used database pages in RAM and advanced techniques for optimizing queries formulated in SQL produced at the compilation stage.

The structural organization of System R is quite consistent with the goals set during its development and the solutions chosen. The main structural components of System R are the relational memory management system (RSS) and the SQL query compiler. RSS provides an interface that is rather low, but sufficient for implementing SQL, to access data stored in the database. Transaction synchronization, change logging and database recovery after failures are also among the RSS functions. The query compiler uses the RSS interface to access a variety of reference information (catalogs of relationships, indexes, access rights, integrity conditions, conditional effects, etc.) and produces work programs that are later executed using the RSS interface. Thus, the system is naturally divided into two levels - the level of memory management and synchronization, in fact, independent of the basic query language of the system, and the language level (SQL level), which solves most of the System R. problems. Note that this independence is rather conditional, than absolute: the SQL language can be replaced by another language, but it should have about the same semantics.

Next, we will consistently consider the features of the organization of RSS, the process of compiling and optimizing queries and the technique for performing compiled transactions (including the possibility of dynamic query compilation noted above).