Item–Supplier Database in 3NF with PL/SQL Function and Trigger in Oracle

Designing a properly normalized database ensures data consistency and eliminates redundancy. In this tutorial, we will create an Item–Supplier database in 3NF and implement a PL/SQL function and trigger in Oracle.

Problem Statement

Consider the following Item_Supplier database:

Item (itemno, itemname)
Supplier (supplier_no, supplier_name, address, city)
Relationship between Item and Supplier is Many-to-Many with descriptive attributes: rate and quantity
Constraints: itemno, supplier_no primary key

Question:

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1. Write a function to print the total number of suppliers of a particular item.
2. Write a trigger which will fire before insert or update when rate or quantity is less than or equal to zero. Raise a user-defined exception with an appropriate message.

Logic Explanation

Step 1: Convert into 3NF

Since Item and Supplier have a Many-to-Many relationship, we create a junction table called Item_Supplier. This removes redundancy and maintains a composite primary key (itemno, supplier_no). The descriptive attributes rate and quantity are stored in this table.

Step 2: Function Logic

Count suppliers from Item_Supplier using COUNT(*) and return 0 if no records exist.

Step 3: Trigger Logic

Create a BEFORE INSERT OR UPDATE trigger. If :NEW.rate <= 0 OR :NEW.quantity <= 0, raise a user-defined exception using RAISE_APPLICATION_ERROR.

Source Code

Step 1: Create Tables

CREATE TABLE Item(
    itemno NUMBER PRIMARY KEY,
    itemname VARCHAR2(50)
);

CREATE TABLE Supplier(
    supplier_no NUMBER PRIMARY KEY,
    supplier_name VARCHAR2(50),
    address VARCHAR2(100),
    city VARCHAR2(50)
);

CREATE TABLE Item_Supplier(
    itemno NUMBER REFERENCES Item(itemno),
    supplier_no NUMBER REFERENCES Supplier(supplier_no),
    rate NUMBER,
    quantity NUMBER,
    PRIMARY KEY (itemno, supplier_no)
);

Step 2: Create Function

CREATE OR REPLACE FUNCTION total_suppliers(p_itemno IN NUMBER)
RETURN NUMBER
AS
    total NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO total
    FROM Item_Supplier
    WHERE itemno = p_itemno;

    RETURN NVL(total,0);
END;
/

Calling the Function

SET SERVEROUTPUT ON;
DECLARE
    cnt NUMBER;
BEGIN
    cnt := total_suppliers(101);
    DBMS_OUTPUT.PUT_LINE('Total suppliers for item 101: '||cnt);
END;
/

Step 3: Create Trigger

CREATE OR REPLACE TRIGGER trg_check_rate_quantity
BEFORE INSERT OR UPDATE ON Item_Supplier
FOR EACH ROW
DECLARE
    invalid_value EXCEPTION;
BEGIN
    IF :NEW.rate <= 0 OR :NEW.quantity <= 0 THEN
        RAISE invalid_value;
    END IF;
EXCEPTION
    WHEN invalid_value THEN
        RAISE_APPLICATION_ERROR(-20001,'Rate and Quantity must be greater than zero!');
END;
/

Step 4: Insert Sample Data

INSERT INTO Item VALUES (101,'Laptop');
INSERT INTO Item VALUES (102,'Mobile');

INSERT INTO Supplier VALUES (1,'Supplier A','Mumbai','Mumbai');
INSERT INTO Supplier VALUES (2,'Supplier B','Delhi','Delhi');

INSERT INTO Item_Supplier VALUES (101,1,50000,10);
INSERT INTO Item_Supplier VALUES (101,2,51000,15);
INSERT INTO Item_Supplier VALUES (102,1,20000,20);

Sample Output

Function Output

Total suppliers for item 101: 2

Trigger Output (Invalid Insert)

INSERT INTO Item_Supplier VALUES (102,2,0,5);

Error:

ORA-20001: Rate and Quantity must be greater than zero!

INSERT INTO Item_Supplier VALUES (102,2,10000,-10);

Error:

ORA-20001: Rate and Quantity must be greater than zero!

Viva Questions

1. Why is Item_Supplier table required?
   It resolves the Many-to-Many relationship between Item and Supplier.
2. What is 3NF?
   A table is in Third Normal Form if it has no transitive dependency and all non-key attributes depend only on the primary key.
3. What is a composite primary key?
   A primary key formed using more than one column, such as (itemno, supplier_no).
4. What is the purpose of a trigger?
   A trigger automatically executes when a specified database event occurs.
5. What is RAISE_APPLICATION_ERROR?
   It is used in PL/SQL to raise a user-defined error with a custom message.