Database Concurrency, Normalization, and PL/pgSQL Programming

Posted on Nov 13, 2025 in Computer Engineering

Transaction Concurrency: Preventing the Lost Update Problem

The Lost Update Problem occurs when two transactions simultaneously update the same data, resulting in one update overwriting the changes made by the other. This concurrency issue leads to data inconsistency.

It can be prevented using locking mechanisms, such as row-level exclusive locks, which ensure that only one transaction can modify the specific data item at any given time.

Core Database Concepts: True or False Statements

  1. Using procedural language SQL (PL/SQL, PL/pgSQL, etc.), we can write blocks of code containing standard SQL statements and procedural extensions that are stored and executed at the DBMS server. → True

  2. From the data modeler’s perspective, the objective of normalization is to ensure that all tables are at least in Third Normal Form (3NF). → True

  3. An SQL View is a physical table used to extract data from base tables. → False (A View is a virtual table.)

  4. A DML trigger is a special stored procedure fired implicitly when a Data Manipulation Language (DML) event (INSERT, UPDATE, DELETE) occurs. → True

  5. According to the normalization process (specifically Boyce-Codd Normal Form or BCNF), all nonprime attributes in a table are dependent on the candidate key(s), the entire candidate key(s) (in case of composite keys), and nothing but the candidate key(s). → True

  6. Strong consistency provides immediate consistency but can result in higher latency and lower availability. In contrast, eventual consistency prioritizes availability but can lead to temporary data inconsistencies. → True

Relational vs. NoSQL Databases

Relational databases (e.g., MySQL, PostgreSQL) utilize structured schemas, rely on SQL, and support strong consistency enforced by ACID transactions.

NoSQL databases (e.g., MongoDB, Cassandra) are typically schema-less, support eventual consistency, and are better suited for unstructured or rapidly changing data. NoSQL systems generally offer higher scalability and flexibility, making them ideal for Big Data applications.

Database Programming Examples (PL/pgSQL)

SQL View Definition

Creating a view to list members from Fes who rented items after April 25, 2024:

CREATE VIEW FesMembersAfterApril2024 AS
SELECT M.MEM_NUM, M.MEM_FNAME, M.MEM_LNAME
FROM MEMBERSHIP M
JOIN RENTAL R ON M.MEM_NUM = R.MEM_NUM
WHERE M.MEM_CITY = 'Fes' AND R.RENT_DATE > '2024-04-25';

PL/pgSQL Function: Calculate Total Rental Amount

A function to calculate the total fee for a specific rental number (rnum):

CREATE OR REPLACE FUNCTION total_rental_amount(rnum INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    total NUMERIC := 0;
BEGIN
    SELECT SUM(DETAIL_FEE)
    INTO total
    FROM DETAILRENTAL
    WHERE RENT_NUM = rnum;

    RETURN total;
END;
$$;

PL/pgSQL Trigger: Update Membership Balance

This trigger function updates the member’s balance immediately after a new rental record is inserted:

CREATE OR REPLACE FUNCTION update_balance() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE MEMBERSHIP
    SET MEM_BALANCE = MEM_BALANCE + (
        SELECT SUM(DETAIL_FEE)
        FROM DETAILRENTAL
        WHERE RENT_NUM = NEW.RENT_NUM
    )
    WHERE MEM_NUM = NEW.MEM_NUM;
    RETURN NEW;
END;
$$;

Applying the trigger to the RENTAL table:

CREATE TRIGGER trg_update_balance
AFTER INSERT ON RENTAL
FOR EACH ROW
EXECUTE PROCEDURE update_balance();

PL/pgSQL Procedure: Calculate Movie Total Cost

A procedure to calculate and display the total rental revenue generated by a specific movie (movnum):

CREATE OR REPLACE PROCEDURE movie_total_cost(movnum INT)
LANGUAGE plpgsql
AS $$
DECLARE
    total NUMERIC := 0;
BEGIN
    SELECT SUM(DETAIL_FEE)
    INTO total
    FROM DETAILRENTAL D
    JOIN VIDEO V ON D.VID_NUM = V.VID_NUM
    WHERE V.MOVIE_NUM = movnum;

    RAISE NOTICE 'Total amount from renting this movie: %', total;
END;
$$;

PL/pgSQL Function: Calculate Late Fee

A function to determine the late fee based on the difference between the return date and the due date for a specific rental detail:

CREATE OR REPLACE FUNCTION calc_late_fee(rentid INT, vidid INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    days_late INT := 0;
    daily_fee NUMERIC := 0;
BEGIN
    SELECT DETAIL_DAILYLATEFEE,
           (DETAIL_RETURNDATE - DETAIL_DUEDATE)
    INTO daily_fee, days_late
    FROM DETAILRENTAL
    WHERE RENT_NUM = rentid AND VID_NUM = vidid;

    IF days_late <= 0 THEN
        RETURN 0;
    ELSE
        RETURN days_late * daily_fee;
    END IF;
END;
$$;