IV.
APPLICATION
10. LIBRARY MANAGEMENT SYSTEM
AIM:
To design and implement a Library Management System application using Oracle 10g with
suitable tables for managing books, members and transactions.
PROCEDURE
Step 1: Start
Step 2: Create a database (schema) for the Library Management System.
Create the necessary tables:
▪ BOOKS (to store book details)
▪ MEMBERS (to store member details)
▪ ISSUE_RETURN (to manage issue and return transactions)
Step 3: Define Primary keys and Foreign keys for relationships.
Step 4: Insert sample records into each table.
Step 5: Perform basic SQL operations like INSERT, UPDATE, DELETE, SELECT to
manage data.
Step 6: Display details of books, members, and issued books using SELECT queries.
Step 7: Stop
(i) BOOKS Table
CREATE TABLE BOOKS (BOOK_ID NUMBER(5) PRIMARY KEY,
TITLE VARCHAR2(100),
AUTHOR VARCHAR2(50),
PUBLISHER VARCHAR2(50),
PRICE NUMBER(6,2),
STATUS VARCHAR2(10) );
(ii) MEMBERS Table
CREATE TABLE MEMBERS (MEMBER_ID NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(50),
ADDRESS VARCHAR2(100),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(50));
(iii) ISSUE_RETURN Table
CREATE TABLE ISSUE_RETURN (TRANS_ID NUMBER(5) PRIMARY KEY,
BOOK_ID NUMBER(5) REFERENCES BOOKS(BOOK_ID),
MEMBER_ID NUMBER(5) REFERENCES MEMBERS(MEMBER_ID),
ISSUE_DATE DATE,
RETURN_DATE DATE);
Insert Books
INSERT INTO BOOKS VALUES (101, 'Database Systems', 'Elmasri', 'Pearson', 550, 'Available');
INSERT INTO BOOKS VALUES (103, 'Computer Networks', 'Tanenbaum', 'PHI', 600, 'Issued');
INSERT INTO BOOKS VALUES (102, 'Operating System', 'Galvin', 'Wiley', 450, 'Available');
SELECT * FROM BOOKS;
BOOK_ID TITLE AUTHOR PUBLISHER PRICE STATUS
101 Database Systems Elmasri Pearson 550 Available
Computer
103 Tanenbaum PHI 600 Issued
Networks
102 Operating System Galvin Wiley 450 Available
Insert Members
INSERT INTO MEMBERS VALUES (201, 'Selva', 'Chennai', '9876543210', 'selva@gmail.com');
INSERT INTO MEMBERS VALUES (202, 'Kumar', 'Madurai', '9876512345', 'kumar@yahoo.com');
SELECT * FROM MEMBERS;
MEMBER_ID NAME ADDRESS PHONE EMAIL
201 Selva Chennai 9876543210 selva@gmail.com
202 Kumar Madurai 9876512345 kumar@yahoo.com
Insert Issue Transactions
INSERT INTO ISSUE_RETURN VALUES (301, 103, 201, SYSDATE, NULL);
SELECT * FROM ISSUE_RETURN
TRANS_ID BOOK_ID MEMBER_ID ISSUE_DATE RETURN_DATE
301 103 201 01-Sep-25
Show all issued books with member details
SELECT I.TRANS_ID, B.TITLE, M.NAME, I.ISSUE_DATE
FROM ISSUE_RETURN I
JOIN BOOKS B ON I.BOOK_ID = B.BOOK_ID
JOIN MEMBERS M ON I.MEMBER_ID = M.MEMBER_ID;
OUTPUT:
TRANS_ID TITLE NAME ISSUE_DATE
Computer
301 Selva 01-Sep-25
Networks
Result.
Thus the Library Management System application was created successfully using
Oracle 10g with BOOKS, MEMBERS, and ISSUE_RETURN tables, and tested with sample
queries.
