Fall Semester 2003

• Describe the relational model.
• Understand Query-By-Example (QBE).
• Use criteria in QBE.
• Create calculated columns in QBE.
• Calculate statistics in QBE.
• Sort data in QBE.
• Join tables in QBE.
• Update data using QBE.
• Understand relational algebra.

Key Terms

• aggregate function
• AND criterion
• attribute
• calculated field
• Cartesian product
• comparison operator
• compound condition
• compound criteria
• computed field
• concatenation
• criteria
• criterion
• delete query
• design grid
• difference
• division
• field
• grouping
• INTERSECT
• intersection
• join
• join column
• join line
• major sort key
• make-table query
• minor sort key
• natural join
• null
• OR criterion
• outer join
• primary key
• primary sort key
• product
• PROJECT
• qualify
• query
• Query-By-Example (QBE)
• record
• relation
• relational algebra
• relational database
• relational operator
• repeating group
• secondary sort key
• SELECT
• sort
• sort key
• statistic
• SUBTRACT
• tuple
• union
• union compatible
• unnormalized relation
• update query

Chapter Outline

I.	Introduction
II.	Relational Databases
III.	Query-By-Example (QBE)
IV.	Simple Queries
V.	Simple Criteria
VI.	Compound Criteria
VII.	Computed Fields
VIII.	Calculating Statistics
IX.	Grouping
X.	Sorting
	A. Sorting on Multiple Keys
XI.	Joining Tables
	A. Joining Multiple Tables
XII.	Using Update Queries
XIII.	Using Delete Queries
XIV.	Using Make-Table Queries
XV.	Relational Algebra
XVI.	Select
XVII.	Project
XVIII.	Join
XIX.	Normal Set Operations
	A. Union
	B. Intersection
	C. Difference
XX.	Product
XXI.	Division
XXII.	Summary
XXIII.	Key Terms
XIV.	Review Questions
XV.	Premiere Products Exercises: QBE
XVI.	Premiere Products Exercises: Relational Algebra
XVII.	Henry Books Case

Notes and Main Ideas

Relational Databases

A relational database is a collection of tables. Formally, the tables are called relations. In a relational database, each entity is stored in its own table and the attributes of the entity become fields or columns in the table. Relationships are implemented through common columns in two or more tables. A relation is a two-dimensional table with specific properties. These properties include: entries in the table are single-valued; each column has a distinct name; all values in a column are values of the same attribute; the order of the columns is immaterial; each row is distinct; and the order of the rows is immaterial. If a structure contains entries that are not single-valued (repeating groups occur), then the structure is called an unnormalized relation. To make each row distinct, one or more columns must uniquely identify a given row in a table. This column or collection of columns is called the primary key. When two or more tables in a database use the same column name, qualify the column name, that is, combine the table name and the column name.

Query-By-Example (QBE)

A query is simply a question represented in a way that a DBMS can recognize and process. Query-By-Example (QBE) is an approach to writing queries that is very visual. The QBE illustrations for Premiere Products use the PC-based DBMS called Access 2002, which is a product of Microsoft.

Simple Queries

A query result can include all fields in a table or only selected fields. For a field to display in a query result, include the field in the design grid.

Simple Criteria

To restrict the results of a query, use criteria (conditions). A comparison operator (relational operator) finds something other than an exact match. The comparison operators are = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and NOT (not equal to).

Compound Criteria

It is possible to combine criteria (compound criteria) to achieve the desired query results. In an AND criterion, both criteria must be true for the compound criterion to be true. In an OR criterion, the overall criterion is true if either of the individual criteria are true.

Computed Fields

Computed (calculated) fields allow the user to add fields to a query that are not stored in the database.

Calculating Statistics

All products that support QBE have built-in statistics (aggregate functions) such as Count, Sum, Avg, Max, Min, StDev (standard deviation), Var (variance), First, and Last.

Grouping

Grouping means creating groups of records that share some common characteristic. To calculate statistics for groups of records, combine grouping with built-in statistics.

Sorting

To list the records in a query's results in a particular order, sort the records. You can sort records using more than one field when necessary.

Joining Tables

When you need to query more than one table, it is necessary to join the tables. To do this, you combine the tables based on matching fields in corresponding columns.

Using Update Queries

An update query makes a specified change to records satisfying the criteria in the query.

Using Delete Queries

A delete query deletes all records satisfying the criteria entered in the query.

Using Make-Table Queries

A make-table query creates a new table using the query results. The new table may be in the current database or in a separate database.

Relational Algebra

Relational algebra is a theoretical way of manipulating a relational database. Operations act on existing tables to produce new tables. When using relational algebra commands, you should use the GIVING clause to identify the name of a temporary table in which to store the results of the command.

Select

The SELECT command takes a horizontal subset of a table and causes only certain rows to be included in the new table.

Project

The PROJECT command takes a vertical subset of a table and causes only certain columns or fields to be included in the new table.

Join

The JOIN command extracts data from more than one table. The tables are joined using a common value in matching columns. The new table displays the joined column only once.

Normal Set Operations

Relational algebra includes set operations for union, intersection, and difference. The union of two tables is a table containing all rows that are in either table or in both tables. For the UNION command to be appropriate, the tables must have the same structure. The intersection (INTERSECT) of two tables is a table containing all rows that are common in both tables. The difference (SUBTRACT) between two tables is the set of all rows that are in one table but not the other.

Product

The product of two tables is the table obtained by concatenating every row in the first table with every row in the second table. Mathematically, this product is called the Cartesian product.

Division

Division occurs when a table is divided by another table. The most common instance is the division of a table with two columns by a table with a single column.

1. What is the formal name for a table?

   ANSWER: relation (highlight to see the answer)

2. What term is given to the column or collection of columns that uniquely identify a given row in a table?

   ANSWER: primary key (highlight to see the answer)

3. Which command within relational algebra takes a horizontal subset of a table?

   ANSWER: SELECT (highlight to see the answer)

Many more such questions will be posted in QuizSite.