This is an old revision of the document!
Database Design
Creation Order:
- Attendee
- Workshop
- Registration
- Attendance
- Version
Attendee
/******************************************************************************/ /* 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), LastName VARCHAR(40) NOT NULL, FirstName VARCHAR(25), Address1 VARCHAR(255), Address2 VARCHAR(255), Address3 VARCHAR(255), City VARCHAR(255), StateProv VARCHAR(100), Country VARCHAR(40), PostalCode VARCHAR(30), PhoneHome VARCHAR(20), PhoneWork VARCHAR(20), PhoneMobile VARCHAR(20), Fax VARCHAR(20), Email VARCHAR(50), Website VARCHAR(255), Salutation VARCHAR(10), 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), ProfessionalOrg3Number 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 */ /******************************************************************************/
Workshop
/******************************************************************************/ /* 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), StateProv VARCHAR(100), Country VARCHAR(40), PostalCode VARCHAR(30), PhoneHome VARCHAR(20), PhoneWork VARCHAR(20), PhoneMobile 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 */ /******************************************************************************/
Registration
/******************************************************************************/ /* Generated by IBExpert 10/25/2010 10:40:37 */ /******************************************************************************/ SET NAMES NONE; /******************************************************************************/ /* Tables */ /******************************************************************************/ CREATE TABLE REGISTRATION ( 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, TotalReceptionGuests SMALLINT DEFAULT 0, TotalAudiometersToCalibrate SMALLINT DEFAULT 0, PacketNumber INTEGER, REGISTRATIONConfirmation SMALLINT, Notes BLOB SUB_TYPE 1 SEGMENT SIZE 2048, RecModifiedBy VARCHAR(30), RecModifiedDateTime TIMESTAMP ); /******************************************************************************/ /* Primary Keys */ /******************************************************************************/ ALTER TABLE REGISTRATION ADD CONSTRAINT PK_REGISTRATION PRIMARY KEY (ATTENDEERECID, WORKSHOPRECID); /******************************************************************************/ /* Foreign Keys */ /******************************************************************************/ ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_ATTENDEE FOREIGN KEY (ATTENDEERECID) REFERENCES ATTENDEE (RECID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_WORKSHOP FOREIGN KEY (WORKSHOPRECID) REFERENCES WORKSHOP (RECID) ON DELETE CASCADE ON UPDATE CASCADE; /******************************************************************************/ /* Triggers */ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /* Triggers for tables */ /******************************************************************************/ /******************************************************************************/ /* Privileges */ /******************************************************************************/
Attendance
/******************************************************************************/ /* Generated by IBExpert 10/25/2010 10:40:37 */ /******************************************************************************/ SET NAMES NONE; /******************************************************************************/ /* Tables */ /******************************************************************************/ CREATE TABLE ATTENDANCE ( ATTENDEERECID INTEGER NOT NULL, WORKSHOPRECID INTEGER NOT NULL, EventDate DATE NOT NULL, EventCheckIn TIMESTAMP, EventCheckOut TIMESTAMP, RecModifiedBy VARCHAR(30), RecModifiedDateTime TIMESTAMP ); /******************************************************************************/ /* Primary Keys */ /******************************************************************************/ ALTER TABLE ATTENDANCE ADD CONSTRAINT PK_ATTENDANCE PRIMARY KEY (ATTENDEERECID, WORKSHOPRECID, EventDate); /******************************************************************************/ /* 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_ATTENDANCE_WORKSHOP FOREIGN KEY (WORKSHOPRECID) REFERENCES WORKSHOP (RECID) ON DELETE CASCADE ON UPDATE CASCADE; /******************************************************************************/ /* Triggers */ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /* Triggers for tables */ /******************************************************************************/ /******************************************************************************/ /* Privileges */ /******************************************************************************/
Version
/******************************************************************************/ /* 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 */ /******************************************************************************/
Entity Relationship Diagram
Relation Scheme Diagram
DataSet
Add a Database Data Source to the project.
Create a dataset object to have access to the underlying data tables.
public partial class MainWindow : Window { public MainWindow() { InitializeComponent(); // construct dataset datasetWorkshopDB ds = new datasetWorkshopDB(); // use a table adapter to populate the Attendee datatable using ATTENDEE table datasetWorkshopDBTableAdapters.ATTENDEETableAdapter adapt = new datasetWorkshopDBTableAdapters.ATTENDEETableAdapter(); adapt.Fill(ds.ATTENDEE); // use the Attendee datatable as the DataContext for the Window this.DataContext = ds.ATTENDEE.DefaultView; } }