alter_tables.sql

卡耐基 梅隆大学软件工程 第七门课程数据库管理系统ssd7 练习3

CREATE TABLE PURCHASE_ORDER4
 (ponum		char(8),
  qty		integer,
  orderdate 	date,
  duedate 	date,
  receiveddate	date,
  PRIMARY KEY (ponum)
);

CREATE TABLE SUPPLIER4
 (supplier_id	char(8),
  name 		char(7),
  address	char(10),
  PRIMARY KEY (supplier_id)
);

CREATE TABLE READ_BY4
 (callnumber	char(8),
  libid 	char(6),
  timesread     integer,
  PRIMARY KEY (callnumber,libid),
  FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber),
  FOREIGN KEY (libid) REFERENCES MEMBER4(libid)
);

CREATE TABLE ORDERED4
 (callnumber	char(8),
  ponum		char(8),
  supplier_id 	char(8),
  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)
);
 
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), 
   ADD FOREIGN KEY (callnumber) REFERENCES TITLE4(callnumber);    
   
ALTER TABLE MEMBER4   
   ADD COLUMN Join_date date,   
   ADD COLUMN Gender char(1);   
   
ALTER TABLE READ_BY4   
   ADD CHECK(timesread >= 0);