-- D:\DDL\postgresql\schema-003.sql
--
-- Generated for ANSI SQL92 on Wed Jun 27  15:29:43 2007 by Server Generator 10.1.2.6.18
--
-- Post generation PostgreSQL specific Changes:
-- 
--  1. change domain FILE from BIT(32000) to BYTEA
--  2. change domain TECH_ID from NUMERIC(20) to BIGINT
-- 


CREATE DOMAIN DESCRIPTION_80 AS VARCHAR(80);
CREATE DOMAIN USER_ID AS VARCHAR(20);
CREATE DOMAIN FILE_NAME AS VARCHAR(100);
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);
CREATE DOMAIN TIME_STAMP AS TIMESTAMP;
CREATE DOMAIN REAL_VALUE AS REAL;
CREATE DOMAIN FILE AS BYTEA;
CREATE DOMAIN CODE AS VARCHAR(20);
CREATE DOMAIN DESCRIPTION_250 AS VARCHAR(250);
CREATE TABLE EXPERIMENT_PROPERTIES  (ID TECH_ID NOT NULL,EXPE_ID TECH_ID NOT NULL,FILE_NAME FILE_NAME NOT NULL,MIME_TYPE VARCHAR(80),VALUE FILE NOT NULL) ;
CREATE TABLE EXPERIMENT_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE PERSONS  (ID TECH_ID NOT NULL,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30),USER_ID USER_ID,EMAIL OBJECT_NAME) ;
CREATE TABLE FILE_FORMAT_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE SAMPLE_INPUTS  (ID TECH_ID NOT NULL,SAMP_ID TECH_ID NOT NULL,PROC_ID TECH_ID NOT NULL) ;
CREATE TABLE OBSERVABLE_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE LOCATOR_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE SAMPLE_COMPONENTS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,SAMP_ID TECH_ID NOT NULL) ;
CREATE TABLE SAMPLE_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE PROCEDURES  (ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP,EXPE_ID TECH_ID NOT NULL,PCTY_ID TECH_ID NOT NULL,PERS_ID TECH_ID) ;
CREATE TABLE PROCEDURE_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL) ;
CREATE TABLE SAMPLE_COMPONENT_MATERIALS  (ID TECH_ID NOT NULL,SACO_ID TECH_ID NOT NULL,MABA_ID TECH_ID NOT NULL) ;
CREATE TABLE EXTERNAL_DATA  (DATA_ID TECH_ID NOT NULL,LOCATION VARCHAR(200) NOT NULL,LOTY_ID TECH_ID NOT NULL,FFTY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL,SAMP_ID TECH_ID) ;
CREATE TABLE DATA  (ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL,OBTY_ID TECH_ID NOT NULL,PROC_ID_ACQUIRED_BY TECH_ID NOT NULL,SAMP_ID_ACQUIRED_FROM TECH_ID,DATA_ID_DERIVED_FROM TECH_ID) ;
CREATE TABLE SAMPLES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,CONT_ID TECH_ID,PROC_ID TECH_ID,SAMP_ID_TOP TECH_ID,SAMP_ID_GENERATED_FROM TECH_ID,SATY_ID TECH_ID NOT NULL) ;
CREATE TABLE MATERIAL_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE MATERIAL_BATCHES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,AMOUNT REAL_VALUE,MATE_ID TECH_ID NOT NULL,CONT_ID TECH_ID,PROC_ID TECH_ID) ;
CREATE TABLE MATERIALS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_250,MATY_ID TECH_ID NOT NULL) ;
CREATE TABLE MOLECULES  (MATE_ID TECH_ID NOT NULL,SEQUENCE VARCHAR(100),ACCESSION_NUMBER OBJECT_NAME,OFF_SET VARCHAR(20),MOLE_ID_INHIBITOR_OF TECH_ID) ;
CREATE TABLE EXPERIMENTS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,EXTY_ID TECH_ID NOT NULL,MATE_ID_STUDY_OBJECT TECH_ID,PERS_ID_REGISTERER TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP,DESCRIPTION DESCRIPTION_250) ;

ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_PK PRIMARY KEY(ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_PK PRIMARY KEY(ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_PK PRIMARY KEY(ID);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_PK PRIMARY KEY(ID);
ALTER TABLE LOCATOR_TYPES ADD CONSTRAINT LOTY_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_COMPONENTS ADD CONSTRAINT SACO_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_PK PRIMARY KEY(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PK PRIMARY KEY(ID);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_PK PRIMARY KEY(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_PK PRIMARY KEY(DATA_ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_PK PRIMARY KEY(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PK PRIMARY KEY(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_PK PRIMARY KEY(MATE_ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_BK_UK UNIQUE(EXPE_ID,FILE_NAME);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_BK_UK UNIQUE(CODE);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_BK_UK UNIQUE(USER_ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE(CODE);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_BK_UK UNIQUE(SAMP_ID,PROC_ID);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_BK_UK UNIQUE(CODE);
ALTER TABLE LOCATOR_TYPES ADD CONSTRAINT LOTY_BK_UK UNIQUE(CODE);
ALTER TABLE SAMPLE_COMPONENTS ADD CONSTRAINT SACO_BK_UK UNIQUE(CODE,SAMP_ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_BK_UK UNIQUE(CODE);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_BK_UK UNIQUE(CODE);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_BK_UK UNIQUE(MABA_ID,SACO_ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_BK_UK UNIQUE(CODE);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_UK UNIQUE(CODE);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE(CODE);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_BK_UK UNIQUE(CODE,MATE_ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_BK_UK UNIQUE(CODE);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_BK_UK UNIQUE(SEQUENCE);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_BK_UK UNIQUE(CODE);

ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_EXPE_FK FOREIGN KEY (EXPE_ID)REFERENCES EXPERIMENTS(ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_PROC_FK FOREIGN KEY (PROC_ID)REFERENCES PROCEDURES(ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_SAMP_FK FOREIGN KEY (SAMP_ID)REFERENCES SAMPLES(ID);
ALTER TABLE SAMPLE_COMPONENTS ADD CONSTRAINT SACO_SAMP_FK FOREIGN KEY (SAMP_ID)REFERENCES SAMPLES(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PCTY_FK FOREIGN KEY (PCTY_ID)REFERENCES PROCEDURE_TYPES(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PERS_FK FOREIGN KEY (PERS_ID)REFERENCES PERSONS(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_EXPE_FK FOREIGN KEY (EXPE_ID)REFERENCES EXPERIMENTS(ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_MABA_FK FOREIGN KEY (MABA_ID)REFERENCES MATERIAL_BATCHES(ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_SACO_FK FOREIGN KEY (SACO_ID)REFERENCES SAMPLE_COMPONENTS(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_SAMP_FK FOREIGN KEY (SAMP_ID)REFERENCES SAMPLES(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_DATA_FK FOREIGN KEY (DATA_ID)REFERENCES DATA(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_LOTY_FK FOREIGN KEY (LOTY_ID)REFERENCES LOCATOR_TYPES(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_FFTY_FK FOREIGN KEY (FFTY_ID)REFERENCES FILE_FORMAT_TYPES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_SAMP_FK FOREIGN KEY (SAMP_ID_ACQUIRED_FROM)REFERENCES SAMPLES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PROC_FK FOREIGN KEY (PROC_ID_ACQUIRED_BY)REFERENCES PROCEDURES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_DATA_FK FOREIGN KEY (DATA_ID_DERIVED_FROM)REFERENCES DATA(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_OBTY_FK FOREIGN KEY (OBTY_ID)REFERENCES OBSERVABLE_TYPES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_PROC_FK FOREIGN KEY (PROC_ID)REFERENCES PROCEDURES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SAMP_FK_GENERATED_FROM FOREIGN KEY (SAMP_ID_GENERATED_FROM)REFERENCES SAMPLES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SAMP_FK_TOP FOREIGN KEY (SAMP_ID_TOP)REFERENCES SAMPLES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SATY_FK FOREIGN KEY (SATY_ID)REFERENCES SAMPLE_TYPES(ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_MATE_FK FOREIGN KEY (MATE_ID)REFERENCES MATERIALS(ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_PROC_FK FOREIGN KEY (PROC_ID)REFERENCES PROCEDURES(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATY_FK FOREIGN KEY (MATY_ID)REFERENCES MATERIAL_TYPES(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_MATE_FK FOREIGN KEY (MATE_ID)REFERENCES MATERIALS(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_MOLE_FK FOREIGN KEY (MOLE_ID_INHIBITOR_OF)REFERENCES MOLECULES(MATE_ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_MATE_FK FOREIGN KEY (MATE_ID_STUDY_OBJECT)REFERENCES MATERIALS(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_EXTY_FK FOREIGN KEY (EXTY_ID)REFERENCES EXPERIMENT_TYPES(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER)REFERENCES PERSONS(ID);


-- D:\DDL\postgresql\3V_LIMS_REL04_SEQ.sqs
--
-- Generated for Oracle 10g on Wed Jun 27  15:43:18 2007 by Server Generator 10.1.2.6.18
 
--  Creating Sequences

CREATE SEQUENCE EXPERIMENT_PROPERTY_ID_SEQ;

CREATE SEQUENCE PROCEDURE_TYPE_ID_SEQ;

CREATE SEQUENCE SAMPLE_INPUT_ID_SEQ;

CREATE SEQUENCE SAMPLE_TYPE_ID_SEQ;

CREATE SEQUENCE LOCATOR_TYPE_ID_SEQ;

CREATE SEQUENCE SAMPLE_ID_SEQ;

CREATE SEQUENCE EXPERIMENT_ID_SEQ;

CREATE SEQUENCE MATERIAL_BATCH_ID_SEQ;

CREATE SEQUENCE OBSERVABLE_TYPE_ID_SEQ;

CREATE SEQUENCE SAMPLE_COMPONENT_ID_SEQ;

CREATE SEQUENCE MATERIAL_ID_SEQ;

CREATE SEQUENCE DATA_ID_SEQ;

--  Creating Sequence for the SAMPLE_COMPONENT_MATERIALS table (standard naming convention was too long)
CREATE SEQUENCE SCMA_ID_SEQ;

CREATE SEQUENCE PROCEDURE_ID_SEQ;

CREATE SEQUENCE FILE_FORMAT_TYPE_ID_SEQ;

CREATE SEQUENCE MATERIAL_TYPE_ID_SEQ;

CREATE SEQUENCE EXPERIMENT_TYPE_ID_SEQ;

CREATE SEQUENCE PERSON_ID_SEQ;

