01
Project Overview
The capstone project is your opportunity to demonstrate mastery of SQL by building a complete database application from scratch. You'll design, implement, optimize, and document a real-world database system.
Portfolio Piece: This project is designed to be showcase-worthy.
Include it in your portfolio and reference it in job interviews to demonstrate your SQL expertise.
What You'll Build
Complete Database
10+ tables with proper normalization, relationships, and constraints
Business Logic
Stored procedures, functions, triggers, and views
Optimized Performance
Indexes, query optimization, and execution plans
Documentation
ER diagrams, data dictionary, and usage guides
02
Choose Your Project
Select one of the following projects or propose your own:
Option A: E-Commerce Platform
Build a database for an online marketplace:
- Products, categories, inventory
- Customers, addresses, payment methods
- Orders, order items, shipments
- Reviews, wishlist, recommendations
- Promotions, coupons, discounts
Option B: Healthcare System
Build a database for a medical facility:
- Patients, medical history, insurance
- Doctors, nurses, specialists
- Appointments, consultations
- Prescriptions, medications, pharmacy
- Billing, claims, payments
Option C: Travel Booking
Build a database for a travel agency:
- Hotels, rooms, amenities
- Flights, airlines, airports
- Bookings, reservations, guests
- Packages, tours, activities
- Reviews, loyalty points, rewards
Option D: Custom Project
Propose your own project! Must include:
- Minimum 10 related tables
- Real-world use case
- Complex relationships
- Approval from instructor
- Similar complexity to options above
03
Technical Requirements
1
Database Schema (60 pts)
- Minimum 10 tables with proper normalization (3NF+)
- Complete ER diagram with all relationships
- Primary keys, foreign keys, constraints
- Appropriate data types for all columns
- Sample data (100+ rows total)
2
Complex Queries (50 pts)
- 10 business queries using JOINs
- 5 queries with subqueries or CTEs
- 3 queries with window functions
- Aggregate reports and analytics
- Query explanations and use cases
3
Stored Objects (50 pts)
- 5 stored procedures for business operations
- 3 user-defined functions
- 3 triggers for data integrity
- 5 views for common data access
- Error handling and transactions
4
Security & Optimization (50 pts)
- Role-based access control (3+ roles)
- Index strategy with justification
- Query optimization (show before/after)
- Execution plan analysis
- Backup and recovery plan
5
Documentation (40 pts)
- Complete data dictionary
- ER diagram and schema documentation
- API/procedure documentation
- Setup and deployment guide
- README with project overview
6
Presentation (50 pts)
- 5-minute video walkthrough
- Live demo of key features
- Explain design decisions
- Show query execution
- Discuss challenges and solutions
04
Submission Guidelines
GitHub Repository
- Create repository:
sql-capstone-[project-name] - Include comprehensive
README.md - Organize files in logical folder structure
- Include all SQL scripts, documentation, and diagrams
- Add video link or upload presentation
Required File Structure
sql-capstone-project/
├── README.md
├── docs/
│ ├── er-diagram.png
│ ├── data-dictionary.md
│ └── setup-guide.md
├── schema/
│ ├── 01-create-tables.sql
│ ├── 02-constraints.sql
│ └── 03-sample-data.sql
├── queries/
│ ├── business-queries.sql
│ └── reports.sql
├── stored-objects/
│ ├── procedures.sql
│ ├── functions.sql
│ ├── triggers.sql
│ └── views.sql
├── security/
│ ├── roles.sql
│ └── permissions.sql
└── optimization/
├── indexes.sql
└── execution-plans.md05
Grading Rubric
| Criteria | Points |
|---|---|
| Database schema (tables, relationships, constraints) | 60 |
| Complex queries (joins, subqueries, window functions) | 50 |
| Stored objects (procedures, functions, triggers, views) | 50 |
| Security & optimization (roles, indexes, performance) | 50 |
| Documentation (data dictionary, diagrams, guides) | 40 |
| Presentation & video walkthrough | 50 |
| Total | 300 |
Bonus Points: Up to 30 additional points for exceptional work including
advanced features like full-text search, JSON operations, geographic queries, or integration
with external APIs.