Database Entitities and Their Subtypes

The following questions/answers will help the reader gain a better idea of database relationships. Specifically, these questions look at entities and their subtypes.

1. What is an entity supertype, and why is it used?

An entity supertype is an entity that is related to one or more entity subtypes. This is the entity that contains the common characteristics of the various subtypes. These are used when created entities may have subtypes that each contains unique attributes. Supertypes, combined with subtypes, serve to remove excessive null data fields caused by the varying data needed for possible subgroups.

2. What kinds of data would you store in an entity subtype?

Entity subtypes are used to store unique attributes based on the subtype discriminator. In these tables, data that is related to that discriminator can be entered and stored. This eliminates the many null values that would occur if only one general entity was created.

3. What is a specialized hierarchy?

Specialized hierarchies are organizations of entity supertypes and subtypes, depicting their relationships, attributes, and discriminators. They are used to outline the arrangement of these child and parent entities.

4. What is a subtype discriminator? Give an example of its use.

This discriminator is an attribute in the entity supertype that determines each subtype. The entered values for a subtype discriminator relate to a specific subtype.

Example: supertype - BOOKS, subtypes - FICTION, NONFICTION

Subtype discriminator = BOOKS.BOOK_TYPE

In this case, the discriminator BOOK_TYPE will determine the subtypes. Each subtype will then in turn have unique attributes.

5. What is an overlapping subtype? Give an example.

Overlapping subtypes exist when entity instances are not restricted to one subtype. If a business rule allows a member of the supertype to be members of multiple subtypes, there are overlapping subtypes.

Example: supertype - BOOKS, subtypes - ACTION, ADVENTURE, ROMANCE

Subtype discriminator - BOOKS.BOOK_TYPE

If the BOOK_TYPE is allowed to be a member of more than one of the subtypes, they become overlapping.

6. What is the difference between partial completeness and total completeness?

Completeness indicates which occurrences in the entity supertype will occur in which subtypes. Partial completeness indicates that each occurrence in the supertype does not have to be a member of a subtype. In contrast, total completeness indicates every occurrence must be a member of one of the subtypes.

7. What is an entity cluster, and what advantages are derived from its use?

This entity serves as a temporary entity used to represent multiple entities and relationships. It is formed by combining related entities into one. It is considered temporary or abstract as it is not actually part of the final ERD. The benefit from forming these entities is to minimize entities in ERD drawings that can get crowded in complex designs.

8. What primary key characteristics are considered desirable? Explain why each characteristic is considered desirable.

Unique Values - this guarantees the primary key represents a unique value.

Nonintelligent - this means the value does not have imbedded semantic value. The user should not be able to relate the value to the attribute name semantically.

No change over time - this is important because the key will not change over time. In correlation with nonintelligence, it is important to select PKs that are not subject to change.

Preferably single-attribute - whenever possible it is good practice to have a primary key with only one attribute. Multi-attribute can make complex design ever more complex and should only be used when absolutely necessary.

Preferably numeric - unique numeric values are simply easier to manage and work with in a database. They can be assigned an incremented easily, often by an internal function of the DBMS.

Security complaint - avoiding information as part of a PK that may be a security risk is just good practice and common sense.

9. Under what circumstances would composite primary keys be appropriate?

Composite primary keys can be useful as identifiers of composite entities where each primary key combination is allowed only once in the M:N relationship. They are also useful as identifiers of weak identities, where the weak entity has a strong relationship with the parent entity.

10. What is a surrogate primary key, and when would you use one?

Surrogate primary keys are created when there is no evident or natural primary key to select. These keys are generated to create a unique identifier. This avoids the use of complex composite primary keys.

11. When implementing a 1:1 relationship, where should you place the foreign key if one side is mandatory and one side is optional? Should the foreign key be mandatory or optional?

The FK is placed on the optional side and is mandatory.