Databases

[Buela_Vigneswaran]

Databases

1. Database Management Systems (DBMS)

• Overview:
  • DBMS is software that enables users to define, create, and manage databases.
  • Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
• Key Concepts:
  • ER Diagrams (Entity-Relationship Diagrams):
    • Visual representation of entities, their attributes, and relationships.
  • Normalization:
    • Process of organizing data to reduce redundancy and improve consistency.
    • Forms: 1NF, 2NF, 3NF, BCNF.
  • Transactions:
    • Set of operations that must be executed as a single unit.
    • Properties (ACID): Atomicity, Consistency, Isolation, Durability.

2. SQL (Structured Query Language)

 

• Basic Commands:

  • Code: Select all

    SELECT

    ,

    Code: Select all

    INSERT

    ,

    Code: Select all

    UPDATE

    ,

    Code: Select all

    DELETE

    .
• Advanced Commands:
  • Joins (

    Code: Select all

    INNER JOIN

    ,

    Code: Select all

    LEFT JOIN

    ,

    Code: Select all

    RIGHT JOIN

    ,

    Code: Select all

    FULL OUTER JOIN

    ).
  • Subqueries, Views, Indexes.
  • Aggregate Functions:

    Code: Select all

    COUNT()

    ,

    Code: Select all

    SUM()

    ,

    Code: Select all

    AVG()

    ,

    Code: Select all

    MAX()

    ,

    Code: Select all

    MIN()

    .
• Stored Procedures and Triggers:
  • Automating repetitive tasks and ensuring database integrity.

3. Advanced Databases

• NoSQL Databases:
  • Designed for unstructured and semi-structured data.
  • Types:
    • Key-Value Stores (Redis, DynamoDB).
    • Document Stores (MongoDB, CouchDB).
    • Column Stores (Cassandra, HBase).
    • Graph Databases (Neo4j).
  • Advantages: Scalability, flexibility, schema-less design.
• Big Data Concepts:
  • Handling massive datasets using technologies like Hadoop, Spark, Hive.

 

4. Database Design

• Schema Design:
  • Logical structure of a database (tables, relationships).
• Keys:
  • Primary Key, Foreign Key, Candidate Key, Composite Key.
• Indexing:
  • Mechanism to speed up data retrieval.
  • Types: Clustered and Non-Clustered Indexes.

5. Database Performance Optimization

• Query Optimization:
  • Ensuring queries run efficiently by minimizing execution time.
• Sharding and Replication:
  • Sharding: Partitioning data across multiple servers.
  • Replication: Copying data across servers for fault tolerance.
• Caching:
  • Using tools like Redis or Memcached to reduce database load.