DBMS PROJECT
TOPIC – LIBRARY
MANAGEMENT
SYSTEM
NAME - VISHAL KUMAR
ADM NO - 2018BCS1128
BRANCH – CSE
TABLE OF CONTENTS
S.NO Pg.No
Contents
1. Data requirements 3-4
• Entities
• Attributes
• Relationships-cardinality
2. Entity Relationship Diagram 4
3. Schema Diagram 5
4. Class Diagram 6
5. Creating Database using ORACLE-10G 6-10
6. Test-case queries 10-11
Data Requirements
Entities
• CUSTOMER
• BOOKS
• PUBLISHER
Attributes
CUSTOMER
Customer_ID
Customer_Name
Address
Street
City
State
Pincode
Contact_No.
Registration_date
BOOKS
Book_ID
Book_name
Category
Rental_price
Author
Status
PUBLISHER
Pub_ID
Pub_Name
Address
Contact_No.
RELATIONSHIP CARDINALITY
CUSTOMER borrowed BOOKS (1 – Many)
PUBLISHER published BOOKS (1 – Many)
ER DIAGRAM
SCHEMA DIAGRAM
CUSTOMER
Customer Name Street City State Pin Registration Issue Return Fine Book_id
_id code _date _date _date
BOOKS
Book_id Name Category Author Rental_price Pub_id
PUBLISHER
Pub_id Name Street City State Pincode
CUST_contact_no
Customer_id Contact_no
PUBL_contact_no
Pub_id Contact_no
CLASS DIAGRAM
CREATING DATABASE USING ORACLE-10G
1. Create table customer(customer_id int, name varchar(50), address varchar(50),
registration_date varchar(30), issue_date varchar(30), return_date varchar(30), fine int,
book_id int, primary key(customer_id), foreign key(book_id) references
books(book_id));
Desc customer;
2. Create table books(book_id int , book_name varchar(50), category varchar(50) ,
rental_price int(10) ,status varchar(50), author varchar(50), pub_id int, primary
key(book_id), foreign key(pub_id) references publisher(pub_id)) ;
Desc books;
3. Create table publisher(pub_id int, name varchar(50), address varchar(50), primary
key(pub_id));
Desc publisher;
4. Create table cust_contact_no(customer_id int, contact_no int, primary
key(customer_id, contact_no), foreign key(customer_id) references
customer(customer_id));
Desc cust_contact_no;
5. Create table publ_contact_no(pub_id int, contact_no int, primary key(pub_id,
contact_no), foreign key(pub_id) references publisher(pub_id));
Desc publ_contact_no;
6. Insert into customer values(01,'cust1','d-1 noida','1-mar-2014','20-mar-2014', '30-mar-
2014',0,1000);
6.1. Insert into customer values(02,'cust2','b-1 delhi','2-mar-2017','15-mar-2017', '5-apr-
2017',10,1001);
6.2. Insert into customer values(03,'cust3','sector-11 noida','3-jun-2018', '10-jun-2018','15-
jun-2018',0,1002);
6.3. Insert into customer values(04,'cust4','dilshad garden delhi','4-jan-2019', '23-jan-
2019','10-feb-2019',50,1003);
6.4. Insert into customer values(05,'cust5','sector-15 noida','6-feb-2016','18-feb-2016', '25-
feb-2016',0,1004);
Select * from customer;
7. insert into books values(1000,'book1','comedy',5,'available','author1',100);
7.1. insert into books values(1001,'book2','scifi',10,'available','author2',101);
7.2. insert into books values(1002,'book3','romance',15,'available','author3',102);
7.3. insert into books values(1003,'book4','thriller',20,'unavailable','author4',103);
7.4. insert into books values(1004,'book5', 'horror',18,'unavailable','author5',104);
Select * from books;
8. insert into publisher values(100,'pub1','delhi');
8.1. insert into publisher values(101,'pub2','kanpur');
8.2. insert into publisher values(102,'pub3','uttarpradesh');
8.3. insert into publisher values(103,'pub4','lucknow');
8.4. insert into publisher values(104,'pub5','mumbai');
Select * from publisher;
9. insert into cust_contact_no values(01,9749087298);
9.1. insert into cust_contact_no values(02,9795075390);
9.2. insert into cust_contact_no values(03,8984875939);
9.3. insert into cust_contact_no values(04,6589898502);
9.4. insert into cust_contact_no values(05,7990057309);
Select * from cust_contact_no;
10. insert into publ_contact_no values(100,9876543211);
10.1. insert into publ_contact_no values(101,8603744851);
10.2. insert into publ_contact_no values(102,9835785472);
10.3. insert into publ_contact_no values(103,9999558783);
10.4. insert into publ_contact_no values(104,8889874514);
Select * from publ_contact_no;
TEST-CASE QUERIES
1. insert into customer values(6,'cust6','tilak nagar, delhi','12-aug-2015','14-aug-2015','26-
aug-2015',5,1005);
2. insert into books values(1005,'book6','detective',6,'available','author6',105);
3. insert into cust_contact_no values(6,9867984906);
4. insert into publ_contact_no values(105,8768475898);
5. insert into customer values(3,'cust6','tilak nagar, delhi','12-aug-2015','14-aug-2015','26-
aug-2015',5,1005);
6. insert into books values(1003,'book6','detective',6,'available','author6',105);
7. insert into publisher values(104,'pub6','banglore');
8. insert into cust_contact_no values(01,9997895879);
9. insert into publ_contact_no values(102,7654788588);
10.insert into cust_contact_no values(2,9795075390);
11. insert into publ_contact_no values(101,8603744851);
