alter_tables.sql

ssd7练习三答案 仅供参考 如有不足 多多包涵

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

/******CREATE TABLE SUPPLIER4*****/
CREATE TABLE SUPPLIER4
 (Supplier_Id   char(8) NOT NULL,
  Name          VARCHAR(20),
  Address       char(20),
  PRIMARY KEY (Supplier_Id)
);

/******CREATE TABLE READ_BY4*****/
CREATE TABLE READ_BY4
 (CallNumber    char(8) NOT NULL,
  LibId         char(6),
  TimesRead     integer,
  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 ORDERED4*****/
CREATE TABLE ORDERED4
 (CallNumber    char(8) NOT NULL,
  PoNum       char(8),
  Supplier_Id    char(8),
  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
);

/*
#
#Add the constraint that ISBN and Name fields are alternate keys in the TITLE4 table
#and that they cannot take a NULL value. 
#
*/
ALTER TABLE TITLE4 
  ALTER COLUMN isbn SET NOT NULL,
  ALTER COLUMN name SET NOT NULL,
  /*ADD UNIQUE(isbn),
  ADD UNIQUE(name),*/
  ADD CONSTRAINT alternate_key UNIQUE (isbn, name);
  
/*
#
#Add referential integrity constraints as specified in all the tables. 
#
*/
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;
  
/*
#
#Add the columns "Join_date" and "Gender" to the MEMBER4 relation. 
#
*/
ALTER TABLE MEMBER4
  ADD Join_date date,
  ADD Gender char(1);

/*
#
#Specify that the column "TimesRead" in READ_BY4 relation cannot have negative values.
#
*/
ALTER TABLE READ_BY4 
  ADD CHECK(TimesRead>=0);