Gallery
Share
Explore

The college enrollment system: Joining JSON Schema

Learning Outcomes:
JOINING JSON Schema

MongoDB Demo Lab: College Enrollment System

1. Collection Structures:

// students collection
{
studentId: String,
name: String,
email: String,
program: String,
enrollmentDate: Date
}

// courses collection
{
courseId: String,
courseName: String,
credits: Number,
department: String,
prerequisites: [String]
}

// enrollments collection
{
enrollmentId: String,
studentId: String,
courseId: String,
semester: String,
grade: String,
status: String
}

2. Demo Queries:

// Find all courses a student is enrolled in
db.enrollments.aggregate([
{
$match: { studentId: "ST101" }
},
{
$lookup: {
from: "courses",
localField: "courseId",
foreignField: "courseId",
as: "courseDetails"
}
}
]);

// Get complete enrollment information by student projected to: course details
db.enrollments.aggregate([
{
$lookup: {
from: "students",
localField: "studentId",
foreignField: "studentId",
as: "studentInfo"
}
},
{
$lookup: {
from: "courses",
localField: "courseId",
foreignField: "courseId",
as: "courseInfo"
}
}
]);


How the College Enrollment System Works

Today, we'll be diving into the College Enrollment System, a practical application of MongoDB to model and manage real-world data.
Understanding how this system works will not only prepare you for managing complex data relationships but will also build the foundation for designing enterprise applications like the United Earth Space Exploration Command system you’ll tackle in your assignment.
Let’s break it down step by step, explaining each component of the system and how it fits together.
⁠

1. Overview of the College Enrollment System

Imagine we are managing a college's enrollment system digitally.
The system keeps track of three key entities:
Students: Who is enrolled at the college.
Courses: What classes the college offers.
Enrollments: The link between students and courses, storing details of which student is taking which course, and additional information like semester and grades.
MongoDB’s ability to store flexible, schema-less data makes it a perfect candidate for managing these interconnected relationships.
⁠

2. Structure of the Data

To handle this system, we create three collections in MongoDB:

a. Students Collection

This stores information about each student:
studentId: Unique identifier for the student.
name: The student’s full name.
email: Their email address.
program: The academic program they are enrolled in.
enrollmentDate: When they joined.
Example document:
{
studentId: "ST101",
name: "John Doe",
email: "john.doe@example.com",
program: "Computer Science",
enrollmentDate: ISODate("2023-09-01")
}

⁠

b. Courses Collection

This represents the courses taught at the college:
courseId: Unique course identifier.
courseName: The name of the course.
credits: The number of credits the course provides.
department: Which academic department it belongs to.
prerequisites: Other courses a student must complete before taking this one.
Example document:
{
courseId: "CS101",
courseName: "Introduction to Programming",
credits: 3,
department: "Computer Science",
prerequisites: []
}

⁠

c. Enrollments Collection

At the heart of the system, this collection links students to the courses they’ve enrolled in:
enrollmentId: Unique ID for each enrollment.
studentId: Links to the studentId field in the students collection.
courseId: Links to the courseId field in the courses collection.
semester: Indicates the semester during which the student is enrolled.
grade: Records their final grade for the course.
status: Whether the enrollment is Active, Dropped, or Completed.
Example document:
{
enrollmentId: "EN1001",
studentId: "ST101",
courseId: "CS101",
semester: "Fall 2023",
grade: "A",
status: "Completed"
}
⁠

3. How the System Works Together

The enrollments collection acts as a bridge between students and courses.
Joining Collections (via $lookup):
A lookup operation allows us to retrieve related data from one collection into another. For example:
To find all courses a specific student is enrolled in.
To enrich enrollment records with detailed information on students and courses.
⁠

4. Examples of Queries

a. Find All Courses Taken by a Student

Here’s how we use $lookup to find all the courses a student is enrolled in:
db.enrollments.aggregate([
{
$match: { studentId: "ST101" } // Filter enrollments for the specific student
},
{
$lookup: {
from: "courses", // The "courses" collection to join with
localField: "courseId", // The field in "enrollments" to match
foreignField: "courseId", // The field in "courses" to match
as: "courseDetails" // Alias for the resulting nested array
}
}
]);
How it works:
The query first filters the enrollments for a specific studentId.
$lookup pulls matching courseId records from the courses collection.
The results include all relevant course details within a courseDetails array.
⁠

b. Enrich Enrollment Data with Student and Course Info

You can join students, enrollments, and courses to create a complete view:
db.enrollments.aggregate([
{
$lookup: {
from: "students",
localField: "studentId",
foreignField: "studentId",
as: "studentInfo"
}
},
{
$lookup: {
from: "courses",
localField: "courseId",
foreignField: "courseId",
as: "courseInfo"
}
}
]);

How it works:
The first $lookup includes detailed information about the student (from the students collection).
The second $lookup pulls in data about the course (from the courses collection).
The enriched enrollment provides a complete picture: the student, the course, and how they connect.
⁠

5. Advantages of MongoDB for This System

Here’s why MongoDB works so well for this type of system:
Flexibility:
MongoDB handles schema-less data, so you can easily add new fields or relationships as the system evolves.
Efficiency:
$lookup provides powerful ways to fetch related data directly, eliminating the need for multiple queries.
Scalability:
Collections can handle large amounts of data, perfect for growing student and course datasets.
Real-World Modeling:
The flexibility of JSON-like documents maps well to how real-world entities interact (students taking courses, etc.).
⁠

6. Bringing It All Together

This system serves as an excellent practical example for understanding data modeling and aggregation in MongoDB.
From managing student records to tracking course enrollments, all the pieces come together seamlessly using MongoDB's robust features.
Once you've mastered this, you’ll be well-equipped to design even more complex systems, like your United Earth Space Exploration Command project.
⁠

7. Class Lab Activity

For today’s hands-on portion, you’ll:
Insert sample data for students, courses, and enrollments into MongoDB.
Write $lookup queries to:
Find all courses for a specific student.
Enrich enrollment data with related student and course information.
⁠

Closing

This college enrollment system is more than just a classroom exercise—it models real-world data challenges you may face as developers. Once you've worked with this, transitioning to designing scalable applications like the UESEC Enterprise System will feel much more approachable. Let’s dive into our hands-on lab and start transforming theory into practice!

Comparison of MongoDB Aggregation (Projection Joins) vs SQL Predicate Joins for Many-to-Many Relationships
⁠
Let’s walk you through a comparative analysis of how MongoDB handles many-to-many relationships in the context of projection (using $lookup) versus the traditional SQL predicate joins.

We will look at how these approaches work in the context of our College Enrollment System, which involves students, courses, and their enrollments.
By the end of this comparison, you'll understand the advantages that MongoDB's embedded JSON model and aggregation framework offer over SQL's reliance on relational tables and predicate joins. This will give you a clearer picture of why NoSQL databases are especially suited to certain kinds of modern applications.
⁠

SQL Solution: Many-to-Many with Relational Tables

In SQL, managing many-to-many relationships involves three steps:
Primary Tables: We store students and courses separately.
Example schema:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);

CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(255),
credits INT
);

Relationship Table: To model the many-to-many relationship between students and courses, we need an intermediate table (relator table)—enrollments:
Example schema:
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
semester VARCHAR(255),
grade VARCHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Using JOINs to Query Relationships:
To retrieve all courses a student is enrolled in, we write a SQL predicate join:
SELECT students.name, courses.course_name, enrollments.grade
FROM enrollments
JOIN students ON enrollments.student_id = students.student_id
JOIN courses ON enrollments.course_id = courses.course_id
WHERE students.student_id = 'ST101';

⁠

Disadvantages of SQL’s Predicate Joins in Many-to-Many Relationships:

Relator Table Maintenance: For every many-to-many relationship, SQL requires an additional intermediary table (enrollments). This adds complexity and introduces potential errors when foreign keys are mismatched.
Clumsy Query Syntax: Joins for even moderately complex relationships become verbose and harder to debug.
Lack of Schema Flexibility: Adding new fields (e.g., attendancePercentage) requires schema changes across all related tables.
Performance Hit: Joins require SQL to execute complex queries and access multiple tables—costly for large datasets.
⁠

MongoDB Solution: Many-to-Many with Embedded JSON and Aggregation

MongoDB tackles many-to-many relationships differently, using embedded JSON documents and $lookup joins. Let’s break this process down in the context of our College Enrollment System.
⁠

1. Primary Collections

We create three collections:
students
courses
enrollments (used as the intermediate collection, just like SQL, but embedded data reduces dependency issues).

2. The $lookup Operation (MongoDB Equivalent of SQL Joins)

In MongoDB, $lookup enables us to perform a join-like operation across collections by projecting related documents into the result of an aggregation pipeline.
For example:
Find all courses a student is taking:
db.enrollments.aggregate([
{
$match: { studentId: "ST101" }
},
{
$lookup: {
from: "courses", // The collection we're joining to
localField: "courseId", // Field in enrollments collection
foreignField: "courseId", // Field to match in courses collection
as: "courseDetails" // Name of the resulting joined field
}
},
{
$project: {
_id: 0,
studentId: 1,
"courseDetails.courseName": 1,
"courseDetails.credits": 1,
"semester": 1,
grade: 1
}
}
]);
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.