Data Base Course

Lesson 1 • Database Management System Overview

Introduces the DBMS as the software layer between users and data. Explains core DBMS functions and their role in data integrity.

Lesson 2 • Types of Database Models

Surveys hierarchical, network, relational, and NoSQL models. Students can select an appropriate model for a given use case.

Lesson 3 • Roles in Database Environments

Identifies stakeholders—administrators, developers, analysts, and end users—and their responsibilities. Clarifies professional expectations for each role.

Lesson 4 • What Is a Database

Defines databases, differentiates them from flat files, and explains why structured storage matters. Establishes vocabulary used throughout the course.

Lesson 1 • Keys and Constraints in ER Models

Explains primary keys, candidate keys, and foreign keys within ER modelling. Reinforces how constraints enforce data integrity at the design stage.

Lesson 2 • Translating Requirements to ER Diagrams

Applies modelling skills to realistic case studies by converting written requirements into complete ER diagrams. Bridges theory and practical design work.

Lesson 3 • Conceptual Data Modelling Basics

Introduces conceptual modelling as the first step in database design. Students learn to identify entities, attributes, and relationships from requirements.

Lesson 4 • Advanced ER Concepts

Covers specialisation, generalisation, aggregation, and ternary relationships. Prepares students to model complex business scenarios accurately.

Lesson 1 • Functional Dependencies

Introduces functional dependencies as the mathematical basis for normalisation. Students identify and document dependencies within a given schema.

Lesson 2 • Mapping ER Diagrams to Relations

Provides step-by-step rules for converting ER diagrams into relational tables. Students practise mapping all ER constructs including weak entities.

Lesson 3 • BCNF and Higher Normal Forms

Extends normalisation to Boyce-Codd, fourth, and fifth normal forms. Students evaluate when higher normalisation is beneficial versus impractical.

Lesson 4 • Normal Forms 1NF Through 3NF

Walks through first, second, and third normal forms with worked examples. Students decompose unnormalised tables into 3NF schemas.

Lesson 1 • Subqueries and Set Operations

Introduces correlated and non-correlated subqueries plus UNION, INTERSECT, and EXCEPT. Students solve complex retrieval problems using nested logic.

Lesson 2 • SQL Fundamentals and SELECT Basics

Introduces SQL syntax, data types, and simple SELECT statements. Students retrieve and filter data from single tables using WHERE and ORDER BY.

Lesson 3 • Data Manipulation Language

Covers INSERT, UPDATE, DELETE, and MERGE statements for modifying table data. Students apply DML safely using transactions and rollback strategies.

Lesson 4 • Aggregation and Grouping

Teaches aggregate functions and GROUP BY for summarising data. Students apply HAVING to filter grouped results.

Lesson 1 • Data Definition Language Essentials

Introduces CREATE, ALTER, and DROP statements for managing database objects. Students build tables with appropriate data types and column definitions.

Lesson 2 • Implementing Integrity Constraints

Covers PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints. Students enforce business rules directly within the schema definition.

Lesson 3 • Views and Virtual Tables

Teaches how to create and use views for abstraction and security. Students build updatable and read-only views to simplify complex queries.

Lesson 4 • Sequences, Defaults, and Generated Columns

Covers auto-increment mechanisms, default values, and computed columns. Students automate key generation and derived attribute storage.

Lesson 1 • Concurrency Problems and Anomalies

Identifies dirty reads, non-repeatable reads, phantom reads, and lost updates. Students recognise these anomalies in concurrent transaction scenarios.

Lesson 2 • Transaction Concepts and ACID Properties

Defines transactions and the four ACID properties with practical examples. Students explain why each property is essential for reliable data operations.

Lesson 3 • Optimistic Concurrency and MVCC

Introduces optimistic locking and multiversion concurrency control as alternatives to strict locking. Students compare MVCC behaviour with traditional lock-based approaches.

Lesson 4 • Locking and Isolation Levels

Covers shared, exclusive, and intent locks alongside the four SQL isolation levels. Students select appropriate isolation levels to balance safety and performance.

Lesson 1 • B-Tree and Hash Indexes

Explains B-tree and hash index structures in depth. Students choose between index types based on query patterns and data distribution.

Lesson 2 • Reading and Interpreting Execution Plans

Teaches how to read EXPLAIN output and identify bottlenecks such as full scans and sort spills. Students apply findings to rewrite queries or add indexes.

Lesson 3 • Physical Storage and File Organisation

Covers disk storage, pages, blocks, and heap vs. sequential file organisation. Students connect physical layout decisions to I/O cost.

Lesson 4 • Query Optimisation Techniques

Covers heuristic and cost-based optimisation, join ordering, and statistics. Students rewrite queries and adjust schemas to reduce execution cost.

Lesson 1 • Access Control and Privilege Management

Covers GRANT, REVOKE, roles, and row-level security for controlling data access. Students design a least-privilege permission model for a multi-user system.

Lesson 2 • Database Monitoring and Maintenance

Covers performance monitoring, statistics updates, index rebuilding, and routine maintenance tasks. Students establish a proactive maintenance schedule for a production database.

Lesson 3 • Authentication and Encryption

Explains user authentication methods and data encryption at rest and in transit. Students configure encrypted connections and evaluate authentication options.

Lesson 4 • Backup and Recovery Strategies

Covers full, incremental, and differential backups alongside point-in-time recovery. Students design a backup schedule that meets defined recovery objectives.