Advanced Database Management System

Credit: 3

Objective

  • To understand the basic concepts and terminology related to DBMS and Relational Database Design

  • To the design and implement Distributed Databases.

  • To understand advanced DBMS techniques to construct tables and write effective queries, forms, and reports

 

Unit I

Formal review of relational database and FDs Implication, Closure, its correctness

 

Unit II

3NF and BCNF, Decomposition and synthesis approaches, Review of SQL99, Basics of query processing, external sorting, file scans

 

Unit III

Processing of joins, materialized vs. pipelined processing, query transformation rules, DB transactions, ACID properties, interleaved executions, schedules, serialisability

 

Unit IV

Correctness of interleaved execution, Locking and management of locks, 2PL, deadlocks, multiple level granularity, CC on B+ trees, Optimistic CC

 

Unit V

T/O based techniques, Multiversion approaches, Comparison of CC methods, dynamic databases, Failure classification, recovery algorithm, XML and relational databases

 

Outcome

  • Exposure for students to write complex queries including full outer joins, self-join, sub queries, and set theoretic queries.

  • Knowhow of the file organization, Query Optimization, Transaction management, and database administration techniques

 

Text Books

  1. R. Ramakrishnan, J. Gehrke, Database Management Systems, McGraw Hill, 2004

  2. A. Silberschatz, H. Korth, S. Sudarshan, Database system concepts, 5/e, McGraw Hill, 2008.

 

Reference Books

  1. K. V. Iyer, Lecture notes available as PDF file for classroom use.