Database Management System (DBMS) Notes
1. Introduction to DBMS
Definition: A Database Management System (DBMS) is software that interacts with users,
applications, and the database itself to capture and analyze data.
Purpose: It enables efficient management and retrieval of data in a structured format.
Advantages:
- Reduces data redundancy
- Ensures data integrity and consistency
- Provides security
- Facilitates concurrent access
2. Database Architecture
Levels of Architecture:
- Physical Level: Describes how data is physically stored.
- Logical Level: Describes what data is stored and the relationships among data.
- View Level: Describes only a part of the database relevant to the user.
Data Models:
- Relational Model: Represents data in tables.
- Hierarchical Model: Data is organized in a tree-like structure.
- Network Model: Data is represented using graphs.
3. Entity-Relationship Model
ER Model:
- ER Diagram: A graphical representation of entities and their relationships.
Keys:
- Primary Key: Uniquely identifies a tuple in a table.
- Candidate Key: A minimal superkey.
- Foreign Key: An attribute linking two tables.
4. Relational Model
Schema and Instance:
- Schema: Structure of a database.
- Instance: A snapshot of data at a given time.
Integrity Constraints:
- Primary Key Constraint: Ensures uniqueness.
- Foreign Key Constraint: Maintains referential integrity.
5. SQL (Structured Query Language)
Components:
- DDL (Data Definition Language): CREATE, DROP, ALTER.
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
- DCL (Data Control Language): GRANT, REVOKE.
Examples:
- SELECT * FROM table_name;
- INSERT INTO table_name (col1, col2) VALUES (val1, val2);
6. Normalization
Process of organizing data to minimize redundancy.
Normal Forms:
- 1NF: Ensures atomic values.
- 2NF: Removes partial dependencies.
- 3NF: Removes transitive dependencies.
- BCNF: A stricter version of 3NF.
7. Transaction Management
ACID Properties:
- Atomicity: Ensures all operations of a transaction are completed.
- Consistency: Maintains database integrity.
- Isolation: Ensures transactions do not interfere with each other.
- Durability: Ensures changes are permanent.
Concurrency Control:
- Locking Mechanisms
- Timestamp Ordering
8. Indexing and Hashing
Indexing:
- Speeds up data retrieval.
- Types: Single-Level, Multi-Level, Clustered.
Hashing:
- Maps data to a fixed-size value using a hash function.
9. File Organization
Methods:
- Sequential Access: Data is stored sequentially.
- Clustered Access: Related data is stored together.
- Multi-Level Indexing: Reduces the number of I/O operations.
10. Distributed Databases
Characteristics:
- Data is distributed across multiple sites.
Advantages:
- Increased reliability and performance.
Challenges:
- Synchronization and consistency issues.
11. NoSQL Databases
Overview:
- Designed for unstructured and semi-structured data.
- Types: Document-based, Key-Value, Column-family, Graph-based.
Use Cases:
- Real-time analytics, IoT applications, Big Data.
