Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
workshopdb:database [2010/11/02 12:40] smayr created |
workshopdb:database [2010/11/04 14:11] (current) smayr |
||
---|---|---|---|
Line 1: | Line 1: | ||
== Database Design == | == Database Design == | ||
+ | Creation Order: | ||
+ | * Attendee | ||
+ | * Workshop | ||
+ | * Registration | ||
+ | * Attendance | ||
+ | * Version | ||
+ | |||
+ | === Attendee === | ||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES NONE; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE GENERATOR ATTENDEE_RECID_GEN; | ||
+ | |||
+ | CREATE TABLE ATTENDEE ( | ||
+ | RECID INTEGER NOT NULL, | ||
+ | ID | ||
+ | LastName | ||
+ | FirstName | ||
+ | |||
+ | Address1 | ||
+ | Address2 | ||
+ | Address3 | ||
+ | City | ||
+ | StateProv | ||
+ | Country | ||
+ | PostalCode | ||
+ | PhoneHome | ||
+ | PhoneWork | ||
+ | PhoneMobile | ||
+ | Fax VARCHAR(20), | ||
+ | Email VARCHAR(50), | ||
+ | Website | ||
+ | |||
+ | Salutation | ||
+ | Occupation | ||
+ | | ||
+ | StateLicense1 | ||
+ | StateLicense1Number | ||
+ | StateLicense2 | ||
+ | StateLicense2Number | ||
+ | StateLicense3 | ||
+ | StateLicense3Number | ||
+ | ProfessionalOrg1 | ||
+ | ProfessionalOrg1Number VARCHAR(30), | ||
+ | ProfessionalOrg2 | ||
+ | ProfessionalOrg2Number VARCHAR(30), | ||
+ | ProfessionalOrg3 | ||
+ | ProfessionalOrg3Number VARCHAR(30), | ||
+ | | ||
+ | AccountNumber VARCHAR(30), | ||
+ | YearToDate | ||
+ | | ||
+ | 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, | ||
+ | end | ||
+ | ^ | ||
+ | |||
+ | SET TERM ; ^ | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | === Workshop === | ||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES NONE; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE GENERATOR WORKSHOP_RECID_GEN; | ||
+ | |||
+ | CREATE TABLE WORKSHOP ( | ||
+ | RECID INTEGER NOT NULL, | ||
+ | AttendeeRecID | ||
+ | WorkshopID | ||
+ | Name | ||
+ | Address1 | ||
+ | Address2 | ||
+ | Address3 | ||
+ | City | ||
+ | StateProv | ||
+ | Country | ||
+ | PostalCode | ||
+ | PhoneHome | ||
+ | PhoneWork | ||
+ | PhoneMobile | ||
+ | Fax VARCHAR(20), | ||
+ | Email VARCHAR(50), | ||
+ | Website | ||
+ | EventDate1 | ||
+ | EventDate2 | ||
+ | EventDate3 | ||
+ | PricingPackage SMALLINT, | ||
+ | CancellationPolicy | ||
+ | Instructions | ||
+ | HotelAccommodations BLOB SUB_TYPE 1 SEGMENT SIZE 2048, | ||
+ | | ||
+ | RecModifiedBy | ||
+ | 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, | ||
+ | end | ||
+ | ^ | ||
+ | |||
+ | SET TERM ; ^ | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | === Registration === | ||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES NONE; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE TABLE REGISTRATION ( | ||
+ | ATTENDEERECID | ||
+ | WORKSHOPRECID | ||
+ | RegistrationDate | ||
+ | PricingPackage | ||
+ | PaymentMethod | ||
+ | PaymentAmountTotal | ||
+ | PaymentAmountDue | ||
+ | PaymentAmountPaid | ||
+ | ExpediteCertificates SMALLINT default 0, | ||
+ | TotalReceptionGuests SMALLINT default 0, | ||
+ | TotalAudiometersToCalibrate SMALLINT default 0, | ||
+ | PacketNumber | ||
+ | REGISTRATIONConfirmation SMALLINT, | ||
+ | Notes BLOB SUB_TYPE 1 SEGMENT SIZE 2048, | ||
+ | RecModifiedBy | ||
+ | RecModifiedDateTime | ||
+ | ); | ||
+ | |||
+ | / | ||
+ | /* Primary Keys */ | ||
+ | / | ||
+ | ALTER TABLE REGISTRATION | ||
+ | ADD CONSTRAINT PK_REGISTRATION | ||
+ | PRIMARY KEY (ATTENDEERECID, | ||
+ | |||
+ | / | ||
+ | /* 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 | ||
+ | / | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | === Attendance === | ||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES NONE; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE TABLE ATTENDANCE ( | ||
+ | ATTENDEERECID | ||
+ | WORKSHOPRECID | ||
+ | EventDate | ||
+ | EventCheckIn | ||
+ | EventCheckOut | ||
+ | RecModifiedBy | ||
+ | RecModifiedDateTime | ||
+ | ); | ||
+ | |||
+ | / | ||
+ | /* Primary Keys */ | ||
+ | / | ||
+ | ALTER TABLE ATTENDANCE | ||
+ | ADD CONSTRAINT PK_ATTENDANCE | ||
+ | PRIMARY KEY (ATTENDEERECID, | ||
+ | |||
+ | / | ||
+ | /* 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 | ||
+ | / | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | === Version === | ||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES NONE; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE TABLE VERSION ( | ||
+ | NUMBER | ||
+ | BUILD | ||
+ | ); | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | </ | ||
== Entity Relationship Diagram == | == Entity Relationship Diagram == | ||
+ | {{: | ||
+ | |||
+ | == Relation Scheme Diagram == | ||
+ | {{: | ||
+ | |||
+ | == DataSet == | ||
+ | |||
+ | Add a [[swdev: | ||
+ | |||
+ | Create a dataset object to have access to the underlying data tables. | ||
+ | |||
+ | <code csharp> | ||
+ | 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 adaptAttendee = new datasetWorkshopDBTableAdapters.ATTENDEETableAdapter(); | ||
+ | adaptAttendee.Fill(ds.ATTENDEE); | ||
+ | |||
+ | datasetWorkshopDBTableAdapters.WORKSHOPTableAdapter adaptWorkshop = new datasetWorkshopDBTableAdapters.WORKSHOPTableAdapter(); | ||
+ | adaptWorkshop.Fill(ds.WORKSHOP); | ||
+ | |||
+ | datasetWorkshopDBTableAdapters.REGISTRATIONTableAdapter adaptRegistration = new datasetWorkshopDBTableAdapters.REGISTRATIONTableAdapter(); | ||
+ | adaptRegistration.Fill(ds.REGISTRATION); | ||
+ | |||
+ | datasetWorkshopDBTableAdapters.ATTENDANCETableAdapter adaptAttendance = new datasetWorkshopDBTableAdapters.ATTENDANCETableAdapter(); | ||
+ | adaptAttendance.Fill(ds.ATTENDANCE); | ||
+ | |||
+ | // use the Attendee datatable as the DataContext for the Window | ||
+ | this.DataContext = ds.ATTENDEE.DefaultView; | ||
+ | } | ||
+ | } | ||
+ | </ |