DROP TABLE Author;
DROP TABLE Book;
DROP TABLE HOLD;
DROP TABLE Dependent;
DROP TABLE Title;
DROP TABLE Member;
DROP TABLE Section;
DROP TABLE Librarian;

CREATE TABLE Title(
CallNumber VARCHAR(40) NOT NULL,
Name VARCHAR(200),
ISBN VARCHAR(40),
Year DATE,
Publisher VARCHAR(80),
PRIMARY KEY (CallNumber),
UNIQUE (ISBN));

CREATE TABLE Author(
CallNumber VARCHAR(40) NOT NULL,
Fname VARCHAR(40) NOT NULL,
MI VARCHAR(10),
Lname VARCHAR(40) NOT NULL,
PRIMARY KEY (CallNumber, Fname, Lname),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));

CREATE TABLE Member(
MemNo integer NOT NULL,
DriverLicState VARCHAR(20),
DriverLicNo VARCHAR(40),
Fname VARCHAR(20),
MI VARCHAR(10),
Lname VARCHAR(20),
Address VARCHAR(250),
PhoneNumber VARCHAR(15),
PRIMARY KEY (MemNo));

CREATE TABLE HOLD(
MemNo integer NOT NULL,
CallNumber VARCHAR(40) NOT NULL,
HoldDate DATE,
PRIMARY KEY (MemNo, CallNumber),
FOREIGN KEY (MemNo) REFERENCES Member(MemNo),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));

CREATE TABLE Librarian(
SSN bigint NOT NULL,
Name VARCHAR(80),
Address VARCHAR(250),
Salary NUMERIC(9,2),
Gender CHAR(1),
Birthday DATE,
SuperSSN bigint,
Section integer,
PRIMARY KEY (SSN),
FOREIGN KEY (SuperSSN) REFERENCES LIBRARIAN(SSN));

CREATE TABLE Section(
SectNo integer NOT NULL,
Name VARCHAR(80),
HeadSSN bigint,
PRIMARY KEY (SectNo),
FOREIGN KEY (HeadSSN) REFERENCES Librarian(SSN));

ALTER TABLE Librarian
ADD CONSTRAINT LibSection FOREIGN KEY (Section) REFERENCES Section(SectNo);

CREATE TABLE Dependent(
LibSSN bigint NOT NULL,
Name VARCHAR(40) NOT NULL,
Birthday DATE,
Kinship VARCHAR(40),
PRIMARY KEY (LibSSN, Name),
FOREIGN KEY (LibSSN) REFERENCES Librarian(SSN));

CREATE TABLE Book(
Book_ID integer NOT NULL,
Edition VARCHAR(80),
BorrowerMemNo bigint,
BorrowDueDate DATE,
CallNumber VARCHAR(40),
LibCheck bigint,
PRIMARY KEY (Book_ID),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber),
FOREIGN KEY (BorrowerMemNo) REFERENCES Member(MemNo),
FOREIGN KEY (LibCheck) REFERENCES Librarian(SSN));

INSERT INTO Title VALUES ('Call123', 'Iliad', 'ISBN123', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Homer Publishing');
INSERT INTO Title VALUES ('Call124', 'Odyssey', 'ISBN124', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Homer Publishing');
INSERT INTO Title VALUES ('Call125', 'Database Systems', 'ISBN125', TO_DATE('1999/01/01', 'YYYY/MM/DD'), 'AWL');
INSERT INTO Title VALUES ('Call126', 'Financial Accounting', 'ISBN126', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'McGrawHill');
INSERT INTO Title VALUES ('Call127', 'Second World War', 'ISBN127', TO_DATE('1986/05/01', 'YYYY/MM/DD'), 'McGrawHill');
INSERT INTO Title VALUES ('Call128', 'Networks', 'ISBN128', TO_DATE('1986/05/01', 'YYYY/MM/DD'), 'AWL');
INSERT INTO Title VALUES ('Call129', 'Pitt Roads', 'ISBN129', TO_DATE('1986/05/01', 'YYYY/MM/DD'), 'AWL');

INSERT INTO Member VALUES (123, 'PA', '123', 'John', '', 'Summers', '4615 Forbes Ave, Pittsburgh, PA 15213', '412-268-0001');
INSERT INTO Member VALUES (124, 'GA', '124', 'Jon', '', 'Butterworth', '10 Fifth Ave, Atlanta, GA 30332', '404-894-0001');
INSERT INTO Member VALUES (125, 'PA', '125', 'Susan', 'B', 'Carlione', '4600 Verona Road, Pittsburgh, PA 15217', '412-200-0001');
INSERT INTO Member VALUES (126, 'NC', '126', 'Mohammed', '', 'Ismail', '250 Peachtree Street, Salem, NC 15213', '421-268-0001');
INSERT INTO Member VALUES (127, 'PA', '127', 'Asterio', '', 'Tanaka', '415 Craig Street, Pittsburgh, PA 15213', '412-220-0001');

INSERT INTO HOLD VALUES (123, 'Call123', TO_DATE('2000-10-10', 'YYYY-MM-DD'));


INSERT INTO Author VALUES ('Call123', 'Hello', '', 'Homer');
INSERT INTO Author VALUES ('Call124', 'Hello', '', 'Homer');
INSERT INTO Author VALUES ('Call125', 'Jack', '', 'Collins');
INSERT INTO Author VALUES ('Call126', 'Jack', '', 'Collins');
INSERT INTO Author VALUES ('Call127', 'Winston', '', 'Churchill');
INSERT INTO Author VALUES ('Call127', 'John', '', 'Keegan');
INSERT INTO Author VALUES ('Call128', 'Jeff', '', 'Tanenbaum');
INSERT INTO Author VALUES ('Call129', 'Carlos', '', 'Tanaka');

INSERT INTO Librarian VALUES (201, 'Ashoka Savasere', '4615 Forbes Ave, Pittsburgh, PA 15213', 40000, 'F', TO_DATE('1972-06-02', 'YYYY-MM-DD'), NULL, NULL);
INSERT INTO Librarian VALUES (202, 'Alfred Watkins', '4615 Forbes Ave, Pittsburgh, PA 15213', 40000, 'M', TO_DATE('1972-07-02', 'YYYY-MM-DD'), NULL, NULL);
INSERT INTO Librarian VALUES (203, 'Yong-Chul Oh', '4600 Forbes Ave, Pittsburgh, PA 15213', 40000, 'M', TO_DATE('1960-06-02', 'YYYY-MM-DD'), NULL, NULL);
INSERT INTO Librarian VALUES (204, 'Shamkant Navathe', '4615 Forbes Ave, Pittsburgh, PA 15213', 40000, 'M', TO_DATE('1975-06-02', 'YYYY-MM-DD'), NULL, NULL);

INSERT INTO Section VALUES (1, 'CheckOut', 201);
INSERT INTO Section VALUES (2, 'Reference', 204);

UPDATE Librarian Set Section = 1
WHERE SSN < 204;
UPDATE Librarian Set Section = 2
WHERE SSN = 204;

INSERT INTO Book VALUES (123, '1', 123, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call123', 202);
INSERT INTO Book VALUES (223, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call123', 201);
INSERT INTO Book VALUES (923, '1', 123, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call123', 202);
INSERT INTO Book VALUES (124, '1', 124, TO_DATE('2000-06-09', 'YYYY-MM-DD'), 'Call124', 201);
INSERT INTO Book VALUES (224, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call124', 201);
INSERT INTO Book VALUES (125, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call125', 201);
INSERT INTO Book VALUES (225, '1', NULL, NULL, 'Call125', NULL);
INSERT INTO Book VALUES (126, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call126', 201);
INSERT INTO Book VALUES (226, '1', 124, TO_DATE('2000-06-09', 'YYYY-MM-DD'), 'Call126', 202);
INSERT INTO Book VALUES (326, '1', 124, TO_DATE('2000-06-09', 'YYYY-MM-DD'), 'Call126', 202);
INSERT INTO Book VALUES (127, '1', NULL, NULL, 'Call127', NULL);
INSERT INTO Book VALUES (128, '1', 125, TO_DATE('2000-11-11', 'YYYY-MM-DD'), 'Call128', 201);
INSERT INTO Book VALUES (228, '1', 126, TO_DATE('2000-10-10', 'YYYY-MM-DD'), 'Call128', 202);
INSERT INTO Book VALUES (129, '1', 123, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call129', 202);
INSERT INTO Book VALUES (229, '1', 125, TO_DATE('2000-12-12', 'YYYY-MM-DD'), 'Call129', 202);

INSERT INTO Dependent VALUES (203, 'Luc Whang', TO_DATE('1998-11-11', 'YYYY-MM-DD'), 'Son');