Deep Thought

Setup - Database

11 min read

Database Setup

Run this script once to create the database and collections used in all exercises.

Instructions

  1. Open your MongoDB shell (mongosh) and connect to your MongoDB instance.
  2. Copy and paste the entire script below into the shell.
  3. Verify the setup by running the check commands at the bottom.

Database name: aggregation_lab

Setup Script

// ============================================================
// AGGREGATION LAB — DATABASE SETUP
// ============================================================
// Run this script once in mongosh to set up all collections
// needed for the exercises.
// ============================================================
 
use aggregation_lab;
 
// Clean up if re-running
db.employees.drop();
db.departments.drop();
db.projects.drop();
db.tasks.drop();
db.orders.drop();
db.products.drop();
db.customers.drop();
db.log_entries.drop();
 
// ============================================================
// 1. EMPLOYEES COLLECTION
// ============================================================
db.employees.insertMany([
  {
    _id: ObjectId("aaa000000000000000000001"),
    name: "Anna Weber",
    email: "anna.weber@company.at",
    age: 34,
    salary: 52000,
    department_id: ObjectId("bbb000000000000000000001"),
    role: "DEVELOPER",
    skills: ["JavaScript", "Python", "MongoDB"],
    ratings: [4, 5, 4, 5, 4],
    hireDate: ISODate("2019-03-15"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000002"),
    name: "Markus Huber",
    email: "markus.huber@company.at",
    age: 28,
    salary: 45000,
    department_id: ObjectId("bbb000000000000000000001"),
    role: "DEVELOPER",
    skills: ["Java", "Spring", "SQL"],
    ratings: [3, 4, 3, 4],
    hireDate: ISODate("2021-06-01"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000003"),
    name: "Sofia Berger",
    email: "sofia.berger@company.at",
    age: 41,
    salary: 68000,
    department_id: ObjectId("bbb000000000000000000002"),
    role: "MANAGER",
    skills: ["Leadership", "Scrum", "Budgeting"],
    ratings: [5, 5, 5, 4, 5],
    hireDate: ISODate("2015-01-10"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000004"),
    name: "Lukas Steiner",
    email: "lukas.steiner@company.at",
    age: 25,
    salary: 38000,
    department_id: ObjectId("bbb000000000000000000003"),
    role: "INTERN",
    skills: ["Python", "Data Analysis"],
    ratings: [3, 3, 4],
    hireDate: ISODate("2023-09-01"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000005"),
    name: "Elena Fischer",
    email: "elena.fischer@company.at",
    age: 37,
    salary: 58000,
    department_id: ObjectId("bbb000000000000000000002"),
    role: "DEVELOPER",
    skills: ["React", "TypeScript", "Node.js", "MongoDB"],
    ratings: [4, 5, 5, 5],
    hireDate: ISODate("2018-07-20"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000006"),
    name: "Thomas Gruber",
    email: "thomas.gruber@company.at",
    age: 52,
    salary: 75000,
    department_id: ObjectId("bbb000000000000000000001"),
    role: "LEAD",
    skills: ["Architecture", "Java", "Cloud", "DevOps"],
    ratings: [5, 4, 5, 5, 4, 5],
    hireDate: ISODate("2012-11-05"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000007"),
    name: "Lisa Maier",
    email: "lisa.maier@company.at",
    age: 30,
    salary: 48000,
    department_id: ObjectId("bbb000000000000000000003"),
    role: "ANALYST",
    skills: ["SQL", "Power BI", "Excel", "Python"],
    ratings: [4, 4, 4, 3, 4],
    hireDate: ISODate("2020-02-14"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000008"),
    name: "David Wimmer",
    email: "david.wimmer@company.at",
    age: 45,
    salary: 62000,
    department_id: ObjectId("bbb000000000000000000002"),
    role: "DEVELOPER",
    skills: ["C#", ".NET", "Azure"],
    ratings: [3, 4, 3, 3, 4],
    hireDate: ISODate("2016-05-22"),
    active: false
  },
  {
    _id: ObjectId("aaa000000000000000000009"),
    name: "Julia Hofer",
    email: "julia.hofer@company.at",
    age: 29,
    salary: 43000,
    department_id: ObjectId("bbb000000000000000000004"),
    role: "DESIGNER",
    skills: ["Figma", "CSS", "UI/UX"],
    ratings: [5, 5, 4, 5],
    hireDate: ISODate("2022-01-15"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000010"),
    name: "Patrick Moser",
    email: "patrick.moser@company.at",
    age: 33,
    salary: 51000,
    department_id: ObjectId("bbb000000000000000000004"),
    role: "DEVELOPER",
    skills: ["JavaScript", "React", "CSS", "HTML"],
    ratings: [4, 3, 4, 4, 3],
    hireDate: ISODate("2020-08-10"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000011"),
    name: "Sarah Pichler",
    email: "sarah.pichler@company.at",
    age: 38,
    salary: 64000,
    department_id: ObjectId("bbb000000000000000000001"),
    role: "LEAD",
    skills: ["Python", "Machine Learning", "TensorFlow", "Statistics"],
    ratings: [5, 5, 5, 5],
    hireDate: ISODate("2017-04-01"),
    active: true
  },
  {
    _id: ObjectId("aaa000000000000000000012"),
    name: "Michael Eder",
    email: "michael.eder@company.at",
    age: 27,
    salary: 41000,
    department_id: ObjectId("bbb000000000000000000003"),
    role: "DEVELOPER",
    skills: ["Python", "Django", "PostgreSQL"],
    ratings: [3, 4, 3],
    hireDate: ISODate("2022-10-01"),
    active: true
  }
]);
 
// ============================================================
// 2. DEPARTMENTS COLLECTION
// ============================================================
db.departments.insertMany([
  {
    _id: ObjectId("bbb000000000000000000001"),
    name: "Engineering",
    budget: 500000,
    location: "Vienna",
    tags: ["tech", "core", "product"]
  },
  {
    _id: ObjectId("bbb000000000000000000002"),
    name: "Product",
    budget: 350000,
    location: "Vienna",
    tags: ["product", "management"]
  },
  {
    _id: ObjectId("bbb000000000000000000003"),
    name: "Data Science",
    budget: 280000,
    location: "Graz",
    tags: ["tech", "analytics", "research"]
  },
  {
    _id: ObjectId("bbb000000000000000000004"),
    name: "Design",
    budget: 200000,
    location: "Linz",
    tags: ["creative", "product", "UX"]
  }
]);
 
// ============================================================
// 3. PROJECTS COLLECTION
// ============================================================
db.projects.insertMany([
  {
    _id: ObjectId("ccc000000000000000000001"),
    title: "Customer Portal Redesign",
    status: "IN_PROGRESS",
    priority: "HIGH",
    department_id: ObjectId("bbb000000000000000000004"),
    team: [
      ObjectId("aaa000000000000000000009"),
      ObjectId("aaa000000000000000000010"),
      ObjectId("aaa000000000000000000005")
    ],
    milestones: [
      { name: "Design Phase", deadline: ISODate("2025-02-01"), completed: true },
      { name: "Frontend Dev", deadline: ISODate("2025-05-01"), completed: false },
      { name: "Testing", deadline: ISODate("2025-06-15"), completed: false }
    ],
    estimatedCost: 120000,
    actualCost: 85000
  },
  {
    _id: ObjectId("ccc000000000000000000002"),
    title: "Data Pipeline Migration",
    status: "IN_PROGRESS",
    priority: "CRITICAL",
    department_id: ObjectId("bbb000000000000000000001"),
    team: [
      ObjectId("aaa000000000000000000006"),
      ObjectId("aaa000000000000000000001"),
      ObjectId("aaa000000000000000000002"),
      ObjectId("aaa000000000000000000011")
    ],
    milestones: [
      { name: "Architecture Review", deadline: ISODate("2025-01-15"), completed: true },
      { name: "Migration Phase 1", deadline: ISODate("2025-03-01"), completed: true },
      { name: "Migration Phase 2", deadline: ISODate("2025-05-01"), completed: false },
      { name: "Validation", deadline: ISODate("2025-06-01"), completed: false }
    ],
    estimatedCost: 250000,
    actualCost: 190000
  },
  {
    _id: ObjectId("ccc000000000000000000003"),
    title: "ML Recommendation Engine",
    status: "PLANNING",
    priority: "MEDIUM",
    department_id: ObjectId("bbb000000000000000000003"),
    team: [
      ObjectId("aaa000000000000000000011"),
      ObjectId("aaa000000000000000000004"),
      ObjectId("aaa000000000000000000007")
    ],
    milestones: [
      { name: "Research", deadline: ISODate("2025-04-01"), completed: false },
      { name: "Prototype", deadline: ISODate("2025-07-01"), completed: false }
    ],
    estimatedCost: 180000,
    actualCost: 0
  },
  {
    _id: ObjectId("ccc000000000000000000004"),
    title: "Internal Dashboard",
    status: "COMPLETED",
    priority: "LOW",
    department_id: ObjectId("bbb000000000000000000002"),
    team: [
      ObjectId("aaa000000000000000000005"),
      ObjectId("aaa000000000000000000008")
    ],
    milestones: [
      { name: "Requirements", deadline: ISODate("2024-09-01"), completed: true },
      { name: "Development", deadline: ISODate("2024-11-01"), completed: true },
      { name: "Deployment", deadline: ISODate("2024-12-01"), completed: true }
    ],
    estimatedCost: 60000,
    actualCost: 55000
  },
  {
    _id: ObjectId("ccc000000000000000000005"),
    title: "Mobile App v2",
    status: "IN_PROGRESS",
    priority: "HIGH",
    department_id: ObjectId("bbb000000000000000000001"),
    team: [
      ObjectId("aaa000000000000000000001"),
      ObjectId("aaa000000000000000000010"),
      ObjectId("aaa000000000000000000009")
    ],
    milestones: [
      { name: "UI Design", deadline: ISODate("2025-01-15"), completed: true },
      { name: "Core Features", deadline: ISODate("2025-04-01"), completed: false },
      { name: "Beta Release", deadline: ISODate("2025-06-01"), completed: false }
    ],
    estimatedCost: 95000,
    actualCost: 40000
  },
  {
    _id: ObjectId("ccc000000000000000000006"),
    title: "Security Audit System",
    status: "PLANNING",
    priority: "CRITICAL",
    department_id: ObjectId("bbb000000000000000000001"),
    team: [
      ObjectId("aaa000000000000000000006"),
      ObjectId("aaa000000000000000000002")
    ],
    milestones: [
      { name: "Threat Assessment", deadline: ISODate("2025-05-01"), completed: false },
      { name: "Implementation", deadline: ISODate("2025-08-01"), completed: false }
    ],
    estimatedCost: 140000,
    actualCost: 0
  }
]);
 
// ============================================================
// 4. TASKS COLLECTION
// ============================================================
db.tasks.insertMany([
  { title: "Design login screen", project_id: ObjectId("ccc000000000000000000001"), assigned_to: ObjectId("aaa000000000000000000009"), status: "DONE", hours: 8, tags: ["design", "UI"] },
  { title: "Implement auth API", project_id: ObjectId("ccc000000000000000000001"), assigned_to: ObjectId("aaa000000000000000000010"), status: "IN_PROGRESS", hours: 16, tags: ["backend", "security"] },
  { title: "Write unit tests for auth", project_id: ObjectId("ccc000000000000000000001"), assigned_to: ObjectId("aaa000000000000000000005"), status: "TODO", hours: 6, tags: ["testing"] },
  { title: "Setup CI/CD pipeline", project_id: ObjectId("ccc000000000000000000002"), assigned_to: ObjectId("aaa000000000000000000006"), status: "DONE", hours: 12, tags: ["devops", "infrastructure"] },
  { title: "Migrate user table", project_id: ObjectId("ccc000000000000000000002"), assigned_to: ObjectId("aaa000000000000000000001"), status: "DONE", hours: 20, tags: ["backend", "database"] },
  { title: "Migrate order table", project_id: ObjectId("ccc000000000000000000002"), assigned_to: ObjectId("aaa000000000000000000001"), status: "IN_PROGRESS", hours: 24, tags: ["backend", "database"] },
  { title: "Validate migrated data", project_id: ObjectId("ccc000000000000000000002"), assigned_to: ObjectId("aaa000000000000000000011"), status: "TODO", hours: 10, tags: ["QA", "database"] },
  { title: "Build ETL pipeline", project_id: ObjectId("ccc000000000000000000002"), assigned_to: ObjectId("aaa000000000000000000002"), status: "IN_PROGRESS", hours: 30, tags: ["backend", "data"] },
  { title: "Research transformer models", project_id: ObjectId("ccc000000000000000000003"), assigned_to: ObjectId("aaa000000000000000000011"), status: "IN_PROGRESS", hours: 40, tags: ["research", "ML"] },
  { title: "Prepare training dataset", project_id: ObjectId("ccc000000000000000000003"), assigned_to: ObjectId("aaa000000000000000000004"), status: "TODO", hours: 15, tags: ["data", "ML"] },
  { title: "Analyze user behavior", project_id: ObjectId("ccc000000000000000000003"), assigned_to: ObjectId("aaa000000000000000000007"), status: "DONE", hours: 12, tags: ["analytics", "research"] },
  { title: "Design dashboard layout", project_id: ObjectId("ccc000000000000000000004"), assigned_to: ObjectId("aaa000000000000000000005"), status: "DONE", hours: 10, tags: ["design", "UI"] },
  { title: "Implement charts", project_id: ObjectId("ccc000000000000000000004"), assigned_to: ObjectId("aaa000000000000000000008"), status: "DONE", hours: 18, tags: ["frontend", "visualization"] },
  { title: "Design app navigation", project_id: ObjectId("ccc000000000000000000005"), assigned_to: ObjectId("aaa000000000000000000009"), status: "DONE", hours: 6, tags: ["design", "UX"] },
  { title: "Build home screen", project_id: ObjectId("ccc000000000000000000005"), assigned_to: ObjectId("aaa000000000000000000010"), status: "IN_PROGRESS", hours: 14, tags: ["frontend", "mobile"] },
  { title: "Implement push notifications", project_id: ObjectId("ccc000000000000000000005"), assigned_to: ObjectId("aaa000000000000000000001"), status: "TODO", hours: 8, tags: ["backend", "mobile"] },
  { title: "Define threat model", project_id: ObjectId("ccc000000000000000000006"), assigned_to: ObjectId("aaa000000000000000000006"), status: "TODO", hours: 20, tags: ["security", "planning"] },
  { title: "Review access controls", project_id: ObjectId("ccc000000000000000000006"), assigned_to: ObjectId("aaa000000000000000000002"), status: "TODO", hours: 16, tags: ["security", "audit"] }
]);
 
// ============================================================
// 5. ORDERS COLLECTION
// ============================================================
db.orders.insertMany([
  { _id: ObjectId("ddd000000000000000000001"), customer_id: ObjectId("eee000000000000000000001"), items: [ { product: "Laptop", quantity: 1, price: 1200 }, { product: "Mouse", quantity: 2, price: 25 } ], date: ISODate("2025-01-15"), status: "DELIVERED", paymentMethod: "CREDIT_CARD" },
  { _id: ObjectId("ddd000000000000000000002"), customer_id: ObjectId("eee000000000000000000002"), items: [ { product: "Keyboard", quantity: 1, price: 85 }, { product: "Monitor", quantity: 1, price: 450 }, { product: "USB Hub", quantity: 1, price: 30 } ], date: ISODate("2025-01-20"), status: "DELIVERED", paymentMethod: "PAYPAL" },
  { _id: ObjectId("ddd000000000000000000003"), customer_id: ObjectId("eee000000000000000000001"), items: [ { product: "Headphones", quantity: 1, price: 200 } ], date: ISODate("2025-02-03"), status: "DELIVERED", paymentMethod: "CREDIT_CARD" },
  { _id: ObjectId("ddd000000000000000000004"), customer_id: ObjectId("eee000000000000000000003"), items: [ { product: "Laptop", quantity: 2, price: 1200 }, { product: "Laptop Bag", quantity: 2, price: 45 }, { product: "Mouse", quantity: 2, price: 25 } ], date: ISODate("2025-02-14"), status: "SHIPPED", paymentMethod: "BANK_TRANSFER" },
  { _id: ObjectId("ddd000000000000000000005"), customer_id: ObjectId("eee000000000000000000004"), items: [ { product: "Monitor", quantity: 2, price: 450 }, { product: "Keyboard", quantity: 2, price: 85 } ], date: ISODate("2025-02-28"), status: "PROCESSING", paymentMethod: "CREDIT_CARD" },
  { _id: ObjectId("ddd000000000000000000006"), customer_id: ObjectId("eee000000000000000000002"), items: [ { product: "Webcam", quantity: 1, price: 75 }, { product: "Headphones", quantity: 1, price: 200 }, { product: "Mouse", quantity: 1, price: 25 } ], date: ISODate("2025-03-05"), status: "DELIVERED", paymentMethod: "PAYPAL" },
  { _id: ObjectId("ddd000000000000000000007"), customer_id: ObjectId("eee000000000000000000005"), items: [ { product: "Laptop", quantity: 1, price: 1200 }, { product: "Keyboard", quantity: 1, price: 85 }, { product: "Mouse", quantity: 1, price: 25 }, { product: "Headphones", quantity: 1, price: 200 } ], date: ISODate("2025-03-10"), status: "SHIPPED", paymentMethod: "CREDIT_CARD" },
  { _id: ObjectId("ddd000000000000000000008"), customer_id: ObjectId("eee000000000000000000003"), items: [ { product: "USB Hub", quantity: 3, price: 30 } ], date: ISODate("2025-03-15"), status: "DELIVERED", paymentMethod: "PAYPAL" },
  { _id: ObjectId("ddd000000000000000000009"), customer_id: ObjectId("eee000000000000000000001"), items: [ { product: "Monitor", quantity: 1, price: 450 }, { product: "Webcam", quantity: 1, price: 75 } ], date: ISODate("2025-03-20"), status: "PROCESSING", paymentMethod: "BANK_TRANSFER" },
  { _id: ObjectId("ddd000000000000000000010"), customer_id: ObjectId("eee000000000000000000004"), items: [ { product: "Laptop Bag", quantity: 1, price: 45 } ], date: ISODate("2025-03-25"), status: "CANCELLED", paymentMethod: "CREDIT_CARD" }
]);
 
// ============================================================
// 6. CUSTOMERS COLLECTION
// ============================================================
db.customers.insertMany([
  { _id: ObjectId("eee000000000000000000001"), name: "TechCorp GmbH", type: "BUSINESS", city: "Vienna", loyaltyTier: "GOLD", registeredSince: ISODate("2020-06-15") },
  { _id: ObjectId("eee000000000000000000002"), name: "Maria Schmidt", type: "PRIVATE", city: "Graz", loyaltyTier: "SILVER", registeredSince: ISODate("2021-03-22") },
  { _id: ObjectId("eee000000000000000000003"), name: "DataWorks AG", type: "BUSINESS", city: "Linz", loyaltyTier: "PLATINUM", registeredSince: ISODate("2019-01-10") },
  { _id: ObjectId("eee000000000000000000004"), name: "Florian Bauer", type: "PRIVATE", city: "Salzburg", loyaltyTier: "BRONZE", registeredSince: ISODate("2023-11-01") },
  { _id: ObjectId("eee000000000000000000005"), name: "CloudNine OG", type: "BUSINESS", city: "Vienna", loyaltyTier: "SILVER", registeredSince: ISODate("2022-07-18") }
]);
 
// ============================================================
// 7. LOG ENTRIES COLLECTION
// ============================================================
db.log_entries.insertMany([
  { timestamp: ISODate("2025-03-01T08:15:00Z"), level: "INFO", service: "auth", message: "User login successful", user_id: ObjectId("aaa000000000000000000001"), metadata: { ip: "192.168.1.10", browser: "Chrome" } },
  { timestamp: ISODate("2025-03-01T08:17:00Z"), level: "INFO", service: "auth", message: "User login successful", user_id: ObjectId("aaa000000000000000000003"), metadata: { ip: "192.168.1.20", browser: "Firefox" } },
  { timestamp: ISODate("2025-03-01T09:00:00Z"), level: "WARNING", service: "api", message: "Rate limit approaching", user_id: ObjectId("aaa000000000000000000001"), metadata: { endpoint: "/api/data", requests: 95 } },
  { timestamp: ISODate("2025-03-01T09:30:00Z"), level: "ERROR", service: "database", message: "Connection timeout", user_id: null, metadata: { host: "db-primary", retries: 3 } },
  { timestamp: ISODate("2025-03-01T09:31:00Z"), level: "ERROR", service: "database", message: "Connection timeout", user_id: null, metadata: { host: "db-primary", retries: 5 } },
  { timestamp: ISODate("2025-03-01T09:32:00Z"), level: "INFO", service: "database", message: "Connection restored", user_id: null, metadata: { host: "db-primary" } },
  { timestamp: ISODate("2025-03-01T10:00:00Z"), level: "INFO", service: "api", message: "Data export completed", user_id: ObjectId("aaa000000000000000000007"), metadata: { records: 15000, format: "CSV" } },
  { timestamp: ISODate("2025-03-01T10:15:00Z"), level: "WARNING", service: "auth", message: "Failed login attempt", user_id: null, metadata: { ip: "10.0.0.55", attempts: 3 } },
  { timestamp: ISODate("2025-03-01T11:00:00Z"), level: "INFO", service: "api", message: "User login successful", user_id: ObjectId("aaa000000000000000000005"), metadata: { ip: "192.168.1.30", browser: "Safari" } },
  { timestamp: ISODate("2025-03-01T11:30:00Z"), level: "ERROR", service: "api", message: "Internal server error", user_id: ObjectId("aaa000000000000000000005"), metadata: { endpoint: "/api/reports", statusCode: 500 } },
  { timestamp: ISODate("2025-03-01T12:00:00Z"), level: "INFO", service: "scheduler", message: "Daily backup started", user_id: null, metadata: { type: "full" } },
  { timestamp: ISODate("2025-03-01T12:45:00Z"), level: "INFO", service: "scheduler", message: "Daily backup completed", user_id: null, metadata: { type: "full", sizeGB: 12.5 } }
]);
 
// ============================================================
// VERIFICATION
// ============================================================
print("\n========================================");
print("  SETUP COMPLETE — Collection counts:");
print("========================================");
print("  employees:   " + db.employees.countDocuments());
print("  departments: " + db.departments.countDocuments());
print("  projects:    " + db.projects.countDocuments());
print("  tasks:       " + db.tasks.countDocuments());
print("  orders:      " + db.orders.countDocuments());
print("  customers:   " + db.customers.countDocuments());
print("  log_entries: " + db.log_entries.countDocuments());
print("========================================");
print("  Expected: 12, 4, 6, 18, 10, 5, 12");
print("========================================\n");

Expected Output

After running the script, you should see:

========================================
  SETUP COMPLETE — Collection counts:
========================================
  employees:   12
  departments: 4
  projects:    6
  tasks:       18
  orders:      10
  customers:   5
  log_entries: 12
========================================
  Expected: 12, 4, 6, 18, 10, 5, 12
========================================

Collection Overview

CollectionDocumentsDescription
employees12Company employees with skills, ratings, salary, department reference
departments4Department info with budget, location, tags
projects6Projects with milestones, team members, costs
tasks18Individual tasks assigned to employees, linked to projects
orders10Customer orders with item arrays
customers5Customer profiles with loyalty tiers
log_entries12System log entries with levels and metadata

Graph View

Backlinks