Relational Database Modeling Course

Lesson 1 • Core Relational Concepts

Introduces relations, tuples, attributes, and domains as formal constructs. Connects mathematical set theory to practical database design.

Lesson 2 • Keys and Identifiers

Explains how keys uniquely identify rows and enforce data integrity. Provides the basis for linking tables in later chapters.

Lesson 3 • Relational Database Management Systems

Surveys RDBMS architecture and the role of the database engine. Helps students understand the environment where models are deployed.

Lesson 4 • What Is a Relational Database

Defines relational databases and their role in structured data management. Establishes vocabulary used throughout the course.

Lesson 1 • ER Notation Styles

Compares Chen, Crow's Foot, and UML class diagram notations. Enables students to read and produce diagrams in any industry-standard format.

Lesson 2 • Entities and Attributes

Defines entities, entity types, and their attributes in ER notation. Grounds abstract concepts in business domain examples.

Lesson 3 • Weak Entities and Identifying Relationships

Addresses entities that depend on a parent for identification. Prepares students to model hierarchical and dependent data structures.

Lesson 4 • Relationships and Cardinality

Models associations between entities using cardinality and participation constraints. Directly shapes how tables are linked in the logical model.

Lesson 1 • Functional Dependencies

Introduces functional dependencies as the mathematical basis for normalisation. Understanding FDs is prerequisite to applying any normal form.

Lesson 2 • Third Normal Form and BCNF

Eliminates transitive dependencies and addresses overlapping candidate keys. Produces schemas that are free of most practical redundancy issues.

Lesson 3 • First and Second Normal Forms

Removes repeating groups and partial dependencies to reach 2NF. Demonstrates how anomalies arise and how decomposition resolves them.

Lesson 4 • Fourth and Fifth Normal Forms

Addresses multi-valued dependencies and join dependencies for advanced decomposition. Applies to complex schemas with independent multi-valued facts.

Lesson 1 • Schema Documentation Standards

Produces data dictionaries and schema documentation for team communication. Establishes professional documentation habits used in enterprise projects.

Lesson 2 • Defining Constraints in the Schema

Specifies NOT NULL, UNIQUE, CHECK, and DEFAULT constraints at the column and table level. Enforces business rules directly in the schema definition.

Lesson 3 • Resolving Many-to-Many Relationships

Introduces junction tables to implement many-to-many associations. Covers composite keys and additional attributes on junction tables.

Lesson 4 • Mapping ER Diagrams to Tables

Applies systematic rules to convert each ER construct into relational tables. Bridges the conceptual and logical modelling phases.

Lesson 1 • Referential Integrity Implementation

Implements foreign key constraints with cascading actions in the physical schema. Balances integrity enforcement against write performance.

Lesson 2 • Storage Structures and Pages

Explains how data is stored in pages, extents, and tablespaces on disk. Provides the physical foundation needed to make informed design decisions.

Lesson 3 • Table Partitioning Strategies

Divides large tables into partitions to improve query performance and manageability. Covers range, list, and hash partitioning schemes.

Lesson 4 • Physical Design Review and Tuning

Evaluates physical designs using execution plans and storage metrics. Iteratively refines indexes and partitions based on workload analysis.

Lesson 1 • Entity-Attribute-Value Pattern

Implements flexible, schema-less attribute storage within a relational model. Evaluates when EAV is appropriate and its significant trade-offs.

Lesson 2 • Modelling Many-to-Many with Attributes

Extends junction tables to capture rich relationship data beyond simple associations. Applies to enrolment, assignment, and role-based scenarios.

Lesson 3 • Temporal and Historical Data Modelling

Tracks data changes over time using valid-time and transaction-time dimensions. Enables audit trails and point-in-time queries in the schema.

Lesson 4 • Modelling Hierarchical Data

Represents tree and graph structures within relational tables using multiple strategies. Covers trade-offs in query complexity and update performance.

Lesson 1 • Scalability Concepts and Trade-offs

Defines vertical and horizontal scaling and their impact on schema design choices. Introduces the CAP theorem and consistency trade-offs.

Lesson 2 • Schema Design for High Write Throughput

Optimises schemas to minimise lock contention and write amplification. Applies append-only and event-sourcing patterns for write-intensive systems.

Lesson 3 • Replication and Read Scaling

Configures primary-replica replication to distribute read workloads. Addresses replication lag and its effect on data consistency.

Lesson 4 • Caching Layers and Schema Impact

Integrates caching strategies with relational schema design to reduce database load. Covers cache invalidation patterns tied to schema update operations.

Lesson 1 • Iterative Modelling and Review

Applies iterative design cycles with peer and stakeholder review checkpoints. Reinforces that models evolve through feedback rather than single-pass design.

Lesson 2 • Domain-Specific Modelling Case Studies

Analyses complete data models from e-commerce, healthcare, and finance domains. Exposes students to recurring patterns and domain-specific constraints.

Lesson 3 • Schema Migration and Evolution

Manages schema changes in production systems without data loss or downtime. Covers migration scripts, backward compatibility, and rollback strategies.

Lesson 4 • Capstone Project Execution

Delivers a complete data model from requirements through physical design for a chosen domain. Synthesises all course skills into a single evaluated artefact.