Database Reference
In-Depth Information
Summary
Views are used to give each user his or her own view of the data in a database. In SQL, a defining query
creates a view. When you enter a query that references a view, it is merged with the defining query to pro-
duce the query that is actually executed. In Access, views are created by saving queries that select the
data to use in the view.
148
Indexes are often used to facilitate data retrieval from the database. You can create an index on any field
or combination of fields.
Security is provided in SQL systems by using the GRANT and REVOKE statements.
Entity integrity is the property that states that no field that is part of the primary key can accept null values.
Referential integrity is the property that states that the value in any foreign key field must be null or must
match an actual value in the primary key field of another table. Referential integrity is specified in SQL
using the FOREIGN KEY clause. In Access, foreign keys are specified by creating relationships.
Legal-values integrity is the property that states that the value entered in a field must be one of the legal
values that satisfies some particular condition. Legal-values integrity is specified in SQL using the CHECK
clause. In Access, legal-values integrity is specified using validation rules.
The ALTER TABLE command allows you to add fields to a table, delete fields, or change the characteristics of
fields. In Access, you can change the structure of a table by making the desired changes in the table design.
The DROP TABLE command lets you delete a table from a database. In Access, you can delete a table
by selecting the Delete command on the table's shortcut menu in the Navigation Pane.
The system catalog is a feature of many relational DBMSs that stores information about the structure of a
database. The system updates the catalog automatically. Each DBMS includes features to produce docu-
mentation of the database structure using the information in the catalog.
A stored procedure is a query saved in a file that users can execute later.
A trigger is an action that occurs automatically in response to an associated database operation such as
an INSERT, UPDATE, or DELETE command. Like a stored procedure, a trigger is stored and compiled on
the server. Unlike a stored procedure, which is executed in response to a user request, a trigger is exe-
cuted in response to a command that causes the associated database operation to occur. Access provides
the functionality of triggers through the use of data macros.
Key Terms
argument
ALTER TABLE
cascade delete
cascade update
catalog
CHECK
client
client/server system
CREATE INDEX
data macro
defining query
delimiter
Documenter
DROP INDEX
DROP TABLE
entity integrity
FOREIGN KEY
foreign key
GRANT
index
index key
legal-values integrity
multiple-column index
multiple-field index
PRIMARY KEY
referential integrity
REVOKE
row-and-column subset view
security
server
single-column index
single-field index
stored procedure
Syscolumns
