This is an old revision of the document!
Create Database Using Firebird
Patient table
/******************************************************************************/ /* Generated by IBExpert 10/19/2010 16:29:54 */ /******************************************************************************/ SET NAMES NONE; /******************************************************************************/ /* Tables */ /******************************************************************************/ CREATE GENERATOR patient_no_gen; CREATE TABLE patient ( patientno INTEGER NOT NULL, patientid VARCHAR(13), firstname VARCHAR(25), lastname VARCHAR(40) NOT NULL, STYLE SMALLINT, speechtest VARCHAR(1), leftserial VARCHAR(12), rightserial VARCHAR(12), leftsrt VARCHAR(12), rightsrt VARCHAR(12), bisrt VARCHAR(12), leftmcl VARCHAR(12), rightmcl VARCHAR(12), bimcl VARCHAR(12), leftucl VARCHAR(12), rightucl VARCHAR(12), biucl VARCHAR(12), birthdate DATE, gender CHAR(1), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(40), state VARCHAR(35), country VARCHAR(40), zip VARCHAR(10), email VARCHAR(50), homephone VARCHAR(20), workphone VARCHAR(20), mobilephone VARCHAR(20), rag250 SMALLINT DEFAULT -1 NOT NULL, rag500 SMALLINT DEFAULT -1 NOT NULL, rag750 SMALLINT DEFAULT -1 NOT NULL, rag1000 SMALLINT DEFAULT -1 NOT NULL, rag1500 SMALLINT DEFAULT -1 NOT NULL, rag2000 SMALLINT DEFAULT -1 NOT NULL, rag3000 SMALLINT DEFAULT -1 NOT NULL, rag4000 SMALLINT DEFAULT -1 NOT NULL, rag6000 SMALLINT DEFAULT -1 NOT NULL, rag8000 SMALLINT DEFAULT -1 NOT NULL, lag250 SMALLINT DEFAULT -1 NOT NULL, lag500 SMALLINT DEFAULT -1 NOT NULL, lag750 SMALLINT DEFAULT -1 NOT NULL, lag1000 SMALLINT DEFAULT -1 NOT NULL, lag1500 SMALLINT DEFAULT -1 NOT NULL, lag2000 SMALLINT DEFAULT -1 NOT NULL, lag3000 SMALLINT DEFAULT -1 NOT NULL, lag4000 SMALLINT DEFAULT -1 NOT NULL, lag6000 SMALLINT DEFAULT -1 NOT NULL, lag8000 SMALLINT DEFAULT -1 NOT NULL ); /******************************************************************************/ /* Primary Keys */ /******************************************************************************/ ALTER TABLE patient ADD CONSTRAINT pk_patient PRIMARY KEY (patientno); /******************************************************************************/ /* Indices */ /******************************************************************************/ CREATE INDEX firstnamex ON patient (firstname); CREATE INDEX lastfirstnmx ON patient (lastname, firstname); CREATE INDEX lastnamex ON patient (lastname); CREATE INDEX leftserialx ON patient (leftserial); CREATE INDEX patientidx ON patient (patientid); CREATE INDEX rightserialx ON patient (rightserial); /******************************************************************************/ /* Triggers */ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /* Triggers for tables */ /******************************************************************************/ /* Trigger: SET_PATIENT_NO */ CREATE OR ALTER TRIGGER set_patient_no FOR patient ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.patientno IS NULL) THEN NEW.patientno = gen_id(patient_no_gen,1); END ^ SET TERM ; ^ /******************************************************************************/ /* Privileges */ /******************************************************************************/
Fitting table
/******************************************************************************/ /* Generated by IBExpert 10/19/2010 16:38:37 */ /******************************************************************************/ SET NAMES NONE; /******************************************************************************/ /* Tables */ /******************************************************************************/ CREATE TABLE fitting ( patientno INTEGER NOT NULL, fittingdate DATE NOT NULL, ear SMALLINT NOT NULL, instrumentid VARCHAR(5) NOT NULL, maxmemory SMALLINT, toggle SMALLINT, uservc SMALLINT, fittingmode SMALLINT, productno SMALLINT, productname VARCHAR(25), notes VARCHAR(300), serialno VARCHAR(12), memchangetone SMALLINT, memchangemultibeep SMALLINT, prescriptiontype SMALLINT DEFAULT 0 ); /******************************************************************************/ /* Primary Keys */ /******************************************************************************/ ALTER TABLE fitting ADD CONSTRAINT pk_fitting PRIMARY KEY (patientno, fittingdate, ear, instrumentid); /******************************************************************************/ /* Foreign Keys */ /******************************************************************************/ ALTER TABLE fitting ADD CONSTRAINT fk_fitting_patient FOREIGN KEY (patientno) REFERENCES patient (patientno) ON DELETE CASCADE ON UPDATE CASCADE; /******************************************************************************/ /* Privileges */ /******************************************************************************/
StylePhotos table
/******************************************************************************/ /* Generated by IBExpert 10/19/2010 16:43:46 */ /******************************************************************************/ SET NAMES NONE; /******************************************************************************/ /* Tables */ /******************************************************************************/ CREATE TABLE stylephotos ( STYLE INTEGER, stylephoto BLOB SUB_TYPE 0 SEGMENT SIZE 80 ); /******************************************************************************/ /* Privileges */ /******************************************************************************/