Database Reference
In-Depth Information
NOTE
Rows in a table (relation) are often called records or
tuples. Columns in a table (relation) are often called fields or attributes.
34
There is a commonly accepted shorthand representation that shows the structure of a relational database:
You write the name of the table and then, within parentheses, list all the columns in the table. In addition, each
table should appear on its own line. Using this method, you would write the Premiere Products database as follows:
Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate)
Customer (CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
Orders (OrderNum, OrderDate, CustomerNum)
OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice)
Part (PartNum, Description, OnHand, Class, Warehouse, Price)
The Premiere Products database contains some duplicate column names. For example, the RepNum col-
umn appears in both the Rep table and the Customer table. Suppose a situation exists wherein the two col-
umns might be confused. If you write RepNum, how would the computer or another programmer know which
RepNum column in which table you intend to use? That could be a problem.
When duplicate column names exist in a database, you need a way to indicate the column to which you are
referring. One common approach to this problem is to write both the table name and the column name, separated
by a period. Thus, you would write the RepNum column in the Customer table as Customer.RepNum and the
RepNum column in the Rep table as Rep.RepNum. Technically, when you combine a column name with a table
name, you say that you qualify thecolumnnames.Itisalways acceptable to qualify column names, even when
there is no possibility of confusion. If confusion may arise, however, it is essential to qualify column names.
The primary key of a table (relation) is the column or collection of columns that uniquely identifies a
given row in that table. In the Rep table, the sales rep
s number uniquely identifies a given row. For example,
rep 35 occurs in only one row of the table. Thus, RepNum is the primary key for the Rep table.
The primary key provides an important way of distinguishing one row in a table from another. Primary keys
are usually represented by underlining the column or collection of columns that comprises the primary key for
each table in the database. Thus, the complete representation for the Premiere Products database is as follows:
'
Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate)
Customer (CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
Orders (OrderNum, OrderDate, CustomerNum)
OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice)
Part (PartNum, Description, OnHand, Class, Warehouse, Price)
Q&A
Question: Why does the primary key of the OrderLine table consist of two columns, not just one?
Answer: No single column uniquely identifies a given row in the OrderLine table. It requires a combination of
two columns: OrderNum and PartNum.
QUERY-BY-EXAMPLE (QBE)
When you ask Access or any other DBMS a question about the data in a database, the question is called a
query. A query is simply a question represented in a way that the DBMS can recognize and process. In this
section, you will investigate Query-By-Example (QBE), an approach to writing queries that is very visual.
With QBE, users ask their questions by entering column names and other criteria using an on-screen grid,
and data appears on the screen in tabular form.
NOTE
This chapter features a specific version of QBE, Microsoft Access 2010, to illustrate the use of QBE. Although the various ver-
sions of QBE are not identical, the differences are relatively minor. After you have mastered one version of QBE, you can apply
your skills to learn another version of QBE.
