Database Technology Course

Lesson 1 • Database Management System Overview

Introduces DBMS software, its components, and the services it provides. Connects system architecture to practical data operations.

Lesson 2 • Types of Database Models

Surveys hierarchical, network, relational, and NoSQL models. Students identify the appropriate model for a given data scenario.

Lesson 3 • Database Architecture and Abstraction

Explains the three-schema architecture and data independence levels. Grounds students in how physical storage separates from logical design.

Lesson 1 • Integrity Constraints and Rules

Introduces entity, referential, and domain integrity constraints. Students apply constraints to prevent invalid data entry.

Lesson 2 • Relational Algebra Fundamentals

Presents select, project, join, and set operations as the theoretical basis for queries. Prepares students for SQL query construction.

Lesson 3 • Tables, Rows, and Columns

Defines relations as tables with attributes and tuples. Connects formal relational terminology to practical table design.

Lesson 4 • Relationships and Foreign Keys

Explains one-to-one, one-to-many, and many-to-many relationships using foreign keys. Students link tables while maintaining referential integrity.

Lesson 1 • Enhanced ER Concepts

Covers specialization, generalization, and aggregation for complex domains. Students extend basic ER models with inheritance hierarchies.

Lesson 2 • Mapping ER Diagrams to Tables

Converts ER diagrams into relational schemas following systematic mapping rules. Bridges conceptual design and physical table creation.

Lesson 3 • Cardinality and Participation

Defines cardinality ratios and total vs. partial participation constraints. Students annotate diagrams to reflect real business rules.

Lesson 4 • Weak Entities and Identifying Relationships

Explains weak entities that depend on owner entities for identification. Students model dependent data structures correctly.

Lesson 1 • SQL Basics and DDL Statements

Covers CREATE, ALTER, and DROP statements for defining database objects. Students build and modify table structures using standard SQL.

Lesson 2 • Aggregate Functions and Grouping

Uses COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING. Students summarize large datasets with grouped calculations.

Lesson 3 • Joins and Multi-Table Queries

Applies INNER, LEFT, RIGHT, and FULL joins to combine related tables. Students retrieve data spanning multiple related tables.

Lesson 4 • SELECT Queries and Filtering

Teaches SELECT, WHERE, ORDER BY, and LIMIT clauses for data retrieval. Students extract precise result sets from single tables.

Lesson 5 • Subqueries and Set Operations

Introduces correlated and non-correlated subqueries plus UNION and INTERSECT. Students compose complex queries from simpler building blocks.

Lesson 1 • Third Normal Form and BCNF

Removes transitive dependencies for 3NF and addresses BCNF edge cases. Students achieve clean, anomaly-free table designs.

Lesson 2 • Higher Normal Forms

Introduces 4NF and 5NF for multi-valued and join dependencies. Students recognize when higher normalization adds value.

Lesson 3 • Functional Dependencies

Defines functional dependencies as the mathematical basis for normalization. Students identify dependencies within a given relation.

Lesson 4 • Normalization in Practice

Applies normalization rules to realistic business schemas end-to-end. Students balance normalization with performance requirements.

Lesson 1 • Transaction Control Language

Covers COMMIT, ROLLBACK, and SAVEPOINT statements for managing transactions. Students write transaction blocks that handle errors gracefully.

Lesson 2 • Optimistic Concurrency and MVCC

Introduces optimistic locking and multiversion concurrency control as lock-free alternatives. Students compare locking and MVCC trade-offs.

Lesson 3 • Isolation Levels and Locking

Maps SQL isolation levels to the anomalies they prevent and explains locking strategies. Students select appropriate isolation levels for workloads.

Lesson 4 • Transactions and ACID Properties

Defines transactions and the four ACID properties that guarantee reliability. Students explain why each property matters in production systems.

Lesson 1 • B-Tree and Hash Index Structures

Covers B-tree and hash index internals and their optimal query patterns. Students match index types to query workload characteristics.

Lesson 2 • Composite and Covering Indexes

Designs multi-column indexes and covering indexes to eliminate table lookups. Students apply column ordering rules for composite indexes.

Lesson 3 • Index Fundamentals

Explains how indexes accelerate data retrieval and their storage trade-offs. Students identify columns that benefit most from indexing.

Lesson 4 • Query Execution Plans

Reads EXPLAIN output to understand how the optimizer executes queries. Students identify full scans, index scans, and join algorithms.

Lesson 1 • Backup and Recovery Strategies

Defines full, incremental, and differential backup types and recovery objectives. Students design backup schedules meeting recovery time requirements.

Lesson 2 • Performance Monitoring and Maintenance

Uses system metrics, wait statistics, and maintenance tasks to sustain performance. Students establish proactive monitoring routines.

Lesson 3 • Authentication and Authorization

Covers user account creation, password policies, and privilege assignment. Students grant and revoke permissions following least-privilege principles.

Lesson 4 • Auditing and Compliance Monitoring

Implements audit logging to track data access and schema changes. Students configure audit trails that satisfy regulatory requirements.