Creation Order:

• Attendee
• Workshop
• Attendance
• Version

/******************************************************************************/
/*                 Generated by IBExpert 10/25/2010 10:42:11                  */
/******************************************************************************/
SET NAMES NONE;
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE GENERATOR ATTENDEE_RECID_GEN;
 
CREATE TABLE ATTENDEE (
    RECID        INTEGER NOT NULL,
    ID           VARCHAR(25),
    FirstName    VARCHAR(25),
    LastName     VARCHAR(40) NOT NULL,
 
    Address1       VARCHAR(255),
    Address2       VARCHAR(255),
    Address3       VARCHAR(255),
    City           VARCHAR(255),
    StateRegion    VARCHAR(100),
    Country        VARCHAR(40),
    PostalCode     VARCHAR(30),
    PhomeHome      VARCHAR(20),
    PhomeWork      VARCHAR(20),
    PhomeMobile    VARCHAR(20),
    Fax            VARCHAR(20),
    Email          VARCHAR(50),
    Website        VARCHAR(255),
 
    Salutation   SMALLINT,
    Occupation   VARCHAR(50),
 
    StateLicense1          VARCHAR(30),
    StateLicense1Number    VARCHAR(30),
    StateLicense2          VARCHAR(30),
    StateLicense2Number    VARCHAR(30),
    StateLicense3          VARCHAR(30),
    StateLicense3Number    VARCHAR(30),
    ProfessionalOrg1       VARCHAR(30),
    ProfessionalOrg1Number VARCHAR(30),
    ProfessionalOrg2       VARCHAR(30),
    ProfessionalOrg2Number VARCHAR(30),
    ProfessionalOrg3       VARCHAR(30),
    ProfessionalOrg4Number VARCHAR(30),
 
    AccountNumber VARCHAR(30),
    YearToDate    NUMERIC(10,2) DEFAULT 0 NOT NULL,
 
    Notes                 BLOB SUB_TYPE 1 SEGMENT SIZE 2048,
 
    RecModifiedBy VARCHAR(30),
    RecModifiedDateTime TIMESTAMP
);
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
ALTER TABLE ATTENDEE 
  ADD CONSTRAINT PK_ATTENDEE 
  PRIMARY KEY (RECID);
 
/******************************************************************************/
/*                                  Indices                                   */
/******************************************************************************/
CREATE INDEX FIRSTNAMEX ON ATTENDEE (FIRSTNAME);
CREATE INDEX LASTFIRSTNMX ON ATTENDEE (LASTNAME, FIRSTNAME);
CREATE INDEX LASTNAMEX ON ATTENDEE (LASTNAME);
CREATE INDEX ATTENDEEIDX ON ATTENDEE (ID);
 
/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/
SET TERM ^ ;
 
/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/
/* Trigger: SET_ATTENDEE_RECID */
CREATE OR ALTER TRIGGER SET_ATTENDEE_RECID FOR ATTENDEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.recid IS NULL) THEN
  NEW.recid = gen_id(attendee_recid_gen,1);
END
^
 
SET TERM ; ^
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

/******************************************************************************/
/*                 Generated by IBExpert 10/25/2010 10:42:11                  */
/******************************************************************************/
SET NAMES NONE;
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE GENERATOR WORKSHOP_RECID_GEN;
 
CREATE TABLE WORKSHOP (
    RECID          INTEGER NOT NULL,
    AttendeeRecID  INTEGER,
    WorkshopID     VARCHAR(30),
    Name           VARCHAR(200),
    Address1       VARCHAR(255),
    Address2       VARCHAR(255),
    Address3       VARCHAR(255),
    City           VARCHAR(255),
    StateRegion    VARCHAR(100),
    Country        VARCHAR(40),
    PostalCode     VARCHAR(30),
    PhomeHome      VARCHAR(20),
    PhomeWork      VARCHAR(20),
    PhomeMobile    VARCHAR(20),
    Fax            VARCHAR(20),
    Email          VARCHAR(50),
    Website        VARCHAR(255),
    EventDate1     DATE,
    EventDate2     DATE,
    EventDate3     DATE,
    PricingPackage SMALLINT,
    CancellationPolicy  BLOB SUB_TYPE 1 SEGMENT SIZE 2048,
    Instructions        BLOB SUB_TYPE 1 SEGMENT SIZE 2048,
    HotelAccommodations BLOB SUB_TYPE 1 SEGMENT SIZE 2048,
 
    RecModifiedBy       VARCHAR(30),
    RecModifiedDateTime TIMESTAMP
);
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
ALTER TABLE WORKSHOP 
  ADD CONSTRAINT PK_WORKSHOP 
  PRIMARY KEY (RECID);
 
/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/
 
/******************************************************************************/
/*                                  Indices                                   */
/******************************************************************************/
CREATE INDEX WORKSHOPIDX ON WORKSHOP (WORKSHOPID);
CREATE INDEX WORKSHOPNAMEX ON WORKSHOP (NAME);
 
/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/
SET TERM ^ ;
 
/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/
/* Trigger: SET_PATIENT_RECID */
CREATE OR ALTER TRIGGER SET_WORKSHOP_RECID FOR WORKSHOP
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.recid IS NULL) THEN
  NEW.recid = gen_id(workshop_recid_gen,1);  
END
^
 
SET TERM ; ^
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

/******************************************************************************/
/*                 Generated by IBExpert 10/25/2010 10:40:37                  */
/******************************************************************************/
SET NAMES NONE;
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE TABLE ATTENDANCE (
    ATTENDEERECID        INTEGER NOT NULL,
    WORKSHOPRECID        INTEGER NOT NULL,
    RegistrationDate     DATE NOT NULL,
    PricingPackage       SMALLINT NOT NULL,
    PaymentMethod        SMALLINT,
    PaymentAmountTotal   NUMERIC(10,2) DEFAULT 0 NOT NULL,
    PaymentAmountDue     NUMERIC(10,2) DEFAULT 0 NOT NULL,
    PaymentAmountPaid    NUMERIC(10,2) DEFAULT 0 NOT NULL,
    ExpediteCertificates SMALLINT DEFAULT 0,
    EventCheckIn1        TIMESTAMP,
    EventCheckOut1       TIMESTAMP,
    EventCheckIn2        TIMESTAMP,
    EventCheckOut2       TIMESTAMP,
    EventCheckIn3        TIMESTAMP,
    EventCheckOut3       TIMESTAMP,
    TotalReceptionGuests SMALLINT DEFAULT 0,
    TotalAudiometersToCalibrate SMALLINT DEFAULT 0,
    PacketNumber           INTEGER,
    AttendanceConfirmation SMALLINT,
	Notes                  BLOB SUB_TYPE 1 SEGMENT SIZE 2048,
    RecModifiedBy          VARCHAR(30),
    RecModifiedDateTime    TIMESTAMP
);
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
ALTER TABLE ATTENDANCE 
  ADD CONSTRAINT PK_ATTENDANCE 
  PRIMARY KEY (ATTENDEERECID, WORKSHOPRECID);
 
/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/
ALTER TABLE ATTENDANCE 
  ADD CONSTRAINT FK_ATTENDANCE_ATTENDEE 
  FOREIGN KEY (ATTENDEERECID) 
  REFERENCES ATTENDEE (RECID) 
    ON DELETE CASCADE ON UPDATE CASCADE;
 
ALTER TABLE ATTENDANCE 
  ADD CONSTRAINT FK_WORKSHOP_ATTENDEE 
  FOREIGN KEY (WORKSHOPRECID) 
  REFERENCES WORKSHOP (RECID) 
    ON DELETE CASCADE ON UPDATE CASCADE;
 
 
/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/
SET TERM ^ ;
 
/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

/******************************************************************************/
/*                 Generated by IBExpert 10/25/2010 10:43:29                  */
/******************************************************************************/
SET NAMES NONE;
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE TABLE VERSION (
    NUMBER  NUMERIC(2,2) NOT NULL,
    BUILD   VARCHAR(255)
);
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/