Database Management System Notes
UNIT I: DBMS Fundamentals
Introduction to DBMS
What is Data? Raw, unorganized facts.
What is a Database? Organized collection of related data.
DBMS Definition: Software to create, organize, manage, and maintain databases.
File System vs DBMS: DBMS provides abstraction, crash recovery, and better data consistency than file systems.
Applications
Used in telecom (call tracking), banking (customer info, transactions), industry, sales, airlines (reservations), and education (student/staff/attendance data).
Characteristics
Data stored in tables.
Reduced redundancy via normalization.
Data consistency for concurrent updates by multiple users.
View of Data
Abstraction Levels: Physical (storage), logical (structure), and view (user perception).
Schema: Design (structure) of the database.
Instance: Data at a particular time.
DBMS Architecture
Single-tier: Database and application on same machine.
Two-tier: Client/server separation, uses JDBC/ODBC.
Three-tier: Middle application server layer between client and DBMS.
Database Models
Hierarchical: Tree-like parent-child.
Network: Graph; supports many-to-many.
Relational: Table-based; most used; SQL.
Entity-Relationship (ER): Real-world entities and their relationships in a diagram.
Database Languages
DDL: CREATE, ALTER, DROP (structure).
DML: INSERT, UPDATE, DELETE, SELECT (data).
DCL: GRANT, REVOKE (permissions).
TCL: COMMIT, ROLLBACK (transactions).
Users and Administrators
Naive users: Use prebuilt apps (e.g., ATM).
Application programmers: Build DB apps.
Sophisticated/specialized users: Write queries directly, design expert systems.
DBA: Define schema, enforce integrity, manage storage and user permissions.
Keys
Types: Super, primary, candidate, alternate, foreign, compound, composite, surrogate.
Purpose: Unique identification and relationship enforcement.
SQL Example: PRIMARY KEY, FOREIGN KEY definitions.
ER Model
Entities: Rectangles; e.g., Student, Employee.
Attributes: Ellipses (simple, composite, multi-valued, derived).
Relationships: Diamonds; cardinality: one-to-one, one-to-many, many-to-many.
Integrity Constraints: Domain (value type), entity (primary key not null), referential (foreign key must match).
Design Issues: Entity vs. attribute, binary vs. n-ary relationships.
UNIT II: Relational Model & SQL
Relational Algebra
Basic Operators: Select (σ), project (π), union, set difference, Cartesian product, rename.
Joins: Inner, left, right, full; division for queries like ‘ALL’.
Relational Calculus: Tuple and domain versions for expressing queries non-procedurally.
SQL & Its Structure
SELECT...FROM...WHERE: Query basics.
GROUP BY, HAVING, ORDER BY: For aggregation and sorting.
Set Operations: UNION, UNION ALL, INTERSECT, MINUS.
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX.
Nested Queries: Subqueries inside WHERE, FROM, or SELECT clauses.
Views
Definition: Virtual table via query.
Use Cases: Security, abstraction, simplified joins.
Management: CREATE VIEW, DROP VIEW.
Triggers
Definition: Procedures that auto-fire on events (e.g., INSERT/UPDATE).
Syntax: BEFORE/AFTER, row/table level.
Uses: Automatic updates, logging, integrity.
Stored Procedures
Definition: Precompiled task blocks with IN, OUT, INOUT parameters.
Creation/Execution: CREATE PROCEDURE, then EXECUTE.
UNIT III: Database Design and Normalization
Decomposition and Dependency Preservation
Decomposition: Splitting tables to remove redundancy.
Lossless vs. Lossy Decomposition: Whether original data can be fully reconstructed.
Dependency Preservation: Ensuring functional dependencies aren't lost.
Normal Forms
1NF: Only atomic (single-valued) attributes.
2NF: No partial dependency of non-prime attributes on candidate keys.
3NF: No transitive dependency of non-prime attributes.
BCNF: All determinants are candidate keys.
4NF: Removal of multi-valued dependencies.
5NF: Eliminates join dependencies.
Practical Issues
Anomalies: Update, insert, delete issues with bad designs.
Examples: Employee-course splits, school courses and fees.
UNIT IV: Transactions, Concurrency, and Recovery
Transactions & ACID Properties
Atomicity: All or nothing.
Consistency: Valid state before/after.
Isolation: No interference between transactions.
Durability: Committed results are permanent.
Schedules & Serializability
Serial Schedules: One at a time.
Non-serial: Interleaved, can lead to conflicts.
Conflict/View Serializability: To ensure concurrent executions act like serial ones.
Recoverability: Rollbacks/commits are coordinated to keep DB consistent.
Isolation Levels
Read Uncommitted: Allows dirty reads.
Read Committed/Repeatable Read/Serializable: Increasingly strict isolation.
Failures & Recovery
Types: Transaction, system, disk failures.
Recovery: Log-based (store actions prior to changes), shadow paging.
Concurrency Problems: Lost update (write-write), dirty read (read uncommitted), unrepeatable read (read-after-write).
Concurrency Control Protocols
Lock-based: Shared/exclusive, two-phase locking (growing/shrinking phase), strict 2PL.
Timestamp-based: Ordered by creation time.
Validation-based: Optimistic, checks serializability before commit.
Multiple Granularity
Lock granularity: Database > area > file > record.
Recovery Techniques
Checkpoints: Save consistent DB snapshots periodically.
ARIES Algorithm: Write-ahead logging for robust recovery.
Remote Backup: Online/offline backups for disaster recovery.
0 Comments