11/4/25, 4:57 AM Database Management Systems - Study Guide

Database Management
🖨️ Print Systems
⬇️ Download HTML
Complete Guide to DBMS Concepts and SQL

University: Savitribai Phule Pune University (SPPU)

Course: Third Year Engineering - Computer Science & IT

Subject Code: 310242
Academic Year: 2024-2025

Unit 1: Introduction to DBMS

1.1 What is a Database?

A database is an organized collection of structured data stored electronically. A DBMS is

software that interacts with users, applications, and the database itself to capture and
analyze data.

Advantages of DBMS:

Data independence and abstraction

Reduced data redundancy

Data consistency and integrity

Concurrent access and crash recovery

Security and authorization

Reduced application development time

1.2 Three-Schema Architecture

External Level (View Level): User's view of database

Conceptual Level (Logical Level): Community view of database

Internal Level (Physical Level): Physical storage structure

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 1/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

1.3 Data Models

Hierarchical Model: Tree-like structure

Network Model: Graph structure

Relational Model: Tables with rows and columns

Object-Oriented Model: Objects with attributes and methods

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 2/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 2: Relational Model

2.1 Basic Concepts

The relational model organizes data into tables (relations) consisting of rows (tuples) and
columns (attributes).

Term Description

Relation Table with rows and columns

Tuple A single row in a table

Attribute Column in a table

Domain Set of allowed values for an attribute

Degree Number of attributes in a relation

Cardinality Number of tuples in a relation

2.2 Keys in Relational Model

Super Key: Set of attributes that uniquely identifies tuples

Candidate Key: Minimal super key

Primary Key: Chosen candidate key

Foreign Key: References primary key of another relation

Alternate Key: Candidate keys not chosen as primary

Composite Key: Primary key with multiple attributes

Example Table - Students:

StudentID (Primary Key) | Name | Email | DepartmentID (Foreign Key)
101 | John Smith | [email protected] | D01
102 | Mary Jones | [email protected] | D02

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 3/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 3: SQL - Structured Query Language

3.1 SQL Categories

DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE

DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE

DCL (Data Control Language): GRANT, REVOKE

TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3.2 DDL Commands

-- Create Table CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name
VARCHAR(50) NOT NULL, Age INT CHECK (Age >= 18), Email VARCHAR(100) UNIQUE,
DeptID INT, FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ); -- Alter
Table ALTER TABLE Students ADD COLUMN Phone VARCHAR(15); ALTER TABLE Students
MODIFY COLUMN Age INT DEFAULT 18; -- Drop Table DROP TABLE Students; --
Truncate Table TRUNCATE TABLE Students;

3.3 DML Commands

-- Insert Data INSERT INTO Students VALUES (101, 'John', 20, '[email protected]',
1); INSERT INTO Students (StudentID, Name) VALUES (102, 'Mary'); -- Update
Data UPDATE Students SET Age = 21 WHERE StudentID = 101; -- Delete Data DELETE
FROM Students WHERE StudentID = 101; -- Select Data SELECT * FROM Students;
SELECT Name, Age FROM Students WHERE Age > 18; SELECT DISTINCT DeptID FROM
Students;

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 4/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 4: Advanced SQL

4.1 Aggregate Functions

-- COUNT, SUM, AVG, MIN, MAX SELECT COUNT(*) FROM Students; SELECT AVG(Age)
FROM Students; SELECT MAX(Age), MIN(Age) FROM Students; SELECT DeptID,
COUNT(*) as StudentCount FROM Students GROUP BY DeptID; -- HAVING Clause
SELECT DeptID, AVG(Age) FROM Students GROUP BY DeptID HAVING AVG(Age) > 20;

4.2 Joins

Join Type Description

INNER JOIN Returns matching rows from both tables

LEFT JOIN All rows from left table, matching from right

RIGHT JOIN All rows from right table, matching from left

FULL OUTER JOIN All rows from both tables

CROSS JOIN Cartesian product of both tables

-- INNER JOIN SELECT S.Name, D.DeptName FROM Students S INNER JOIN Department
D ON S.DeptID = D.DeptID; -- LEFT JOIN SELECT S.Name, D.DeptName FROM Students
S LEFT JOIN Department D ON S.DeptID = D.DeptID;

4.3 Subqueries

-- Subquery in WHERE clause SELECT Name FROM Students WHERE DeptID IN (SELECT
DeptID FROM Department WHERE Location = 'Pune'); -- Correlated Subquery SELECT
S1.Name, S1.Age FROM Students S1 WHERE Age > (SELECT AVG(Age) FROM Students S2
WHERE S1.DeptID = S2.DeptID);

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 5/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 5: Normalization

5.1 Need for Normalization

Normalization is the process of organizing data to reduce redundancy and improve data
integrity.

Anomalies in Unnormalized Data:

Insertion Anomaly: Cannot insert data due to missing information

Deletion Anomaly: Deleting data causes loss of other information

Update Anomaly: Data inconsistency due to multiple updates

5.2 Normal Forms

First Normal Form (1NF):

All attributes contain atomic values

No repeating groups

Each column contains values of a single type

Second Normal Form (2NF):

Must be in 1NF

No partial dependencies (all non-key attributes fully dependent on primary key)

Third Normal Form (3NF):

Must be in 2NF

No transitive dependencies

Boyce-Codd Normal Form (BCNF):

Must be in 3NF

For every functional dependency X → Y, X must be a super key

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 6/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Example - 2NF Violation:

Student(StudentID, CourseID, StudentName, CourseName)
Problem: StudentName depends only on StudentID (partial dependency)
Solution: Split into Student(StudentID, StudentName) and Course(CourseID,
CourseName)

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 7/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 6: Transactions

6.1 ACID Properties

Property Description

Atomicity All or nothing - transaction fully completes or fully fails

Consistency Database moves from one valid state to another

Isolation Concurrent transactions don't interfere with each other

Durability Committed changes are permanent

6.2 Transaction States

Active: Initial state, transaction is executing

Partially Committed: After final statement execution

Committed: After successful completion

Failed: After discovering normal execution cannot proceed

Aborted: After transaction rollback

-- Transaction Example BEGIN TRANSACTION; UPDATE Account SET Balance = Balance

- 1000 WHERE AccountNo = 'A101'; UPDATE Account SET Balance = Balance + 1000
WHERE AccountNo = 'A102'; IF @@ERROR = 0 COMMIT; ELSE ROLLBACK;

6.3 Concurrency Control

Concurrency control ensures correct results when multiple transactions execute

simultaneously.

Problems:

Lost Update: Two transactions update same data

Dirty Read: Reading uncommitted data

Non-Repeatable Read: Data changes between reads

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 8/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Phantom Read: New rows appear between reads

Locking Mechanisms:

Shared Lock (S): Multiple transactions can read

Exclusive Lock (X): Only one transaction can write

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 9/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 7: Indexing

7.1 Index Structures

Indexes are data structures that improve the speed of data retrieval operations.

Index Type Description

Primary Index Created on primary key, ordered

Clustering Index Created on non-key ordered field

Secondary Index Created on non-ordering field

B-Tree Index Balanced tree structure for fast search

Hash Index Uses hash function for direct access

-- Create Index CREATE INDEX idx_student_name ON Students(Name); -- Create

Unique Index CREATE UNIQUE INDEX idx_email ON Students(Email); -- Drop Index
DROP INDEX idx_student_name;

Note: While indexes speed up SELECT queries, they slow down INSERT, UPDATE,
and DELETE operations as indexes must be maintained.

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 10/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Unit 8: NoSQL Databases

8.1 Types of NoSQL Databases

Document Stores: MongoDB, CouchDB

Key-Value Stores: Redis, DynamoDB

Column-Family Stores: Cassandra, HBase

Graph Databases: Neo4j, OrientDB

8.2 CAP Theorem

A distributed database can provide only two of these three guarantees:

Consistency: All nodes see same data at same time

Availability: Every request receives a response

Partition Tolerance: System continues despite network failures

8.3 SQL vs NoSQL

Aspect SQL NoSQL

Tables with fixed

Data Model Flexible schema
schema

Scalability Vertical scaling Horizontal scaling

BASE (Basically Available, Soft state,

Transactions ACID compliant
Eventually consistent)

Complex queries,
Best For Big data, real-time applications
transactions

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 11/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

Practice Questions

Q1: Explain the three-schema architecture of DBMS. Discuss data independence with
examples. (10 marks)

Q2: Write SQL queries for the following:

a) Create a table 'Employee' with appropriate constraints
b) Insert 5 records
c) Display employees with salary > 50000
d) Update salary by 10% for dept 'IT'
e) Delete employees with age < 25 (10 marks)

Q3: Explain normalization with examples. Convert an unnormalized table to 3NF

showing each step. (10 marks)

Q4: What are ACID properties? Explain with examples. Discuss concurrency control
problems. (8 marks)

Q5: Explain different types of joins with examples. Write SQL queries demonstrating
INNER JOIN, LEFT JOIN, and RIGHT JOIN. (10 marks)

Important Topics for Exam

1. Relational model concepts and keys

2. SQL queries - DDL, DML, joins, subqueries

3. Normalization up to BCNF

4. Transaction management and ACID properties

5. Indexing and B-tree structures

6. Concurrency control and locking

7. ER diagrams and mapping to relational model

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 12/13
11/4/25, 4:57 AM Database Management Systems - Study Guide

8. Query optimization techniques

Database Management Systems Study Guide - Pune University

Comprehensive DBMS concepts and SQL guide for examination

© 2024 | Educational Resource

file:///C:/Users/Acer/Downloads/DBMS_Study_Guide_SPPU.html 13/13