alter_tables.sql

icarnegie上数据库课程中的exercise3的答案

--3.1
CREATE TABLE PURCHASE_ORDER4
	(PoNum char(8) NOT NULL ,
	 Qty   int,
	 OrderDate DATETIME,
	 DueDate  DATETIME,
	 ReceivedDate DATETIME,
	 PRIMARY KEY (PoNum)
	);

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

CREATE TABLE READ_BY4
	(CallNumber char(8) ,
	 LibId char(6),
	 TimesRead int,
	 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)
);

--3.2
ALTER TABLE TITLE4
ADD CONSTRAINT T CHECK (NAME IS NOT NULL AND ISBN IS NOT NULL);

ALTER TABLE TITLE4
ADD  UNIQUE (ISBN , Name);

--3.3
ALTER TABLE BOOK4
ADD FOREIGN KEY (BorrowerId) REFERENCES MEMBER4 (LibId);

ALTER TABLE BOOK4
ADD FOREIGN KEY (CallNumber)  REFERENCES TITLE4 (CallNumber);

--3.4
ALTER TABLE MEMBER4
ADD Join_Date DATETIME ;

ALTER TABLE MEMBER4
ADD Gender char(2);

--3.5
ALTER TABLE READ_BY4
ADD CONSTRAINT TT CHECK (TimesRead>0);