Database Systems: Conceptual, Logical, and Physical Data Modeling

Comprehensive vocabulary flashcards covering conceptual ER modeling, the enhanced ER (EER) model, relational logical modeling, normalization from 1NF to 3NF, and physical database design.

Data Modeling

A design technique for capturing reality that ensures a database mirrors the real world in order to answer questions about it.

Business Rule

A statement that defines or constrains some aspect of the business, intended to assert business structure or to control and influence business behavior.

Entity Type

A collection of entities that share common properties or characteristics.

Entity Instance

A single occurrence of an entity type.

Strong Entity Type

An entity type that exists independently of other entity types and always possesses a unique identifier.

Weak Entity Type

An entity type whose existence depends on another entity type (its identifying owner) and has no independent business meaning without it.

Identifying Relationship

The relationship between a weak entity type and its owner entity type, denoted in diagrams by a dashed or double line.

Composite Attribute

An attribute that can be broken down into more detailed, meaningful component attributes.

Multivalued Attribute

An attribute that may take on more than one value for a given entity instance, indicated in diagrams by curly braces ${...}$.

Derived Attribute

An attribute whose value is not stored directly but can be calculated from related attribute values, indicated in diagrams by square brackets $[...]$.

Identifier

An attribute or combination of attributes that uniquely identifies each entity instance in an entity type.

Degree of a Relationship

The number of entity types that participate in a given relationship.

Unary Relationship (Recursive Relationship)

A relationship between the instances of a single entity type.

Binary Relationship

A relationship between the instances of two entity types, representing the most common type encountered in data modeling.

Ternary Relationship

A simultaneous relationship among the instances of three entity types.

Cardinality Constraint

A rule specifying the number of instances of entity B that can or must be associated with each instance of entity A.

Minimum Cardinality

The minimum number of instances of entity B that may be associated with each instance of entity A; zero indicates optional participation, while one indicates mandatory participation.

Maximum Cardinality

The maximum number of instances of entity B that may be associated with each instance of entity A; if "many", it is considered unlimited.

Associative Entity

An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances.

Supertype

A generic entity type that has a relationship with one or more specialized subgroups called subtypes.

Subtype

A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings.

Attribute Inheritance

The property by which subtype entities inherit the values of all attributes belonging to their supertype.

Generalization

The bottom-up process of defining a more general entity type from a set of more specialized entity types.

Specialization

The top-down process of defining one or more subtypes of a supertype and forming supertype/subtype relationships.

Total Specialization Rule

A completeness constraint requiring that each instance of a supertype must also be a member of at least one subtype, depicted with a double line.

Partial Specialization Rule

A completeness constraint specifying that an instance of a supertype does not have to belong to any subtype, depicted with a single line.

Disjoint Rule

A disjointness constraint specifying that an instance of a supertype can be a member of only one subtype at a time, denoted by the letter "d".

Overlap Rule

A disjointness constraint specifying that an instance of a supertype can simultaneously be a member of two or more subtypes, denoted by the letter "o".

Subtype Discriminator

An attribute of the supertype whose values determine the target subtype or subtypes to which an entity instance belongs.

Entity Cluster

A set of one or more entity types and associated relationships grouped into a single abstract entity type to simplify complex diagrams.

Relation

A named, two-dimensional table of data consisting of a set of named columns and an arbitrary number of unnamed rows (tuples).

Superkey

An attribute or combination of attributes that uniquely identifies each row (entity instance) in a table.

Candidate Key

A superkey that contains no redundancies, meaning no subset of its attributes can uniquely identify each row.

Primary Key

A candidate key selected to uniquely identify all other attributes' values in any given row of a relation, underlined with a solid line.

Composite Key

A primary key made up of more than one attribute, where no component of the key is permitted to contain null values.

Secondary Key

An attribute or combination of attributes used strictly for data retrieval purposes, which does not necessarily yield a unique outcome.

Foreign Key

An attribute in one relation that serves as the primary key of another relation in the same database, serving as a linking field.

Domain Constraint

An integrity rule requiring that all values appearing in a specific column of a relation must originate from the same defined domain.

Entity Integrity Rule

An integrity rule stating that every relation must have a primary key, and the primary key values must be valid, unique, and not null.

Referential Integrity Constraint

An integrity rule requiring that each foreign key value must either match a primary key value in the related relation or be null.

Well-Structured Relation

A relation that contains minimal redundancy and allows users to insert, modify, and delete rows in a table without anomalies.

Insertion Anomaly

An inconsistency experienced when attempting to store a value for one attribute, but unable to do so because the value of another attribute is unknown.

Deletion Anomaly

An inconsistency experienced when a value for an attribute that should be kept is unexpectedly lost when a value for another attribute is deleted.

Modification Anomaly

An inconsistency experienced when changes to multiple rows of a table are needed to effect an update to a single attribute value.

Normalization

The process of successively reducing relations with anomalies to produce smaller, well-structured relations by eliminating data redundancy.

Functional Dependency

A relationship between attributes ($A \rightarrow B$) such that for every instance of $A$, that value of $A$ uniquely determines the value of $B$.

Determinant

The attribute or set of attributes located on the left-hand side of the arrow in a functional dependency.

First Normal Form (1NF)

A state of a relation in which all repeating groups are removed (each row/column intersection contains single values) and a primary key is defined.

Partial Functional Dependency

A functional dependency in which one or more nonkey attributes depend on only a part, rather than the entirety, of a composite primary key.

Second Normal Form (2NF)

A state of a relation that is in 1NF and contains no partial functional dependencies.

Transitive Dependency

A functional dependency between two or more nonkey attributes that are not part of any primary or candidate key.

Third Normal Form (3NF)

A state of a relation that is in 2NF and contains no transitive dependencies.

Physical Modeling

The phase of database design that translates the logical description of data into technical specifications for storing and retrieving data.

Field

The smallest unit of application data recognized and managed within a physical database.

Range Control

A data integrity constraint that specifies the upper and lower bounds for acceptable values in a field.

Default Value

An integrity control specifying the value that a field will automatically take if no data is explicitly entered.

Denormalization

The process of transforming normalized relations into unnormalized physical record specifications to improve query retrieval speed by reducing table joins.

Horizontal Partitioning

Distributing the rows of a table across multiple separate files, where each fragment contains the same attributes but different rows.

Vertical Partitioning

Distributing the columns of a table across multiple separate files, where each fragment must repeat the primary key column.

Data Replication

Purposely storing copies of the same data in multiple database locations to reduce contention and improve access speed, at the cost of data duplication.

Sequential File Organization

A storage organization structure where physical records are stored in order according to a primary key value.

Indexed File Organization

A storage organization structure where records are stored sequentially or nonsequentially and an index is created to locate individual records.

Hashed File Organization

A storage organization structure in which the physical storage address of each record is determined directly by a hashing algorithm.