Movie Relational Database
This project showcases my proficiency in relational database design, ER modeling, and SQL. It demonstrates my ability to translate complex data into well-structured database systems with secure access and optimized querying.
Highlights
- Robust Database Design: Modeled clean relational structures for scalable movie and TV show datasets.
- ER Diagrams: Designed visual models to define relationships, keys, and normalization strategy.
- SQL Implementation: Created schemas, queries, and procedures to populate and query data.
- Privilege Management: Used SQL GRANT/REVOKE to enforce secure and role-based access.
The Dataset
Combined four streaming platform datasets (Netflix, Prime, Hulu, Disney) into one unified structure containing titles, directors, genres, countries, release dates, ratings, and descriptions.
Appendix A: Sample from merged base dataset
Appendix C: Original flat ER model
ER Modeling
The original dataset was not normalized. I restructured it to third normal form and modeled cardinalities, PKs, and FKs.
Appendix G: Final normalized ER diagram
Database Creation
Below are steps for creating the database schema, inserting data, setting privileges, and optimizing the system.
MySQL Schema and Data
Privilege Setup
Optimization
Sample Queries
Query 1:
Find which host aired the most content from a specific director, within a date range and country.
Query 2:
Determine top genres by host in a specific country and time range.
Query 3:
Get the latest hosted content by a provider in a given region.
