Chapter 1st

Introduction to DBMS
1.1 Introduction to Data, Database and DBMS
Data: -Data refers to raw facts, figures, or symbols that are collected for reference or analysis. It can be numbers, text,
images, audio, or video. It has no meaning unless it's processed.
Types of Data: -
• Structured Data – Easily organized in tables (e.g., SQL databases).
• Unstructured Data – Lacks predefined format (e.g., emails, PDFs, images).
• Semi-structured Data – Not as rigid as structured, but still organized (e.g., XML, JSON).
• Metadata – Data about data (e.g., file size, creation date).
Characteristics of data: -
• Raw and unprocessed
• Can be qualitative (descriptive) or quantitative (numerical)
• Needs interpretation or processing to become meaningful (i.e., information)
Example:
• Raw: "Kruti", "21", "M", "Nashik".
Database: -A Database is an organized collection of related data stored electronically, designed to efficiently store,
retrieve, and manage information.
Purpose:
• To store large volumes of data in a structured way
• Allow multiple users and applications to access and manipulate data securely and efficiently
Key Properties:
• Persistence: Data is stored permanently until deleted.
• Data Integrity: Ensures accuracy and consistency.
• Data Independence: Application logic and data storage are separate.
• Concurrency: Supports multiple users at once.
• Security: Data is protected using authentication and permissions.
Types of Databases:
1. Relational Database (RDBMS) – Based on tables (e.g., MySQL, PostgreSQL).
2. NoSQL Database – Non-relational, for unstructured data (e.g., MongoDB, Cassandra).
3. Distributed Database – Spread over multiple machines.
4. Object-oriented Database – Stores data as objects (e.g., db4o).
5. Cloud Database – Hosted in cloud environments (e.g., Amazon RDS).
Example: - A student Database
Roll No Name Age Course
101 Kruti 21 [Link]. IT
102 Aayush 22 BCA
DBMS (Database Management System): -A DBMS is software that interacts with end-users, applications, and the
database to capture and analyze data. It provides an interface to manage databases efficiently.
Functions of a DBMS:
1. Data Storage, Retrieval, and Update
2. User Access Control and Security
3. Transaction Management (ensures ACID properties)
4. Backup and Recovery
5. Data Integrity Management
6. Data Dictionary Access (metadata)
Components of DBMS:
1. DBMS Engine – Core service that handles storage and retrieval.
2. Query Processor – Translates user queries into DB commands.
3. Database Schema – Defines the structure (tables, types, etc.)
4. Transaction Manager – Ensures data consistency.
5. Storage Manager – Handles physical data files on disk.
Types of DBMS:
• Hierarchical DBMS – Tree-like structure (e.g., IBM IMS)
• Network DBMS – Complex relationships (e.g., IDMS)
• Relational DBMS (RDBMS) – Table-based (e.g., Oracle, SQL Server)
• Object-oriented DBMS – Integrates object models with databases
Advantages of DBMS:
• Reduces data redundancy
• Enforces data integrity
• Centralized control and security
• Easy backup and restore
• Efficient data sharing and accessibility
Disadvantages:
• Complex to implement
• Costly (software + hardware)
• Requires technical expertise
• Performance can degrade for huge data without optimization
Note: -You store data in a database, and use a DBMS to interact with that data.

1.2File System vs DBMS

What is a File System?
A file system is a software that manages the way data is stored and retrieved on a disk storage (e.g., hard drives, SSDs).
Data is stored in files, which are organized into directories/folders.
Features: -
• Stores data in flat files like .txt, .csv, etc.
• Data access is sequential or indexed (not relational).
• No in-built mechanisms for data consistency, multi-user access, or query languages.
Example Scenario
Suppose a college stores student info in multiple .txt files:
• [Link], [Link], [Link]
To find the marks of a student:
• A developer has to write a program to read multiple files, match student IDs, and fetch data.
• If multiple users try to access or update data, consistency may be lost.
What is a DBMS?
A DBMS is software that manages databases. It allows users to store, retrieve, update, and delete data using
structured formats (tables, relations) and query languages (e.g., SQL).
Features: -
• Data stored in tables (rows and columns).
• Supports multi-user access, concurrency, transaction management, and data integrity.
• Powerful query language (SQL) for data manipulation.
• Provides backup, recovery, security, and data abstraction.
File System vs DBMS – Detailed Comparison Table: -
Criteria File System DBMS (Database Management System)
1. Data Storage Format Stored in files (e.g., .txt, .csv) Stored in tables (rows & columns)
High (same data may be stored in
2. Data Redundancy Low (data normalization avoids repetition)
multiple files)
Ensured through constraints and
3. Data Consistency Difficult to maintain
transactions
Enforced using rules (e.g., primary key,
4. Data Integrity Manual enforcement
foreign key)
5. Data Retrieval Requires custom programs or scripts Uses SQL queries (easy and flexible)
6. Concurrency Poor multi-user access support Handles multiple users efficiently
 Criteria File System DBMS (Database Management System)
Fine-grained access control (user roles,
7. Security Basic file-level protection
privileges)
8. Backup and Recovery Manual backup required Built-in backup and recovery features
9. Transaction
Not supported Supported using ACID properties
Management
10. Scalability Poor with large data Designed for large-scale applications
11. Indexing Manual, limited indexing options Automated and optimized indexing
Logical and physical data abstraction
12. Data Abstraction None
supported
13. Relationships Difficult to establish between files Built-in relational model support
Optimized for complex queries and
14. Performance Decreases with complexity
relations
15. Query Language Not available SQL or other query languages available

When to use what: -

File System DBMS
Small applications Large and complex applications
Single-user systems Multi-user enterprise systems
No need for complex queries Heavy querying and relational data
Limited storage needs Large volumes of data

1.3 Levels of Data Abstraction and Data Independence: -

What is Data Abstraction?
Data abstraction is the process of hiding the implementation details of the database and exposing only the necessary
parts to the user. It helps in simplifying user interaction, enhancing security, and achieving data independence.
Just like how a car driver only needs to know how to drive, not how the engine works — a DBMS user only needs to
know what data is available, not how it is stored or managed internally.
3 Levels of Data Abstraction in DBMS
DBMS uses three levels of abstraction to separate user applications from physical database details:
1. Physical Level (Lowest level)
What it describes:
• How data is actually stored in memory or on disk
• File formats, indexing methods, data blocks, compression, etc.
Who interacts with this level:
• DBA (Database Administrator) and System Programmers
Key Points:
• Focus is on efficiency and performance.
• Determines storage structure, e.g., B-Trees, hashing.
• Changes here should not affect the higher levels (logical or view).
Example:
A student record is stored using a binary search tree and indexed on Roll Number with hashing techniques.
2. Logical Level (Middle level)
What it describes:
• The structure and relationship of the data
• What data is stored and what relationships exist among them
Who interacts with this level:
• Database designers and developers
Key Points:
• Describes tables, fields, data types, constraints (like primary key, foreign key)
 • Independent of physical storage
• Central to data modeling (ER diagrams are used here)
• Changes here should not affect the application (view level)
Example:
A STUDENT table has attributes: Roll_No, Name, Age, Course_ID
3. View Level (Highest level)
What it describes:
• The part of the database that a particular user or application can access
• Also called the external level
Who interacts with this level:
• End-users and application developers
Key Points:
• Hides unnecessary details (security and simplicity)
• Multiple views can be created for different users
• Helps in data security and user-specific customization
Example:
A faculty member may see only names and course IDs of students, but not their personal details like age or roll
number.

┌──────────────────────┐
│ View Level │ ← What the user sees (custom views)
└──────────────────────┘
↑
┌──────────────────────┐
│ Logical Level │ ← Structure and relationships (tables, keys)
└──────────────────────┘
↑
┌──────────────────────┐
│ Physical Level │ ← Actual storage on disk (indexing, files)
└──────────────────────┘
Importance of Data Abstraction in DBMS
Feature Benefit
Simplicity Hides complex internals from end-users
Security Users see only authorized data via views
Data Independence Changes at one level do not affect others
Efficiency Optimizations at physical level without user impact
Maintainability Easier to design, update, and scale databases

Data Independence:
1. Logical Data Independence
• Ability to change logical schema without changing application programs
• Example: Adding a new column to a table
2. Physical Data Independence
• Ability to change physical storage without affecting logical schema
• Example: Changing from file system to SSD or indexing method
Note:-

Layer Real-Life Analogy

View Level Menu card in a restaurant
Logical Level Kitchen recipe or cooking instructions
Physical Level Actual cooking (frying, baking, boiling)
1.4 Architecture of DBMS: -
What is DBMS Architecture?
DBMS architecture refers to the design structure of a Database Management System that outlines how data is
processed, stored, and accessed. It defines how different users interact with the database system, and how data flows
through different layers.
Types of DBMS Architecture:
There are two main classifications of DBMS architecture:
1. Logical Architecture – Levels of abstraction (discussed earlier):
• View level (external)
• Logical level (conceptual)
• Physical level (internal)
2. Physical Architecture – Internal structure of DBMS:
This includes modules, components, and layers that manage:
• Query processing
• Storage
• Transactions
• Security
Components of DBMS Architecture (Detailed Explanation)
1. End Users
• People who interact with the database via applications or query
interfaces.
• Types: DBA, application developer, casual users, naive users.
2. Application Programs
• Interface between end-users and DBMS.
• Written in programming languages like Java, Python, C#, etc.
• Connect to DBMS using APIs (JDBC, ODBC).
3. Query Processor
• Converts user queries (usually in SQL) into a series of low-level
instructions that the DBMS can execute.
• Major subcomponents:
a) DDL Compiler (Data Definition Language)
• Interprets schema definitions (e.g., CREATE, ALTER) and stores
metadata in the data dictionary.
b) DML Compiler (Data Manipulation Language)
• Translates queries like SELECT, INSERT, UPDATE, DELETE into low-level
instructions.
c) Query Optimizer
• Determines the most efficient way to execute a query.
• Uses cost-based or rule-based optimization techniques.
4. Execution Engine
• Executes low-level instructions passed from the query processor.
• Coordinates data retrieval or modification from storage.
5. Transaction Manager
• Ensures database correctness, consistency, and isolation for concurrent transactions.
• Maintains ACID properties:
o Atomicity
o Consistency
o Isolation
o Durability
6. File Manager
• Handles data storage and manages the physical files on disk.
• Takes care of file structure, allocation, buffering, and access methods.
7. Data Storage (Physical Layer)
• Lowest level where data is actually stored on disks.
• Includes data files, indexes, and log files (for recovery).
1.5 Users of DBMS
1. Database Administrator (DBA)
Role:
The DBA is responsible for the overall control and management of the database system.
Responsibilities:
• Installing and configuring the DBMS software
• Creating and managing user accounts
• Ensuring data security and privacy
• Backup and recovery of data
• Monitoring performance and tuning queries
• Managing disk space and storage
• Defining schema, access rules, and authorizations
Example:
A DBA at a bank sets up database security policies, creates scheduled backups, and handles emergency data recovery.
2. Database Designers
Role:
They design the logical structure (schema) of the database based on the user requirements.
Responsibilities:
• Analyzing user needs and business processes
• Designing Entity-Relationship (ER) models
• Creating tables, relationships, and constraints
• Defining data types and normalization rules
• Supporting DBAs in database structure updates
Example:
A university data architect designs how Students, Courses, and Instructors relate in the database.
3. End Users
These are the people who interact with the database either directly or indirectly through applications.
a) Naive Users
• Non-technical users
• Use predefined applications or forms
• No knowledge of database structure
Example: A bank clerk enters or views data using a GUI-based software.
b) Sophisticated Users
• Use advanced tools like SQL queries, BI tools, or data science platforms
• Understand the database structure
• May use data analytics, reporting, or data mining
Example: A data analyst running custom SQL queries on a sales database.
c) Casual Users
• Occasionally interact with the database using query languages
• Not frequent users; may need to extract specific information
Example: A manager querying employee performance monthly using a dashboard.
d) Stand-alone Users
• Maintain personal databases using tools like MS Access
• Design and run their own applications without involving DBAs
Example: A school teacher maintaining student records in a local MS Access database.
4. Application Programmers / Developers
Role:
They develop applications that use the database for data manipulation and presentation.
Responsibilities:
• Write programs in Java, Python, C#, etc.
• Use JDBC, ODBC, or other APIs to connect to the DBMS
• Implement user interfaces, validation, and business logic
• Ensure secure data access from the app side
Example:
A developer creating a mobile app that allows users to book movie tickets by interacting with the underlying database.
1.6 Applications of DBMS: -
Sector DBMS Application Area
Banking Transactions, accounts, loans, audits
Telecom Billing, call records, usage data
Education Student data, attendance, results
E-commerce Orders, products, customer data
Healthcare Patient records, appointments
Airlines/Railways Reservations, schedules, ticketing
Social Media Posts, messages, user data
Retail Inventory, sales, billing
Government Aadhaar, tax, passport, law enforcement data
HRMS Employee data, payroll, attendance
Libraries Books, users, borrowing history
Research Experiment data, analytics