Database Design and Modeling

4.

Logical Database Design

4.1.

Relational Model Foundations

4.1.1.

Theoretical Background

4.1.1.1.

Mathematical Foundations

4.1.1.2.

Set Theory Basis

4.1.1.3.

Relational Algebra

4.1.2.

4.1.2.1.

Relations and Tables

4.1.2.2.

Tuples and Rows

4.1.2.3.

Attributes and Columns

4.1.2.4.

4.1.2.5.

Schema vs Instance

4.1.3.

Relational Model Properties

4.1.3.1.

Uniqueness of Tuples

4.1.3.2.

Order Independence

4.1.3.3.

4.1.3.4.

Null Value Handling

4.1.4.

Relational Algebra Operations

4.1.4.1.

4.1.4.2.

4.1.4.3.

4.1.4.4.

4.1.4.5.

4.1.4.6.

Cartesian Product

4.1.4.7.

Join Operations

4.2.

ER to Relational Mapping

4.2.1.

Mapping Strong Entities

4.2.1.1.

Creating Tables

4.2.1.2.

Attribute Mapping

4.2.1.3.

Primary Key Assignment

4.2.2.

Mapping Weak Entities

4.2.2.1.

Including Owner Keys

4.2.2.2.

Composite Primary Keys

4.2.2.3.

Foreign Key Relationships

4.2.3.

Mapping Binary Relationships

4.2.3.1.

One-to-One Mapping

4.2.3.1.1.

Foreign Key Placement Options

4.2.3.1.2.

Table Merging Considerations

4.2.3.2.

One-to-Many Mapping

4.2.3.2.1.

Foreign Key in Many Side

4.2.3.2.2.

Referential Integrity

4.2.3.3.

Many-to-Many Mapping

4.2.3.3.1.

Junction Tables

4.2.3.3.2.

Associative Entities

4.2.3.3.3.

4.2.4.

Mapping Unary Relationships

4.2.4.1.

Self-Referencing Foreign Keys

4.2.4.2.

Recursive Relationships

4.2.4.3.

Hierarchical Structures

4.2.5.

Mapping Ternary Relationships

4.2.5.1.

Multi-Way Relationships

4.2.5.2.

Complex Junction Tables

4.2.6.

Mapping Multi-Valued Attributes

4.2.6.1.

Separate Tables

4.2.6.2.

Foreign Key Links

4.2.6.3.

Normalization Requirements

4.2.7.

Mapping Specialization Hierarchies

4.2.7.1.

Single Table Inheritance

4.2.7.1.1.

All Attributes in One Table

4.2.7.1.2.

Discriminator Columns

4.2.7.1.3.

Null Value Issues

4.2.7.2.

Class Table Inheritance

4.2.7.2.1.

Separate Tables per Class

4.2.7.2.2.

Join Operations Required

4.2.7.3.

Concrete Table Inheritance

4.2.7.3.1.

Tables for Leaf Classes Only

4.2.7.3.2.

Attribute Duplication

4.3.

Keys in Relational Model

4.3.1.

Superkey Concepts

4.3.1.1.

Unique Identification

4.3.1.2.

Minimal vs Non-Minimal

4.3.2.

4.3.2.1.

Minimal Superkeys

4.3.2.2.

Multiple Candidates

4.3.2.3.

Selection Criteria

4.3.3.

Primary Key Selection

4.3.3.1.

Uniqueness Requirements

4.3.3.2.

Stability Considerations

4.3.3.3.

Simplicity Preferences

4.3.4.

4.3.4.1.

Non-Primary Candidates

4.3.4.2.

Unique Constraints

4.3.5.

4.3.5.1.

Referential Relationships

4.3.5.2.

Parent-Child Links

4.3.5.3.

Cascade Options

4.3.6.

4.3.6.1.

Multi-Attribute Keys

4.3.6.2.

4.3.6.3.

Complexity Considerations

4.3.7.

4.3.7.1.

Artificial Identifiers

4.3.7.2.

System-Generated Values

4.3.7.3.

Advantages and Disadvantages

4.4.

Integrity Constraints

4.4.1.

Entity Integrity

4.4.1.1.

Primary Key Constraints

4.4.1.2.

Non-Null Requirements

4.4.1.3.

Uniqueness Enforcement

4.4.2.

Referential Integrity

4.4.2.1.

Foreign Key Constraints

4.4.2.2.

Parent-Child Relationships

4.4.2.3.

4.4.2.3.1.

4.4.2.3.2.

4.4.2.3.3.

4.4.2.3.4.

4.4.2.3.5.

4.4.3.

Domain Integrity

4.4.3.1.

Data Type Constraints

4.4.3.2.

Value Range Restrictions

4.4.3.3.

Format Validations

4.4.4.

User-Defined Constraints

4.4.4.1.

CHECK Constraints

4.4.4.2.

Business Rule Enforcement

4.4.4.3.

Complex Validations

4.4.5.

Semantic Integrity

4.4.5.1.

Business Logic Rules

4.4.5.2.

Cross-Table Constraints

4.4.5.3.

Temporal Constraints

Previous

3. Conceptual Data Modeling

Go to top

Next

5. Normalization Theory and Practice