A fully normalized SQL-based movie database system built from scratch. Designed to simulate real-world entertainment data operations — including movies, genres, user reviews, roles, cast/crew, and award metadata — using advanced relational modeling, constraints, and procedural logic.
🎓 Final Project: Database Management | Stony Brook University
This database supports the core structure of a real-world movie review platform. It includes entity-relationship modeling converted to SQL, with strong emphasis on:
- ✅ Relational normalization (3NF+)
- ✅ Primary and foreign key integrity
- ✅ Many-to-many mapping with junction tables
- ✅ Procedural logic with triggers & stored procedures
| Entity | Description |
|---|---|
| Movie | Stores metadata for each film |
| Genre | Genre classification linked to each movie |
| Person | Contains actor, director, and writer identities |
| Role | Maps people to movie roles (actor, director, etc.) |
| Review | Stores ratings and comments by users |
| Award | National & international awards associated with movies |
| User_Account | Basic user management & reviewers |
| Movie_Genre | Many-to-many between Movie and Genre |
| Movie_Award | Many-to-many between Movie and Award |
- 🔐 Triggers: Prevent illegal review ratings or insertion of invalid dates
- 📜 Stored Procedures:
- InsertMovie — modular movie + genre + role setup
- InsertReview — review system with business logic
- GetRecommendedMovies — simple user-specific suggestion engine
- 🧹 Validation: Genre/award duplication, date constraints, null-safe checks
- 🧪 Sample Data Insertion Scripts provided
- Declarative schema creation (
CREATE TABLE,ALTER) - Transaction-safe stored procedures and conditional logic
BEFORE INSERT,BEFORE UPDATETriggers for field-level validation- Indexing, constraints,
IF EXISTS,SIGNAL SQLSTATEerror throws - Modular SQL design for reusability
Also developed a basic web interface to showcase the graphical outputs.