Database Management Systems (DBMS)

• DBMS (Database Management System) is software for managing databases.
• It acts as an interface between users/applications and the database.
• Users can create, read, update, and delete data using a DBMS.
• DBMS provides data security, integrity, and concurrency control.

Purpose of DBMS

• Data storage, retrieval, and updating capabilities are key.
• It provides a user-accessible catalog.
• Transaction support is included.
• Concurrency control services are part of the package.
• Recovery services are essential for data preservation.
• Authorization services manage who can access what.
• It supports data communication.
• Integrity services ensure data accuracy.
• Data independence is a design goal.
• Utility services offer additional functionality.

DBMS Components

• Query Processor interprets, optimizes, and executes queries.
• Storage Manager handles data storage and retrieval.
• Transaction Manager maintains data consistency during transactions.
• Authorization Manager controls user access to data.
• Data Manager handles low-level data access.

Data Models

• A data model defines how data is organized and structured in a database.
• Relational Model organizes data into tables with rows and columns.
• Hierarchical Model organizes data in a tree-like structure.
• Network Model extends the hierarchical model, allowing more complex relationships.
• Object-Oriented Model represents data as objects with attributes and methods.
• Entity-Relationship Model is a high-level conceptual model representing entities and relationships.

Relational Model

• Data is organized into tables (relations) with columns and rows, each row having a unique key.
• Data access and management are simplified using relational algebra and calculus.
• Tables are made up of tuples (rows) and attributes (columns).
• Each tuple represents an instance of an entity.
• Each attribute represents a characteristic of the entity.
• A Primary Key uniquely identifies each tuple in a table.
• A Foreign Key establishes links between tables.
• Relationships are established through foreign keys.
• SQL (Structured Query Language) is used to manage and query relational databases.

Data Definition Language (DDL)

• Defines the database schema and data structures.
• Includes commands like CREATE, ALTER, and DROP.
• CREATE is used to makes database objects like tables, indexes, and views.
• ALTER modifies the structure of existing database objects.
• DROP deletes database objects.

Data Manipulation Language (DML)

• Manipulates data within the database.
• Includes commands like SELECT, INSERT, UPDATE, and DELETE.
• SELECT retrieves data from the database.
• INSERT adds new data into the database.
• UPDATE modifies existing data in the database.
• DELETE removes data from the database.

Data Control Language (DCL)

• Controls access to the database and manages permissions.
• Includes commands like GRANT and REVOKE.
• GRANT gives privileges to users.
• REVOKE removes privileges from users.

Database Normalization

• Organizes data to minimize redundancy and improve data integrity.
• Reduces data anomalies like insertion, update, and deletion issues.
• First Normal Form (1NF) requires each column to contain only atomic values.
• Second Normal Form (2NF) requires being in 1NF and all non-key attributes to be fully functionally dependent on the primary key.
• Third Normal Form (3NF) requires being in 2NF and all non-key attributes to not be transitively dependent on the primary key.
• Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF, which addresses certain redundancy issues not handled by it.

Transactions

• A logical unit of work that must be executed completely or not at all.
• Follows ACID properties.
• Atomicity: All operations in a transaction are treated as a single unit.
• Consistency: A transaction must maintain data integrity.
• Isolation: Transactions are isolated from each other.
• Durability: Once a transaction is committed, it is permanent.

Concurrency Control

• Manages simultaneous database access by multiple users.
• Prevents data inconsistencies due to concurrent operations.
• Techniques:
• Locking prevents multiple transactions from accessing the same data simultaneously.
• Timestamping assigns timestamps to transactions to order execution.
• Optimistic Concurrency Control assumes conflicts are rare and checks for them at the end of a transaction.

Database Security

• Mechanisms protect the database from unauthorized access and malicious activities.
• Includes authentication, authorization, and auditing.
• Authentication verifies user identity.
• Authorization controls what data and operations users can access.
• Auditing tracks database activity for security monitoring.

Database Integrity

• Ensures the accuracy and consistency of data in the database.
• Achieved through constraints, triggers, and validation rules.
• Constraints are rules that enforce data integrity (e.g., primary key, foreign key, not null).
• Triggers are procedures that are automatically executed in response to certain events.
• Validation Rules check the validity of data before it is stored in the database.

Database Indexing

• It improves the speed of data retrieval operations.
• It comes at the cost of additional writes and storage space to maintain the index data structure.
• Creates an index on one or more columns of a table.
• Allows the database to quickly locate rows that match a search condition.

Data Warehousing

• Data warehouses store large amounts of historical data for analysis and reporting.
• Data is typically extracted from multiple sources, transformed, and loaded into the data warehouse.
• Used for business intelligence and decision support.

Data Mining

• It is discovering patterns and insights from large datasets.
• Techniques include:
• Association rule mining
• Clustering
• Classification

Distributed Databases

• A database that is spread across multiple physical locations.
• Improves availability, scalability, and performance.
• Requires mechanisms for data replication and synchronization.

NoSQL Databases

• Non-relational databases that provide flexible data models and scalability.
• Different types of NoSQL databases:
• Key-Value Stores
• Document Databases
• Column-Family Stores
• Graph Databases

A Database Management System (DBMS) is software for managing databases, providing an interface between users/applications and the database. It enables users to create, read, update, and delete data while ensuring data security, integrity, and concurrency control. Key components include the Query Processor, Storage Manager, and Transaction Manager.