Databases Reference
In-Depth Information
table for each topic means that data are stored only once, which makes the database more efficient
and reduces data-entry errors. Tables organize data into columns (also referred to as fields) and
rows (also referred to as records).
A table is just a set of rows and columns. This is very important because a set does not have
order for its elements. Each row is a set of columns, each of which can have only
one possible value. All rows from the same table have the same set of columns, although some
columns may have NULL values, i.e., some values in that row may not be initialized. Note that a
NULL value for a string column is different from an empty string. You should think about a NULL
value as an “unknown” value.
The next logical question is: if data can be stored within many different tables in the same
database, how can all the different tables relate to each other to extract required information? The
answer is: the common field. A common field is a column that is present within each table that
must be related to. Using a common field, Microsoft Access can bring together the data from many
different tables for viewing, editing, or printing.
To illustrate the concept of the table, a sample database will be created for a clock shop. In
this instance, two tables will be created — one for products (named “Products”) and one for
suppliers (named “Suppliers”). The products table will contain information relating to the prod-
ucts available for sale at the clock shop. The fields or columns in the products table will be:
MANUFACTURER, MODELNAME, MODELNUMBER, PRICERETAIL, PRICECON-
SUMER, PRICECOST, PICTURE, TYPE, and DESCRIPTION. Notice that every field name is
in capital letters. This is not required, but many database administrators choose to name columns
in uppercase letters (it takes the guesswork out of what case a letter is in a database query, which
will be covered in depth later in the chapter). Another common nomenclature is to separate
descriptors in field names by means of an underscore character “_.” Example: MODELNAME
becomes MODEL_NAME.
The Supplier's table will contain information relating to each supplier the clock shop acquires
stock from. The fields in this table will be: NAME, MANUFACTURER, ADDRESS, CITY, STATE,
ZIP, PHONE, FAX, and WEBSITE. The MANUFACTURER is the common field between the
products and suppliers tables. If the contact information is desired for a specific product, a query
can be made between the two tables utilizing the MANUFACTURER field. In some cases the
NAME of the supplier will be identical to the name of the MANUFACTURER.
A table can easily be created in Access by clicking on the “Create Table in Design View” icon
as illustrated in Figure 7.1. Before creating a table, the user must decide what data type each field
will be.
any
predefined
FIGURE 7. 1
Creating a Table in the Design View.
