Lesson Notes By Weeks and Term v5 - Grade 12

Lesson Video

This page supports the lesson note with a companion video and a short classroom-ready summary.

For class groups and homework, share this lesson page so learners also get the summary, objectives, and full lesson context.

Performance objectives

• Design and implement stored procedures.
• Create and use user-defined functions (UDFs).
• Implement database triggers to enforce rules.
• Understand and apply database transactions (ACID).
• Optimize SQL queries using indexing.

Lesson summary

This week, we delve into more advanced database concepts and SQL techniques that allow you to build robust and efficient data-driven applications. We'll build upon the fundamental SQL knowledge you've already acquired, exploring stored procedures, functions, triggers, and transactions. These are crucial tools for developing real-world applications such as online banking systems, e-commerce platforms, and data analytics solutions – all of which are increasingly important in the South African tech landscape. Knowing how to manage and manipulate data effectively is a highly sought-after skill.

Lesson notes

2.1 Stored Procedures A stored procedure is a precompiled set of SQL statements stored in the database. Stored procedures offer several advantages: Improved Performance: Since they are precompiled, stored procedures execute faster than individual SQL statements.

Modularity: Stored procedures encapsulate complex logic, making code more maintainable and reusable.

Security: Stored procedures can be granted specific permissions, limiting direct access to underlying tables.

Reduced Network Traffic: Only the stored procedure call is sent across the network, rather than multiple SQL statements.

Example (MySQL): Let's say we have a `Students` table with columns `StudentID`, `FirstName`, `LastName`, and `Marks`. We want to create a stored procedure to insert a new student record: ```sql DELIMITER // CREATE PROCEDURE InsertNewStudent ( IN p_FirstName VARCHAR(50), IN p_LastName VARCHAR(50), IN p_Marks INT ) BEGIN INSERT INTO Students (FirstName, LastName, Marks) VALUES (p_FirstName, p_LastName, p_Marks); END // DELIMITER ; ``` Explanation: `DELIMITER //`: This changes the statement delimiter to `//` to allow the procedure definition to contain semicolons. `CREATE PROCEDURE InsertNewStudent(...)`: This creates a stored procedure named `InsertNewStudent`. `IN p_FirstName VARCHAR(50)`: Defines input parameters for the procedure (FirstName, LastName, and Marks). `BEGIN ... END`: Encloses the SQL statements within the procedure. `INSERT INTO Students ...`: Inserts a new row into the `Students` table using the provided parameters. `DELIMITER ;`: Resets the statement delimiter to the default semicolon.

Calling the Stored Procedure: ```sql CALL InsertNewStudent('Thando', 'Dlamini', 75); ``` 2.2 User-Defined Functions (UDFs) UDFs allow you to create custom functions that can be used in SQL queries. They extend the built-in SQL functions.

Example (MySQL): Let's create a function that calculates the average mark for a student, given their StudentID: ```sql DELIMITER // CREATE FUNCTION CalculateAverageMark (p_StudentID INT) RETURNS DECIMAL(5,2) BEGIN DECLARE avg_mark DECIMAL(5,2); SELECT AVG(Marks) INTO avg_mark FROM Results WHERE StudentID = p_StudentID; RETURN avg_mark; END // DELIMITER ; ``` Explanation: `CREATE FUNCTION CalculateAverageMark(...)`: Creates a function named `CalculateAverageMark`. `RETURNS DECIMAL(5,2)`: Specifies the data type of the value returned by the function. `DECLARE avg_mark DECIMAL(5,2)`: Declares a local variable to store the average mark. `SELECT AVG(Marks) INTO avg_mark ...`: Calculates the average mark for the given StudentID and stores it in the `avg_mark` variable. `RETURN avg_mark`: Returns the calculated average mark.

Using the Function: ```sql SELECT StudentID, CalculateAverageMark(StudentID) AS AverageMark FROM Students; ``` 2.3 Triggers Triggers are special stored procedures that automatically execute in response to certain events (e.g., INSERT, UPDATE, DELETE) on a specific table. They are useful for enforcing business rules, auditing changes, and maintaining data integrity.

Example (MySQL): Let's create a trigger that logs changes to the `Students` table into an `AuditLog` table: ```sql CREATE TABLE AuditLog ( LogID INT AUTO_INCREMENT PRIMARY KEY, TableName VARCHAR(50), Action VARCHAR(10), Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, OldValues TEXT, NewValues TEXT ); DELIMITER // CREATE TRIGGER Students_AfterUpdate AFTER UPDATE ON Students FOR EACH ROW BEGIN INSERT INTO AuditLog (TableName, Action, OldValues, NewValues) VALUES ( 'Students', 'UPDATE', CONCAT('StudentID=', OLD.StudentID, ', FirstName=', OLD.FirstName, ', LastName=', OLD.LastName, ', Marks=', OLD.Marks), CONCAT('StudentID=', NEW.StudentID, ', FirstName=', NEW.FirstName, ', LastName=', NEW.LastName, ', Marks=', NEW.Marks) ); END // DELIMITER ; ``` Explanation: `CREATE TRIGGER Students_AfterUpdate ...`: Creates a trigger named `Students_AfterUpdate` that executes after an UPDATE operation on the `Students` table. `AFTER UPDATE ON Students`: Specifies the event that triggers the trigger. `FOR EACH ROW`: Indicates that the trigger executes once for each row affected by the UPDATE statement. `BEGIN ... END`: Encloses the SQL statements within the trigger. `INSERT INTO AuditLog ...`: Inserts a new row into the `AuditLog` table with information about the UPDATE operation, including the old and new values of the affected columns. `OLD` and `NEW` are special keywords that refer to the previous and current values of the row being updated. 2.4 Transactions and ACID Properties A transaction is a logical unit of work that consists of one or more SQL statements. Transactions ensure that either all statements within the transaction succeed, or none of them do. This is crucial for maintaining data consistency, especially in critical applications like banking systems. The ACID properties guarantee reliable transaction processing: Atomicity: The entire transaction is treated as a single, indivisible unit of work.