Keys for Database Tables and Column Descriptors

Question: How does a database designer distinguish between key columns and descriptor columns of a table?
In a relational database, a clear distinction between key columns and descriptor columns is essential for ensuring data integrity, efficient data retrieval, and proper table structure. A database designer distinguishes between these two types of columns based on their functional roles and characteristics within a table.

Key columns are used to establish unique identification, relationships, and referential integrity within a table or across multiple tables in a database. They are typically classified into two categories:

1. Primary key: A primary key is a column or a combination of columns in a table that uniquely identifies each row. Primary keys must satisfy the following conditions:
   
   1. Unique: No two rows can have the same primary key value.
   2. Non-null: Primary key columns cannot contain null values.
   3. Immutable: Primary key values should rarely, if ever, be modified to maintain data integrity and avoid potential inconsistencies.
2. Foreign key: A foreign key is a column or a set of columns in one table that refers to the primary key in another table. Foreign keys establish relationships between tables, allowing for more efficient querying and enforcing referential integrity constraints.
   When designing a table, the database designer identifies key columns based on the table's requirements and the relationships that must be established between tables.

Descriptor columns, also known as non-key columns or attribute columns, provide additional information or attributes about each row in a table. These columns are not used to uniquely identify rows or establish relationships between tables, but rather to store the data required for various applications and use cases. Descriptor columns can include a wide range of data types, such as text, numbers, dates, or binary data, depending on the nature of the data being stored. When designing a table, the database designer identifies descriptor columns based on the information that needs to be stored for each entity represented by the table. These columns should be chosen to support the intended application's functionality, reporting needs, and data analysis requirements.
In summary, a database designer distinguishes between key columns and descriptor columns by analyzing the functional roles and characteristics of each column within a table. Key columns are used for unique identification and establishing relationships, while descriptor columns store additional attributes and information about each entity. By clearly defining the roles of these columns, a designer can create efficient, well-structured, and maintainable database systems that effectively support the desired applications and use cases.

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.

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.

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 customervin 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.

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};

CREATE SCHEMA AUTHORIZATION JONES;

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.