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)
Write the SQL queries which are given below:

i. Creation of tables with their attributes:
BOOK (Book_id, Title, Publisher_Name, Pub_Year)

create table book(
book_id int not null,
title varchar(25) not null,
publisher_name varchar(20) not null,
pub_year int not null,
primary key(book_id)
);

ii. BOOK_AUTHORS (Book_id, Author_Name)

create table book_authors(
book_id int not null,
author_name varchar(20) not null,
primary key(book_id)
);

iii. PUBLISHER (Name, Address, Phone)

create table publisher(
name varchar(20) not null,
address char(25) not null,
phone int not null,
primary key(phone));

iv. BOOK_COPIES (Book_id, Branch_id, No_of_Copies)

create table book_copies(
book_id int not null,
branch_id int not null,
no_of_copies int not null,
primary key(book_id)
);

v. BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date)

create table book_lending(
book_id int not null,
branch_id int not null,
card_no int not null,
date_out date not null,
due_date date not null,
primary key(book_id)
);

vi. LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

create table library_branch(
branch_id int not null,
branch_name varchar(25) not null,
address char(25) not null,
primary key(branch_id)
);

Insertion of records:
i. BOOK (Book_id, Title, Publisher_Name, Pub_Year)

insert into book(book_id, title, publisher_name, pub_year)values(1111, 'SE', 'PEARSON', 2005);
insert into book(book_id, title, publisher_name, pub_year)values(2222, 'DBMS', 'MCGRAW', 2004);
insert into book(book_id, title, publisher_name, pub_year)values(3333, 'ANOTOMY', 'PEARSON', 2010);
insert into book(book_id, title, publisher_name, pub_year)values(4444, 'ENCYCLOPEDIA', 'SAPNA', 2010);

ii. BOOK_AUTHORS (Book_id, Author_Name)

insert into book_authors(book_id, author_name)values(1111, 'SOMMERVILLE');
insert into book_authors(book_id, author_name)values(2222, 'NAVATHE');
insert into book_authors(book_id, author_name)values(3333, 'HENRY GRAY');
insert into book_authors(book_id, author_name)values(4444, 'THOMAS');

iii. PUBLISHER (NAME, ADDRESS, PHONE)

insert into publisher(name, address, phone)values('PEARSON', 'BANGALORE', 9875462530);
insert into publisher(name, address, phone)values('MCGRAW', 'NEWDELHI', 7845691234);
insert into publisher(name, address, phone)values('SAPNA', 'BANGALORE', 7845963210);

iv. BOOK_COPIES (BOOK_ID, BRANCH_ID, NO_OF_COPIES)

alter table book_copies
drop primary key;
(here we drop primary key because our book_id is repeated which creates an error “unique constraints violates”. And if we don’t mention a primary key while creating a table it shows an error of “invalid identifier”.)
insert into book_copies(book_id, branch_id, no_of_copies)values(1111, 11, 5);
insert into book_copies(book_id, branch_id, no_of_copies)values(3333, 22, 6);
insert into book_copies(book_id, branch_id, no_of_copies)values(4444, 33, 10);
insert into book_copies(book_id, branch_id, no_of_copies)values(2222, 11, 12);
insert into book_copies(book_id, branch_id, no_of_copies)values(4444, 55, 3);

v. BOOK_LENDING(BOOK_ID, BRANCH_ID, CARD_NO, DATE_OUT, DUE_DATE)

alter table book_lending
drop primary key;
(here we drop primary key because our book_id is repeated which creates an error “unique constraints violates”. And if we don’t mention a primary key while creating a table it shows an error of “invalid identifier”.)
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(2222, 11, 1, TO_DATE('10-JAN-17','DD-MM-YY'), TO_DATE('20-AUG-17','DD-MM-YY'));
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(3333, 22, 2, TO_DATE('09-JUL-17','DD-MM-YY'), TO_DATE('12-AUG-17','DD-MM-YY'));
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(4444, 55, 1, TO_DATE('11-APR-17','DD-MM-YY'), TO_DATE('09-AUG-17','DD-MM-YY'));
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(2222, 11, 5, TO_DATE('09-AUG-17','DD-MM-YY'), TO_DATE('19-AUG-17','DD-MM-YY'));
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(4444, 33, 1, TO_DATE('10-JUL-17','DD-MM-YY'), TO_DATE('15-AUG-17','DD-MM-YY'));
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(1111, 11, 1, TO_DATE('12-MAY-17','DD-MM-YY'), TO_DATE('10-JUN-17','DD-MM-YY'));
insert into book_lending(book_id, branch_id, card_no, date_out, due_date)values(3333, 22, 1, TO_DATE('10-JUL-17','DD-MM-YY'), TO_DATE('15-JUL-17','DD-MM-YY'));

vi. LIBRARY_BRANCH (BRANCH_ID, BRANCH_NAME, ADDRESS)

insert into library_branch(branch_id, branch_name, address)values(11, 'CENTRAL TECHNICAL', 'MG ROAD');
insert into library_branch(branch_id, branch_name, address)values(22, 'MEDICAL', 'BH ROAD');
insert into library_branch(branch_id, branch_name, address)values(33, 'CHILDREN', 'SS PURAM');
insert into library_branch(branch_id, branch_name, address)values(44, 'SECRETARIAT', 'SIRAGATE');
insert into library_branch(branch_id, branch_name, address)values(55, 'GENERAL', 'JAYANAGAR');

Dear Readers, you can support wointec.com, by follow us on the following Channels: