MySQL for Web and Application Development

Comprehensive MySQL Database Design and Management System Project

The objective of this final project is to demonstrate mastery of MySQL programming by designing, implementing, and querying a relational database based on a real-world business scenario. Step 1: Scenario Selection. Choose a domain such as an E-commerce Platform, a Hospital Management System, or a University Registrar. Step 2: Database Schema Design. Design a schema consisting of at least five related tables. You must ensure the design adheres to Third Normal Form (3NF) to minimize data redundancy. Step 3: DDL Implementation. Write a SQL script to create the database and tables. You must use appropriate data types and strictly enforce referential integrity using Primary Keys, Foreign Keys, and NOT NULL constraints. Step 4: Data Seeding. Populate each table with a minimum of 10 realistic records to allow for comprehensive testing. Step 5: Advanced Querying. Develop five complex queries that provide business insights. These queries must utilize multiple JOIN types (INNER, LEFT), subqueries, and aggregate functions with GROUP BY and HAVING clauses. Step 6: Database Objects. Implement at least one View to simplify data access for end-users and one Stored Procedure to automate a repetitive task. Step 7: Final Submission. Submit a single .sql file containing all scripts, organized with clear headings and comments.