DAY -1
DBMS BASIC QUESTION
1 Understanding DBMS with an Example
DBMS (Database Management System) is software that helps in managing
and organizing data efficiently. It allows users to store, retrieve, update, and
delete data easily while ensuring data consistency, security, and integrity.
1.1 Example of DBMS
Imagine a library management system where details about books, stu-
dents, and issued books need to be maintained.
Without DBMS (File-based system):
• The library keeps records in different files (Excel sheets, text files).
• Searching for a particular book may take time.
• If a student updates their phone number in one file but not in another,
there is data inconsistency.
• There could be data redundancy (same data stored multiple times in
different files).
With DBMS:
• A database is created with multiple related tables (Books, Students,
Issued Books).
• A DBMS like MySQL, PostgreSQL, or Oracle ensures that updates are
applied across all relevant data.
• Searching for books, issuing books, or checking availability is fast and
efficient.
• Data consistency and security are maintained.
1
2 What is RDBMS?
RDBMS (Relational Database Management System) is a type of DBMS that
stores data in a structured format using tables with relationships between
them.
2.1 Example of RDBMS
Continuing with the Library Management System, we can structure our data
into tables:
Book ID Title Author Available Copies
101 Introduction to DBMS R. Elmasri 5
102 SQL Fundamentals John Patrick 3
Student ID Name Phone
201 Ayush Gupta 9876543210
202 Raj Sharma 9123456789
Issue ID Student ID Book ID Issue Date Return Date
1 201 101 2024-03-01 2024-03-10
2 202 102 2024-03-05 2024-03-15
3 Issues with Traditional File-Based Systems
and How DBMS Solves Them
Traditional file-based systems store data in separate files without a proper
structure or relationship between them. This approach has several limitations
that make Database Management Systems (DBMS) a better choice.
3.1 1. Lack of Indexing →Slow Data Access
Problem in File-Based System:
• In a traditional file system, data is stored in multiple files, and there is
no proper indexing.
• Searching for a record requires scanning the entire file, which is time-
consuming.
Example:
2
• Searching for a student in a file with 10,000 records takes a long time.
How DBMS Solves It:
CREATE INDEX student_index ON students(name);
SELECT * FROM students WHERE name = ’Ayush Gupta’;
3.2 2. Data Redundancy and Inconsistency
Problem in File-Based System:
• The same data is stored in multiple files, leading to redundancy and
inconsistency.
How DBMS Solves It:
CREATE TABLE patients (
PatientID INT PRIMARY KEY,
Name VARCHAR(50),
Address VARCHAR(100)
);
CREATE TABLE billing (
BillID INT PRIMARY KEY,
PatientID INT,
AmountDue DECIMAL(10,2),
FOREIGN KEY (PatientID) REFERENCES patients(PatientID)
);
3.3 3. Difficulty in Data Access & Retrieval
How DBMS Solves It:
SELECT * FROM employees WHERE salary > 50000;
4 Conclusion: Why DBMS is Better
Issue in File System Solution in DBMS
Slow data access Uses indexing for fast search
Redundant data Eliminates redundancy using tables
Difficult retrieval Simple SQL queries
No concurrency Multiple users work safely
No security Constraints ensure valid data
3
Thus, DBMS is the better choice because it provides efficient, consistent,
secure, and scalable data management. article longtable array booktabs list-
ings
SQL Commands and Categories
5 Different Languages in DBMS
A Database Management System (DBMS) consists of several languages that
help in interacting with the database. These languages can be classified as
follows:
• Data Definition Language (DDL): Defines the structure and schema
of the database.
• Data Manipulation Language (DML): Handles operations related
to data retrieval, insertion, updating, and deletion.
• Data Control Language (DCL): Manages access and permissions
in the database.
• Transaction Control Language (TCL): Ensures data integrity and
consistency during transactions.
6 Data Definition Language (DDL)
DDL is responsible for defining the database structure and schema. The
commands in this category are used to create, modify, and delete database
objects such as tables, indexes, and views.
Commands in DDL:
CREATE – Used to create a new database object (table, index, view,
etc.). CREATE TABLE Students ( ID INT PRIMARY KEY, Name VAR-
CHAR(50), Age INT, Course VARCHAR(30) );
ALTER – Modifies an existing database object (e.g., adding/deleting a
column). ALTER TABLE Students ADD Email VARCHAR(50);
DROP – Deletes an existing database object (table, index, view, etc.).
DROP TABLE Students;
TRUNCATE – Removes all records from a table but keeps its structure.
TRUNCATE TABLE Students;
4
RENAME – Renames a database object. RENAME TABLE Students
TO StudentI nf o;
7 Data Manipulation Language (DML)
DML is used to perform operations on the data stored in the database, such
as retrieval, insertion, updating, and deletion.
Commands in DML:
SELECT – Retrieves data from a table. SELECT * FROM Students;
INSERT – Adds new records to a table. INSERT INTO Students (ID,
Name, Age, Course) VALUES (1, ’John’, 20, ’B.Tech’);
UPDATE – Modifies existing records in a table. UPDATE Students
SET Age = 21 WHERE ID = 1;
DELETE – Removes specific records from a table. DELETE FROM
Students WHERE ID = 1;
8 Data Control Language (DCL)
DCL deals with access control and permissions in a database.
Commands in DCL:
GRANT – Provides specific privileges to users. GRANT SELECT, IN-
SERT ON Students TO user1;
REVOKE – Removes privileges from users. REVOKE INSERT ON
Students FROM user1;
9 Transaction Control Language (TCL)
TCL manages transactions in a database to ensure data integrity and con-
sistency.
Commands in TCL:
COMMIT – Saves all the changes made in a transaction permanently.
COMMIT;
5
ROLLBACK – Undoes all changes made in the current transaction.
ROLLBACK;
SAVEPOINT – Creates a checkpoint in a transaction to which a roll-
back can be performed. SAVEPOINT A; UPDATE Students SET Age =
22 WHERE ID = 1; ROLLBACK TO A;
10 Summary Table
Language Purpose Example Commands
DDL (Data Definition) Defines database structure CREATE, ALTER, DRO
TRUNCATE, RENAME
DML (Data Manipulation) Modifies data SELECT, INSERT, UPDAT
DELETE
DCL (Data Control) Controls access & permissions GRANT, REVOKE
TCL (Transaction Control) Manages transactions COMMIT, ROLLBACK, SAV
POINT
