Database Design and Modeling

  1. Logical Database Design
    1. Relational Model Foundations
      1. Theoretical Background
        1. Mathematical Foundations
          1. Set Theory Basis
            1. Relational Algebra
            2. Core Concepts
              1. Relations and Tables
                1. Tuples and Rows
                  1. Attributes and Columns
                    1. Domains
                      1. Schema vs Instance
                      2. Relational Model Properties
                        1. Uniqueness of Tuples
                          1. Order Independence
                            1. Atomic Values
                              1. Null Value Handling
                              2. Relational Algebra Operations
                                1. Selection
                                  1. Projection
                                    1. Union
                                      1. Intersection
                                        1. Difference
                                          1. Cartesian Product
                                            1. Join Operations
                                          2. ER to Relational Mapping
                                            1. Mapping Strong Entities
                                              1. Creating Tables
                                                1. Attribute Mapping
                                                  1. Primary Key Assignment
                                                  2. Mapping Weak Entities
                                                    1. Including Owner Keys
                                                      1. Composite Primary Keys
                                                        1. Foreign Key Relationships
                                                        2. Mapping Binary Relationships
                                                          1. One-to-One Mapping
                                                            1. Foreign Key Placement Options
                                                              1. Table Merging Considerations
                                                              2. One-to-Many Mapping
                                                                1. Foreign Key in Many Side
                                                                  1. Referential Integrity
                                                                  2. Many-to-Many Mapping
                                                                    1. Junction Tables
                                                                      1. Associative Entities
                                                                        1. Composite Keys
                                                                      2. Mapping Unary Relationships
                                                                        1. Self-Referencing Foreign Keys
                                                                          1. Recursive Relationships
                                                                            1. Hierarchical Structures
                                                                            2. Mapping Ternary Relationships
                                                                              1. Multi-Way Relationships
                                                                                1. Complex Junction Tables
                                                                                2. Mapping Multi-Valued Attributes
                                                                                  1. Separate Tables
                                                                                    1. Normalization Requirements
                                                                                    2. Mapping Specialization Hierarchies
                                                                                      1. Single Table Inheritance
                                                                                        1. All Attributes in One Table
                                                                                          1. Discriminator Columns
                                                                                            1. Null Value Issues
                                                                                            2. Class Table Inheritance
                                                                                              1. Separate Tables per Class
                                                                                                1. Join Operations Required
                                                                                                2. Concrete Table Inheritance
                                                                                                  1. Tables for Leaf Classes Only
                                                                                                    1. Attribute Duplication
                                                                                                3. Keys in Relational Model
                                                                                                  1. Superkey Concepts
                                                                                                    1. Unique Identification
                                                                                                      1. Minimal vs Non-Minimal
                                                                                                      2. Candidate Keys
                                                                                                        1. Minimal Superkeys
                                                                                                          1. Multiple Candidates
                                                                                                            1. Selection Criteria
                                                                                                            2. Primary Key Selection
                                                                                                              1. Uniqueness Requirements
                                                                                                                1. Stability Considerations
                                                                                                                  1. Simplicity Preferences
                                                                                                                  2. Alternate Keys
                                                                                                                    1. Non-Primary Candidates
                                                                                                                      1. Unique Constraints
                                                                                                                      2. Foreign Keys
                                                                                                                        1. Referential Relationships
                                                                                                                          1. Cascade Options
                                                                                                                          2. Composite Keys
                                                                                                                            1. Multi-Attribute Keys
                                                                                                                              1. When to Use
                                                                                                                                1. Complexity Considerations
                                                                                                                                2. Surrogate Keys
                                                                                                                                  1. Artificial Identifiers
                                                                                                                                    1. System-Generated Values
                                                                                                                                      1. Advantages and Disadvantages
                                                                                                                                    2. Integrity Constraints
                                                                                                                                      1. Entity Integrity
                                                                                                                                        1. Primary Key Constraints
                                                                                                                                          1. Non-Null Requirements
                                                                                                                                            1. Uniqueness Enforcement
                                                                                                                                            2. Referential Integrity
                                                                                                                                              1. Foreign Key Constraints
                                                                                                                                                1. Parent-Child Relationships
                                                                                                                                                  1. Cascade Rules
                                                                                                                                                    1. CASCADE DELETE
                                                                                                                                                      1. CASCADE UPDATE
                                                                                                                                                        1. SET NULL
                                                                                                                                                          1. SET DEFAULT
                                                                                                                                                            1. RESTRICT
                                                                                                                                                          2. Domain Integrity
                                                                                                                                                            1. Data Type Constraints
                                                                                                                                                              1. Value Range Restrictions
                                                                                                                                                                1. Format Validations
                                                                                                                                                                2. User-Defined Constraints
                                                                                                                                                                  1. CHECK Constraints
                                                                                                                                                                    1. Business Rule Enforcement
                                                                                                                                                                      1. Complex Validations
                                                                                                                                                                      2. Semantic Integrity
                                                                                                                                                                        1. Business Logic Rules
                                                                                                                                                                          1. Cross-Table Constraints
                                                                                                                                                                            1. Temporal Constraints

                                                                                                                                                                        Previous

                                                                                                                                                                        3. Conceptual Data Modeling

                                                                                                                                                                        Go to top

                                                                                                                                                                        Next

                                                                                                                                                                        5. Normalization Theory and Practice