Share
Explore

Worksheet: JSON exercise with roles, steps, and deliverables.

You can use this as a Template for your upcoming assignment:

⁠

📄 Paper Exercise

Model a College Enrollment System

Mission Objective: Design a NoSQL (MongoDB) data model using collections and documents for a college enrollment system.
Collections to model:
students
classes
enrollments
⁠

0) Team Setup (1 min)

Form teams of 3–4.
Assign roles: Lead Modeler, Scribe, Challenger, Presenter.
⁠

1) Constraints & Ground Rules

MongoDB stores documents (JSON) inside collections.
Each document has an _id (unique identifier).
Use lowerCamelCase for field names.
Aim for readability and queryability. Avoid over-nesting.
⁠

2) Required Fields

You must include the following minimal fields. You may add more if useful.

students

_id (ObjectId or string like "stu_1234")
name (string)
email (string, unique)
major (string)

classes

_id (ObjectId or string like "CPSC-101-F25")
courseCode (string, e.g., "CPSC101")
title (string)
instructor (string)
schedule (array or object; e.g., days/times)

enrollments

_id (ObjectId or string like "enr_9876")
studentId (references students._id)
classId (references classes._id)
dateEnrolled (ISO date string)
🍪 Tip: enrollments is the join collection that links students ↔ classes.
⁠

3) Modeling Steps

Step A — Draft Example Documents (6 min)

On paper, sketch one example document for each collection.
Template — Students (copy this and fill):
{
"_id": "stu_1001",
"name": "Amy Tan",
"email": "amy.tan@example.edu",
"major": "Computer Science"
}

Template — Classes (copy and fill):
{
"_id": "CPSC-101-F25",
"courseCode": "CPSC101",
"title": "Intro to Programming",
"instructor": "Dr. Singh",
"schedule": {
"days": ["Mon", "Wed"],
"startTime": "10:00",
"endTime": "11:20",
"room": "ENG-201"
}
}

Template — Enrollments (copy and fill):
{
"_id": "enr_5001",
"studentId": "stu_1001",
"classId": "CPSC-101-F25",
"dateEnrolled": "2025-09-10"
}

✅ Decision Point: For schedule, choose one format:
Embedded object (as above), or
Array of meeting objects (e.g., [ { day:"Mon", start:"10:00", end:"11:20", room:"ENG-201" } ]). Be consistent.
⁠

Step B — Queries You Must Support (4 min)

Beside your JSON, write the intent of these queries (natural language is fine):
Find all students named “Amy”
List all students enrolled in class CPSC-101-F25
List all classes a given student (stu_1001) is enrolled in
Find classes taught by “Dr. Singh” on Mondays
🎯 Hint: Your field names and types should make these queries straightforward.
⁠

4) Enhancements

Add status to enrollments ("active" | "dropped" | "waitlisted").
Add credits to classes.
Add year/term (e.g., "F25") to classes.
Add createdAt/updatedAt timestamps.
⁠

5) Validation Checklist

Before you present, verify:
Every collection has _id and the required fields.
enrollments.studentId refers to an existing students._id.
enrollments.classId refers to an existing classes._id.
Field names use lowerCamelCase and consistent types.
Your model answers the four queries above.
⁠

6) Presentation

Presenter shows the three example documents.
Explain 1–2 design choices (e.g., schedule format, id format).
Walk the class through how your model supports the four queries.
⁠

Common Pitfalls (read if stuck)

❌ Embedding entire student or class documents inside enrollments (causes data duplication).
❌ Inconsistent key names (studentID vs studentId). Pick one.
❌ Dates as free-form text. Use ISO style (e.g., "2025-09-10").
⁠

Instructor Notes (for you)

Circulate during Step A to enforce proper references in enrollments.
Ask one team why they chose embedded vs array for schedule.
Quick cold-call: “Which field(s) should be unique?” (Answer: email, possibly courseCode+term composite.)
If a team finishes early, assign a composite index thought exercise:
“If we query by classId and status often, note an index: { classId: 1, status: 1 } (Compass later).”
⁠
Deliverable: Hand in the paper sheet with the three example documents and your answers to the four queries (in words).

Let’s see how MongoDB handles connections between collections, and how this differs from SQL joins.
⁠

📘 MongoDB Connections Between Collections (Analogy to SQL Joins)

1. In SQL (Relational World)

Tables are strictly relational.
A student row references a class row through a foreign key (e.g., enrollments.student_id → students.id).
To fetch related data, you use a JOIN in the query:
SELECT s.name, c.title
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN classes c ON e.class_id = c.id;

⁠

2. In MongoDB (NoSQL Document World)

MongoDB does not require JOINs (it can avoid them by embedding data).
Instead, you have two options:

Option A — References (Normalized, SQL-like)

Store ObjectId (or string IDs) in one collection that points to another.
Example (enrollments references students and classes):
{
"_id": "enr_5001",
"studentId": "stu_1001",
"classId": "CPSC-101-F25",
"dateEnrolled": "2025-09-10"
}

To fetch related documents, the application code (Mongoose/Node.js) performs multiple queries:
Find the enrollment.
Fetch the referenced student and class separately.
Stitch them together in memory.
⚓ This is the closest analogy to SQL joins, but handled by the application, not the database engine.
⁠

Option B — Embedding (Denormalized, No Joins Needed)

Store related documents directly inside another document.
Example: embedding student info inside enrollments:
{
"_id": "enr_5001",
"student": { "id": "stu_1001", "name": "Amy Tan", "email": "amy.tan@example.edu" },
"class": { "id": "CPSC-101-F25", "title": "Intro to Programming" },
"dateEnrolled": "2025-09-10"
}

Advantage: one query = all data.
Tradeoff: If a student changes email, it must be updated in all enrollment documents (risk of duplication errors).
⁠

3. MongoDB Joins Do Exist (Aggregation $lookup)

Mongo supports joins across collections using the aggregation framework:
db.enrollments.aggregate([
{
$lookup: {
from: "students",
localField: "studentId",
foreignField: "_id",
as: "studentInfo"
}
},
{
$lookup: {
from: "classes",
localField: "classId",
foreignField: "_id",
as: "classInfo"
}
}
])

This is equivalent to SQL joins, but heavier. Mongo’s philosophy encourages embedding or referencing instead of frequent $lookup.
⁠

4. The Mongoose Way (Populate = Application-Side Join)

In Node.js with Mongoose, you can define relationships and let Mongoose “auto-join” via .populate():
// Enrollment schema
const enrollmentSchema = new mongoose.Schema({
studentId: { type: mongoose.Schema.Types.ObjectId, ref: "Student" },
classId: { type: mongoose.Schema.Types.ObjectId, ref: "Class" },
dateEnrolled: Date
});

// Query with populate (like JOIN)
Enrollment.find()
.populate("studentId")
.populate("classId")
.then(result => console.log(result));

This works like a JOIN in SQL, but the join happens at the application layer via multiple queries.
⁠

⚓ Command Summary

SQL joins are database-native.
Mongo joins can be:
References + multiple queries (normalized, SQL-like).
Embedding (denormalized, no join needed).
$lookup aggregation (true join, but slower).
Mongoose populate() (application-managed join).
⁠

Side-by-side table (SQL vs Mongo for students–classes–enrollments)

Table 1
Concept
SQL (Relational)
MongoDB (Document)
Data Structures
Tables with rows and columns.
Collections with JSON documents.
Primary Key
id (integer, auto-increment).
_id (ObjectId or custom string).
Foreign Keys
Enforced at DB level.
Not enforced — just store IDs (references).
Relationships
Defined with FOREIGN KEY.
By embedding or storing references.
Join Queries
Native with JOIN.
Application handles joins, or $lookup aggregation.
There are no rows in this table
⁠


Here’s a side-by-side briefing table that shows how the students–classes–enrollments domain works in SQL vs MongoDB. This gives a crystal-clear picture of how joins translate.
⁠

📊 SQL vs MongoDB — Students, Classes, Enrollments

Table 2
Concept
SQL (Relational)
MongoDB (Document)
Data Structures
Tables with rows and columns.
Collections with JSON documents.
Primary Key
id (integer, auto-increment).
_id (ObjectId or custom string).
Foreign Keys
Enforced at DB level.
Not enforced — just store IDs (references).
Relationships
Defined with FOREIGN KEY.
By embedding or storing references.
Join Queries
Native with JOIN.
Application handles joins, or $lookup aggregation.
There are no rows in this table
⁠
⁠

Example Data

SQL

students table
id | name | email | major
---+---------+---------------------+-----------------
1 | Amy Tan | amy.tan@example.edu | Computer Science

classes table
id | course_code | title | instructor | schedule
---+-------------+------------------------+------------+---------
10 | CPSC101 | Intro to Programming | Dr. Singh | Mon/Wed 10-11:20

enrollments table
id | student_id | class_id | date_enrolled
---+------------+----------+--------------
100 | 1 | 10 | 2025-09-10

⁠

MongoDB (Referenced Approach)

students collection
{
"_id": "stu_1001",
"name": "Amy Tan",
"email": "amy.tan@example.edu",
"major": "Computer Science"
}

classes collection
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.