Module 2 - The Relational Model: Introduction, QBE, and Relational Algebra

Query-By-Example (QBE)

A visual interface that helps you retrieve data from relational databases.

relations

A two-dimensional table-style collection of data in which all entries are single-valued, each column has a distinct name, all the values in a column are values of the attribute that is identified by the column name, the order of columns is immaterial, each row is distinct, and the order of rows is immaterial. Also called a table.

records

A collection of related fields; can be thought of as a row in a table. Also called a tuple.

unnormalized relation

A structure that satisfies the properties required to be a relation (table) with the exception of allowing repeating groups (the entries in the table do not have to be single-valued).

qualify

To indicate the table (relation) of which a given column (attribute) is a part by preceding the column name with the table name. For example, Clients.Street indicates the column named Street in the table named Clients.

repeating groups

Multiple entries for a single record in a table.

tuples

A collection of related fields; can be thought of as a row in a table. Also called a record or row.

primary key

The column or columns that uniquely identify a row in a table.

GUI (graphical user interface)

A visual way of interacting with a computer using items such as icons and menus instead of textual commands.

query

A question structured in a way that the DBMS can recognize and process.

foreign key

The field used to connect a table on the 'many' side of a one-to-many relationship.

composite primary key

An identifier used when more than one column is necessary to make a row unique in a table. See also primary key.

Query Design View

The Access window in which you develop queries by specifying the fields, sort order, and limiting criteria that determine which fields and records are displayed in the resulting datasheet.

select query

A query that selects fields and records from the database. Also called a simple query.

design grid

In Access, the area in which you specify the fields to include in the query results, a sort order for the query results, any criteria, and other instructions.

Design View

An Access view in which the structure of an object can be manipulated.

simple query

A query that selects fields and records from the database. Also called a Select query.

Datasheet View

An Access view that shows a table as a collection of rows and columns, similar to a spreadsheet.

criteria

More than one criterion or condition.

comparison operator

An operator used to compare values. Valid operators are =,

parameter query

A query that prompts the user for input before executing.

operators

A mathematical symbol used in an expression to combine different values, resulting in a single value.

criterion

A single condition or criterion used to filter records.

relational operator

An operator used to compare values, such as =,

compound criteria

Criteria that involve multiple conditions.

OR criteria

Criteria that allow for multiple possible matches.

BETWEEN operator

An operator used to filter records within a specified range.

compound conditions

Conditions that combine multiple criteria.

AND criteria

Criteria that require all conditions to be true.

computed field

A field that derives its value from an expression involving other fields.

Condition

A statement that can be either true or false. In queries, only records for which the statement is true will be included; also called a condition.

Combination of Criteria

Combination of criteria in which only one criterion must be true.

AND/OR Criteria

Two simple criteria (conditions) in a query that are combined with the AND or OR operators.

Range Operator

An operator that allows you to specify a range of values for the criteria, including the lower number, the higher number, and all numbers in between.

Calculated Field

A field whose value is computed from other fields in the database; also called a calculated field.

Criteria Combination

Combination of criteria in which each criterion must be true.

Functions

Part of an expression used to calculate the number of entries, the sum or average of all the entries in a given column, or the largest or smallest of the entries in a given column; also called aggregate function.

Text Data

Text data, including spaces, symbols, and punctuation marks.

Expression

A combination of data and operators that results in a single value.

Boolean

A data type for fields that store only Yes or No (On or Off, True or False) values.

Argument

Information a macro action needs to complete processing; also, specific information provided to a function so it can operate.

Grouping Field

The field that divides records into groups based on the values in the specified field.

Group

To create collections of records that share a common characteristic.

Return Value

The data a function returns when it completes its task.

Sort

To arrange rows in a table or results of a query in a particular order.

Sort Key

The field on which records are sorted.

Primary Sort Key

When sorting on two fields, the more important field; also called a major sort key.

Minor Sort Key

When sorting on two fields, the less important field; also called secondary sort key.

Join

To connect two tables based on common data.

Parent Table

The table on the "one" side of a "one-to-many" relationship.

Child Table

The table on the "many" side of a "one-to-many" relationship.

one-to-many relationship

The table on the "one" side of a "one-to-many" relationship.

primary key field

A field that contains unique information for each record. A primary key field cannot contain a null entry.

key symbol

In Access, the symbol that identifies the primary key field in each table.

foreign key field

In a one-to-many relationship between two tables, the field in the "many" table that links the table to the primary key field in the "one" table.

scrubbing

To remove and fix orphan records in a relational database.

action query

A query that changes data in a batch process.

make-table query

A query that creates a new table in the current or another database with the records selected by the query.

batch update

To modify several records in a single process.

update query

A query that makes a specified change to all records satisfying the criteria in the query.

delete query

A query that permanently deletes all the records satisfying the criteria entered in the query.

query optimizer

A DBMS component that analyzes a query and attempts to determine the most efficient way to execute it.

SQL

See Structured Query Language (SQL).

Relational algebra

A query language that takes instances of relations as input and yields instances of relations as output.

PROJECT

The relational algebra command used to select columns from a table.

SELECT

SQL keyword to select fields and records from a relational database; also, the relational algebra command to select rows from a table.

join column

The column on which two tables are joined. Also see join.

Structured Query Language

A very popular relational data definition and manipulation language that is used in many relational DBMSs.

outer join

The form of a join in which all records appear, even if they don't match.

concatenation

The combination of two or more rows in an operation, such as a join, or the combination of two or more columns for a primary key field to uniquely identify a given row in the table; also, joining two or more strings of textual data into one piece of data.

null

An intentional "nothing" value meaning "unknown" or "not applicable."

union compatible

Two tables are union compatible if they have the same number of fields and if their corresponding fields have identical data types.

union

A combination of two tables consisting of all records that are in either table.

natural join

The most common form of a join.

intersection

When comparing tables, an intersection is a new table containing all rows that are in both original tables.

difference

When comparing tables, the set of all rows that are in the first table but that are not in the second table.

INTERSECT

The relational algebra command for performing the intersection of two tables.

SUBTRACT

The relational algebra command for performing the difference of two tables.

product

The table obtained by concatenating every row in the first table with every row in the second table.

Cartesian product

The table obtained by concatenating every row in the first table with every row in the second table.

division

The relational algebra command that combines tables and searches for rows in the first table that match all rows in the second table.