alter_tables.sql

ssd7 ex3 答案

1.
CREATE TABLE read_by4
(
  callnumber char(8) ,
  libid char(6) ,
  timesreaf int,
  PRIMARY KEY (callnumber, libid));
CREATE TABLE purchase_order4
 (ponum        char(10),
  qty          integer,
  orderdate    date,
  duedate      date,
  receiveddate date,
  PRIMARY KEY (ponum)
);
CREATE TABLE supplier4
 (supplier_id char(8),
  name        char(20),
  address     char(50),
  PRIMARY KEY (supplier_id)
);
CREATE TABLE ORDERED4
 (callnumber  char(8), 
  ponum       char(10),
  supplier_id char(10),
  PRIMARY KEY(callnumber, ponum, supplier_id));











2.
 ALTER TABLE title4
  ADD CONSTRAINT alterisbn UNIQUE (isbn);
 ALTER TABLE title4
  ADD CONSTRAINT altername UNIQUE (name);
 ALTER TABLE title4
  ALTER COLUMN isbn SET NOT NULL;
 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 char(2);
 

























5. ALTER TABLE read_by4 ADD check(timesread >=0);