Database Introduction

What are Databases?

Databases are organized collections of structured information, or data, typically stored electronically in a computer system. They allow for efficient storage, retrieval, modification, and management of data.

Database Types & Categories

1. Relational Databases (SQL)

Overview: Store data in tables with rows and columns, using Structured Query Language (SQL).

Key Concepts:

• Tables: Collections of related data

• Rows: Individual records

• Columns: Data attributes

• Primary Keys: Unique identifiers

• Foreign Keys: References to other tables

• ACID Properties: Atomicity, Consistency, Isolation, Durability

Popular Examples:

sql

-- Example SQL Query
SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.country = 'USA'
ORDER BY orders.total DESC;

Common RDBMS:

• MySQL: Most popular open-source RDBMS

• PostgreSQL: Advanced open-source with JSON support

• Oracle Database: Enterprise-grade, feature-rich

• Microsoft SQL Server: Windows-centric, enterprise features

• SQLite: Lightweight, file-based, embedded applications

Use Cases:

• Financial systems

• E-commerce platforms

• Healthcare records

• Traditional business applications

2. NoSQL Databases

Overview: Non-relational databases designed for specific data models and scalability.

Document Databases

Structure: Store data in JSON-like documents

json

{
  "user_id": "12345",
  "name": "John Doe",
  "orders": [
    {"order_id": "A100", "total": 99.99},
    {"order_id": "A101", "total": 149.99}
  ]
}

Examples: MongoDB, Couchbase, Firebase Firestore
Use Cases: Content management, user profiles, catalogs

Key-Value Stores

Structure: Simple key-value pairs

text

key: "user:12345:session"
value: "{'last_login': '2023-10-01', 'preferences': {...}}"

Examples: Redis, Amazon DynamoDB, Apache Cassandra
Use Cases: Caching, session storage, real-time data

Column-Family Stores

Structure: Store data in columns rather than rows
Examples: Apache Cassandra, HBase, ScyllaDB
Use Cases: Time-series data, write-heavy applications

Graph Databases

Structure: Store data as nodes and relationships

text

(User) - [FRIENDS_WITH] -> (User)
(User) - [LIKES] -> (Post)

Examples: Neo4j, Amazon Neptune, JanusGraph
Use Cases: Social networks, recommendation engines, fraud detection

3. NewSQL Databases

Overview: Combine SQL reliability with NoSQL scalability
Examples: Google Spanner, CockroachDB, VoltDB
Use Cases: Global-scale applications requiring ACID transactions

Database Architecture Patterns

1. Client-Server Architecture

text

Clients → Application Server → Database Server

• Traditional model

• Centralized data management

• Examples: MySQL, PostgreSQL

2. Distributed Architecture

text

Multiple Nodes → Coordination → Data Distribution

• Horizontal scaling

• Fault tolerance

• Examples: Cassandra, MongoDB sharding

3. Master-Slave Replication

text

Master (Write) → Replication → Slaves (Read)

• Read scalability

• Data redundancy

• Failover capability

4. Sharding (Horizontal Partitioning)

text

Shard 1: Users A-F
Shard 2: Users G-M
Shard 3: Users N-Z

• Distributes data across multiple servers

• Improves performance for large datasets

SQL vs NoSQL: Detailed Comparison

When to Use SQL (Relational):

python

# Use SQL when you need:
- Complex queries and joins
- ACID transactions
- Structured data with clear schema
- Data integrity and constraints
- Reporting and analytics
- Mature tooling and community

When to Use NoSQL:

python

# Use NoSQL when you need:
- Rapid development and schema flexibility
- Horizontal scaling
- High write throughput
- Semi-structured or unstructured data
- Specific data models (documents, graphs, etc.)
- Real-time applications

Modern Database Concepts

1. ACID vs BASE

ACID (Traditional RDBMS):

• Atomicity: All or nothing transactions

• Consistency: Data remains consistent after transactions

• Isolation: Concurrent transactions don't interfere

• Durability: Committed transactions persist

BASE (Many NoSQL systems):

• Basically Available: System seems to work all the time

• Soft state: State may change over time

• Eventual consistency: Will become consistent eventually

2. CAP Theorem

A distributed system can only guarantee two of these three properties:

• Consistency: All nodes see the same data

• Availability: System responds to every request

• Partition Tolerance: System works despite network partitions

3. Database Indexing

Purpose: Improve query performance

sql

-- Creating indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Types of indexes:
-- - B-tree (default for most cases)
-- - Hash (exact matches only)
-- - GiST, SP-GiST (geospatial, full-text)
-- - GIN (array, full-text search)

4. Database Normalization

Process of organizing data to reduce redundancy:

• 1NF: Atomic values, no repeating groups

• 2NF: No partial dependencies

• 3NF: No transitive dependencies

• BCNF: Stronger 3NF

• 4NF: No multi-valued dependencies

Popular Database Technologies in Detail

PostgreSQL

sql

-- Advanced features
-- JSON support
SELECT user_data->>'name' AS name 
FROM users 
WHERE user_data @> '{"premium": true}';

-- Full-text search
SELECT * FROM articles 
WHERE to_tsvector('english', content) 
@@ to_tsquery('database & performance');

-- Geospatial queries
SELECT name FROM locations 
WHERE ST_DWithin(
  coordinates, 
  ST_MakePoint(-74.006, 40.7128), 
  1000  -- 1km radius
);

MongoDB

javascript

// Document operations
db.users.insertOne({
  name: "John Doe",
  email: "[email protected]",
  preferences: {
    theme: "dark",
    notifications: true
  },
  created_at: new Date()
});

// Aggregation pipeline
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { 
      _id: "$user_id", 
      total_spent: { $sum: "$amount" } 
  }},
  { $sort: { total_spent: -1 } }
]);

Redis

python

# Various data structures
import redis

r = redis.Redis()

# Strings
r.set('user:session:123', 'session_data', ex=3600)

# Lists
r.lpush('recent_users', 'user123')
r.ltrim('recent_users', 0, 99)  # Keep only 100 most recent

# Sets
r.sadd('online_users', 'user123', 'user456')

# Sorted Sets
r.zadd('leaderboard', {'player1': 1000, 'player2': 1500})

# Hashes
r.hset('user:123', mapping={
    'name': 'John',
    'email': '[email protected]'
})

Database Design Principles

1. Entity-Relationship Modeling

sql

-- Example: E-commerce schema
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0),
    stock_quantity INTEGER DEFAULT 0
);

CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(user_id),
    total_amount DECIMAL(10,2),
    status ORDER_STATUS_ENUM
);

CREATE TABLE order_items (
    order_id UUID REFERENCES orders(order_id),
    product_id UUID REFERENCES products(product_id),
    quantity INTEGER CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

2. Performance Considerations

sql

-- Query optimization techniques
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Indexing strategies
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partitioning large tables
CREATE TABLE orders_2023 PARTITION OF orders 
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Modern Database Trends

1. Cloud Databases

• AWS: RDS, Aurora, DynamoDB, Redshift

• Google Cloud: Cloud SQL, Spanner, Firestore

• Azure: SQL Database, Cosmos DB

2. Serverless Databases

• Automatic scaling

• Pay-per-use pricing

• Managed operations

• Examples: AWS Aurora Serverless, Firebase

3. Multi-Model Databases

• Single database supporting multiple data models

• Examples: Azure Cosmos DB, ArangoDB, OrientDB

4. Time-Series Databases

• Optimized for time-stamped data

• Examples: InfluxDB, TimescaleDB, Prometheus

5. Vector Databases

• For AI/ML applications

• Similarity search on vectors

• Examples: Pinecone, Weaviate, Qdrant

Database Operations & Administration

1. Backup Strategies

sql

-- PostgreSQL backup
pg_dump -h localhost -U username dbname > backup.sql

-- MySQL backup
mysqldump -u username -p dbname > backup.sql

-- MongoDB backup
mongodump --uri="mongodb://localhost:27017/mydb"

2. Monitoring & Performance

sql

-- Query performance analysis
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Database size monitoring
SELECT 
    schemaname AS table_schema,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

3. Security Best Practices

sql

-- Principle of least privilege
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE ON users TO app_user;

-- Row-level security (PostgreSQL)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_policy ON users 
    USING (user_id = current_user_id());

-- Encryption
-- Transparent Data Encryption (TDE)
-- Column-level encryption

Choosing the Right Database

Decision Framework:

python

def choose_database(requirements):
    if requirements.acid_compliance:
        return consider_sql_databases()
    
    if requirements.horizontal_scaling:
        return consider_nosql_databases()
    
    if requirements.complex_queries:
        return consider_relational_databases()
    
    if requirements.flexible_schema:
        return consider_document_databases()
    
    if requirements.relationships:
        return consider_graph_databases()
    
    if requirements.caching:
        return consider_key_value_stores()

Hybrid Approaches:

python

# Polyglot Persistence - using multiple databases
architecture = {
    "primary_data": "PostgreSQL",        # Main application data
    "caching_layer": "Redis",            # Session cache, rate limiting
    "full_text_search": "Elasticsearch", # Search functionality
    "analytics": "ClickHouse",           # Analytical queries
    "real_time": "Firebase"              # Real-time features
}

Emerging Technologies

1. Blockchain Databases

• Immutable ledger technology

• Decentralized trust

• Examples: BigchainDB, Amazon QLDB

2. AI-Enhanced Databases

• Automated query optimization

• Predictive indexing

• Self-healing systems

3. Edge Databases

• Data processing at network edge

• Reduced latency

• Offline capability

This comprehensive overview covers the essential aspects of database technologies, from traditional relational systems to modern distributed databases and emerging trends. The choice of database technology depends on your specific use case, scalability requirements, data structure, and consistency needs.