Database Management Systems-II Notes
Subject Overview
Database Management Systems-II builds on DBMS-I with transaction management and concurrency control, storage/
Subject at a glance
4
Units
38
Topics
3
Credits
4
Infographics
Unit-wise Syllabus
4 units — open any unit for detailed topic-wise notes, diagrams and important exam questions
- ACID properties
- transactions and schedules
- concurrent execution
- lock-based concurrency control
- performance of locking
- transaction support in SQL
- crash recovery
- 2PL
- serializability and recoverability
- lock management
- deadlock handling
Atomicity
All or nothing
Consistency
Valid state before and after
Isolation
Transactions do not interfere
Durability
Committed data survives failures
Study guide
Transactions keep data correct when many users act at once. You study the ACID properties, schedules, concurrent execution, lock-based concurrency control, two-phase locking, serialisability and recoverability, lock management, deadlock handling, transaction support in SQL and crash recovery. Learn ACID with a bank-transfer example; it makes every property clear.
- Data on external storage
- file organizations and indexing
- index data structures
- comparison of file organizations
- index selection guidelines
- database security introduction
- access control
- discretionary access control
Data order
Rows stored in index order
Separate structure pointing to rows
Per table
Only one
Many
Speed
Fast range queries
Fast lookups on extra columns
Cost
Slower inserts in the middle
Extra storage
Study guide
Databases must store and retrieve data efficiently and safely. You study data on external storage, file organisations and indexing, index data structures, comparison of file organisations, index selection guidelines and database security with access control and discretionary access control. Understand why an index speeds up searching but slows down updates.
- Introduction to NoSQL
- document/key-value/column-family/graph data models
- features of NoSQL document databases
- CAP theorem
- BASE vs ACID
- CRUD operations
- overview of Hadoop/MongoDB/Cassandra
- MongoDB operator implementation
Structure
Tables with fixed schema
Documents, key-value, column, graph
Scaling
Mostly vertical
Horizontal across servers
Consistency
ACID
Often BASE (eventual consistency)
Example
MySQL, PostgreSQL
MongoDB, Cassandra
Study guide
NoSQL databases handle data that does not fit neatly into tables. You study document, key-value, column-family and graph models, features of document databases, the CAP theorem, BASE versus ACID, CRUD operations and an overview of Hadoop, MongoDB and Cassandra, with MongoDB operators. Be able to say when you would choose NoSQL over SQL.
- Introduction to data warehousing
- OLAP
- data mining types
- data pre-processing
- attribute-oriented induction
- association rule mining
- frequent itemset mining
- the Apriori algorithm
- classification process
- decision tree induction
- attribute selection measures
- 1Count items
Find frequent 1-itemsets above min support
- 2Join
Combine frequent itemsets into larger candidates
- 3Prune
Drop candidates with an infrequent subset
- 4Repeat
Until no new frequent itemsets
- 5Generate rules
Keep rules above min confidence
Study guide
Organisations analyse historical data to find patterns. You study data warehousing and OLAP, types of data mining, preprocessing, attribute-oriented induction, association rules, frequent itemsets, the Apriori algorithm, the classification process, decision tree induction and attribute selection measures. Work through an Apriori example by hand with a small transaction table.
Why Database Management Systems-II Matters
Real systems depend on transactions, indexes, NoSQL and analytics. Understanding these separates a developer who only writes queries from one who can design reliable, scalable data systems, and MongoDB and data mining skills are valued in modern data roles.
How to Prepare for Database Management Systems-II
- Learn ACID, serialisability and 2PL with small schedule examples.
- Practise the Apriori algorithm and decision tree construction numerically.
- Compare SQL and NoSQL in a short table covering structure, scaling and consistency.
- Write MongoDB queries for every CRUD operation.
Database Management Systems-II: Frequently Asked Questions
What are the ACID properties?
Atomicity, Consistency, Isolation and Durability: the guarantees that make a database transaction reliable.
What is the CAP theorem?
It states that a distributed data store cannot simultaneously guarantee Consistency, Availability and Partition tolerance; at most two of the three can be fully provided.