Master MongoDB document modeling, PostgreSQL query optimization, and Redis caching strategies. Write real commands, execute actual queries, and configure production-grade systems.
Database Administration - Module 8
Hands-on labs requiring you to write actual database commands and queries. No shortcuts!
Lab 22: MongoDB NoSQL Administration
NoSQL / Expert
Scenario: E-Commerce Product Catalog
ShopGlobal needs a scalable document database for their product catalog. You must write actual MongoDB commands to create the database, design document schemas, build indexes, and write aggregation pipelines. Each command will be validated for correctness - you cannot proceed without writing proper syntax.
Learning Objectives:
Schema Design: Write insertOne() with all required fields
Indexing: Create compound indexes with correct syntax
Aggregation: Build multi-stage pipelines with $match, $group, $sort
Sharding: Configure shard keys and enable sharding
?? Step-by-Step Instructions
Step 1: Create Database & Collection
Switch to database and create collection with JSON schema validation.
Required Commands (type EXACTLY):
1. use shopglobal
2. db.createCollection("products")
?? Tip: Database name must be shopglobal, collection must be products
Step 2: Insert Product Document
Insert a product with ALL required fields using proper BSON types.
Required Fields:
� sku: string (e.g., "PROD-001")
� name: string
� price: number (must be > 0)
� category: string
� inventory: number (must be >= 0)
� tags: array of strings
?? Tip: Use db.products.insertOne({...}) with ALL 6 fields!
Step 3: Create Compound Index
Create a compound index for efficient category + price queries.
?? Tip: Hashed shard key ensures even data distribution.
MongoDB Shell (mongosh)
mongosh 2.0 -- localhost:27017
MongoDB Shell v2.0.0
Connecting to: mongodb://localhost:27017
Using MongoDB: 7.0.0
Connected to replica set "rs0"test>Type your commands below. Press Enter to execute.
FinTech Analytics needs optimized SQL for their transaction processing system. You must write complete SQL statements - EXPLAIN ANALYZE queries, CREATE INDEX statements, partitioned tables, and optimized CTEs. Each query is validated for syntax and required clauses.
Learning Objectives:
Query Analysis: Write EXPLAIN ANALYZE statements
Index Creation: Create composite indexes with INCLUDE clause
Partitioning: Design PARTITION BY RANGE tables
Optimization: Write CTEs with window functions
?? Step-by-Step Instructions
Step 1: Analyze Query Performance
Write an EXPLAIN ANALYZE query to identify performance issues.
Required Query:
Must include: EXPLAIN ANALYZE
Must SELECT from: transactions table
Must have: WHERE clause with date filter
Must have: ORDER BY clause
?? Tip: Example: EXPLAIN ANALYZE SELECT * FROM transactions WHERE created_at >= '2024-01-01' ORDER BY amount DESC;
Step 2: Create Composite Index
Create an index to eliminate sequential scans identified in Step 1.
Required Syntax:
� Must use: CREATE INDEX
� Index name: idx_txn_account_date
� Table: transactions
� Columns: account_id, created_at DESC
� Must have: INCLUDE (amount)
?? Tip: INCLUDE creates a covering index - query can be answered from index alone!
Step 3: Create Partitioned Table
Design a table with range partitioning on timestamp column.
Required Elements:
� CREATE TABLE transactions_part
� Columns: id, account_id, amount, created_at TIMESTAMP
� Must include: PARTITION BY RANGE (created_at)
?? Tip: Partitioning enables partition pruning - only relevant partitions are scanned.
Step 4: Create Table Partition
Create a monthly partition for January 2024 data.
Required Syntax: CREATE TABLE transactions_2024_01 PARTITION OF transactions_part FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
?? Tip: Range is [start, end) - end date is exclusive!
Step 5: Write Optimized CTE Query
Create a Common Table Expression with window functions.
Required Elements:
� Must start with: WITH clause (CTE)
� Must use: SUM() OVER (PARTITION BY
� Must include: GROUP BY in final query
Run EXPLAIN ANALYZE to view the query execution plan...
--
Total Cost
--
Exec Time
--
Scan Type
Complete all 6 steps to see optimization results
Progress:0/6 tasks completed
Score: 0/100
0%
Lab Completed!
Excellent query optimization!
Lab 24: Redis Commands & Data Structures
Cache / Expert
Scenario: Real-Time Session Management
SocialHub needs Redis for session management, caching, and real-time features. You must type actual Redis commands - SET, HSET, ZADD, PUBLISH, and configuration commands. Each command is validated for correct syntax and required parameters.
Learning Objectives:
String Operations: SET with EX/PX expiration options
Hash Operations: HSET for session data storage
Sorted Sets: ZADD for rate limiting and leaderboards
Pub/Sub: SUBSCRIBE and PUBLISH for real-time messaging
?? Step-by-Step Instructions
Step 1: Configure Memory & Eviction
Set Redis memory limit and eviction policy using CONFIG SET.
Required Commands:
1. CONFIG SET maxmemory 256mb
2. CONFIG SET maxmemory-policy allkeys-lru
?? Tip: Both commands required. allkeys-lru evicts least recently used keys when memory is full.
Step 2: Create Session with TTL
Store a user session using SET with expiration.
Required Syntax: SET session:user:1001 "{"user_id":1001,"role":"admin"}" EX 3600
� Key format: session:user:<id>
� Must have: EX (seconds) or PX (milliseconds)
� TTL must be: 3600 seconds (1 hour)
?? Tip: Use EX for seconds, PX for milliseconds. JSON value must be quoted.
Step 3: Store User Profile in Hash
Use HSET to store structured user profile data.
Required Command: HSET user:1001:profile name "John Doe" email "john@example.com" role "admin"
� Key format: user:<id>:profile
� Must have ALL fields: name, email, role
?? Tip: HSET allows multiple field-value pairs in one command.
Step 4: Implement Rate Limiter
Use sorted set with timestamps for sliding window rate limiting.