DBMS-II (Theory)

Paper Code: 
24CBCA405
Credits: 
04
Periods/week: 
04
Max. Marks: 
100.00
Objective: 

The course will enable the students to

  1. Learn various advance concepts of Transaction Processing Concurrency and Recovery.
  2.  Applying SQL to design and manipulate databases.

 

Course Outcomes: 

Course

Learning Outcome

(at course     level)

Learning and teaching strategies

Assessment Strategies

Course

 Code

Course

Title

24CBCA

405

DBMS-II

(Theory)

 

CO223. Analyse ACID properties of transactions and their implications on system correctness and performance.

CO224. Evaluate concurrency control techniques and its role in maintaining the database’s integrity.

CO225. Exploring the Database recovery and security mechanisms.

CO226. Analyzing Emerging Databases and exploring their architectures

CO227. Implement different types of DDL and DML statements.

CO228. Contribute effectively in course- specific interaction.

Approach in teaching: Interactive Lectures, Discussion, Reading assignments, Demonstration.

 

Learning activities for the students: Self learning assignments, Effective questions, Seminar presentation, Giving tasks.

Class test, Semester end examinations, Quiz, Assignments, Presentation.

 

 

12.00
Unit I: 

Transaction Processing

Operations of Transaction, ACID Properties of Transaction, Atomicity, Consistency, Isolation, Durability, States of Transaction, Active State, Partially Committed, Committed, Failed State, Aborted, Transaction Schedule, Serial Schedule, Non-serial Schedule, Serializable Schedule, Serializability, Conflict Serializable Schedule, Conflicting Operations, Conflict Equivalent, View Serializability, View Equivalent

 

12.00
Unit II: 

Concurrency Control                                                                                                         

Problems of Concurrency Control, Lost Update Problem, Dirty Read, Inconsistent Retrievals Problem, Concurrency Control Protocol, Lock-Based Protocol, Types of Lock Protocols, Simplistic Lock Protocol, Pre-claiming Lock Protocol, Two-Phase Locking (2PL), Strict Two-Phase Locking (Strict-2PL), Timestamp Ordering Protocol, Validation Based Protocol, Thomas Write Rule, Multiple Granularity

 

12.00
Unit III: 

Database Recovery & Security Database Recovery:

Concepts, Recovery outline, Caching of Disk blocks, Write ahead logging, Steal/No Steal and Force/No Force, Checkpoints in the System Log and Fuzzy Check pointing, Transaction Rollback. Recovery Techniques Based on Deferred Update and on Immediate Update.

Database Integrity and Security: Domain constraints, Referential Integrity, Introduction to Assertions, Security violation and Authorization.

 

12.00
Unit IV: 

Emerging Databases Cloud-Based Database Solutions:

Introduction, Database Types, benefits, Security & Scalability Distributed & Parallel Databases: Introduction, DDBMS & PDBMS Architecture, Design, Fragmentation, Allocation and replication of fragments, I/O Parallelism (Data Partitioning) Object Oriented Database (OODBMS): Introduction, OODBMS Model, Object Identity Introduction to XML Database and Multimedia Database

12.00
Unit V: 

Advanced SQL

Create Tables with constraints, Create, Open, and Remove Databases.

SQL Functions- String, Maths, Date & Time, DML Commands: Group By with having Clause, Queries and Sub-queries, Cartesian Products, Equi Joins, DDL Commands: Alter Table, Drop Table

Introduction to NOSQL: Features, Advantages, Disadvantages, SQL Vs NoSQL, Types of NOSQL Database

 

ESSENTIAL READINGS: 
  1. R. Elmasri and S. B. Navathe, “Fundamentals of Database Systems”, Addison Wesley, 7th Edition, 2015
  2. Sumita Arora, “Informatics Practices”, Dhanpat Rai & Co., 2020

 

REFERENCES: 

Suggested READINGS: 

  1. Abraham Silberschatz, Henry Korth, S. Sudarshan, “Database Systems Concepts”, 7th Edition, McGraw Hill International Edition, 2019
  2. James R. Groff & Paul N. Weinberg, “The Complete Reference SQL”, McGraw Hill Education, 3rd  Edition, 2017
  3. Alexis Leon & Mathews Leon, “SQL: A Complete Reference”, Tata McGraw Hill, 6th Edition, 2008

e -RESOURCES:

  1. https://www.w3schools.com/mysql/mysql_sql.asp
  2. https://www.slideshare.net/search/slideshow?searchfrom=header&q=mysql
  3. https://www.slideshare.net/search/slideshow?searchfrom=header&q=transaction+processing&ud=any&ft=all&lang=**&sort=
  4. https://www.slideshare.net/search/slideshow?searchfrom=header&q=database+recovery&ud=any&ft=all&lang=**&sort=
  5. https://www.slideshare.net/search/slideshow?searchfrom=header&q=rdbms&ud=any&ft=all&lang=**&sort=
  6. https://www.slideshare.net/search/slideshow?searchfrom=header&q=relational+database+management+systems&ud=any&ft=all&lang=**&sort=

JOURNALS:

  1. https://www.elsevier.com/books/transaction-processing/gray/978-0-08-051955-5
  2. https://www.elsevier.com/books/principles-of-transaction-processing/bernstein/978-1-55860-623-4
  3. https://www.elsevier.com/books/sql-clearly-explained/harrington/978-1-55860-876-4
  4. https://www.elsevier.com/books/concurrency-control-in-distributed-database-systems/cellary/978-0-444-70409-2
  5. https://www.elsevier.com/books/principles-of-transaction-processing/bernstein/978-1-55860-623-4
  6. https://www.elsevier.com/books/relational-database-design-and-implementation/harrington/978-0-12-804399-8
  7. https://www.elsevier.com/books/database/oneil/978-1-4831-8404-3
  8. https://www.elsevier.com/books/database-management-systems/gorman/978-0-7506-0135-1

 

Academic Year: 
2024-25
  • Printer-friendly version