Key Notes for DBMS Revision

1. Introduction to DBMS

• Database: Organized collection of data.

• DBMS (Database Management System): Software to create, manage, and manipulate databases.

• Examples: MySQL, PostgreSQL, Oracle, SQL Server.

2. Database Models

• Relational Model: Data is stored in tables (relations). Uses SQL for querying.

• NoSQL: Includes document stores (MongoDB), key-value stores (Redis), column stores (Cassandra), and graph databases (Neo4j).

3. SQL Basics

• DDL (Data Definition Language): CREATE, ALTER, DROP for defining and modifying schema.

• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE for managing data.

• DCL (Data Control Language): GRANT, REVOKE for permissions.

• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT for transaction management.

4. Normalization

• 1NF (First Normal Form): No repeating groups or arrays.

• 2NF (Second Normal Form): 1NF + no partial dependency.

• 3NF (Third Normal Form): 2NF + no transitive dependency.

• BCNF (Boyce-Codd Normal Form): Stricter version of 3NF.

5. Keys in Relational Model

• Primary Key: Unique identifier for a record.

• Foreign Key: Reference to a primary key in another table.

• Composite Key: Primary key consisting of multiple columns.

• Candidate Key: Columns that can uniquely identify a row.

• Alternate Key: Candidate keys not chosen as primary key.

6. Indexing

• Purpose: Speed up query performance.

• Types:

  • Clustered: Data is stored in the index order.

  • Non-clustered: Separate structure from the data storage.

  • Unique Index: Ensures all values are unique.

  • Full-text Index: For fast searching within text columns.

7. Transactions

• ACID Properties:

  • Atomicity: All operations are completed, or none are.

  • Consistency: Database remains consistent before and after the transaction.

  • Isolation: Transactions do not interfere with each other.

  • Durability: Once committed, changes are permanent.

8. Concurrency Control

• Pessimistic Locking: Locks data until transaction is complete.

• Optimistic Locking: Checks for data changes before committing.

• Isolation Levels:

  • Read Uncommitted: Dirty reads allowed.

  • Read Committed: No dirty reads.

  • Repeatable Read: No dirty or non-repeatable reads.

  • Serializable: No dirty, non-repeatable, or phantom reads.

9. Database Backup and Recovery

• Backup Types: Full, Differential, Incremental.

• Recovery Models: Simple, Full, Bulk-Logged.

• Log-Based Recovery: Uses transaction logs to restore database state.

10. ER Model (Entity-Relationship Model)

• Entities: Objects or things in the database.

• Attributes: Properties of entities.

• Relationships: Associations between entities.

• ER Diagrams: Visual representation of the database structure.

11. Advanced SQL

• Joins: Combining rows from two or more tables.

  • Inner Join: Rows with matching values.

  • Left Join: All rows from the left table, matched rows from the right.

  • Right Join: All rows from the right table, matched rows from the left.

  • Full Join: All rows when there is a match in either table.

• Subqueries: Query within a query.

• Views: Virtual tables created by a query.

• Stored Procedures: Precompiled collections of SQL statements.

• Triggers: Automatically executed responses to certain events on a table.

12. NoSQL Databases

• Document Stores: JSON-like documents (MongoDB).

• Key-Value Stores: Key-value pairs (Redis).

• Column Stores: Data stored in columns (Cassandra).

• Graph Databases: Nodes and relationships (Neo4j).

13. Data Warehousing and OLAP

• Data Warehouse: Central repository for integrated data from various sources.

• ETL (Extract, Transform, Load): Process of loading data into a warehouse.

• OLAP (Online Analytical Processing): Analyzing data in multiple dimensions.

14. Big Data Technologies

• Hadoop: Framework for distributed storage and processing.

• MapReduce: Programming model for processing large datasets.

• Spark: In-memory data processing framework.