script pro oracle

Set Echo on
Purge Recyclebin;
/* používate-li verzi nižší než Oracle 10g,
 zakomentujte příkaz PURGE */
Drop table department_list cascade constraints;
Drop table role_list cascade constraints;
Drop table role cascade constraints;
Drop table department cascade constraints;
Drop table works_as cascade constraints;
Create table department_list (
      department VarChar2(255) Not Null,
      active Integer Not Null,
      Constraint PK_department_list Primary Key (department)
);
Create table role_list (
      role VarChar2(255) Not Null,
      active Integer Null,
      count Integer Null,
      Constraint PK_role_list Primary Key (role)
);
Create table role (
      role_od Date Null,
      active VarChar2(1) Null,
      role VarChar2(255) Not Null,
      ID_Z Integer Not Null,
      role_do Date Null,
      Constraint PK_role Primary Key (ID_Z)
);
Create table department (
      ID_E Integer Not Null,
      active Integer Not Null,
      do Date Null,
      od Date Not Null,
      department VarChar2(255) Not Null,
      Constraint PK_department Primary Key (ID_E)
);
Create table works_as (
      role_ID_Z Integer Not Null,
      band_ID_E Integer Not Null,
      band_M1od Date Null,
      band_M1do Date Null,
      band_M2od Date Null,
      band_M2do Date Null,
      band_M3od Date Null,
      band_M3do Date Null,
      band_b1od Date Null,
      band_b1do Date Null,
      band_b2od Date Null,
      band_b3do Date Null,
      band_b4od Date Null,
      band_b4do Date Null,
      band_b2do Date Null,
      band_b3od Date Null,
      department_employee_role1_ID_E Integer Not Null,
      employee_role1_datum_nastupu Date Not Null,
      employee_role1_name VarChar2(255) Not Null,
      employee_role1_development_candidate Integer Null,
      employee_role1_surname VarChar2(255) Not Null,
      employee_role1_interest2 VarChar2(255) Null,
      employee_role1_rights VarChar2(2) Not Null,
      employee_role1_interest1 VarChar2(255) Null,
      employee_role1_strenghts VarChar2(271) Null,
      employee_role1_note VarChar2(255) Null,
      employee_role1_ID_E Integer Not Null,
      Constraint UNQ_works_as_1 Unique (band_ID_E),
      Constraint UNQ_works_as_2 Unique (employee_role1_ID_E)
);
Alter table works_as add (
      Constraint FK_works_as_1 Foreign Key (role_ID_Z) References role(ID_Z),
      Constraint FK_works_as_2 Foreign Key (department_employee_role1_ID_E) References department(ID_E)
);