| 1 |
Introduction to Database Design and Management - PostgreSQL installation, database creation, connecting via CLI and GUI, exploration basic queries. |
| 2 |
Data Models and Relational Model - Create tables, define columns and constraints, insert basic data, relational model concepts. |
| 3 |
Relational Algebra and SQL Mapping – Implement selection, projection, joins, and set operations in SQL. |
| 4 |
ER Diagram Design and Enhanced ER (EER) –Design ER and EER diagrams for a scenario (entities, relationships, inheritance) |
| 5 |
SQL Basics – DDL and DML – Create tables with constraints, insert/update/delete data |
| 6 |
Advanced SQL – Multi-table Queries – Practice joins, subqueries, and filtering. |
| 7 |
MIDTERM |
| 8 |
Advanced SQL – Aggregation, Grouping, and CTEs. |
| 9 |
Normalization and Design Principles – Apply 1NF, 2NF, 3NF, BCNF; redesign unnormalized tables. |
| 10 |
Indexing and Query Optimization – Create indexes and analyze query performance with EXPLAIN. |
| 11 |
Views, Functions, Stored Procedures and Triggers – Implement views, functions, stored procedures and triggers for automation. |
| 12 |
Transaction Management and Concurrency – ACID properties, practice transactions, isolation levels, and handling deadlock, concurrency control |
| 13 |
Backup, Recovery, and Security – Backup/restore databases, manage users, roles, and privileges. |
| 14 |
Project Presentations - Students present their PostgreSQL database projects. |
