Database Design

IIT Madras Course , Prof. D. Janaki Ram

593 students enrolled


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

Lecture 34: Case Study ORACLE and Microsoft Access

Up Next
You can skip ad in
      • 2x
      • 1.5x
      • 1x
      • 0.5x
      • 0.25x
        EMBED LINK
        Enter password to view
        Please enter valid password!
        4.0 (3 Ratings)

        Lecture Details

        Lecture Series on Database Management System by Dr. S. Srinath, IIIT Bangalore. For more details on NPTEL visit




        3 Rates

        Comments Added Successfully!
        Please Enter Comments
        Please Enter CAPTCHA
        Invalid CAPTCHA
        Please Login and Submit Your Comment