Oracle PL/SQL Example: RDBMS in 3NF with Procedure and Trigger (Client–Policy Database)

Introduction

Learn how to design a relational database in 3NF (Third Normal Form) and implement PL/SQL Procedure and Trigger in Oracle using a practical Client–Policy example. This beginner-friendly guide explains concepts, logic, source code, and viva questions.

Problem Statement

Consider the following entities and their relationships:

Client(client_no, client_name, address, birthdate)
Policy_info(policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many-to-Many

Constraints:

• Primary keys must be defined.
• prem_amt and maturity_amt should be greater than 0.

Tasks:

1. Write a procedure which will display all policy details having premium amount less than 5000.
2. Write a trigger which will fire before insert or update on policy_info having maturity amount less than premium amount. (Raise user defined exception and give appropriate message)

Logic Explanation

Step 1: Database Design in 3NF

To convert the database into Third Normal Form (3NF):

• Each table has a primary key.
• No partial dependency.
• No transitive dependency.
• Many-to-Many relationship is resolved using a junction table (Client_Policy).

Tables:

1. Client
2. Policy_info
3. Client_Policy (Bridge Table)

Step 2: Procedure Logic

• Use a cursor FOR loop.
• Fetch records where prem_amt < 5000.
• Display records using DBMS_OUTPUT.PUT_LINE.

Step 3: Trigger Logic

• BEFORE INSERT OR UPDATE trigger.
• Check condition: maturity_amt < prem_amt.
• Raise user-defined exception using RAISE_APPLICATION_ERROR.

Source Code

1️⃣ Create Client Table

CREATE TABLE Client(
    client_no NUMBER PRIMARY KEY,
    client_name VARCHAR2(50),
    address VARCHAR2(100),
    birthdate DATE
);

2️⃣ Create Policy_info Table

CREATE TABLE Policy_info(
    policy_no NUMBER PRIMARY KEY,
    desc VARCHAR2(100),
    maturity_amt NUMBER(10,2) CHECK(maturity_amt > 0),
    prem_amt NUMBER(10,2) CHECK(prem_amt > 0),
    policy_date DATE
);

3️⃣ Create Junction Table

CREATE TABLE Client_Policy(
    client_no NUMBER REFERENCES Client(client_no),
    policy_no NUMBER REFERENCES Policy_info(policy_no),
    PRIMARY KEY(client_no, policy_no)
);

4️⃣ Procedure to Display Policies with Premium < 5000

CREATE OR REPLACE PROCEDURE display_low_premium_policies AS
BEGIN
    FOR rec IN(
        SELECT policy_no, desc, prem_amt, maturity_amt, policy_date
        FROM Policy_info
        WHERE prem_amt < 5000
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE('Policy No: ' || rec.policy_no ||
                             ' Description: ' || rec.desc ||
                             ' Premium: ' || rec.prem_amt ||
                             ' Maturity: ' || rec.maturity_amt);
    END LOOP;
END;
/

To Execute:

SET SERVEROUTPUT ON;
EXEC display_low_premium_policies;

5️⃣ Trigger to Check Maturity Amount

CREATE OR REPLACE TRIGGER trg_check_maturity
BEFORE INSERT OR UPDATE ON Policy_info
FOR EACH ROW
BEGIN
    IF :NEW.maturity_amt < :NEW.prem_amt THEN
        RAISE_APPLICATION_ERROR(-20001,
        'Maturity amount cannot be less than Premium amount');
    END IF;
END;
/

6️⃣ Insert Sample Data

INSERT INTO Client VALUES(1,'Alice','Mumbai',TO_DATE('1990-05-10','YYYY-MM-DD'));
INSERT INTO Client VALUES(2,'Bob','Delhi',TO_DATE('1985-08-15','YYYY-MM-DD'));

INSERT INTO Policy_info VALUES(101,'Term Plan',10000,4000,TO_DATE('2025-01-10','YYYY-MM-DD'));
INSERT INTO Policy_info VALUES(102,'Life Plan',15000,6000,TO_DATE('2025-02-15','YYYY-MM-DD'));

INSERT INTO Client_Policy VALUES(1,101);
INSERT INTO Client_Policy VALUES(2,102);

Sample Output

After executing:

EXEC display_low_premium_policies;

Output:

Policy No: 101 Description: Term Plan Premium: 4000 Maturity: 10000

If you try inserting invalid data:

INSERT INTO Policy_info VALUES(103,'Invalid Plan',3000,5000,TO_DATE('2025-03-01','YYYY-MM-DD'));

Output:

ORA-20001: Maturity amount cannot be less than Premium amount

Viva Questions with Answers

1. What is 3NF?
   3NF (Third Normal Form) ensures that all non-key attributes depend only on the primary key and there is no transitive dependency in the table.
2. Why do we use a junction table?
   A junction table is used to handle Many-to-Many relationships between two entities. It contains foreign keys from both tables to maintain proper normalization.
3. What is a Trigger?
   A trigger is a database object that automatically executes when a specific event such as INSERT, UPDATE, or DELETE occurs on a table.
4. What is the difference between Procedure and Function?
   A procedure performs a specific action and may or may not return a value. A function always returns a value and can be used in expressions or calculations.
5. What is RAISE_APPLICATION_ERROR?
   RAISE_APPLICATION_ERROR is used in PL/SQL to generate custom error messages with user-defined error numbers.