DBMS: Functions, Components, and Data Models

• DBMS refers to Database Management System
• The software efficiently manages databases
• DBMS is responsible for Data storage, retrieval, and updates

Core Functions

• Data definition refers to processes like creating, altering, and removing database structures
• Data manipulation includes the processes of inserting, updating, deleting, and retrieving data
• Data security means enforcing access controls and protecting data
• Data integrity pertains to ensuring data accuracy and consistency
• Concurrency control involves managing simultaneous access by multiple users
• Backup and recovery involves protecting against data loss

Components

• Query processor translates and executes user queries
• Storage manager handles data storage and retrieval on disk
• Transaction manager ensures data consistency during transactions
• Authorization manager controls user access permissions

Data Models

• Hierarchical model organizes data in a tree-like structure
• Network model extends the hierarchical model, to allow many-to-many relationships
• Relational model organizes data into tables with rows and columns
• Object-oriented model represents data as objects with attributes and methods
• Object-relational model combines features of both relational and object-oriented models

Relational Model

• Data is organized into tables (relations)
• Tables consist of rows (tuples) and columns (attributes)
• Primary key uniquely identifies each row in a table
• Foreign key establishes a link between two tables
• Relationships define how data is related across tables

Normalization

• Normalization reduces data redundancy and improves data integrity
• Organizing data into tables enforces data dependencies properly
• First Normal Form (1NF) means each column contains only atomic values
• Second Normal Form (2NF) requires that a database is in 1NF form and all non-key attributes are fully functionally dependent on the primary key
• Third Normal Form (3NF) builds on 2NF and means that all non-key attributes are not transitively dependent on the primary key

SQL

• SQL means Structured Query Language
• SQL is the standard language to use when interacting with relational databases
• SQL is used for querying, inserting, updating, and deleting data
• Key SQL commands: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP

Transactions

• A transaction represents a logical unit of work that must be executed atomically
• Transactions are defined by ACID properties: Atomicity, Consistency, Isolation, Durability
• Atomicity means all changes in a transaction are treated as a single operation
• Consistency is where a transaction must maintain database integrity
• Isolation is where transactions are isolated from each other
• Durability means that once a transaction is committed, it is permanent

Concurrency Control

• Concurrency control manages simultaneous access to the database
• Locking prevents multiple transactions from accessing the same data at the same time
• Two-phase locking (2PL) requires a transaction to acquires all locks before releasing any
• Timestamping assigns timestamps to transactions to ensure serializability

Indexing

• Indexing improves query performance
• Indexes creates a data structure for faster data retrieval
• Types of indexes: B-tree, hash, inverted
• Proper indexing can significantly reduce query execution time

Database Security

• Access control involves limiting user access to specific data and operations
• Authentication verifies the identity of users
• Encryption protects data from unauthorized access
• Auditing tracks database activity for security purposes

Backup and Recovery

• Regular backups protect against data loss
• Recovery procedures restore a database to a consistent state after a failure
• Types of backups: Full, incremental, differential
• Recovery techniques: Rollforward, rollback

Types of DBMS

• Relational DBMS (RDBMS) manages data in a relational model, for example: MySQL, PostgreSQL, Oracle, SQL Server
• NoSQL DBMS handles unstructured or semi-structured data, for example: MongoDB, Cassandra, Redis
• Object-oriented DBMS (OODBMS) manages data as objects, for example: ObjectDB, Versant
• Graph DBMS is designed for managing relationships between data, for example: Neo4j, Amazon Neptune

Distributed DBMS

• Data is stored across multiple computers
• This improves scalability and availability
• Challenges include data consistency and transaction management across different nodes

Data Warehousing

• A data warehouse constitutes a central repository of integrated data from multiple sources
• Data Warehousing is used for reporting and data analysis, typically employing historical and read-only data
• ETL (Extraction, Transformation, and Loading) processes populate the data warehouse

Data Mining

• Data mining discovers patterns and insights from large datasets
• Data mining techniques include classification, clustering, and association rule mining
• Supports decision-making and prediction

Cloud Databases

• Databases hosted on cloud platforms
• Cloud Databases offer scalability, availability, and cost-effectiveness
• Example cloud databases: Amazon RDS, Azure SQL Database, Google Cloud SQL

Emerging Trends

• NewSQL databases combine scalability of NoSQL with ACID properties of RDBMS
• In-memory databases store data in memory for faster access
• Blockchain databases use blockchain technology for secure and distributed data management