Database Management Systems (DBMS) - Complete Guide
Last Updated: August 19, 2026 | Marks Weightage: ~10 Marks | BPSC CS Teacher
Master Database Management Systems with this comprehensive guide for the BPSC Computer Science Teacher exam.
🏆 NextVision Infotech 📍 NH-82, Singathiya, Gaya, Bihar 805131 | 📞 +91 7739299366
1. DBMS Architecture
🏛️ 3-Tier Architecture
- External Level: User view (UI)
- Conceptual Level: Logical schema (Tables)
- Internal Level: Physical storage (Files)
🗂️ Schema vs Instance
- Schema: Database structure (Design)
- Instance: Data in database at a moment
2. Data Models
📊 Hierarchical
Tree-like structure
📊 Network
Graph-like structure
📊 Relational
Tables with rows and columns
📊 Object-Oriented
Objects with attributes
3. Relational Model
- Relation: Table
- Attribute: Column
- Tuple: Row
- Keys: Super Key, Candidate Key, Primary Key, Foreign Key
📌 Example: Student(StudentID, Name, Age, CourseID)
4. SQL Commands
📝 DDL (Data Definition Language)
- CREATE: Create table/database
- ALTER: Modify table structure
- DROP: Delete table/database
- TRUNCATE: Remove all data
📝 DML (Data Manipulation Language)
- SELECT: Retrieve data
- INSERT: Add new row
- UPDATE: Modify existing data
- DELETE: Remove row
📌 SQL Clauses
- WHERE: Filter rows
- GROUP BY: Group rows
- HAVING: Filter groups
- ORDER BY: Sort results
5. SQL Functions
🧮 Math
- SUM(), AVG()
- MAX(), MIN()
- COUNT()
📝 Text
- UPPER(), LOWER()
- SUBSTRING()
- LENGTH()
📅 Date
- NOW(), CURDATE()
- YEAR(), MONTH()
- DATEDIFF()
💡 Exam Tip: Practice writing SQL queries with these functions.
6. SQL Joins
| Join Type | Description |
|---|---|
| INNER JOIN | Returns only matching rows from both tables |
| LEFT JOIN | Returns all rows from left table + matching from right |
| RIGHT JOIN | Returns all rows from right table + matching from left |
| FULL OUTER JOIN | Returns all rows from both tables |
| Equi-Join | Join with equality condition |
| Natural Join | Joins on columns with same name |
7. ER Diagrams
- Entity: Real-world object (Rectangle)
- Attribute: Property of entity (Oval)
- Relationship: Association between entities (Diamond)
- Cardinality: 1:1, 1:N, M:N
📌 Example: Student (Entity) → Enrolls (Relationship) → Course (Entity)
8. Normalization
📊 Normal Forms
- 1NF: Atomic values, no repeating groups
- 2NF: 1NF + Fully functional dependency
- 3NF: 2NF + No transitive dependency
- BCNF: 3NF + Every determinant is a candidate key
📌 Example
Student(StudentID, Name, Course, Instructor)
- 1NF: Remove repeating groups
- 2NF: Remove partial dependencies
- 3NF: Remove transitive dependencies
9. Transactions & ACID Properties
Transaction: A single logical unit of work (e.g., transferring money)
💎 ACID Properties
- Atomicity: All or nothing
- Consistency: Valid state before and after
- Isolation: Transactions don't interfere
- Durability: Committed changes persist
🔒 Concurrency Control
- Locking (Shared/Exclusive)
- Two-phase locking (2PL)
- Timestamp ordering
10. NoSQL
📄 Document
MongoDB, Firebase
🔑 Key-Value
Redis, DynamoDB
📊 Column-Family
Cassandra, HBase
🔗 Graph
Neo4j, ArangoDB
11. Frequently Asked Questions
❓ What is the difference between DBMS and RDBMS?
DBMS is general; RDBMS follows the relational model and uses SQL.
❓ What is the difference between Primary Key and Foreign Key?
Primary Key uniquely identifies rows in its table; Foreign Key references a Primary Key in another table.
❓ What are the ACID properties?
Atomicity, Consistency, Isolation, Durability
❓ What is the difference between DELETE and TRUNCATE?
DELETE is DML (can be rolled back), TRUNCATE is DDL (cannot be rolled back).