alter_tables.sql

卡耐基的SSD7-Exercise3~~答案部分~~

/*1*/
CREATE TABLE PURCHASE_ORDER4
 (PoNum char(8) NOT NULL,
  Qty integer,
  OrderDate date,
  DueDate date,
  ReceiveDate date,
  PRIMARY KEY(PoNum)
);

CREATE TABLE SUPPLIER4
(Supplier_Id char(8) NOT NULL,
 Name char(8),
 Address char(10),
 PRIMARY KEY (Supplier_Id)
);

CREATE TABLE READ_BY4
(CallNumber char(8) NOT NULL,
 LibId char(6) NOT NULL,
 TimesRead integer,
 PRIMARY KEY (CallNumber, LibId),
 FOREIGN KEY (CallNumber) REFERENCES TITLE4 (CallNumber),
 FOREIGN KEY (LibId) REFERENCES MEMBER4(LibId)
);

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),
 FOREIGN KEY (PoNum) REFERENCES PURCHASE_ORDER4 (PoNum),
 FOREIGN KEY (Supplier_Id) REFERENCES SUPPLIER4 (Supplier_Id)
);


/*2*/
ALTER TABLE TITLE4 ADD UNIQUE(ISBN);
ALTER TABLE TITLE4 ALTER COLUMN isbn SET NOT NULL;
ALTER TABLE TITLE4 ADD 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 Join_date date;
ALTER TABLE MEMBER4 ADD Gender VARCHAR(1); 


/*5*/
ALTER TABLE READ_BY4 ADD CHECK(timeread >= 0);