DBMS-II

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

Course Objectives:

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

 

25CBCA405

 

DBMS- II (Theory)

CO223.   Analyse  ACID properties of transactions and their implications on syste 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, Givingtasks.

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: 

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: 

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+processin g&ud=any&ft=all&lang=**&sort=

4. https://www.slideshare.net/search/slideshow?                                                              searchfrom=header&q=database+recovery&u d=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-

 

Academic Year: 
2025-26
  • Printer-friendly version