Differences

This shows you the differences between two versions of the page.

--- workshopdb:database [2010/11/02 17:03]
smayr
+++ workshopdb:database [2010/11/04 14:11] (current)
smayr
@@ Line 3: / Line 3: @@
   * Attendee
   * Workshop
+  * Registration
   * Attendance
   * Version
@@ Line 21: / Line 22: @@
     RECID        INTEGER NOT NULL,
     ID           VARCHAR(25),
-    FirstName    VARCHAR(25),
     LastName     VARCHAR(40) NOT NULL,
+    FirstName    VARCHAR(25),
     Address1       VARCHAR(255),
@@ Line 28: / Line 29: @@
     Address3       VARCHAR(255),
     City           VARCHAR(255),
-    StateRegion    VARCHAR(100),
+    StateProv      VARCHAR(100),
     Country        VARCHAR(40),
     PostalCode     VARCHAR(30),
-    PhomeHome      VARCHAR(20),
+    PhoneHome      VARCHAR(20),
-    PhomeWork      VARCHAR(20),
+    PhoneWork      VARCHAR(20),
-    PhomeMobile    VARCHAR(20),
+    PhoneMobile    VARCHAR(20),
     Fax            VARCHAR(20),
     Email          VARCHAR(50),
     Website        VARCHAR(255),
-    Salutation   SMALLINT,
+    Salutation   VARCHAR(10),
     Occupation   VARCHAR(50),
@@ Line 52: / Line 53: @@
     ProfessionalOrg2Number VARCHAR(30),
     ProfessionalOrg3       VARCHAR(30),
-    ProfessionalOrg4Number VARCHAR(30),
+    ProfessionalOrg3Number VARCHAR(30),
     AccountNumber VARCHAR(30),
@@ Line 124: / Line 125: @@
     Address3       VARCHAR(255),
     City           VARCHAR(255),
-    StateRegion    VARCHAR(100),
+    StateProv      VARCHAR(100),
     Country        VARCHAR(40),
     PostalCode     VARCHAR(30),
-    PhomeHome      VARCHAR(20),
+    PhoneHome      VARCHAR(20),
-    PhomeWork      VARCHAR(20),
+    PhoneWork      VARCHAR(20),
-    PhomeMobile    VARCHAR(20),
+    PhoneMobile    VARCHAR(20),
     Fax            VARCHAR(20),
     Email          VARCHAR(50),
@@ Line 187: / Line 188: @@
 </code>
-=== Attendance ===
+=== Registration ===
 <code sql>
 /******************************************************************************/
@@ Line 197: / Line 198: @@
 /*                                   Tables                                   */
 /******************************************************************************/
-CREATE TABLE ATTENDANCE (
+CREATE TABLE REGISTRATION (
     ATTENDEERECID        INTEGER NOT NULL,
     WORKSHOPRECID        INTEGER NOT NULL,
@@ Line 207: / Line 208: @@
     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,
+    REGISTRATIONConfirmation SMALLINT,
-	Notes                  BLOB SUB_TYPE 1 SEGMENT SIZE 2048,
+    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                                 */
+/******************************************************************************/
+</code>
+=== Attendance ===
+<code sql>
+/******************************************************************************/
+/*                 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
@@ Line 227: / Line 279: @@
 ALTER TABLE ATTENDANCE
   ADD CONSTRAINT PK_ATTENDANCE
-  PRIMARY KEY (ATTENDEERECID, WORKSHOPRECID);
+  PRIMARY KEY (ATTENDEERECID, WORKSHOPRECID, EventDate);
 /******************************************************************************/
@@ Line 239: / Line 291: @@
 ALTER TABLE ATTENDANCE
-  ADD CONSTRAINT FK_WORKSHOP_ATTENDEE
+  ADD CONSTRAINT FK_ATTENDANCE_WORKSHOP
   FOREIGN KEY (WORKSHOPRECID)
   REFERENCES WORKSHOP (RECID)
     ON DELETE CASCADE ON UPDATE CASCADE;
 /******************************************************************************/
 /*                                  Triggers                                  */
@@ Line 280: / Line 332: @@
 == 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.
+<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;
+    }
+}
+</code>