Project 7: Stored Procedure Pack

Design a small set of stored routines with clear interfaces and documentation.

Quick Reference

Attribute Value
Difficulty Advanced
Time Estimate 10-20 hours
Main Programming Language SQL (conceptual)
Alternative Programming Languages N/A
Coolness Level Level 3: Automation Builder
Business Potential Level 3: Logic Centralization
Prerequisites Stored routine concepts, Transaction boundaries, Error handling
Key Topics Stored Programs

1. Learning Objectives

By completing this project, you will:

  1. Execute the core workflow for Stored Procedure Pack without relying on hidden automation.
  2. Apply the relevant MySQL concepts to reach a concrete outcome.
  3. Explain the reasoning behind your design decisions.
  4. Validate results against a deterministic outcome.

2. All Theory Needed (Per-Concept Breakdown)

Stored Programs, Triggers, and Events

Fundamentals MySQL supports stored procedures, stored functions, triggers, and scheduled events. Stored procedures and functions are server-side routines that encapsulate logic close to the data. Triggers execute automatically when rows are inserted, updated, or deleted. Events run on schedules and enable time-based automation. These features allow you to enforce business rules in the database, but they must be used carefully because they add hidden logic that can surprise application developers.

Because stored programs run inside the server, they can reduce network round trips. However, they also centralize logic in the database, which can be harder to test. Use them when consistency across clients is more important than flexibility. Deep Dive Stored programs are a way to centralize logic. A stored procedure is invoked explicitly, while a stored function is used in expressions. This distinction matters: procedures are for actions, functions are for computation. The MySQL manual describes how to create these routines and the privileges required.

Triggers are reactive. They run automatically in response to row-level changes. For example, an audit trigger can insert a record into a log table whenever a row is updated. Triggers operate within the transaction that caused them, which means their failures can roll back the original change. The MySQL documentation defines trigger timing (BEFORE or AFTER) and events (INSERT, UPDATE, DELETE), and explains that triggers are associated with tables.

Events are scheduled jobs that run at specific times or intervals. They require the event scheduler to be enabled and can be used for tasks like nightly cleanup or periodic aggregation. MySQL documents event scheduling and configuration, including that events do not execute unless the scheduler is enabled.

The main design question is where logic should live. Logic in stored programs can reduce application complexity and enforce consistency across clients. But it can also make debugging harder, because the logic is hidden inside the database. Triggers are especially dangerous when they create unexpected side effects, such as cascading writes or performance bottlenecks. For this reason, you should document every stored program and trigger, and keep their scope narrow and observable.

Stored programs also interact with replication and binary logging. Some routines and triggers can require additional privileges or specific logging settings. You must account for these in production. The MySQL manual notes the privileges needed to create and execute stored routines and triggers, which should be part of your security model.

Stored routines have a security context. They can run with the privileges of the definer or the invoker, depending on configuration. This affects what data they can access and is a key security consideration. If you do not document this, you may create hidden privilege escalation paths.

Testing stored programs is harder than testing application code because they run inside the database. You should create test cases with known inputs and expected outputs, and you should record the results. Versioning routines is also important. A change to a stored procedure can affect all clients immediately. You need a deployment plan and rollback strategy.

Triggers can introduce hidden coupling between tables. For example, a trigger that writes to an audit table adds work to every update. If the audit table is slow, it slows the primary table. This is why triggers should be small, predictable, and carefully monitored.

Events, while useful for automation, require operational discipline. If the scheduler is disabled, events do not run. If events are long-running, they can overlap and create load spikes. You should monitor event execution and keep event logic simple.

Another operational issue is observability. If stored programs and triggers fail silently or produce unexpected side effects, debugging becomes slow. You should log errors and expose execution outcomes through monitoring wherever possible. This keeps server-side logic from becoming a black box. Definitions & key terms

  • Stored procedure: server-side routine invoked by a call.
  • Trigger: automatic action tied to table events.
  • Event: scheduled database job.

Mental model diagram

DML -> Trigger -> Audit/Rules
Timer -> Event -> Scheduled action
Call -> Procedure -> Server-side logic

How it works (step-by-step)

  1. Create a routine, trigger, or event with proper privileges.
  2. Execute or trigger it by data changes or schedules.
  3. Monitor effects and performance.

Minimal concrete example

Pseudo-trigger:
IF new.status changes THEN write an audit record

Common misconceptions

  • “Triggers are always safer.” (They can hide complexity.)
  • “Stored programs remove the need for application logic.” (They complement it.)

Check-your-understanding questions

  1. When does a trigger execute?
  2. Why must events be enabled to run?
  3. What is a risk of too many triggers?

Check-your-understanding answers

  1. On row-level events like insert/update/delete.
  2. The event scheduler controls execution.
  3. Hidden side effects and performance overhead.

Real-world applications

  • Audit logging
  • Scheduled cleanup jobs
  • Encapsulating billing rules

Where you will apply it Projects 7, 8, and 12.

References

  • Stored procedures and functions.
  • Triggers and trigger behavior.
  • Event scheduling and configuration.

Key insight Stored programs move logic closer to data but must be controlled and observable.

Summary Use stored programs for consistency, and document them to avoid surprises.

Homework/exercises to practice the concept

  1. Sketch a trigger that writes an audit trail.
  2. Design a nightly event for data cleanup.

Solutions to the homework/exercises

  1. Trigger on UPDATE with a log insert.
  2. Event scheduled daily with a cleanup routine.

3. Project Specification

3.1 What You Will Build

A routine catalog with inputs, outputs, and side effects.

Included:

  • Procedure list
  • Function list
  • Documentation templates

Excluded:

  • Runnable stored routines

3.2 Functional Requirements

  1. Core workflow: Define at least three routines with inputs and outputs
  2. Repeatability: Document side effects and errors
  3. Validation: Explain why each routine is in the database

3.3 Non-Functional Requirements

  • Performance: Routines avoid unnecessary work.
  • Reliability: Behavior is predictable and documented.
  • Usability: Catalog is easy to maintain.

3.4 Example Usage / Output

Routine catalog entry with inputs, outputs, and constraints.

3.5 Data Formats / Schemas / Protocols

  • Routine catalog in text form

3.6 Edge Cases

  • Null inputs
  • Error conditions
  • Unexpected states

3.7 Real World Outcome

This is the deterministic output you can compare against directly.

3.7.1 How to Run (Copy/Paste)

  • cat routine_catalog.txt

3.7.2 Golden Path Demo (Deterministic)

Catalog includes purpose, inputs, outputs, and edge cases.

3.7.3 If CLI: provide an exact terminal transcript

$ cat routine_catalog.txt
Routine: calculate_invoice_total
Inputs: customer_id, period

4. Solution Architecture

4.1 High-Level Design

Input knowledge -> Design decisions -> Validation checklist -> Outcome

4.2 Key Components

Component Responsibility Key Decisions
Input data Defines the scenario Keep it realistic and bounded
Workflow plan Defines steps and checks Favor repeatability
Validation Confirms correctness Use explicit criteria

4.4 Data Structures (No Full Code)

  • Artifact: the document or plan you produce (schema plan, index plan, etc.)
  • Checklist: a set of checks to validate outcomes
  • Decision log: a short list of key choices and reasons

4.4 Algorithm Overview

Key Algorithm: Design-Validate Loop

  1. Define the target outcome clearly.
  2. Choose the smallest set of steps to reach it.
  3. Validate with explicit checks and record results.

Complexity Analysis:

  • Time: O(n) over artifacts produced
  • Space: O(1) additional space

5. Implementation Guide

5.1 Development Environment Setup

mysql --version
mysqladmin ping

5.2 Project Structure

project-root/
|-- input/
|   `-- scenario.txt
|-- notes/
|   `-- decisions.md
`-- outputs/
    `-- expected.txt

5.3 The Core Question You’re Answering

“Design a small set of stored routines with clear interfaces and documentation.”

5.4 Concepts You Must Understand First

Stop and research these before starting:

  • Stored routine concepts
  • Transaction boundaries
  • Error handling

5.5 Questions to Guide Your Design

  1. What is the smallest set of steps to reach the outcome?
  2. Which concept chapter gives the most relevant guidance?
  3. How will you verify correctness?

5.6 Thinking Exercise

Before executing, sketch the workflow and identify risks.

5.7 The Interview Questions They’ll Ask

  1. “Why did you choose this design?”
  2. “What tradeoffs did you consider?”
  3. “How did you validate the outcome?”
  4. “What would you change for production scale?”

5.8 Hints in Layers

Hint 1: Start with the outcome Write the outcome first, then back into the steps.

Hint 2: Keep steps observable Each step should produce something you can verify.

Hint 3: Use the checklist Turn assumptions into explicit checks.

Hint 4: Document decisions Write down why you chose each design element.

5.9 Books That Will Help

Topic Book Chapter
Foundations “Database System Concepts” Ch. 1-2
MySQL specifics “High Performance MySQL” Ch. 1
Operations “MySQL Cookbook” Ch. 1

5.10 Implementation Phases

Phase 1: Foundation (10-20 hours)

Goals:

  • Understand the scenario
  • Define the key constraints

Tasks:

  1. Summarize the scenario in 5-10 bullet points
  2. Identify key entities or actions

Checkpoint: Scenario summary is clear and accurate.

Phase 2: Core Functionality (10-20 hours)

Goals:

  • Produce the main artifact
  • Apply concept guidance

Tasks:

  1. Build the artifact step by step
  2. Validate against the checklist

Checkpoint: Artifact matches the golden path demo.

Phase 3: Polish & Edge Cases (10-20 hours)

Goals:

  • Handle edge cases
  • Document tradeoffs

Tasks:

  1. Test the edge cases
  2. Record tradeoffs and future work

Checkpoint: Edge cases are documented and addressed.

5.11 Key Implementation Decisions

Decision Options Recommendation Rationale
Scope Narrow vs broad Start narrow Clearer validation
Validation Manual vs scripted Manual first Faster learning
Documentation Minimal vs detailed Detailed Future-proofing

6. Testing Strategy

6.1 Test Categories

Category Purpose Examples
Sanity checks Verify basics Version checks, simple outputs
Consistency checks Validate logic Cross-reference with requirements
Edge cases Stress assumptions Outliers and missing data

6.2 Critical Test Cases

  1. Nominal case: The main scenario works as expected.
  2. Boundary case: The smallest and largest valid inputs.
  3. Failure case: A scenario that should be rejected or flagged.

6.3 Test Data

Use the provided scenario and add two variations with edge conditions.

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Pitfall Symptom Solution
Unclear requirements Conflicting outputs Re-define scope and assumptions
Missing validation Silent errors Add explicit checks
Overcomplication Slow progress Reduce scope and iterate

7.2 Debugging Strategies

  • Trace decisions: Review why each design choice was made.
  • Simplify: Reduce to a minimal version and expand.

7.3 Performance Traps

Complex designs without validation often lead to rework and wasted time.

8. Extensions & Challenges

8.1 Beginner Extensions

  • Re-run the workflow with a smaller dataset
  • Document one additional edge case

8.2 Intermediate Extensions

  • Apply the workflow to a real dataset
  • Add a performance or reliability note

8.3 Advanced Extensions

  • Propose a production-ready version
  • Add a monitoring or operational checklist

9. Real-World Connections

9.1 Industry Applications

  • SaaS products: schemas and indexes define reliability
  • Analytics pipelines: query tuning and archiving reduce costs
  • MySQL: core database engine
  • Percona Toolkit: performance and diagnostics tools

9.3 Interview Relevance

  • Data modeling questions
  • Index and query tuning scenarios
  • Replication and recovery scenarios

10. Resources

10.1 Essential Reading

  • “High Performance MySQL” - core optimization principles
  • “MySQL Cookbook” - operational workflows

10.2 Video Resources

  • “MySQL Performance Tuning” (searchable title)
  • “ACID and Isolation” (searchable title)