Unit – I: Introduction to Database Management System

1. Introduction to DBMS and Its Advantages

Definition:

A Database Management System (DBMS) is software that helps in storing, managing, and retrieving data from databases efficiently.

Example:

A library system where books, members, issue/return records are stored in a database and managed using software like MySQL or Oracle DB.

Advantages of DBMS:

Advantage	Description
Data Redundancy Control	Avoids duplication of data.
Data Consistency	Ensures all users see the same data.
Data Integrity	Maintains accuracy and consistency.
Data Security	Provides user-level access control.
Concurrent Access	Multiple users can access data simultaneously without conflict.
Backup and Recovery	Automatically handles data backup and recovery after failure.
Better Data Sharing	Centralized access improves collaboration.

Diagram: DBMS Overview

 

2. Data Abstraction / Data Independence

What is Data Abstraction?

Data Abstraction is the process of hiding unnecessary details from users and showing only the information that is required. It allows users to interact with the database without knowing how the data is physically stored.

Definition:

Data Abstraction is the technique of hiding the internal implementation details of the database and providing only the essential information to the user.

Why is Data Abstraction Needed?

• Simplifies database usage.
• Improves security by restricting unnecessary access.
• Makes the database easier to maintain.
• Allows changes in database structure without affecting users. 

Levels of Abstraction:

Level	Description	Example
Physical Level	How data is actually stored	Binary files, indexes
Logical Level	What data is stored and relationships	Tables, relationships like foreign key
View Level	How data is presented to users	Reports, forms

Diagram: Levels of Data Abstraction

1. Physical Level (Internal Level)

This is the lowest level of abstraction.

It describes how data is actually stored inside the computer.

It includes:

• File organization
• Storage blocks
• Indexing
• Compression
• Memory allocation

Example

Database stores student records as:

Hard Disk

----------------------------------------
Block 101
----------------------------------------
Student_ID : 101
Name       : Jay
Branch     : Computer
CGPA       : 8.90
----------------------------------------

The user never sees these storage details.

2. Logical Level (Conceptual Level)

This is the middle level.

It describes:

• What data is stored.
• Relationships between data.
• Constraints.

Database designers work at this level.

Example

Student

Student_ID
Name
Branch
Semester
CGPA

Relationships:

Student
   |
   | Enrolls
   |
Course

The designer knows the tables and relationships but not the storage location.

3. View Level (External Level)

This is the highest level.

Different users see different parts of the database.

Example

Student View

Student_ID
Name
CGPA

Faculty View

Student_ID
Name
Attendance
Marks

Accounts Department

Student_ID
Name
Fees
Scholarship

Each user only sees the information they need.

Real-Life Example

Consider an ATM Machine.

User
   │
   ▼
ATM Screen
   │
   ▼
Bank Database
   │
   ▼
Hard Disk

User

Only sees:

• Balance
• Withdraw
• Deposit

Hidden Information

• Database tables
• SQL Queries
• Server
• Disk Storage
• File System

This is Data Abstraction.

Data Independence (DBMS)

Definition

Data Independence is the ability to modify the database schema at one level without requiring changes at the next higher level. It ensures that changes made in the database structure do not affect users or application programs.

Definition:

Data Independence is the capability of a Database Management System (DBMS) to modify the schema at one level without affecting the schema at the higher level.

---

Diagram of Data Independence

            Users / Applications
                    │
                    ▼
          External (View) Schema
                    │
      Logical Data Independence
                    │
                    ▼
        Conceptual (Logical) Schema
                    │
     Physical Data Independence
                    │
                    ▼
         Internal (Physical) Schema
                    │
                    ▼
              Physical Storage

---

Types of Data Independence

There are two types of Data Independence:

1. Logical Data Independence
2. Physical Data Independence

---

1. Logical Data Independence

Definition

Logical Data Independence is the ability to change the logical (conceptual) schema without affecting the external (view) schema or application programs.

In simple words, modifications made to the database structure (such as adding or removing attributes, tables, or relationships) should not require changes in the user views.

Examples of Logical Changes

• Adding a new column to a table.
• Creating a new table.
• Deleting an unused table.
• Modifying relationships between tables.

Example

Before Modification

Student
------------------------
Student_ID
Name
Branch

After Modification

Student
------------------------
Student_ID
Name
Branch
Email
Phone

The application that displays only Student_ID, Name, and Branch continues to work without any changes because the user view remains the same.

Real-Life Example

A college adds an Email column to the student database.

• Existing student portal displays:
  • Student ID
  • Name
  • Branch

The portal works exactly as before because the additional field does not affect the existing user view.

2. Physical Data Independence

Definition

Physical Data Independence is the ability to change the physical (internal) schema without affecting the logical (conceptual) schema.

These changes are related to how the data is stored internally and are invisible to users and applications.

Examples of Physical Changes

• Changing file organization.
• Adding indexes.
• Compressing data files.
• Moving data from HDD to SSD.
• Partitioning database files.

Example

Before

Student Database
Stored on Hard Disk (HDD)

After

Student Database
Stored on Solid State Drive (SSD)

The application still retrieves the student records in the same way because only the storage method has changed.

Another Example

Before:

Search Student Records
(No Index)

After:

Search Student Records
(Indexed)

Searching becomes much faster, but the application program does not need any modification.

---

Comparison of Logical and Physical Data Independence

Feature	Logical Data Independence	Physical Data Independence
Schema Changed	Logical (Conceptual) Schema	Physical (Internal) Schema
Affects	External (View) Schema	Logical (Conceptual) Schema
Difficulty	More difficult to achieve	Easier to achieve
Changes Include	Adding/removing tables, attributes, relationships	Indexing, file organization, storage location, compression
Example	Adding an Email column to the Student table	Moving the database from HDD to SSD

---

Advantages of Data Independence

• Reduces application maintenance.
• Improves database flexibility.
• Allows performance optimization without affecting users.
• Simplifies database administration.
• Supports future expansion of the database.
• Increases system reliability and scalability.

---

Key Points to Remember

• Data Independence means changing one level of the database without affecting higher levels.
• Logical Data Independence → Changes in the logical schema do not affect the view schema.
• Physical Data Independence → Changes in the physical schema do not affect the logical schema.
• Physical Data Independence is generally easier to achieve than Logical Data Independence.

---

Exam Definition (2 Marks)

Data Independence:
Data Independence is the ability of a DBMS to modify the database schema at one level without affecting the schema at the next higher level.

Types:

1. Logical Data Independence: Changes in the logical schema do not affect the external (view) schema.
2. Physical Data Independence: Changes in the physical schema do not affect the logical (conceptual) schema.

---

3. Components of Database

Main Components:

Component	Description
Hardware	Physical devices (servers, storage)
Software	DBMS software like MySQL, Oracle
Data	Actual data stored in databases
Users	People interacting with DBMS (DBA, Developer, End-users)
Procedures	Instructions and rules for design, use, and maintenance

Diagram: Components of DBMS

---

4. DBMS Architecture

Types of DBMS Architectures:

1. 1-Tier Architecture:
• User interacts directly with the database.
• Used in small, local applications.
2. 2-Tier Architecture:
• Application (Client) and Database (Server).
• Application communicates with the DBMS.
3. 3-Tier Architecture:
• Presentation Tier – User interface (Webpage)
• Logic Tier – Business logic (API/Server)
• Data Tier – Database

Diagram: 3-Tier Architecture

---

5. Metadata, Database Languages, Data Storage and Querying

Metadata:

• Data about data
• Example: Data types, schema, constraints, indexes.

Database Languages:

Language	Purpose	Example
DDL (Data Definition)	Define structure (CREATE, ALTER)	CREATE TABLE student (...)
DML (Data Manipulation)	Manipulate data (INSERT, UPDATE)	INSERT INTO student VALUES (...)
DCL (Control)	Control access (GRANT, REVOKE)	GRANT SELECT ON student TO user1;
TCL (Transaction)	Manage transactions (COMMIT, ROLLBACK)	COMMIT;

Data Storage:

• Data is stored in files on disk.
• DBMS uses indexing, hashing, and B-Trees for fast access.

Querying:

• SQL is used to query the database.
• Example:

sql

SELECT name FROM student WHERE marks > 75;

---

Summary Table:

Topic	Key Point
DBMS Introduction	Manages data efficiently with advantages like security and consistency.
Data Abstraction	3 levels: Physical, Logical, View.
Components of DBMS	Hardware, Software, Data, Users, Procedures.
DBMS Architecture	1-Tier, 2-Tier, and 3-Tier models.
Metadata and Languages	Metadata = data about data; Languages = DDL, DML, DCL, TCL.
Storage & Querying	Uses file systems with SQL for querying.

Unit – I: Introduction to DBMS – Q&A

---

1. What is a Database Management System (DBMS)? Explain its advantages.

Answer:

A Database Management System (DBMS) is software that allows users to store, retrieve, manage, and manipulate data in databases efficiently and securely.

Advantages of DBMS:

1.      Reduces Data Redundancy – Avoids duplication of data.

2.      Ensures Data Consistency – All users see consistent and updated data.

3.      Improves Data Security – Controls access through user permissions.

4.      Allows Data Sharing – Multiple users can access data simultaneously.

5.      Provides Backup & Recovery – Automatic data recovery in case of failure.

6.      Maintains Data Integrity – Enforces data accuracy with constraints.

7.      Improves Decision Making – Provides tools for easy data retrieval.

---

2. Explain Data Abstraction with its levels.

Answer:

Data Abstraction in DBMS hides the internal complexity of the database from users and provides a simplified interface.

Three Levels of Data Abstraction:

Level	Description
View Level	What the user sees (forms, reports).
Logical Level	What data is stored and the relationships.
Physical Level	How data is stored on hardware.

Diagram:

sql

+------------------+   <-- View Level

|  Customer Report |

+------------------+

        |

+------------------+   <-- Logical Level

|  Table: Customer |

+------------------+

        |

+------------------+   <-- Physical Level

|  Disk Blocks     |

+------------------+

---

3. What is Data Independence? Explain its types with examples.

Answer:

Data Independence is the capacity to change the schema at one level without affecting the schema at the next higher level.

Types:

1.      Logical Data Independence

o    Changes in logical schema (e.g., adding a column) don’t affect application programs.

o    Example: Adding “email” to the "Student" table should not break the UI.

2.      Physical Data Independence

o    Changes in physical storage (e.g., file structure) don’t affect the logical schema.

o    Example: Moving data to SSD doesn't affect table definitions.

---

4. List and explain the components of DBMS.

Answer:

Main Components of DBMS:

1.      Hardware – Physical devices (e.g., server, disk).

2.      Software – DBMS software like MySQL, Oracle.

3.      Data – Stored facts (e.g., student records).

4.      Users –

o    DBA: Database Administrator

o    Application Programmers

o    End-users

5.      Procedures – Set of rules to design, access, and maintain DBMS.

Diagram:

Users → Application → DBMS → Database

---

5. Describe DBMS architecture and types.

Answer:

DBMS architecture defines how the components of DBMS interact with each other.

Types of DBMS Architecture:

1.      1-Tier Architecture

o    All DBMS components on the same system.

o    Example: Standalone desktop application.

2.      2-Tier Architecture

o    Client directly connects to the DBMS server.

o    Example: Client-server applications using ODBC/JDBC.

3.      3-Tier Architecture

o    Presentation Layer → Application Layer → Database Layer

o    Used in modern web-based applications.

Diagram: 3-Tier Architecture

sql

+---------------+     Presentation (UI)

| Web Browser   |

+-------+-------+

        |

+-------v-------+     Application (Business Logic)

| Application   |

+-------+-------+

        |

+-------v-------+     Database Layer

| DBMS / SQL    |

+---------------+

---

6. What is Metadata? How is it useful in DBMS?

Answer:

Metadata is data about data. It describes the structure, constraints, and relationships of stored data.

Examples:

·         Table names, field names

·         Data types of fields

·         Primary and foreign keys

·         Indexes

Usefulness:

·         Helps query optimizers to plan efficient queries

·         Assists developers in understanding data structure

---

7. Explain different types of DBMS languages with examples.

Answer:

Language	Purpose	Example
DDL	Define structure of database	CREATE TABLE student(...)
DML	Manipulate data	INSERT INTO student VALUES(...)
DCL	Control access	GRANT SELECT ON student TO user1;
TCL	Control transactions	COMMIT;, ROLLBACK;

8. What are the functions of DBMS?

Answer:

Main functions include:

1.      Data Storage Management

2.      Data Retrieval using Queries

3.      Data Manipulation (Insert, Update, Delete)

4.      Transaction Management

5.      Concurrency Control

6.      Backup and Recovery

7.      Security and Authorization

8.      Data Integrity Enforcement

---

9. Write a sample SQL query for:

a) Creating a table

b) Inserting a record

c) Retrieving specific data

Answer:

sql

-- a) Create table

CREATE TABLE student (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    marks INT

);

-- b) Insert a record

INSERT INTO student VALUES (1, 'Jaydeep', 85);

-- c) Retrieve data

SELECT name FROM student WHERE marks > 75;

---

10. Differentiate between File System and DBMS.

Feature	File System	DBMS
Data Redundancy	High	Low
Data Integrity	Not enforced	Enforced using constraints
Data Sharing	Difficult	Easy and concurrent
Security	Minimal	Strong access control
Backup/Recovery	Manual	Automatic

DBMS Unit – I: MCQs

---

1. What does DBMS stand for?

A) Database Manipulation System
B) Data Backup Management System
C) Database Management System 
D) Database Monitoring Software

---

2. Which of the following is NOT an advantage of DBMS?

A) Data redundancy control
B) Data sharing
C) Complexity in usage 
D) Backup and recovery

---

3. The level of data abstraction which shows how the data is actually stored is:

A) View level
B) Physical level 
C) Logical level
D) Conceptual level

---

4. Which level of abstraction describes what data is stored in the database?

A) View Level
B) Physical Level
C) Logical Level 
D) Internal Level

---

5. The type of data independence that deals with changes in physical storage is:

A) Logical Data Independence
B) External Data Independence
C) Physical Data Independence 
D) Conceptual Data Independence

---

6. Which component of DBMS is responsible for managing storage space?

A) Query Processor
B) Transaction Manager
C) Storage Manager 
D) Authorization Manager

---

7. In a 3-tier architecture, the middle tier is called:

A) Database Tier
B) Client Tier
C) Application/Logic Tier 
D) UI Tier

---

8. Metadata refers to:

A) Data stored in hard disk
B) Information about queries
C) Data about data 
D) Raw data only

---

9. Which of the following is a DML command?

A) CREATE
B) INSERT 
C) GRANT
D) COMMIT

---

10. Which of the following is used to define database schema?

A) DML
B) DDL 
C) DCL
D) TCL

---

11. Which of the following is a DBMS software?

A) Windows
B) MS Word
C) MySQL 
D) GitHub

---

12. Which one of the following ensures only authorized users access the database?

A) Query Optimizer
B) Concurrency Control
C) Authorization Manager 
D) Buffer Manager

---

13. What does SQL stand for?

A) Structured Question Language
B) Structured Query Language 
C) System Query Language
D) Sequential Query Logic

---

14. Which language is used to control access to data in a DBMS?

A) DCL 
B) DML
C) DDL
D) TCL

---

15. COMMIT and ROLLBACK are part of which category of SQL commands?

A) DCL
B) DML
C) DDL
D) TCL