- Email: [email protected]
Direct manipulation of a data dictionary with SQL
Inform&on Systems Vol. 16, I%. 3, PP. 323-333, 1991 Printed in Great Britain. All rights reserved
DIRECT
0306-4379191 $3.00 + 0.00 Copyright ‘0 1991 Pergamon Press plc
MANIPULATION OF A DATA DICTIONARY WITH SQL? ROB B. BUITENDIJK’and HARMVANDERLEK’
‘Netherlands *BSO (Bureau
Railways Ltd, Information Postbox 2025, 3500
and Automation Coordination (IAC), NL, The Netherlands
Moreelsepark
1,
HA, Utrecht
For Systems Development), Department of Management Support, P.O. Box 97780, Jan van Nassaustraat 127, 2509 GD, Den Haag NL, The Netherlands
(Received 27 August 1990; received for publication 21 February 1991)
Abstract--Current versions of SQL do not permit a direct alteration of the contents of the data dictionary by means of data manipulation-statements. This article discusses the advantages and limitations if this prohibition is removed. It appears that the benefits outweigh the limitations to a large extent. The results argue for an implementation of the functionality to change the contents of the data dictionary through data manipulation-statements in future versions of SQL.
1.
INTRODUCTION
Relational database management systems (RDBMSs) usually contain an on-line data dictionary that is structured and can be interrogated in the same manner as the user’s database. Consequently, no fundamental difference exists between the retrieval of meta-data and the retrieval of operational data. This concept, which is supported by SQL [2], induces a strong advantage of RDBMSs over non-RDBMSs: a user needs only one language for both operational data and meta-data (without any restriction induced by predefined access paths). The possibility not only to interrogate but also to change the contents of the data dictionary with SQL in the same way as with operational data, is quickly rejected in literature (see, for instance, [3]). This potentiality, further on referenced by DCM (Data Definition and Data control by means of Data &lanipulation), would lead too easily to a destroyed catalog, disabling a correct functioning of the RDBMS. Nonetheless, DCM introduces some very interesting issues. For example, it yields a higher command conciseness and a higher degree of customizability. These attributes are often regarded as favorable for query languages (see, for example, [4, 51). In this article, the concept of DCM is elaborated. The elaboration progresses step by step, and is meant to justify the hypotheses that, contrary to what is currently argued in literature, it is beneficial to incorporate the concept of DCM in future versions of SQL. The structure of this paper is as follows. Section 2 introduces the table definition of a sample and simplified catalog structure. This definition is used throughout the paper. Some standard DDL and DCL-statements are simulated by DML-statementsf in Section 3, showing the essence of DCM. Section 4 introduces a benefit of the new approach: the simulation of functions that are not present in SQL, thus increasing the degree of customizability. Section 5 shows that the principle of DCM also holds for SQL2. A conceptual argument for the proposition that this principle holds for every DDL and DCL-statement is provided in Section 6. The underpinning is based on the natural language principle NIAM, an information modeling methodology [6]. Section 7 discusses the benefits and limitations of DCM. As it turns out, the
tA
Dutch version of this paper appeared earlier in Ref. [l]. $The standard abbreviations DDL, DCL and DML are used for Data Definition Data Manipulation Language, respectively.
Language,
Data Control
Language
and
ROB B. BUITENDIJKand HARM VAN DERLEK
324
benefits outweigh conclusions.
the limitations.
Finally,
2. A SAMPLE
Section 8 is reserved for a summary
CATALOG
and some
STRUCTURE
A DDL or DCL-statement directly interacts with tables in the catalog of the RDBMS. A user of DDL or DCL does not have to be aware of this interaction, and thus neither of the structure of the catalog. This transparency becomes inappropriate, however, if the DML is used to interact with the catalog: in that case, the structure of the catalog (i.c. names of tables and attributes) should be known to the user. In this section, the definition of a catalog structure is presented that is used throughout this article. The presented structure is hypothetical, but shows strong resemblances with structures of catalogs from RDBMSs that are currently commercially available. Its definition is as follows. TABLES (tname, creator, remarks) This table contains for every table: the name of the table, an identification of the person who created the table, and, if appropriate, some remarks about the table. [Note: it is common practice to include attribute ‘creator’ in the primary key. For reasons of simplicity, the attribute is excluded (unless explicitly mentioned otherwise). The exclusion does not violate the results of the following discussions]. COLUMNS
(tnume, uname, atype, length, scale)
This table contains for every attribute: the name of the table in which the attribute participates, the name of the attribute, the data type and length and scale data referring to the data type. TABAUTH (grantor, grantee, tname, selectauth) This table indicates whether a user (‘grantee’) is permitted by the owner (‘grantor’) of a table (‘tname’) to interrogate (‘selectauth’) is ‘Y’ or ‘N’) that table. This structure obviously is a simplification. In RDBMSs as DB2 and Oracle, similar tables include additional attributes. These attributes often contain some systems data (for instance, statistical data, like the number of tuples in a table) that influence the internal working of the system. The values of these attributes are determined by the system itself, and can and may not be changed by users (although the users may be authorized to consult these attributes). For reasons of surveyability, these systems attributes are ignored. (So, in fact, the presented tables are derived views.) In addition to this type of attributes, the catalog of an RDBMS may contain tables and attributes that contain data about primary keys and foreign keys. These tables and attributes are for the moment disregarded, since the examples of SQL’s DDL in the following section do not use any data on primary keys and foreign keys. In Section 5, the catalog structure is extended with these tables and attributes to be able to present some examples of SQL2’s DDL.
3. EXAMPLES
OF DCM FOR
SQL-STATEMENTS
In this section, some example of DDL and DCL-statements are given that are in fact superfluous if the user is authorized to alter the contents of the catalog with DML-statements. Each example is placed in a box, containing a DDL or DCL-statement on the left-hand side, and one or more equivalent DML-statements on the right-hand side. (The DDL and DCL-statements are supposed to be semantically correct.) It is not the intention to provide a complete overview of all possible DDL and DCL-statements, but to show and to make plausible the principles of DCM.
325
Manipulation of a data dictionary with SQL
Example 1. Creating a table DML of SQL
DDL of SQL CREATE TABLE department (depnr INTEGER, description CHAR(20), budget DECIMAl(7,2));
INSERT INTO tables (tname) VALUES (‘department’); INSERT INTO columns (tname,aname,atype) VALUES (~depa~ment’, ‘depnr’, ‘INTEGER’); INSERT INTO columns (tname,aname,atype,length) VALUES (‘department’, ‘description’, ‘CHAR’, 20); INSERT INTO columns (tname,aname,atype, length,scale) VALUES (‘department‘, ‘budget’, ‘DECIMAL’, 7,2);
It should be admitted: the left-hand side looks more attractive than the right-hand side. It is not the intention of this article, however, to suggest that in the future all tables should be created in the way as indicated in the right-hand side. Nonetheless, an apparent conclusion from this example is that the DDL-statement is nothing more than a macro (shorthand) that can generate the sequence of DML-statements. Example 2. Adding an attribute to a table DML of SQL
DDL of SQL ALTER TABLE department ADD (manager CHAR(5));
INSERT INTO columns (tname,aname,atype,length) VALUES (‘department’, ‘manager’, ‘CHAR’, 5);
In this example, the DDL-statement corresponds to exactly one DML-statement. Often, however, more than one DML-statement is necessary to simulate a DDL (or DCL)-statement. The following example shows that an UPDATE-statement can be used to add a comment to a table. Example 3. Adding comment to a table 1 DML of SQL
DDL of SQL COMMENT ON TABLE IS ‘Important table’;
department
It comes as no surprise that a DROP-statement statements, as is shown in the next example.
UPDATE tables SET remarks = ‘Important table’ WHERE tname=‘department’;
corresponds
with a sequence of DELETE-
326
ROB B. BUITENDIJKand HARMVANDER LEK
Example 4. Removing a table DDL of SQL DROP TABLE
DML of SQL
department;
DELETE FROM tables WHERE tname = ‘department’; (DELETE FROM columns WHERE tname = ‘department’;)
The second DELETE-statement is placed in parentheses, because this statement is unnecessary in case the referential integrity regarding the tables TABLES and COLUMNS is taken care of by the RDBMS itself. Also DCL-statements can be simulated by DML-statements. Consider the following example. Example 5. Authorizing a user to interrogate a table DML of SQL
DCL of SQL
SET AUTOCOMMIT
GRANT SELECT ON department TO Buitendijk;
INSERT INTO tabauth (USER, ‘Buitendijk’, ‘department’, ‘Y’); COMMIT
OFF; VALUES
WORK;
UPDATE tabauth SET selectauth = ‘Y’ WHERE grantor = USER AND grantee = ‘Buitendijk’ AND tname = ‘department’; SET AUTOCOMMIT
ON;
(USER is a systems variable that contains the identification of the user that uses SQL.) The example is more complex than the previous ones. The complexity is caused by the fact that table TABAUTH already may contain a tuple (USER, Buitendijk, department, ‘N’), indicating that user Buitendijk is not allowed to read table DEPARTMENT. If this is the case, the INSERT-statement results in duplicate primary key values, which should be rejected. The solution to the problem is found in the use of the COMMIT-concept. The SET AUTOCOMMIT OFF-statement postpones the detection of duplicate primary key values until a COMMIT WORK is given. This statement checks (amongst others) the uniqueness of primary key values and will undo all previous statements (in this example: a removal of the inserted tuple) if this constraint is violated. Eventually, the UPDATE-statement changes the value ‘N’ into ‘Y’ in the appropriate tuple. (Note that this statement is superfluous if the INSERT-statement is not undone.) From the examples above, it appears that the concept of DCM is applicable in a large number of cases. The concept may sometimes result in a sequence of DML-statements that, besides simulating a DDL or DCL-statement, should also take care of maintaining the integrity of the catalog, but this does not fundamentally affect the translatability of DDL and DCL-statements. 4.
EXAMPLES
OF
NEW
STATEMENTS
In the previous section, some examples are provided of DDL and DCL-statements that correspond to sequences of DML-statements. In each case, the DDL or DCL-statement was given, and a number of DML-statements had to be determined with the same function and result. In this section, an inverse line of thought is followed. Suppose that it is possible to add, change and delete data present in the data dictionary (that is, DCM holds). With this functionality, the user is in a position to define new statements that cannot or only very awkwardly be accomplished by DDL-statements from the current version of SQL. Furthermore, it enables the user to define synonyms, focused on the user’s requirements, for existing statements.
Manipulation of a data dictionary with SQL
321
Note that the possibility to define new statements introduces a strong advantage of DCM. It increases the degree of customizability, which is often considered as very valuable, since it can lower the user’s burden regarding repeating statements and allows for meeting diverging user requirements. Some examples are given in the following to illustrate this advantage of DCM. Example
6. Duplicating a table
Sometimes, it is desirable to copy the contents and structure of one table to another table. This is the case, for instance, when several users need the same basic data to fulfill their tasks, but are allowed to alter the data to their own, often diverging needs. A hypothetical DDL-statement for this action is COPY TABLE to TO b, which is used in the box below. Hypothetical
DML of SQL
DDL of SQL
COPY TABLE departments TO dep_001;
INSERT INTO table (tname) VALUES (‘dep_001’); INSERT INTO columns SELECT ‘dep_OOl’, aname, atype, length, scale FROM columns WHERE tname = ‘department’; INSERT INTO dep_001 SELECT * FROM department;
The idea is simple. First a table is created according to the definition of the original table, and next the copy-table is filled with the tuples from the original. The same idea can be used to copy the structure of all tables from one user environment to another, as the following example shows. Example
7. Copying tables to another user environment
Suppose that during the design phase of a systems development project, a number of tables is created by a project member with code PMOOl_DESIGN (this member code is contained in attribute ‘creator’ of table TABLES). The moment the design phase is completed, the tables may need to be transferred to another member, with code PM002_BUILD, participating in the building phase. If a GRANT-statement is considered to be inappropriate (for instance, because the tables are still needed in the design phase), the tables should be copied from one environment (creator) to another (creator). A hypothetical DDL-statement is COPY TABLES FROM a TO b. An example of this statement is given below. ( Hypothetical
DDL of SQL
COPY TABLES FROM pm001 -design TO pm002_build;
DML of SQL INSERT INTO tables (creator, tname) SELECT ‘pm002_build’, tname FROM tables WHERE creator= ‘pm001 -design’; INSERT INTO columns (creator, tname, aname) SELECT ‘pm002_build’, tname, aname FROM columns WHERE creator= ‘pm001 design’;
[Note that in this example-by exception-the combination (creator, tname) is supposed to the primary key of table TABLES, which is often the case in commercially available RDBMSs.)
ROB B. BIJITENDIJKand
328
HARMVAN DER LEK
By defining new statements, one can also introduce a shorthand notation for an existing statement. Suppose that some users often create tables with all attributes defined on one data type (e.g. for testing purposes). For this purpose, a shorthand statement can be defined as is shown in the following example. Example 8. Creating a shorthand notation Hypothetical
DDL of SQL
DML of SQL
NEW TABLE temp_dep WITH ATTRIBUTES nr, name ON CHAR(20);
INSERT INTO tables (tname) VALUES (‘temp_dep’); INSERT INTO columns (tname,aname,atype,length) VALUES (‘temp_dep’, ‘nr’, ‘CHAR’, 20); INSERT INTO columns (tname,aname,atype,length) VALUES (‘temp_dep’, ‘name’, ‘CHAR’, 20);
From the example in this section, it appears that completely new statements can be created by means of the concept of DCM. If it is also possible to define macros, SQL becomes extremely powerful. 5. EXAMPLES
OF
DCM
FOR
SQL2-STATEMENTS
The previous sections mainly focus on statements that define means of attributes and tables, and authorizations on these tables. To complete the definition of tables, it is necessary to define primary and foreign keys. The current version of SQL does not contain any explicit provisions for these types of keys. After lone discussions in literature and working groups, a draft version of SQL2 (SQL’s successor) was formulated in 1987 [7]. Amongst others, this version includes some facilities to define primary keys and foreign keys. The purpose of this section is to show that these extensions of SQL2, which have been long coming, are in fact superfluous if the concept of DCM is applicable. Obviously, the use of primary and foreign keys affects the structure of the catalog. For a correct understanding of the examples to follow, the reader should be aware of the necessary adaptions to the structure. The following (again: sample) tables are added to the catalog structure as presented in Section 2. KEYS (tname,keynr ) This table contains for each table the rotation number of the key of that table. A table may contain more than one key (thus several candidate keys). The numbering of keys is supposed to start at 1 for each table. KEY-COLUMNS
(tname,keynr,aname)
For each candidate key, this table contains: the corresponding table name, its rotation number and the names of the attributes that constitute the candidate key. FOREIGN-KEYS
(from_tname,
to_tname, foreign_keynr)
This table includes for each foreign key: the name of the table that contains the foreign key, the name of the table the foreign key references, and the rotation number of the foreign key.
Manipulation
329
of a data dictionary with SQL
A table may contain more than one foreign key. The numbering of foreign keys is supposed to start at 1 for each table. FOREIGN_KEY_COLUMNS
(from_tname, to_tname, foreign_keynr, from_aname, to_aname)
This table contains for each foreign key: the name of the table that contains the foreign key, the name of the table the foreign key references, the rotation number of the foreign key, and the names of the corresponding attributes that constitute the foreign key. Some foreign key-relationships can be distinguished between the seven tables defined so-far. These relationships are depicted in Fig. 1. In a way, table KEYS is redundant, since it is a projection of table KEY-COLUMNS. It is nonetheless included because: (1) it yields a natural correspondence between, on the other hand, KEYS and TABLES, and, on the other hand, KEY-COLUMNS and COLUMNS; and (2) it allows for (future) inclusion of data that are applicable to a key as a whole without any redundancy. The same remarks apply to the tables FOREIGN-KEYS and FOREIGN_KEY_COLUMNS. Having augmented the catalog structure, some examples can be given regarding the definition of primary keys and foreign keys. Example 9. Defining a table with a key In this example, the table from Example 1 is extended with a key. For reasons of surveyability, the DML-statements from Example 1 are omitted. DDL of SQL2
DML of SQL
CREATE TABLE department (depnr INTEGER, description CHAR(20), budget DECIMAL(7,2)) PRIMARY KEY (depnr);
(DM L-statements Example 2)
from
INSERT INTO keys VALUES (‘department’, 1); INSERT INTO key-columns VALUES (‘department’, 1, ‘depnr’);
The idea is, again, simple: first the table is created according to Example 1, and next the definition of the (candidate) key is added. Note that it is possible to add other candidate keys. It is only a matter of choice which key constitutes the primary key (for example, the candidate key with rotation number 1).
I
An arrow
goes
from
TABAUTH
a foreign
Fig. 1. The foreign key-relationships
key
to
a primary
between catalog tables.
key.
330
ROB
B.
BUITENDIJKand HARM VAN DERLEK
A similar idea can be used to define foreign keys. The following example illustrates this. Example
10. De$ning a table with a foreign key
Suppose the table from Example 9 has been created. Then a foreign key for a table that references table DEPARTMENTS can be defined with DML-statements as follows (again, some DML-statements are omitted). DDL of SQL2 CREATE TABLE employees (empnr INTEGER, name CHAR(20), depnr INTEGER)) PRIMARY KEY (empnr), FOREIGN KEY (depnr) REFERENCES department;
DML of SQL (similar DML-statements in Example 8)
as
INSERT INTO foreign-keys VALUES (‘employees’, ‘departments’, 1); INSERT TO foreign-key-columns VALUES (‘employees’, ‘departments’, 1, ‘depnr’, ‘depnr’);
If a foreign key consists of more than one attribute, several tuples have to be added to table FOREIGNXEYCOLUMNS (one tuple for every attribute of the foreign key). For table FOREIGN-KEY, however, only one tuple is needed for every foreign key. Examples 9 and 10 make plausible that the explicit primary key and foreign key statements of SQL2 can be simulated by sequences of DML-statements. In fact, the simulations make the extensions of SQL2 superfluous. 6.
A CONCEPTUAL
ARGUMENT
FOR THE OF DCM
GENERAL
APPLICABILITY
The previous sections show that the principle of DCM is applicable in a great number of cases, such as the addition of attributes, the definition of primary keys, and the authorization of users. (Of course, DCM hinges on one condition: it should be allowed to use DML-statements to alter directly the contents of the catalog). The generalized version of these cases is as follows: for every DDL and DCL-statement, there exists a sequence of DML-statements with the same effect as the original statement. Obviously, the examples in the previous sections do not proof this statement, but merely make it plausible. Still, it is very easy to see that the hypothesis holds, considering the following train of thought. Suppose that each DDL or DCL-statement constitutes a message from a user to the RDBMS (a message is a sentence in natural language with some semantics to both the user and the system). The different types of DDL and DCL-statements are known (since they are defined by the ANSI standards, see [2,7]) and finite in number. This means that the types of messages are also known and finite in number. According to the natural language principle of NIAM-see [6], each such type of message can be represented by a so-called sentence structure. A sentence structure represents the semantics that are necessary (for the user as well as the system) to understand a message. According to NIAM, again, sentence structures can be transformed into a set of relations with attributes [6]. As a consequence, each message, and thus each DDL and DCL-statements, can be represented by a number of tuples in tables. Furthermore, the transfer of a message from the user to the system can consequently be accomplished by a number of DML-statements, which add, delete or change data in tables. A difficulty seems to be that a DDL or DCL-statement can introduce side effects that induce changes not administrated in the catalog. For example, a CREATE TABLE-statement not only
Manipulation
of a data dictionary with SQL
331
changes the contents of the catalog, but may also entail the reservation of physical memory space on disks. These side effects cannot be simulated with DML-statements. However, this issue can be solved as it is solved by current RDBMSs: the system starts certain actions (like the reservation of physical memory space) as a reaction on the detection of a specific statement (such as the creation of a table). With DCM, the detection should obviously not be based any more on (sets of) DDL and DCL-statements. but on DML-statements. 7. THE
ADVANTAGES
AND
DISADVANTAGES
OF
DCM
To assess the appropriateness of DCM for its incorporation in future vesions of SQL, this section considers its main benefits and limitations. Some of them are already mentioned in previous sections, others are not yet covered. The advantages of DCM are based on the following improvements: l l l
simplification of SQL, simplification and uniformity of the interface between certain software tools and RDBMSs, enlargement of SQL’s functionality.
Each improvement 7.I. Simplijkation
is elaborated below. of SQL
The application of DCM results in an absence of explicit DDL and DCL-statements. This absence simplifies the language SQL, resulting in at least two advantages as compared with the traditional situation concerning SQL. First, software manufacturers need less effort to implement SQL in their RDBMSs, simply because less statements need to be implemented. Secondly, and more importantly, the only standardization that should take place with DCM is on: l
l
the structure and naming of the part of the catalog that may be altered by a user if the concept of DCM is used, the DML-statements.
In both areas, standardization is already taking place. DDL and DCL-statements need not be standardized any more, removing the problems that have been encountered during the standardization of, in particular, this type of statements [8]. If one is able to define a macro for a set of DML-statements, the question may arise whether or not macros should (worldwide) be standardized. It should be noted, however, that the concept of DCM does not force a specific answer to this question. IF DCM is applicable, the standardization of some macros may (for reasons of, e.g. education) or may not (for instance, to maintain the high level of customizability) take place. 7.2. Simplljication and uniformity of the interface The improvement of simplication and uniformity of the interface between certain software tools and RDBMSs is illustrated with two examples of these tools. CASE-tools and data entry-tools. The current state-of-the-art is such that for each CASE-tool X and RDBMS Y, an interface X-Y is needed to create and change the database structure of which the design is registrated in the CASE-tool. The need hinges on the mutually differing DDL and DCL-implementations of the software manufacturers. If, however, DDL and DCL-statements are not necessary (as is the case with DCM), the interface X-Y can be standardized as being a number of flat files that contain the database design. CASE-tool X simply generates these flat files by means of users’ specifications, and RDBMS Y creates the database structure by loading these flat files (with DML-statements) into the catalog. (Of course, a premise of the applicability of the flat files is the presence of a standardized catalog structure.) Not only CASE-tools, but also data entry-tools benefit from the principle of DCM. These tools enable users to create and add tuples to tables. Since current versions of SQL are divided in DDL and DML, data entry-tools also show two separate functions: one function for the definition of tables, and another for the addition of tuples. The functions mutually differ in structure, and
Roe B. BUITENDIJKand HARM VAN DER LEK
332
require, for instance, different screen layouts. If DCM is applicable, this division superfluous, since both data definition and data addition consist of the same action: the of tuples to tables. As a result, for instance, with only one screen layout employees can to a department and attributes can be added to a table (notice the resemblance in structure the two phrases in italic).
becomes addition be added
between
7.3. Enlargement of SQL’s functionality The principle of DCM allows for the simulation of existing statements, and for the creation of new statements. Each such new statement consists of a sequence of DML-statements, and can be represented, if possible, by a macro function. Section 4 contains some examples of new statements. Besides the improvements and advantages entailed by DCM, the concept also introduces two disadvantages: l l
more key punches necessary to enter statements, more effort from the systems side to maintain the integrity of the catalog.
7.4. More key punches In general, the use of DML-statements requires more typing work than the original DDL or DCL-statement. See for instance Examples 1 and 5 for a large sequence of DML-statements. It should be noticed, however, that this problem is not fundamental and unsurmountable. It has already been mentioned more than once that the use of macros yields a satisfactory solution to this problem. 7.5. More efort from the systems side It takes a lot of effort from the system to maintain the integrity of the catalog when DCM is used (this effort often is the reason to reject the implementation of DCM). The following two issues are essential to this effort. First, there should be solid authorization specifications on an attribute level (and not on a table level, as is common in current RDBMSs). Secondly, a DML-statement should always be related to a previous statement, since both may constitute a (subset of a) sequence of statements that simulates only one DDL or DCL-statement. For example, consider the following statement: INSERT INTO table (tname) VALUES (‘department’);
Then it is not apparent from this DML-statement whether the corresponding DDL-statement is CREATE TABLE or, say, COPY TABLE. For a unique identification of the DDL-statement, the previous (and subsequent) statements need to be considered. Again, however, it should be noticed that this increased effort from the systems side should not be a real barrier for the implementation of DCM. Both issues can be solved (albeit with some effort from software manufacturers), since they do not introduce fundamental, insurmountable problems: authorization on an attribute level is a strong current development, and the principles of compiler design may contribute to the solvation of the latter issue. Considering the mentioned benefits and limitations of DCM, it is obvious that the former outweigh the latter. Consequently, it can be considered as beneficial to implement DCM in future versions of SQL. 8. SUMMARY
AND
CONCLUSIONS
This article elaborates on the use of DML to after the contents of the catalog. This concept, referred to as DCM (Data Definition and Data Control by means of Data Manipulation) is often quickly rejected in literature and is not present in current RDBMSs, but, as turns out, induces some strong benefits: a simplification of SQL and interfaces between RDBMSs and software tools (like data entry tools), and an enlargement of SQL’s functionality. The disadvantages refer to an aggravated effort from the users and systems side to make DCM work.
Manipulation of a data dictionary with SQL
333
Since the limitations can be solved and do not introduce fundamental, insurmountable barriers for the implementation of DCM, it is concluded that future SQL-versions can benefit from the incorporation of the concept of DCM. SQL is constantly evolving. Frequently, new versions (SQL2, SQL3, and so on) are proposed. These versions, however, are not based on the concept of DCM. The mentioned advantages plead for a change of direction. REFERENCES [I] H. Van der Lek and R. B. Buitendijk. Is ‘CREATE TABLE’ nodig? (in Dutch). Inforrnatie 31, 774-779 (1989). [2) [3] [4] [S]
ANSI. Da~ubuse &nguage SQL (1987). C. J. Date, An Introduction to Database Systems, Vol. 1, 5th edn. Addison-Wesley, Reading, MA (1990). F. H. Lochovsky and D. C. Tsichritzis. On evaluating interactive query languages. Information Sci. 29,93-J 13 (1983). M. Jarke and Y. Vassiliou. A framework for choosing a database query language. ACM Compur. Sure. 17, 313-340 (1985). [6] G. M. Nijssen and T. A. Halpin, Conceptual Schema and Relational Database Design--A Fact Oriented Approach, Prentice Hall, New York (1989). [7] ANSI. Database Language SQL2 (working draft) (1989). [RI Nederlands Genootschap voor Informatica (NGI). Congres ouer SQL2 (in Dutch) Utrecht (1988).
DIRECT
0306-4379191 $3.00 + 0.00 Copyright ‘0 1991 Pergamon Press plc
MANIPULATION OF A DATA DICTIONARY WITH SQL? ROB B. BUITENDIJK’and HARMVANDERLEK’
‘Netherlands *BSO (Bureau
Railways Ltd, Information Postbox 2025, 3500
and Automation Coordination (IAC), NL, The Netherlands
Moreelsepark
1,
HA, Utrecht
For Systems Development), Department of Management Support, P.O. Box 97780, Jan van Nassaustraat 127, 2509 GD, Den Haag NL, The Netherlands
(Received 27 August 1990; received for publication 21 February 1991)
Abstract--Current versions of SQL do not permit a direct alteration of the contents of the data dictionary by means of data manipulation-statements. This article discusses the advantages and limitations if this prohibition is removed. It appears that the benefits outweigh the limitations to a large extent. The results argue for an implementation of the functionality to change the contents of the data dictionary through data manipulation-statements in future versions of SQL.
1.
INTRODUCTION
Relational database management systems (RDBMSs) usually contain an on-line data dictionary that is structured and can be interrogated in the same manner as the user’s database. Consequently, no fundamental difference exists between the retrieval of meta-data and the retrieval of operational data. This concept, which is supported by SQL [2], induces a strong advantage of RDBMSs over non-RDBMSs: a user needs only one language for both operational data and meta-data (without any restriction induced by predefined access paths). The possibility not only to interrogate but also to change the contents of the data dictionary with SQL in the same way as with operational data, is quickly rejected in literature (see, for instance, [3]). This potentiality, further on referenced by DCM (Data Definition and Data control by means of Data &lanipulation), would lead too easily to a destroyed catalog, disabling a correct functioning of the RDBMS. Nonetheless, DCM introduces some very interesting issues. For example, it yields a higher command conciseness and a higher degree of customizability. These attributes are often regarded as favorable for query languages (see, for example, [4, 51). In this article, the concept of DCM is elaborated. The elaboration progresses step by step, and is meant to justify the hypotheses that, contrary to what is currently argued in literature, it is beneficial to incorporate the concept of DCM in future versions of SQL. The structure of this paper is as follows. Section 2 introduces the table definition of a sample and simplified catalog structure. This definition is used throughout the paper. Some standard DDL and DCL-statements are simulated by DML-statementsf in Section 3, showing the essence of DCM. Section 4 introduces a benefit of the new approach: the simulation of functions that are not present in SQL, thus increasing the degree of customizability. Section 5 shows that the principle of DCM also holds for SQL2. A conceptual argument for the proposition that this principle holds for every DDL and DCL-statement is provided in Section 6. The underpinning is based on the natural language principle NIAM, an information modeling methodology [6]. Section 7 discusses the benefits and limitations of DCM. As it turns out, the
tA
Dutch version of this paper appeared earlier in Ref. [l]. $The standard abbreviations DDL, DCL and DML are used for Data Definition Data Manipulation Language, respectively.
Language,
Data Control
Language
and
ROB B. BUITENDIJKand HARM VAN DERLEK
324
benefits outweigh conclusions.
the limitations.
Finally,
2. A SAMPLE
Section 8 is reserved for a summary
CATALOG
and some
STRUCTURE
A DDL or DCL-statement directly interacts with tables in the catalog of the RDBMS. A user of DDL or DCL does not have to be aware of this interaction, and thus neither of the structure of the catalog. This transparency becomes inappropriate, however, if the DML is used to interact with the catalog: in that case, the structure of the catalog (i.c. names of tables and attributes) should be known to the user. In this section, the definition of a catalog structure is presented that is used throughout this article. The presented structure is hypothetical, but shows strong resemblances with structures of catalogs from RDBMSs that are currently commercially available. Its definition is as follows. TABLES (tname, creator, remarks) This table contains for every table: the name of the table, an identification of the person who created the table, and, if appropriate, some remarks about the table. [Note: it is common practice to include attribute ‘creator’ in the primary key. For reasons of simplicity, the attribute is excluded (unless explicitly mentioned otherwise). The exclusion does not violate the results of the following discussions]. COLUMNS
(tnume, uname, atype, length, scale)
This table contains for every attribute: the name of the table in which the attribute participates, the name of the attribute, the data type and length and scale data referring to the data type. TABAUTH (grantor, grantee, tname, selectauth) This table indicates whether a user (‘grantee’) is permitted by the owner (‘grantor’) of a table (‘tname’) to interrogate (‘selectauth’) is ‘Y’ or ‘N’) that table. This structure obviously is a simplification. In RDBMSs as DB2 and Oracle, similar tables include additional attributes. These attributes often contain some systems data (for instance, statistical data, like the number of tuples in a table) that influence the internal working of the system. The values of these attributes are determined by the system itself, and can and may not be changed by users (although the users may be authorized to consult these attributes). For reasons of surveyability, these systems attributes are ignored. (So, in fact, the presented tables are derived views.) In addition to this type of attributes, the catalog of an RDBMS may contain tables and attributes that contain data about primary keys and foreign keys. These tables and attributes are for the moment disregarded, since the examples of SQL’s DDL in the following section do not use any data on primary keys and foreign keys. In Section 5, the catalog structure is extended with these tables and attributes to be able to present some examples of SQL2’s DDL.
3. EXAMPLES
OF DCM FOR
SQL-STATEMENTS
In this section, some example of DDL and DCL-statements are given that are in fact superfluous if the user is authorized to alter the contents of the catalog with DML-statements. Each example is placed in a box, containing a DDL or DCL-statement on the left-hand side, and one or more equivalent DML-statements on the right-hand side. (The DDL and DCL-statements are supposed to be semantically correct.) It is not the intention to provide a complete overview of all possible DDL and DCL-statements, but to show and to make plausible the principles of DCM.
325
Manipulation of a data dictionary with SQL
Example 1. Creating a table DML of SQL
DDL of SQL CREATE TABLE department (depnr INTEGER, description CHAR(20), budget DECIMAl(7,2));
INSERT INTO tables (tname) VALUES (‘department’); INSERT INTO columns (tname,aname,atype) VALUES (~depa~ment’, ‘depnr’, ‘INTEGER’); INSERT INTO columns (tname,aname,atype,length) VALUES (‘department’, ‘description’, ‘CHAR’, 20); INSERT INTO columns (tname,aname,atype, length,scale) VALUES (‘department‘, ‘budget’, ‘DECIMAL’, 7,2);
It should be admitted: the left-hand side looks more attractive than the right-hand side. It is not the intention of this article, however, to suggest that in the future all tables should be created in the way as indicated in the right-hand side. Nonetheless, an apparent conclusion from this example is that the DDL-statement is nothing more than a macro (shorthand) that can generate the sequence of DML-statements. Example 2. Adding an attribute to a table DML of SQL
DDL of SQL ALTER TABLE department ADD (manager CHAR(5));
INSERT INTO columns (tname,aname,atype,length) VALUES (‘department’, ‘manager’, ‘CHAR’, 5);
In this example, the DDL-statement corresponds to exactly one DML-statement. Often, however, more than one DML-statement is necessary to simulate a DDL (or DCL)-statement. The following example shows that an UPDATE-statement can be used to add a comment to a table. Example 3. Adding comment to a table 1 DML of SQL
DDL of SQL COMMENT ON TABLE IS ‘Important table’;
department
It comes as no surprise that a DROP-statement statements, as is shown in the next example.
UPDATE tables SET remarks = ‘Important table’ WHERE tname=‘department’;
corresponds
with a sequence of DELETE-
326
ROB B. BUITENDIJKand HARMVANDER LEK
Example 4. Removing a table DDL of SQL DROP TABLE
DML of SQL
department;
DELETE FROM tables WHERE tname = ‘department’; (DELETE FROM columns WHERE tname = ‘department’;)
The second DELETE-statement is placed in parentheses, because this statement is unnecessary in case the referential integrity regarding the tables TABLES and COLUMNS is taken care of by the RDBMS itself. Also DCL-statements can be simulated by DML-statements. Consider the following example. Example 5. Authorizing a user to interrogate a table DML of SQL
DCL of SQL
SET AUTOCOMMIT
GRANT SELECT ON department TO Buitendijk;
INSERT INTO tabauth (USER, ‘Buitendijk’, ‘department’, ‘Y’); COMMIT
OFF; VALUES
WORK;
UPDATE tabauth SET selectauth = ‘Y’ WHERE grantor = USER AND grantee = ‘Buitendijk’ AND tname = ‘department’; SET AUTOCOMMIT
ON;
(USER is a systems variable that contains the identification of the user that uses SQL.) The example is more complex than the previous ones. The complexity is caused by the fact that table TABAUTH already may contain a tuple (USER, Buitendijk, department, ‘N’), indicating that user Buitendijk is not allowed to read table DEPARTMENT. If this is the case, the INSERT-statement results in duplicate primary key values, which should be rejected. The solution to the problem is found in the use of the COMMIT-concept. The SET AUTOCOMMIT OFF-statement postpones the detection of duplicate primary key values until a COMMIT WORK is given. This statement checks (amongst others) the uniqueness of primary key values and will undo all previous statements (in this example: a removal of the inserted tuple) if this constraint is violated. Eventually, the UPDATE-statement changes the value ‘N’ into ‘Y’ in the appropriate tuple. (Note that this statement is superfluous if the INSERT-statement is not undone.) From the examples above, it appears that the concept of DCM is applicable in a large number of cases. The concept may sometimes result in a sequence of DML-statements that, besides simulating a DDL or DCL-statement, should also take care of maintaining the integrity of the catalog, but this does not fundamentally affect the translatability of DDL and DCL-statements. 4.
EXAMPLES
OF
NEW
STATEMENTS
In the previous section, some examples are provided of DDL and DCL-statements that correspond to sequences of DML-statements. In each case, the DDL or DCL-statement was given, and a number of DML-statements had to be determined with the same function and result. In this section, an inverse line of thought is followed. Suppose that it is possible to add, change and delete data present in the data dictionary (that is, DCM holds). With this functionality, the user is in a position to define new statements that cannot or only very awkwardly be accomplished by DDL-statements from the current version of SQL. Furthermore, it enables the user to define synonyms, focused on the user’s requirements, for existing statements.
Manipulation of a data dictionary with SQL
321
Note that the possibility to define new statements introduces a strong advantage of DCM. It increases the degree of customizability, which is often considered as very valuable, since it can lower the user’s burden regarding repeating statements and allows for meeting diverging user requirements. Some examples are given in the following to illustrate this advantage of DCM. Example
6. Duplicating a table
Sometimes, it is desirable to copy the contents and structure of one table to another table. This is the case, for instance, when several users need the same basic data to fulfill their tasks, but are allowed to alter the data to their own, often diverging needs. A hypothetical DDL-statement for this action is COPY TABLE to TO b, which is used in the box below. Hypothetical
DML of SQL
DDL of SQL
COPY TABLE departments TO dep_001;
INSERT INTO table (tname) VALUES (‘dep_001’); INSERT INTO columns SELECT ‘dep_OOl’, aname, atype, length, scale FROM columns WHERE tname = ‘department’; INSERT INTO dep_001 SELECT * FROM department;
The idea is simple. First a table is created according to the definition of the original table, and next the copy-table is filled with the tuples from the original. The same idea can be used to copy the structure of all tables from one user environment to another, as the following example shows. Example
7. Copying tables to another user environment
Suppose that during the design phase of a systems development project, a number of tables is created by a project member with code PMOOl_DESIGN (this member code is contained in attribute ‘creator’ of table TABLES). The moment the design phase is completed, the tables may need to be transferred to another member, with code PM002_BUILD, participating in the building phase. If a GRANT-statement is considered to be inappropriate (for instance, because the tables are still needed in the design phase), the tables should be copied from one environment (creator) to another (creator). A hypothetical DDL-statement is COPY TABLES FROM a TO b. An example of this statement is given below. ( Hypothetical
DDL of SQL
COPY TABLES FROM pm001 -design TO pm002_build;
DML of SQL INSERT INTO tables (creator, tname) SELECT ‘pm002_build’, tname FROM tables WHERE creator= ‘pm001 -design’; INSERT INTO columns (creator, tname, aname) SELECT ‘pm002_build’, tname, aname FROM columns WHERE creator= ‘pm001 design’;
[Note that in this example-by exception-the combination (creator, tname) is supposed to the primary key of table TABLES, which is often the case in commercially available RDBMSs.)
ROB B. BIJITENDIJKand
328
HARMVAN DER LEK
By defining new statements, one can also introduce a shorthand notation for an existing statement. Suppose that some users often create tables with all attributes defined on one data type (e.g. for testing purposes). For this purpose, a shorthand statement can be defined as is shown in the following example. Example 8. Creating a shorthand notation Hypothetical
DDL of SQL
DML of SQL
NEW TABLE temp_dep WITH ATTRIBUTES nr, name ON CHAR(20);
INSERT INTO tables (tname) VALUES (‘temp_dep’); INSERT INTO columns (tname,aname,atype,length) VALUES (‘temp_dep’, ‘nr’, ‘CHAR’, 20); INSERT INTO columns (tname,aname,atype,length) VALUES (‘temp_dep’, ‘name’, ‘CHAR’, 20);
From the example in this section, it appears that completely new statements can be created by means of the concept of DCM. If it is also possible to define macros, SQL becomes extremely powerful. 5. EXAMPLES
OF
DCM
FOR
SQL2-STATEMENTS
The previous sections mainly focus on statements that define means of attributes and tables, and authorizations on these tables. To complete the definition of tables, it is necessary to define primary and foreign keys. The current version of SQL does not contain any explicit provisions for these types of keys. After lone discussions in literature and working groups, a draft version of SQL2 (SQL’s successor) was formulated in 1987 [7]. Amongst others, this version includes some facilities to define primary keys and foreign keys. The purpose of this section is to show that these extensions of SQL2, which have been long coming, are in fact superfluous if the concept of DCM is applicable. Obviously, the use of primary and foreign keys affects the structure of the catalog. For a correct understanding of the examples to follow, the reader should be aware of the necessary adaptions to the structure. The following (again: sample) tables are added to the catalog structure as presented in Section 2. KEYS (tname,keynr ) This table contains for each table the rotation number of the key of that table. A table may contain more than one key (thus several candidate keys). The numbering of keys is supposed to start at 1 for each table. KEY-COLUMNS
(tname,keynr,aname)
For each candidate key, this table contains: the corresponding table name, its rotation number and the names of the attributes that constitute the candidate key. FOREIGN-KEYS
(from_tname,
to_tname, foreign_keynr)
This table includes for each foreign key: the name of the table that contains the foreign key, the name of the table the foreign key references, and the rotation number of the foreign key.
Manipulation
329
of a data dictionary with SQL
A table may contain more than one foreign key. The numbering of foreign keys is supposed to start at 1 for each table. FOREIGN_KEY_COLUMNS
(from_tname, to_tname, foreign_keynr, from_aname, to_aname)
This table contains for each foreign key: the name of the table that contains the foreign key, the name of the table the foreign key references, the rotation number of the foreign key, and the names of the corresponding attributes that constitute the foreign key. Some foreign key-relationships can be distinguished between the seven tables defined so-far. These relationships are depicted in Fig. 1. In a way, table KEYS is redundant, since it is a projection of table KEY-COLUMNS. It is nonetheless included because: (1) it yields a natural correspondence between, on the other hand, KEYS and TABLES, and, on the other hand, KEY-COLUMNS and COLUMNS; and (2) it allows for (future) inclusion of data that are applicable to a key as a whole without any redundancy. The same remarks apply to the tables FOREIGN-KEYS and FOREIGN_KEY_COLUMNS. Having augmented the catalog structure, some examples can be given regarding the definition of primary keys and foreign keys. Example 9. Defining a table with a key In this example, the table from Example 1 is extended with a key. For reasons of surveyability, the DML-statements from Example 1 are omitted. DDL of SQL2
DML of SQL
CREATE TABLE department (depnr INTEGER, description CHAR(20), budget DECIMAL(7,2)) PRIMARY KEY (depnr);
(DM L-statements Example 2)
from
INSERT INTO keys VALUES (‘department’, 1); INSERT INTO key-columns VALUES (‘department’, 1, ‘depnr’);
The idea is, again, simple: first the table is created according to Example 1, and next the definition of the (candidate) key is added. Note that it is possible to add other candidate keys. It is only a matter of choice which key constitutes the primary key (for example, the candidate key with rotation number 1).
I
An arrow
goes
from
TABAUTH
a foreign
Fig. 1. The foreign key-relationships
key
to
a primary
between catalog tables.
key.
330
ROB
B.
BUITENDIJKand HARM VAN DERLEK
A similar idea can be used to define foreign keys. The following example illustrates this. Example
10. De$ning a table with a foreign key
Suppose the table from Example 9 has been created. Then a foreign key for a table that references table DEPARTMENTS can be defined with DML-statements as follows (again, some DML-statements are omitted). DDL of SQL2 CREATE TABLE employees (empnr INTEGER, name CHAR(20), depnr INTEGER)) PRIMARY KEY (empnr), FOREIGN KEY (depnr) REFERENCES department;
DML of SQL (similar DML-statements in Example 8)
as
INSERT INTO foreign-keys VALUES (‘employees’, ‘departments’, 1); INSERT TO foreign-key-columns VALUES (‘employees’, ‘departments’, 1, ‘depnr’, ‘depnr’);
If a foreign key consists of more than one attribute, several tuples have to be added to table FOREIGNXEYCOLUMNS (one tuple for every attribute of the foreign key). For table FOREIGN-KEY, however, only one tuple is needed for every foreign key. Examples 9 and 10 make plausible that the explicit primary key and foreign key statements of SQL2 can be simulated by sequences of DML-statements. In fact, the simulations make the extensions of SQL2 superfluous. 6.
A CONCEPTUAL
ARGUMENT
FOR THE OF DCM
GENERAL
APPLICABILITY
The previous sections show that the principle of DCM is applicable in a great number of cases, such as the addition of attributes, the definition of primary keys, and the authorization of users. (Of course, DCM hinges on one condition: it should be allowed to use DML-statements to alter directly the contents of the catalog). The generalized version of these cases is as follows: for every DDL and DCL-statement, there exists a sequence of DML-statements with the same effect as the original statement. Obviously, the examples in the previous sections do not proof this statement, but merely make it plausible. Still, it is very easy to see that the hypothesis holds, considering the following train of thought. Suppose that each DDL or DCL-statement constitutes a message from a user to the RDBMS (a message is a sentence in natural language with some semantics to both the user and the system). The different types of DDL and DCL-statements are known (since they are defined by the ANSI standards, see [2,7]) and finite in number. This means that the types of messages are also known and finite in number. According to the natural language principle of NIAM-see [6], each such type of message can be represented by a so-called sentence structure. A sentence structure represents the semantics that are necessary (for the user as well as the system) to understand a message. According to NIAM, again, sentence structures can be transformed into a set of relations with attributes [6]. As a consequence, each message, and thus each DDL and DCL-statements, can be represented by a number of tuples in tables. Furthermore, the transfer of a message from the user to the system can consequently be accomplished by a number of DML-statements, which add, delete or change data in tables. A difficulty seems to be that a DDL or DCL-statement can introduce side effects that induce changes not administrated in the catalog. For example, a CREATE TABLE-statement not only
Manipulation
of a data dictionary with SQL
331
changes the contents of the catalog, but may also entail the reservation of physical memory space on disks. These side effects cannot be simulated with DML-statements. However, this issue can be solved as it is solved by current RDBMSs: the system starts certain actions (like the reservation of physical memory space) as a reaction on the detection of a specific statement (such as the creation of a table). With DCM, the detection should obviously not be based any more on (sets of) DDL and DCL-statements. but on DML-statements. 7. THE
ADVANTAGES
AND
DISADVANTAGES
OF
DCM
To assess the appropriateness of DCM for its incorporation in future vesions of SQL, this section considers its main benefits and limitations. Some of them are already mentioned in previous sections, others are not yet covered. The advantages of DCM are based on the following improvements: l l l
simplification of SQL, simplification and uniformity of the interface between certain software tools and RDBMSs, enlargement of SQL’s functionality.
Each improvement 7.I. Simplijkation
is elaborated below. of SQL
The application of DCM results in an absence of explicit DDL and DCL-statements. This absence simplifies the language SQL, resulting in at least two advantages as compared with the traditional situation concerning SQL. First, software manufacturers need less effort to implement SQL in their RDBMSs, simply because less statements need to be implemented. Secondly, and more importantly, the only standardization that should take place with DCM is on: l
l
the structure and naming of the part of the catalog that may be altered by a user if the concept of DCM is used, the DML-statements.
In both areas, standardization is already taking place. DDL and DCL-statements need not be standardized any more, removing the problems that have been encountered during the standardization of, in particular, this type of statements [8]. If one is able to define a macro for a set of DML-statements, the question may arise whether or not macros should (worldwide) be standardized. It should be noted, however, that the concept of DCM does not force a specific answer to this question. IF DCM is applicable, the standardization of some macros may (for reasons of, e.g. education) or may not (for instance, to maintain the high level of customizability) take place. 7.2. Simplljication and uniformity of the interface The improvement of simplication and uniformity of the interface between certain software tools and RDBMSs is illustrated with two examples of these tools. CASE-tools and data entry-tools. The current state-of-the-art is such that for each CASE-tool X and RDBMS Y, an interface X-Y is needed to create and change the database structure of which the design is registrated in the CASE-tool. The need hinges on the mutually differing DDL and DCL-implementations of the software manufacturers. If, however, DDL and DCL-statements are not necessary (as is the case with DCM), the interface X-Y can be standardized as being a number of flat files that contain the database design. CASE-tool X simply generates these flat files by means of users’ specifications, and RDBMS Y creates the database structure by loading these flat files (with DML-statements) into the catalog. (Of course, a premise of the applicability of the flat files is the presence of a standardized catalog structure.) Not only CASE-tools, but also data entry-tools benefit from the principle of DCM. These tools enable users to create and add tuples to tables. Since current versions of SQL are divided in DDL and DML, data entry-tools also show two separate functions: one function for the definition of tables, and another for the addition of tuples. The functions mutually differ in structure, and
Roe B. BUITENDIJKand HARM VAN DER LEK
332
require, for instance, different screen layouts. If DCM is applicable, this division superfluous, since both data definition and data addition consist of the same action: the of tuples to tables. As a result, for instance, with only one screen layout employees can to a department and attributes can be added to a table (notice the resemblance in structure the two phrases in italic).
becomes addition be added
between
7.3. Enlargement of SQL’s functionality The principle of DCM allows for the simulation of existing statements, and for the creation of new statements. Each such new statement consists of a sequence of DML-statements, and can be represented, if possible, by a macro function. Section 4 contains some examples of new statements. Besides the improvements and advantages entailed by DCM, the concept also introduces two disadvantages: l l
more key punches necessary to enter statements, more effort from the systems side to maintain the integrity of the catalog.
7.4. More key punches In general, the use of DML-statements requires more typing work than the original DDL or DCL-statement. See for instance Examples 1 and 5 for a large sequence of DML-statements. It should be noticed, however, that this problem is not fundamental and unsurmountable. It has already been mentioned more than once that the use of macros yields a satisfactory solution to this problem. 7.5. More efort from the systems side It takes a lot of effort from the system to maintain the integrity of the catalog when DCM is used (this effort often is the reason to reject the implementation of DCM). The following two issues are essential to this effort. First, there should be solid authorization specifications on an attribute level (and not on a table level, as is common in current RDBMSs). Secondly, a DML-statement should always be related to a previous statement, since both may constitute a (subset of a) sequence of statements that simulates only one DDL or DCL-statement. For example, consider the following statement: INSERT INTO table (tname) VALUES (‘department’);
Then it is not apparent from this DML-statement whether the corresponding DDL-statement is CREATE TABLE or, say, COPY TABLE. For a unique identification of the DDL-statement, the previous (and subsequent) statements need to be considered. Again, however, it should be noticed that this increased effort from the systems side should not be a real barrier for the implementation of DCM. Both issues can be solved (albeit with some effort from software manufacturers), since they do not introduce fundamental, insurmountable problems: authorization on an attribute level is a strong current development, and the principles of compiler design may contribute to the solvation of the latter issue. Considering the mentioned benefits and limitations of DCM, it is obvious that the former outweigh the latter. Consequently, it can be considered as beneficial to implement DCM in future versions of SQL. 8. SUMMARY
AND
CONCLUSIONS
This article elaborates on the use of DML to after the contents of the catalog. This concept, referred to as DCM (Data Definition and Data Control by means of Data Manipulation) is often quickly rejected in literature and is not present in current RDBMSs, but, as turns out, induces some strong benefits: a simplification of SQL and interfaces between RDBMSs and software tools (like data entry tools), and an enlargement of SQL’s functionality. The disadvantages refer to an aggravated effort from the users and systems side to make DCM work.
Manipulation of a data dictionary with SQL
333
Since the limitations can be solved and do not introduce fundamental, insurmountable barriers for the implementation of DCM, it is concluded that future SQL-versions can benefit from the incorporation of the concept of DCM. SQL is constantly evolving. Frequently, new versions (SQL2, SQL3, and so on) are proposed. These versions, however, are not based on the concept of DCM. The mentioned advantages plead for a change of direction. REFERENCES [I] H. Van der Lek and R. B. Buitendijk. Is ‘CREATE TABLE’ nodig? (in Dutch). Inforrnatie 31, 774-779 (1989). [2) [3] [4] [S]
ANSI. Da~ubuse &nguage SQL (1987). C. J. Date, An Introduction to Database Systems, Vol. 1, 5th edn. Addison-Wesley, Reading, MA (1990). F. H. Lochovsky and D. C. Tsichritzis. On evaluating interactive query languages. Information Sci. 29,93-J 13 (1983). M. Jarke and Y. Vassiliou. A framework for choosing a database query language. ACM Compur. Sure. 17, 313-340 (1985). [6] G. M. Nijssen and T. A. Halpin, Conceptual Schema and Relational Database Design--A Fact Oriented Approach, Prentice Hall, New York (1989). [7] ANSI. Database Language SQL2 (working draft) (1989). [RI Nederlands Genootschap voor Informatica (NGI). Congres ouer SQL2 (in Dutch) Utrecht (1988).