== 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 ==
{{:workshopdb:workshopdb-erd.png|WorkshopDB: Entity Relationship Diagram}}
== Relation Scheme Diagram ==
{{:workshopdb:workshopdb-relationschemediagram.png|WorkshopDB: Relation Scheme Diagram}}
== DataSet ==
Add a [[swdev:dotnet:Firebird Databases in .NET#adding_firebird_support_in_an_application|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 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;
}
}