Comprehensive Guide to Database Management Systems (DBMS)
1. Introduction to DBMS
A Database Management System (DBMS) is software designed to define, create, manage,
and control access to a database. It provides users and programmers with a systematic way
to create, retrieve, update, and manage data. DBMS acts as an interface between end-users
and databases, ensuring data is consistently organized and easily accessible.
Core Functions:
• Data storage, retrieval, and update
• Transaction management
• Access control and security
• Backup and recovery
2. Traditional File Systems vs DBMS
Feature Traditional File System DBMS
Redundancy High Controlled
Access Manual, application-dependent Standardized query language (SQL)
Integrity Poor enforcement Constraints enforce integrity
Concurrency Difficult Managed by DBMS
Security Low High (authentication, authorization)
DBMS offers several improvements over traditional file systems, especially in terms of
reducing data duplication, improving data integrity, and simplifying access.
3. Database Models
a. Hierarchical Model
• Organizes data in a tree structure with one-to-many relationships.
• Each child has only one parent.
• Good for representing organizational charts.
b. Network Model
• More flexible than hierarchical.
• Supports many-to-many relationships.
• Data represented using graph structures.
c. Relational Model
• Most widely used.
• Organizes data into tables (relations) of rows and columns.
• Uses keys to establish relationships between tables.
d. Object-Oriented Model
• Integrates object-oriented programming with databases.
• Data is stored in the form of objects, just like in OOP.
4. Relational Model Concepts
• Relation: A table with columns and rows.
• Tuple: A row in a table.
• Attribute: A column in a table.
• Degree: Number of attributes in a relation.
• Cardinality: Number of tuples in a relation.
• Domain: The set of possible values for an attribute.
5. Types of Keys in DBMS
• Super Key: Any combination of attributes that uniquely identify a row.
• Candidate Key: Minimal super key; no unnecessary attributes.
• Primary Key: Chosen candidate key; uniquely identifies a record.
• Foreign Key: References a primary key in another table.
• Composite Key: A key consisting of two or more attributes.
• Alternate Key: Candidate keys not chosen as the primary key.
6. Entity-Relationship (ER) Model
• Entity: Real-world object (e.g., Student, Teacher).
• Attributes: Characteristics of entities (e.g., name, age).
• Relationships: Connections between entities (e.g., teaches, enrolls).
• Generalization: Abstracting common features from multiple entities.
• Specialization: Creating sub-entities from a main entity.
• Aggregation: Treating a relationship as an entity.
7. Normalization
The process of organizing data to reduce redundancy and ensure integrity.
Forms of Normalization:
• 1NF: Atomic (indivisible) values, no repeating groups.
• 2NF: Meets 1NF, and no partial dependency.
• 3NF: Meets 2NF, and no transitive dependency.
• BCNF: Stricter version of 3NF; every determinant must be a candidate key.
• 4NF: Removes multi-valued dependencies.
• 5NF: Removes join dependencies.
8. SQL (Structured Query Language)
Categories of SQL Commands:
• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
SQL enables querying, inserting, updating, and deleting data in relational databases.
9. Relational Algebra
A procedural query language used to operate on relations.
Key Operations:
• Select (σ): Filters rows.
• Project (π): Filters columns.
• Union (∪): Combines results.
• Set Difference (−): Finds differences.
• Cartesian Product (×): Combines tuples.
• Join (⨝): Merges relations based on a condition.
• Rename (ρ): Renames relations.
10. Functional Dependencies
A functional dependency (FD) expresses a relationship between two sets of attributes in a
relation.
If A → B, then the value of A determines the value of B.
• Essential for normalization.
• Helps identify keys.
11. Transactions and ACID Properties
A transaction is a logical unit of work. It must be:
• Atomic: All or nothing.
• Consistent: Leaves the database in a valid state.
• Isolated: Executed without interference from others.
• Durable: Changes persist even after failure.
12. Concurrency Control
Mechanisms to handle simultaneous database operations to ensure correctness.
Techniques:
• Lock-based protocols: Shared vs Exclusive Locks
• Two-Phase Locking (2PL): Growing and shrinking phases
• Timestamp ordering: Each transaction assigned a timestamp
13. Indexing
Indexing improves data retrieval speed.
• Primary Index: On primary key.
• Secondary Index: On non-primary attributes.
• Dense Index: Index entry for every record.
• Sparse Index: Index for selected records only.
14. File Organization
Types:
• Heap File: Unordered records.
• Sorted File: Ordered by one attribute.
• Hash File: Uses a hash function to determine location.
Each method affects the speed of access and data manipulation.
15. Query Processing and Optimization
DBMS transforms SQL queries into efficient execution plans.
Stages:
• Parsing and translation
• Optimization: Cost-based or heuristic
• Evaluation
16. Joins
Used to combine rows from two or more tables.
• Inner Join: Returns matching rows.
• Left Join: All from left, matched from right.
• Right Join: All from right, matched from left.
• Full Join: All from both tables.
• Natural Join: Based on same-named columns.
17. Views
A view is a virtual table based on the result of a query.
Benefits:
• Simplifies queries
• Hides data complexity
• Improves security by restricting column access
18. Recovery Techniques
Ensures database consistency in case of failure.
• Log-based recovery
• Checkpointing
• Shadow Paging
19. Concurrency Issues
Problems that can arise during concurrent execution:
• Lost Update: Overwrites of updates by two transactions.
• Dirty Read: Reading uncommitted data.
• Unrepeatable Read: Data changes between reads.
• Phantom Read: New records added during transaction execution.
20. Deadlock in DBMS
Occurs when transactions wait endlessly for resources held by each other.
Prevention Techniques:
• Wait-die and wound-wait
• Timeout-based methods
• Deadlock detection and recovery
21. DBMS Security
Protects data from unauthorized access and corruption.
Methods:
• User authentication
• Role-based access control
• Data encryption