Unit I: Introduction to DBMS

1. Database System vs File System

 Definition: File system stores data in files; DBMS is software for creating and
managing databases.
 Example: File system - Excel sheets; DBMS - MySQL for online banking.
 Advantages:
o DBMS: Better data integrity, security.
o File system: Simpler for small tasks.
 Disadvantages:
o DBMS: Complex and costly.
o File system: No data independence.

2. Database System Concept and Architecture

 Definition: Structure of DBMS including components like hardware, software, users.

 Example: 3-tier architecture in banking systems.

3. Data Model, Schema, and Instances

 Definition:
o Data Model: Blueprint of database structure.
o Schema: Logical design.
o Instance: Snapshot of data.
 Example: ER model for college database.

4. Data Independence

 Definition: Ability to change schema without changing application.

 Types: Logical and Physical.

5. Database Languages and Interfaces

 Definition: Languages used in DBMS like DDL, DML.

 Example: SQL

6. DDL and DML

 Definition:
o DDL (Data Definition Language): Defines structure.
o DML (Data Manipulation Language): For data operations.

7. Overall Database Structure

 Includes: Tables, views, indexes.

8. ER Model Concepts

 Definition: Diagrammatic way to design DB structure.

  Components: Entity, Attribute, Relationship.
 Example: ER diagram for university.

9. Mapping Constraints

 Definition: Constraints like 1:1, 1:N, M:N.

Flowchart: DBMS Overview → Architecture → Data Models → Schema & Instances →

Data Independence → Languages (DDL/DML) → ER Model

Unit II: Relational Data Model and Language

1. Keys: Super Key, Candidate Key, Primary Key

 Definition:
o Super Key: Uniquely identifies a row.
o Candidate Key: Minimal super key.
o Primary Key: Chosen candidate key.
 Example: Student_ID in student table.

2. Relational Model Concepts

 Definition: Organizes data into relations (tables).

3. Integrity Constraints

 Types:
o Entity Integrity: No null in primary key.
o Referential Integrity: Foreign key must refer to existing primary key.
o Domain Constraint: Attribute values must be from defined domain.

4. Relational Algebra and Calculus

 Relational Algebra: Procedural query language.

 Relational Calculus: Non-procedural.

5. Tuple and Domain Calculus

 Definition: Types of relational calculus.

Flowchart: Keys → Relational Model → Integrity Constraints → Algebra & Calculus

Unit III: SQL and Database Design

1. Characteristics and Advantages of SQL

  Definition: Standard language for RDBMS.
 Advantages: Easy to learn, powerful, standardized.

2. SQL Data Types and Literals

 Example: INT, VARCHAR, DATE.

3. Types of SQL Commands

 DDL, DML, DCL, TCL

4. SQL Operators and Procedures

 Examples: =, <>, IN, LIKE

5. Tables, Views, Indexes

 Tables: Store data.

 Views: Virtual table.
 Indexes: Improve search speed.

6. Queries, Subqueries, Aggregate Functions

 Aggregate: SUM, AVG, COUNT

7. Insert, Update, Delete, Joins, Set Operations

 Joins: Inner, Left, Right, Full

 Set: UNION, INTERSECT, MINUS

8. Cursors, Triggers, Procedures in PL/SQL

 Cursor: Pointer to query result.

 Trigger: Auto action on data change.
 Procedure: Stored code.

9. Database Design and Normalization

 Functional Dependency: Relationship among attributes.

 Normal Forms:
o 1NF: Atomic values.
o 2NF: No partial dependency.
o 3NF: No transitive dependency.

Flowchart: SQL Basics → Commands & Operators → Tables & Views → Queries & Joins
→ Procedures & Triggers → Normalization

Unit IV: Transaction Processing

1. Transaction System and Serializability

 Definition: Ensures correctness and consistency of DB.

2. Conflict & View Serializability

 Conflict: Operations can be swapped safely.

 View: Final result must be same.

3. Recoverability & Backup

 Definition: Ability to restore DB after failure.

4. Log Based Recovery and Checkpoints

 Log: Tracks changes.

 Checkpoint: Save state.

5. Deadlock Handling

 Detection, Prevention, Avoidance

6. Concurrency Control Techniques

 Locking, Timestamp, Validation, Multigranularity, MVCC

Flowchart: Transaction → Serializability → Recovery → Deadlock → Concurrency

Techniques

Unit V: Recent Trends in DBMS

1. Centralized vs Client-Server Architecture

 Centralized: One location.

 Client-Server: Distributed workload.

2. Distributed DB

 Data spread across locations.

3. Object-Oriented, XML, and Multimedia DB

 Store complex data like images, videos.

4. Spatial & Temporal DB

 Spatial: Geographical data.

 Temporal: Time-based data.
5. DSS, Data Mining, Warehousing, Visualization

 Support decision making, discover patterns, store huge data.

6. Mobile, Web, and Active DB

 Active: Reacts to events.

Flowchart: DBMS Trends → Architecture → New DB Models → Analytics & Visualization

→ Web & Mobile DB

Unit I: Introduction to DBMS (Definitions with Examples)

1. Database System vs File System

 Definition: A file system stores data in files manually, while a DBMS is software
used to create and manage databases.
 Example: Storing student records in Excel (file system) vs using MySQL (DBMS).

2. Database System Concept and Architecture

 Definition: It refers to the structure and components of a DBMS such as hardware,

software, and users.
 Example: A 3-tier architecture used in online shopping websites.

3. Data Model, Schema, and Instances

 Definition:
o Data Model: Blueprint for how data is structured.
o Schema: Design of the database.
o Instance: Actual data at a moment.
 Example: A college ER diagram (schema), and the student entries (instances).

4. Data Independence

 Definition: Ability to change the database structure without changing application

programs.
 Example: Updating a table structure without changing the user interface.

5. Database Languages and Interfaces

 Definition: Languages used to create and access data in a DBMS.

 Example: SQL is a common DB language.

6. DDL and DML

  Definition:
o DDL: Defines the structure (e.g., CREATE).
o DML: Manages data (e.g., SELECT, INSERT).
 Example: Using DDL to create a table, and DML to add data to it.

7. Overall Database Structure

 Definition: The complete organization of data elements like tables, views, and
indexes.
 Example: A banking system with multiple tables and reports.

8. ER Model Concepts

 Definition: A method to design databases visually using entities and relationships.

 Example: ER diagram showing students enrolled in courses.

9. Mapping Constraints

 Definition: Rules that define the relationship types like one-to-one, one-to-many.
 Example: A teacher (one) teaches many students (many).

Unit II: Relational Data Model and Language (Definitions with Examples)

1. Keys: Super Key, Candidate Key, Primary Key

 Definition:
o Super Key: Any set of attributes that uniquely identifies a row.
o Candidate Key: Minimal super key.
o Primary Key: Main unique identifier.
 Example: Student_ID in a student table.

2. Relational Model Concepts

 Definition: A model that organizes data into tables (relations).

 Example: A table of employees with ID, name, and salary.

3. Integrity Constraints

 Definition: Rules to ensure correct and valid data.

 Example: Primary key should not be NULL (entity integrity).

4. Relational Algebra and Calculus

 Definition:
o Relational Algebra: Uses operations to query data.
o Relational Calculus: Uses logic to describe queries.
 Example: Selecting rows using projection and selection.
5. Tuple and Domain Calculus

 Definition: Types of relational calculus to define queries.

 Example: Finding students with marks > 80 using logic.

Unit III: SQL and Database Design (Definitions with Examples)

1. Characteristics and Advantages of SQL

 Definition: SQL is a standard language to manage and access relational databases.

 Example: Writing queries to fetch student data.

2. SQL Data Types and Literals

 Definition: Data types define the type of data (like number, text).
 Example: INT for age, VARCHAR for name.

3. Types of SQL Commands

 Definition: Commands to define and manipulate data.

 Example: DDL (CREATE), DML (INSERT), DCL (GRANT), TCL (COMMIT).

4. SQL Operators and Procedures

 Definition: Operators help filter data in queries.

 Example: Using > or = in WHERE clause.

5. Tables, Views, Indexes

 Definition:
o Table: Stores data.
o View: Virtual table from query.
o Index: Speeds up searching.
 Example: Product table with index on price.

6. Queries, Subqueries, Aggregate Functions

 Definition:
o Query: Request to get data.
o Subquery: Query inside another query.
o Aggregate Function: Performs calculations like AVG.
 Example: AVG(salary) to get average salary.

7. Insert, Update, Delete, Joins, Set Operations

 Definition: Commands to modify data and connect tables.

 Example: JOIN to combine student and marks table.
8. Cursors, Triggers, Procedures in PL/SQL

 Definition:
o Cursor: Points to query result.
o Trigger: Auto action on data change.
o Procedure: Stored group of SQL statements.
 Example: Trigger to auto-update stock after sale.

9. Database Design and Normalization

 Definition: Organizing data to remove redundancy.

o 1NF: No repeating groups.
o 2NF: No partial dependency.
o 3NF: No transitive dependency.
 Example: Separating student and course info into two tables.

Unit IV: Transaction Processing (Definitions with Examples)

1. Transaction System and Serializability

 Definition: Ensures multiple transactions run correctly.

 Example: Transferring money between bank accounts.

2. Conflict & View Serializability

 Definition: Ways to check if transactions can be executed in parallel safely.

 Example: Swapping read/write operations without changing result.

3. Recoverability & Backup

 Definition: Ability to recover from crashes.

 Example: Auto-backup of your email data.

4. Log Based Recovery and Checkpoints

 Definition:
o Log: Record of changes.
o Checkpoint: Save point in DB.
 Example: Restore game to last save point.

5. Deadlock Handling

 Definition: Handling situations where two transactions wait for each other forever.
 Example: Two people trying to reserve the last seat.

6. Concurrency Control Techniques

 Definition: Methods to handle multiple transactions at the same time.

  Example: Locking a row while editing.

Unit V: Recent Trends in DBMS (Definitions with Examples)

1. Centralized vs Client-Server Architecture

 Definition:
o Centralized: All data on one server.
o Client-Server: Tasks divided between client and server.
 Example: ATM network uses client-server.

2. Distributed DB

 Definition: Database is spread across multiple locations.

 Example: Google’s data across data centers.

3. Object-Oriented, XML, and Multimedia DB

 Definition: Handle complex data like images, videos, documents.

 Example: YouTube stores video metadata in multimedia DB.

4. Spatial & Temporal DB

 Definition:
o Spatial: Stores map-based data.
o Temporal: Stores time-based data.
 Example: Google Maps uses spatial DB.

5. DSS, Data Mining, Warehousing, Visualization

 Definition: Technologies for analysis and decision making.

 Example: Amazon recommends products using data mining.

6. Mobile, Web, and Active DB

 Definition:
o Mobile DB: For mobile apps.
o Web DB: For web services.
o Active DB: Reacts to events.
 Example: Mobile banking app uses mobile DB.

Unit I: Introduction to DBMS (Flowcharts)

1. DBMS Architecture
+-----------------------+
| User/Application |
+----------+------------+
|
+----------v------------+
| DBMS (Software Layer)|
+----------+------------+
|
+----------v------------+
| Database Storage |
+-----------------------+

2. ER Model Design Flow

Start → Identify Entities → Identify Attributes → Identify Relationships →

Define Primary Keys → Create ER Diagram

Unit II: Relational Data Model and Language (Flowcharts)

1. Key Identification Flow

Start → Identify Attributes → Check Uniqueness → Super Key → Eliminate

Extra Attributes → Candidate Key → Choose Primary Key

2. Integrity Constraint Check Flow

Start → Check Entity Integrity (Primary Key not NULL) → Check Referential
Integrity (Foreign Key matches PK) → Check Domain Constraints

3. Relational Algebra Process Flow

Start → Input Relations → Apply Selection/Projection → Apply

Join/Union/Intersection → Output Relation

Unit III: SQL and Database Design (Flowcharts)

1. SQL Command Process

Start → Choose Operation Type →

├─ DDL → CREATE/ALTER/DROP
├─ DML → SELECT/INSERT/UPDATE/DELETE
├─ DCL → GRANT/REVOKE
└─ TCL → COMMIT/ROLLBACK

2. Query Execution Flow

Start → Parse Query → Optimize Query Plan → Execute Query → Return Result

3. Normalization Steps

Start → 1NF (Atomic values) → 2NF (Full dependency) → 3NF (No transitive
dependency)
Unit IV: Transaction Processing (Flowcharts)

1. Transaction Lifecycle

Start → Begin Transaction → Execute Operations → Commit OR Rollback → End

2. Serializability Check Flow

Start → Build Precedence Graph → Check for Cycles

├─ If Cycle → Not Serializable
└─ No Cycle → Conflict Serializable

3. Deadlock Handling Flow

Start → Detect Deadlock → Choose Victim → Abort Transaction → Release Locks

→ Continue

Unit V: Recent Trends in DBMS (Flowcharts)

1. Centralized vs Distributed DBMS Flow

Start → Centralized DB (Single Location) OR

→ Distributed DB (Multiple Sites)
└→ Sync & Coordination

2. Data Warehousing and Mining Flow

Data Sources → Data Cleaning → Data Warehouse → Data Mining → Pattern

Discovery → Decision Support

3. Web & Mobile DB Access Flow

User Device → Application Layer → API/Backend → DB Query → Result to User

Unit I: Introduction to DBMS (Definitions with Examples)

Comparison Table: File System vs Database System

Feature File System Database System

Data Management Manual Automated

Redundancy High Low

 Feature File System Database System

Data Integrity Difficult to maintain Easy with constraints

Querying Capability Limited Advanced querying via SQL

Security Basic Advanced with user roles

Comparison Table: DDL vs DML

Feature DDL (Data Definition Language) DML (Data Manipulation Language)

Purpose Define database structure Manage data inside structure

Example Commands CREATE, ALTER, DROP INSERT, UPDATE, DELETE, SELECT

Affects Structure of database Data inside tables

Rollback Possible Generally No Yes

Comparison Table: Schema vs Instance

Feature Schema Instance

Definition Design or blueprint of the database Actual data at a point in time

Changes Rarely changes Changes frequently

Example Table structure Rows in a table

Comparison Table: Data Model vs Schema

Feature Data Model Schema

Definition Framework to organize data Actual design of a database

Example Relational, Object-oriented Student(name, age, roll_no)

Comparison Table: ER Model vs Relational Model

Feature ER Model Relational Model

Purpose Database design Database implementation

Representation Entities and relationships Tables

Example Student → Enrolls → Course STUDENT(id, name), COURSE(code, title)

Unit II: Relational Data Model and Language (Definitions with Examples)

Comparison Table: Super Key vs Candidate Key vs Primary Key

Feature Super Key Candidate Key Primary Key

Definition Any key that uniquely identifies rows Minimal super key Selected candidate key

Uniqueness Yes Yes Yes

Redundancy May contain extra attributes No extra attributes No extra attributes

Example (ID, Email), (ID) (ID), (Email) (ID)

Comparison Table: Relational Algebra vs Relational Calculus

Feature Relational Algebra Relational Calculus

Approach Procedural Non-procedural

Syntax Uses operators Uses logical expressions

Example SELECT, PROJECT {t

Comparison Table: Tuple Calculus vs Domain Calculus

Feature Tuple Relational Calculus Domain Relational Calculus

Basis Tuple variables Domain variables

Example {t t ∈ STUDENT ∧ t.marks > 80}

Unit III: SQL and Database Design (Definitions with Examples)

Comparison Table: SQL Data Types vs Literals

Feature SQL Data Types SQL Literals

Definition Types of values a column can hold Constant values in SQL

Example INT, VARCHAR, DATE 'John', 100, '2024-05-10'

Comparison Table: SQL Commands Types

Command Type Description Example Commands

DDL Define structure CREATE, ALTER, DROP

DML Manage data SELECT, INSERT, UPDATE, DELETE

DCL Control access GRANT, REVOKE

TCL Manage transactions COMMIT, ROLLBACK, SAVEPOINT

Comparison Table: Table vs View vs Index

Feature Table View Index

Stores Data Yes No (virtual) No (for speed)

Updates Yes Sometimes No

Speed Normal Slower than table Speeds up retrieval

Comparison Table: Join vs Union vs Intersection vs Minus

Operation Purpose Result

Join Combine rows from two tables Related data from both tables

Union Combine results of two queries All unique rows

Intersection Common rows from two queries Only common rows

Minus Rows in one query but not in other Difference

Unit IV: Transaction Processing (Definitions with Examples)

Comparison Table: Conflict Serializability vs View Serializability

Feature Conflict Serializability View Serializability

Basis Order of operations Final result

Check Method Using precedence graph Comparing views

Complexity Easier to test Harder to test

Comparison Table: Log Based Recovery vs Checkpoint

 Feature Log Based Recovery Checkpoint

Purpose Track changes Save system state

When Used Always Periodically

Example Redo/Undo operations Mark safe point for recovery

Comparison Table: Deadlock vs Starvation

Feature Deadlock Starvation

Definition Two transactions wait forever A transaction never gets executed

Cause Cyclic wait Low priority or unfairness

Unit V: Recent Trends in DBMS (Definitions with Examples)

Comparison Table: Centralized vs Client-Server Architecture

Feature Centralized DB Client-Server DB

Data Storage One central location Divided between client & server

Access Speed May be slower for remote Faster access with network

Example Old mainframe DB ATM systems, online apps

Comparison Table: Object-Oriented DB vs Relational DB

Feature Object-Oriented DB Relational DB

Data Type Objects with attributes & methods Tables with rows & columns

Example Multimedia, CAD databases Banking, HR systems

Comparison Table: Spatial DB vs Temporal DB

Feature Spatial DB Temporal DB

Data Type Geographical/Spatial Time-based

Example Google Maps Employee shift records

Comparison Table: Data Mining vs Data Warehousing

Feature Data Mining Data Warehousing

Purpose Discover patterns Store large historical data

Use Case Recommendation systems Business analysis

Comparison Table: Web DB vs Mobile DB

Feature Web Database Mobile Database

Use Platform Web browser based apps Mobile devices

Example Online store DB Mobile banking app

✅ Unit I: Introduction to DBMS

Topic Application Industry Example

DBMS Managing complex data with multiple users Banking Systems (e.g., ICICI Bank
Architecture accessing data simultaneously Core DBMS)

Designing structured database blueprints Hospital Management System for

ER Model
before actual implementation patient record planning

✅ Unit II: Relational Data Model and Language

Topic Application Industry Example

Keys & Enforcing data uniqueness and relationships among

Amazon Product Catalog
Constraints tables

Relational PostgreSQL used by

Query processing and optimization
Algebra Instagram

Relational Declarative query specification for complex data

Oracle Data Warehousing
Calculus retrieval

✅ Unit III: SQL and Database Design

 Topic Application Industry Example

Creating, updating, and managing data in MySQL used in WordPress

SQL Commands
relational databases websites

Flipkart's search results

Views & Indexes Speeding up query processing, restricting access
optimization

Joins & Uber driver-passenger data

Combining data across tables for insights
Aggregates linking

Reducing redundancy and improving data University student records

Normalization
integrity systems

✅ Unit IV: Transaction Processing & Concurrency Control

Topic Application Industry Example

Transaction Ensuring all database operations execute

HDFC Bank Fund Transfer
Management completely or not at all

Avoiding system hang-ups in multi-user

Deadlock Handling SAP Inventory Management
databases

Concurrency Ensuring accuracy during simultaneous data Stock Trading Platforms like
Protocols access NSE/BSE

✅ Unit V: Recent Trends in DBMS

Topic Application Industry Example

Centralized vs Handling global data from multiple Facebook’s Global User

Distributed DB locations Database

Data Warehousing & Analyzing large historical data for trends

Salesforce CRM Insights
Mining and forecasting

Web & Mobile Accessing live databases from Zomato, WhatsApp Real-Time
Databases web/mobile apps Chat DB

Spatial/Temporal Managing location-based and time- Google Maps, Ola Location

Databases based data Tracking

Multimedia & XML Storing images, audio, video, and YouTube Media Storage, News
Databases hierarchical data Aggregators