RDBMS - In-Depth Class Notes
1. Introduction to RDBMS
RDBMS (Relational Database Management System) is a type of DBMS based on the
relational model proposed by E.F. Codd.
Stores data in tables (relations) consisting of rows (tuples) and columns
(attributes).
Provides abstraction, consistency, and powerful querying capabilities.
Key Features:
Data is organized into relations (tables).
Uses SQL (Structured Query Language).
Ensures ACID properties.
Enforces integrity constraints.
2. Database Design
2.1 Entity-Relationship (ER) Model:
Visual representation of entities and relationships.
Entity: Object in the real world (e.g., Student).
Attribute: Property of an entity (e.g., Name, Age).
Relationship: Association among entities.
Types:
One-to-One
One-to-Many
Many-to-Many
2.2 Normalization:
Process of organizing data to reduce redundancy.
Normal Forms:
1NF: Atomic attributes.
2NF: No partial dependency (applies to composite keys).
3NF: No transitive dependency.
BCNF: Stronger version of 3NF.
3. SQL (Structured Query Language)
3.1 Data Definition Language (DDL):
CREATE , ALTER , DROP , TRUNCATE
Defines structure of database objects.
3.2 Data Manipulation Language (DML):
SELECT , INSERT , UPDATE , DELETE
Manipulates existing data.
3.3 Data Control Language (DCL):
GRANT , REVOKE
Controls access to data.
3.4 Transaction Control Language (TCL):
COMMIT , ROLLBACK , SAVEPOINT
Manages changes made by DML statements.
3.5 Joins:
Combine rows from two or more tables.
INNER JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN
FULL JOIN
SELF JOIN
4. Keys and Constraints
Primary Key: Uniquely identifies a tuple in a relation.
Foreign Key: Attribute in one table referencing primary key in another.
Candidate Key: Set of attributes that uniquely identify a tuple.
Super Key: Superset of a candidate key.
Unique, Not Null, Check, Default Constraints: Maintain data integrity.
5. Transactions and Concurrency
Transaction: A sequence of operations performed as a single logical unit.
ACID Properties:
Atomicity: All or nothing.
Consistency: Maintains valid state.
Isolation: Intermediate state not visible.
Durability: Changes persist after commit.
Concurrency Control:
Ensures correctness in concurrent transaction execution.
Techniques:
Lock-based protocols (2PL)
Timestamp ordering
Optimistic concurrency control
6. Indexing and Optimization
Index: Data structure to speed up retrieval.
Types: B-Tree, Hash, Composite
Query Optimization:
Execution plans, cost estimation, reordering joins
7. Storage and File Structures
Heap Files: Unordered records.
Sorted Files: Sorted on some attribute.
Hashing: Maps keys to bucket numbers.
Clustering: Related rows stored together.
8. Triggers and Views
Triggers: Actions automatically performed in response to events.
BEFORE , AFTER triggers on INSERT , UPDATE , DELETE
Views: Virtual tables created by querying base tables.
Useful for abstraction and access control.
9. PL/SQL and Stored Procedures
PL/SQL: Procedural language extension to SQL (used in Oracle).
Stored Procedures: Precompiled SQL statements stored in the database.
Functions: Return a single value.
Cursors: Control row-by-row processing.
10. Advanced Topics
Normalization vs Denormalization
NoSQL vs RDBMS
Distributed Databases
Replication and Sharding
Data Warehousing and OLAP
Note: Practice with a real RDBMS like MySQL or PostgreSQL and work on designing
schemas, writing queries, and managing transactions to build expertise. Reference:
"Database System Concepts" by Silberschatz, Korth, and Sudarshan.
