Database

A database is an organized collection of data that can be stored and accessed electronically. They are designed to store large amounts of information by structuring data that optimized information retrieval and modification. The structuring can include using tables, columns, rows, objects and indexes and other mechanisms that facilitate effective data management.

The structure of a database can be file-based, relational, record-based, document-based, and key-value based among other structures. The relational databases with tables, columns and rows and the use of SQL - Structured Query Language is the most common database used for transactional use today. MongoDB is an example of NoSQL Database.

Database Types

graph TD
    DB[Database Systems] --> Rel[Relational]
    DB --> NoSQL[NoSQL]
    DB --> Special[Specialized]
    Rel --> PostgreSQL["[[PostgreSQL]]"]
    Rel --> MySQL["[[MySQL]]"]
    Rel --> SQLite["[[SQLite]]"]
    Rel --> Oracle[Oracle]
    NoSQL --> Doc["Document ([[MongoDB]])"]
    NoSQL --> KV["Key-Value ([[Redis]])"]
    NoSQL --> WC["Wide-Column ([[Cassandra]])"]
    NoSQL --> Graph["Graph ([[Neo4j]])"]
    Special --> TS["Time-Series ([[InfluxDB]])"]
    Special --> Search["Search (Elasticsearch)"]
    Special --> InMem["In-Memory (Memcached)"]

See NoSQL Databases for when to choose non-relational and History of Relational Databases for how we got here.

Structured vs Unstructured Data

| Aspect | Structured | Unstructured |
| -------- | --------SQL------------------------------------------------- | --------------------------------------------------------------------------- |
| Format | Tables, rows, columns | Text, images, video, audio, documents |
| Schema | Predefined, rigid | No predefined schema |
| Querying | SQL | Requires specialized tools (NLP, ML) |
| Storage | Relational databases (PostgreSQL, MySQL, Oracle) | NoSQL Databases (MongoDB, Cassandra), Normalization]] |
| Examples SQLfinancial transactions, invesubqueries media posts, sensor data, videos |

Stransactional) falls between the two replication, partitioning, and shardingd tabular schema. Document databases like MongoDB are designed for this.

Core Concepts

• Managed by a Database Management Systems (DBMS)
• Structured according to a three-level architecture (internal, conceptual, external)
• Designed using ERDs and Normalization
• Queried with SQL using joins, subqueries, and Window Functions
• Protected by ACID properties for transactional integrity
• Scaled via replication, partitioning, and sharding