Database Design Chapter 6

Procedure

A collection of procedural and SQL statements that are assigned a unique name within the schema and stored in the database.

User-defined data type (UDT)

A data type that a user can define by making it a subclass of a standard type or creating a type that behaves as an object. UDTs may also have defined functions and methods.

Outer join

A join in which rows that do not have matching values in common columns are nevertheless included in the result table.

Equi-join

A join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table.

Natural join

A join that is the same as an equi-join except that one of the duplicate columns is eliminated in the result table.

Trigger

A named set of SQL statements that are considered (triggered) when a data modification (i.e., INSERT, UPDATE, DELETE) occurs or if certain data definitions are encountered. If a condition stated within a trigger is met, then a prescribed action is taken.

Join

A relational operation that causes two tables with a common domain to be combined into a single table or view.

Function

A stored subroutine that returns one value and has only input parameters.

Virtual table

A table constructed automatically as needed by a DBMS. Virtual tables are not maintained as real data.

Base Table

A table in the relational data model containing the inserted raw data. Base tables correspond to the relations that are identified in the database's conceptual schema.

Dynamic view

A virtual table that is created dynamically upon request by a user. A dynamic view is not a temporary table. Rather, its definition is stored in the system catalog, and the contents of the view are materialized as a result of an SQL query that uses the view. It differs from a materialized view, which may be stored on a disk and refreshed at intervals or when used, depending on the RDBMS.

Materialized view

Copies or replicas of data, based on SQL queries created in the same manner as dynamic views. However, a materialized view exists as a table, and thus care must be taken to keep it synchronized with its associated base tables.

Persistent Stored Modules (SQL/PSM)

Extensions defined originally in SQL:1999 that include the capability to create and drop modules of code stored in the database schema across user sessions.

correlated subquery

In SQL, a subquery in which processing the inner query depends on data from the outer query.

Match the following terms to the appropriate definition: ____ equi-join ____ derived table ____ natural join ____ correlated subquery ____ outer join ____ trigger a. returns all records of designated table b. keeps redundant columns c. utilizes values from main query in the subquery d. outcome of a query embedded in the FROM clause e. a set of SQL statements executed under specific conditions f. does not keep redundant columns

b. equi-join d. derived table f. natural join c. correlated subquery a. outer join e. trigger

self-join

when a join requires matching rows in a table with other rows in that same table—that is, joining a table with itself. Self-joins arise for several reasons, the most common of which is a unary relationship