SAS/ACCESS Software for Relational Databases: Reference

Overview of Relational Database Management Systems

A relational DBMS organizes and accesses data according to relationships among data items. The relationships among data items are expressed by tables consisting of columns and rows. The order of the rows and columns is not significant. Each column can contain one type of data, and each row can hold one data value for each column. See A DBMS Table for a conceptual picture of a table.

In a relational DBMS, you use a high-level language to operate on the data managed by the DBMS. In many cases, that language is SQL. A DBMS enables you, or an application program such as the SAS System, to use SQL statements to read, modify, create, and protect the data it manages.

Databases

A database is a collection of objects that includes tables, views, stored procedures, triggers, and indexes. Each database contains several database system files and data files. Databases are usually created by a database administrator with the SQL command CREATE DATABASE. In most databases, you use the GRANT statement to give users privileges on database objects to users. You can create SAS/ACCESS descriptor files to access the data in the objects, or access the objects directly by using the SAS/ACCESS LIBNAME statement.

Tables

A table is a named object that consists of a specific number of columns and some number of rows. The rows have no inherent order, unlike observations in a SAS data set.

In many databases, tables are created with the SQL statement CREATE TABLE. This statement names the table and the columns and defines the data type of each column.

A DBMS Table illustrates four columns from the CUSTOMERS table and highlights a column and a row.

A DBMS Table

[IMAGE]

Table Privileges

In a database, when you create a table or other database object, you own it. The owner of the table can do the following, depending on his or her privileges:

select columns and rows from the table for viewing
add or remove columns from the table
insert, delete, or update rows
create other objects (indexes and views) based on the table
alter or drop the table
grant privileges to other users and applications programs.

If you want to use a table that you do not own, the table's owner must grant you privileges to the table.

Views

A view is a named object that usually consists of an SQL SELECT statement that defines a group of data derived from one or more DBMS tables or views. The view itself contains no data. Views are usually created with the SQL statement CREATE VIEW, which names the view and includes an SQL SELECT statement that retrieves the data.

When you reference a view in an SQL statement, the DBMS accesses the table(s) or view(s) on which the view is based. Certain restrictions might apply when a view derives its data from multiple tables or other views. For example, updates, inserts, and deletes might not be allowed. For more information on view restrictions, refer to your DBMS documentation.

Note: To access a view owned by another user, you must be granted privileges on the view and on its underlying tables and views. Having privileges on the table(s) from which a view derives its data does not automatically give you privileges on the view. [cautionend]

Note: If a table from which a view derives its data is dropped, the view itself is also dropped. [cautionend]

Indexes

An index is a database object that speeds data retrieval by directing the DBMS to the location in storage of a particular row for a given column. Indexes are usually created with the SQL statement CREATE INDEX, which assigns the index a name and identifies the table and column(s) on which the index is created. In many DBMSs, the CREATE INDEX statement enables you to specify the keyword UNIQUE to define a unique index on one or more columns. In some DBMSs, you can also use a CONSTRAINT clause with an ALTER TABLE statement to specify the keywords UNIQUE and USING INDEX, which define a unique index on one or more columns. Unique indexes prevent duplicate values in the column, or combination of columns, on which they are created. Some DBMSs allow you to create different types of indexes, such as sorted or hashed indexes. See your DBMS documentation for details.

An index is keyed on all specified columns unless the KEY option is used. Keys provide a way to identify rows and relate (or join) rows in one table to rows in another table. A primary key is the column or combination of columns that uniquely identify a row. A foreign key is a column or combination of columns in one table that reference the primary key in another table. The foreign key must have the same attributes as the primary key it references.

If the table on which an index is created is dropped, the index is also dropped.

Most DBMSs automatically determine the most efficient way to process an SQL statement and uses the appropriate indexes if they are available. In addition, the way you specify criteria for selecting rows can affect whether the DBMS can use the indexes. Your database administrator can help you determine whether your selection criteria enable the DBMS to use your tables' indexes.

Stored Procedures

Stored procedures are another type of named object. They usually consist of SQL statements, except for CREATE statements, and include control-of-flow keywords such as IF, ...ELSE, BEGIN, and ...END. Because stored procedures are precompiled, they are more efficient than submitting the same SQL statements individually or in batches.

Stored procedures can generally take parameters, return status values and parameters, and call other procedures. They can also be executed on remote servers. Stored procedures are often created to execute groups of SQL statements that are submitted repeatedly. For example, a stored procedure might be created to list all the orders that were backordered for a particular stock number and to remove the back-order status from that stock number. The stored procedure could then be used whenever a formerly out-of-stock product is back in stock.

See your DBMS documentation for information on whether your DBMS supports stored procedures and how to create them.

Triggers

A trigger is a stored procedure that is associated with a table. They are often used to help maintain referential integrity--that is, consistency among related data that is stored in different tables--or to maintain business constraints. Triggers are executed whenever data modification operations (UPDATE, INSERT, or DELETE) are performed on a specified column. You use the CREATE TRIGGER statement to create a trigger. You can use triggers to

enforce security authorizations or business-specific constraints
provide auditing and transparent event-logging
generate derived column values automatically
maintain replicated asynchronous tables.

A trigger is automatically executed, or "fired," by the DBMS when a specified SQL statement is issued against a table. You can define triggers for SQL DELETE, INSERT, and UPDATE statements. You also define whether a trigger fires before or after a specified SQL statement is executed or once for each row affected by the statement.

The SAS/ACCESS interface view engine generally supports all actions and constraints specified by triggers. For more information, refer to your DBMS documentation.

Constraints

A constraint restricts the values that can be stored in a table. A constraint checks the values you insert or update in a table against the conditions specified by the constraint. For example, a constraint could ensure that the value entered for the column GENDER is only M or F. Constraints help ensure the referential integrity in a database. See your DBMS documentation for additional information on how your DBMS supports referential integrity.

Chapter Contents
Previous
Next
Top of Page