Oracle PL/SQL Program: Library Management Database in 3NF with Function and Parameterized Cursor

Introduction

Learn how to design a Library Management Database in 3NF and implement PL/SQL Function and Parameterized Cursor in Oracle. This beginner-friendly guide explains the logic, source code, sample output, and viva questions for exams.

Problem Statement

Consider the following entities and their relationships:

Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)

Relation between Library and Book is One-to-Many.
Constraints:

• Primary key must be defined.
• Price should not be NULL.

Tasks:

1. Write a function which will accept publication name from user and display total price of books of that publication.
2. Write a cursor which will display library wise book details (Use Parameterized Cursor).

Logic

Step 1: Database Design in 3NF

To achieve Third Normal Form (3NF):

• Each table has a primary key.
• No partial dependency.
• No transitive dependency.
• One-to-Many relationship implemented using foreign key (Lno in Book table referencing Library).

Step 2: Function Logic

• Accept publication name as input parameter.
• Use SUM(Price) to calculate total price.
• Use NVL to return 0 if no records found.

Step 3: Parameterized Cursor Logic

• Create cursor with library name as parameter.
• Fetch book details library-wise.
• Display results using DBMS_OUTPUT.

Source Code

1️⃣ Create Library Table

CREATE TABLE Library(
    Lno NUMBER PRIMARY KEY,
    Lname VARCHAR2(50),
    Location VARCHAR2(50),
    Librarian VARCHAR2(50),
    no_of_books NUMBER
);

2️⃣ Create Book Table

CREATE TABLE Book(
    Bid NUMBER PRIMARY KEY,
    Bname VARCHAR2(50),
    Author_Name VARCHAR2(50),
    Price NUMBER(10,2) NOT NULL,
    publication VARCHAR2(50),
    Lno NUMBER REFERENCES Library(Lno)
);

3️⃣ Function to Calculate Total Price by Publication

CREATE OR REPLACE FUNCTION total_price_by_publication(pub_name IN VARCHAR2)
RETURN NUMBER
AS
    total_price NUMBER := 0;
BEGIN
    SELECT SUM(Price)
    INTO total_price
    FROM Book
    WHERE publication = pub_name;

    RETURN NVL(total_price,0);
END;
/

Execute Function

SET SERVEROUTPUT ON;
DECLARE
    total NUMBER;
BEGIN
    total := total_price_by_publication('Pearson');
    DBMS_OUTPUT.PUT_LINE('Total price of books by Pearson: ' || total);
END;
/

4️⃣ Parameterized Cursor for Library-wise Book Details

DECLARE
    CURSOR lib_books_cur(lib_name VARCHAR2) IS
        SELECT b.Bid,b.Bname,b.Author_Name,b.Price,b.publication
        FROM Book b
        JOIN Library l ON b.Lno = l.Lno
        WHERE l.Lname = lib_name
        ORDER BY b.Bid;

    rec lib_books_cur%ROWTYPE;
BEGIN
    OPEN lib_books_cur('Central Library');
    LOOP
        FETCH lib_books_cur INTO rec;
        EXIT WHEN lib_books_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Book ID: ' || rec.Bid ||
                             ' Title: ' || rec.Bname ||
                             ' Author: ' || rec.Author_Name ||
                             ' Price: ' || rec.Price ||
                             ' Publication: ' || rec.publication);
    END LOOP;
    CLOSE lib_books_cur;
END;
/

5️⃣ Insert Sample Data

INSERT INTO Library VALUES(1,'Central Library','Mumbai','John Doe',3);
INSERT INTO Library VALUES(2,'City Library','Delhi','Jane Smith',2);

INSERT INTO Book VALUES(101,'C++ Programming','Bjarne Stroustrup',500,'Pearson',1);
INSERT INTO Book VALUES(102,'Java Programming','James Gosling',600,'McGraw-Hill',1);
INSERT INTO Book VALUES(103,'Python Basics','Guido van Rossum',400,'Pearson',2);
INSERT INTO Book VALUES(104,'Data Structures','Mark Allen',700,'Pearson',1);

Sample Output

After executing function:

Total price of books by Pearson: 1600

After executing cursor:

Book ID: 101 Title: C++ Programming Author: Bjarne Stroustrup Price: 500 Publication: Pearson
Book ID: 102 Title: Java Programming Author: James Gosling Price: 600 Publication: McGraw-Hill
Book ID: 104 Title: Data Structures Author: Mark Allen Price: 700 Publication: Pearson

Viva Questions with Answers

1. What is 3NF?
   3NF ensures there is no transitive dependency and all non-key attributes depend only on the primary key.
2. What is a Function in PL/SQL?
   A function is a stored program that performs a calculation and must return a value.
3. What is a Parameterized Cursor?
   A parameterized cursor accepts parameters at runtime and retrieves records based on the given input.
4. What is the difference between Procedure and Function?
   A procedure performs an action and may not return a value. A function always returns a value.
5. What is NVL function?
   NVL replaces NULL with a specified value. It is used here to return 0 when no records are found.