refactions3.sql

derby database source code.good for you.

                              PRIMARY KEY (C0,C1),
                              FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E550_T2 ON DELETE SET NULL);

alter table refint.E550_T1 add  CONSTRAINT F550 FOREIGN KEY (C0b,C2)
                             REFERENCES refint.E550_T3 ON DELETE SET NULL;



CREATE TABLE refint.E570_T1 (C1 CHAR(3) NOT NULL,
                              C0 CHAR(3) NOT NULL,
                              C0a CHAR(3),
                              C2 CHAR(3),
                              PRIMARY KEY (C0,C1));

CREATE TABLE refint.E570_T2 (C1 CHAR(3) NOT NULL,
                              C0 CHAR(3) NOT NULL,
                              C2  CHAR(3),
                              C0b  CHAR(3),
                              PRIMARY KEY (C0,C1));


CREATE TABLE refint.E570_T3 (C1 CHAR(3) NOT NULL,
                              C0 CHAR(3) NOT NULL,
                              C2  CHAR(3),
                              C0b CHAR(3),
                              PRIMARY KEY (C0,C1));

alter table refint.E570_T1 add CONSTRAINT E570_T1_T3  FOREIGN KEY (C0a,C1)
                              REFERENCES refint.E570_T3 ON DELETE CASCADE;
alter table refint.E570_T2 add CONSTRAINT E570_T2_T1 FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E570_T1 ON DELETE RESTRICT;

alter table  refint.E570_T3 add CONSTRAINT E570_T3_T2 FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E570_T2 ON DELETE SET NULL;

CREATE VIEW refint.E570_V1 AS SELECT * FROM refint.E570_T3;

CREATE TABLE refint.E710_T1 (C1 CHAR(3) NOT NULL,
                              C0 CHAR(3) NOT NULL,
                              C2 CHAR(3),
                              C0b CHAR(3),
                              C3 CHAR(3),
                              C0c CHAR(3),
                              PRIMARY KEY (C0,C1),
                              FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E710_T1 ON DELETE CASCADE,
                              FOREIGN KEY (C0c,C3)
                              REFERENCES refint.E710_T1 ON DELETE CASCADE);


CREATE TABLE refint.E720_T1 (C1 CHAR(3) NOT NULL,
                              C0 CHAR(3) NOT NULL,
                              C2 CHAR(3),
                              C0b CHAR(3),
                              PRIMARY KEY (C0,C1),
                              FOREIGN KEY (C0b,C2)
                              REFERENCES refint.E720_T1 ON DELETE CASCADE);

CREATE TABLE refint.E720_T2 (X1 CHAR(3) NOT NULL,
                              C0 CHAR(3) NOT NULL,
                              X3 CHAR(3),
                              C0d CHAR(3),
                              C3 CHAR(3),
                              C0c CHAR(3),
                              PRIMARY KEY (C0,X1),
                              FOREIGN KEY (C0d,X3)
                              REFERENCES refint.E720_T2 ON DELETE CASCADE,
                              FOREIGN KEY (C0c,C3)
                              REFERENCES refint.E720_T1 ON DELETE CASCADE);


CREATE TABLE refint.E610_T1 (C0 CHAR(3) NOT NULL,
                              P1 CHAR(3) NOT NULL,
                              P2 CHAR(3) NOT NULL,
                              PRIMARY KEY (C0,P1,P2));

CREATE TABLE refint.E610_T2 (P1 CHAR(3),
                              C0 CHAR(3) NOT NULL,
                              P4  CHAR(3) NOT NULL,
                              P5  CHAR(3) NOT NULL,
                              PRIMARY KEY (C0,P4,P5));

CREATE TABLE refint.E610_T3 (F1 CHAR(3),
                              C0 CHAR(3),
                              C0e CHAR(3),
                              F2  CHAR(3),
                              C0g CHAR(3),
                              F3  CHAR(3),
                              CONSTRAINT E610_T1_T3 FOREIGN KEY (C0e,F1,F2)
                              REFERENCES refint.E610_T1,
                              CONSTRAINT E610_T2_T3 FOREIGN KEY (C0g,F2,F3)
                              REFERENCES refint.E610_T2)
                              ;

---END OF TEST CASES GOT FROM DB2 Tests.

--START RANDOM COMPLEX LINKS

create table t1( a int not null primary key, b int);
create table t2(x int, y int not null unique, z int);
create table t3(l int, m int not null unique , k int );
create table t4(c1 int not null unique , c2 int);
create table t5(c1 int not null unique , c2 int);
create table t6(c1 int not null unique , c2 int);
--cycle
alter table t2 add constraint c3 foreign key (z)
                              references t4(c1) on delete cascade;
alter table t4 add constraint c4 foreign key (c2)
                              references t5(c1) on delete cascade;
alter table t5 add constraint c5 foreign key (c2)
                              references t6(c1) on delete cascade;

alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete cascade;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete cascade;
alter table t3 add constraint c6 foreign key (k)
                              references t2(y) on delete cascade;
--link a self referencing table to above cycle with a SET NULL
create table t7( a int not null primary key, b int not null  unique,
                  x int references t7(a) ON DELETE CASCADE,
                  z int references t7(b) ON DELETE CASCADE,
			      w int references t6(c1) ON DELETE SET NULL);
--valide multiple paths
create table t8( a int not null primary key, b int);
create table t9(x int, y int not null unique, z int);
create table t10(l int, m int not null unique , k int );
create table t11(c1 int not null unique , c2 int);
alter table t9 add constraint c7 foreign key (x)
                              references t8(a) on delete set null;
alter table t9 add constraint c8 foreign key (z)
                              references t11(c1) on delete set null;
alter table t10 add constraint c9 foreign key (l)
                              references t8(a) on delete set null;
alter table t11 add constraint c10 foreign key (c1)
                              references t10(m) on delete cascade;
--link this one first cycle case
alter table t9 add constraint c11 foreign key (z)
                              references t5(c1) on delete SET NULL;
--valide multiple paths
create table t12( a int not null primary key, b int);
create table t13(x int, y int not null unique, z int);
create table t14(l int, m int not null unique , k int );
create table t15(c1 int not null unique , c2 int);
alter table t13 add constraint c12 foreign key (x)
                              references t12(a) on delete SET NULL;
alter table t13 add constraint c13 foreign key (z)
                              references t15(c1) on delete SET NULL;
alter table t14 add constraint c14 foreign key (l)
                              references t12(a) on delete CASCADE;
alter table t15 add constraint c15 foreign key (c2)
                              references t14(m) on delete SET NULL;
--link this one to first cycle case
alter table t12 add constraint c16 foreign key (b)
                              references t2(y) on delete CASCADE;

alter table t2 drop constraint c2;
alter table t3 drop constraint c6;
alter table t12 drop constraint c16;
alter table t9 drop constraint c11;
alter table t9 drop constraint c7;
alter table t10 drop constraint c9;
alter table t11 drop constraint c10;
alter table t13 drop constraint c12;
alter table t14 drop constraint c14;
alter table t15 drop constraint c15;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t7;
drop table t6;
drop table t8;
drop table t9;
drop table t10;
drop table t11;
drop table t12;
drop table t13;
drop table t14;
drop table t15;


--END OF RANDOM COMPLEX CASE

--FOLLOWING SQL SHOULD PASS

CREATE TABLE Employee (
  ssn INTEGER NOT NULL,
  name VARCHAR(30),
  salary INTEGER,
  address VARCHAR(50),
  constraint EmployeeKey PRIMARY KEY (ssn)
); 

CREATE TABLE Manages (manager_ssn INTEGER NOT NULL unique,
  employee_ssn INTEGER NOT NULL,
  constraint ManagesKey PRIMARY KEY (manager_ssn,employee_ssn),
  FOREIGN KEY (employee_ssn) REFERENCES Employee(ssn)        
  ON DELETE CASCADE ON UPDATE NO ACTION,
  FOREIGN KEY (manager_ssn) REFERENCES Employee(ssn)
  ON DELETE CASCADE ON UPDATE NO ACTION);


CREATE TABLE Shop( 
  shop_name VARCHAR(20)NOT NULL,
  open_closed_times TIME,   
  department VARCHAR(20),
  location INTEGER CHECK(location BETWEEN 1 AND 50),
  floor INTEGER CHECK(floor BETWEEN 1 AND 4), 
  shift char(20), 
  rent INTEGER,
  tel_no INTEGER,
  income INTEGER,
  expenditure INTEGER,
  manager_ssn INTEGER,
  FOREIGN KEY (manager_ssn) REFERENCES Manages(manager_ssn)
  ON DELETE SET NULL ON UPDATE NO ACTION,
  constraint ShopKey PRIMARY KEY (shop_name));


CREATE TABLE Works_in (
   ssn INTEGER NOT NULL,
   shop_name VARCHAR(20)NOT NULL,
   since DATE,
   task VARCHAR(20),
   constraint WorksInKey PRIMARY KEY (ssn, shop_name),
   constraint fkey1 FOREIGN KEY (ssn) REFERENCES Employee
   ON DELETE CASCADE ON UPDATE RESTRICT,
   constraint fkey2 FOREIGN KEY (shop_name) REFERENCES Shop
   ON DELETE NO ACTION ON UPDATE NO ACTION);



CREATE TABLE Owns(ssn INTEGER NOT NULL,
     shop_name VARCHAR(20)NOT NULL,
     date1 DATE,
     constraint OwnsKey PRIMARY KEY (ssn, shop_name),
     FOREIGN KEY (ssn) REFERENCES Employee            
     ON DELETE CASCADE ON UPDATE NO ACTION,
     FOREIGN KEY (shop_name) REFERENCES Shop
     ON DELETE CASCADE ON UPDATE NO ACTION);

CREATE TABLE Item(item_id INTEGER NOT NULL,
     Supplier VARCHAR(20),
     price INTEGER,
     department VARCHAR(20),
     constraint ItemKey PRIMARY KEY (item_id));

CREATE TABLE Producer(producer_name VARCHAR(20) NOT NULL,
  city VARCHAR(20),
  address VARCHAR(50),
  department VARCHAR(20),
  constraint ProducerKey PRIMARY KEY (producer_name));

CREATE TABLE Supplies (producer_name VARCHAR(20) NOT NULL,
   item_id INTEGER NOT NULL,
   constraint SuppliesKey PRIMARY KEY (producer_name, item_id),
   FOREIGN KEY (item_id) REFERENCES Item              
   ON DELETE CASCADE ON UPDATE NO ACTION,
   FOREIGN KEY (producer_name) REFERENCES Producer
   ON DELETE RESTRICT ON UPDATE NO ACTION);

  

CREATE TABLE Sells( item_id INTEGER NOT NULL,
	shop_name VARCHAR(20)NOT NULL,
	constraint SellsKey PRIMARY KEY (item_id, shop_name),
	FOREIGN KEY (item_id) REFERENCES Item                 
    ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY (shop_name) REFERENCES Shop
    ON DELETE CASCADE ON UPDATE NO ACTION);


CREATE TABLE Commerce(item_id INTEGER NOT NULL,
  shop_name VARCHAR(20)NOT NULL,
  cost INTEGER,
  date1 DATE,
  constraint CommerceKey PRIMARY KEY (item_id, shop_name),
  FOREIGN KEY (item_id) REFERENCES Item               
  ON DELETE CASCADE ON UPDATE NO ACTION,
  FOREIGN KEY (shop_name) REFERENCES Shop
  ON DELETE RESTRICT ON UPDATE NO ACTION);

  

CREATE TABLE Stocks(  item_id INTEGER NOT NULL,

  shop_name VARCHAR(20) NOT NULL,
  available INTEGER,
  purchased_date DATE,
  ordered INTEGER,
  constraint StocksKey PRIMARY KEY (item_id, shop_name),
  FOREIGN KEY (item_id) REFERENCES Item               
  ON DELETE CASCADE ON UPDATE NO ACTION,
  FOREIGN KEY (shop_name) REFERENCES Shop
  ON DELETE RESTRICT ON UPDATE NO ACTION);


CREATE TABLE Orders(  
  producer_name VARCHAR(20) NOT NULL,
  shop_name VARCHAR(20)NOT NULL,
  item_id INTEGER NOT NULL,
  receival_date DATE,
  order_date DATE,
  item_amount INTEGER,
  cost INTEGER,
  constraint OrdersKey PRIMARY KEY (item_id, shop_name, producer_name),
  FOREIGN KEY (shop_name) REFERENCES Shop
  ON DELETE RESTRICT ON UPDATE NO ACTION,
  FOREIGN KEY (item_id) REFERENCES Item               
  ON DELETE CASCADE ON UPDATE NO ACTION );


CREATE TABLE Food(
  item_id INTEGER NOT NULL,
  type VARCHAR(20),
  expiration_date DATE,
  constraint FoodKey PRIMARY KEY (item_id),
  FOREIGN KEY (item_id) REFERENCES Item
  ON DELETE CASCADE);

CREATE TABLE Media(
    item_id INTEGER NOT NULL,
    type VARCHAR(20),
    author VARCHAR(50),
    publisher VARCHAR(50),
    title VARCHAR(20),
    published_date DATE,
    constraint MediaKey PRIMARY KEY (item_id),
    FOREIGN KEY (item_id) REFERENCES Item
    ON DELETE CASCADE);

CREATE TABLE Clothing(
     item_id INTEGER NOT NULL,
     type VARCHAR(20),
     color VARCHAR(20),
	 cloth_size char(2),
     brand VARCHAR(30),
     constraint ClothingKey PRIMARY KEY (item_id),
     FOREIGN KEY (item_id) REFERENCES Item
     ON DELETE CASCADE);

CREATE TABLE Accessories(
           item_id INTEGER NOT NULL,
           type VARCHAR(20),
           constraint AccessoriesKey PRIMARY KEY (item_id),
           FOREIGN KEY (item_id) REFERENCES Item
           ON DELETE CASCADE);

---END