Learning Objectives
This course provides an introduction to relational database systems. The topics covered include the relational model, SQL, transactions, database design, and concepts and algorithms for building database management systems.
Learning Outcomes
Upon successful completion, the students will be able to define program-data independence, data models for database systems, database schema and database instances. He/she will able to recall Relational Algebra concepts, and use it to translate queries to Relational Algebra, identify Structure Query Language statements used in creation and manipulation of database, identify the methodology of conceptual modeling through Entity Relationship model, identify the methodology of logical model and also identify the methodology of physical model. He/she will be able to describe the differences between OODBMS, ORDBMS and RDBMS and describe the practical implications of each approach. Student will also be able to analyze and design a real database application and develop and evaluate a real database application using a database management system.
Syllabus
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.
E/R Model - Conceptual data modeling - motivation, entities, entity types, various types of attributes, relationships, relationship types, E/R diagram notation, examples. Relational Data Model - Concept of relations, schema-instance distinction, keys, referential integrity and foreign keys, relational algebra operators: selection, projection, cross product, various types of joins, division, example queries, 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.
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.
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
- Database System Concepts (Sixth Edition) Avi Silberschatz, Henry F. Korth, S. Sudarshan McGraw-Hill 2011 ISBN 978-0071325226/ 0-07-352332-1
- Database Management Systems, Third Edition Raghu Ramakrishnan and Johannes Gehrke McGraw-Hill ©2003 ISBN: 978-0072465631/ 0-07-246563-8
References
- Fundamentals of Database Systems, 7th Edition Ramez Elmasri, University of Texas at Arlington Shamkant B. Navathe Pearson India ©2011 ISBN 978-0321369574
- Database Systems: The Complete Book, (2nd Edition, 2014) Hector Garcia-Molina, Jeffrey D Ullman and Jennifer Widom Pearson India ISBN: 978-9332518674, 9-33-251867-X
Past Offerings
- Offered in Jan-May, 2023 by Koninika Pal
- Offered in Jan-May, 2022 by Koninika Pal
- Offered in Jan-May, 2021 by Sahely
- Offered in Jan-May, 2020 by Mrinal
- Offered in Jan-May, 2019 by Sahely
Course Metadata
| Item | Details |
|---|---|
| Course Title | Database Management Systems |
| Course Code | CS3020 |
| Course Credits | 3-0-0-3 |
| Course Category | PMT |
| Approved on | Senate 6 of IIT Palakkad |