Database Management System

Syllabus

1. Introduction (3 hours)
   1. Application and evolution of database
   2. Data abstraction (physical, logical, and view level) and data independence
   3. Schema and instances
2. Data Models (7 hours)
   1. Introduction to data models (entity-relationship, relational, object, hierarchical, network, graph data models)
   2. E-R model: entities and entity sets, attributes and keys, strong and weak entity sets, relationship and relationship sets (mapping cardinalities), specialization, generalization and aggregation
   3. Relational model: concept of relational model, key constraints, converting ER model into relational model
3. Relational Query Languages (7 hours)
   1. Relational algebra
   2. Concept of DDL, DML and DCL
   3. Overview of the SQL query language: DDL and DML queries
   4. Set operations
   5. Aggregate functions, GROUP BY, HAVING
   6. Joins and types of joins
   7. Nested sub queries
   8. Database modification (insert, update, delete)
   9. Views
   10. Triggers and stored procedures
   11. Privilege and roles management: GRANT and REVOKE statements
4. Database Constraints and Normalization (6 hours)
   1. Integrity constraints and domain constraints
   2. Assertions
   3. Functional dependencies
   4. Different normal forms (1NF, 2NF, 3NF, BCNF)
5. Query Processing and Optimization (4 hours)
   1. Query processing, optimization and evaluation
   2. Transformation of relational expressions
   3. Techniques of query optimization: cost based and heuristic optimization
   4. Query evaluation: materialization and pipelining
   5. Denormalization for performance
   6. Materialized view
   7. Performance tuning
6. File Structure and Hashing (5 hours)
   1. Disks and storage
   2. Records organizations
   3. Ordered indices
   4. B+ tree index
   5. Hashing concepts: static and dynamic hashing
7. Transaction Processing and Concurrency Control (5 hours)
   1. Transaction and transaction model, state diagram
   2. ACID properties
   3. Concurrent execution of transactions
   4. Serializability (conflict and view serializability)
   5. Lock based protocols
   6. Deadlock handling and prevention
   7. Multiple granularity
8. Crash Recovery (4 hours)
   1. Failure classification
   2. Recovery and atomicity
   3. Log-based recovery
   4. Shadow paging
   5. High availability using remote backup systems
9. Advanced Database Concepts (4 hours)
   1. Concept of object-oriented databases
   2. Distributed database model
   3. Concept of data warehousing and online analytical processing
   4. Basic concepts of NoSQL and big data

Practicals

1. Database server installation and configuration
2. DB client installation and connection to DB server, practice with SELECT on existing DB
3. Practice with DML queries: select, insert, update and delete
4. Advanced queries with joins and subqueries
5. Aggregation and grouping
6. Practice with DDL commands: create/alter/drop table, integrity constraints and views
7. Triggers and stored procedures
8. Query processing, optimization, performance tuning and database administration
9. Group project work