alter_tables.sql

ssd8练习答案

--3
--3.1
CREATE TABLE READ_BY4
(CallNumber  	char(8) NOT NULL, 
 LibId		char(6) NOT NULL, 
 TimesRead	int,
 PRIMARY KEY(CallNumber,LibId),
 FOREIGN KEY(CallNumber) REFERENCES  TITLE4(CallNumber),
 FOREIGN KEY(LibId)  REFERENCES MEMBER4(LibId)
);
CREATE TABLE PURCHASE_ORDER4
(PoNum 		char(8) NOT NULL, 
 Qty 		int,
 OrderDate 	date,
 DueDate	date, 
 ReceivedDate   date,
 PRIMARY KEY(PoNum) 
);
CREATE TABLE SUPPLIER4
(Supplier_Id    char(8) NOT NULL,
 Name		char(8) NOT NULL, 
 Address	char(11),
 PRIMARY KEY(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),
 FOREIGN KEY(CallNumber) REFERENCES  TITLE4(CallNumber),
 FOREIGN KEY(PoNum) REFERENCES  PURCHASE_ORDER4(PoNum),
 FOREIGN KEY(Supplier_Id) REFERENCES   SUPPLIER4(Supplier_Id)
);
--3.2
ALTER TABLE TITLE4
	ADD UNIQUE(Name),
	ADD UNIQUE(isbn),
	ALTER Name SET not NULL,
	ALTER ISBN SET not NULL;
--3.3	
ALTER TABLE BOOK4
  ADD CONSTRAINT FK_BOOK4_MEMBER4 FOREIGN KEY(borrowerid) REFERENCES MEMBER4(libid);

ALTER TABLE BOOK4
  ADD CONSTRAINT FK_BOOK4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callnumber);

ALTER TABLE READ_BY4
  ADD CONSTRAINT FK_READ_BY4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callnumber);

ALTER TABLE READ_BY4
  ADD CONSTRAINT FK_READ_BY4_MEMBER4 FOREIGN KEY(LibId) REFERENCES MEMBER4(LibId);

ALTER TABLE ORDERED4
  ADD CONSTRAINT FK_ORDERED4_TITLE4 FOREIGN KEY(CallNumber) REFERENCES TITLE4(CallNumber);

ALTER TABLE ORDERED4
  ADD CONSTRAINT FK_ORDERED4_PURCHASE_ORDER4 FOREIGN KEY(PoNum) REFERENCES PURCHASE_ORDER4(PoNum);

ALTER TABLE ORDERED4
  ADD CONSTRAINT FK_ORDERED4_SUPPLIER4 FOREIGN KEY(Supplier_Id) REFERENCES SUPPLIER4(Supplier_Id);

--3.4
ALTER TABLE MEMBER4
	ADD Join_date date,
	ADD Gender    char(1);
--3.5 
ALTER TABLE READ_BY4 
	ADD check(TimesRead>=0);