Mastering INF3707 Assignment 03: Database Design, Queries, and Stored Procedures

Tackling practical database modules like INF3707 (Database Design and Implementation) requires more than just reading theory—it demands hands-on execution using MySQL Server and MySQL Workbench[cite: 9]. Assignment 03 is a comprehensive, compulsory 100-mark assessment that tests your ability to write efficient SQL queries, structure relational tables, create views, and program stored procedures[cite: 9].

Whether you are working through data aggregation or relational design, here is a breakdown of what you must study and master to pass this assignment with a distinction.

Core Topics You Must Study to Pass

To successfully complete Assignment 03, you need to thoroughly review Chapters 4 through 15 of your prescribed textbook[cite: 9] and focus heavily on the following practical areas:

• Advanced Querying & Conditional Logic (Chapters 4 & 5): Master operators like UNION to combine result sets dynamically[cite: 9]. Learn how to use conditional expressions (such as checking whether a ship_date exists to determine a ship_status of SHIPPED or NOT SHIPPED)[cite: 9]. Practice writing precise INSERT statements using explicit column lists while omitting default or auto-incremented fields[cite: 9].
• Data Aggregation & Grouping (Chapter 6): Understand how to join tables and use aggregate functions like COUNT() and MAX() combined with GROUP BY to summarize data per category[cite: 9].
• Subqueries & Joins (Chapter 7): Know how to swap between explicit table joins and subqueries utilizing the IN keyword in a WHERE clause while maintaining identical result sets[cite: 9].
• Data Definition Language & Physical Database Design (Chapter 11): Practice turning Crow’s Foot Entity-Relationship Diagrams (ERDs)—such as the relationships between users, downloads, and products—into fully functional DDL scripts[cite: 9]. Ensure your scripts include database creation/dropping checks, utf8mb4 character sets, InnoDB storage engines, and necessary foreign key indexes[cite: 9].
• Views and Virtual Tables (Chapter 12): Learn how to create reusable views (e.g., combining customer and address tables to display unified billing and shipping data)[cite: 9].
• Stored Procedures, Variables, and Control Flow (Chapters 13 & 15): Master writing programmable SQL logic using DELIMITER, variable declarations (DECLARE), conditional statements (IF...THEN...ELSE), and parameterized procedures designed to handle data insertions safely[cite: 9].

Essential Study Tips for Success

• Always Test in MySQL Workbench First: This is a fully practical module[cite: 9]. Never write your SQL code directly onto your answer sheet. Run and test every query in your local database environment first to ensure it compiles without errors.
• Format Your Submissions Correctly: The assessment requires you to submit your work as a PDF file[cite: 9]. For every question, you must provide both the exact SQL code and a clear screenshot of the running code and output grid (following the formatting layout specified in Tutorial Letter 104)[cite: 9].
• Watch Out for Constraints: When creating tables (like handling unique category names or foreign key constraints), test your negative test cases (such as trying to introduce duplicate data) to ensure your procedures handle errors or constraints as expected[cite: 9].