Relational Constructs   «Prev  Next»

Lesson 14 All-key relations
Objective Describe all-key relations.

Describe All-key Relations in Database

When a table is created to represent an entity, it is possible that every column in the table will be part of the entity's primary key. As an example, examine the entities in the following graphic.

Database consisting of three tables: 1) ITEM 2) LANGUAGES 3) AVAILABLE_IN
Database consisting of three tables: 1) ITEM, 2) LANGUAGES, 3) AVAILABLE_IN

All-key Relations Defined

The entity named AVAILABLE_IN contains two columns, both of which are part of the entity's primary key.
When every field in a relation is part of the primary key for that relation, the relation is referred to as an all-key relation [1]. All-key relations are valuable because they enable you to create many-to-many relationships between tables in the database without incurring the problems of many-to-many relationships.
AVAILABLE_IN provides just such a bridge between the ITEM and LANGUAGES tables. Rather than including fields in the ITEM table to record in which languages the CD is available (which is bad design, as we will see), or creating a many-to-many relationship (which is also bad design), users can enter as many languages as they need to describe each Stories on CD product.
Note: The entity AVAILABLE_IN contains the column ItemNo which is a foreign key to the table ITEM. The information about languages is stored in the AVAILABLE_IN table.
The next lesson describes foreign keys[2].

All-key Relations

It is possible to have a table in which every column is part of the primary key. As an example, consider a library book catalog where each book title owned by a library has a natural unique primary key: the ISBN (International Standard Book Number). Each ISBN is assigned to one or more subject headings in the library's catalog, and each subject heading is also assigned to one or more books. We therefore have a many-to-many relationship between books and subject headings. A relation to represent this relationship might be:
subject_catalog (isbn, subject_heading)

All we need to do is pair a subject heading with a book identifier and no additional data is needed. Therefore, all columns in the table become part of the primary key. There is absolutely no problem with having all-key relations in a database. In fact, they occur whenever a database design contains a composite entity that has no relationship data. They are not necessarily errors, and you can use them wherever needed.

Key Constraints

Key constraints include primary keys, foreign keys, and unique keys. All these key types are discussed briefly later on in this chapter and further in later chapters in this book. Key constraints allow the checking and validation of values between fields in different tables. Primary and foreign keys are essentially the implementation of relationships between parent and child tables. Those relationships or relations are the source of the term relational database.

[1] all-key relation: A relation in which every field is a member of the relation's primary key
[2] foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.