DBMS Deep Dive – Step by Step (Before SQL)
1. What is Data, Information, Database, DBMS?
Term Meaning Example
Data Raw facts, unprocessed “Yashu”, 24, “Bangalore”
Information Processed data with meaning “Yashu is 24 years old and lives in Bangalore”
Database Organized collection of data Student DB storing all student info
DBMS Software that stores, manages, and retrieves data MySQL, PostgreSQL, Oracle
Why DBMS?
• Avoids data redundancy
• Maintains consistency
• Allows multiple users
• Provides security
2. Types of Databases
1. Centralized DB → all data in one location.
2. Distributed DB → data spread across multiple sites.
3. Operational / OLTP DB → supports real-time transactions (banking).
4. Data Warehouse / OLAP → for analysis and reporting.
5. Relational DB (RDBMS) → stores data in tables (MySQL, PostgreSQL).
6. NoSQL DB → non-relational, flexible schema (MongoDB, Cassandra).
7. Hierarchical DB → tree-structured (parent-child).
8. Network DB → graph-like structure with links/pointers.
3. DBMS Architecture
Architecture Description
DB + Application + UI on same
1-tier
machine
2-tier Client ↔ Server
Architecture Description
Client ↔ Application Server ↔ DB
3-tier
Server
Components
• Database Engine → core storage/retrieval
• Query Processor → interprets queries
• Transaction Manager → handles transactions & ACID
• Storage Manager → handles files, buffers, indexes
• Metadata → schema & data definitions
4. Schemas and Instances
• Schema → structure/blueprint of DB (tables, columns,
constraints).
• Instance → actual data at a point in time.
• Example:
o Schema: STUDENT(ID, Name, Age, CourseID)
o Instance: (101, “Yashu”, 24, 501)
5. Entity & Attributes
Entity → Real-world object (Student, Employee).
Attribute → Property of entity (columns in table).
Types of Attributes
1. Simple → cannot divide further (Age)
2. Composite → can divide into sub-parts (FullName = First + Last)
3. Derived → calculated from other attributes (Age from DOB)
4. Multivalued → multiple values possible (PhoneNumbers)
5. Key Attribute → uniquely identifies entity (StudentID
5. Entity & Attributes
Entity → Real-world object (Student, Employee).
Attribute → Property of entity (columns in table).
Types of Attributes
1. Simple → cannot divide further (Age)
2. Composite → can divide into sub-parts (FullName = First + Last)
3. Derived → calculated from other attributes (Age from DOB)
4. Multivalued → multiple values possible (PhoneNumbers)
5. Key Attribute → uniquely identifies entity (StudentID)
6. Keys in DBMS
Type Description Example
Super Key Any set of attributes uniquely identifying {StudentID, Name}
Candidate Key Minimal Super Key {StudentID}
Primary Key Chosen candidate key StudentID
Composite Key PK with multiple attributes {OrderID, ProductID}
Foreign Key References PK in another table CourseID in STUDENT table
Alternate Key Candidate key not chosen as PK EmailID
7. Relationships
1. One-to-One (1:1) → One entity maps to one entity (Passport ↔ Person)
2. One-to-Many (1:N) → One entity maps to many (Department ↔ Employees)
3. Many-to-Many (M:N) → Many entities map to many (Students ↔ Courses)
8. Constraints
Constraint Meaning
NOT NULL Column must have value
UNIQUE All values distinct
PRIMARY KEY Unique + Not Null
FOREIGN KEY Ensures referential integrity
CHECK Value must satisfy condition
DEFAULT Auto-fill missing value
9. Types of Data Models
1. Hierarchical → Tree (Parent-Child)
2. Network → Graph (Many-to-Many links)
3. Relational → Tables (Rows & Columns)
4. Entity-Relationship (ER) → Conceptual diagram of entities & relationships
5. Object-Oriented → Stores objects with methods
10. Normalization (Remove Redundancy)
Normal Form Requirement
1NF Atomic values, no repeating groups
2NF 1NF + no partial dependency
3NF 2NF + no transitive dependency
BCNF Stronger than 3NF
Optional: 4NF, 5NF – rarely used
11. Transactions & ACID
Transaction → Logical unit of work (e.g., Bank transfer).
ACID Properties
1. Atomicity → all or nothing
2. Consistency → DB remains valid
3. Isolation → concurrent transactions don’t interfere
4. Durability → committed changes permanent
Types of Transactions
• Single-user, Multi-user
• Read-only, Update
Example:
BEGIN TRANSACTION
UPDATE Account SET Balance = Balance - 500 WHERE AccID = 101;
UPDATE Account SET Balance = Balance + 500 WHERE AccID = 102;
COMMIT;
12. DBMS Operations
1. DDL (Data Definition Language) → Create/Alter/Drop tables
2. DML (Data Manipulation Language) → Insert/Update/Delete/Select data
3. DCL (Data Control Language) → Grant/Revoke permissions
4. TCL (Transaction Control Language) → Commit/Rollback/Savepoint
5. Concurrency Control → Handles multiple users
6. Recovery Operations → Crash recovery, backups
13. DBMS Users
Type Role
DBA Manages DB
Application Programmer Writes DB programs
End User Interacts via apps
System Analyst Designs DB system
14. Extra/Advanced Topics (Optional but useful)
• Indexing → Clustered, Non-clustered
• Views → Virtual tables
• Triggers → Auto actions on insert/update/delete
• Stored Procedures → Predefined SQL routines
• Cursors → Row-by-row processing
• Isolation levels → Serializable, Repeatable Read, Read Committed, Read Uncommitted
• Distributed DB concepts → replication, fragmentation, transparency
Step-by-Step Flow Before SQL
Conceptual Flow:
Real-World Object → Entity → Attributes → Tables → Keys & Relationships → Constraints → Transactions →
Operations → SQL Commands → Result
Example:
• Object = Student
• Entity = STUDENT table
• Attributes = ID, Name, Age
• Primary Key = ID
• Transaction = Insert new student
• Operation = DML (INSERT)
• SQL = INSERT INTO STUDENT VALUES(101, 'Yashu', 24);
DBMS Languages
DBMS languages are sub-languages used to interact with the database. Each serves a different purpose.
1. DDL – Data Definition Language
Purpose: Define or modify the structure of the database (tables, indexes, constraints, schema).
Common Commands:
Command Meaning
CREATE Create a database, table, index, or view
ALTER Modify structure of an existing table or DB object
DROP Delete a table, database, or index
TRUNCATE Delete all data in a table quickly (structure remains)
Example (Conceptual):
CREATE TABLE STUDENT (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
CourseID INT
);
ALTER TABLE STUDENT ADD COLUMN Email VARCHAR(50);
DROP TABLE TEMP_TABLE;
Key point: DDL changes schema, not data itself (though new tables will be empty).
2. DML – Data Manipulation Language
Purpose: Work with data inside the tables (CRUD operations).
Common Commands:
Command Meaning
INSERT Add new records
UPDATE Modify existing records
Command Meaning
DELETE Remove records
SELECT Retrieve data
Example:
INSERT INTO STUDENT VALUES (101, 'Yashu', 24, 501);
UPDATE STUDENT SET Age = 25 WHERE StudentID = 101;
DELETE FROM STUDENT WHERE StudentID = 102;
SELECT * FROM STUDENT;
Key point: DML changes actual data, not table structure.
3. DCL – Data Control Language
Purpose: Control access and permissions of users in the database.
Common Commands:
Command Meaning
GRANT Give permissions to a user/role
REVOKE Remove permissions from a user/role
Example:
GRANT SELECT, INSERT ON STUDENT TO 'teacher';
REVOKE DELETE ON STUDENT FROM 'student';
Key point: DCL ensures security & proper access control.
4. TCL – Transaction Control Language
Purpose: Manage transactions to ensure ACID properties (Atomicity, Consistency, Isolation, Durability).
Common Commands:
Command Meaning
COMMIT Save all changes permanently
ROLLBACK Undo changes made in a transaction
Command Meaning
SAVEPOINT Set a point in transaction to rollback partially
SET TRANSACTION Set properties for a transaction (optional)
Example:
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 500 WHERE AccID = 101;
UPDATE Account SET Balance = Balance + 500 WHERE AccID = 102;
-- If all good
COMMIT;
-- If error occurs
ROLLBACK;
Key point: TCL ensures data consistency and safety.
5. Summary Table of DBMS Languages
Extra Notes for Interviews:
1. SQL itself is not a single-purpose language, it combines DDL + DML + DCL + TCL.
2. DDL commands are auto-committed (changes are permanent immediately).
3. DML commands are controlled via TCL (you can commit or rollback).