Notes

CREATE TABLE publisher(
name VARCHAR2(20),
address VARCHAR2(20),
phone VARCHAR2(20));


CREATE TABLE book(
book_id NUMBER(10),
title VARCHAR2(20),
publisher_name VARCHAR2(20));


CREATE TABLE book_authors(
book_id NUMBER(10),
title VARCHAR2(20),
publisher_name VARCHAR2(20));




CREATE TABLE libary_branch(
branch_id NUMBER(10),
branch_name VARCHAR2(20),
address VARCHAR2(20));



CREATE TABLE borrower(
card_no NUMBER(10),
name VARCHAR2(20),
address VARCHAR2(20)
phone VARCHAR2(20));




CREATE TABLE book_copies(
book_id NUMBER(10),
branch_id VARCHAR2(20),
number_of_copies VARCHAR2(20));

CREATE TABLE book_loans(
book_id NUMBER(10),
branch_id NUMBER(10),
card_no NUMBER(10),
date_out DATE,
due_date DATE);





ALTER TABLE publisher
ADD CONSTRAINTS pk_publisher
PRIMARY KEY (name);

ALTER TABLE book
ADD CONSTRAINTS fk_f_publisher
FOREIGN KEY(publisher_name)
REFERENCES publisher(name);



ALTER TABLE book
ADD CONSTRAINTS pk_book
PRIMARY KEY (book_id);


ALTER TABLE book_authors
ADD CONSTRAINTS fk_f_authors
FOREIGN KEY(book_id)
REFERENCES book(book_id);

ALTER TABLE book_copies
ADD CONSTRAINTS fk_f_copies
FOREIGN KEY(book_id)
REFERENCES book(book_id);


ALTER TABLE book_loans
ADD CONSTRAINTS fk_f_loans
FOREIGN KEY(book_id)
REFERENCES book(book_id);


ALTER TABLE libary_branch
ADD CONSTRAINTS pk_l_branch
PRIMARY KEY (branch_id);

ALTER TABLE borrower
ADD CONSTRAINTS pk_borrower
PRIMARY KEY (card_no);



ALTER TABLE book_copies
ADD CONSTRAINTS fk_f_book
FOREIGN KEY(book_id)
REFERENCES book(book_id);



ALTER TABLE book_copies
ADD CONSTRAINTS fk_f_lb
FOREIGN KEY(branch_id)
REFERENCES libary_branch(branch_id);


ALTER TABLE book_loans
ADD CONSTRAINTS fk_book
FOREIGN KEY(book_id)
REFERENCES book(book_id);


ALTER TABLE book_loans
ADD CONSTRAINTS fk_libary_br
FOREIGN KEY(branch_id)
REFERENCES book(branch_id);s

ALTER TABLE book_loans
ADD CONSTRAINTS fk_borrower
FOREIGN KEY(card_no)
REFERENCES book(card_no);