.echo "Databases book Databases and SQL are being built. Please wait."

DROP TABLE REPLACEMENT;
DROP TABLE EMPLOYEE;
DROP TABLE LOCATION;
DROP TABLE COPY;
DROP TABLE BOOKSECTION;
DROP TABLE BOOK;
DROP TABLE BORROWER;


CREATE TABLE LOCATION
	(LOCNAME CHAR(10) NOT NULL, 
	 BRANCH CHAR(8), 
	 CITY CHAR(10),
	 CONSTRAINT LCTNPK PRIMARY KEY (LOCNAME));
CREATE TABLE EMPLOYEE 
	(ENR SMALLINT NOT NULL, 
	 ENAME CHAR(8), 
	 DEPARTMENT CHAR(12), 
	 FNAME CHAR(12), 
	 SALARY SMALLINT, 
	 LOCNAME CHAR(10),
	 CONSTRAINT EPLYPK PRIMARY KEY (ENR),
	 CONSTRAINT EMPLFK FOREIGN KEY (LOCNAME) REFERENCES LOCATION);
CREATE TABLE REPLACEMENT 
	(REPLACED SMALLINT NOT NULL, 
	 SUBSTITUTE SMALLINT NOT NULL,
	 CONSTRAINT RMTPK PRIMARY KEY (REPLACED,SUBSTITUTE),
	 CONSTRAINT RPLNFK FOREIGN KEY (REPLACED) REFERENCES EMPLOYEE,
	 CONSTRAINT SBSTFK FOREIGN KEY (SUBSTITUTE) REFERENCES EMPLOYEE);
CREATE TABLE BORROWER 
	(BNAME CHAR(8) NOT NULL, 
	 BTHDATE CHAR(10), 
	 ADDRESS CHAR(12), 
	 TOWN CHAR(8),
	 CONSTRAINT BTWPK PRIMARY KEY (BNAME));
CREATE TABLE BOOK 
	(BOOKNR SMALLINT NOT NULL, 
	 BOOKNAME CHAR(14), 
	 CATEGORY CHAR(10),
	 CONSTRAINT BKPK PRIMARY KEY (BOOKNR));
CREATE TABLE COPY 
	(BOOKNR SMALLINT NOT NULL, 
	 SERIALNR SMALLINT,
	 PURCHDATE CHAR(10), 
	 LOANDATE CHAR(10), 
	 BNAME CHAR(8),
	 CONSTRAINT CPYPK PRIMARY KEY (BOOKNR,SERIALNR),
	 CONSTRAINT CPBKFK FOREIGN KEY (BOOKNR) REFERENCES BOOK,
	 CONSTRAINT CPBRFK FOREIGN KEY (BNAME) REFERENCES BORROWER);
CREATE TABLE BOOKSECTION 
	(PARTNR SMALLINT NOT NULL, 
	 SECTIONNR SMALLINT,
	 SECTIONNAME CHAR(14), 
	 INPARTNR SMALLINT, 
	 INBOOKNR SMALLINT,
	 CONSTRAINT BSNPK PRIMARY KEY (PARTNR),
	 CONSTRAINT INBSFK FOREIGN KEY (INPARTNR) REFERENCES BOOKSECTION,
	 CONSTRAINT INBKFK FOREIGN KEY (INBOOKNR) REFERENCES BOOK);


INSERT INTO LOCATION   (LOCNAME,BRANCH,CITY)
VALUES ('FASTBITE','CATERING','KINGSTON');
INSERT INTO LOCATION   (LOCNAME,BRANCH,CITY)
VALUES ('FONG','CATERING', 'KINGSTON');
INSERT INTO LOCATION   (LOCNAME,BRANCH,CITY)
VALUES ('MADISON','HOTEL','BRENTWOOD');
INSERT INTO LOCATION   (LOCNAME,BRANCH,CITY)
VALUES ('OKOTEL','HOTEL','BRENTWOOD');
INSERT INTO LOCATION   (LOCNAME,BRANCH,CITY)
VALUES ('RHODOS', 'CATERING', 'LAKEWOOD');
INSERT INTO LOCATION   (LOCNAME,BRANCH,CITY)
VALUES ('SEAVIEW', 'HOTEL', 'FREEPORT');



INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (1, 'AUSTIN', 'MANAGEMENT', 'DIRECTOR',8000, 'MADISON');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (2, 'BENSON', 'RECEPTION', 'ENTERTAINER',18000, 'SEAVIEW');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (3, 'BRADFORD', 'ROOMSERVICE', 'CLERK',1400, 'SEAVIEW');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (4, 'COOPER', 'RECEPTION', 'CLERK',800, 'OKOTEL');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (5, 'DAVIS', 'MANAGEMENT', 'DIRECTOR',8000, 'OKOTEL');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (6, 'EDWARDS', 'CHIPS', 'DIRECTOR',1200, 'FASTBITE');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (7, 'EDWARDS', 'MANAGEMENT', 'SECRETARY',9000, 'MADISON');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (8, 'HUNT', 'MANAGEMENT', 'DIRECTOR',9000, 'FONG');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (9, 'JOHNSON', 'ROOMSERVICE', 'CLERK',1600, 'MADISON');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (10, 'KEPLER', 'KITCHEN', 'COOK',673, 'RHODOS');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (11, 'LEE', 'KITCHEN', 'COOK',4500, 'FONG');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (12, 'LEE', 'KITCHEN', 'COOK',800, 'FONG');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (13, 'NORRIS', 'RECEPTION', 'ENTERTAINER',6000, 'MADISON');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (14, 'TAYLOR', 'RESTAURANT', 'WAITER',3200, 'RHODOS');
INSERT INTO EMPLOYEE    (ENR,ENAME,DEPARTMENT,FNAME,SALARY,LOCNAME)
VALUES (15, 'WEBB', 'MANAGEMENT', 'DIRECTOR',8000, 'SEAVIEW');



INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (1,5);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (2,13);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (3,2);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (4,5);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (4,9);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (4,14);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (5,7);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (6,11);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (6,12);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (9,13);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (9,4);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (10,11);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (10,14);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (11,12);
INSERT INTO REPLACEMENT   (REPLACED, SUBSTITUTE)
VALUES (12,11);



INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('BENSON', '1966-06-19', '2 CHURCHHILL', 'SHELTON');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('BURTON', '1984-09-23', '17 BOULEVARD', 'HAMDEN');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('EATON', '1978-01-14', '18 PARKLANE', 'HAMDON');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('GRAY', '1994-04-16','58 TOLLROAD', 'MILFORD');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('HART', '1956-03-21', '34 KINGSROAD', 'SHELTON');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('ROSS', '1991-07-12', '10 FLOWERSTREET', 'SHELTON');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('SMITH', '1988-08-13', '79 HUDSONSTREET', 'SHELTON');
INSERT INTO BORROWER   (BNAME,BTHDATE,ADDRESS,TOWN)
VALUES ('WOLF', '1995-09-17', '9 COUNTRY DRIVE', 'HAMDEN');



INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (1, 'SEA TRAVEL', 'NOVEL');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (2, 'AT THE LAKE', 'SPORT');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (3, 'DOCTER X', 'NOVEL');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (4, 'FISHING', 'SPORT');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (5, 'AVIATION', 'TECHNICS');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (6, 'SUMMER FOLLY', 'NOVEL');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (7, 'ADVENTURE', 'NOVEL');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (8, 'SAILING', 'SPORT');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (9, 'NURSE ANN', 'NOVEL');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (10, 'UNDER REPAIR', 'TECHNICS');
INSERT INTO BOOK    (BOOKNR,BOOKNAME,CATEGORY)
VALUES (11, 'ELEKTRONICS', 'TECHNICS');



INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (1,1, '2017-01-12',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (1,2, '2017-01-12', '2017-04-29', 'WOLFF');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (2,1, '2017-01-20', '2017-04-28', 'BURTON');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (3,1, '2017-01-20', NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (4,1, '2017-01-20', '2017-04-28', 'BURTON');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (4,2, '2017-02-23',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (4,3, '2017-02-23',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (5,1, '2017-01-20', '2017-04-28', 'GRAY');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (6,1, '2017-01-20',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (6,2, '2017-02-23',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (6,3, '2017-02-23', '2017-05-15', 'EATON');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (7,1, '2017-01-20',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (7,2, '2017-04-14',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (8,1, '2017-01-20',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (9,1, '2017-01-23', '2017-05-18', 'HART');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (10,1, '2017-01-20', '2017-05-20', 'BURTON');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (10,2, '2017-04-15', '2017-04-28', 'GRAY');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (10,3, '2017-04-07', '2017-05-19', 'BENSON');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (11,1, '2017-05-04', '2017-05-22', 'GRAY');
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (11,2, '2017-05-04',NULL,NULL);
INSERT INTO COPY    (BOOKNR,SERIALNR,PURCHDATE,LOANDATE,BNAME)
VALUES (11,3, '2017-05-04', '2017-05-22', 'BENSON');




INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (1,1, 'THE ROUND',NULL,3);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (2,2, 'CONCLUSION',NULL,3);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (3,1, 'INTRODUCTION',NULL,5);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (4,2, 'DRAWING',NULL,5);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (5,3, 'BUILDING',NULL,5);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (6,4, 'FLYING',NULL,5);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (7,1, 'MEASURING',4,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (8,2, 'ON PAPER',4,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (9,1, 'LINNEN',5,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (10,2, 'SOWING',5,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (11,3, 'STAPLING',5,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (12,1, 'TOOLS',NULL,10);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (13,2, 'MATERIALS',NULL,10);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (14,3, 'MAKING IT',NULL,10);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (15,1, 'THE SAW',12,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (16,2, 'THE HAMMER',12,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (17,1, 'CLOTHING',14,NULL);
INSERT INTO BOOKSECTION    (PARTNR,SECTIONNR,SECTIONNAME,INPARTNR,INBOOKNR)
VALUES (18,2, 'BANDAGE',14,NULL);



COMMIT;