Lecture
The means of defining the database schema in the SQL / 89 standard are among the weakest and most interpretable parts of the standard. Moreover, I don’t know any implementation in which such a set of schema definition tools would be supported.
Therefore, in order to achieve the mobility of an application system in a fairly wide class of SQL / 89 implementations, it is necessary to carefully localize the components of the database schema definition. I think that it is best to concentrate all the work with the database schema in one module and keep in mind that when moving to another DBMS, it is very likely that you will need to rework this module.
Note in particular that in SQL / 89 there are no tools for modifying the database schema at all: there is no possibility to remove the table schema, add a new column to the table schema, etc. In all implementations, such tools are supported, but they can differ in both syntax and semantics.
Despite the lack of special hopes that we will be able to meet an implementation that supports the SQL / 89 schema definition language, we will briefly describe this language (without syntactic details) in order to evaluate at a substantive level the possibilities of SQL / 89 in this part and get at least some then a means of comparing different implementations.
In accordance with the rules of SQL / 89, each table in this database has simple and qualified names. The name qualifier is the "identifier of authority" of the table, which is usually in implementations the same as the name of some user, and the qualified name of the table is:
<authority id>. <simple name>
The approach to defining a schema in SQL / 89 is that all tables with the same credential identifier are created (defined) by executing one schema definition statement. At the same time, the standard does not define the way the statement for determining a schema is executed: should it be executed only in interactive mode or can be embedded in a program written in a traditional programming language.
The statement for defining a schema contains an authority identifier and a list of schema elements, each of which can be a table definition, a view definition, or a privilege definition. Each of these definitions is represented by a separate SQL / 89 statement, but all of them, as already mentioned, must be embedded in the schema definition statement.
For these operators, we present the syntax, since this will allow us to more clearly describe their features.
The table definition statement has the following syntax:
<table definition> :: =
CREATE TABLE <table name> (<table element>
[{, <table element>} ...])
<table element> :: =
<column definition>
| <table constraint definition> In addition to the table name, the operator specifies a list of table elements, each of which serves either to define a column or to define a constraint on the integrity of the table being defined. At least one column definition is required. The CREATE TABLE statement defines the so-called base table, i.e. real data storage.
To define table columns and integrity constraints, special operators are used that must be nested in a table definition operator.
The column definition operator is described by the following syntax rules:
<column definition> :: =
<column name> <data type>
[<default clause>] [<column constraint> ...]
<default clause> :: =
DEFAULT {<literal> | USER | Null}
<column constraint> :: =
NOT NULL [<unique specification>]
| <references specification>
| CHECK (<search condition>) As you can see, in addition to the obligatory part, in which the column name and its data type are defined, the column definition can contain two optional sections: the default column value section and the column integrity constraints section.
The default value section specifies the value to be placed with the row stored in this table, unless the value of this column is explicitly indicated. The default value can be specified as a literal constant with the type corresponding to the type of the column; by specifying the keyword USER, which, when executing the string entry operator, corresponds to a character string containing the name of the current user (in this case, the column must have the type of character strings); or by setting the keyword NULL to mean that the default value is an undefined value. If the default column value is not specified, and NOT NULL is specified in the column integrity constraints section, an attempt to add a row to the table with an unspecified value of this column will result in an error.
Specifying NOT NULL integrity constraints in the section results in the implicit generation of a check integrity constraint for the entire table (see the next section) "CHECK (C IS NOT NULL)" (where C is the name of this column). If the NOT NULL constraint is not specified, and there is no default section, then the default section DEFAULT NULL is generated implicitly. If the specification of uniqueness is specified, then the corresponding specification of uniqueness for the table is generated.
If the reference constraints section of the integrity constraint is specified in the column (<reference specification>), then the corresponding definition of the reference constraint for the table is generated:
FOREIGN KEY (C) <reference specification>.
Finally, if a check constraint is specified for a column, then the search condition for this constraint should refer only to this column, and the corresponding check constraint for the entire table is implicitly generated.
Table integrity constraints have the following syntax:
<table constraint definition> :: =
<unique constraint definition>
| <referential constraint definition>
| <check constraint definition>
<unique constraint definition> :: =
<unique specification> (<unique column list>)
<unique specification> :: = UNIQUE | PRIMARY KEY
<unique column list> :: = <column name> [{, <column name>} ...]
<referential constraint definition> :: =
FOREIGN KEY (<referencing columns>) <references specification>
<references specification> :: =
REFERENCES <referenced table and columns>
<referencing columns> :: = <reference column list>
<referenced table and columns> :: =
<table name> [(<reference column list>)]
<reference column list> :: = <column name> [{, <column name>} ...]
<check constraint definition> :: = CHECK (<search condition>) Several integrity constraints can be specified for a single table, including those implicitly generated by column integrity constraints. The SQL / 89 standard states that table constraints are actually checked during the execution of each SQL statement.
Note: The presence of a properly selected set of database constraints is very important for the reliable operation of an application information system. However, in some DBMS integrity constraints are practically not supported. Therefore, when designing an application system, it is necessary to make a decision on what is more significant: rely on the support of integrity constraints, but limit the set of possible DBMSs, or abandon their use at the SQL level, while retaining the possibility of using not the most advanced DBMS.
Further, T denotes a table for which integrity constraints are defined.
Uniqueness constraint
Each column name in the uniqueness list must name a T column and must not be included in this list more than once. When defining a column in the uniqueness list, the NO NULL column constraint must be specified. Among the limitations of the uniqueness of T, there should not be more than one definition of a primary key (a uniqueness constraint with the keyword RIMARY KEY).
The effect of restricting uniqueness is that two or more rows are not allowed in table T, the values of the columns of which are unique.
Link Restriction
The constraint on references from a given set of columns CT of table T to a given set of columns CT1 of some table T1 defined at that time determines the condition on the contents of both of these tables, where the links can be considered correct.
If the list of columns CT1 is explicitly specified in the definition of a constraint by reference, then this list is required to be explicitly included in any definition of the uniqueness of the table T1. If the list of CT1 is not specified explicitly in the definition of the constraint on the references of table T, then the definition of the primary key is required in the definition of table T1, and the list of CT1 is implicitly assumed to be the same as the list of column names from the definition of the primary key of table T1. The column names of the lists CT and CT1 must name the columns of tables T and T1, respectively, and should not appear in the lists more than once. The lists of columns CT and CT1 must contain the same number of elements, and the column of table T identified by the i-th element of the list CT must be of the same type as the column of table T1 identified by the i-th element of the list CT1.
By definition, tables T and T1 satisfy a given constraint by reference, if for each row s of table T such that all values of columns s identified by list CT are not undefined, there is row s1 of table T1 such that values of columns s1 identified by list CT1, positionally equal to the values of the columns s, identified by the list CT. Humanly, this can be formulated as follows: the restriction on references is satisfied if for each valid reference there is an object to which it refers. In the terminology familiar to programmers, the restriction on links does not allow producing "hanging" links that do not lead to any object.
Check limit
The check constraint specifies the condition that each row of table T must satisfy individually. This condition must not contain subqueries, specifications of aggregate functions, or references to external variables or parameters. It can only include the column names of a given table and literal constants.
A table satisfies a check integrity constraint if and only if the condition calculation for each row of the table is true.
Note: In some implementations, extended link restriction and verification constraint mechanisms are allowed. You should be careful if you do not want to go beyond the limits of the standard.
The view mechanism is a powerful tool in the SQL language, which allows you to hide the actual database structure from some users by defining the database view, which is actually some query stored in the database with named columns, and for the user is no different from the base database table (taking into account technical limitations). Any implementation must ensure that the state of the table being presented exactly matches the state of the base tables on which the view is defined. Usually, the calculation of the presented table (materialization of the corresponding query) is performed each time the view is used.
In SQL / 89, the view definition statement has the following syntax:
<view definition> :: =
CREATE VIEW <table name> [(<view column list>)]
AS <query specification> [WITH CHECK OPTION]
<view column list> :: = <column name> [{, <column name>} ...] The defined represented table V is changeable (that is, you can use the DELETE and UPDATE statements with respect to V) if and only if the following conditions are fulfilled for the query specification:
Note: These restrictions are very strong. In implementations, they can be weakened. But if you strive for mobility, you should not use advanced features.
If there is at least one arithmetic expression in the query specification list that consists of more than one column specification, or if one column name participates in the selection list more than once, the view definition must contain a list of the column names of the table being represented. More simply, you need to explicitly name the columns of the table being represented if these names are not inherited from the columns of the tables in the FROM section of the query specification.
The WITH CHECK OPTION clause in the view definition makes sense only in the case of the definition of the table being changed, which is determined by the specification of the query containing the WHERE clause. If this requirement is present, no changes to the presented table are allowed, which lead to the appearance in the base tables of rows that are not visible in the represented table (ie, such rows that do not satisfy the search condition of the WHERE clause of the query specification). If WITH CHECK OPTION is absent in the view definition, this control is not performed.
In accordance with the ideology of the SQL language, the control of the access rights of the given user to the database tables is based on the privilege mechanism. In fact, this mechanism is that to perform any action on the table, the user must have the appropriate privilege (in fact, all possible actions are described by a fixed standard set of privileges). The user who created the table automatically becomes the owner of all possible privileges to perform operations on this table. These privileges include the privilege to transfer all or some privileges in relation to this table to another user, including the privilege to transfer privileges. Sometimes the reverse operation of removing privileges from a user who previously received them is also supported.
SQL / 89 defines a simplified privilege mechanism schema. First, the “distribution” of privileges is possible only when defining a table. Secondly, a user who has received some privileges from other users can only transfer them further when defining a scheme.
The definition of privileges is done in the following syntax:
<privilege definition> :: =
GRANT <privileges> ON <table name> TO <grantee>
[{, <grantee>} ...] [WITH GRANT OPTION]
<privileges> :: =
ALL PRIVILEGES
| <action> [{, <action>} ...]
<action> :: = SELECT | INSERT | DELETE
| UPDATE [(<grant column list>)]
| REFERENCES [(<grant column list>]
<grant column list> :: = <column name> [{, <column name>} ...]
<grantee> :: = PUBLIC | <authorization identifier> The meaning of the privilege determination mechanism in SQL / 89 is quite clear from this syntax. We only note that the privilege REFERENCES with respect to the indicated columns of table T1 must be possessed in order to be able to define the constraint on the links between this table and the existing table T1 at the time of defining table T.
Note again that, although in a general sense, privilege-based access protection mechanisms are supported in all SQL-oriented DBMSs, implementations may vary in details. This is again the place that needs to be localized, if you strive to create a mobile application system.
Comments
To leave a comment
Databases IBM System R - relational DBMS
Terms: Databases IBM System R - relational DBMS