Scribd
Upload
15 views5 pages

Library SQL Project Explanation

The document outlines a library system project using Oracle SQL Plus, detailing the creation of tables for students, books, and borrow records. It includes procedures for inserting student data, calculating fines, extending due dates, displaying books with cursors, and a trigger to check fines upon borrowing. The project demonstrates fundamental SQL operations and PL/SQL programming techniques for managing a library system.

Uploaded by

kavyamanimaran.02
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views5 pages

Library SQL Project Explanation

The document outlines a library system project using Oracle SQL Plus, detailing the creation of tables for students, books, and borrow records. It includes procedures for inserting student data, calculating fines, extending due dates, displaying books with cursors, and a trigger to check fines upon borrowing. The project demonstrates fundamental SQL operations and PL/SQL programming techniques for managing a library system.

Uploaded by

kavyamanimaran.02
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Oracle SQL Plus - Library System Project Explanation

STEP 1: Create Tables

-- Student Table

CREATE TABLE student (

s_id NUMBER PRIMARY KEY,

s_name VARCHAR2(100),

s_gender VARCHAR2(10),

year NUMBER,

depart VARCHAR2(50)

);

-- Books Table

CREATE TABLE books (

b_id NUMBER PRIMARY KEY,

b_name VARCHAR2(100),

b_author VARCHAR2(100),

b_publish VARCHAR2(100)

);

-- Borrow Table

CREATE TABLE borrow (

s_id NUMBER,

b_id NUMBER,

i_date DATE,
due_date DATE,

r_date DATE,

fine NUMBER,

FOREIGN KEY (s_id) REFERENCES student(s_id),

FOREIGN KEY (b_id) REFERENCES books(b_id)

);

STEP 2: Procedure to Insert Data into Student Table

CREATE OR REPLACE PROCEDURE insert_student (

p_s_id IN NUMBER,

p_s_name IN VARCHAR2,

p_s_gender IN VARCHAR2,

p_year IN NUMBER,

p_depart IN VARCHAR2

IS

BEGIN

INSERT INTO student (s_id, s_name, s_gender, year, depart)

VALUES (p_s_id, p_s_name, p_s_gender, p_year, p_depart);

END;

-- To execute the procedure:

BEGIN

insert_student(1, 'Alice', 'Female', 2, 'CSE');

END;
/

STEP 3: Function to Calculate Fine

CREATE OR REPLACE FUNCTION calculate_fine (

p_s_id IN borrow.s_id%TYPE,

p_b_id IN borrow.b_id%TYPE

) RETURN NUMBER

IS

v_fine NUMBER := 0;

v_days NUMBER;

BEGIN

SELECT r_date - due_date

INTO v_days

FROM borrow

WHERE s_id = p_s_id AND b_id = p_b_id;

IF v_days > 0 THEN

v_fine := v_days * 10;

END IF;

RETURN v_fine;

END;

STEP 4: Implicit Cursor to Extend Due Dates


BEGIN

UPDATE borrow

SET due_date = due_date + 5;

DBMS_OUTPUT.PUT_LINE('Due dates extended by 5 days for all borrowed books.');

END;

STEP 5: Explicit Cursor to Display Books

DECLARE

CURSOR book_cursor IS

SELECT * FROM books;

v_book books%ROWTYPE;

BEGIN

OPEN book_cursor;

LOOP

FETCH book_cursor INTO v_book;

EXIT WHEN book_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('ID: ' || v_book.b_id || ', Name: ' || v_book.b_name ||

', Author: ' || v_book.b_author || ', Publisher: ' || v_book.b_publish);

END LOOP;

CLOSE book_cursor;

END;

STEP 6: Trigger to Check Fine


CREATE OR REPLACE TRIGGER check_fine

BEFORE INSERT OR UPDATE ON borrow

FOR EACH ROW

DECLARE

v_fine NUMBER := 0;

BEGIN

IF :NEW.r_date > :NEW.due_date THEN

v_fine := (:NEW.r_date - :NEW.due_date) * 10;

:[Link] := v_fine;

DBMS_OUTPUT.PUT_LINE('Student must pay fine: ' || v_fine);

ELSE

:[Link] := 0;

DBMS_OUTPUT.PUT_LINE('No fine needed.');

END IF;

END;

You might also like