Database reviewer

Relational Algebra

Mathematical, procedural foundation of database queries.

Relational Algebra (Procedural)

Giving the chef step by step instructions. You define how to get the data.

SQL (Declarative)

Ordering from the menu. You define what data you want, leaving the query optimizer to figure out the fastest way to get it.

Selection (σ)

Filters tuples (rows) based on a specified condition (predicate).

Selection RA Notation

σ major = 'CS' Students.

Projection (π)

Selects specific attributes (columns) and discards the rest. Automatically eliminates duplicate rows in theoretical RA.

Projection RA Notation

π name, gpa Students.

Rename (ρ)

Renames a relation or its attributes. Helpful when working with self joins or temporary results.

Rename RA Notation

ρ CS_Students σ major = 'CS' Students.

Cartesian Product (×)

Combines every row of the first table with every row of the second table.

Cartesian Product RA Notation

Students × Courses.

Join (⋈)

Combines related rows from two tables based on a common key condition.

Join RA Notation

Students ⋈ Students.student_id = Enrollments.student_id Enrollments.

Set Operations (∪, ∩, −)

Requires Union Compatibility, meaning both tables must have the same number of columns with matching domain types.

Union (∪)

Combines rows from two tables, excluding duplicates.

Intersection (∩)

Returns only rows present in both tables.

Set Difference (−)

Returns rows in the first table that are not in the second table.

SELECT

Projections (π), picks columns.

FROM

Table source and joins (⋈).

WHERE

Selections (σ), filters rows.

GROUP BY

Aggregate operations.

HAVING

Filters aggregated groups.

ORDER BY

Sorts output.

SQL Execution Order

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Selection (σ) to SQL

WHERE / HAVING filters rows based on conditions.

Projection (π) to SQL

SELECT filters columns and projects output.

Cartesian Product (×) to SQL

CROSS JOIN generates all possible row combinations.

Join (⋈) to SQL

JOIN ON merges tables on matching keys.

Union (∪) to SQL

UNION combines non duplicate sets.

Set Difference (−) to SQL

EXCEPT / MINUS removes overlapping rows.

NoSQL Database

Non relational database management systems designed to handle unstructured, semi structured, and high velocity datasets. Refers to non relational database management systems that prioritize horizontal scalability, dynamic data modeling, and low latency performance for specialized workloads.

Document Databases

Organize data into self contained documents using hierarchical formats like JSON, BSON, or XML. Content management systems, user profiles, evolving e commerce product catalogs. MongoDB, Couchbase, Amazon DocumentDB.

Key Value Stores

Use a simple hash table index where a unique key points to an associated value. High speed caching, user session state, real time leaderboards. Redis, Amazon DynamoDB, Aerospike.

Wide Column Stores

Organize data into dynamic column families where each row can contain a different number of columns indexed by dynamic keys or timestamps. IoT telemetry, time series event logging, financial market data. Apache Cassandra, ScyllaDB, Google Cloud Bigtable.

Graph Databases

Map data using Nodes, Edges, and Properties attached to both.

Real Time Geospatial Ride Matching

Ride hailing platforms process continuous GPS updates from drivers and passengers, requiring real time spatial proximity and driver availability. Uses in memory key value stores and geospatial indexing engines such as Redis and Apache Cassandra to locate and dispatch the nearest driver in sub second timeframes.

Geohash Indexing

Redis converts 2D coordinates (longitude, latitude) into a 52 bit integer Geohash and stores it in a Sorted Set (ZSET).

Sub Second Latency

Bounding box and radius queries do not perform full table scans and Redis evaluates neighboring grid cells in memory.

High Write Throughput in Ride Matching

Driver location updates overwrite existing member scores directly in RAM without requiring disk I/O locks.

Personalized Streaming and Content Recommendation

Media platforms deliver personalized movie recommendations and maintain precise playback states across hundreds of millions of concurrent global viewers.

NoSQL Mechanics for Streaming

Employs wide column stores such as Apache Cassandra and key value stores for scalable and multi region data handling.

High Write Throughput in Streaming

Cassandra writes data to an append only commit log and in memory table before flushing to immutable disk files.

Clustering Keys and Automatic Sorting

The clustering key sorts items physically on disk in ascending order, allowing recommendations to be fetched instantly without an explicit ORDER BY computation.

Partition Driven Scaling

Data is spread across global nodes using a token ring derived from the hash of the Partition Key.

Messaging Platforms and Social Feeds

Social media applications handle billions of posts, comments, likes, and direct messages that must be ingested continuously and rendered in personalized feeds.

Relationship First Classing

Graph databases store connections as pointer references on disk instead of recalculating relationships through SQL JOIN operations.

Hybrid Architectural Patterns

Graph stores are ideal for social graph relationship queries, while Cassandra and Redis can use asynchronous workers to push new post IDs into pre indexed timeline queues.

High Volume E Commerce Shopping Carts

During massive flash sales, millions of shoppers add items to shopping carts simultaneously, where database delays or locks can lead to lost revenue.

NoSQL Mechanics for Shopping Carts

Uses managed key value and document stores such as Amazon DynamoDB, with shopping cart payloads indexed directly by session_id or user_id.

O(1) Hash Partition Routing

The Partition Key is hashed to route requests directly to the exact storage partition hosting the user's cart.

Time To Live (TTL) Automatic Expiration

DynamoDB automatically identifies and deletes expired abandoned carts in the background.

Atomic Update Expressions

DynamoDB executes atomic field updates directly on the storage node instead of fetching and modifying the cart document locally.

IoT and Connected Vehicle Telemetry

Autonomous and connected vehicles stream real time diagnostic telemetry such as speed, battery health, tire pressure, and environmental sensor data every few seconds.

NoSQL Mechanics for IoT Telemetry

Uses wide column and time series NoSQL databases such as Apache Cassandra and ScyllaDB for high throughput ingestion.

High Compression Storage Engines

Time series storage engines group identical metric types sequentially on disk and use specialized algorithms to reduce data size.

Tags vs Fields Indexing Strategy

Tags are indexed for filtering while fields are unindexed raw numeric arrays for high throughput writes.

Automated Downsampling and Retention Policies

High frequency telemetry is kept in high resolution for a short period and aggregated into long term averages for trend analysis.

CAP Theorem

A distributed database system can simultaneously provide at most two out of three guarantees: Consistency, Availability, and Partition Tolerance.

Consistency

Every read request receives the most recent write or an error, and all nodes see and return the exact same data.

Availability

Every non failing node returns a non error response for every read and write request, but the returned data is not guaranteed to be the most recent.

Partition Tolerance

The system continues to operate despite network partitions where communication between nodes is delayed, dropped, or split into isolated groups.

CP Systems

Prioritize data correctness and may reject writes or delay reads when nodes cannot communicate to verify the latest state.

AP Systems

Prioritize continuous uptime and continue accepting reads and writes even when nodes cannot reach the rest of the cluster.

CA Systems

Guarantee full consistency and uptime but cannot survive network splits across nodes.

CP Representative Databases

MongoDB, HBase, Redis Cluster mode, Bigtable.

AP Representative Databases

Apache Cassandra, Amazon DynamoDB, CouchDB, Riak.

CA Representative Databases

Traditional single node RDBMS such as PostgreSQL and MySQL.

Eventual Consistency

A weak consistency model where, if no new updates are made, all replicas across a distributed network will eventually converge and return the exact same value.

Linearizable Consistency

Forces all nodes to immediately return the latest write at the cost of blocking or delaying operations.

Eventual Consistency and AP Systems

Eventual consistency enables AP systems to continue accepting reads and writes during network partitions and synchronize their data after the partition heals.

Last Write Wins (LWW)

Uses physical or logical timestamps where the update with the newest timestamp overwrites older data.

Vector Clocks and Version Vectors

Tracks causal relationships between updates to detect concurrent writes and prompt application level conflict resolution.

Conflict Free Replicated Data Types (CRDTs)

Purpose built data structures designed to mathematically merge divergent states without conflicts.

Read Repair and Hinted Handoffs

Serves the freshest version to the client and updates the stale node in the background when outdated data is detected.

Schema Less Databases

A schema less database, more accurately described as schema on read, does not enforce a fixed table structure, predefined column list, or data type constraints prior to data ingestion.

Schema on Write

A traditional relational database requires the schema to be defined upfront and rejects records with unexpected fields.

Schema Less NoSQL

Data is stored as self contained documents, key value pairs, or wide columns where each record can store different fields or structures without requiring database wide schema migrations.

Data Self Containment (Denormalization)

NoSQL encourages embedding related data into a single document to avoid complex multi table JOIN queries across distributed network boundaries.

Horizontal Sharding without Locks

NoSQL databases partition data across nodes using a shard key, allowing self contained documents to be distributed across thousands of servers.

Structural Flexibility

Schema less design stores self contained records, eliminates distributed JOINs, and enables simple data sharding.

Operational Strategy

Eventual consistency prioritizes continuous availability, accepts writes locally on any node, and synchronizes state asynchronously.

Schema Less and Eventual Consistency in NoSQL

By removing fixed schemas, NoSQL databases allow data to be packaged into independent, easily sharded units that can be read and written across thousands of servers globally without requiring slow synchronized network locks.