Database Engineering: Relational SQL & Document NoSQL

Relational Foundations: Codd's Model, PostgreSQL & MySQL Engines

Introduced in 1970 by Edgar F. Codd at IBM, the Relational Model structures data into mathematical relations (tables) composed of tuples (rows) and attributes (columns). Unlike legacy hierarchical or network databases that required physical pointers to navigate data, relational databases enforce a strict separation between logical data structures and physical disk storage.

1. Anatomical Core of Relational Schemas

• Tables (Relations): Two-dimensional representations of structured entities governed by a strictly typed schema.
• Rows (Tuples / Records): Discrete entity instances containing atomic attribute values.
• Columns (Attributes): Strongly typed entity characteristics (e.g., INTEGER, VARCHAR(255), TIMESTAMPTZ, JSONB).
• Primary Keys (PK): A column or composite set of columns guaranteeing absolute uniqueness and non-nullability for every row. Modern systems choose between sequential BIGSERIAL / IDENTITY integers (compact 8-byte index footprint, but susceptible to enumeration attacks) and distributed UUIDv7 identifiers (16-byte cryptographically secure, time-ordered, preventing B-Tree page splits).
• Foreign Keys (FK) & Referential Integrity: Enforce relational integrity between child and parent tables via constraint rules:
  • ON DELETE CASCADE: Automatically deletes child records when parent is removed.
  • ON DELETE RESTRICT / NO ACTION: Aborts deletion if dependent children exist.
  • ON DELETE SET NULL: Retains child records but sets foreign key reference to null.

2. Engine Architecture: PostgreSQL vs. MySQL (InnoDB)

SQL Query Architecture: Joins, B-Tree Indexes & Execution Plans

Structured Query Language (SQL) is a declarative domain-specific language: developers specify what data is required, and the database cost-based query optimizer determines how to retrieve it across disk blocks.

1. Relational Join Taxonomy

• INNER JOIN: Returns rows where matching relational keys exist in both left and right tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table, populated with right table attributes when matches exist, or NULL when absent.
• RIGHT (OUTER) JOIN: Symmetric opposite of Left Join; returns all rows from the right table.
• FULL OUTER JOIN: Combines Left and Right joins; emits all rows from both tables, filling non-matching sides with NULL.
• CROSS JOIN: Produces the complete Cartesian Product (size: M x N rows) combining every row of the first table with every row of the second.

2. Internal Mechanics of B-Tree Indexing

Without an index, querying a table with 10,000,000 rows requires a Sequential Scan (Seq Scan): reading every disk block into memory with O(N) complexity. A B-Tree (Balanced Tree) index transforms lookup complexity to O(log N):

• Self-Balancing Hierarchy: Composed of a Root Page, Intermediate Branch Pages, and Leaf Pages. All leaf pages reside at the exact same depth.
• Doubly-Linked Leaf Nodes: Leaf nodes store ordered key values paired with physical Tuple Identifiers (TIDs: page number + offset). Because leaf pages form a bidirectional linked list, B-Trees execute Range Scans (WHERE price BETWEEN 10 AND 50) with blazing speed.
• Covering Indexes & Index-Only Scans: By utilizing the INCLUDE clause (CREATE INDEX idx_user_cover ON users (email) INCLUDE (full_name);), the database satisfies the entire query exclusively from the index tree without performing expensive heap table pointer dereferences.

Transactions & ACID Guarantees: MVCC vs. 2-Phase Locking

A database transaction is a logical unit of work comprising one or more database operations that must execute with absolute reliability. The ACID guarantees define transaction integrity:

• Atomicity (All-or-Nothing): Either every mutation inside the transaction commits successfully, or the entire transaction is rolled back. Implemented physically via the Write-Ahead Log (WAL) or Undo Logs.
• Consistency: Transactions transition the database from one valid state to another, strictly satisfying all defined schema constraints, unique keys, foreign keys, and check triggers.
• Isolation: Determines how concurrent transactions interact and whether intermediate uncommitted states are visible across threads.
• Durability: Once a transaction commits, its mutations are permanent and will survive catastrophic power failure or operating system crashes via synchronous disk flushes (fsync).

1. Transaction Isolation Levels & Concurrency Anomalies

2. Concurrency Control: MVCC vs. 2-Phase Locking (2PL)

Legacy engines implemented isolation through Two-Phase Locking (2PL): read operations acquired shared read locks (S-locks), blocking all write locks (X-locks). In heavy read-write workloads, 2PL causes extreme contention, locking queues, and deadlocks.

Modern engines (PostgreSQL, InnoDB) utilize Multi-Version Concurrency Control (MVCC):

• "Readers never block writers, and writers never block readers."
• When a transaction begins, it receives an instantaneous snapshot of the database based on active Transaction IDs (XIDs).
• When a row is updated, the engine does not overwrite existing bytes; it creates a new tuple version with an updated xmin (creation XID) and marks the old tuple with xmax (deletion XID). Readers continue reading the older tuple version unaffected by ongoing writes.

Database Normalization: From 1NF to Boyce-Codd Normal Form

Normalization is the formal mathematical decomposition of relational tables to eliminate data redundancy and prevent Insertion, Update, and Deletion Anomalies:

• First Normal Form (1NF):
  • Every column contains strictly atomic (indivisible) values (no CSV strings or nested arrays stored in varchar columns).
  • No repeating groups of similar columns (e.g., eliminating phone1, phone2, phone3).
  • Each row is uniquely identifiable via a Primary Key.
• Second Normal Form (2NF):
  • Must satisfy 1NF.
  • Must contain zero Partial Functional Dependencies: every non-key attribute must depend on the entire primary key, not a subset of a composite primary key.
• Third Normal Form (3NF):
  • Must satisfy 2NF.
  • Must contain zero Transitive Dependencies: non-key attributes must depend directly on the primary key, not on another non-key attribute (X -> Y -> Z). For example, storing zip_code and city in an order table violates 3NF because city depends on zip_code, not the order ID.
• Boyce-Codd Normal Form (BCNF):
  • A stricter refinement of 3NF. For every non-trivial functional dependency X -> Y, X must be a superkey.

Object-Relational Mapping (ORM): Prisma, Sequelize, SQLAlchemy & Hibernate

An Object-Relational Mapper (ORM) bridges the architectural mismatch between object-oriented programming languages (which model data as graphs of objects with pointers) and relational databases (which model data as scalar tuples connected via keys).

NoSQL & MongoDB Architecture: Documents, Collections & WiredTiger

Relational databases struggle when data structures are highly polymorphic, evolve continuously, or require massive horizontal distribution across multi-datacenter clusters. NoSQL (Not Only SQL) systems trade strict relational constraints for horizontal scalability and schema flexibility.

1. MongoDB Physical Storage: BSON & WiredTiger Engine

• BSON (Binary JSON): MongoDB documents are serialized into BSON—a binary encoding format that supports rich data types absent from standard JSON (e.g., ObjectId, ISODate, 64-bit integers, and raw binary buffers). BSON includes explicit length prefixes allowing fast field traversal without full document parsing.
• WiredTiger Storage Engine: Employs memory-mapped files, prefix compression, snappy compression, and document-level concurrency control with internal checkpointing to disk every 60 seconds or 2 GB of journal logs.
• 16 Megabyte BSON Document Size Limit: MongoDB enforces a strict 16 MB maximum size per document to prevent single documents from saturating network sockets or causing excessive memory bloat in the WiredTiger cache.

2. Data Modeling: Embedding vs. Referencing

Data modeling in MongoDB is driven by application query access patterns rather than theoretical normalization:

Mongoose ODM & The MongoDB Aggregation Pipeline

While MongoDB is schema-less at the database level, production enterprise code requires validation at the application tier. Mongoose acts as an Object Data Modeling (ODM) layer providing schemas, validation rules, lifecycle hooks (pre/post middleware), and population helpers.

1. The MongoDB Aggregation Framework

The Aggregation Pipeline is MongoDB's high-performance data processing engine modeled on Unix pipe streams: documents enter a multi-stage pipeline where each stage transforms the document stream and feeds its output into the subsequent stage:

• $match: Filters documents based on predicate criteria (equivalent to SQL WHERE). Must be placed as early as possible in the pipeline to utilize indexes!
• $project: Reshapes document structures, calculates computed fields, or excludes attributes (equivalent to SQL SELECT).
• $group: Groups documents by a specified identifier key and computes accumulator metrics ($sum, $avg, $min, $max) (equivalent to SQL GROUP BY).
• $unwind: Deconstructs an array field in input documents to output a distinct document for every array element.
• $lookup: Performs an equality join against an external collection (equivalent to SQL LEFT OUTER JOIN).
• $sort & $limit: Orders and truncates the streaming result set.

Architectural Decision Matrix: SQL versus NoSQL Trade-offs

Selecting a database engine requires matching workload patterns against physical engine characteristics rather than adhering to industry dogmatism:

Production Implementations: Prisma ORM & Mongoose Aggregations

1. Type-Safe Prisma Client with Interactive Transactions

The TypeScript code below demonstrates an enterprise financial transfer workflow executing inside a Prisma ACID transaction, guaranteeing atomicity and strict balance checking.

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

interface TransferResult {
  transactionId: string;
  senderBalance: number;
  receiverBalance: number;
}

export async function executeFundsTransfer(
  senderId: string,
  receiverId: string,
  amountCents: number
): Promise<TransferResult> {
  // Interactive ACID transaction: either both succeed, or both roll back cleanly
  return await prisma.$transaction(async (tx) => {
    // 1. Retrieve and lock sender account
    const sender = await tx.account.findUnique({
      where: { id: senderId }
    });

    if (!sender || sender.balanceCents < amountCents) {
      throw new Error('Insufficient funds or invalid sender account');
    }

    // 2. Deduct from sender
    const updatedSender = await tx.account.update({
      where: { id: senderId },
      data: { balanceCents: { decrement: amountCents } }
    });

    // 3. Credit receiver
    const updatedReceiver = await tx.account.update({
      where: { id: receiverId },
      data: { balanceCents: { increment: amountCents } }
    });

    // 4. Insert immutable audit ledger entry
    const ledgerEntry = await tx.transferLedger.create({
      data: {
        fromAccountId: senderId,
        toAccountId: receiverId,
        amount: amountCents,
        status: 'COMMITTED'
      }
    });

    return {
      transactionId: ledgerEntry.id,
      senderBalance: updatedSender.balanceCents,
      receiverBalance: updatedReceiver.balanceCents
    };
  });
}

2. Production Mongoose Schema & Revenue Analytics Aggregation

Below is a production Mongoose model with validation rules, compound indexes, and an aggregation pipeline computing monthly gross sales grouped by product category.

import { Schema, model, Document } from 'mongoose';

interface IOrderItem {
  productId: Schema.Types.ObjectId;
  category: string;
  quantity: number;
  unitPrice: number;
}

interface IOrder extends Document {
  customerId: Schema.Types.ObjectId;
  items: IOrderItem[];
  status: string;
  createdAt: Date;
}

const OrderSchema = new Schema<IOrder>({
  customerId: { type: Schema.Types.ObjectId, ref: 'Customer', required: true },
  status: { type: String, enum: ['PENDING', 'PAID', 'CANCELLED'], default: 'PENDING' },
  items: [{
    productId: { type: Schema.Types.ObjectId, ref: 'Product', required: true },
    category: { type: String, required: true },
    quantity: { type: Number, required: true, min: 1 },
    unitPrice: { type: Number, required: true, min: 0 }
  }]
}, { timestamps: true });

// Compound index to accelerate status and date range filtering
OrderSchema.index({ status: 1, createdAt: -1 });

export const OrderModel = model<IOrder>('Order', OrderSchema);

// Complex Multi-Stage Analytics Aggregation
export async function getCategoryRevenueReport(startDate: Date) {
  return await OrderModel.aggregate([
    // Stage 1: Filter paid orders within window (Index Scan)
    { $match: { status: 'PAID', createdAt: { $gte: startDate } } },

    // Stage 2: Deconstruct embedded items array
    { $unwind: '$items' },

    // Stage 3: Group by item category and compute revenue metrics
    {
      $group: {
        _id: '$items.category',
        totalRevenue: { $sum: { $multiply: ['$items.quantity', '$items.unitPrice'] } },
        unitsSold: { $sum: '$items.quantity' },
        orderCount: { $sum: 1 }
      }
    },

    // Stage 4: Reshape projected output structure
    {
      $project: {
        _id: 0,
        category: '$_id',
        totalRevenue: 1,
        unitsSold: 1,
        orderCount: 1
      }
    },

    // Stage 5: Sort by highest grossing category
    { $sort: { totalRevenue: -1 } }
  ]);
}

University & Industrial Examination Review