Assignment 9-A

Design Production Database

Design a complete production-ready database from scratch. You'll apply normalization principles, create ER diagrams, implement the schema, and document scalability strategies.

8-10 hours
Advanced
200 Points
What You'll Practice
  • Normalization (1NF to BCNF)
  • ER diagram creation
  • Cardinality relationships
  • Schema implementation
  • Scalability planning
Contents
01

Assignment Overview

In this assignment, you will design a complete Learning Management System (LMS) database from the ground up. You'll gather requirements, normalize the design, create documentation, and implement a production-ready schema.

Skills Applied: This assignment tests your understanding of Normalization (Topic 9.1), ER Diagrams (Topic 9.2), and Scalability Patterns (Topic 9.3) from Module 9.
Normalization

1NF, 2NF, 3NF, BCNF forms

ER Modeling

Entities, relationships, cardinality

Scalability

Sharding, replication, partitioning

02

The Scenario

EduPlatform LMS

You're the lead database architect for a new Learning Management System that will serve millions of students worldwide. The system needs to handle courses, enrollments, assignments, grades, and discussions.

"We expect 10 million users, 100,000 courses, and need to support real-time analytics. Design must be scalable from day one."

System Requirements

  • Users - Students, instructors, administrators with roles
  • Courses - Multiple sections, modules, prerequisites
  • Enrollments - Registration, progress tracking
  • Content - Videos, documents, quizzes, assignments
  • Assessments - Submissions, grading, feedback
  • Discussions - Forums, comments, threads
03

Deliverables

1
Requirements Analysis
  • Document all entities and their attributes
  • Identify relationships between entities
  • Define cardinality for each relationship
  • List business rules and constraints
2
ER Diagram
  • Create complete ER diagram with all entities
  • Show primary and foreign keys
  • Indicate cardinality (1:1, 1:N, M:N)
  • Include attribute data types
3
Normalization Documentation
  • Show original unnormalized data
  • Document 1NF transformation
  • Document 2NF transformation
  • Document 3NF/BCNF final form
4
Schema Implementation
  • Complete DDL scripts for all tables
  • All constraints (PK, FK, CHECK, UNIQUE)
  • Indexes for common query patterns
  • Sample data for testing
5
Scalability Plan
  • Partitioning strategy for large tables
  • Read replica recommendations
  • Caching layer suggestions
  • Archive strategy for old data
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-9-database-design
  • Include requirements.md with entity analysis
  • Include er-diagram.png (or use dbdiagram.io link)
  • Include normalization.md with step-by-step process
  • Include schema.sql with complete DDL
  • Include scalability.md with growth plan
05

Grading Rubric

Criteria Points
Requirements analysis completeness 30
ER diagram accuracy and detail 40
Normalization documentation 40
Schema implementation quality 50
Scalability planning 40
Total 200