Advanced Database Management System

Course Code: MCA312

Course Title: Advanced Database Management System (4 Credits)



Course Contents


Unit-1: Comparison between different databases: Significance of Databases, Database System Applications, Advantages and Disadvantages of different Database Management systems, Comparison between DBMS, RDBMS, Distributed and Centralized DB.


Unit-2: RDBMS and SQL: Relational Query Languages, The SQL Query Language, Querying Multiple Relations, Creating Relations in SQL, Destroying and Altering Relations, Adding and Deleting Tuples, Integrity Constraints (ICs), Primary and Candidate Keys in SQL, Foreign Keys, Referential Integrity in SQL, Enforcing Referential Integrity,  Categories of SQL Commands, Data Definition, Data Manipulation Statements: SELECT - The Basic Form Subqueries, Functions, GROUP BY Feature, Updating the Database, Data Definition Facilities, Views, Embedded SQL *, Declaring Variables and Exceptions, Embedding SQL Statements, Transaction Processing, Consistency and Isolation, Atomicity and Durability, Dynamic SQL.


Unit-3: Normalization: Functional Dependency, Anomalies in a Database, The normalization process: Conversion to first normal form, Conversion to second normal form, Conversion to third normal form, The boyce-code normal form(BCNF), Fourth Normal form and fifth normal form, normalization and database design, Denormalization


Unit-4: Query Optimization: Algorithm for Executing Query Operations: External sorting, Select operation, Join operation, PROJECT and set operation, Aggregate operations, Outer join, Heuristics in Query Optimization, Semantic Query Optimization, Converting Query Tree to Query Evaluation Plan, multiquery optimization and application, Efficient and extensible algorithms for multi-query optimization, execution strategies for SQL sub queries, Query Processing for SQL Updates


Unit-5: Query Execution: Introduction to Physical-Query-Plan Operators, One-Pass Algorithms for Database, Operations, Nested-Loop Joins, Two-Pass Algorithms Based on Sorting, Two-Pass, Algorithms Based on Hashing, Index-Based Algorithms, Buffer Management, Parallel Algorithms for Relational Operations, Using Heuristics in Query Optimization, Basic Algorithms for Executing Query Operations.


Unit-6: Adaptive Query Processing and Query Evaluation: Query processing mechanism: eddy , eddy  architecture , how eddy allows for extreme flexibility , properties of query processing algorithms, why adaptive query processing is needed, , where it is most appropriately used, Hardware and Workload Complexity , User Interface Complexity, Data Complexity, Synchronization Barriers in query processing, Robust Query Processing through Progressive Optimization. query evaluation techniques for large databases, Query evaluation plans


Unit -7 Concurrency Control Serializability: Enforcing,  Serializability by Locks, Locking Systems With Several, Lock Modes, Architecture for a Locking Scheduler Managing Hierarchies of Database Elements, Concurrency Control by Timestamps, Concurrency Control by Validation, Database recovery management


Unit – 8   Transaction processing: Introduction of transaction processing, advantages and disadvantages of transaction processing system, online transaction processing system, serializability and recoverability, view serializability, resolving deadlock, distributed locking. Transaction management in multi-database system, long duration transaction, high-performance transaction system.


Unit –9: Parallel Database Architectures for parallel databases: Parallel query evaluation, Parallelizing individual operations, I/O Parallelism, Interquery Parallelism, Intraquery Parallelism, Intraoperation Parallelism, Interoperation Parallelism, Design of Parallel Systems.


Unit –10:  Object Oriented DBMS Overview of object: oriented paradigm, OODBMS architectural approaches, Object identity, procedures and encapsulation  , Object oriented data model: relationship ,identifiers, Basic OODBMS terminology,  Inheritance , Basic interface and class structure, Type hierarchies and inheritance, Type extents and persistent programming languages, OODBMS storage issues.


Unit –11: DDB: Distributed Database Introduction of DDB, DDBMS architectures, Homogeneous and Heterogeneous databases, Distributed data storage, Advantages of Data Distribution, Disadvantages of Data Distribution Distributed transactions, Commit protocols, Availability, Concurrency control & recovery in distributed databases, Directory systems, Data Replication, Data Fragmentation. Distributed database transparency features, distribution transparency.


Unit-12: Object Relational and Extended Relational Databases: design techniques used in RDBMS, extension techniques in RDBMS, standards for OODBMS

products and applications: ODMG-93 standards, ODMG Smalltalk binding, SQL3, Nested relations and collections, Storage and access methods , Implementation issues for extended type , Comparing RDBMS, OODBMS &ORDBMS.


Unit – 13: XML Query processing: XML query languages: XML-QL, Lorel, Quilt, XQL, XQuery, and Approaches for XML query processing, Query processing on relational structure and storage schema, XML database management system.


Unit-14: Database application: Active database: starburst, oracle, DB2, chimera, Applications of active database, design principles for active rules, Temporal database, special, text and multimedia database. Video database management: storage management for video, video preprocessing for content representation and indexing, image and semantic-based query processing, real time buffer management.