Databases: Concepts and Types

• A database is an organized collection of structured information or data
• Databases are typically stored electronically in a computer system
• Databases are designed for efficient data storage, management, and retrieval
• Databases are crucial for simple data storage and complex data analysis applications
• Databases provide a structured way to organize and access data
• Databases ensure data integrity, consistency, and security

Key Concepts

• Data Model: An abstract model describing how data is represented and accessed
• Schema: The structure of a database, defining tables, fields, and relationships
• Instance: Actual data stored in the database at a specific moment
• Database Management System (DBMS): Software that manages the database
• DBMS provides functionalities like data storage, retrieval, security, and backup

Types of Databases

• Relational Databases:
  • Organize data into tables with rows (records) and columns (fields)
  • Use SQL (Structured Query Language) for querying and managing data
  • Establish relationships between tables using foreign keys
  • Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server
  • Emphasize ACID properties (Atomicity, Consistency, Isolation, Durability)
  • ACID properties ensure reliable transactions
• NoSQL Databases:
  • Non-relational databases
  • Provide flexible schemas and scalability
  • Suited for handling large volumes of unstructured or semi-structured data
  • Types include:
    • Document Databases (e.g., MongoDB): Store data as JSON-like documents
    • Key-Value Stores (e.g., Redis): Store data as key-value pairs
    • Wide-Column Stores (e.g., Cassandra): Store data in columns across many servers
    • Graph Databases (e.g., Neo4j): Store data as nodes and relationships
  • Offer different consistency models, such as eventual consistency
• Object-Oriented Databases:
  • Store data as objects, similar to object-oriented programming
  • Support concepts like inheritance, encapsulation, and polymorphism
  • Example: ObjectDB
• In-Memory Databases:
  • Store data in RAM for faster access
  • Used for applications requiring real-time data processing
  • Examples: Redis, MemSQL
• Graph Databases:
  • Use graph structures with nodes, edges, and properties to store and represent data
  • Suited for modeling complex relationships
  • Example: Neo4j

Database Design

• Conceptual Design:
  • Identifying entities, attributes, and relationships
  • Creating an Entity-Relationship (ER) diagram
• Logical Design:
  • Mapping entities and attributes to tables and columns
  • Defining primary keys and foreign keys
  • Normalizing the database to reduce redundancy and improve data integrity
• Physical Design:
  • Specifying data types, indexes, and storage structures
  • Optimizing performance based on hardware and software considerations

Normalization

• Process of organizing data to minimize redundancy and dependency
• Involves dividing databases into tables and defining relationships between the tables
• Normal forms include:
  • First Normal Form (1NF): Eliminates repeating groups
  • Second Normal Form (2NF): Eliminates redundant data
  • Third Normal Form (3NF): Eliminates transitive dependency

SQL (Structured Query Language)

• Standard language for managing and querying relational databases
• Key SQL commands:
  • SELECT: Retrieves data from one or more tables
  • INSERT: Adds new data into a table
  • UPDATE: Modifies existing data in a table
  • DELETE: Removes data from a table
  • CREATE: Creates a new database, table, or index
  • ALTER: Modifies the structure of an existing database, table, or index
  • DROP: Deletes a database, table, or index
• Example: SELECT * FROM Employees WHERE Department = 'Sales';

Transactions

• A sequence of operations treated as a single logical unit of work
• Must adhere to ACID properties:
  • Atomicity: All operations in a transaction succeed or fail as a whole
  • Consistency: The transaction maintains the integrity of the database
  • Isolation: Concurrent transactions do not interfere with each other
  • Durability: Once a transaction is committed, the changes are permanent

Indexing

• Data structure that improves the speed of data retrieval operations on a database table
• Creates a pointer to data in a table
• Types of indexes:
  • B-tree index
  • Hash index
  • Clustered index
  • Non-clustered index

Database Security

• Measures to protect databases from unauthorized access and malicious activities
• Techniques include:
  • Authentication: Verifying the identity of users
  • Authorization: Granting specific permissions to users
  • Encryption: Encoding data to prevent unauthorized access
  • Auditing: Tracking database activities for security monitoring
  • Firewalls: Act as a barrier between the database and unauthorized networks
  • Regular backups: Protect against data loss due to hardware failure or cyber attacks
  • Access control lists (ACLs): Define which users or groups have access to specific database objects

Backup and Recovery

• Essential for protecting data against loss due to hardware failure, software errors, or human mistakes
• Backup strategies include:
  • Full backup: Copies all data in the database
  • Incremental backup: Copies only the data that has changed since the last full or incremental backup
  • Differential backup: Copies only the data that has changed since the last full backup
• Recovery involves restoring the database to a consistent state after a failure
• Techniques include:
  • Point-in-time recovery: Restoring the database to a specific point in time
  • Transaction log replay: Applying transaction logs to recover committed transactions

Database Administration

• Roles and responsibilities of a database administrator (DBA):
  • Installing and configuring database software
  • Designing and implementing database systems
  • Monitoring database performance
  • Ensuring database security
  • Performing database backups and recovery
  • Managing user access and permissions
  • Troubleshooting database issues
  • Applying patches and upgrades