DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions (MCQs)DBMS Lab Programs
A database is an organized collection of structured data stored electronically so that it can be easily accessed, managed, and updated. Databases store data...
What is a Database?
A database is an organized collection of structured data stored electronically so that it can be easily accessed, managed, and updated. Databases store data in a way that enables efficient retrieval and manipulation.
Examples of databases in everyday life:
- A library catalog storing information about books
- A hospital system storing patient records
- An e-commerce platform storing products and orders
- A university storing student and course information
History of Database Systems
| Era | System | Characteristic |
|---|---|---|
| 1960s | File-based systems | Data stored in flat files |
| Late 1960s | IMS (IBM) | First hierarchical DBMS |
| 1970s | CODASYL | Network model databases |
| 1970 | E.F. Codd's paper | Relational model proposed |
| 1980s | Oracle, DB2 | Commercial relational DBMS |
| 1986 | SQL standardized | ANSI SQL standard published |
| 2000s | Open source | MySQL, PostgreSQL growth |
| 2010s | NoSQL era | MongoDB, Cassandra for big data |
| Present | NewSQL/Cloud | CockroachDB, Amazon Aurora |
File-Based System vs. DBMS
Before DBMS, data was stored in flat files managed by application programs.
Problems with File-Based Systems
Application A
Customer.txt
Redundant copy
Application B
Customer.csv
Redundant copy
Application C
Customer.dat
Redundant copy
- Data Redundancy: Same data stored in multiple files → wastes space and causes inconsistency.
- Data Inconsistency: Updating one file doesn't update others.
- Difficult Data Access: No standard query language — programs must be written for each query.
- Data Isolation: Data scattered in different formats; hard to access together.
- Integrity Problems: Enforcing rules (e.g., age must be > 0) is entirely up to application code.
- Concurrency Issues: No mechanism for multiple users to access data simultaneously without conflict.
- Security Problems: No fine-grained access control.
- No Recovery: System crashes can corrupt files with no automatic recovery.
How DBMS Solves These Problems
| Problem | DBMS Solution |
|---|---|
| Redundancy | Central storage, normalization |
| Inconsistency | Single authoritative source |
| Poor access | SQL query language |
| Integrity | Constraints (CHECK, FK, NOT NULL) |
| Concurrency | Locking, MVCC protocols |
| Security | Roles, GRANT/REVOKE |
| Recovery | Transaction logs, checkpoints |
Components of a DBMS
DBMS
| Query Processor | Storage Manager | |||
|---|---|---|---|---|
| - Parser | - Buffer Manager | |||
| - Optimizer | - File Manager | |||
| - Executor | - Auth Manager | |||
| Transaction Mgr | Data Dictionary | |||
| - Concurrency Ctrl | (System Catalog) | |||
| - Recovery Mgr |
- Query Processor: Parses, optimizes, and executes SQL queries
- Storage Manager: Manages physical file I/O and buffer pool
- Transaction Manager: Ensures ACID properties; handles concurrency and recovery
- Data Dictionary (System Catalog): Stores metadata — table schemas, indexes, user info, constraints
Purpose of a DBMS
- Data Storage and Retrieval — Store large volumes of data and retrieve them efficiently
- Data Integrity — Ensure data accuracy through constraints
- Data Security — Control who can read, write, or modify data
- Concurrent Access — Allow multiple users simultaneously without conflicts
- Data Independence — Applications unaffected by changes in data storage structure
- Backup and Recovery — Automatic mechanisms to restore data after failures
Continue the lesson path
Study next in this module
01Unit 1Entity-Relationship (ER) ModelThe Entity-Relationship (ER) model is a high-level conceptual data model developed by Peter Chen in 1976. It provides a graphical representation of the...02Unit 1Keys in DBMSA key is an attribute or a set of attributes that is used to uniquely identify tuples (rows) in a relation (table). Keys are fundamental to the relational...03Unit 1ER DiagramsAn Entity-Relationship (ER) Diagram is a visual representation of the entities, attributes, and relationships in a database design. It is the primary tool...04Unit 1Relationships and CardinalityA relationship describes an association or connection between two or more entities in the ER model. In the relational model, relationships are implemented...