PL/SQL Triggers, Procedures, Views, and Concurrency Control in Database Systems

Posted on May 23, 2024 in Computers

PL/SQL Row-Level Trigger Example

To create a PL/SQL row-level trigger in Oracle that displays the old salary, new salary, and salary difference whenever a modification to the salary column of the emptable occurs, you can follow this syntax:

  1. CREATE OR REPLACE TRIGGER salary_change_trigger
  2. BEFORE UPDATE OF salary ON emptable
  3. FOR EACH ROW
  4. DECLARE old_salary NUMBER; new_salary NUMBER; salary_diff NUMBER;
  5. BEGIN old_salary := :OLD.salary; new_salary := :NEW.salary; salary_diff := new_salary – old_salary; DBMS_OUTPUT.PUT_LINE(‘Old Salary: ‘ || old_salary); DBMS_OUTPUT.PUT_LINE(‘New Salary: ‘ || new_salary); DBMS_OUTPUT.PUT_LINE(‘Salary Difference: ‘ || salary_diff);
  6. END; /

Explanation:

  • CREATE OR REPLACE TRIGGER: This statement creates or replaces a trigger.
  • salary_change_trigger: Name of the trigger.
  • BEFORE UPDATE OF salary ON emptable: This specifies that the trigger fires before an update operation is performed on the salary column of the emptable.
  • FOR EACH ROW: This indicates that the trigger is a row-level trigger, meaning it executes once for each row affected by the triggering statement.
  • DECLARE: This begins the declaration section of the PL/SQL block where variables are declared.
  • old_salary: Declares a variable to store the old salary.
  • new_salary: Declares a variable to store the new salary.
  • salary_diff: Declares a variable to store the salary difference.
  • BEGIN: This marks the beginning of the executable section of the trigger.
  • old_salary := :OLD.salary: Assigns the value of the old salary to the variable old_salary. :OLD is a special qualifier used in triggers to refer to the old value of a column.
  • new_salary := :NEW.salary: Assigns the value of the new salary to the variable new_salary. :NEW is a special qualifier used in triggers to refer to the new value of a column.
  • salary_diff := new_salary – old_salary: Calculates the salary difference.
  • DBMS_OUTPUT.PUT_LINE: This procedure is used to display output. In this trigger, it will display the old salary, new salary, and salary difference.
  • END: Marks the end of the executable section.
  • /: This indicates the end of the trigger definition.

PL/SQL Procedure Example

Syntax

  1. CREATE OR REPLACE PROCEDURE procedure_name (parameter1 IN datatype, parameter2 OUT datatype,)
  2. IS — Declaration section
  3. BEGIN — Execution section — SQL statements, PL/SQL logic
  4. EXCEPTION — Exception handling WHEN exception_name1 THEN — Handle exception 1 WHEN exception_name2 THEN — Handle exception 2
  5. END; /

Example

  1. CREATE OR REPLACE PROCEDURE check_employee_existence( p_empno IN EMP.EMPNO%TYPE, p_exists OUT BOOLEAN)
  2. IS v_count NUMBER;
  3. BEGIN SELECT COUNT(*) INTO v_count FROM EMP WHERE EMPNO = p_empno; IF v_count > 0 THEN p_exists := TRUE; ELSE p_exists := FALSE; END IF;
  4. EXCEPTION WHEN OTHERS THEN p_exists := FALSE; — Exception occurred, set existence to false
  5. END; / — PL/SQL Program to Use the Procedure
  6. SET SERVEROUTPUT ON;
  7. DECLARE v_empno EMP.EMPNO%TYPE v_exists BOOLEAN;
  8. BEGIN — Accept empno from user v_empno := &empno; — Prompt for empno input check_employee_existence(v_empno, v_exists); — Check existence and display message accordingly IF v_exists THEN DBMS_OUTPUT.PUT_LINE(‘Employee with empno ‘ || v_empno || ‘ exists.’); ELSE DBMS_OUTPUT.PUT_LINE(‘Employee with empno ‘ || v_empno || ‘ does not exist.’); END IF;
  9. END; /

Explanation

  1. Creating Procedure: (check_employee_existence): The procedure check_employee_existence takes an employee number (p_empno) as input and returns a boolean value indicating whether the employee exists in the EMP table. v_count variable holds the count of rows returned from the query. If count > 0, it means the employee exists. If an exception occurs, the procedure sets the existence to FALSE.
  2. PL/SQL Program: It prompts the user to enter an employee number (empno) and calls the procedure check_employee_existence. If the employee exists, it prints a message confirming the existence; otherwise, it prints a message indicating the employee does not exist. Exception handling is included to handle any errors gracefully.

Creating Views in SQL

You can create a view based on the result of the procedure. However, since procedures typically involve executing logic and returning values, you can’t directly create a view based on a procedure. Instead, you can use a function or a query to achieve similar functionality in a view.

Syntax

  1. CREATE OR REPLACE VIEW view_name AS
  2. SELECT column1, column2,
  3. FROM table_name
  4. WHERE conditions;
  • CREATE OR REPLACE VIEW: This clause is used to create a new view or replace an existing one with the same name.
  • view_name: This is the name of the view you want to create.
  • AS: This keyword indicates the beginning of the query that defines the view.
  • SELECT column1, column2, …: This is the list of columns you want to include in your view. You can also use expressions here.
  • FROM table_name: This specifies the table(s) from which the data will be fetched
  • WHERE conditions: This is an optional part that allows you to specify conditions to filter the data.

Example

  1. CREATE OR REPLACE FUNCTION employee_exists(p_empno IN EMP.EMPNO%TYPE) RETURN VARCHAR2
  2. IS v_exists BOOLEAN;
  3. BEGIN check_employee_existence(p_empno, v_exists); IF v_exists THEN RETURN ‘Exists’; ELSE RETURN ‘Does not exist’; END IF;
  4. END;/
  5. CREATE OR REPLACE VIEW emp_existence_view AS
  6. SELECT empno, employee_exists(empno) AS existence_status
  7. FROM EMP;

Creating Functions in SQL

Syntax

  1. CREATE [OR REPLACE] FUNCTION function_name ( parameter1 IN datatype, parameter2 OUT datatype, ) RETURN return_datatype
  2. IS — Declaration section
  3. BEGIN — Execution section — SQL statements, PL/SQL logic
  4. EXCEPTION — Exception handling WHEN exception_name1 THEN — Handle exception 1 WHEN exception_name2 THEN — Handle exception 2
  5. END;/
  • CREATE [OR REPLACE] FUNCTION: This indicates the beginning of the function creation statement. OR REPLACE allows you to modify an existing function with the same name if it already exists.
  • function_name: This is the name of your function.
  • (parameter1 IN datatype, parameter2 OUT datatype, …): These are the parameters that the function takes. Parameters can be IN, OUT, or IN OUT. IN parameters are read-only within the function, OUT parameters are used to return values, and IN OUT parameters can be both read from and written to within the function.
  • RETURN return_datatype: This specifies the datatype that the function will return.
  • IS: This keyword indicates the beginning of the procedural code block.
  • Declaration section: This is where you declare local variables, cursors, and other elements you’ll use within the function.
  • Execution section: This is where you put your SQL statements and PL/SQL logic.
  • EXCEPTION: This keyword indicates the start of the exception handling block. Here you define how to handle exceptions that may occur during the execution of the function.
  • WHEN exception_name THEN: You can specify different exceptions and their handling within this block.
  • END;: This keyword marks the end of the function.
  • /: This indicates the end of the entire function creation statement.

Example

  1. CREATE OR REPLACE FUNCTION employee_exists(p_empno IN EMP.EMPNO%TYPE) RETURN BOOLEAN
  2. IS v_count NUMBER;
  3. BEGIN SELECT COUNT(*) INTO v_count FROM EMP WHERE EMPNO = p_empno; IF v_count > 0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF;
  4. EXCEPTION WHEN OTHERS THEN RETURN FALSE; — Exception occurred, return false
  5. END;/

UNIT-4: Query Processing in Database Systems

Query processing is a critical aspect of database management systems, where user requests are analyzed, optimized, and executed to retrieve the desired data efficiently. The process can be quite complex, involving multiple layers of analysis and optimization. Here’s a breakdown of the typical steps involved in query processing, accompanied by a simplified flow diagram.

Steps in Query Processing

  • Parsing and Syntax Analysis: The query is checked for syntactic correctness using the grammar rules of the query language (like SQL). The query is converted into a parse tree, which represents the syntactic structure of the query.
  • Semantic Analysis: Checks for semantic correctness of the query. Verifies that the tables and fields referenced in the query exist and that the operations are valid on the data types involved.
  • Query Optimization: The optimizer takes the internal representation of the query and transforms it into the most efficient execution plan based on various strategies. Optimization involves selecting the best execution plan from potentially many alternatives, based on cost estimations (like minimal disk access).
  • Query Evaluation/Execution: The database engine executes the optimized query plan. Operations such as scans, joins, and sorts are carried out based on the plan.
  • Output Generation: The final step is the generation and return of results to the user or application that issued the query.

ja68rXRla+NrnxtdOVroytfG135Nhe66u+DrrJXO7ra0QddZfdBV9ltdOVroytfG+6bkMidJh9GjBgxYsSIESNGjJgdI4kcMWLEiBEjRowYMSd2Kv8fcDPgagGFUdgAAAAASUVORK5CYII=

Relational Algebra Operations in Query Processing

Relational algebra is a theoretical framework used to process and retrieve data stored in relational database systems. It forms the backbone of SQL and provides a foundation for understanding how queries are executed. Here’s a brief overview of the key relational algebra operations used in query processing:

  1. Selection (σ): The selection operation filters rows from a relation based on a specific condition. It retrieves all rows in which the condition holds true. Example: σage > 25(Employees). This expression selects all employees whose age is greater than 25.
  2. Projection (π): Projection is used to eliminate columns from a relation. It returns a new relation with only the specified columns. Example: πname, age(Employees). This would result in a table with just the name and age columns from the Employees table.
  3. Cartesian Product (×): A Cartesian product combines two relations into one. For two tables A and B, the Cartesian product is the set of all possible combinations of rows from A with rows from B. This operation is often followed by a selection to produce meaningful results. Example: A × B, where A and B are tables, this operation will produce a table that combines each row of A with each row of B.
  4. Join (⋈): Join is a crucial operation that combines two relations based on a condition. It’s more specific than a Cartesian product and typically involves a condition that links the tables together. Example: Employees ⋈Employees.department_id = Departments.id Departments. This join returns all employees along with their department details.
  5. Union (∪): The union operation combines the rows of two relations and removes duplicate rows from the result. Both relations must have the same number of columns and corresponding column types. Example: A ∪ B, where A and B are relations, this operation returns a table containing all rows that appear in either A or B.
  6. Intersection (∩): Intersection returns only those rows that are present in both relations. Example: A ∩ B. This operation will result in a table that contains only the rows that appear in both A and B.
  7. Difference (−): This operation returns rows from one relation that are not found in another. It is useful for finding discrepancies between tables. Example: A − B. This will produce a set of rows that are in A but not in B.
  8. Division (÷): Division is used to identify “complete” sets of data matching a criterion. For example, finding departments where every employee meets certain conditions. Example: Employees ÷ Departments. This operation could be used to find all departments where every employee is older than 25.
  9. Rename (ρ): Rename is a unary operation used to rename the output relation’s columns or the relation itself. This is especially useful in queries involving self-join where the same table is joined to itself. Example: ρ(new_name)(A). This renames the relation A to new_name.

UNIT-1: Data Models in Database Systems

1. Hierarchical Data Model

The hierarchical data model organizes data in a tree-like structure, where each record has a single parent and zero or more children. It is very efficient for representing data with a clear parent-child relationship.

Example: A common example is an organizational chart where each entity (employee) has one direct supervisor and can have zero or more direct reports. In a database, an employee’s record might include fields for employee ID, name, position, and supervisor ID.

2. Network Data Model

This model extends the hierarchical model by allowing multiple parent-child relationships. Data in this model is organized in a graph that can be navigated in many directions.

Example: Consider a database of an airline reservation system where entities include Flights, Passengers, and Crew. A Passenger might be linked to multiple Flights and each Flight could be linked to multiple Crew members and Passengers, forming a complex network of relationships.

3. Relational Data Model

The relational model uses a table-based format, where data is structured into rows and tables. Each table (relation) has key fields that identify the data. Relations can be manipulated using algebraic operations.

Example: A student database might contain tables for Students, Courses, and Enrollments. Students could be represented by fields such as student_id, name, and major. Courses might include course_id, title, and instructor. Enrollments, which link students to courses, might include records of student_id and course_id pairs.

4. Object-Oriented Data Model

This model integrates object-oriented programming principles into databases, where data is represented as objects. It allows for the representation of complex data and relationships similar to how software applications are developed.

Example: In a library management system, objects could represent Books, Authors, and Borrowers. Each Book object might contain data about its title, authors, and borrower, and methods for checking the book out or returning it.

5. Entity-Relationship Model

The entity-relationship (ER) model uses entities and their relationships to represent data. It is widely used in database design. An ER diagram is used to visualize entities, attributes, and relationships.

Example: In an ER diagram for a hospital system, entities might include Patient, Doctor, and Appointment. Relationships could be defined as a Patient being assigned to a Doctor, and both Patient and Doctor being linked by an Appointment.

6. Document Data Model

Used primarily in NoSQL databases, the document data model focuses on storing data in document formats like JSON, BSON, or XML. Each document stores semi-structured data that can be different from other documents in the same database.

Example: In a content management system (CMS), each content item might be stored as a JSON document with fields for content ID, title, content body, tags, and publication date. Documents can easily incorporate nested structures, such as lists of comments or multimedia attachments.

7. Key-Value Data Model

This is a simple model where each item in the database is stored as an attribute name (or “key”) and its value. This model is designed for simplicity and speed, particularly useful for applications that require high-speed lookups.

Normalization in Database Design

Normalization is a technique used in databases to reduce redundancy and dependency by organizing data into tables and columns. This process helps improve data integrity and efficiency in database operations.

  • First Normal Form (1NF): In 1NF, each column in a table must contain atomic values, meaning that it cannot be further divided. Additionally, each column should have a unique name, and the order of rows and columns should not matter.
  • Second Normal Form (2NF): 2NF builds upon 1NF by requiring that every non-prime attribute (i.e., attributes not part of the primary key) be fully functionally dependent on the entire primary key. This eliminates partial dependencies where an attribute depends on only a part of the primary key.
  • Third Normal Form (3NF): In 3NF, a table must be in 2NF, and no non-prime attribute should depend on any other non-prime attribute (transitive dependency). This means that every non-prime attribute should be directly dependent on the primary key.
  • Boyce-Codd Normal Form (BCNF): BCNF is a stricter form of 3NF in which every determinant (attribute whose value determines other values in a row) is a candidate key. This ensures that there are no non-trivial dependencies between attributes.
  • Fourth Normal Form (4NF): 4NF deals with multi-valued dependencies, ensuring that each multi-valued dependency in the table is a result of the table’s candidate keys.
  • Fifth Normal Form (5NF): Also known as Project-Join Normal Form (PJNF), 5NF deals with cases where a table contains join dependencies, ensuring that all join dependencies are implied by the candidate keys.

UNIT-3: Concurrency Control in Database Systems

Concurrency Control Protocols for Serializability and Deadlock Freedom

Two-Phase Locking (2PL):

  • Conflict Serializability: Two-phase locking ensures conflict serializability by adhering to the strict rules of locking. It divides transactions into two phases: the growing phase and the shrinking phase. During the growing phase, transactions acquire locks on data items they need for execution. Once a transaction releases a lock (moves to the shrinking phase), it cannot acquire any new locks. This ensures that conflicting operations are serialized, preventing conflicts that could lead to anomalies like lost updates or inconsistent reads.
  • Freedom from Deadlock: Two-phase locking also ensures freedom from deadlock. Deadlocks occur when transactions wait indefinitely for resources held by other transactions. In two-phase locking, transactions request locks in a predetermined order (e.g., increasing order of data item IDs). This protocol ensures that transactions never request locks in a way that can lead to cyclic waiting, thus preventing deadlocks.

Timestamp Ordering:

  • Conflict Serializability: Timestamp ordering is based on assigning unique timestamps to transactions based on their start time. Transactions are then ordered according to their timestamps, and conflicting operations are executed in timestamp order. This ensures conflict serializability as transactions are ordered in a way that prevents conflicts.
  • Freedom from Deadlock: Timestamp ordering also ensures freedom from deadlock. In this protocol, transactions are executed based on their timestamps without any blocking or waiting. Therefore, there’s no possibility of transactions waiting indefinitely for resources held by other transactions, which eliminates the occurrence of deadlocks.

Both 2-phase locking and timestamp ordering ensure conflict serializability, as they control access to shared resources in a way that prevents conflicts among concurrent transactions. Additionally, both protocols guarantee freedom from deadlock by enforcing rules that prevent cyclic waiting for resources. However, the mechanisms they employ to achieve these goals differ: 2-phase locking relies on locking resources in a specific order, while timestamp ordering uses transaction timestamps to order and schedule transactions without blocking or waiting.

Concurrency Control Techniques

  • Locking: Locking is a widely used technique where transactions acquire locks on data items to prevent other transactions from accessing or modifying them concurrently.
  • Two-Phase Locking (2PL): In 2PL, transactions acquire all necessary locks before performing any data modifications (growing phase) and release all locks after the modifications are complete (shrinking phase).
  • Timestamp Ordering (TO): Each transaction is assigned a unique timestamp, and transactions are ordered based on their timestamps.
  • Multi-Version Concurrency Control (MVCC): MVCC maintains multiple versions of data items to allow concurrent read and write operations without blocking each other.
  • Optimistic Concurrency Control (OCC): OCC allows transactions to proceed without acquiring locks initially. Before committing, each transaction verifies that its changes do not conflict with other concurrent transactions.

Serializability in Database Management Systems

Serializability is crucial in database management systems (DBMS) to maintain data integrity and consistency in multi-user environments where multiple transactions may be executing simultaneously. Without serializability, concurrent execution of transactions could lead to problems such as lost updates, inconsistent reads, and other anomalies.

  • Locking: DBMS can use locking mechanisms to control access to data items. Transactions request locks on data items they intend to access or modify, and locks are granted based on predefined rules.
  • Concurrency Control Algorithms: These algorithms coordinate the execution of concurrent transactions to ensure serializability while maximizing concurrency.