Relational Constructs   «Prev  Next»
Lesson 10 Keys Column Descriptors
Objective Distinguish between key columns and descriptor columns.

Keys for Database Tables and Column Descriptors


CUSTOMER
CustId CustLast CustFirst CustStreet CustAptNo CustCity CustState CustZip CustPhone

Entities are represented by Database Tables

As we have seen throughout this module, entities are represented by database tables. Those tables contain columns that describe different attributes of the entity.
For example, a table describing customers probably would include each customer's name, address, and phone number, along with several other categories of information.

Key columns

The CUSTOMER table also contains a column labeled CustID, which you can not fill in on the basis of customer input or any pre-existing information about the customer. Values in the CustID column are assigned by the data-entry clerk or are generated by the RDBMS.
Regardless of the source, the CustID column will contain a unique value for each customer in the database, thus distinguishing that record from every other record in the table. That makes the CustID column a key column, because it differentiates a record from every other record in the table.

Descriptor columns

Because it is possible for many records to have the same value as other records in columns such as CustFirst, CustLast, and CustPhone, those columns are referred to as descriptor columns. The columns describe the customer referred to by the record, but do not necessarily differentiate that customer from every other customer in the table.
In the extreme case, consider a father and son, both named Thomas L. Rothrauff, living at the same address. Because there is no column for a name suffix like "Jr." or "III", the two customers would be indistinguishable in the CUSTOMER table.

To solve the problem, the elder Mr. Rothrauff and the younger Mr. Rothrauff each would be assigned a unique CustID to differentiate them in the Stories on CD database.

CUSTOMER
CustID CustLast CustFirst CustStreet CustAptNo CustCity CustState CustZip CustPhone
001 Rothrauff Thomas 1 Water St. C Hurt VA 24081 540.555.1212
002 Rothrauff Thomas 1 Water St. C Hurt VA 24081 540.555.1212

CUSTOMER table consisting of 2 rows.

The Database Schema

In the SQL environment, a schema is a group of database objects which consist of 1) tables and 2) indexes and are related to each other. Usually, the schema belongs to a single user or application. A single database can hold multiple schemas belonging to different users or applications. Think of a schema as a logical grouping of database objects, such as tables, indexes, and views. Schemas are useful in that they group tables by owner (or function) and enforce a first level of security by allowing each user to see only the tables that belong to that user. ANSI SQL standards define a command to create a database schema:
CREATE SCHEMA AUTHORIZATION {creator};
Therefore, if the creator is JONES, use the command:
CREATE SCHEMA AUTHORIZATION JONES;

Columns and Column Characteristics

A column in a relation has the following properties.
  1. A name that is unique within the table: Two or more tables within the same relational database schema may have columns with the same names, in fact, as you will see shortly, in some circumstances this is highly desirable but a single table must have unique column names. When the same column name appears in more than one table and tables that contain that column are used in the same manipulation operation, you qualify the name of the column by preceding it with the name of its table and a period:
  2. A domain: The values in a column are drawn from one and only one domain. As a result, relations are said to be column homogeneous. In addition, every column in a table is subject to a domain constraint. Depending on your DBMS the domain constraint may be as simple as a data type, such as integers or dates. Alternatively, your DBMS may allow you to create your own, very specific, domains that can be attached to columns.
  3. There are no 'positional concepts': In other words, the columns can be viewed in any order without affecting the meaning of the data.
The next lesson defines primary keys.