Notes

CREATE database library_system

CREATE TABLE publishers(
name varchar(20),
address varchar(15),
phone varchar(10)
);


CREATE TABLE books(
book_id int(4),
title varchar(15),
publisher_name varchar(15)
);


CREATE TABLE book_authors(
book_id int(4),
author_name varchar(15)
);


CREATE TABLE library_branches(
branch_id int(4),
branch_name varchar(15),
address varchar(15)
);


CREATE TABLE borrowers(
card_no int(4),
name varchar(15),
address varchar(15),
phone varchar(10)
);


CREATE TABLE book_copies(
book_id int(4),
branch_id int(4),
no_of_copies int(2)
);


CREATE TABLE book_loans(
book_id int(4),
branch_id int(4),
card_no int(4),
date_out DATE,
due_date DATE
);




ALTER TABLE publishers
ADD CONSTRAINT pk_publishers
PRIMARY KEY(name);

ALTER TABLE books
ADD CONSTRAINT pk_books
PRIMARY KEY(book_id);

ALTER TABLE book_authors
ADD CONSTRAINT pk_book_authors
PRIMARY KEY(book_id, author_name);

ALTER TABLE library_branches
ADD CONSTRAINT pk_library_branches
PRIMARY KEY(branch_id);

ALTER TABLE borrowers
ADD CONSTRAINT pk_borrowers
PRIMARY KEY(card_no);

ALTER TABLE book_copies
ADD CONSTRAINT pk_book_copies
PRIMARY KEY(book_id, branch_id);

ALTER TABLE book_loans
ADD CONSTRAINT pk_book_loans
PRIMARY KEY(book_id, branch_id, card_no);


ALTER TABLE books
ADD CONSTRAINT fk_b_ publishers
FOREIGN KEY(publisher_name)
REFERENCES publishers(name);

ALTER TABLE book_authors
ADD CONSTRAINT fk_ba_books
FOREIGN KEY(book_id)
REFERENCES books(book_id);

ALTER TABLE book_copies
ADD CONSTRAINT fk_bc_books
FOREIGN KEY(book_id)
REFERENCES books(book_id);

ALTER TABLE book_copies
ADD CONSTRAINT fk_bc_library_branches
FOREIGN KEY(branch_id)
REFERENCES library_branches(branch_id);

ALTER TABLE book_loans
ADD CONSTRAINT fk_bl_books
FOREIGN KEY(book_id)
REFERENCES books(book_id);

ALTER TABLE book_loans
ADD CONSTRAINT fk_bl_library_branches
FOREIGN KEY(branch_id)
REFERENCES library_branches(branch_id);

ALTER TABLE book_loans
ADD CONSTRAINT fk_bl_borrowers
FOREIGN KEY(card_no)
REFERENCES borrowers(card_no);


INSERT INTO publishers(name, address, phone)
VALUES('RAM', 'JORPATI', '9818900788');
INSERT INTO publishers(name, address, phone)
VALUES('SAM', 'KALANKI', '9818900788');
INSERT INTO publishers(name, address, phone)
VALUES('DAM', 'BANESWOR', '9818900788');
INSERT INTO publishers(name, address, phone)
VALUES('MAM', 'RATNAPARK', '9818900788');
INSERT INTO publishers(name, address, phone)
VALUES('HAM', 'KOTESHWOR', '9818900788');

INSERT INTO books
VALUES(1001, 'STORY BOOK', 'RAM');
INSERT INTO books
VALUES(1002, 'THE LOST TRIBE', 'SAM');
INSERT INTO books
VALUES(1003, 'THE LOST TRIBE', 'DAM');
INSERT INTO books
VALUES(1004, 'POEM BOOK', 'MAM');
INSERT INTO books
VALUES(1005, 'NOVEL BOOK', 'HAM');

INSERT INTO book_authors
VALUES(1001, 'HENRY');
INSERT INTO book_authors
VALUES(1001, 'SHAKSEPEARE');
INSERT INTO book_authors
VALUES(1002, 'STEPHEN KING');
INSERT INTO book_authors
VALUES(1004, 'ANEL');
INSERT INTO book_authors
VALUES(1003, 'HALY');

INSERT INTO library_branches
VALUES(1001, 'HIMAL', 'JORPATI');
INSERT INTO library_branches
VALUES(1002, 'SHARPSTOWN', 'KALANKI');
INSERT INTO library_branches
VALUES(1003, 'PAHAD', 'BANESWOR');
INSERT INTO library_branches
VALUES(1004, 'CENTRAL', 'KALIMATI');
INSERT INTO library_branches
VALUES(1005, 'TERAI', 'BOUDHA');

INSERT INTO borrowers
VALUES(4000, 'HARRY', 'JORPATI', '9987149728');
INSERT INTO borrowers
VALUES(4001, 'KATY', 'RATNAPARK', '9987319728');
INSERT INTO borrowers
VALUES(4002, 'JENNY', 'POKHARA', '9987341728');
INSERT INTO borrowers
VALUES(4003, 'HARI', 'JORPATI', '9987349721');
INSERT INTO borrowers
VALUES(4004, 'KOLA', 'KALIMATI', '9987349718');

INSERT INTO book_copies
VALUES(1001, 1001, 21);
INSERT INTO book_copies
VALUES(1002, 1002, 8);
INSERT INTO book_copies
VALUES(1001, 1002, 6);
INSERT INTO book_copies
VALUES(1003, 1004, 5);
INSERT INTO book_copies
VALUES(1003, 1003, 10);

INSERT INTO book_loans
VALUES(1001, 1001, 4000, '12-MAR-2018', '18-MAR-2018');
INSERT INTO book_loans
VALUES(1002, 1002, 4000, '12-APR-2018', '18-APR-2018');
INSERT INTO book_loans
VALUES(1005, 1005, 4005, '12-JUL-2018', '18-JUL-2018');
INSERT INTO book_loans
VALUES(1003, 1003, 4000, '12-AUG-2018', '18-AUG-2018');
INSERT INTO book_loans
VALUES(1004, 1002, 4001, '12-SEP-2018', '18-SEP-2018');
INSERT INTO book_loans
VALUES(1001, 1002, 4004, '21-SEP-2018', '28-SEP-2018');