CREATE.sql

CREATE TABLE PUBLISHER
(NAME VARCHAR2 (20) PRIMARY KEY,
ADDRESS VARCHAR2 (20),
PHONE NUMBER (10));

CREATE TABLE BOOK
(BOOK_ID NUMBER(4) PRIMARY KEY,
TITLE VARCHAR2 (20),
PUB_YEAR VARCHAR2 (20),
PUBLISHER_NAME VARCHAR2(20),
FOREIGN KEY (PUBLISHER_NAME) REFERENCES PUBLISHER (NAME) ON DELETE CASCADE);

CREATE TABLE BOOK_AUTHORS
(BOOK_ID NUMBER(4),
AUTHOR_NAME VARCHAR2 (20),
PRIMARY KEY (BOOK_ID, AUTHOR_NAME),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE);

CREATE TABLE LIBRARY_BRANCH
(BRANCH_ID INTEGER PRIMARY KEY,
BRANCH_NAME VARCHAR2 (20),
ADDRESS VARCHAR2 (30));

CREATE TABLE BOOK_COPIES
(BOOK_ID NUMBER(4),
BRANCH_ID NUMBER(5),
NO_OF_COPIES INTEGER,
PRIMARY KEY (BOOK_ID, BRANCH_ID),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
FOREIGN KEY (BRANCH_ID) REFERENCES LIBRARY_BRANCH (BRANCH_ID) ON DELETE CASCADE);

CREATE TABLE CARD
(CARD_NO INTEGER PRIMARY KEY);

CREATE TABLE BOOK_LENDING
(BOOK_ID NUMBER(4),
BRANCH_ID NUMBER(5),
CARD_NO NUMBER(6) NOT NULL,
DATE_OUT DATE,
DUE_DATE DATE,
PRIMARY KEY (BOOK_ID, BRANCH_ID, CARD_NO),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
FOREIGN KEY (BRANCH_ID) REFERENCES LIBRARY_BRANCH (BRANCH_ID) ON DELETE CASCADE);