/*
#@author:wangzhesi
#053597
*/

CREATE TABLE TITLE4
 (callnumber  char(8) NOT NULL,
  isbn        char(8),
  year        date,
  name        char(14),
  authorfname char(14),
  authorlname char(14),
  PRIMARY KEY (callnumber)
);

CREATE TABLE MEMBER4
 (libid       char(6) NOT NULL,
  fname       char(14),
  lname       char(14),
  address     char(20),
  phonenumber char(10),
  PRIMARY KEY (libid)
);

CREATE TABLE BOOK4
 (book_id       char(8) NOT NULL,
  edition       char(10),
  status        char(5),
  borrowerid    char(6),
  borrowduedate date,
  callnumber    char(8),
  PRIMARY KEY (book_id)
);

--1:
/*
PURCHASE_ORDER4(PoNum, Qty, OrderDate, DueDate, ReceivedDate);
PK(PoNum)
*/
CREATE TABLE PURCHASE_ORDER4
 (ponum       char(8) NOT NULL,
  qty         integer,
  orderdate   date,
  duedate     date,
  receivedate date,
  PRIMARY KEY(ponum)
);

/*
SUPPLIER4(Supplier_Id, Name, Address);
PK(Supplier_Id)
*/
CREATE TABLE SUPPLIER4
 (supplier_id  char(8) NOT NULL,
  name         char(8),
  address      char(10),
  PRIMARY KEY(supplier_id)
);

/*
READ_BY4(CallNumber, LibId, TimesRead);
PK(CallNumber, LibId)
FK(CallNumber) -> TITLE4(CallNumber)
FK(LibId) -> MEMBER4(LibId)
*/
CREATE TABLE READ_BY4
 (callnumber  char(8) NOT NULL,
  libid       char(6) NOT NULL,
  timeread    integer,
  PRIMARY KEY (callnumber,libid)
);

/*
ORDERED4(CallNumber, PoNum, Supplier_Id);
PK(CallNumber, PoNum, Supplier_Id)
FK(CallNumber) -> TITLE4(CallNumber)
FK(PoNum) -> PURCHASE_ORDER4(PoNum)
FK(Supplier_Id) -> SUPPLIER4(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)
); 

--2:
ALTER TABLE TITLE4 ADD CONSTRAINT alterisbn UNIQUE(isbn);
ALTER TABLE TITLE4 ALTER COLUMN isbn SET NOT NULL;
ALTER TABLE TITLE4 ADD CONSTRAINT altername UNIQUE(name);
ALTER TABLE TITLE4 ALTER COLUMN name SET NOT NULL;

--3:
ALTER TABLE BOOK4 ADD FOREIGN KEY (borrowerid) REFERENCES MEMBER4(libid);
ALTER TABLE BOOK4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);
ALTER TABLE READ_BY4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);
ALTER TABLE READ_BY4 ADD FOREIGN KEY (libid) REFERENCES MEMBER4(libid);
ALTER TABLE ORDERED4 ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);
ALTER TABLE ORDERED4 ADD FOREIGN KEY (ponum) REFERENCES PURCHASE_ORDER4(ponum);
ALTER TABLE ORDERED4 ADD FOREIGN KEY (supplier_id) REFERENCES SUPPLIER4(supplier_id);

--4:
ALTER TABLE MEMBER4 ADD COLUMN Join_date date;
ALTER TABLE MEMBER4 ADD COLUMN Gender char(1) CHECK(Gender IN('M','F'));

--5:
ALTER TABLE READ_BY4 ADD CHECK(timeread >= 0);