Scribd
Upload
10 views

Sample Lab Program

The document describes the creation of tables for a library database including tables for books, publishers, authors, branches, book copies, and book lending with sample data inserted.

Uploaded by

bjbhargav06
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
10 views

Sample Lab Program

The document describes the creation of tables for a library database including tables for books, publishers, authors, branches, book copies, and book lending with sample data inserted.

Uploaded by

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

Sample lab program

1. Consider the following schema for a Library Database:

BOOK(Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS(Book_id, Author_Name)


PUBLISHER(Name, Address, Phone) BOOK_COPIES(Book_id, Branch_id, No_of_Copies)

BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH(Branch_id,


Branch_Name, Address)

CREATE TABLE PUBLISHER (NAME VARCHAR2 (20), PHONE INTEGER,

ADDRESS VARCHAR2 (20),

CONSTRAINT PKP PRIMARY KEY(NAME));

CREATE TABLE BOOK (BOOK_ID VARCHAR(8), TITLE VARCHAR2 (20),

PUBLISHER_NAME VARCHAR(20), PUB_YEAR INTEGER,

CONSTRAINT PKB PRIMARY KEY(BOOK_ID),

CONSTRAINT FKB FOREIGN KEY(PUBLISHER_NAME) REFERENCES PUBLISHER(NAME));

CREATE TABLE BOOK_AUTHORS (BOOK_ID VARCHAR(8), AUTHOR_NAME VARCHAR2 (20),

CONSTRAINT PKBA PRIMARY KEY (BOOK_ID,AUTHOR_NAME),

CONSTRAINT FKBA FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE);

CREATE TABLE LIBRARY_BRANCH (BRANCH_ID VARCHAR(6), BRANCH_NAME VARCHAR2 (20),

ADDRESS VARCHAR2 (20),

CONSTRAINT PKLB PRIMARY KEY(BRANCH_ID));

CREATE TABLE BOOK_COPIES (BOOK_ID VARCHAR(8), BRANCH_ID VARCHAR2(6), NO_OF_COPIES


INTEGER,

CONSTRAINT PKBC PRIMARY KEY(BOOK_ID, BRANCH_ID),


CONSTRAINT FKBC FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE,
CONSTRAINT FKBB FOREIGN KEY(BRANCH_ID) REFERENCES LIBRARY_BRANCH(BRANCH_ID));

CREATE TABLE BOOK_LENDING (BOOK_ID VARCHAR(8), BRANCH_ID VARCHAR2(6), CARD_NO INTEGER,

DATE_OUT DATE, DUE_DATE DATE,

CONSTRAINT PKBL PRIMARY KEY(BOOK_ID, BRANCH_ID,CARD_NO), CONSTRAINT FKBL FOREIGN


KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE);

INSERT INTO PUBLISHER VALUES ('MCGRAW-HILL', 9989076587, 'BANGALORE'); INSERT INTO


PUBLISHER VALUES ('PEARSON', 9889076565, 'NEWDELHI'); INSERT INTO PUBLISHER VALUES
('RANDOM HOUSE', 7455679345, 'HYDRABAD');

INSERT INTO PUBLISHER VALUES ('HACHETTE LIVRE', 8970862340, 'CHENAI'); INSERT INTO PUBLISHER
VALUES ('GRUPO PLANETA', 7756120238, 'BANGALORE');

SQL> SELECT * FROM PUBLISHER;

NAME PHONE ADDRESS

MCGRAW-HILL 9989076587 BANGALORE

PEARSON 9889076565 NEWDELHI

RANDOM HOUSE 7455679345 HYDRABAD

HACHETTE LIVRE 8970862340 CHENAI

GRUPO PLANETA 7756120238 BANGALORE

INSERT INTO BOOK VALUES ('1','DBMS', 'MCGRAW-HILL',2017); INSERT INTO BOOK VALUES
('2','ADBMS', 'MCGRAW-HILL',2016); INSERT INTO BOOK VALUES ('3','CN', 'PEARSON',2016);

INSERT INTO BOOK VALUES ('4','CG', 'GRUPO PLANETA',2015); INSERT INTO BOOK VALUES ('5','OS',
'PEARSON',2016);

SQL> SELECT * FROM BOOK;

BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR

1 DBMS MCGRAW-HILL 2017

2 ADBMS MCGRAW-HILL 2016


3 CN PEARSON 2016

4 CG GRUPO PLANETA 2015

5 OS PEARSON 2016

INSERT INTO BOOK_AUTHORS VALUES ('1','NAVATHE'); INSERT INTO BOOK_AUTHORS VALUES


('2','NAVATHE'); INSERT INTO BOOK_AUTHORS VALUES ('3','TANENBAUM'); INSERT INTO
BOOK_AUTHORS VALUES ('4','EDWARD ANGEL'); INSERT INTO BOOK_AUTHORS VALUES ('5','GALVIN');

SQL> SELECT * FROM BOOK_AUTHORS ;

BOOK_ID AUTHOR_NAME

1 NAVATHE

2 NAVATHE

3 TANENBAUM

4 EDWARD ANGEL

5 GALVIN

INSERT INTO LIBRARY_BRANCH VALUES ('10','VV PURAM','BANGALORE'); INSERT INTO


LIBRARY_BRANCH VALUES ('11','BIT','BANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES ('12','RAJAJI NAGAR', 'BANGALORE'); INSERT INTO


LIBRARY_BRANCH VALUES ('13','JP NAGAR','BANGALORE'); INSERT INTO LIBRARY_BRANCH VALUES
('14','JAYANAGAR','BANGALORE');
SQL> SELECT * FROM LIBRARY_BRANCH;

BRANCH BRANCH_NAME ADDRESS


------ ----------------------------- ----------------
10 VV PURAM BANGALORE
11 BIT BANGALORE
12 RAJAJI NAGAR BANGALORE
13 JP NAGAR BANGALORE
14 JAYANAGAR BANGALORE

INSERT INTO BOOK_COPIES VALUES ( '1','10', 10);


INSERT INTO BOOK_COPIES VALUES ( '1','11', 5); INSERT
INTO BOOK_COPIES VALUES ( '2','12', 2); INSERT INTO
BOOK_COPIES VALUES ( '2','13', 5); INSERT INTO
BOOK_COPIES VALUES ( '3','14', 7); INSERT INTO
BOOK_COPIES VALUES ( '5','10', 1); INSERT INTO
BOOK_COPIES VALUES ( '4','11', 3);

SQL> SELECT * FROM BOOK_COPIES;

BOOK_ID BRANCH NO_OF_COPIES

1 10 10
1 11 5
2 12 2
2 13 5
3 14 7
5 10 1
4 11 3
INSERT INTO BOOK_LENDING VALUES ('1', '10', 101,'01-JAN-17','01-JUN-17'); INSERT INTO
BOOK_LENDING VALUES ('3', '14', 101,'11-JAN-17','11-MAR-17' ); INSERT INTO
BOOK_LENDING VALUES ('2', '13', 101,'21-FEB-17','21-APR-17'); INSERT INTO
BOOK_LENDING VALUES ('4', '11', 101,'15-MAR-17','15-JUL-17'); INSERT INTO
BOOK_LENDING VALUES ('1', '11', 104,'12-APR-17','12-MAY-17' )
SQL> SELECT * FROM BOOK_LENDING;

BOOK_ID BRANCH CARD_NO DATE_OUT DUE_DATE

1 10 101 01-JAN-17 01-JUN-17


3 14 101 11-JAN-17 11-MAR-17
2 13 101 21-FEB-17 21-APR-17
4 11 101 15-MAR-17 15-JUL-17
1 11 104 12-APR-17 12-MAY-17

You might also like