Data Modeling & Database Fundamentals

From raw data to wisdom — relational theory, columnar internals, NoSQL paradigms, graph databases, and choosing the right model for the job.

1. Data Hierarchy

Data is raw facts. Information is data with context — organized through data models using SQL and query languages. Knowledge emerges when information connects and patterns appear. Wisdom is applying knowledge to make sound decisions.

2. Relational Foundations & ACID

Edgar Codd (1970) introduced the relational data model: data organized into relations (tables) with rows and columns. Each column attribute must have a unique name and every value in a column must be the same type — no implicit conversions ("65" - 5 + 10 is not valid).

ACID Guarantees

ACID guarantees ensure reliable concurrent database operations:

3. Normalization (1NF → 3NF)

Normalization is the process of organizing tables into "normal forms" to reduce redundancy and inconsistencies. Anomalies result from poor schema design — CRUD (Create, Read, Update, Delete) operations suffer when data is denormalized incorrectly.

First Normal Form (1NF)

Each cell in a table contains a single value — no arrays, no comma-separated lists, no nested structures. When a schema fails 1NF: queries become harder, optimization features (indexes) are much harder to build and use, and constraints become weaker.

Second Normal Form (2NF)

Satisfies 1NF, plus: define a clear primary key AND ensure all columns depend on the entire key, not just part of it. Relevant when using composite primary keys (two or more columns forming the key). Natural primary key: a single column like Name that identifies the record. Composite primary key: multiple columns together (e.g., StudentID + CourseID).

Third Normal Form (3NF)

Satisfies 1NF and 2NF, plus: no non-key column should depend on another non-key column (no transitive dependencies).

4. OLTP vs OLAP

5. Star & Snowflake Schemas

OLAP databases use denormalized schemas optimized for analytical queries. The two dominant patterns:

6. Columnar Storage Internals

Traditional vs True Column-Store

How Columnar Query Execution Works (Bitmap Scanning)

-- Executing: SELECT * FROM orders WHERE status='shipped' AND region='US'

Step 1: Scan status column → produce position bitmap
        [0, 0, 1, 0, 1, 1, 0, 0, 0, 0]  -- 1 = matches 'shipped'

Step 2: Scan region column → produce position bitmap
        [1, 0, 1, 1, 0, 1, 0, 1, 0, 0]  -- 1 = matches 'US'

Step 3: AND bitmaps together → combined position bitmap
        [0, 0, 1, 0, 0, 1, 0, 0, 0, 0]  -- positions 3 and 6 match both

Step 4: Use bitmap to fetch only matching rows from remaining columns
        -- Read only 2 rows instead of 10 — massive I/O savings

Apache Parquet — Columnar but Row-Grouped

-- Parquet file structure: row-groups containing column chunks

Row Group 1 (rows 0–99,999):
  ├── Column Chunk: order_id    (100K values, compressed)
  ├── Column Chunk: status      (100K values, compressed)
  └── Column Chunk: amount      (100K values, compressed)

Row Group 2 (rows 100K–199,999):
  ├── Column Chunk: order_id    (100K values, compressed)
  ├── Column Chunk: status      (100K values, compressed)
  └── Column Chunk: amount      (100K values, compressed)

7. OLAP Database Landscape

8. DuckDB vs PostgreSQL

9. NoSQL & Document Stores

NoSQL = Not Only SQL (not "no relational databases"). The structure of data keeps shifting — documents, key-value pairs, flexible record structures. Categories:

MongoDB

"Humongous database" — built for massive scale. No schema changes needed, no table extensions. Uses MQL (MongoDB Query Language). Internally stores data as BSON (binary JSON); for users it behaves like JSON.

Amazon DynamoDB

Purpose-built for unpredictable, massive scale — designed to support Amazon's holiday traffic spikes. Single-digit ms performance at any scale. Access patterns are more constrained than MongoDB — must design partition/sort keys carefully.

Apache Cassandra

Built for Facebook's inbox search serving millions of users. If you don't enforce some structure, it becomes extremely complex to filter and aggregate data. Amazon Keyspaces provides managed Cassandra using CQL for high-throughput workloads.

10. Document CRUD with Code

Create (Insert)

# MongoDB
db.customers.insert_one(data_dict)

# DynamoDB
db.Table("Customers").put_item(Item=data_dict)

# Firestore
db.collection("Customers").add(data_dict)

Read (Query)

filter_expr = {"email": "example@email.com"}

# MongoDB
db.customers.find_one(filter_expr)

# DynamoDB
db.Table("Customers").get_item(Key=filter_expr)

# Firestore
db.collection("Customers").where("email", "==", "example@email.com").get()

Update

filter_expr = {"email": "old@email.com"}
new_values  = {"email": "new@email.com"}

# MongoDB — does NOT overwrite whole doc, only updates specified attributes
db.customers.update_one(filter_expr, {"$set": new_values})

# DynamoDB — uses expression syntax, distinguishable via placeholders
db.Table("Customers").update_item(
    Key=filter_expr,
    UpdateExpression="SET email = :new_email",
    ExpressionAttributeValues={":new_email": "new@email.com"}
)

# Firestore
result = db.collection("Customers").where("email", "==", "old").get()[0].reference
result.update(new_values)

Delete

filter_expr = {"user_id": "user123"}

# MongoDB — can delete based on ANY filter
db.customers.delete_one(filter_expr)

# DynamoDB — can ONLY delete based on primary key
db.Table("Customers").delete_item(Key=filter_expr)

# Firestore
result = db.collection("Customers").where("user_id", "==", "user123").get()[0].reference
result.delete()

11. MQL Operators & Aggregation Pipeline

MQL Update Operators

In MQL, operators always start with $ (dollar sign):

Aggregation Pipeline

MongoDB's aggregation pipeline is essentially an ETL pipeline for performing advanced data analysis and manipulation on collections:

# An ETL pipeline inside MongoDB
db.orders.aggregate([
    {"$lookup": {...}},   # JOIN-like operator across collections (Extract)
    {"$match": {...}},    # Filter documents                    (Extract)
    {"$group": {          # Aggregate: $sum, $avg, $count       (Transform)
        "_id": "$category",
        "total": {"$sum": "$amount"}
    }},
    {"$sort": {...}}      # Order results                       (Load)
])

Joins Across Document Databases

12. Schema Evolution in Document DBs

Document databases have no DDL (no CREATE TABLE, no ALTER TABLE). You simply start inserting documents. The document shape becomes the de facto structure of the collection.

MongoDB Schema Validation

MongoDB does support optional schema validation at the collection level — rules defined during collection creation validate documents on insert/update. This provides a middle ground between fully rigid relational schemas and zero enforcement.

# Define validation rules when creating a collection
db.create_collection("customers", validator={
    "$jsonSchema": {
        "required": ["name", "email"],
        "properties": {
            "name":  {"bsonType": "string"},
            "email": {"bsonType": "string", "pattern": "@"}
        }
    }
})

13. ACID Guarantees in NoSQL

NoSQL databases implement ACID differently — scoped differently, made optional, and optimized for their specific design and workload patterns.

14. Embedding vs Referencing

MongoDB inverts the relational pattern:

customer = {
  _id: 1,
  name: "Alice",
  address: {        ← embedded
    city: "Phoenix",
    zip: "85001"
  }
}

customer = {
  _id: 1,
  name: "Alice",
  address_id: 42    ← reference
}

15. CRUD Anomalies in NoSQL

16. Graph Databases

When the most important thing about data is the relationships between items — the connections — graph databases are the natural fit. Connections are primary, not secondary. Graph databases treat connections between entities as first-class citizens.

Three questions a graph database answers naturally:

Origin & Use Cases

Neo4j was built by a team trying to build an ECM (Enterprise Content Management) system — for storing, organizing, and retrieving large amounts of interconnected content (documents, references, permissions, pages). They realized the relationships mattered more than the data itself. Graph databases provide full ACID guarantees.

Graph Data Model

ACID in Graph Databases

Intermediate Nodes & Normalization

When a relationship carries too many properties, create an intermediate node instead. This is the graph equivalent of normalization — determining the right level of detail per entity.

Neo4j Python Driver: CREATE vs MERGE

# CREATE — always creates new (duplicates if run twice)
driver.execute_query("CREATE (c:Customer {name: $name})", name="Alice")

# MERGE — find-or-create (idempotent, preferred)
driver.execute_query("""
    MERGE (c:Customer {id: $id})
    ON CREATE SET c.name = $name, c.created = datetime()
    ON MATCH SET c.updated = datetime()
    RETURN c
""", id="C001", name="Alice")

# driver.execute_query() — handles session internally (newer API)
# session.run() — requires explicit with driver.session() block
# RETURN is optional — use for confirmation, debugging, accessing IDs

# Relationships — create with nodes or separately
driver.execute_query("""
    MATCH (c:Customer {id: $cid}), (e:Email {address: $email})
    MERGE (c)-[:HAS_EMAIL]->(e)
""", cid="C001", email="alice@example.com")

Graph Database Engines

17. Cloud Database Services

AWS Authentication Pattern (boto3)

import boto3, json
from botocore.exceptions import ClientError

# STS Session
session = boto3.Session(
    aws_access_key_id="...",
    aws_secret_access_key="...",
    aws_session_token="...",
    region_name="us-east-1"
)
sts_client = session.client("sts")
identity = sts_client.get_caller_identity()

# Secrets Manager — store DB credentials
secretsmanager = boto3.client("secretsmanager")
try:
    response = secretsmanager.create_secret(
        Name="rds-postgres-credentials",
        SecretString=json.dumps(secret_dict)
    )
except ClientError as e:
    print(e)

The Data Model — Final Thought

A data model is a structured representation connecting human understanding (logical) with machine storage and retrieval (physical).