Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
workshopdb:database [2010/11/02 17:00] smayr |
workshopdb:database [2010/11/04 14:11] (current) smayr |
||
---|---|---|---|
Line 1: | Line 1: | ||
== Database Design == | == Database Design == | ||
+ | Creation Order: | ||
* Attendee | * Attendee | ||
* Workshop | * Workshop | ||
+ | * Registration | ||
* Attendance | * Attendance | ||
* Version | * Version | ||
- | Attendee: | + | === Attendee |
<code sql> | <code sql> | ||
/ | / | ||
Line 20: | Line 22: | ||
RECID INTEGER NOT NULL, | RECID INTEGER NOT NULL, | ||
ID | ID | ||
- | FirstName | ||
LastName | LastName | ||
+ | FirstName | ||
Address1 | Address1 | ||
Line 27: | Line 29: | ||
Address3 | Address3 | ||
City | City | ||
- | | + | |
Country | Country | ||
PostalCode | PostalCode | ||
- | | + | |
- | | + | |
- | | + | |
Fax VARCHAR(20), | Fax VARCHAR(20), | ||
Email VARCHAR(50), | Email VARCHAR(50), | ||
Website | Website | ||
- | Salutation | + | Salutation |
Occupation | Occupation | ||
| | ||
Line 51: | Line 53: | ||
ProfessionalOrg2Number VARCHAR(30), | ProfessionalOrg2Number VARCHAR(30), | ||
ProfessionalOrg3 | ProfessionalOrg3 | ||
- | | + | |
| | ||
AccountNumber VARCHAR(30), | AccountNumber VARCHAR(30), | ||
Line 101: | Line 103: | ||
/ | / | ||
</ | </ | ||
+ | |||
+ | === 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; | ||
+ | } | ||
+ | } | ||
+ | </ |