connect 'jdbc:derby:workstyleDB;user=workstyle;password=hogehoge';

CREATE TABLE FILE_INFO (
       FILE_ID INT NOT NULL
     , NAME VARCHAR(256) NOT NULL
     , PATH VARCHAR(256)
     , PRIMARY KEY (FILE_ID)
);
INSERT INTO FILE_INFO (FILE_ID, NAME, PATH) SELECT FILE_ID, NAME, PATH FROM FILE;

CREATE TABLE TMP_FILE_RELATION (
       RELATION_ID INT NOT NULL
     , TASK_ID INT NOT NULL
     , FILE_ID INT NOT NULL
     , PRIMARY KEY (RELATION_ID)
);
INSERT INTO TMP_FILE_RELATION (RELATION_ID, TASK_ID, FILE_ID) SELECT RELATION_ID, TASK_ID, FILE_ID FROM TASK_FILE_RELATION;

DROP VIEW ATTACHED_FILE;
DROP TABLE TASK_FILE_RELATION;

CREATE TABLE TASK_FILE_RELATION (
       TASK_ID INT NOT NULL
     , FILE_ID INT NOT NULL
     , PRIMARY KEY (TASK_ID, FILE_ID)
);

INSERT INTO TASK_FILE_RELATION (TASK_ID, FILE_ID) SELECT TASK_ID, FILE_ID FROM TMP_FILE_RELATION;

DROP TABLE FILE;
DROP TABLE TMP_FILE_RELATION;

CREATE TABLE TASK_RELATION (
       SRC_TASK_ID INT NOT NULL
     , DEST_TASK_ID INT NOT NULL
     , PRIMARY KEY (SRC_TASK_ID, DEST_TASK_ID)
);

ALTER TABLE TASK_FILE_RELATION
  ADD CONSTRAINT FK_TASK_FILE_RELATION_1
      FOREIGN KEY (TASK_ID)
      REFERENCES TASK (TASK_ID);

ALTER TABLE TASK_FILE_RELATION
  ADD CONSTRAINT FK_TASK_FILE_RELATION_2
      FOREIGN KEY (FILE_ID)
      REFERENCES FILE_INFO (FILE_ID);

ALTER TABLE TASK_RELATION
  ADD CONSTRAINT FK_TASK_RELATION_1
      FOREIGN KEY (SRC_TASK_ID)
      REFERENCES TASK (TASK_ID);

ALTER TABLE TASK_RELATION
  ADD CONSTRAINT FK_TASK_RELATION_2
      FOREIGN KEY (DEST_TASK_ID)
      REFERENCES TASK (TASK_ID);

CREATE VIEW ATTACHED_FILE AS
SELECT F.FILE_ID, F.NAME, F.PATH, R.TASK_ID
FROM FILE_INFO F, TASK_FILE_RELATION R
WHERE F.FILE_ID = R.FILE_ID;

UPDATE ID_TABLE SET TABLE_NAME = 'FILE_INFO' WHERE ID_TABLE_ID=6;
INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (7, 'TAG_TYPE', 2001, 1)