Database Design

Overview

Introductory Concepts - Databases and Information Systems, An example usage context, Database system concepts and architecture - Semantic Database Design :High-level conceptual modeling, ER Modeling concepts, ER Diagrams, Cardinality constraints, Higher-order relationships, Enhanced ER Model (EER), Weak-entity types, Subclasses and inheritance, Specialization and Generalization, Modeling of UNION types using categories - Relational Model, Languages and Systems - Relational algebra:Relational model concepts, Relational integrity constraints, Update operations on relations, Relational algebra model, ER to relational mapping - SQL:Data definition in SQL, Queries and update statements, Views, Integrity constraints, Specifying indexes, Embedded SQL - IBM DB2 case study:Architecture of DB2, Data definition and manipulation in DB2 - EER to Relational mapping - Database design using the relational model:Functional dependencies: Keys in a relational model, Concept of functional dependencies, Normal forms based on primary keys, Boyce-Codd Normal Forms - Further Dependencies: Multi-values dependencies and fourth normal form, Join dependencies and fifth normal form, Inclusion dependencies, Other dependencies and normal forms - Storage and Indexing Structures:Storage structures:Secondary storage devices, Buffering of blocks, File Organization, Heaps, Sorted Files, Hashing and overflow handling techniques, Dynamic hashing, Extensible hashing, Other file organizations - Indexing methods : Basic terminology, Primary indexes, Clustering index, Secondary index, Multilevel indexes, ISAM, B-trees, B+ trees, inserting and searching algorithms for B+ trees, Other indexing methods

Transaction Processing and Concurrency Control:Transaction Fundamentals (3 sessions): OLTP environments, Concurrency issues, need for transactions, Necessary properties of transactions (ACID properties), Transaction states, serializability, Serial schedules, Conflict serializability, View serializability, Recoverable and non-recoverable schedules, Cascading rollbacks, Cascadeless schedules - Concurrency control:Serialized and non-serialized schedules, Testing for serializability, Locking, Lock compatibility matrix, Locking and serializability, Deadlocks and starvation, Two-phase locking (2PL) protocol, Conservative, strict and rigorous 2PL, 2PL with lock conversions, Timestamp-ordering based protocol, Multi-versioning protocol, Multi-granularity locking, Deadlock prevention protocols, Wait-die and wound-wait schemes, Time-out based schemes, Deadlock recovery, Nested transactions - Database recovery techniques:Recovery concepts, Deferred updates technique, Immediate update technique, Shadow paging, ARIES recovery algorithm

Query Processing and Optimization:Translating SQL into relational algebra, Basic query operations, Heuristics in query optimization, Selectivity and cost estimates in query optimization, Semantic query optimization - Database Security and Authorization:Discretionary access control, Mandatory access control and multi-level security, Statistical database security - Enhanced Data Models for specific applications:Active database concepts, Temporal databases, Spatial databases, multi-media - databases - Distributed databases and issues:Data fragmentation, replication and allocation in distributed databases, Types of distributed database systems Query processing in distributed databases, Concurrency control and recovery in distributed databases