Module 4 - The Relational Model: Advanced Topics

single-field index

An index built on a single field (column).

index

A behind-the-scenes, system-generated copy of a selected field or fields organized in ascending or descending order so that when data in that field is used in a search or sorted, the process is much faster than it would be without the predeveloped index.

CREATE INDEX

The SQL command that creates an index in a table.

single-column index

An index built on a single field (column).

multiple-field index

An index with more than one key field.

multiple-column index

An index with more than one key field.

composite key

An identifier consisting of a combination of two or more fields that is used when more than one column is necessary to make a row unique in a table; also, an index with more than one key field.

DROP INDEX

The SQL command that drops (deletes) an index.

GRANT

The SQL command that provides privileges to users, including the right to select, insert, update, and delete table data.

index key

The field or fields on which an index is built.

exclusive mode

A way of opening a database so that you are the only user currently working in the database and have also locked all other users and processes from accessing it.

REVOKE

The SQL command that removes privileges granted to users.

file level

Tasks such as those for security, encryption, and backup are applied to the entire database file, including all tables, queries, and other objects that the database may contain.

Firewalls

Software that prevents the unauthorized access to a network or server, and can protect against electronic attacks on a database.

entity integrity

The rule that no column (attribute) that is part of the primary key may accept null values.

PRIMARY KEY clause

The SQL clause that is used in a CREATE TABLE or ALTER TABLE command to set a table’s primary key field(s).

FOREIGN KEY clause

The clause in an SQL CREATE TABLE or ALTER TABLE command that specifies referential integrity.

Cascade Delete Related Records

An Access setting specifying that if a record on the 'one' side of a relationship is deleted, all related records in the 'many' table are also deleted.

Cascade Update Related Fields

An Access setting specifying that if the primary key field in the table on the 'one' side of the relationship changes, the foreign key field value in all related records in the table on the 'many' side of the relationship will also change.

legal-values integrity

The property that no record can exist in the database with a value in a field other than a legal value.

CHECK

The SQL clause used to enforce legal-values integrity.

validation rule

In Access, a rule that data entered in a field must satisfy.

validation text

In Access, a field property that is displayed in a dialog box if a user enters an invalid value for the field.

field properties

A characteristic that defines each field such as Indexed, Field Size, Validation Rule, and Validation Text.

ALTER TABLE

The SQL command used to change the structure of a table.

DROP TABLE

The SQL command that drops (deletes) a table from a database.

system catalog

A structure that contains information about the objects (tables, columns, indexes, views, and so on) in a database. Also called the catalog.

RIGHT JOIN

The SQL command that selects all records in the table on the 'many' side of a one-to-many relationship even if they do not have any matching records in the 'one' table.

INNER JOIN

The SQL command that selects records that have matching values in both tables.

LEFT JOIN

The SQL command that selects all records in the table on the 'one' side of a one-to-many relationship even if they do not have any matching records in the 'many' table.

system tables

A table containing metadata about the database.

catalog

A structure that contains information about the objects (tables, columns, indexes, views, and so on) in a database. Also called the system catalog.

Database Documenter

An Access tool that allows you to print detailed documentation about any table, query, report, form, or other object in the database.

server

A centralized computer that stores the database and provides services to the clients in a client/server system; also a computer or program that provides functionality for other programs or devices and access to resources, especially network resources.

client/server system

A networked system in which a special site on the network, called the server, provides services to the other sites, called the clients. Clients send requests for the specific services they need. Software, often including a DBMS, running on the server then processes the requests and sends only the appropriate data and other results back to the clients.

client

A computer that is connected to a network and has access to the server in a client/server system; also, hardware or software that accesses a service made available by a server.

table event

A definable action that occurs in a database table such as adding, changing, or deleting data in a record.

stored procedure

A file containing a collection of compiled and optimized SQL statements that are available for future use.

data macro

An automatic process that runs when a table event occurs.

Action Catalog

In Access, a small window that displays available macro actions.

trigger

An action that occurs automatically in response to an associated database operation such as INSERT, UPDATE, or DELETE.

After Insert

An Access table event that runs after a new record has been inserted into the table.

Before Delete

An Access table event that runs before a record is deleted to help the user validate or cancel the deletion.

Before Change

An Access table event that runs before a record is changed to help the user validate or cancel the edits.

After Update

An Access table event that runs after an existing record has been changed.

After Delete

An Access table event that runs after an existing record has been deleted.