Database Reference
In-Depth Information
CHAPTER 2
THE RELATIONAL MODEL 1: INTRODUCTION, QBE, AND
RELATIONAL ALGEBRA
—
1. A relation is a two-dimensional table in which (1) the entries in the table are single-valued,
(2) each column has a distinct name, (3) all of the values in a column are values of the same
attribute, (4) the order of the columns is immaterial, (5) each row is distinct, and (6) the order of
the rows is immaterial.
3. An unnormalized relation is a structure that satisfies all the properties of a relation except the
restriction that entries must be single-valued. It is not a relation.
5. In the shorthand representation, each table is listed, and after each table, all the columns of the
table are listed in parentheses. Primary keys are underlined. The shorthand representation for the
Henry Books database is as follows:
360
Branch (BranchNum, BranchName, BranchLocation)
Publisher (PublisherCode, PublisherName, City)
Author (AuthorNum, AuthorLast, AuthorFirst)
Book (BookCode, Title, PublisherCode, Type, Paperback)
Wrote (BookCode, AuthorNum, Sequence)
Copy (BookCode, BranchNum, CopyNum, Quality, Price)
7. The primary key is the column or collection of columns that uniquely identifies a given row. In
the Henry Books database, the primary key of the Branch table is BranchNum. The primary key
of the Publisher table is PublisherCode. The primary key of the Author table is AuthorNum. The
primary key of the Book table is BookCode. The primary key of the Wrote table is the concatena-
tion (combination) of BookCode and AuthorNum. The primary key of the Copy table is the con-
catenation of BookCode, BranchNum, and CopyNum.
9. Enter the criteria in the Criteria row for the appropriate field name.
11. Type the expression instead of a field name in the design grid. Alternatively, you can enter the
expression in the Zoom dialog box.
13. Indicate the appropriate sort order (ascending or descending) in the Sort row of the design grid
for the appropriate field.
15. Include the field lists from both tables in the query design. Provided the tables have matching fields,
a join line will connect the tables. Include the desired fields from either table in the design grid.
17. Use a delete query when you want to delete all rows satisfying some criteria.
19. Relational algebra is a theoretical way of manipulating a relational database. Relational algebra
includes operations that act on existing tables to produce new tables, similar to the way the
operations of addition and subtraction act on numbers to produce new numbers in the mathe-
matical algebra with which you are familiar.
21. The PROJECT command selects only the specified columns.
23. The UNION command selects all rows that are in the first table, in the second table, or both.
25. The INTERSECT command selects all rows that are in both tables.
27. The PRODUCT command (mathematically called the Cartesian product) is the table obtained by
concatenating every row in the first table with every row in the second table.
CHAPTER 3
THE RELATIONAL MODEL 2: SQL
—
1. To create a table in SQL, use a CREATE TABLE command. The word TABLE is followed by the
name of the table to be created and then by the names and data types of the columns (fields) that
make up the table. The data types you can use are INTEGER (large negative and positive whole
numbers), SMALLINT (whole numbers from
32,768 to 32,767), DECIMAL (numbers that have a
decimal part), CHAR (alphanumeric strings), and DATE (date values).
-
