Definition of an Entity
An entity is a database object that represents a thing in the real world. Entities are expressed as nouns.
Entities can be concrete, like buildings and employees or they can be more abstract things like departments and accounts.
Loosely speaking, an entity corresponds to a relation in relational theory. When a relation is made physical, it is normally referred to as a table, though the term is also used to describe physical as well as conceptual tables.
Types of Entities
There are several different schemes for categorizing entities based on qualitatively different criteria. For purposes of this document, the following two schemes are defined:
- Major and minor entities
- Supertype and subtype entities
The following table provides definitions for these types:
| Entity Type | Definition | Example |
|---|---|---|
| Major | An entity with relatively large cardinality and degree that is updated frequently. | Order table |
| Minor | An entity with small cardinality and degree that is rarely updated. Minor entities are typically used in a single, 1:M association, and their primary key is often nonnumeric. |
Nation Code table |
| Supertype | A generic entity that is a superclass of one or more subtype entities. Supertype and subtype entities model the same real world entity at a high level. Supertypes must, by definition, have one or more reciprocal subtypes. |
Publications table |
| Subtype | A specific entity that is a disjoint subclass of one and only one supertype entity. Subtype and supertype entities model the same real world entity at a high level. Subtype entities typically have a higher degree than their supertypes, with the additional attributes describing detailed characteristics of the subtype that distinguish it from the other subtype entities of a mutual supertype. |
(all as subtypes of the supertype Publications) |
Definition of a Relationship
A relationship is an association among two or more entities or other relationships. Relationships are expressed as verbs.
Relationships among entities are described by one of three ratios:
| Relationship | Shorthand Notation |
|---|---|
| One-to-one | 1:1 |
| One-to-many | 1:M |
| Many-to-many | M:M |
Relationships as defined in the E-R model have no direct counterpart in relational theory. The closest property of relational theory to expressing what a relationship is in E-R theory is the primary key-foreign key relationship.
Relationship Theory, One-to-One Relationships, One-to-Many Relationships, and Many-to-Many Relationships describe the properties of relationships in greater detail.
Definition of Attribute
An attribute is a characteristic of an entity. Every entity has at least one attribute: its primary key (More accurately, a candidate key). Attributes are expressed as nouns qualified by adjectives that clarify their role.
An attribute plays one of three possible roles in any table:
- Primary key attributes identify the entity or relationship modeled by a table.
Primary key attributes are said to be identifier attributes because they uniquely identify an instance of an entity.
- Foreign key attributes define relationships between and among entities or among entities and relationships.
A foreign key attribute can be an identifier attribute if it is part of a composite primary key; otherwise, foreign key attributes are descriptor attributes.
- Nonkey attributes further describe the entity or relationship modeled by a table.
Nonkey attributes are said to be descriptor attributes because they specify a nonunique characteristic of an instance of an entity.
In the relational model, attributes have the same properties as they do in E-R theory.
Definition of a Derivative Attribute
So-called derivative attributes violate the rules of normalization in relational theory because they are not atomic. A derivative attribute is any attribute that can be derived by calculation from other data in the model.
The issue of derivative attributes should not concern you during the logical design phase other than knowing that they should not be modeled. Derivative attributes are an important consideration for physical database design, where they are often modeled as a means for enhancing system performance.
Note that Teradata Database offers several features like hash and join indexes, aggregate join indexes, and global temporary tables that lessen the temptation to denormalize the physical design of your base tables by using derivative attributes.