|
|
|
Up and Running with ALLBASE/SQL: HP 3000 and HP 9000 Computer Systems > Chapter 8 Glossary of Terms in ALLBASE/SQL
|
|
Type of query that is issued for the needs of a particular moment. It is usually not stored for later use or built into an application. Ad hoc queries are important in the use of relational databases for decision support. Logging method that uses log files to roll back incomplete transactions after a system failure and to roll forward from an earlier DBEnvironment backup. Uses a relatively large file or set of files to record all activity that modifies databases from the point at which you do a backup of the entire DBEvironment. If the logs are intact following a hard crash, you can recover a DBEnvironment from an earlier saved version. A characteristic of a data element considered during database design. As you organize your data, you arrange it into categories that possess similar attributes. The categories are known as entities. Permission to access specific objects for specific purposes within an ALLBASE/SQL DBEnvironment. Three major types are SPECIAL authority, TABLE authority, and RUN authority. An integrity constraint that enforces a condition that must not be false for the columns of a table. Any value you attempt to insert into a column that has a check constraint defined on it must either satisfy the condition or be NULL. Special category of ALLBASE/SQL owner that is neither a particular DBEUserID nor a group. You do not explicitly create a class; you create it implicitly by creating objects owned by it. A class does not have members like a group. Objects owned by classes can be dropped or modified only by a DBA. A class does not have a password associated with it. An index which attempts to locate new rows in physical proximity to other rows with similar key values. Valuable when a large number of inserts follows a similar large number of delete operations. The ability of multiple users to access the same database files simultaneously. Concurrency is regulated by locking, which controls the degree of concurrent access permitted--from exclusive read or write access to shared read with concurrent updates. A condition placed upon a column or table that requires values in the column or table to meet certain conditions before a row can be inserted or deleted. Two types supported by ALLBASE/SQL are unique constraints and referential constraints. An isolation level that guarantees that any data on the page you are currently accessing cannot be updated by other users until you move off that page. This offers a greater degree of concurrency than Repeatable Read, which is the default isolation level. Study of raw data before building a database. Concerns the kind of data that is to be stored and how the data is to be used. A structured arrangement of data elements designed for the easy selection of information. In ALLBASE/SQL, a database is a collection of tables, views, and indexes having the same ownership in a DBEnvironment. A DBEnvironment may contain several databases. The individual with DBA authority who creates and maintains objects in a DBEnvironment. DBA authority permits the use of certain restricted SQL and SQLUtil commands or options, and also confers co-ownership of all the objects in a DBEnvironment. The creation of a specific arrangement of data in tables or data sets with an appropriate security structure. The set of SQL commands that control access to data. This includes the ADD, REMOVE, GRANT, and REVOKE commands, as well as the commands to create, manage, and drop authorization groups. Also known as DCL. The set of SQL commands that create and drop database objects. This includes the commands to create and remove DBEFileSets, DBEFiles, tables, views, and indexes. Also known as DDL. The set of SQL commands that access data. This includes the actions of selecting data, inserting rows, updating columns, and deleting rows. Also known as DML. A kind of data that can be stored in database tables. Valid types are CHARACTER, VARCHAR, INTEGER, DECIMAL, FLOAT, DATE, TIME, DATETIME, INTERVAL, BINARY, and VARBINARY. LONG varieties of BINARY and VARBINARY are also available. The most powerful authority within an ALLBASE/SQL DBEnvironment. Includes the authority to create new objects, drop all existing objects, and grant or revoke all authorities for other users. DBA authority implies co-ownership of all objects within the DBEnvironment. A central component of ALLBASE/SQL that performs physical file access and logging. DBCore also provides concurrency control through the use of isolation levels and locking. DBEnvironment Configuration File. This contains startup parameters for the DBEnvironment. The contents of this file are initially determined at the time you issue the START DBE NEW command. You can modify some of these parameters using SQLUtil, and you can override some of them with the START DBE command. The individual who issues the START DBE NEW command. Some maintenance operations require you to be the DBECreator. File containing data or indexes or both. A DBEFile of type TABLE can only contain table data; a DBEFile of type INDEX can only contain index data; a DBEFile of type MIXED can contain both table and index data. DBEFiles are operating system files and are named according to the conventions of the operating system. Logical grouping of DBEFiles. You associate newly created DBEFiles with a DBEFileSet, and you specify a DBEFileSet when you create a table. A collection of files containing one or more databases. Files include the DBECon file (which holds startup parameters and log file names); DBEFile0, which contains the system catalog; and log files. A DBenvironment may also contain additional DBEFiles for table and index data. The DBEnvironment is the maximum scope of a transaction within ALLBASE/SQL. In HP-UX, a login name. In MPE/iX, a login name and account name joined with the character '@'. One type of owner of database objects. An application program incorporating SQL statements for programmatic access to ALLBASE/SQL databases. Each embedded SQL statement begins with the keywords EXEC SQL. Embedded SQL programs are preprocessed, then compiled before execution. For most SQL commands, the preprocessor stores a section, or runtime version of the command, in the DBEnvironment. Basic subdivision of data elements in database design. Each entity is a thing or event about which information is kept in the database. For each entity, there is at least one attribute that uniquely identifies a data element as belonging to the entity. specifies a value. The most common sources of values are columns in a table or host variables in an application program. Expressions are used to identify columns or rows or to define new values for columns. A column or columns in a table which have a relationship to a primary column or columns in a different table such that the value must exist in the primary key column before it can be inserted into the foreign key column, and it must be deleted from all foreign key columns before it can be deleted from the primary key column. Authorization group. Membership in a group is used to confer common ownership or common authorization for other objects in the DBE. You create a group explicitly, using the CREATE GROUP command, then you add users to it. You can then grant authorizations to the group or revoke authorizations from the group. You can also use the group name for the ownership of database objects. ALLBASE/SQL table containing rows that are stored in such a way as to permit fast access to specific tuples by means of a hash function. A hash structure provides a method for quickly finding a row by calculating its location based on the value of the hash key, which you specify when you create the table. A variable in an application program that receives data from an ALLBASE/SQL database (output host variable) or passes data to the database from the program (input host variable). Locking of tables in transactions according to table type and isolation level. For example, PRIVATE tables are locked exclusively for all access; PUBLIC tables are locked exclusively only for write operations. A data structure that potentially speeds access to table data through the use of an index scan. The four types of index are: unique, clustering, unique and clustering, and non-unique and non-clustering. An index is created for one or more key columns in the table. A methoe of looking up each row in an index to find its location in the data file, then accessing the row in the table. This kind of access requires the existence of a B-tree index, which you must create. You do not explicitly request an index scan. Instead, SQLCore makes this choice if the query optimizer decides that the use of an index is the best way to access the data. A constraint placed on the columns of a table to ensure that a database contains only valid data. Two types are the referential constraint and the unique constraint. The interactive interface to ALLBASE/SQL. ISQL is the tool you use for ad hoc queries as well as for loading and unloading data and other database administration tasks. The degree of separation enforced between the transactions of different users. There are four levels: Repeatable Read (RR), Cursor Stability (CS), Read Committed (RC), and Read Uncommitted (RU). You specify an isolation level in the BEGIN WORK command. A query that accesses data from two or more ALLBASE/SQL tables at a time. A join column is a column that occurs in both tables of a join (often it is a key column) and contains similar values in both tables. A column which is indexed, or a column which participates in integrity constraints as all or part of a PRIMARY or FOREIGN key. The value contained in the columns of a key. Key values are stored in index pages along with pointers to the location of rows in data pages. A technique for concurrency control through which ALLBASE/SQL restricts access to data by one individual when the data is being used by another. Locks are of three types: shared, exclusive, or shared with intent to become exclusive. Lock type is determined by the type of table being accessed and by the kind of operation the user is performing. Locks are released when a transaction ends with a COMMIT WORK command. The use of log files to record operations that modify database files. Logging is of two kinds: nonarchive logging, and archive logging. Both kinds permit you to roll back incomplete transactions following a system failure. This maintains data integrity by backing out changes to the database that were not committed. Only archive logging allows you to roll forward from an earlier version of a DBEnvironment by reapplying all committed transactions up to a specific recovery time. A file containing ALLBASE/SQL error and warning messages. When a message is displayed, its text comes from this file. You can look up the meaning of the message in the ALLBASE/SQL Message Manual. An error and warning file known as SQLMSG generated by a preprocessor session. It contains any errors generated during the preprocessing of an embedded SQL program. The file that results from using the preprocessor on an embedded SQL source file. The modified source file can then be compiled into an executable program. A group of sections stored in the DBEnvironment when an embedded SQL program is preprocessed or when you use the PREPARE command in ISQL. The sections are activated when the program is run or when the EXECUTE command is issued in ISQL. The language of the DBEnvironment or of specific CHAR and VARCHAR columns in a table. You specify the DBEnvironment's language in the START DBE NEW command using the LANG= clause. In table creation, you can use the LANG= clause as part of a character column description. The default language is known in HP-UX as n-computer; in MPE/iX, it is NATIVE-3000. The default logging method. Uses log files to roll back (that is, undo) incomplete transactions that were not committed at the time of a system failure. A formal process of adjusting table design in relational databases by examining and adjusting the relationships among columns. A structure created and stored in an ALLBASE/SQL DBEnvironment. The most common objects are tables, views, indexes, and groups. Component of SQLCore which chooses the access path in processing a query. In optimization, ALLBASE/SQL chooses whether to use serial access to the data, or whether to use an index or hash structure if they exist. If there is a choice among indexes, the optimizer calculates the best access path. A DBEUserID, a group name, or a class name. Ownership applies to database objects such as tables, views, indexes, and authorization groups. The owner may drop the object or transfer it to some other owner. Part of query syntax that specifies a subset of rows to be returned in the query result. Predicates are introduced by the keyword WHERE, so they are sometimes called WHERE clauses. Predicates let you specify a range of values. The comparison predicate lets you compare a column value with a constant or host variable; the LIKE predicate lets you compare a column value with a portion of a character string; the BETWEEN predicate specifies a range of values for a comparison. Special predicates of various kinds let you search for rows in more complex ways, including the use of subqueries. A component of ALLBASE/SQL that converts an embedded SQL program into a modified source file for input to a compiler in one of several programming languages: C, COBOL, FORTRAN, and Pascal. A column in a table defined so as to permit reference by foreign keys in other tables. A primary key also enforces uniqueness within the column. A sequence of SQL statements that are stored in a DBEnvironment and applied as a group either through rules or through execution by specific users. Together with rules, procedures let you define generalized constraints within a database to implement the relationships in the database design. A set of operators, expressions, and commands that let you manipulate a database. The query language of ALLBASE/SQL is SQL. An isolation level that guarantees only that data you read in a transaction has been committed by some earlier transaction; that is, it is not currently in the process of update by some other transaction at the time you are reading it. In practical terms, this means that another transaction can update or delete the same row before your transaction is over. However, concurrency is greatly improved. An isolation level that enforces no separation between your transaction and those of others, because no locks are obtained for reads. This level permits dirty reads, that is, reading data from the data buffers that has not and may never be written to the database at all. An integrity constraint that enforces a relationship between the rows of two tables. Any value you attempt to insert into a table that has a referential constraint must either be NULL or be the same as a value in the referenced table. Ways of extracting data from relational tables. The three primary relational operations are selection, projection, and joining. The meaningful interaction of entities in database design. Relationships may be one-to-one, one-to-many, or many-to-many. An isolation level that enforces the highest level of separation between the transactions of different users. This level guarantees that when you re-read any data you have read previously in the same transaction, the value seen in the second read will be the same as the value seen in the first read. In practical terms, this means that other users may not update any data you have read at this isolation level until you COMMIT WORK. A process by which ALLBASE/SQL backs out of incomplete transactions using a log file. If a DBEnvironment stops while some transactions are still in progress, they must be undone the next time the DBEnvironment starts up. A process by which ALLBASE/SQL reapplies transactions to a DBEnvironment from a log file. Rollforward recovery requires the use of archive logging. A database object that ties the execution of a procedure to specific kinds of data manipulation performed on a database table. Together with procedures, rules let you define generalized constraints within a database to implement the relationships in the database design. Permission to execute stored sections that perform ALLBASE/SQL queries or other operations from an application program. Required in addition to permission at the operating system level to execute the application itself. A complete SQL database definition as coded in a CREATE SCHEMA statement. Also, an ISQL command file containing commands to create a DBEnvironment and the objects within it, such as DBEFileSets, DBEFiles, tables, views, indexes, and authorities. You can create a schema file with an editor, or you can generate one from an existing DBEnvironment by using SQLGEN. Also, a TurboIMAGE database definition which is the input to the TurboIMAGE DBSCHEMA program. An SQL command stored in the DBEnvironment for use at run time by an application program. When sections are valid, they can be executed immediately by ALLBASE/SQL. When they are invalid, they must be revalidated at run time before execution. A method of reading sequentially from the start of a table until the row is found. Also called table or relation scan. This is the default scan method used to access rows in a table when indexes do not exist. If indexes do exist on a table, the optimizer chooses whether to perform an index scan or a serial scan. Permission to use the DBEnvironment in particular ways. CONNECT authority lets you establish a user session. RESOURCE authority lets you create and drop objects such as tables, views, DBEFiles, etc. DBA authority gives you permission to perform all SQL and SQLUtil commands, and it grants co-ownership of all objects in a DBEnvironment. A central component of ALLBASE/SQL. SQLCore checks the syntax of commands and prepares them for processing. SQLCore also optimizes queries, that is, chooses the best access path to the data. A utility program for database administrators that generates the SQL commands necessary to re-create all or part of a DBEnvironment. The output from SQLGEN is a command file (sometimes called a schema) that can be used as input to ISQL in re-creating database objects. A utility program for database administrators that assists in migrating a DBEnvironment from one version of ALLBASE/SQL to another without unloading and reloading data. A utility program for database administrators that assists with DBEnvironment maintenance, backup, and recovery. SQLUtil also lets you modify the startup parameters for a DBEnvironment. A standard query language syntax defined by ANSI standards in the United States nad X/OPEN standards in Europe. The relational database query language used by ALLBASE/SQL. A query within another query. An example is a subquery embedded in the predicate of another query. The result of the inner query is used to evaluate the outer query. A DBEFileSet created by ALLBASE/SQL when you issue the START DBE NEW command. The DBEFile known as DBEFile0 is associated with SYSTEM, which is the DBEFileSet containing the system catalog. You can add DBEFiles to SYSTEM as you would to any other DBEFileSet. Also, a special user associated with the system views in the system catalog. A system-maintained database of tables and views owned by the special user SYSTEM and containing information about all the objects in the DBEnvironment. Contains data about all the objects created in the DBEnvironment. Differs from the DBECon file, which contains startup parameters, not object definitions. A component view within the system catalog. You can issue queries on the views in the system catalog just as you would on ordinary database tables to display information about the DBEnvironment. Basic unit of data storage in a relational database. Also known as a relation. Tables consist of rows and columns. A result table is a query result displayed in tabular form. Permission to use specific SQL commands on particular tables. There are several kinds of TABLE authority: SELECT, INSERT, DELETE, UPDATE, and INDEX. SELECT, INSERT, and DELETE let you operate on rows or sets of rows in a table; UPDATE lets you modify specific rows or columns in a table; and INDEX lets you create indexes on a table. A unit of work in ALLBASE/SQL. Also, a unit of DBEnvironment logging and recovery. A transaction is started with a BEGIN WORK command and is ended by a COMMIT WORK command. The BEGIN WORK statement may be implicitly issued by ALLBASE/SQL if no other transaction is current. An integrity constraint which requires that no two rows in a table have the same values in a specified column or columns. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
![[absql13]](img/absql13.gif)