🧠 1. Introduction to DBMS
🔹 What is a Database?
A database is an organized collection of related data that can be easily accessed, managed, and updated.
Example:
A college database may contain data about students, teachers, and courses.
🔹 What is DBMS?
A Database Management System (DBMS) is a software that allows users to define, create, maintain, and control access to the database.
Examples: MySQL, Oracle, SQL Server, PostgreSQL, MS Access.
🔹 Need for DBMS
- Avoids data redundancy (duplicate data).
- Ensures data consistency.
- Provides data security and integrity.
- Allows concurrent access by multiple users.
- Offers backup and recovery mechanisms.
🧩 2. Components of DBMS
| Component | Description |
|---|---|
| Hardware | Physical devices – computers, storage, servers. |
| Software | DBMS software (e.g., Oracle, MySQL). |
| Data | Raw facts stored in the database. |
| Users | People who use DBMS (DBA, end users, developers). |
| Procedures | Rules and instructions for operating DBMS. |
👩💻 3. Types of Users in DBMS
| User Type | Description |
|---|---|
| DBA (Database Administrator) | Manages and maintains database systems. |
| Database Designers | Design structure of database (schemas). |
| Application Programmers | Write programs to interact with DBMS. |
| End Users | Access database using queries or applications. |
⚙️ 4. Architecture of DBMS
🔹 Three-Level Architecture (ANSI-SPARC Model)
- Internal Level – Physical storage of data.
- Conceptual Level – Logical structure of entire database.
- External Level – Individual user views.
✅ Goal: Data abstraction — users don’t need to know how data is physically stored.
🧮 5. Data Models
A data model describes how data is logically structured and manipulated.
| Model Type | Description | Example |
|---|---|---|
| Hierarchical Model | Data organized in tree structure (parent-child). | IBM IMS |
| Network Model | Data represented as graph (many-to-many relationships). | CODASYL |
| Relational Model | Data stored in tables (rows & columns). | MySQL, Oracle |
| Object-Oriented Model | Data represented as objects. | OODBMS |
| Entity-Relationship Model | Conceptual model using ER diagrams. | Logical design |
🧾 6. Relational Model
🔹 Basic Terminology
| Term | Meaning |
|---|---|
| Relation | A table. |
| Tuple | A row or record. |
| Attribute | A column or field. |
| Domain | Allowed values for an attribute. |
| Degree | Number of attributes in a relation. |
| Cardinality | Number of tuples (rows) in a relation. |
🔑 7. Keys in DBMS
Keys are used to uniquely identify records in a table.
| Key Type | Description |
|---|---|
| Primary Key | Unique identifier for each record. |
| Candidate Key | Attribute(s) that can qualify as a primary key. |
| Alternate Key | Remaining candidate keys after primary key is chosen. |
| Foreign Key | Attribute that refers to primary key in another table. |
| Composite Key | Combination of two or more fields to uniquely identify a record. |
| Super Key | Any attribute(s) that can uniquely identify a record. |
📊 8. Entity-Relationship (ER) Model
🔹 Entities
Objects or concepts that can have data stored about them.
Example: Student, Teacher, Course.
🔹 Attributes
Properties that describe an entity.
Example: Name, Roll_No, Address.
🔹 Relationships
Associations between entities.
Example: Student – Enrolls – in Course.
🔹 Types of Relationships
| Type | Description | Example |
|---|---|---|
| One-to-One (1:1) | One entity linked to one of another. | Person–Passport |
| One-to-Many (1:N) | One entity linked to many others. | Teacher–Students |
| Many-to-Many (M:N) | Many entities linked to many others. | Students–Courses |
💽 9. Normalization
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
🔹 Normal Forms
| Form | Condition | Example |
|---|---|---|
| 1NF (First Normal Form) | No repeating groups; atomic values only. | Split multiple phone numbers into separate rows. |
| 2NF (Second Normal Form) | 1NF + no partial dependency (non-key depends on part of composite key). | |
| 3NF (Third Normal Form) | 2NF + no transitive dependency (non-key depends only on key). | |
| BCNF (Boyce-Codd Normal Form) | Stronger version of 3NF; every determinant is a candidate key. |
✅ Goal: Eliminate redundancy, anomalies (update, delete, insert).
🔄 10. Denormalization
The process of combining normalized tables to improve query performance (used in data warehouses).
🧠 11. SQL (Structured Query Language)
SQL is the standard language used to interact with relational databases.
🔸 Categories of SQL Commands
| Category | Full Form | Example Commands |
|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE |
| DML | Data Manipulation Language | SELECT, INSERT, UPDATE, DELETE |
| DCL | Data Control Language | GRANT, REVOKE |
| TCL | Transaction Control Language | COMMIT, ROLLBACK, SAVEPOINT |
| DQL | Data Query Language | SELECT |
🔸 Common SQL Commands
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
marks INT
);
INSERT INTO student VALUES (1, 'Amit', 85);
SELECT * FROM student;
UPDATE student SET marks = 90 WHERE roll_no = 1;
DELETE FROM student WHERE roll_no = 1;
🧮 12. Relational Algebra Operations
| Operation | Symbol | Description |
|---|---|---|
| SELECT (σ) | σ | Selects rows from a relation. |
| PROJECT (π) | π | Selects columns from a relation. |
| UNION (U) | ∪ | Combines tuples from two relations. |
| SET DIFFERENCE | – | Tuples in one relation but not the other. |
| CARTESIAN PRODUCT | × | Combines all tuples. |
| JOIN | ⨝ | Combines related tuples from two relations. |
🔁 13. Transactions and Concurrency Control
🔹 Transaction
A transaction is a sequence of database operations that forms a single logical unit of work.
Example: Money transfer (Withdraw + Deposit).
🔹 ACID Properties
| Property | Meaning |
|---|---|
| Atomicity | All or none of the operations complete. |
| Consistency | Database remains consistent before and after. |
| Isolation | Transactions execute independently. |
| Durability | Changes persist after completion. |
🔹 Concurrency Control Techniques
- Locking (Shared/Exclusive locks)
- Timestamp ordering
- Two-Phase Locking (2PL)
- Deadlock handling
💾 14. Backup and Recovery
| Type | Description |
|---|---|
| Backup | Copy of data used for recovery. |
| Recovery | Process of restoring database after failure. |
Failures:
- Transaction failure
- System crash
- Disk failure
Recovery Methods:
- Checkpoints
- Log-based recovery
🔐 15. Data Security in DBMS
- Authorization: Controlling access.
- Encryption: Protecting data via codes.
- Auditing: Tracking user activities.
- Backup: Preventing data loss.
🧮 16. Data Independence
| Type | Description |
|---|---|
| Physical Data Independence | Change in physical storage doesn’t affect logical structure. |
| Logical Data Independence | Change in logical structure doesn’t affect user views. |
🗂️ 17. Indexing
Index improves search performance by maintaining a separate structure that stores pointers to records.
Types of Indexes:
- Primary index
- Secondary index
- Clustered / Non-clustered index
- B+ Tree index (used in RDBMS)
🌐 18. Distributed Database
A distributed database stores data across multiple locations or servers, but appears as a single database to users.
Advantages:
- Reliability
- Scalability
- Faster local access
Challenges:
- Synchronization
- Data replication
- Network dependency
🧠 19. Data Warehousing and Data Mining
| Concept | Description |
|---|---|
| Data Warehouse | Central repository for historical data used for analysis. |
| Data Mining | Process of discovering patterns or insights from data. |
Techniques: Classification, Clustering, Association Rules, etc.
🚀 20. NoSQL Databases
NoSQL = Not Only SQL
Used for unstructured/big data.
Types:
| Type | Example |
|---|---|
| Document-based | MongoDB |
| Key-Value Store | Redis |
| Column-based | Cassandra |
| Graph-based | Neo4j |
✅ 21. Important Terms (Quick Revision)
| Term | Meaning |
|---|---|
| Schema | Structure of database. |
| Instance | Data at a particular moment. |
| Tuple | Row in a table. |
| Attribute | Column in a table. |
| Domain | Set of possible values. |
| Integrity Constraints | Rules ensuring data correctness. |
| View | Virtual table created using SQL queries. |
🏁 Summary Chart
| Topic | Key Point |
|---|---|
| DBMS | Software to manage databases |
| RDBMS | Data in tables with relationships |
| Keys | Uniquely identify records |
| Normalization | Remove redundancy |
| SQL | Language to manage DB |
| Transaction | ACID properties |
| Data Models | Hierarchical, Network, Relational |
| Architecture | 3-level abstraction |
| Security | Authorization, Encryption |
| Modern DB | NoSQL, Cloud DB, Big Data |