Q3. Consider the following entities and their relationships. Create a 

RDB in 3 NF with appropriate data types and Constraints. [15 Marks]

Book (Book_no, title, author, price, year_published) Customer (cid, cname, addr)

Relation between Book and Customer is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be >0;

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

---

Q1. Design RDB in 3NF with Appropriate Data Types and Constraints

Entities

1. CLIENT (client_no, client_name, address, birthdate)
2. POLICY_INFO (policy_no, description, maturity_amt, prem_amt, policy_date)
3. Relationship: Many-to-Many
4. Junction Table: CLIENT_POLICY

---

Step 1: Create Tables (3NF Structure)

1. CLIENT Table

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

---

2. POLICY_INFO Table

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
);

Constraints Applied:

• Primary Key: policy_no
• CHECK: maturity_amt > 0
• CHECK: prem_amt > 0

---

3. CLIENT_POLICY Table (Junction Table)

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)
);

Purpose:
Resolves the Many-to-Many relationship between CLIENT and POLICY_INFO.

---

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);

---

Q2. PL/SQL Function

Function: Return Total Maturity Amount of a Particular Client

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;

Output

800000

(500000 + 300000)

---

Q3. Cursor: 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;

Viva Questions

1. What is 3NF?
A table is in 3NF if it is in 2NF and has no transitive dependency.

2. Why is CLIENT_POLICY created?
To resolve the Many-to-Many relationship between CLIENT and POLICY_INFO.

3. What is CHECK constraint?
It restricts column values based on a condition.

4. Difference between Function and Procedure?
Function returns a value; Procedure does not return a value directly.

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