Skip to content
Rounder
Start
BCA-442E · Advanced Web Development Technologies
Unit 4

SQL vs NoSQL and MongoDB Database Architecture

Sign in to save
Unit 4 Learning Hub

SQL vs NoSQL and MongoDB Database Architecture

Core Definitions & Key Concepts

Core Definitions & Key Concepts

What is the primary difference between SQL (Relational) and NoSQL databases

SQL databases store structured data in fixed tabular schemas with strict relations, foreign keys, and ACID transactions. NoSQL databases store non-relational, schema-flexible data (documents, key-value, column-family, graphs) optimized for horizontal scalability.

What is the CAP Theorem (Brewer's Theorem)

States that a distributed data store can simultaneously provide at most two out of the following three guarantees: Consistency (C), Availability (A), and Partition Tolerance (P). Since network partitions are inevitable in real networks, distributed databases must choose between CP or AP.

What is ACID in SQL versus BASE in NoSQL

  • ACID: Atomicity, Consistency, Isolation, Durability (Strict transactional guarantees).
  • BASE: Basically Available, Soft state, Eventual consistency (Prioritizes availability and horizontal scaling over immediate consistency).

What is MongoDB and what data format does it use internally

MongoDB is an open-source, distributed, document-oriented NoSQL database. It stores records as flexible, JSON-like documents represented internally in BSON (Binary JSON) format.

A Database, Collection, and Document in MongoDB

  • Database: Top-level container for collections.
  • Collection: A grouping of MongoDB documents (equivalent to an RDBMS table, but without a rigid schema).
  • Document: A single record composed of field-and-value pairs (equivalent to an RDBMS row).

What is the _id field in a MongoDB document

A mandatory 12-byte primary key unique to each document. If omitted during creation, MongoDB automatically generates an ObjectId consisting of a 4-byte timestamp, 5-byte random value, and 3-byte incrementing counter.

What is the MongoDB Aggregation Pipeline

A multi-stage framework for data processing and transformation where documents pass through a pipeline of sequential stages (e.g., $match, $group, $project, $sort, $unwind) to produce aggregated analytical results.

Embedded (Denormalized) Documents and Referenced (Normalized) Documents in MongoDB

  • Embedded Documents: Child data stored directly inside the parent document (1-to-1 or bounded 1-to-few relations). Fast single-query reads, risks 16MB document size limit.
  • Referenced Documents: Storing child documents in separate collections linked via _id references (1-to-many or many-to-many relations). Eliminates data duplication, requires multiple queries or $lookup joins.

What is Sharding in MongoDB

A horizontal scaling method that distributes data sets across multiple physical servers (shards) using a shard key, allowing clusters to scale storage and throughput beyond the limits of a single machine.

What is MongoDB Atlas

A fully managed multi-cloud database service developed by MongoDB, automating provisioning, multi-region replication, automated patching, backups, and horizontal auto-scaling on AWS, Google Cloud, and Azure.

✓Topic 1 covered: Core Definitions & Key Concepts
Next: Conceptual & Architectural Analysis↓

Conceptual & Architectural Analysis

Conceptual & Architectural Analysis

Compare Relational SQL (PostgreSQL/MySQL) vs Document NoSQL (MongoDB)

DimensionSQL (PostgreSQL / MySQL)NoSQL Document (MongoDB)
Data ModelFixed Relational Tables (Rows & Columns)Dynamic Schema Documents (BSON / JSON)
Schema EnforcementStrict compile-time schema validationDynamic, schema-flexible, polymorphic
RelationshipsForeign Keys and relational JOIN operationsEmbedded documents or manual $lookup references
Scaling ArchitecturePrimarily Vertical (Bigger CPU/RAM)Natively Horizontal (Sharding across clusters)
TransactionsStrong ACID by defaultTunable consistency; multi-document ACID since v4.0
Best Used ForFinancial ledgers, complex relational joinsProduct catalogs, content hubs, IoT telemetry

The Anatomy of a BSON Document and its Advantages over Plain JSON

BSON (Binary JSON) Architecture

  • Type Richness: Standard JSON natively supports only String, Number, Boolean, Array, Object, and Null. BSON extends this with: Date (64-bit int epoch), Decimal128 (precise currency math), BinaryData (blobs/images), Int32, Int64, Timestamp, and ObjectId.
  • Fast Traversal & Indexing: BSON prefixes elements with byte lengths and type tags, allowing the query engine to skip non-matching document fields without parsing the entire text payload into memory.

Detail the MongoDB Aggregation Pipeline with Real-World Code Example

MongoDB Aggregation Framework

JavaScript
1db.orders.aggregate([
2    // Stage 1: Filter completed orders in year 2024
3    { $match: { status: 'completed', orderDate: { $gte: ISODate('2024-01-01') } } },
4    // Stage 2: Flatten line items array
5    { $unwind: '$items' },
6    // Stage 3: Group by item category and compute revenue
7    { $group: {
8        _id: '$items.category',
9        totalRevenue: { $sum: { $multiply: ['$items.price', '$items.quantity'] } },
10        averageOrderSize: { $avg: '$items.quantity' }
11    }},
12    // Stage 4: Filter high-grossing categories
13    { $match: { totalRevenue: { $gt: 50000 } } },
14    // Stage 5: Sort descending
15    { $sort: { totalRevenue: -1 } }
16]);

Data Modeling in MongoDB: Embedding vs Referencing Decision Matrix

MongoDB Schema Design Heuristics

  1. Favor Embedding Unless Compelling Reason Exists:
  • Embed if entities have 'contains' relationships (e.g., User with 2 postal addresses).
  • Embed for 1-to-1 relationships and bounded 1-to-few relationships (< 100 items).
  • Guarantees atomic updates and single-read disk I/O.
  1. Favor Referencing When:
  • Unbounded 1-to-many relationships (e.g., Sensor logging millions of events; embedding will breach the 16MB document limit).
  • Many-to-many relationships (e.g., Students and Courses).
  • Frequently updated shared entities to prevent updating thousands of embedded duplicate copies.

MongoDB Replica Sets: Primary, Secondary, and Automated Failover

MongoDB High Availability Replica Set

System Architecture / Concept DiagramHorizontal Scroll Enabled
               +----------------------+
               |   Primary Node       | (Accepts all write operations)
               +----------+-----------+
                          |
              Replication | Oplog Streams
                          v
         +----------------+----------------+
         |                                 |
         v                                 v
+------------------+             +------------------+
| Secondary Node 1 |             | Secondary Node 2 |
+------------------+             +------------------+
  • Heartbeat & Failover: Nodes exchange heartbeats every 2 seconds. If Primary fails to respond for > 10 seconds, Secondary nodes initiate a Raft-based consensus election and elect a new Primary within < 12 seconds with zero human intervention.
✓Topic 2 covered: Conceptual & Architectural Analysis
Next: In-Depth Analysis & Implementation↓

In-Depth Analysis & Implementation

In-Depth Analysis & Implementation

Provide an In-Depth Analysis of Distributed Database Theory: CAP Theorem, PACELC Theorem, and Tunable Read/Write Concerns in MongoDB

Distributed Consistency & Consensus Theory

1. The PACELC Theorem (Daniel Abadi)

Extends the binary CAP theorem by analyzing distributed systems during normal operation when NO partition exists:

  • If Partition (P): Tradeoff between Availability (A) and Consistency (C).
  • Else (E): Tradeoff between Latency (L) and Consistency (C).
  • MongoDB Classification: PC/EC by default (favors consistency during partition; favors consistency over latency during normal operations, but tunable to PA/EL).

2. Tunable Write Concern (w) and Read Concern in MongoDB

  • Write Concern `w: 1`: Confirms write as soon as primary node acknowledges memory write (Fast, but data lost if primary crashes before disk sync).
  • Write Concern `w: 'majority'`: Confirms write only after the majority of replica set nodes have committed the operation to their in-memory journaling log (Immune to rollbacks).
  • Read Concern `'majority'`: Queries return data that has been acknowledged by a majority of nodes, preventing dirty reads of uncommitted transactions.

Design and Build a High-Throughput Node.js Application Interfacing with MongoDB via Mongoose with Schema Validation and Indexing

Production Mongoose Engineering

JavaScript
1import mongoose from 'mongoose';
2

3const courseSchema = new mongoose.Schema({
4    courseCode: {
5        type: String,
6        required: [true, 'Course code is mandatory'],
7        unique: true,
8        uppercase: true,
9        trim: true,
10        index: true // Single field index for fast lookups
11    },
12    title: { type: String, required: true },
13    credits: { type: Number, min: 1, max: 6, default: 4 },
14    semester: { type: Number, required: true, min: 1, max: 6 },
15    tags: [String]
16}, {
17    timestamps: true,
18    toJSON: { virtuals: true },
19    toObject: { virtuals: true }
20});
21

22// Compound Index: Optimizes queries filtering by semester and sorting by credits
23courseSchema.index({ semester: 1, credits: -1 });
24

25// Pre-Save Middleware Hook
26courseSchema.pre('save', function(next) {
27    console.log(`Saving course: ${this.courseCode}`);
28    next();
29});
30

31export const Course = mongoose.model('Course', courseSchema);

MongoDB Indexing Internal Mechanics: B-Trees, Compound Indexes, and the ESR (Equality, Sort, Range) Rule

MongoDB Indexing Mechanics

1. WiredTiger B-Tree Storage Engine

  • MongoDB indexes are structured as balanced B-Trees on disk, providing O(\log N) search, insertion, and deletion complexity.
  • Covered Query: A query where all fields in the query predicate and all projected return fields are contained entirely within the index itself. WiredTiger fulfills the query directly from RAM without touching actual document data files.

2. The Golden ESR Indexing Rule

When building compound indexes for complex queries containing Equality filters, Sort clauses, and Range filters, index fields must be strictly ordered as:

  1. E (Equality): Place exact match equality fields first ({ status: 'active' }).
  2. S (Sort): Place fields determining sort order second ({ createdAt: -1 }).
  3. R (Range): Place range filter inequality fields last ({ age: { $gt: 21 } }).
  • Violating the ESR rule forces MongoDB to execute expensive in-memory blocking sorts (SORT_KEY_GENERATOR), causing query degradation under high loads.
✓Topic 3 covered: In-Depth Analysis & Implementation
Next: Important Exam Points & Quick Revision↓

Important Exam Points & Quick Revision

Important Exam Points & Quick Revision

Examiners frequently test: SQL vs NoSQL comparison matrix (5/10 marks), CAP theorem (5 marks), Embedding vs Referencing heuristics (5/10 marks), and MongoDB Aggregation pipeline stages (10/15 marks).

Practice Ready

Ready to practice Unit 4?

You've read the core concepts. Take the balanced 10-question practice set to test your mastery!

Save progress