alter_tables.sql

本人的ssd7的练习3的答案

1. Create tables for the above relations for which tables have not been created. 


Create Table READ_BY4
(
callnumber char(8) NOT NULL,
libid char(6) NOT NULL,
timesread smallint,
PRIMARY KEY (callnumber, libid),
FOREIGN KEY (callnumber) References TITLE4,
FOREIGN KEY (libid) References MEMBER4
);

Create Table PURCHASE_ORDER4 
(
ponum char(8) NOT NULL,
qty smallint,
orderdate date,
duedate date,
receiveddate date,
PRIMARY KEY (ponum)
);

Create Table SUPPLIER4
(
supplier_id char(8) NOT NULL,
name char(20),
address char(20),
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,
FOREIGN KEY (ponum) References PURCHASE_ORDER4,
FOREIGN KEY (supplier_id) References SUPPLIER4
);



2. 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 isbn SET NOT NULL;

Alter Table TITLE4
  ALTER name SET NOT NULL;

Alter Table TITLE4
  Add CONSTRAINT ak_isbn UNIQUE(isbn);

Alter Table TITLE4
  Add CONSTRAINT ak_name UNIQUE(name);



3. List the IDs and first names of all the members who have read less than 5% of the total number of books read from the library, in non-decreasing order of the number of books read by each. 

Alter Table BOOK4
  Add CONSTRAINT ref_BOOK4_MEMBER4 FOREIGN KEY(borrowerId) REFERENCES MEMBER4(libid);

Alter Table BOOK4
  Add CONSTRAINT ref_BOOK4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callnumber);

Alter Table READ_BY4
  Add CONSTRAINT ref_READ_BY4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callnumber);

Alter Table READ_BY4
  Add CONSTRAINT ref_READ_BY4_MEMBER4 FOREIGN KEY(libid) REFERENCES MEMBER4(libid);

Alter Table ORDERED4
  Add CONSTRAINT ref_ORDERED4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callumber);

Alter Table ORDERED4
  Add CONSTRAINT ref_ORDERED4_PURCHASE_ORDER4 FOREIGN KEY(ponum) REFERENCES PURCHASE_ORDER4(ponum);

Alter Table ORDERED4
  Add CONSTRAINT ref_ORDERED4_SUPPLIER4 FOREIGN KEY(supplier_Id) REFERENCES SUPPLIER4(supplier_Id);



4. Add the columns "Join_date" and "Gender" to the MEMBER4 relation.

Alter Table MEMBER4
	Add Column Join_date date;
Alter Table MEMBER4
	Add Column Gender char;


5. Specify that the column "TimesRead" in READ_BY4 relation cannot have negative values.

Alter Table READ_BY4
	Add Constraint times_read_not_negative CHECK (timesRead >= 0);