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.
• Utilize triggers to automate database operations.
• Implement basic indexing to improve performance.
• Apply transaction management for data consistency.
• Implement data validation to ensure data integrity.

Lesson summary

This week, we delve into advanced database concepts and SQL techniques that go beyond basic data retrieval and manipulation. We will focus on how to build more robust and efficient database applications, crucial for managing and analyzing the increasing amounts of data in today's digital world. From managing customer information in large retail chains to tracking patient data in hospitals, these advanced database skills are directly applicable to various industries in South Africa. Understanding these concepts will equip you with the necessary tools to develop sophisticated applications, manage data effectively, and contribute meaningfully to South Africa's growing IT sector.

Lesson notes

2.1 Stored Procedures: Stored procedures are pre-compiled SQL code blocks stored within the database. They are similar to functions or procedures in programming languages.

They offer several advantages: Improved Performance: Stored procedures are compiled and stored in the database server, reducing network traffic and execution time.

Enhanced Security: They can control data access, limiting users to executing specific procedures rather than having direct access to tables.

Code Reusability: Stored procedures can be called from multiple applications, reducing code duplication and improving maintainability.

Example (MySQL): ```sql DELIMITER // CREATE PROCEDURE GetCustomerDetails (IN cust_id INT) BEGIN SELECT customer_id, customer_name, city FROM Customers WHERE customer_id = cust_id; END // DELIMITER ; CALL GetCustomerDetails(123); ``` Explanation: This code defines a stored procedure `GetCustomerDetails` that accepts a customer ID as input. The procedure then retrieves the customer's ID, name, and city from the `Customers` table where the customer ID matches the input. The `DELIMITER` statement is used to change the statement delimiter because the procedure definition contains semicolons. Finally, the stored procedure is called with a customer ID of

1

2

3. Imagine a large retailer like Shoprite using this to quickly retrieve customer information when processing loyalty card rewards. 2.2 Triggers: Triggers are special stored procedures that automatically execute in response to certain events on a particular table (e.g., INSERT, UPDATE, DELETE).

They are useful for: Auditing: Tracking changes made to data.

Data Validation: Ensuring data integrity by enforcing rules.

Automating Tasks: Performing actions based on specific events.

Example (MySQL): ```sql DELIMITER // CREATE TRIGGER BeforeOrderInsert BEFORE INSERT ON Orders FOR EACH ROW BEGIN IF NEW.order_date > CURDATE() THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order date cannot be in the future.'; END IF; END // DELIMITER ; ``` Explanation: This trigger `BeforeOrderInsert` is executed before each new row is inserted into the `Orders` table. It checks if the `order_date` is in the future. If it is, the trigger raises an error, preventing the insertion of the row. This is a practical example for any e-commerce system in South Africa, ensuring that orders are not placed with future dates. The `SIGNAL SQLSTATE` command is used to raise a custom error, providing more specific information. 2.3 Indexing: Indexes are data structures that improve the speed of data retrieval operations on a database table. They work similarly to an index in a book, allowing the database to quickly locate specific rows without scanning the entire table.

Benefits: Faster query execution, improved application performance.

Trade-offs: Increased storage space, slower INSERT, UPDATE, and DELETE operations (as indexes need to be updated).

Example (MySQL): ```sql CREATE INDEX idx_customer_city ON Customers (city); ``` Explanation: This statement creates an index named `idx_customer_city` on the `city` column of the `Customers` table. When querying the table based on the `city` column (e.g., `SELECT * FROM Customers WHERE city = 'Cape Town'`), the database can use the index to quickly locate the relevant rows. Think of a government database needing to quickly find all residents in Durban. Indexing the "city" field becomes crucial for performance. 2.4 Transaction Management: Transactions are a sequence of database operations that are treated as a single unit of work. Transactions guarantee that either all operations within the transaction are completed successfully (committed), or none of them are (rolled back). This is crucial for maintaining data consistency and integrity.

ACID Properties: Transactions adhere to the ACID properties: Atomicity: The entire transaction is treated as a single unit.

Consistency: The transaction ensures that the database remains in a consistent state.

Isolation: Transactions are isolated from each other, preventing interference.

Durability: Once a transaction is committed, the changes are permanent.

Example (MySQL): ```sql START TRANSACTION; UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT; ``` Explanation: This code snippet demonstrates a transaction that transfers R100 from account 1 to account

2. The `START TRANSACTION` statement begins the transaction. The `UPDATE` statements modify the account balances. The `COMMIT` statement commits the transaction, making the changes permanent. If any of the `UPDATE` statements fail, the `ROLLBACK` statement could be used to undo the changes, ensuring that the transaction is atomic. This is essential for banking systems like FNB or ABSA, ensuring money transfers are accurate. 2.5 Data Validation: Data validation refers to the process of ensuring that data entered into a database is accurate, consistent, and complete.