-- D:\DDL\postgresql\3V_LIMS_Version_1.sql
--
-- Generated for ANSI SQL92 on Thu May 10  15:50:21 2007 by Server Generator 10.1.2.6.18


 CREATE DOMAIN DESCRIPTION_80 AS VARCHAR(80);
CREATE DOMAIN USER_ID AS VARCHAR(15);
CREATE DOMAIN TECH_ID AS INTEGER;
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);
CREATE DOMAIN CODE AS VARCHAR(8);
CREATE DOMAIN DESCRIPTION_250 AS VARCHAR(250);
CREATE TABLE EXPERIMENT_TYPES  (CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL) ;
CREATE TABLE CONTACTS  (ID TECH_ID NOT NULL,CNTA_TYPE VARCHAR(10) NOT NULL DEFAULT 'PERS',FIRSTNAME VARCHAR(30),MIDINITIAL VARCHAR(1),LASTNAME VARCHAR(30),EMAIL VARCHAR(50),USER_ID USER_ID,CNTA_ID_ORGANIZATION TECH_ID,ORGANIZATION_NAME OBJECT_NAME,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE MATERIAL_TYPES  (CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL) ;
CREATE TABLE MATERIALS  (ID TECH_ID NOT NULL,NAME OBJECT_NAME,MATE_SUB_TYPE VARCHAR(4) NOT NULL DEFAULT 'MATE',MATY_CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_250) ;
CREATE TABLE EXPERIMENTS  (ID TECH_ID NOT NULL,NAME OBJECT_NAME,CNTA_ID_REGISTERER TECH_ID NOT NULL,MATE_ID_STUDY_OBJECT TECH_ID NOT NULL,EXTY_CODE CODE NOT NULL,REGISTRATION_DATE DATE,DESCRIPTION DESCRIPTION_250) ;

ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_PK PRIMARY KEY(CODE);
ALTER TABLE CONTACTS ADD CONSTRAINT CNTA_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_PK PRIMARY KEY(CODE);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PK PRIMARY KEY(ID);

ALTER TABLE CONTACTS ADD CONSTRAINT AVCON_1178805021_CNTA__000 CHECK (CNTA_TYPE IN ('PERS', 'ORGA')) ;
ALTER TABLE MATERIALS ADD CONSTRAINT AVCON_1178805021_MATE__000 CHECK (MATE_SUB_TYPE IN ('MATE', 'STOB')) ;
ALTER TABLE CONTACTS ADD CONSTRAINT CNTA_CNTA_ORGANIZATION_FK FOREIGN KEY (CNTA_ID_ORGANIZATION)REFERENCES CONTACTS(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATY_FK FOREIGN KEY (MATY_CODE)REFERENCES MATERIAL_TYPES(CODE);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_CNTA_FK FOREIGN KEY (CNTA_ID_REGISTERER)REFERENCES CONTACTS(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_CODE)REFERENCES EXPERIMENT_TYPES(CODE);

-- D:\DDL\oracle\3V_LIMS_Version_1.sqs
--
-- Generated for Oracle 9i on Thu May 10  15:49:25 2007 by Server Generator 10.1.2.6.18
 
-- Creating Sequence 'CONTACT_ID_SEQ'
CREATE SEQUENCE CONTACT_ID_SEQ;

-- Creating Sequence 'EXPERIMENT_ID_SEQ'
CREATE SEQUENCE EXPERIMENT_ID_SEQ;

-- Creating Sequence 'MATERIAL_ID_SEQ'
CREATE SEQUENCE MATERIAL_ID_SEQ;



