🗄️ DATABASE MANAGEMENT SYSTEM
(DBMS) – DETAILED STUDY NOTES
1. Introduction to DBMS
A Database Management System (DBMS) is software that allows users to define, create,
maintain, and control access to databases. It provides an organized and systematic way to
store, retrieve, and manipulate data efficiently.
1.1 Key Concepts
● Data: Raw facts and figures.
● Information: Processed data that is meaningful.
● Database: Collection of logically related data.
● DBMS: A software layer that manages data and ensures integrity and security.
1.2 Advantages of DBMS
● Reduces data redundancy and inconsistency.
● Enables data sharing and concurrent access.
● Enforces data integrity and security.
● Facilitates backup and recovery.
● Provides data independence from application programs.
1.3 Disadvantages
● High initial setup cost.
● Complexity in management.
● Requires skilled personnel.
2. Database Models
2.1 Hierarchical Model
Data organized in a tree-like structure with parent-child relationships (one-to-many).
Example: File systems.
2.2 Network Model
Represents data as records connected by links (many-to-many relationships).
Example: CODASYL model.
2.3 Relational Model
Data is stored in tables (relations) consisting of rows (tuples) and columns (attributes).
Developed by E.F. Codd (1970).
This is the most widely used model (used by Oracle, MySQL, PostgreSQL, etc.).
2.4 Object-Oriented Model
Combines object-oriented programming with database concepts — stores data as objects with
attributes and methods.
3. DBMS Architecture
3.1 Three-Level Architecture
Defined by ANSI/SPARC:
1. External Level (View Level): User’s view of the data.
2. Conceptual Level: Logical structure of the entire database.
3. Internal Level: Physical storage details.
Data Independence:
● Logical Independence: Ability to change the logical schema without affecting external
schema.
● Physical Independence: Ability to change storage structure without affecting logical
schema.
4. Keys in Relational Model
Keys uniquely identify tuples (rows) in a relation.
● Super Key: A set of attributes that uniquely identifies a row.
● Candidate Key: Minimal super key; no unnecessary attributes.
● Primary Key: Chosen candidate key; uniquely identifies each tuple.
● Alternate Key: Candidate keys not chosen as primary.
● Foreign Key: Refers to the primary key of another table (establishes referential
integrity).
● Composite Key: Combination of two or more attributes to uniquely identify a record.
Example:
STUDENT (RollNo, Name, DeptID)
DEPARTMENT (DeptID, DeptName)
Here, DeptID in STUDENT is a foreign key referencing DEPARTMENT.
5. Relational Algebra
A procedural query language that operates on relations (tables).
Basic Operations
1. SELECT (σ): Filters rows based on condition.
○ σ(Dept = 'CSE')(STUDENT)
2. PROJECT (π): Chooses specific columns.
○ π(Name, Dept)(STUDENT)
3. UNION (∪): Combines tuples from two relations, removes duplicates.
4. SET DIFFERENCE (−): Tuples in one relation but not in another.
5. CARTESIAN PRODUCT (×): Combines all tuples from both relations.
6. RENAME (ρ): Renames relations or attributes.
7. JOIN: Combines related tuples from two relations.
○ Natural Join: Automatically joins on same-named attributes.
○ Theta Join: Joins using a condition.
6. SQL – Structured Query Language
SQL is a standard language used to interact with relational databases.
6.1 SQL Categories
1. DDL (Data Definition Language): Defines database structure.
Commands: CREATE, ALTER, DROP, TRUNCATE.
2. DML (Data Manipulation Language): Manages data.
Commands: INSERT, UPDATE, DELETE, SELECT.
3. DCL (Data Control Language): Controls access.
Commands: GRANT, REVOKE.
4. TCL (Transaction Control Language): Manages transactions.
Commands: COMMIT, ROLLBACK, SAVEPOINT.
6.2 Common SQL Commands
CREATE TABLE STUDENT (
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Dept VARCHAR(20)
);
SELECT Name FROM STUDENT WHERE Dept = 'ECE';
UPDATE STUDENT SET Dept = 'CSE' WHERE RollNo = 101;
DELETE FROM STUDENT WHERE RollNo = 102;
6.3 Clauses and Functions
● WHERE: Filters rows.
● ORDER BY: Sorts results.
● GROUP BY: Groups rows by column values.
● HAVING: Filters groups.
● Aggregate Functions: COUNT, SUM, AVG, MAX, MIN.
7. Normalization
Normalization is the process of organizing data to reduce redundancy and improve integrity.
7.1 Normal Forms
Normal Form Definition Removes
1NF Atomic (indivisible) values only Repeating groups
2NF 1NF + no partial dependency Partial dependency
3NF 2NF + no transitive dependency Transitive dependency
BCNF Stronger 3NF Anomalies due to overlapping candidate
keys
4NF Removes multivalued Multi-valued dependencies
dependencies
5NF Removes join dependencies Complex redundancy
Example:
If a table contains StudentID → Course and Course → Instructor,
then Instructor indirectly depends on StudentID — violating 3NF.
8. Integrity Constraints
Constraints enforce the correctness and validity of data.
1. Domain Constraint: Values must belong to a defined domain.
2. Entity Integrity: Primary key cannot be NULL or duplicate.
3. Referential Integrity: Foreign key must match a valid primary key.
4. Check Constraint: Enforces a specific condition on column values.
5. Unique Constraint: Ensures all values in a column are distinct.
Example:
CREATE TABLE EMP(
ID INT PRIMARY KEY,
Salary INT CHECK (Salary > 0),
DeptID INT REFERENCES DEPT(DeptID)
);
9. Joins and Subqueries
Joins combine data from multiple tables:
● Inner Join: Returns matching rows.
● Left Join: All rows from left table and matched rows from right.
● Right Join: All rows from right table and matched rows from left.
● Full Join: All rows from both tables.
● Self Join: A table joined with itself.
Subquery: A query within another query.
SELECT Name FROM STUDENT
WHERE DeptID IN (SELECT DeptID FROM DEPARTMENT WHERE DeptName='CSE');
10. Transactions and Concurrency Control
A transaction is a logical unit of work executed completely or not at all.
10.1 ACID Properties
1. Atomicity: All-or-nothing execution.
2. Consistency: Maintains database rules.
3. Isolation: Concurrent transactions do not interfere.
4. Durability: Changes persist after commit.
10.2 Concurrency Control
Prevents conflicts when multiple users access data simultaneously.
Techniques:
● Locking: Ensures mutual exclusion (shared & exclusive locks).
● Timestamp Ordering: Executes in order of timestamps.
● Optimistic Control: Checks conflicts at commit time.
10.3 Recovery Mechanisms
● Commit: Saves all changes permanently.
● Rollback: Reverts uncommitted changes.
● Checkpoint: Intermediate save state for faster recovery.
11. Views and Indexes
11.1 Views
A view is a virtual table created by a query. It doesn’t store data physically.
CREATE VIEW CS_Students AS
SELECT Name FROM STUDENT WHERE Dept = 'CSE';
Benefits:
● Simplifies queries.
● Provides security by restricting access.
● Offers logical data independence.
11.2 Indexes
Improve data retrieval speed.
CREATE INDEX idx_name ON STUDENT(Name);
Types:
● Primary Index: Based on primary key.
● Secondary Index: On non-key attributes.
● Clustered Index: Reorders table rows physically.
12. ER Model (Entity-Relationship Model)
A conceptual design tool for databases.
Key Components:
● Entity: Real-world object (e.g., Student, Department).
● Attributes: Characteristics of entity (e.g., Name, ID).
● Relationship: Association among entities (e.g., Enrolled_in).
Types of Relationships:
● One-to-One (1:1)
● One-to-Many (1:N)
● Many-to-Many (M:N)
Mapping to Relational Model:
Each entity → table
Each relationship → foreign key or separate table (for M:N)
13. Database Security
● Authentication: Verifies user identity (username/password).
● Authorization: Determines access rights.
● Encryption: Protects data confidentiality.
● Audit Trails: Tracks access and modifications.
● Backup & Recovery: Protects against data loss.
14. Distributed and Parallel Databases
● Distributed Database: Data stored across multiple sites but managed centrally.
● Parallel Database: Uses multiple processors to execute queries faster.
● Replication: Copies of data at multiple sites for availability.
● Fragmentation: Splits a database into logical parts stored at different locations.
15. Modern Trends
● NoSQL Databases: Handle unstructured and large-scale data (MongoDB, Cassandra).
● NewSQL Databases: Combine NoSQL scalability with SQL consistency.
● Cloud Databases: Hosted and managed on cloud platforms.
● In-Memory Databases: Store data entirely in main memory for faster performance.