Create RDB in 3NF with PL/SQL Function and Cursor in Oracle (Many-to-Many Relationship)

Designing a relational database in Third Normal Form (3NF) ensures data integrity, removes redundancy, and improves database performance. In this tutorial, we will design a Client–Policy Insurance Database in Oracle and implement a PL/SQL function and cursor in a simple, beginner-friendly way.

Problem Statement

Consider the following tables:

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
Constraint: Primary key, prem_amt and maturity_amt should be > 0.

Question:

1. Write a function which will return total maturity amount of policies of a particular client.
2. Write a cursor which will display policy date wise client details.

Logic Explanation

Step 1: Convert into 3NF

Since Client and Policy_info have a Many-to-Many relationship, we create a third table called CLIENT_POLICY (junction table).

This ensures:

• No data redundancy
• Proper normalization
• Clear primary and foreign key relationships

Step 2: Apply Constraints

• client_no → Primary Key
• policy_no → Primary Key
• maturity_amt > 0
• prem_amt > 0

Step 3: PL/SQL Function Logic

• Join POLICY_INFO and CLIENT_POLICY
• Use SUM(maturity_amt)
• Return total maturity amount for the given client

Step 4: Cursor Logic

• Join CLIENT, CLIENT_POLICY, and POLICY_INFO
• Sort results by policy_date
• Display output using DBMS_OUTPUT

Source Code

Step 1: Create Tables

CREATE TABLE CLIENT
(
    client_no      NUMBER PRIMARY KEY,
    client_name    VARCHAR2(100) NOT NULL,
    address        VARCHAR2(200),
    birthdate      DATE
);

CREATE TABLE POLICY_INFO 
(
    policy_no      NUMBER PRIMARY KEY,
    description    VARCHAR2(200),
    maturity_amt   NUMBER CHECK (maturity_amt > 0),
    prem_amt       NUMBER CHECK (prem_amt > 0),
    policy_date    DATE
);

CREATE TABLE CLIENT_POLICY 
(
    client_no      NUMBER,
    policy_no      NUMBER,
    PRIMARY KEY (client_no, policy_no),
    FOREIGN KEY (client_no) REFERENCES CLIENT(client_no),
    FOREIGN KEY (policy_no) REFERENCES POLICY_INFO(policy_no)
);

Step 2: Insert Sample Data

INSERT INTO CLIENT VALUES (1, 'Rahul Sharma', 'Delhi', DATE '1990-05-12');
INSERT INTO CLIENT VALUES (2, 'Anita Verma', 'Mumbai', DATE '1988-09-25');
INSERT INTO CLIENT VALUES (3, 'Suresh Kumar', 'Chennai', DATE '1992-01-18');

INSERT INTO POLICY_INFO VALUES (101, 'Life Insurance Policy', 500000, 12000, DATE '2020-03-15');
INSERT INTO POLICY_INFO VALUES (102, 'Health Insurance Policy', 300000, 10000, DATE '2021-06-10');
INSERT INTO POLICY_INFO VALUES (103, 'Retirement Policy', 800000, 15000, DATE '2019-11-01');

INSERT INTO CLIENT_POLICY VALUES (1, 101);
INSERT INTO CLIENT_POLICY VALUES (1, 102);
INSERT INTO CLIENT_POLICY VALUES (2, 103);
INSERT INTO CLIENT_POLICY VALUES (3, 101);

1. PL/SQL Function

Function to Return Total Maturity Amount

CREATE OR REPLACE FUNCTION get_total_maturity(p_client_no IN NUMBER)
RETURN NUMBER IS
    v_total_maturity NUMBER := 0;
BEGIN
    SELECT SUM(pi.maturity_amt)
    INTO v_total_maturity
    FROM policy_info pi
    JOIN client_policy cp
      ON pi.policy_no = cp.policy_no
    WHERE cp.client_no = p_client_no;

    IF v_total_maturity IS NULL THEN
        v_total_maturity := 0;
    END IF;

    RETURN v_total_maturity;
END;
/

Calling the Function

SELECT get_total_maturity(1) FROM dual;

2. Cursor to Display Policy Date Wise Client Details

SET SERVEROUTPUT ON;

DECLARE
    CURSOR c_policy_details IS
        SELECT c.client_no,
               c.client_name,
               p.policy_no,
               p.policy_date,
               p.maturity_amt,
               p.prem_amt
        FROM client c
        JOIN client_policy cp
          ON c.client_no = cp.client_no
        JOIN policy_info p
          ON cp.policy_no = p.policy_no
       ORDER BY p.policy_date;

BEGIN
    FOR r IN c_policy_details LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Client No: ' || r.client_no ||
            ', Name: ' || r.client_name ||
            ', Policy No: ' || r.policy_no ||
            ', Policy Date: ' || TO_CHAR(r.policy_date, 'DD-MON-YYYY') ||
            ', Maturity: ' || r.maturity_amt ||
            ', Premium: ' || r.prem_amt
        );
    END LOOP;
END;
/

Sample Output

GET_TOTAL_MATURITY(1)
---------------------
800000

(500000 + 300000)

Cursor Output

Client No: 2, Name: Anita Verma, Policy No: 103, Policy Date: 01-NOV-2019, Maturity: 800000, Premium: 15000
Client No: 1, Name: Rahul Sharma, Policy No: 101, Policy Date: 15-MAR-2020, Maturity: 500000, Premium: 12000
Client No: 3, Name: Suresh Kumar, Policy No: 101, Policy Date: 15-MAR-2020, Maturity: 500000, Premium: 12000
Client No: 1, Name: Rahul Sharma, Policy No: 102, Policy Date: 10-JUN-2021, Maturity: 300000, Premium: 10000

Viva Questions

1. Q: What is 3NF?
   A: A table is in Third Normal Form if it is in 2NF and has no transitive dependency.

2. Q: Why do we create CLIENT_POLICY table?
A: To resolve the Many-to-Many relationship between CLIENT and POLICY_INFO.

3. Q: What is CHECK constraint?
A: It ensures that column values satisfy a specified condition.

4. Q: Difference between Function and Procedure?
A: A Function returns a value. A Procedure does not return a value directly.

5. Q: What is a Cursor?
A: A Cursor is used in PL/SQL to fetch and process multiple rows one at a time.