Module 1 Introduction to Database Administration PDF

MODULE 1 INTRODUCTION TO DATABASE ADMINISTRATION AGENDA Introduction Course Material Database Administration/Administrator Data Security, Data Availability, Data Quality 2 INTRODUCTION Overview This module will explore on some preliminary concepts and tools needed for the whole course. I...

MODULE 1 INTRODUCTION TO DATABASE ADMINISTRATION AGENDA Introduction Course Material Database Administration/Administrator Data Security, Data Availability, Data Quality 2 INTRODUCTION Overview This module will explore on some preliminary concepts and tools needed for the whole course. It will start by I dentifying the tools that will be needed in the course. This is being separated in a lesson so that the user can initially explore the tolls. After the identification of tools, next lesson will give you the overview of database administration and the core topics of database administration by idenfying the responsibility of a Database Administrator. 3 INTRODUCTION Course Objective After successful completion of this module, you should be able to: 1. Install the tools needed in the course 2. Understand the basic knowledge that must be gained for the entire subject 3. Enumerate the responsibility of a database administrator 4. Identify the skills needed and the certification that they can consider to be a DBA. 4 LESSON 1: TOOLS TO USED IN THIS COURSE 1. VERSION CONTROL GIT 2. Latest MySQL Version 3. MySQL WorkBench 5 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Database Administration consists of everything required to manage a database it includes ensuring the database to be available everytime and anytime it is needed by the user or software application. The database administrator (DBA) is the person who manages, secures and ensures the availability of the data produced produced and used by the organization. 6 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR A database administrator's responsibilities may include: Installing and upgrading the database server and/or application tools. Planning for and allocating the database system’s physical requirements, such as memory,disk space, network requirements, etc. Modifying the database structure using information provided by application developers. Creating users profiles, and ensuring system security by careful allocation of userpermissions. 7 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Ensuring compliance with database vendor license agreement, including number of installations, and taking care of licensing renewals. Creating a backup and recovery strategy for the database, and regularly testing the backups to ensure usability. Monitoring technical support for both database systems and related applications. Creating reports by querying from database (as per need). These reports can be in the form of pre- formatted reports using the application frontend, or custom-made ad hoc reports by the database 8 administrator. LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Monitoring and optimizing the database’s performance using either manual or automated tools. This may be a DBA’s most important tasks. Migrating database instances to new hardware and new versions of software from on premise to cloud based databases and vice versa. 9 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR There are several DBA Specialisation 1. There are also database administrators who create and design databases in the first place. They usually work as part of a team, often in a more specialized software development company. And that database is likely to be part of a larger application for some specific business need. 10 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR 2. the Performance Database Administrator or Tuning Database Administrator. This administrator specializes in optimizing and improving the performance of various databases. Data access can then be optimized by use of indexes, which read like a book’s table of contents. DBAs help to ensure that the database is optimized to take advantage of the physical or virtual hardware on which it is hosted. 11 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR 3. the Application DBA. This role focuses specifically on integrating databases into the applications that use them 12 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR There are two main paths to become a database administrator: Focusing on being a DBA from the beginning of an IT career, or (more commonly) starting in a general ICT field, such as helpdesk support or development, and gradually becoming more proficient in database administration. Just like most of the ICT careers you will be needing some set of skill and character to be a DBA: Excellent 13 troubleshooting skills, Logical LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Having one of this certification will boost your chances in getting your dream DBA job. Oracle: Oracle DB Certified Associate, Oracle DB Certified Professional Microsoft SQL Server: MCSE (Data platform and Business Intelligence) and the older MCDBA IBM: IBM Analytics Certification Oracle: MySQL Database Developer, MySQL Database Administrator MongoDB: Certified DBA, Certified Developer 14 Cassandra: Certified Cassandra Administrator, Certified LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR When you are applying for your DBA job, make sure to be prepare to showcase your DBA expertise in relation to the following: Database installation and configuration Data security User creation and maintenance Database backups and recovery Database performance tuning and optimization Data transformation and loading Reporting and querying 15 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Security is protecting databases from unauthorized access, breaches, and theft. Access Control: Restricting database access to authorized users. Encryption: Protecting data at rest and in transit with encryption methods. Backups: Ensuring that secure copies of data are always available. Audit Logs: Tracking who accessed or changed data. 16 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Common Threats: SQL injection, insider threats, ransomware, malware attacks. 17 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Security Best Practices: Implement multi-factor authentication (MFA). Regularly update and patch database software. Use role-based access control (RBAC). Monitor and audit access logs frequently. Encrypt sensitive data both at rest and in transit 18 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Availability is Ensuring that data is accessible to users when needed. Redundancy: Duplicate data and systems to prevent downtime. Failover Systems: Backup systems that automatically take over in case of failure. Backup and Recovery Plans: Regular backups to ensure fast recovery in case of failure. Disaster Recovery: Planning for catastrophic events like natural disasters or cyberattacks. 19 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Examples of Downtime Causes: Hardware failures, network issues, human errors, and cyberattacks. 20 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Availability Best Practices: Implement High Availability (HA) architecture: Use clustered databases, load balancers. Regularly test disaster recovery plans. Automate backups and ensure offsite storage for disaster recovery. Use cloud-based solutions to enhance redundancy and scalability. 21 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Quality is Ensuring that data is accurate, complete, and reliable. Accuracy: Data reflects the real-world object or event correctly. Consistency: Data should be uniform across all platforms. Completeness: No missing or incomplete records. Timeliness: Data should be up-to-date and available in real time. Relevance: Data should be applicable to the business context. 22 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Consequences of Poor Data Quality: Poor decision-making, inefficiencies, loss of trust, compliance issues. 23 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Quality Best Practices Use automated validation checks during data entry. Implement data cleansing processes to remove or correct inaccurate records. Establish a governance policy with data stewards. Ensure regular data audits for quality assessment. Standardize data collection processes. 24 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY RECAP: 1. Data Security protects against threats. 2. Data Availability ensures systems remain accessible. 3. Data Quality guarantees the usefulness of data for decision- making. Importance of balancing these three pillars to maintain a reliable and efficient database 25system. THANK YOU Introduction to MySQL Query Language: DML DDL, DCL and TCL  Introduction to SQL SQL (Structured Query Language) is used to interact with databases. Divided into several categories, two key ones being: DML: Data Manipulation Language (manages data)DDL Data Definition Language (defines database structure) 9/4/20XX Presentation Title 28  What is DML? Data Manipulation Language (DML) involves the actual data inside the tables. Common Commands: SELECT: Retrieve data from a database. INSERT: Add new data. UPDATE: Modify existing data. DELETE: Remove data. 9/4/20XX Presentation Title 29  What is DDL? Data Definition Language (DDL) defines and manages the structure of the database. Common Commands: CREATE: Create tables, databases, indexes. ALTER: Modify the structure of an existing object. DROP: Delete tables, databases, indexes. TRUNCATE: Remove all records from a table but keep the structure. 9/4/20XX Presentation Title 30  What is DCL? DCL (Data Control Language) manages access to the database. Common commands: GRANT: Give a user access to database privileges. REVOKE: Remove privileges from a user. 9/4/20XX Presentation Title 31  What is TCL? TCL (Transaction Control Language) manages database transactions. Common commands: COMMIT: Save changes made by DML commands. ROLLBACK: Revert changes made by DML commands. SAVEPOINT: Set a point within a transaction to roll back to if needed. 9/4/20XX Presentation Title 32  DML Commands - Examples SELECT Query Example: SELECT * FROM users; INSERT Query Example: INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]’); UPDATE Query Example: UPDATE users SET email = '[email protected]' WHERE id = 1; DELETE Query Example: DELETE FROM users WHERE id = 1; 9/4/20XX Presentation Title 33  DDL Commands - Examples CREATE Table Example: CREATE TABLE users ( id INT AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255), PRIMARY KEY (id)); ALTER Table Example: ALTER TABLE users ADD COLUMN phone VARCHAR(15); DROP Table Example: DROP TABLE users; TRUNCATE Table Example: TRUNCATE TABLE users; 9/4/20XX Presentation Title 34  DCL Commands - Examples GRANT Command Example: GRANT SELECT, INSERT ON database_name.* TO 'user_name'@'localhost’; REVOKE Command Example: REVOKE INSERT ON database_name.* FROM 'user_name'@'localhost'; 9/4/20XX Presentation Title 35  TCL Commands - Examples COMMIT Command Example: START TRANSACTION; UPDATE accounts SET balance = balance - 500 WHERE account_id = 1; UPDATE accounts SET balance = balance + 500 WHERE account_id = 2; COMMIT; ROLLBACK Command Example: START TRANSACTION; UPDATE accounts SET balance = balance - 500 WHERE account_id = 1; UPDATE accounts SET balance = balance + 500 WHERE account_id = 2; ROLLBACK; 9/4/20XX Presentation Title 36  TCL Commands - Examples SAVEPOINT Command Example: START TRANSACTION; SAVEPOINT before_update; UPDATE accounts SET balance = balance - 500 WHERE account_id = 1; ROLLBACK TO before_update; COMMIT; 9/4/20XX Presentation Title 37  Differences Between DML, DDL, DCL and TCL 9/4/20XX Presentation Title 38 MySQL Column and Scalar Functions Enhancing Database Operations with Built-In Functions Introduction to MySQL Functions MySQL provides a rich set of built-in functions to enhance SQL queries Functions allow you to perform calculations, manipulate data, and retrieve useful information from databases Two main types: Column Functions (Aggregate Functions) and Scalar Functions Column Functions Overview (Aggregate Functions) Column functions operate on a set of rows and return a single result Commonly used for summarizing data Includes functions like SUM(), AVG(), COUNT(), MAX(), and MIN() Example of Column Functions SUM(): Adds up the values in a column AVG(): Calculates the average of a set of values COUNT(): Counts the number of rows in a result set MAX(): Retrieves the maximum value from a set of data MIN(): Retrieves the minimum value from a set of data Scalar Functions Overview Scalar functions operate on individual values and return a single result for each input These functions are often used for manipulating text, numbers, and dates Common functions: UCASE(), LCASE(), CONCAT(), ROUND(), NOW() String Functions (Scalar) UCASE(): Converts a string to uppercase LCASE(): Converts a string to lowercase CONCAT(): Concatenates two or more strings SUBSTRING(): Extracts a portion of a string LENGTH(): Returns the length of a string Numeric Functions (Scalar) ROUND(): Rounds a number to the specified number of decimal places ABS(): Returns the absolute value of a number MOD(): Returns the remainder of a division operation FLOOR(): Rounds a number down to the nearest integer CEIL(): Rounds a number up to the nearest integer Date Functions (Scalar) NOW(): Returns the current date and time CURDATE(): Returns the current date DATE_ADD(): Adds a specified time interval to a date DATEDIFF(): Calculates the difference between two dates DAY(), MONTH(), YEAR(): Extracts the day, month, or year from a date Combining Functions in Queries Functions can be combined for complex queries Example: CONCAT(UCASE(SUBSTRING(first_name, 1, 1)), LCASE(last_name)) to format names Use of SUM() and ROUND() together for accurate financial calculations Example Query Using Both Column and Scalar Functions SELECT department, ROUND(SUM(salary), 2) AS total_salary FROM employees GROUP BY department; Conclusion MySQL offers powerful column and scalar functions to enhance SQL queries Aggregate functions like SUM() and COUNT() are ideal for summarizing data Scalar functions such as ROUND() and CONCAT() are useful for manipulating individual data points Proper use of these functions can greatly improve the efficiency of database operations Integrity Constraints in MySQL Databases A Comprehensive Guide to Using and Enforcing Integrity Constraints. Importance of Integrity Constraints Ensuring Data Accuracy: Integrity constraints ensure the data is accurate by preventing invalid entries. Maintaining Data Consistency: Constraints enforce rules across tables, especially in relational databases. Ensuring Data Reliability: Prevents data corruption by enforcing business rules at the database level. Avoiding Logical Errors: Prevents actions that could break relationships between data (e.g., orphaned records). Reducing Redundancy: Prevents duplicate or irrelevant data entries. Types of Primary Key (PK): Guarantees Integrity unique identification of each Constraints in record. MySQL Foreign Key (FK): Links tables and enforces relationships. Unique Constraint: Prevents duplicate values in specific columns. Types of NOT NULL: Ensures that a field Integrity cannot be left empty. Constraints in Check Constraint: Validates MySQL that data in a column meets specific criteria (MySQL 8.0+). Default Constraint: Assigns a default value if no value is provided. Enforcing Integrity Constraints in MySQL Creating Constraints: Adding constraints during table creation or using ALTER TABLE Example for Primary Key: CREATE TABLE Customers ( customer_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL );  Example for Foreign Key Enforcing ALTER TABLE Orders Integrity ADD CONSTRAINT fk_customer Constraints in FOREIGN KEY (customer_id) REFERENCES Customers(customer_id); MySQL Best Practice: Always apply constraints directly at the database level. Creating Tables with MySQL Integrity Constraints Primary Key Definition CREATE TABLE Customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) ); Creating Tables with MySQL Integrity Constraints Foreign Key Definition CREATE TABLE Customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) ); Creating Tables with MySQL Integrity Constraints Unique and NOT NULL Example CREATE TABLE Users ( user_id INT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL ); Creating Tables with MySQL Integrity Constraints Check Constraint (MySQL 8.0+) CREATE TABLE Employees ( emp_id INT PRIMARY KEY, salary DECIMAL(10, 2), CHECK (salary > 0) );  Choose the Right Data Types: Use INT for numeric data, VARCHAR for text fields, DECIMAL for monetary values, Optimizing etc. Example: price DECIMAL(10,2) ensures Table monetary values with two decimal places. Design in Appropriate Field Sizes: Avoid unnecessarily large field sizes (e.g., MySQL VARCHAR(255) vs. VARCHAR(50)). Keep field sizes appropriate to the expected data. Optimizing Table Design in MySQL Normalization: Apply the Indexes: Add indexes to principles of normalization frequently searched fields for (1NF, 2NF, 3NF) to avoid data faster querying. redundancy and ensure that data is structured correctly. CREATE INDEX idx_email ON Users (email); Cascading Actions with Referential Integrity in MySQL ON DELETE CASCADE: Automatically deletes child records when the parent record is deleted. CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE ); Cascading Actions with Referential Integrity in MySQL ON UPDATE CASCADE: Automatically updates child records when the parent key changes CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON UPDATE CASCADE); Cascading Actions with Referential Integrity in MySQL Why Use Cascades?:Simplifies data management when relationships exist. Prevents orphaned records (records in child tables with no matching parent).  Default Constraints: Assign default values to fields when no data is provided. Additional MySQL CREATE TABLE Products ( Constraint product_id INT PRIMARY KEY, s & Best name VARCHAR(255), Practices stock INT DEFAULT 0 );  Indexing for Faster Queries: Add indexes to columns frequently used in WHERE Additional clauses for faster query performance. Preventing Data Anomalies: Use CHECK MySQL constraints, foreign keys, and validation logic to prevent data anomalies (e.g., Constraints entering a future date of birth). Foreign Key Options: Use SET NULL to set & Best foreign key fields to NULL when the parent Practices record is deleted. ON DELETE SET NULL Conclusion and Key Takeaways Integrity Constraints Ensure Data Reliability: Use constraints to maintain the accuracy and consistency of your data. Cascade Actions Are Powerful: Use them carefully to avoid unwanted data deletion or updates. Enforce Integrity in Both Database and Code: MySQL constraints are the first line of defense, but application-level validation is also important. Optimal Table Design Matters: Correct data types, field sizes, and constraints lead to efficient and reliable databases. MySQL Joins and Set Operations MySQL Joins MySQL Joining Tables A JOIN clause is used to combine rows from two or more tables, based on a related column between them. MySQL Joins Let's look at a selection from the "Orders" table: Then, look at a selection from the "Customers" table: MySQL Joins Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables: MySQL Joins and it will produce something like this: Supported Types of Joins in MySQL INNER JOIN: Returns records that have matching values in both tables LEFT JOIN: Returns all records from the left table, and the matched records from the right table RIGHT JOIN: Returns all records from the right table, and the matched records from the left table CROSS JOIN: Returns all records from both tables MySQL INNER JOIN Keyword The INNER JOIN keyword selects records that have matching values in both tables. INNER JOIN Syntax MySQL LEFT JOIN Keyword The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2). LEFT JOIN Syntax MySQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1). RIGHT JOIN Syntax SQL CROSS JOIN Keyword The CROSS JOIN keyword returns all records from both tables (table1 and table2). CROSS JOIN Syntax The MySQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in every SELECT statement must also be in the same order The MySQL UNION Operator UNION Syntax UNION ALL Syntax The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL: The MySQL UNION Operator UNION Syntax UNION ALL Syntax The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL: Introducti on to Subqueri es in MySQL Concepts, Syntax, and Practical Examples What is a Subquery ? A subquery is a query nested inside another SQL query. Used for performing operations like filtering, aggregating, and setting conditions for main queries. Can return a single value, multiple values, or an entire table. Understanding Outer and Inner Queries in SQL Subqueries Outer Query The outer query, also called the main query, is the primary part of the SQL statement that uses the results from a subquery (inner query) to filter or calculate its results. It defines the final output of the query, using data from the subquery to refine or restrict the results. SELECT name, salary FROM employees e1 WHERE salary > (subquery) Here, the outer query is selecting the name and salary from the employees table and using a condition (salary > (subquery)) to filter the results. Understanding Outer and Inner Queries in SQL Subqueries Inner Query (Subquery) The inner query, or subquery, is a nested query within the main query that performs calculations or data retrieval which the outer query relies on. In a correlated subquery, the inner query depends on values from the outer query for each row. It provides values or conditions to the outer query. In the case of correlated subqueries, it is recalculated for each row in the outer query. Understanding Outer and Inner Queries in SQL Subqueries (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id) This inner query calculates the average salary for the department of each employee. The outer query then uses this result to check if an employee's salary is above this department average. Types of Subqueries in MySQL Single Row Subqueries – Return one row. Multiple Row Subqueries – Return multiple rows. Correlated Subqueries – Refer to columns in the outer query. Example of a Single Row Subquery Scenario: Find employees who earn the highest salary in the company. SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); Explanation: The subquery (SELECT MAX(salary) FROM employees) finds the highest salary. The main query then fetches employees with that salary. Example of a Multiple Row Subquery Scenario: List all employees who work in departments with a location of 'New York’. SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York’); Explanation: The subquery fetches all department_ids with location 'New York’. The main query finds employees in these departments. Example of a Correlated Subquery Scenario: Retrieve employees who earn more than the average salary of their department. SELECT name, salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id); Explanation: The subquery calculates the average salary for each department. The main query checks if an employee's salary is above the average of their department. Best Practices with Subqueries Avoid subqueries if JOINs can achieve the same result, as JOINs are generally more efficient. Use correlated subqueries sparingly, as they are more resource-intensive. Consider readability; complex subqueries can be hard to debug. Conclusion Subqueries are powerful tools for performing complex queries. Choose the right type (single-row, multi-row, or correlated) based on your requirements. Practice helps in understanding the most efficient way to implement subqueries. Understanding MySQL Transactions, Stored Procedures, and Views Theories, Usage, Advantages, and Program Implementation Agenda 1. Introduction to MySQL Transactions 2. Stored Procedures in MySQL 3. Using Views in MySQL 4. Code Demonstrations 5. Testing Program Codes Introduction to MySQL Transactions What is a Transaction? A transaction is a logical unit of work in MySQL where one or more operations are executed as a single unit. If all operations succeed, the transaction is committed, and changes are saved. If any operation fails, the entire transaction is rolled back, ensuring the database remains in a consistent state. Introduction to MySQL Transactions ACID Properties of Transactions The ACID properties ensure that a transaction is processed reliably. These are: Atomicity: Ensures that all operations within the transaction are either fully completed or not executed at all. Consistency: After a transaction, the database moves from one valid state to another, maintaining data integrity. Introduction to MySQL Transactions Isolation: Transactions are isolated from each other. Each transaction's operations do not interfere with others. Durability: Once committed, changes are permanent, even in the event of a system crash. When to Use Transactions Use Cases for Transactions: Ensuring Data Integrity: When multiple operations need to be treated as a single unit of work, e.g., transferring funds from one bank account to another. Multiple Related Updates: If an operation requires updates across multiple tables (e.g., updating inventory and sales at the same time). When to Use Transactions Example: In a banking system, transferring money from account A to account B involves: Debiting account A Crediting account B If any step fails (e.g., insufficient funds), the transaction should be rolled back to ensure data integrity. Stored Procedures in MySQL What is a Stored Procedure? A stored procedure is a set of precompiled SQL statements that can be executed together as a single unit. Stored procedures are stored in the database and can be called with parameters, allowing dynamic behavior. Stored Procedures in MySQL Advantages of Stored Procedures: 1. Performance: Reduces overhead by allowing the database server to execute a set of SQL statements without having to send multiple queries from the client. 2. Reusability: Once created, a stored procedure can be called multiple times, improving maintainability. 3. Security: Restricts direct access to data. Users can execute a procedure without needing permission on the underlying tables. 4. Maintainability: Centralizes business logic within the database, making updates easier. When to Use Stored Procedures Use Cases for Stored Procedures: Business Logic: Encapsulating business logic, such as discount calculations or inventory updates, ensures consistency. Complex Queries: For repeated complex queries (e.g., monthly sales reports), storing the logic in a stored procedure improves efficiency and readability. Transactions: Stored procedures can group multiple SQL statements into a single transaction for atomicity. When to Use Stored Procedures Example: A stored procedure that calculates a total order amount, applying discounts based on the order value. Stored DELIMITER $$ Procedure CREATE PROCEDURE CalculateOrderTotal(IN orderID INT) Example BEGIN (Code) DECLARE total DECIMAL(10,2); This code -- Calculate the total of items in the order defines a SELECT SUM(price * quantity) INTO total stored FROM order_items procedure that WHERE order_id = orderID; calculates the total of items -- Apply discount if applicable in an order, IF total > 100 THEN applies a SET total = total * 0.9; -- 10% discount discount if the END IF; total exceeds -- Return the total amount after discount 100, and SELECT total AS FinalOrderTotal; returns the END $$ final order amount. DELIMITER ; Using Views in MySQL What is a View? A view is a virtual table constructed from a SELECT query. It presents data in a specific format, allowing users to query complex data more easily. Views do not store data but instead store the query that defines the data. Using Views in MySQL Advantages of Views: Simplification: Views can abstract away complex queries, making them reusable. Security: Restrict access to sensitive data by providing users with only the necessary columns or rows. Data Integrity: Since views don't allow data modification, they can help prevent accidental changes to the underlying data. When to Use Views Use Cases for Views: Simplify Complex Queries: When you frequently need to join multiple tables or perform complex calculations, a view simplifies the query. Reporting: Views are ideal for creating reusable reports or summaries. Data Abstraction: Provide a simplified, read-only view of the data, hiding unnecessary View Example (Code) CREATE VIEW CustomerOrderView AS This code creates a SELECT c.customer_name, view called o.order_id, oi.product_name, CustomerOrderVie oi.quantity, oi.price w that presents FROM customers c data by joining three tables: JOIN orders o ON customers, orders, c.customer_id = and order_items. It o.customer_id shows the JOIN order_items oi ON customer name, o.order_id = oi.order_id; order ID, product name, quantity, and price for each order. Testing Stored Procedure and View Test for Stored Procedure: Run a simple test by calling the stored procedure with a specific orderID and checking the returned result. CALL CalculateOrderTotal(101); This will return the final order total after applying any discounts. Testing Stored Procedure and View Test for View: Query the view to verify that it returns the expected results. SELECT * FROM CustomerOrderView WHERE customer_name = 'John Doe’; This will return all orders for the customer 'John Doe' along with product details. Best Practices For Transactions: Always ensure that transactions are either committed or rolled back to maintain database consistency. Use BEGIN TRANSACTION, COMMIT, and ROLLBACK to handle transactions properly. Best Practices For Stored Procedures: Avoid making stored procedures too complex. Break them down into smaller parts for maintainability. Use input parameters effectively to maximize reusability. Best Practices For Views: Avoid using views for data manipulation. They should be used for read-only operations. Keep views simple to avoid performance issues, especially when dealing with large datasets. Conclusion Recap: Transactions ensure data integrity and consistency by grouping multiple operations into a single unit. Stored Procedures encapsulate business logic, improve performance, and enhance security. Views simplify data access, enhance security, and improve data presentation.  Triggers and Error Handling in Database Administration A Beginner’s Guide Learning Objectives 1. Understand when to use triggers in a database. 2. Identify the components of a program with triggers. 3. Explore concepts associated with triggers. 4. Differentiate between types of triggers. 5. Learn to code, test, and debug triggers and error- handling routines. Introduction to Triggers A trigger is a database object that automatically executes predefined actions in response to specific events on a table or view. Purpose: Automate repetitive tasks. Enforce business rules. Maintain data integrity. When to Use Triggers 1. Automating audit trails for changes in data. 2. Enforcing complex constraints that can't be managed by the database schema. 3. Synchronizing data across related tables. 4. Executing automated actions in response to critical business events. Parts of a Trigger Program 1. Trigger Event: Specifies when the trigger executes (e.g., INSERT, UPDATE, DELETE). 2. Trigger Timing: BEFORE or AFTER the event. 3. Trigger Action: The SQL statements executed when the trigger is fired. Sample Code - Trigger CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN -- action END; Concepts Used with Triggers New and Old Data References: NEW: Access new data values in INSERT/UPDATE triggers. OLD: Access previous data values in DELETE/UPDATE triggers. Recursion Prevention: Avoid infinite loops with proper conditions. Trigger Limitations: Cannot commit transactions within triggers. Limited debugging tools in some databases. Types of Triggers 1. Row-Level Triggers: Executes for each affected row. Example: Audit changes in salary for each employee. 2. Statement-Level Triggers: Executes once for the triggering statement. Example: Log a bulk INSERT operation. Coding Triggers 1. Use CREATE TRIGGER CREATE TRIGGER log_update syntax to define a trigger. AFTER UPDATE ON employees 2. Add logic using FOR EACH ROW conditional statements (IF, CASE) where BEGIN needed. INSERT INTO audit_log (user_id, action) 3. Use procedural VALUES (NEW.id, 'Update languages (e.g., performed'); PL/SQL, T-SQL) for END; complex actions. Testing and Debugging Triggers Testing Tips: Create test cases for each trigger action. Use transaction rollback to avoid permanent changes. Debugging Steps: Use logging tables to capture trigger actions. Check database server logs for errors. Temporarily disable triggers to isolate issues. Error Handling in Triggers TRY-CATCH blocks (SQL BEGIN Server). -- Trigger logic EXCEPTION handling EXCEPTION (PL/SQL). WHEN OTHERS THEN INSERT INTO error_log (error_message) VALUES (SQLERRM); END;