CREATE TABLE read_by4
 (callnumber  char(8) NOT NULL,
  libid       char(6) NOT NULL,
  timesread   int,
  PRIMARY KEY (callnumber, libid),
  FOREIGN KEY (callnumber) REFERENCES title4(callnumber)
		ON DELETE NO ACTION ON UPDATE CASCADE,
  FOREIGN KEY (libid) REFERENCES member4(libid)
		ON DELETE NO ACTION ON UPDATE CASCADE
);

CREATE TABLE purchase_order4
 (ponum  char(8) NOT NULL,
  qty    int NOT NULL,
  orderdate  date,
  duedate date,
  receiveddate  date,
  PRIMARY KEY (poNum)
);

CREATE TABLE supplier4
 (supplier_id  char(8) NOT NULL,
  name          char(20) NOT NULL,
  address       char(20),
  PRIMARY KEY (supplier_id)
);

CREATE TABLE ordered4
 (callnumber  char(8) NOT NULL,
  ponum  char(8) NOT NULL,
  supplier_id  char(8) NOT NULL,
  PRIMARY KEY (callnumber, ponum, supplier_id),

    FOREIGN KEY (callnumber) REFERENCES title4(callnumber)
		ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY (ponum) REFERENCES purchase_order4(ponum)
		ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES supplier4(supplier_id)
		ON DELETE NO ACTION ON UPDATE CASCADE
);



ALTER TABLE title4
ADD CHECK (isbn IS NOT NULL),
ADD CHECK (name IS NOT NULL),
ADD CONSTRAINT alternate_key UNIQUE (isbn, name);

ALTER TABLE book4
ADD FOREIGN KEY (borrowerId) REFERENCES member4(libid) 
	ON DELETE NO ACTION ON UPDATE CASCADE,
ADD FOREIGN KEY (callnumber) REFERENCES title4(callnumber) 
	ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE member4
ADD COLUMN join_date date,
ADD COLUMN gender char(1);


ALTER TABLE read_by4
ADD CHECK(timesread >= 0);

DELETE FROM READ_BY4;
DELETE FROM BOOK4;
DELETE FROM MEMBER4;
DELETE FROM ORDERED4;
DELETE FROM PURCHASE_ORDER4;
DELETE FROM TITLE4;
DELETE FROM SUPPLIER4;

/*
#
#                        TITLE4
#_________________________________________________________
#  callnumber  isbn  year  name  authorfname authorlname
#_________________________________________________________
*/
INSERT INTO TITLE4 VALUES ('AB123456', 'CD123456',TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Pitt Roads', 'Aon', 'Arad');
INSERT INTO TITLE4 VALUES ('AB123457', 'CD123457',TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'UPitt Maps', 'Bon', 'Brad');
INSERT INTO TITLE4 VALUES ('AB123458', 'CD123458', TO_DATE('1995/01/01', 'YYYY/MM/DD'), 'UPenn Maps', 'Con', 'Crad');
INSERT INTO TITLE4 VALUES ('AB123459', 'CD123459', TO_DATE('1994/01/01', 'YYYY/MM/DD'), 'Penn Roads', 'Don', 'Drad');
INSERT INTO TITLE4 VALUES ('AB123460', 'CD123460', TO_DATE('1993/01/01', 'YYYY/MM/DD'), 'Intro to C', 'Eon', 'Erad');
INSERT INTO TITLE4 VALUES ('AB123461', 'CD123461', TO_DATE('1997/01/01', 'YYYY/MM/DD'), 'Fortan -77', 'Fon', 'Frad');
INSERT INTO TITLE4 VALUES ('AB123462', 'CD123462', TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'Quick Basic', 'Stella', 'Potter');
INSERT INTO TITLE4 VALUES ('AB123463', 'CD123463', TO_DATE('1995/01/01', 'YYYY/MM/DD'), 'Visit India',  'Ravi', 'Kumar');
INSERT INTO TITLE4 VALUES ('AB123464', 'CD123464', TO_DATE('1994/01/01', 'YYYY/MM/DD'), 'Turbo Pascal', 'Angus', 'Emily');
INSERT INTO TITLE4 VALUES ('AB123465', 'CD123465', TO_DATE('1993/01/01', 'YYYY/MM/DD'), 'Indian Songs', 'Lata', 'Mangeskar');

/*
#
#                          MEMBER4  
#_______________________________________________________________
#libid   fname  lname  address  phonenumber  join_date  Gender
#_______________________________________________________________
*/
INSERT INTO MEMBER4 VALUES ('A%B124', 'Sam', 'Son', 'Pgh 15213', '4126457890', TO_DATE('1997/12/01', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A%B125', 'Sama', 'Sona', 'Pgh 15213', '4126347891', TO_DATE('1997/12/15', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('A%B126', 'Cam', 'Con', 'Penn 10214', '6126457780', TO_DATE('1995/06/01', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A%B127', 'Cama', 'Cona', 'Penn 10214', '6126467891', TO_DATE('1995/05/01', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('AxB127', 'Camax', 'Conax', 'PennX 10214', '6126467892', TO_DATE('1995/05/01', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('A&B124', 'Dan', 'Don', 'Pgh 15215', '4126451190', TO_DATE('1990/11/15', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A&B125', 'Dana', 'Dona', 'Pgh 15215', '4126433890', TO_DATE('1990/12/15', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('A&B126', 'Ram', 'Shyam', 'Penn 10215', '6126454690', TO_DATE('1990/05/10', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('A&B127', 'Rama', 'Shyama', 'Penn 10215', '6126489890', TO_DATE('1990/01/10', 'YYYY/MM/DD'), 'F');
INSERT INTO MEMBER4 VALUES ('C%D124', 'San', 'Don', 'Pgh 15220', '4126457342', TO_DATE('1990/01/20', 'YYYY/MM/DD'), 'M');
INSERT INTO MEMBER4 VALUES ('C%D125', 'Sana', 'Dona', 'Penn 10220', '6126457341', TO_DATE('1990/05/20', 'YYYY/MM/DD'), 'F');


/*
#
#                             BOOK4
#__________________________________________________________________
#  bookid  edition  status  borrowerid  borrowduedate  callnumber
#__________________________________________________________________
*/
INSERT INTO BOOK4 VALUES ('Q76.97.0', 'first', 'good', 'A%B124', TO_DATE('1998/05/05', 'YYYY/MM/DD'), 'AB123456');
INSERT INTO BOOK4 VALUES ('Q76.97.1', 'third', 'good', 'A%B125', TO_DATE('1998/06/10', 'YYYY/MM/DD'), 'AB123457');
INSERT INTO BOOK4 VALUES ('Q76.97.2', 'first', 'ok', 'A%B126', TO_DATE('1998/07/15', 'YYYY/MM/DD'), 'AB123458');
INSERT INTO BOOK4 VALUES ('Q76.97.3', 'third', 'ok', 'A%B127', TO_DATE('1998/08/20', 'YYYY/MM/DD'), 'AB123459');
INSERT INTO BOOK4 VALUES ('Q76.97.4', 'first', 'good', 'A&B124', TO_DATE('1998/05/20', 'YYYY/MM/DD'), 'AB123460');
INSERT INTO BOOK4 VALUES ('Q76.97.5', 'first', 'good', 'A&B125', TO_DATE('1998/06/15', 'YYYY/MM/DD'), 'AB123461');
INSERT INTO BOOK4 VALUES ('Q76.97.6', 'third', 'good', 'A&B126', TO_DATE('1998/07/10', 'YYYY/MM/DD'), 'AB123462');
INSERT INTO BOOK4 VALUES ('Q76.97.7', 'third', 'good', 'A&B127', TO_DATE('1998/08/05', 'YYYY/MM/DD'), 'AB123463');
INSERT INTO BOOK4 VALUES ('Q76.97.8', 'second', 'bad', 'A%B124', TO_DATE('1998/08/25', 'YYYY/MM/DD'), 'AB123464');
INSERT INTO BOOK4 VALUES ('Q76.97.9', 'second', 'bad', 'A%B125', TO_DATE('1998/08/25', 'YYYY/MM/DD'), 'AB123465');

/*
#
#               PURCHASE_ORDER4
#_________________________________________________
#  ponum  qty  orderdate  duedate  receiveddate
#_________________________________________________
*/
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100000', 5, TO_DATE('1990/05/01', 'YYYY/MM/DD'), TO_DATE('1990/06/01', 'YYYY/MM/DD'), TO_DATE('1990/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100001', 5, TO_DATE('1990/06/01', 'YYYY/MM/DD'), TO_DATE('1990/07/01', 'YYYY/MM/DD'), TO_DATE('1990/06/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100002', 5, TO_DATE('1990/07/01', 'YYYY/MM/DD'), TO_DATE('1990/08/01', 'YYYY/MM/DD'), TO_DATE('1990/07/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100003', 15, TO_DATE('1997/01/01', 'YYYY/MM/DD'), TO_DATE('1997/03/01', 'YYYY/MM/DD'), TO_DATE('1997/05/01', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100004', 5, TO_DATE('1997/02/01', 'YYYY/MM/DD'), TO_DATE('1997/05/01', 'YYYY/MM/DD'), TO_DATE('1997/06/01', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100005', 2, TO_DATE('1998/05/01', 'YYYY/MM/DD'), TO_DATE('1998/06/01', 'YYYY/MM/DD'), TO_DATE('1998/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100006', 2, TO_DATE('1998/05/01', 'YYYY/MM/DD'), TO_DATE('1998/07/01', 'YYYY/MM/DD'), TO_DATE('1998/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100007', 2, TO_DATE('1998/05/01', 'YYYY/MM/DD'), TO_DATE('1998/08/01', 'YYYY/MM/DD'), TO_DATE('1998/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100008', 10, TO_DATE('1995/05/01', 'YYYY/MM/DD'), TO_DATE('1995/06/01', 'YYYY/MM/DD'), TO_DATE('1995/05/15', 'YYYY/MM/DD'));
INSERT INTO PURCHASE_ORDER4 VALUES ('PO100009', 10, TO_DATE('1995/05/01', 'YYYY/MM/DD'), TO_DATE('1995/06/01', 'YYYY/MM/DD'), TO_DATE('1995/05/15', 'YYYY/MM/DD'));

/*
#
#         SUPPLIER4
#______________________________
#  supplier_id  name  address
#______________________________
*/
INSERT INTO SUPPLIER4 VALUES ('SP000001', 'SP PITT', 'Pitt 15260');
INSERT INTO SUPPLIER4 VALUES ('SP000002', 'SP PITT', 'Pitt 15270');
INSERT INTO SUPPLIER4 VALUES ('SP000003', 'SP PENN', 'Penn 10260');
INSERT INTO SUPPLIER4 VALUES ('SP000004', 'SP PENN', 'Penn 10270');
INSERT INTO SUPPLIER4 VALUES ('SP000005', 'SP BRAD', 'Brad 20260');

/*
#
#            READ_BY4
#_________________________________
#  callnumber  libid  timesread
#_________________________________
*/
INSERT INTO READ_BY4 VALUES ('AB123456','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123457','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123458','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123459','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B124', 2);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123462','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123463','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123464','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123465','A%B124', 5);
INSERT INTO READ_BY4 VALUES ('AB123456','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123457','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123458','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123459','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B125', 5);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123462','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123463','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123464','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123465','A%B125', 10);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123462','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123463','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123464','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123465','A%B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123460','A%B127', 2);
INSERT INTO READ_BY4 VALUES ('AB123461','A%B127', 2);
INSERT INTO READ_BY4 VALUES ('AB123462','A&B124', 1);
INSERT INTO READ_BY4 VALUES ('AB123463','A&B124', 1);
INSERT INTO READ_BY4 VALUES ('AB123462','A&B125', 1);
INSERT INTO READ_BY4 VALUES ('AB123463','A&B125', 1);
INSERT INTO READ_BY4 VALUES ('AB123464','A&B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123465','A&B126', 1);
INSERT INTO READ_BY4 VALUES ('AB123464','A&B127', 1);
INSERT INTO READ_BY4 VALUES ('AB123465','A&B127', 1);
INSERT INTO READ_BY4 VALUES ('AB123456','C%D124', 2);
INSERT INTO READ_BY4 VALUES ('AB123457','C%D124', 2);
INSERT INTO READ_BY4 VALUES ('AB123456','C%D125', 2);
INSERT INTO READ_BY4 VALUES ('AB123457','C%D125', 2);

/*
#
#             ORDERED4
#__________________________________
#  callnumber  ponum  supplier_id
#__________________________________
*/
INSERT INTO ORDERED4 VALUES ('AB123456','PO100000','SP000001');
INSERT INTO ORDERED4 VALUES ('AB123456','PO100001','SP000002');
INSERT INTO ORDERED4 VALUES ('AB123456','PO100002','SP000003');
INSERT INTO ORDERED4 VALUES ('AB123457','PO100003','SP000001');
INSERT INTO ORDERED4 VALUES ('AB123458','PO100004','SP000002');
INSERT INTO ORDERED4 VALUES ('AB123459','PO100005','SP000002');
INSERT INTO ORDERED4 VALUES ('AB123459','PO100006','SP000003');
INSERT INTO ORDERED4 VALUES ('AB123459','PO100007','SP000004');
INSERT INTO ORDERED4 VALUES ('AB123460','PO100008','SP000001');
INSERT INTO ORDERED4 VALUES ('AB123460','PO100009','SP000002');