Database Schema.txt

CREATE TABLE PERSON
(
	"ID" INTEGER not null PRIMARY KEY,
	"PNAME" VARCHAR(30) not null,
	"PASSWORD" VARCHAR(10) not null,
	"ADDRESS" VARCHAR(30) not null,
	"PHONE_NO" INTEGER not null
);


CREATE TABLE BOOK
(
	"ID" INTEGER not null PRIMARY KEY,
	"TITLE" VARCHAR(30) not null,
	"AUTHOR" VARCHAR(20) not null,
	"SUBJECT" VARCHAR(20) not null,
	"IS_ISSUED" BOOLEAN not null 

);

CREATE TABLE STAFF
(
    "S_ID" INTEGER not null PRIMARY KEY,
    "TYPE" VARCHAR(10) not null,
    "SALARY" DOUBLE
);

CREATE TABLE CLERK
(
	"C_ID" INTEGER not null PRIMARY KEY,
	"DESK_NO" INTEGER not null
);

CREATE TABLE LIBRARIAN
(
	"L_ID" INTEGER not null PRIMARY KEY,
	"OFFICE_NO" INTEGER not null
);


CREATE TABLE BORROWER
(
	"B_ID" INTEGER not null PRIMARY KEY

);

CREATE TABLE BORROWED_BOOK
(
	"BOOK" INTEGER not null PRIMARY KEY,
	"BORROWER" INTEGER not null
);

CREATE TABLE LOAN
(
	"L_ID" INTEGER not null PRIMARY KEY,
	"BORROWER" INTEGER not null,
	"BOOK" 	INTEGER not null,
	"ISSUER" INTEGER not null,
	"ISS_DATE" TIMESTAMP not null,
	"RECEIVER" INTEGER ,
	"RET_DATE" TIMESTAMP ,
	"FINE_PAID" BOOLEAN	
);

CREATE TABLE ON_HOLD_BOOK
(
	"REQ_ID" INTEGER not null PRIMARY KEY,
	"BOOK" INTEGER not null,
	"BORROWER" INTEGER not null,
	"REQ_DATE" DATE not null
);

----------------------------------------FOREIGN KEY CONSTRAINTS----------------------------

ALTER TABLE BORROWED_BOOK
ADD
FOREIGN KEY (BORROWER)REFERENCES BORROWER(B_ID);


ALTER TABLE LOAN
ADD
FOREIGN KEY (BORROWER)REFERENCES BORROWER(B_ID);

ALTER TABLE LOAN
ADD
FOREIGN KEY (ISSUER)REFERENCES STAFF(S_ID);

ALTER TABLE LOAN
ADD
FOREIGN KEY (RECEIVER)REFERENCES STAFF(S_ID);


ALTER TABLE ON_HOLD_BOOK
ADD
FOREIGN KEY (BORROWER)REFERENCES BORROWER(B_ID);

ALTER TABLE LIBRARIAN
ADD
FOREIGN KEY (L_ID)REFERENCES STAFF(S_ID);

ALTER TABLE CLERK
ADD
FOREIGN KEY (C_ID)REFERENCES STAFF(S_ID);

ALTER TABLE STAFF
ADD
FOREIGN KEY (S_ID)REFERENCES PERSON(ID);

ALTER TABLE BORROWER
ADD
FOREIGN KEY (B_ID)REFERENCES PERSON(ID);

-------------------------------------------------------------