Subject Code: CS3L003	Name: Database Systems	L-T-P: 3-1-0	Credit: 4
Pre-requisite(s): None
Introduction: General introduction to database systems; Database-DBMS distinction, Approaches to building a database, Data models, Database management system, Three-schema architecture of a database, Challenges in building a DBMS, Various components of a DBMS; Entity Relationship Model: Conceptual data modeling - motivation, Entities, Entity types, Various types of attributes, Relationships, Relationship types, E/R diagram notation, Specialization and Generalization, Examples; Relational Data Model: Concept of relations, Schema-instance distinction, Keys, referential integrity and foreign keys; Relational Algebra: Selection, Projection, Cross product, Various types of joins, Division, Example queries; Introduction to Tuple relation calculus, Domain relational calculus, Converting the database specification in E/R notation to the relational schema; SQL: Introduction, Data definition in SQL, Table, key and foreign key definitions, Update behaviors, Querying in SQL, Basic select- from- where block and its semantics, Nested queries - correlated and uncorrelated, Notion of aggregation, Aggregation functions group by and having clauses, Embedded SQL; Functional Dependencies and Normal forms: Importance of a good schema design, Problems encountered with bad schema designs, Motivation for normal forms, dependency theory - functional dependencies, Armstrong's axioms for FD's, Closure of a set of FD's, Minimal covers; Definitions of 1NF, 2NF, 3NF and BCNF, Decompositions and desirable properties of them, Algorithms for 3NF and BCNF normalization; Multi-valued dependencies and 4NF, Join dependencies and definition of 5NF; Data Storage and Indexes: File organizations, Primary, Secondary index structures, Various index structures - hash-based, Dynamic hashing techniques, Multi-level indexes, B trees, B+ trees; Transaction processing and Error recovery: Concepts of transaction processing, ACID properties, Concurrency control, Locking based protocols for CC, Error recovery and logging, Undo, Redo, Undo-redo logging and recovery methods. Text Books: R. Elmasri and S. B. Navathe, Fundamentals of Database systems, Sixth Edition, Addison Wesley 2010. A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts, Sixth Edition McGraw Hill 2010. Reference Books: Ramakrishnan, Raghu, and Johannes Gehrke. Database Management Systems. 3rd ed. McGraw-Hill, 2002

Subject Code: CS3L003

Name: Database Systems

L-T-P: 3-1-0

Credit: 4

Pre-requisite(s): None

Introduction: General introduction to database systems; Database-DBMS distinction, Approaches to building a database, Data models, Database management system, Three-schema architecture of a database, Challenges in building a DBMS, Various components of a DBMS; Entity Relationship Model: Conceptual data modeling - motivation, Entities, Entity types, Various types of attributes, Relationships, Relationship types, E/R diagram notation, Specialization and Generalization, Examples; Relational Data Model: Concept of relations, Schema-instance distinction, Keys, referential integrity and foreign keys; Relational Algebra: Selection, Projection, Cross product, Various types of joins, Division, Example queries; Introduction to Tuple relation calculus, Domain relational calculus, Converting the database specification in E/R notation to the relational schema; SQL: Introduction, Data definition in SQL, Table, key and foreign key definitions, Update behaviors, Querying in SQL, Basic select- from- where block and its semantics, Nested queries - correlated and uncorrelated, Notion of aggregation, Aggregation functions group by and having clauses, Embedded SQL; Functional Dependencies and Normal forms: Importance of a good schema design, Problems encountered with bad schema designs, Motivation for normal forms, dependency theory - functional dependencies, Armstrong's axioms for FD's, Closure of a set of FD's, Minimal covers; Definitions of 1NF, 2NF, 3NF and BCNF, Decompositions and desirable properties of them, Algorithms for 3NF and BCNF normalization; Multi-valued dependencies and 4NF, Join dependencies and definition of 5NF; Data Storage and Indexes: File organizations, Primary, Secondary index structures, Various index structures - hash-based, Dynamic hashing techniques, Multi-level indexes, B trees, B+ trees; Transaction processing and Error recovery: Concepts of transaction processing, ACID properties, Concurrency control, Locking based protocols for CC, Error recovery and logging, Undo, Redo, Undo-redo logging and recovery methods.
Text Books:

R. Elmasri and S. B. Navathe, Fundamentals of Database systems, Sixth Edition, Addison Wesley 2010.
A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts, Sixth Edition McGraw Hill 2010.

Reference Books:

Ramakrishnan, Raghu, and Johannes Gehrke. Database Management Systems. 3rd ed. McGraw-Hill, 2002