Database Design

In this course, students will learn about semantic data modeling and database design concepts. By the end of the semester, students are expected to have complete familiarity with all concepts mentioned in the detailed syllabus.

• Introduction to Database Systems (1 session)
  • Course introduction
  • Definition and importance of databases
  • Key elements of database environment
  • ACID Properties
  • Types of database architectures (Centralized, Client-Server, Distributed)
  • Components of Relational Database Management System (RDBMS)
• Relational Model (1 session)
  • Relations and related concepts
  • Types of keys
  • Relational operations
  • Constraints
• SQL Implementation (4 sessions)
  • Tabular Database Language (SQL)
  • DDL commands
  • DML commands
  • DCL commands
  • TCL commands
  • Subqueries
  • Types of Joins
  • View & Materialized View
• Database Design (4 sessions)
  • Semantic modeling
  • Entity, Attribute, Relationship
  • ER and EER diagrams
  • Aggregation and Generalization
  • Converting semantic models to logical design
  • Normalization (1NF, 2NF, 3NF, and BCNF)
  • Common pitfalls and solutions
• Modern SQL (1 session)
  • Output Control + Redirection
  • Window Functions
  • Nested Queries
  • Lateral Joins
  • Common Table Expressions (CTE)
  • Recursive Queries
• Relational Model Integrity (1 session)
  • Integrity
  • Assertion
  • Procedure, Function
  • Trigger
• Database Performance Optimization (2 sessions)
  • Query Execution Plan and Query Profiling
  • Clustered & Non-Clustered Indexes
  • Database Tuning
  • Data Purging
  • Data Partitioning
• Data Warehouse (1 session)
  • Concept and importance of data warehouses
  • Different data warehouse architectures
  • ETL processes (Extract, Transform, Load)
  • Designing and building Data Marts
  • Implementing Normalized and Denormalized data
  • Data mining in data warehouses
• NoSQL Databases (1 session)
  • Reasons for using NoSQL databases
  • CAP theorem
  • Key-Value, Columnar, Graph, Document and Multi-Model databases
  • Horizontal and vertical scaling
  • Database design for big data and distributed architectures
• Introduction to MongoDB and Neo4J with real-world applications (1 session)
• Introduction to Vector Databases and their applications in AI (1 session)

Note: Starting from the introduction of tabular data structures onward, SQL will be used for examples wherever necessary. Students must study SQL from the third week onward in parallel with course progress.

• Weekly practical assignments: 50% of grade
• Final exam: 50% of grade