PANTOMATH

Posted: **Thu Dec 26, 2024 11:23 am**

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.