Docstoc

Connect 2 Database Schema

Document Sample
Connect 2 Database Schema Powered By Docstoc
					                             A Consortium for Innovative Career
                           and Workforce Development Resources




                                                                   VTECS C ONNECT 2.0




                                                           D ATABASE S CHEMA D OCUMENT



                Date:    November 26, 2002
             Version:    1.01
               Status:   Active




Database Schema Document                                                    Page 1 of 40
                                       A Consortium for Innovative Career
                                     and Workforce Development Resources


                                                            Table of Contents
    1.1    Table Specifications ................................................................................................................................ 3
          1.1.1        tblCatalog .................................................................................................................................... 3
          1.1.2        tblRace......................................................................................................................................... 4
          1.1.3        tblGrade ...................................................................................................................................... 5
          1.1.4        tblRelatedInstruction ................................................................................................................. 6
          1.1.5        tblTeacherType........................................................................................................................... 7
          1.1.6        tblSize .......................................................................................................................................... 8
          1.1.7        tblTermType ............................................................................................................................... 9
          1.1.8        tblTrainingAgreementType ..................................................................................................... 10
          1.1.9        tblContactType ......................................................................................................................... 11
          1.1.10       tblWorkWageType ................................................................................................................... 12
          1.1.11       tblQA ......................................................................................................................................... 13
          1.1.12       tblComment .............................................................................................................................. 14
          1.1.13       TblStudentComment ................................................................................................................ 15
          1.1.14       tblContact.................................................................................................................................. 16
          1.1.15       tblStudent .................................................................................................................................. 17
          1.1.16       tblTeacher ................................................................................................................................. 20
          1.1.17       tblOrganization ........................................................................................................................ 21
          1.1.18       tblSchool .................................................................................................................................... 23
          1.1.19       tblUserDefined .......................................................................................................................... 24
          1.1.20       tblTerm...................................................................................................................................... 25
          1.1.21       tblTrainingAgreement ............................................................................................................. 26
          1.1.22       tblEmployment ......................................................................................................................... 27
          1.1.23       tblTrainingPlan ........................................................................................................................ 28
          1.1.24       tblPlanNode .............................................................................................................................. 30
          1.1.25       tblStudentTrainingPlan ........................................................................................................... 31
          1.1.26       tblStudentProgress ................................................................................................................... 32
          1.1.27       tblStudentWorkWage .............................................................................................................. 33
          1.1.28       tblUser ....................................................................................................................................... 34
          1.1.29       tblUserSetting ........................................................................................................................... 37
          1.1.30       tblDesktop ................................................................................................................................. 37
          1.1.31       tblSystem ................................................................................................................................... 38
          1.1.32       tblProgressColumn ................................................................................................................... 39
          1.1.33       tblTooltip................................................................................................................................... 40
          1.1.34       tblHTML ................................................................................................................................... 40
          1.1.35       vtecs_temp ................................................................................................................................. 40




                                                                                                                                                                              Formatted: Bullets and Numbering




Database Schema Document                                                                                                                                        Page 2 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


3.11.1 Table Specifications
The following tables are practical implements of the database contained in the V-TECS Connect 2.0
application.

                                                                                                                    Formatted: Bullets and Numbering
3.1.11.1.1 tblCatalog

3.1.1.11.1.1.1 Specifications
The table tblCatalog contains catalog questions that may be assigned to several entities

 Field Name                              Type             Size    Unique –   Required   Note
                                                                  Non
                                                                  Unique
                                                                  Index
 CatalogID                               Int                      Unique     Yes        Primary key. System-
                                                                                        generated number.
                                                                                        Primary key to
                                                                                        identify a catalog
 CatalogType                             Char             1                  Yes        Either System or
                                                                                        Custom. This field is
                                                                                        ‘S’ or ‘C’
 QuestionType                            Char             1                  Yes        Either Yes/No, Text,
                                                                                        Selection, Multi-
                                                                                        Select or Memo. This
                                                                                        field is ‘Y’, ’T’, ‘S’,
                                                                                        ‘M’, ‘E’
 EntityType                              Char             1                  No         Either Student,
                                                                                        Organization, School,
                                                                                        Teacher, or All. This
                                                                                        field is ‘S’, ’O’, ‘C’,
                                                                                        ‘T’, ‘A’
 QuestionText                            Varchar          250                No         The actual question
 DefaultAnswer                           Text                                No         Default answer (if
                                                                                        applicable)
 Force                                   Bit                                 Yes        Must the user answer
                                                                                        the question or can
                                                                                        they leave it blank.
 Active                                  Bit                                 Yes        Record active or
                                                                                        inactive?
 GlobalFlag                              bit                                 Yes        Is the question a
                                                                                        global custom
                                                                                        question?
 OrganizationID                          Int                                 No         Foreign Key
 SchoolID                                Int                                 No         Foreign Key
 TeacherID                               Int                                 No         Foreign Key
 CreatedDate                             Datetime                            Yes
 LastUpdatedDate                         Datetime                            No
 CreatedBy                               Int                                 Yes        Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                           Int                                 No         Foreign Key. User
                                                                                        who this record is
                                                                                        updated by




Database Schema Document                                                                                 Page 3 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.1.21.1.1.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 TblQA                   1-n        Yes                         No                 No
 TblSchool               n-1        Yes                         No                 No
 TblTeacher              n-1        Yes                         No                 No
 tblOrganization         n-1        Yes                         No                 No
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 TblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 tblUserDefined          1-n        Yes                         No                 No


                                                                                                                    Formatted: Bullets and Numbering
3.1.21.1.2 tblRace

3.1.2.11.1.2.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 RaceID                                 Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 RaceName                               Varchar          50                 Yes         Name of race.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should




Database Schema Document                                                                                 Page 4 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.2.21.1.2.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 TblStudent              1-n        Yes                         No                 No

                                                                                                                    Formatted: Bullets and Numbering
3.1.21.1.3 tblGrade

3.1.2.11.1.3.1 Specification

 Field Name                               Type           Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 GradeID                                  Int                    Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 GradeName                                Varchar        50                 Yes         Name of grade.
 CreatedDate                              Datetime                          Yes
 LastUpdatedDate                          Datetime                          No
 CreatedBy                                Int                               Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                            Int                               No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 GradeOrder                               Int                               Yes         Keep GradeName
                                                                                        column in defined -
                                                                                        Order
 Designator                               Uniqueiden                        No          system-generated
                                          tifier                                        GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                              Uniqueiden                        No          system-generated
                                          tifier                                        GUID (Global




Database Schema Document                                                                                 Page 5 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                   Formatted: Bullets and Numbering
3.1.2.21.1.3.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 TblStudent              1-n        Yes                         No                 No

                                                                                                                   Formatted: Bullets and Numbering
3.1.21.1.4 tblRelatedInstruction

3.1.2.11.1.4.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 InstructionID                          Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 InstructionName                        Varchar          50                 Yes         Name of related
                                                                                        instruction.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                             Uniqueident                         No          Generated by Insert
                                        ifier                                           statement using
                                                                                        NEWID() function.
 Change_Flag                            Uniqueident                         No          Generated by Insert,
                                        ifier                                           Update statements
                                                                                        using NEWID()
                                                                                        function.
                                                                                                                   Formatted: Bullets and Numbering
3.1.2.21.1.4.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 TblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 tblStudent              1-n        Yes                         No                 No




Database Schema Document                                                                                Page 6 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources

                                                                                                                    Formatted: Bullets and Numbering
3.1.21.1.5 tblTeacherType

3.1.2.11.1.5.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 TchTypeID                              Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 TchTypeName                            Varchar          50                 Yes         Name of Teacher
                                                                                        Type.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.2.21.1.5.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 tblTeacher              1-n        Yes                         No                 No

                                                                                                                    Formatted: Bullets and Numbering




Database Schema Document                                                                                 Page 7 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


3.1.21.1.6 tblSize

3.1.2.11.1.6.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 SizeID                                 Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 SizeName                               Varchar          50                 Yes         Name of Size.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 SizeOrder                              Int                                 Yes         Keep SizeName
                                                                                        column in defined -
                                                                                        Order
 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.2.21.1.6.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 tblOrganization         1-n        Yes                         No                 No

                                                                                                                    Formatted: Bullets and Numbering




Database Schema Document                                                                                 Page 8 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


3.1.21.1.7 tblTermType

3.1.2.11.1.7.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 TrmTypeID                              Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 TrmTypeName                            Varchar          50                 Yes         Name of Term Type.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing

 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.2.21.1.7.2 Relationships

 Table                   Type       Enforce Referential         Cascade Update     Cascade Delete Related
                                    Integrity                   Related Fields     Records
 tblUser                 n-1        Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1        Yes                         No                 No
 (LastUpdatedBy)
 tblSchool               1-n        Yes                         No                 No

                                                                                                                    Formatted: Bullets and Numbering




Database Schema Document                                                                                 Page 9 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


3.1.21.1.8 tblTrainingAgreementType

3.1.2.11.1.8.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 TrnAgrmtTypeID                         Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 TrnAgrmtTypeName                       Varchar          50                 Yes         Name of Training
                                                                                        Agreement Type.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.2.21.1.8.2 Relationships

 Table                         Type    Enforce Referential      Cascade Update     Cascade Delete Related
                                       Integrity                Related Fields     Records
 tblUser (CreatedBy)           n-1     Yes                      No                 No
 tblUser                       n-1     Yes                      No                 No
 (LastUpdatedBy)
 tblTrainingAgreement          1-n     Yes                      No                 No

                                                                                                                    Formatted: Bullets and Numbering




Database Schema Document                                                                                Page 10 of 40
                                 A Consortium for Innovative Career
                               and Workforce Development Resources


3.1.21.1.9 tblContactType

3.1.2.11.1.9.1 Specification

 Field Name                             Type             Size    Unique /   Required    Note
                                                                 Non
                                                                 Unique
                                                                 Index
 CntTypeID                              Int                      Unique     Yes         Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 CntTypeName                            Varchar          50                 Yes         Name of Contact
                                                                                        Type.
 StudentFlag                            Char             1                  Yes         To know whether this
                                                                                        is Emergency Contact
                                                                                        Type. Either
                                                                                        Organization, Student
                                                                                        or School. This field
                                                                                        is ‘S’, ‘O’, ‘C’.
 CreatedDate                            Datetime                            Yes
 LastUpdatedDate                        Datetime                            No
 CreatedBy                              Int                                 Yes         Foreign Key. User
                                                                                        inputs this record.
 LastUpdatedBy                          Int                                 No          Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                             Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                            Uniqueident                         No          system-generated
                                        ifier                                           GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                    Formatted: Bullets and Numbering
3.1.2.21.1.9.2 Relationships

 Table                         Type    Enforce Referential      Cascade Update     Cascade Delete Related
                                       Integrity                Related Fields     Records
 tblUser (CreatedBy)           n-1     Yes                      No                 No
 tblUser                       n-1     Yes                      No                 No
 (LastUpdatedBy)
 tblContact                    1-n     Yes                      No                 No




Database Schema Document                                                                                Page 11 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources

                                                                                                                     Formatted: Bullets and Numbering
3.1.21.1.10 tblWorkWageType

3.1.2.11.1.10.1 Specification

 Field Name                              Type             Size    Unique /   Required    Note
                                                                  Non
                                                                  Unique
                                                                  Index
 WrkWgTypeID                             Int                      Unique     Yes         Primary key. System-
                                                                                         generated number
                                                                                         (identity).
 WrkWgTypeName                           Varchar          50                 Yes         Name of Work Wage
                                                                                         Type.
 CreatedDate                             Datetime                            Yes
 LastUpdatedDate                         Datetime                            No
 CreatedBy                               Int                                 Yes         Foreign Key. User
                                                                                         inputs this record.
 LastUpdatedBy                           Int                                 No          Foreign Key. User
                                                                                         who this record is
                                                                                         updated by
 Designator                              Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and
                                                                                         importing
 Change_Flag                             Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Change_Flag will be
                                                                                         changed when record
                                                                                         is modified.
                                                                                                                     Formatted: Bullets and Numbering
3.1.2.21.1.10.2 Relationships

 Table                      Type        Enforce Referential      Cascade Update     Cascade Delete Related
                                        Integrity                Related Fields     Records
 tblUser (CreatedBy)        n-1         Yes                      No                 No
 tblUser                    n-1         Yes                      No                 No
 (LastUpdatedBy)
 tblStudentWorkWage         1-n         Yes                      No                 No




Database Schema Document                                                                                 Page 12 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


1.1.11   tblQA

1.1.11.1 Specification

 Field Name                              Type             Size    Unique /   Required    Note
                                                                  Non
                                                                  Unique
                                                                  Index
 QAID                                    Int                      Unique     Yes         Primary key. System-
                                                                                         generated number
                                                                                         (identity).
 QAText                                  Text                                No          Possible answers
 CatalogID                               Int                                 Yes         Foreign Key.
 CreatedDate                             Datetime                            Yes
 LastUpdatedDate                         Datetime                            No
 CreatedBy                               Int                                 Yes         Foreign Key. User
                                                                                         inputs this record.
 LastUpdatedBy                           Int                                 No          Foreign Key. User
                                                                                         who this record is
                                                                                         updated by
 Designator                              Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and
                                                                                         importing
 Change_Flag                             Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Change_Flag will be
                                                                                         changed when record
                                                                                         is modified.
                                                                                                                     Formatted: Bullets and Numbering
3.1.2.21.1.11.2 Relationships

 Table                   Type        Enforce Referential         Cascade Update     Cascade Delete Related
                                     Integrity                   Related Fields     Records
 tblUser                 n-1         Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1         Yes                         No                 No
 (LastUpdatedBy)
 tblCatalog              n-1         Yes                         No                 No


                                                                                                                     Formatted: Bullets and Numbering




Database Schema Document                                                                                 Page 13 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


3.1.31.1.12 tblComment

3.1.3.11.1.12.1 Specifications
The table tblComment contains Comment records used by several entities

 Field Name                              Type                Size   Unique –   Required   Note
                                                                    Non
                                                                    Unique
                                                                    Index
 CommentID                               Int                        Unique     Yes        Primary key.
                                                                                          System-generated
                                                                                          number. Primary
                                                                                          key to identify a
                                                                                          comment
 Comment                                 Text                                  Yes        Whatever comments
                                                                                          the user types in
 OrganizationID                          Int                                   No         Foreign Key
 SchoolID                                Int                                   No         Foreign Key
 TeacherID                               int                                   No         Foreign Key For
                                                                                          future use only.
 CreatedDate                             Datetime                              Yes
 LastUpdatedDate                         Datetime                              No
 CreatedBy                               Int                                   Yes        Foreign Key. User
                                                                                          inputs this record.
 LastUpdatedBy                           Int                                   No         Foreign Key. User
                                                                                          who this record is
                                                                                          updated by
 Designator                              Uniqueident                           No         system-generated
                                         ifier                                            GUID (Global
                                                                                          Unique Identifier) -
                                                                                          Designator should
                                                                                          never change when
                                                                                          exporting and
                                                                                          importing
 Change_Flag                             Uniqueident                           No         system-generated
                                         ifier                                            GUID (Global
                                                                                          Unique Identifier) -
                                                                                          Change_Flag will be
                                                                                          changed when
                                                                                          record is modified.
                                                                                                                     Formatted: Bullets and Numbering
3.1.3.21.1.12.2 Relationships
 Table                            Type         Enforce              Cascade Update    Cascade Delete Related
                                               Referential          Related Fields    Records
                                               Integrity
 tblSchool                   1-1      Yes                No                   No
 tblOrganization             1-1      Yes                No                   No
 tblTeacher                  1-1      Yes                No                   No
 tblUser (CreatedBy)         n-1      Yes                No                   No
 tblUser (LastUpdatedBy) n - 1        Yes                No                   No
Note: There is a check Constraint: (OrganizatonID is not null ) Or (SchoolID is not null)




Database Schema Document                                                                                 Page 14 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources

                                                                                                                     Formatted: Bullets and Numbering
3.1.41.1.13 TblStudentComment

3.1.4.11.1.13.1 Specifications
The table tblStudentComment contains Comment records used by several entities

 Field Name                              Type                Size   Unique –   Required   Note
                                                                    Non
                                                                    Unique
                                                                    Index
 StdCommentID                            Int                        Unique     Yes        Primary key.
                                                                                          System-generated
                                                                                          number. Primary
                                                                                          key to identify a
                                                                                          comment
 PublicComment                           Text                                  No         Whatever comments
                                                                                          the user types in
 PrivateComment                          Text                                  No         Whatever comments
                                                                                          the user types in
 StudentID                               Int                                   Yes        Foreign Key
 CreatedDate                             Datetime                              Yes
 LastUpdatedDate                         Datetime                              No
 CreatedBy                               Int                                   Yes        Foreign Key. User
                                                                                          inputs this record.
 LastUpdatedBy                           Int                                   No         Foreign Key. User
                                                                                          who this record is
                                                                                          updated by
 Designator                              Uniqueident                           No         system-generated
                                         ifier                                            GUID (Global
                                                                                          Unique Identifier) -
                                                                                          Designator should
                                                                                          never change when
                                                                                          exporting and
                                                                                          importing
 Change_Flag                             Uniqueident                           No         system-generated
                                         ifier                                            GUID (Global
                                                                                          Unique Identifier) -
                                                                                          Change_Flag will be
                                                                                          changed when
                                                                                          record is modified.
                                                                                                                     Formatted: Bullets and Numbering
3.1.4.21.1.13.2 Relationships
 Table                            Type         Enforce              Cascade Update    Cascade Delete Related
                                               Referential          Related Fields    Records
                                               Integrity
 tblUser (CreatedBy)         n-1      Yes              No                    No
 tblUser (LastUpdatedBy) n - 1        Yes              No                    No
 tblStudent                  1-1      Yes              No                    No
Note: There is a check Constraint: (PublicComment is not null ) Or (PrivateComment is not null)




Database Schema Document                                                                                 Page 15 of 40
                               A Consortium for Innovative Career
                             and Workforce Development Resources

                                                                                                               Formatted: Bullets and Numbering
3.1.51.1.14 tblContact

3.1.5.11.1.14.1 Specifications
The table tblContact contains contacts used by several entities

 Field Name                           Type             Size     Unique –   Required   Note
                                                                Non
                                                                Unique
                                                                Index
 ContactID                            Int                       Unique     Yes        Primary key.
                                                                                      System-generated
                                                                                      number. Primary
                                                                                      key to identify a
                                                                                      contact
 FirstName                            Varchar          25                  Yes        Contact’s last name
 LastName                             Varchar          30                  Yes        Contact’s First
                                                                                      Name
 MiddleName                           Varchar          1                   No         Contact’s Middle
                                                                                      Initial
 Address                              Varchar          50                  No         Emergency
                                                                                      Contact’s Address
 CityStateZip                         Varchar          45                  No         Emergency
                                                                                      Contact’s City,
                                                                                      State, and Zip
 JobTitle                             Varchar          45                  No         Contact’s Job Title
 Phone                                Varchar          20                  No         Contact’s Phone
                                                                                      Number or
                                                                                      Emergency
                                                                                      Contact’s Home
                                                                                      Phone Number
 Employer                             Varchar          50                  No         Emergency
                                                                                      Contact’s
                                                                                      Employer’s Name
 WorkPhone                            Varchar          20                  No         Emergency
                                                                                      Contact’s
                                                                                      Employer’s Work
                                                                                      Phone
 Fax                                  Varchar          20                  No         Contact’s Fax
                                                                                      Number
 Email                                Varchar          75                  No         Internet Email
                                                                                      Address for the
                                                                                      Contact or
                                                                                      Emergency Contact
 SchoolID                             Int                                  No         Foreign Key
 CntTypeID                            Int                                  No         Foreign Key
 OrganizationID                       Int                                  No         Foreign Key or
                                                                                      Emergency
                                                                                      Contact’s
                                                                                      Employer’s Name
 DefaultFlag                          Bit                                  Yes        To know whether
                                                                                      this contact is
                                                                                      default.




Database Schema Document                                                                           Page 16 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


 StudentID                               Int                                  No         Foreign Key
 CreatedDate                             Datetime                             Yes
 LastUpdatedDate                         Datetime                             No
 CreatedBy                               Int                                  Yes        Foreign Key. User
                                                                                         inputs this record.
 LastUpdatedBy                           Int                                  No         Foreign Key. User
                                                                                         who this record is
                                                                                         updated by
 Designator                              Uniqueident                          No         system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and
                                                                                         importing
 Change_Flag                             Uniqueident                          No         system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Change_Flag will be
                                                                                         changed when
                                                                                         record is modified.
                                                                                                                   Formatted: Bullets and Numbering
3.1.5.21.1.14.2 Relationships

 Table                   Type        Enforce Referential         Cascade Update     Cascade Delete Related
                                     Integrity                   Related Fields     Records
 tblUser                n-1     Yes                       No                  No
 (CreatedBy)
 tblUser                n-1     Yes                       No                  No
 (LastUpdatedBy)
 tblSchool              n-1     Yes                       No                  No
 tblContactType         n-1     Yes                       No                  No
 tblOrganization        n-1     Yes                       No                  No
 tblStudent             n-1     Yes                       No                  No
 tblEmployment          1-n     Yes                       No                  No
 tblEmployment          1-n     Yes                       No                  No
Note: There is a check Constraint: (SchoolID is not null ) Or (OrganizationID is not null) Or
(StudentID is not null)

                                                                                                                   Formatted: Bullets and Numbering
3.1.61.1.15 tblStudent

3.1.6.11.1.15.1 Specifications
The table tblStudent contains student’s information

 Field Name                              Type             Size     Unique /   Required   Note
                                                                   Non
                                                                   Unique
                                                                   Index
 StudentID                               Int                       Unique     Yes        Primary key.
                                                                                         System-generated
                                                                                         number (identity).
 TermID                                  Int                                  No         Foreign Key.




Database Schema Document                                                                               Page 17 of 40
                             A Consortium for Innovative Career
                           and Workforce Development Resources


 FirstName                          Varchar          25           Yes   Student’s First
                                                                        Name
 LastName                           Varchar          30           Yes   Student’s Last Name
 MiddleName                         Varchar          1            No    Student’s Middle
                                                                        Initial
 Address1                           Varchar          50           No    Student’s Address
                                                                        line 1
 Address2                           Varchar          50           No    Student’s Address
                                                                        line 2
 City                               Varchar          30           No    Student’s City
 State                              Varchar          2            No    Student’s State
 Zip                                Varchar          10           No    Student’s Zip
 RaceID                             Int                           No    Foreign Key.
                                                                        Student’s Race
 Gender                             Char             1            No    Student’s Gender
                                                                        (Male/Female). This
                                                                        field is ‘M’ or ‘F’.
 SchoolID                           Int                           No    Foreign Key.
                                                                        Student’s School’s
                                                                        Name
 TeacherID                          Int                           No    Foreign Key.
                                                                        Student’s Teacher’s
                                                                        Name
 CoordinatorID                      Int                           No    Foreign Key.
                                                                        Student’s
                                                                        Coordinator Name
 TrnAgreementID                     Int                           No    Foreign Key.
                                                                        Training Agreement
                                                                        the Student is
                                                                        currently under
 SSNO                               Varchar          25           No    Social Security
                                                                        Number (May not
                                                                        actually be SSN)
 OtherID                            Varchar          50           No    Student’s
                                                                        Identification
                                                                        Number 2
 Phone                              Varchar          20           No    Student’s Home
                                                                        Phone Number
 Fax                                Varchar          20           No    Student’s Fax
                                                                        Number
 Email                              Varchar          75           No    Internet Email
                                                                        Address of the
                                                                        Student
 EmergencyPhone                     Varchar          20           No    Student’s Main
                                                                        Emergency Phone
 DOB                                Datetime                      No    Student’s Birth Date
 InstructionID                      Int                           No    Foreign Key.
                                                                        Student’s Related
                                                                        Instruction
 GradeID                            Int                           No    Foreign Key.
                                                                        Student’s Current




Database Schema Document                                                             Page 18 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


                                                                                         Grade
 Active                                  Bit                                 Yes         Record active or
                                                                                         inactive?
 CreatedBy                               Int                                 Yes         This is created by
 CreatedDate                             Datetime                            Yes
 LastUpdatedDate                         Datetime                            No
 UserID                                  Int                                 Yes         This is assigned to
                                                                                         which user.
 LastUpdatedBy                           Int                                 No          Foreign Key. User
                                                                                         who this record is
                                                                                         updated by
 Designator                              Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and
                                                                                         importing
 Change_Flag                             Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Change_Flag will be
                                                                                         changed when
                                                                                         record is modified.
                                                                                                                   Formatted: Bullets and Numbering
3.1.6.21.1.15.2 Relationships
 Table                          Typ     Enforce Referential        Cascade Update   Cascade Delete Related
                                e       Integrity                  Related Fields   Records
 tblTeacher                     n-1     Yes                        No               No
 tblTeacher                     n-1     Yes                        No               No
 (Coordinator)
 tblSchool                      n-1     Yes                        No               No
 tblRace                        n-1     Yes                        No               No
 tblGrade                       n-1     Yes                        No               No
 tblRelated Instruction         n-1     Yes                        No               No
 tblContact                     n-1     Yes                        No               No
 tblTrainingAgreement           n-1     Yes                        No               No
 (Training Type)
 tblStudentComment              1-1     Yes                        No               No
 tblTerm                        n-1     Yes                        No               No
 tblUser (UserID)               n-1     Yes                        No               No
 tblUser (CreatedBy)            n-1     Yes                        No               No
 tblUser                        n-1     Yes                        No               No
 (LastUpdatedBy)
 tblUserDefined                 1-n     Yes                        No               No
 tblEmployment                  1-n     Yes                        No               No

                                                                                                                   Formatted: Bullets and Numbering




Database Schema Document                                                                               Page 19 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


3.1.71.1.16 tblTeacher

3.1.7.11.1.16.1 Specification
The table tblTeacher contains teacher’s information.

 Field Name                          Type             Size     Unique /   Required   Note
                                                               Non
                                                               Unique
                                                               Index
 TeacherID                           Int                       Unique     Yes        Primary key.
                                                                                     System-generated
                                                                                     number (identity).
 FirstName                           Varchar          25                  Yes        Teacher’s First
                                                                                     Name
 LastName                            Varchar          30                  Yes        Teacher’s Last
                                                                                     Name
 MiddleName                          Varchar          1                   No         Teacher’s Middle
                                                                                     Initial
 Title                               Varchar          50                  No         Teacher’s title
 IDNo                                Varchar          25                  No         Teacher’s
                                                                                     Identification
                                                                                     Number
 Address1                            Varchar          50                  No         Teacher’s Address
                                                                                     line 1
 Address2                            Varchar          50                  No         Teacher’s Address
                                                                                     line 2
 City                                Varchar          30                  No         Teacher’s City
 State                               Varchar          2                   No         Teacher’s State
 Zip                                 Varchar          10                  No         Teacher’s Zip
 LocationID                          Int                                  No         Foreign Key.
                                                                                     Teacher’s normal
                                                                                     operating Location
 TchTypeID                           Int                                  No         Foreign Key.
                                                                                     Teacher Type
 Phone                               Varchar          20                  No         Teacher’s Phone
                                                                                     Number
 Fax                                 Varchar          20                  No         Teacher’s Fax
                                                                                     Number
 Email                               Varchar          75                  No         Internet Email
                                                                                     Address of the
                                                                                     Teacher
 Active                              Bit                                  Yes        Record active or
                                                                                     inactive?
 CreatedDate                         Datetime                             Yes
 LastUpdatedDate                     Datetime                             No
 CreatedBy                           Int                                  Yes        Foreign Key. User
                                                                                     who this record is
                                                                                     created by
 LastUpdatedBy                       Int                                  No         Foreign Key. User
                                                                                     who this record is
                                                                                     updated by




Database Schema Document                                                                          Page 20 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


 Designator                              Uniqueident                          No         system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and
                                                                                         importing
 Change_Flag                             Uniqueident                          No         system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Change_Flag will be
                                                                                         changed when
                                                                                         record is modified.
                                                                                                                   Formatted: Bullets and Numbering
3.1.7.21.1.16.2 Relationships

 Table                  Type         Enforce Referential         Cascade Update     Cascade Delete Related
                                     Integrity                   Related Fields     Records
 tblUser                n-1          Yes                         No                 No
 (CreatedBy)
 tblUser                n-1          Yes                         No                 No
 (LastUpdatedBy)
 tblSchool              n-1          Yes                         No                 No
 tblTeacher Type        n-1          Yes                         No                 No
 tblUserDefined         1-n          Yes                         No                 No
 tblStudent             1-n          Yes                         No                 No
 (Teacher)
 tblStudent             1-n          Yes                         No                 No
 (Coordinator)
 tblComment             1-1          Yes                         No                 No
 tblCatalog             1-n          Yes                         No                 No
                                                                                                                   Formatted: Bullets and Numbering
3.1.81.1.17 tblOrganization

3.1.8.11.1.17.1 Specification
The table tblOrganization contains organization’s information.

 Field Name                              Type             Size     Unique /   Required   Note
                                                                   Non
                                                                   Unique
                                                                   Index
 OrganizationID                          Int                       Unique     Yes        Primary key. System-
                                                                                         generated number
                                                                                         (identity).
 OrgName                                 Varchar          100                 Yes        Organization’s Name
 Address1                                Varchar          50                  No         Organization’s
                                                                                         Address line 1
 Address2                                Varchar          50                  No         Organization’s
                                                                                         Address line 2
 City                                    Varchar          30                  No         Organization’s City
 State                                   Varchar          2                   No         Organization’s State
 Zip                                     Varchar          10                  No         Organization’s Zip




Database Schema Document                                                                               Page 21 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


                                                                                        Code
 EIN                                     Varchar          25                 No         Organization’s
                                                                                        Identification
                                                                                        Number
 SizeID                                  Int                                 No         Foreign Key.
                                                                                        Organization’s Size
 Phone                                   Varchar          20                 No         Organization’s Phone
                                                                                        Number
 Fax                                     Varchar          20                 No         Organization’s Fax
                                                                                        Number
 Email                                   Varchar          75                 No         Internet Email
                                                                                        Address of the
                                                                                        Organization
 Active                                  Bit                                 Yes        Record active or
                                                                                        inactive?
 CreatedDate                             Datetime                            Yes
 LastUpdatedDate                         Datetime                            No
 CreatedBy                               Int                                 Yes        Foreign Key. User
                                                                                        who this record is
                                                                                        created by
 LastUpdatedBy                           Int                                 No         Foreign Key. User
                                                                                        who this record is
                                                                                        updated by
 Designator                              Uniqueident                         No         system-generated
                                         ifier                                          GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                             Uniqueident                         No         system-generated
                                         ifier                                          GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
                                                                                                                   Formatted: Bullets and Numbering
3.1.8.21.1.17.2 Relationships

 Table                  Type         Enforce Referential         Cascade Update    Cascade Delete Related
                                     Integrity                   Related Fields    Records
 tblUser                n-1          Yes                         No                No
 (CreatedBy)
 tblUser                n-1          Yes                         No                No
 (LastUpdatedBy)
 tblSize                n-1          Yes                         No                No
 tblCatalog             1-n          Yes                         No                No
 tblUserDefined         1-n          Yes                         No                No
 tblContact             1-n          Yes                         No                No
 tblComment             1-1          Yes                         No                No
 tblEmployment          1-n          Yes                         No                No




Database Schema Document                                                                               Page 22 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources

                                                                                                                Formatted: Bullets and Numbering
3.1.91.1.18 tblSchool

3.1.9.11.1.18.1 Specification
The table tblSchool contains school’s information.

 Field Name                          Type             Size     Unique /   Required   Note
                                                               Non
                                                               Unique
                                                               Index
 SchoolID                            Int                       Unique     Yes        Primary key. System-
                                                                                     generated number
                                                                                     (identity).
 SchoolName                          Varchar          100                 Yes        School’s Name
 Address1                            Varchar          50                  No         School’s Address line
                                                                                     1
 Address2                            Varchar          50                  No         School’s Address line
                                                                                     2
 City                                Varchar          30                  No         School’s City
 State                               Varchar          2                   No         School’s State
 Zip                                 Varchar          10                  No         School’s Zip
 Agency                              Int                                  No         Foreign Key.
                                                                                     School’s Agency
                                                                                     Name
 Phone                               Varchar          20                  No         School’s Phone
                                                                                     Number
 Fax                                 Varchar          20                  No         School’s Fax Number
 Email                               Varchar          75                  No         Internet Email
                                                                                     Address of School
 TrmTypeID                           Int                                  No         Foreign Key. The
                                                                                     Term Type the
                                                                                     School uses
 Active                              Bit                                  Yes        Record active or
                                                                                     inactive?
 CreatedDate                         Datetime                             Yes
 LastUpdatedDate                     Datetime                             No
 CreatedBy                           Int                                  Yes        Foreign Key. User
                                                                                     who this record is
                                                                                     created by
 LastUpdatedBy                       Int                                  No         Foreign Key. User
                                                                                     who this record is
                                                                                     updated by
 Designator                          Uniqueident                          No         system-generated
                                     ifier                                           GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Designator should
                                                                                     never change when
                                                                                     exporting and
                                                                                     importing
 Change_Flag                         Uniqueident                          No         system-generated
                                     ifier                                           GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Change_Flag will be




Database Schema Document                                                                            Page 23 of 40
                                  A Consortium for Innovative Career
                                and Workforce Development Resources


                                                                                          changed when record
                                                                                          is modified.
                                                                                                                     Formatted: Bullets and Numbering
3.1.9.21.1.18.2 Relationships

 Table                   Type        Enforce Referential         Cascade Update     Cascade Delete Related
                                     Integrity                   Related Fields     Records
 tblUser                 n-1         Yes                         No                 No
 (CreatedBy)
 tblUser                 n-1         Yes                         No                 No
 (LastUpdatedBy)
 tblSchool (Agency)      n-1         Yes                         No                 No
 tblCatalog              1-n         Yes                         No                 No
 tblTermType             n-1         Yes                         No                 No
 tblUserDefined          1-n         Yes                         No                 No
 tblTeacher              1-n         Yes                         No                 No
 tblStudent              1-n         Yes                         No                 No
 tblContact              1-n         Yes                         No                 No
 tblComment              1-1         Yes                         No                 No

                                                                                                                     Formatted: Bullets and Numbering
3.1.101.1.19 tblUserDefined

3.1.10.11.1.19.1 Specification
The table tblUserDefined contains User-Defined Catalog Answers

 Field Name                              Type             Size    Unique /   Required    Note
                                                                  Non
                                                                  Unique
                                                                  Index
 UserDefinedID                           Int                      Unique     Yes         Primary key. System-
                                                                                         generated number
                                                                                         (identity).
 CatalogID                               Int                                 Yes         Foreign Key
 AnswerText                              text                                No          The actual answer
 StudentID                               Int                                 No          Foreign Key.
 OrganizationID                          Int                                 No          Foreign Key.
 SchoolID                                Int                                 No          Foreign Key.
 TeacherID                               Int                                 No          Foreign Key.
 CreatedDate                             Datetime                            Yes
 LastUpdatedDate                         Datetime                            No
 CreatedBy                               Int                                 Yes         Foreign Key. User
                                                                                         who this record is
                                                                                         created by
 LastUpdatedBy                           Int                                 No          Foreign Key. User
                                                                                         who this record is
                                                                                         updated by
 Designator                              Uniqueident                         No          system-generated
                                         ifier                                           GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and




Database Schema Document                                                                                 Page 24 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


                                                                                     importing
 Change_Flag                         Uniqueident                         No          system-generated
                                     ifier                                           GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Change_Flag will be
                                                                                     changed when record
                                                                                     is modified.
                                                                                                                Formatted: Bullets and Numbering
3.1.10.21.1.19.2 Relationships

 Table                  Type     Enforce Referential         Cascade Update     Cascade Delete Related
                                 Integrity                   Related Fields     Records
 tblUser                n-1      Yes                         No                 No
 (CreatedBy)
 tblUser                n-1      Yes                         No                 No
 (LastUpdatedBy)
 tblStudent             n-1      Yes                         No                 No
 tblSchool              n-1      Yes                         No                 No
 tblOrganization        n-1      Yes                         No                 No
 tblTeacher             n-1      Yes                         No                 No
 tblCatalog             n-1      Yes                         No                 No


                                                                                                                Formatted: Bullets and Numbering
3.1.111.1.20 tblTerm

3.1.11.11.1.20.1 Specification
The table tblTerm contains school Terms such as semesters, trimesters or quarters

 Field Name                          Type             Size    Unique /   Required    Note
                                                              Non
                                                              Unique
                                                              Index
 TermID                              Int                      Unique     Yes         Primary key. System-
                                                                                     generated number
                                                                                     (identity).
 Title                               Varchar          50                 Yes         School’s Term Title
 StartDate                           Datetime                            Yes         School’s Term Start
                                                                                     Date
 EndDate                             Datetime                            No          School’s Term End
                                                                                     Date
 Active                              Bit                                 Yes         Record active or
                                                                                     inactive?
 CreatedDate                         Datetime                            Yes
 LastUpdatedDate                     Datetime                            No
 CreatedBy                           Int                                 Yes         Foreign Key. User
                                                                                     who this record is
                                                                                     created by
 LastUpdatedBy                       Int                                 No          Foreign Key. User
                                                                                     who this record is
                                                                                     updated by
 Designator                          Uniqueident                         No          system-generated
                                     ifier                                           GUID (Global




Database Schema Document                                                                            Page 25 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


                                                                                     Unique Identifier) -
                                                                                     Designator should
                                                                                     never change when
                                                                                     exporting and
                                                                                     importing
 Change_Flag                         Uniqueident                         No          system-generated
                                     ifier                                           GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Change_Flag will be
                                                                                     changed when record
                                                                                     is modified.
                                                                                                                 Formatted: Bullets and Numbering
3.1.11.21.1.20.2 Relationships

 Table                  Type     Enforce Referential         Cascade Update     Cascade Delete Related
                                 Integrity                   Related Fields     Records
 tblUser                n-1      Yes                         No                 No
 (CreatedBy)
 tblUser                n-1      Yes                         No                 No
 (LastUpdatedBy)
 tblStudent             1-n      Yes                         No                 No


                                                                                                                 Formatted: Bullets and Numbering
3.1.121.1.21 tblTrainingAgreement

3.1.12.11.1.21.1 Specification
The table tblTrainingAgreement contains Training Agreement types and contracts

 Field Name                          Type             Size    Unique /   Required    Note
                                                              Non
                                                              Unique
                                                              Index
 TrnAgreementID                      Int                      Unique     Yes         Primary key. System-
                                                                                     generated number
                                                                                     (identity).
 TrnAgrmtTypeID                      Int                                 No          Foreign Key.
                                                                                     Training Agreement
                                                                                     Type
 AgreementText                       Text                                No          Training Agreement
                                                                                     Text
 AgrName                             Varchar          80                 Yes         Training Agreement
                                                                                     Name
 Active                              Bit                                 Yes         Record active or
                                                                                     inactive?
 CreatedDate                         Datetime                            Yes
 LastUpdatedDate                     Datetime                            No
 CreatedBy                           Int                                 Yes         Foreign Key. User
                                                                                     who this record is
                                                                                     created by
 LastUpdatedBy                       Int                                 No          Foreign Key. User
                                                                                     who this record is
                                                                                     updated by




Database Schema Document                                                                             Page 26 of 40
                               A Consortium for Innovative Career
                             and Workforce Development Resources


 Designator                           Uniqueident                            No          system-generated
                                      ifier                                              GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Designator should
                                                                                         never change when
                                                                                         exporting and
                                                                                         importing
 Change_Flag                          Uniqueident                            No          system-generated
                                      ifier                                              GUID (Global
                                                                                         Unique Identifier) -
                                                                                         Change_Flag will be
                                                                                         changed when record
                                                                                         is modified.
                                                                                                                      Formatted: Bullets and Numbering
3.1.12.21.1.21.2 Relationships

 Table                            Type       Enforce Referential        Cascade Update          Cascade Delete
                                             Integrity                  Related Fields          Related Records
 TblStudent                       1–n        Yes                        No                      No
 tblTrainingAgreementType         n–1        Yes                        No                      No
 tblUser (CreatedBy)              n–1        Yes                        No                      No
 tblUser (LastUpdatedBy)          n–1        Yes                        No                      No


                                                                                                                      Formatted: Bullets and Numbering
3.1.131.1.22 tblEmployment

3.1.13.11.1.22.1 Specification
The table tblEmployment contains student’s assigned Employers Information.

 Field Name                           Type             Size    Unique /      Required    Note
                                                               Non
                                                               Unique
                                                               Index
 EmploymentID                         Int                      Unique        Yes         Primary key. System-
                                                                                         generated number
                                                                                         (identity).
 StudentID                            Int                                    Yes         Foreign Key.
 OrganizationID                       Int                                    Yes         Foreign Key.
                                                                                         Student’s Employer’s
                                                                                         Organization Name
 ContactID                            Int                                    No          Foreign Key.
                                                                                         Student’s Employer’s
                                                                                         Contact Name
 SupervisorID                         Int                                    No          Foreign Key.
                                                                                         Student’s Employer’s
                                                                                         Supervisor Name
 JobTitle                             Varchar          45                    No
 StartDate                            Datetime                               No          Student’s Employer’s
                                                                                         Work Start Date
 EndDate                              Datetime                               No          Student’s Employer’s
                                                                                         Work End Date
 CreatedDate                          Datetime                               Yes




Database Schema Document                                                                                  Page 27 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


 LastUpdatedDate                     Datetime                            No
 CreatedBy                           Int                                 Yes         Foreign Key. User
                                                                                     who this record is
                                                                                     created by
 LastUpdatedBy                       Int                                 No          Foreign Key. User
                                                                                     who this record is
                                                                                     updated by
 Designator                          Uniqueident                         No          system-generated
                                     ifier                                           GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Designator should
                                                                                     never change when
                                                                                     exporting and
                                                                                     importing
 Change_Flag                         Uniqueident                         No          system-generated
                                     ifier                                           GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Change_Flag will be
                                                                                     changed when record
                                                                                     is modified.
                                                                                                                 Formatted: Bullets and Numbering
3.1.13.21.1.22.2 Relationships

 Table                    Type      Enforce Referential        Cascade Update       Cascade Delete Related
                                    Integrity                  Related Fields       Records
 tblUser (CreatedBy)      n-1       Yes                        No                   No
 tblUser                  n-1       Yes                        No                   No
 (LastUpdatedBy)
 tblStudent               n-1       Yes                        No                   No
 tblOrganization          n-1       Yes                        No                   No
 tblContact (Contact)     n-1       Yes                        No                   No
 tblContact               n-1       Yes                        No                   No
 (Supervisor)
 TblStudentTrainingPl     1-1       Yes                        No                   No
 an
 tblStudentWorkWage       1-n       Yes                        No                   No

                                                                                                                 Formatted: Bullets and Numbering
3.1.141.1.23 tblTrainingPlan

3.1.14.11.1.23.1 Specification
The table tblTrainingPlan contains Training Plans, Duties and Tasks

 Field Name                          Type             Size    Unique /   Required    Note
                                                              Non
                                                              Unique
                                                              Index
 TrainingPlanID                      Int                      Unique     Yes         Primary key. System-
                                                                                     generated number
                                                                                     (identity).
 Title                               Varchar          50                 Yes         Training Plan Title
 Comments                            text                                No          Training Plan
                                                                                     Comments




Database Schema Document                                                                             Page 28 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


 CreatedBy                              Int                               No        Foreign Key.
                                                                                    Training Plan Created
                                                                                    By (user)
 Active                                 Bit                               Yes       Record active or
                                                                                    inactive?
 TrainingPlanType                       Char           1                  Yes       Either Training Plan,
                                                                                    Task List or Student
                                                                                    Training Plan. This
                                                                                    field is ‘T’, ‘L’ or
                                                                                    ‘S’.
 VtecsOccupationID                      Varchar        10                 No        VTECS Occupation
                                                                                    Code
 VtecsTaskListID                        Varchar        10                 No        VTECS Training
                                                                                    Plan Code
 OldTaskListID                          Varchar        10                 No        Keep
                                                                                    VtecsTaskListID
                                                                                    fields from Legacy
                                                                                    System(CONNECT
                                                                                    1.x)
 CreatedDate                            Datetime                          Yes
 LastUpdatedDate                        Datetime                          No
 LastUpdatedBy                          Int                               No        Foreign Key. User
                                                                                    who this record is
                                                                                    updated by
 Designator                             Uniqueident                       No        system-generated
                                        ifier                                       GUID (Global
                                                                                    Unique Identifier) -
                                                                                    Designator should
                                                                                    never change when
                                                                                    exporting and
                                                                                    importing
 Change_Flag                            Uniqueident                       No        system-generated
                                        ifier                                       GUID (Global
                                                                                    Unique Identifier) -
                                                                                    Change_Flag will be
                                                                                    changed when record
                                                                                    is modified.
                                                                                                                Formatted: Bullets and Numbering
3.1.14.21.1.23.2 Relationships

 Table                           Type     Enforce Referential      Cascade Update    Cascade Delete Related
                                          Integrity                Related Fields    Records
 tblPlanNode                     1–n      Yes                      No                No
 tblUser (CreatedBy)             n–1      Yes                      No                No
 tblUser                         n–1      Yes                      No                No
 (LastUpdatedBy)
 tblStudentTrainingPlan          1-n      Yes                      No                No

                                                                                                                Formatted: Bullets and Numbering




Database Schema Document                                                                            Page 29 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


3.1.151.1.24 tblPlanNode

3.1.15.11.1.24.1 Specification
The table tblPlanNode contains duties, tasks.

 Field Name                          Type             Size    Unique /   Required   Note
                                                              Non
                                                              Unique
                                                              Index
 PlanNodeID                          Int                      Unique     Yes        Primary key. System-
                                                                                    generated number
                                                                                    (identity).
 Description                         Varchar          50                 Yes        Duty’s name.
 ParentID                            Int                                 No         Foreign key. To
                                                                                    know whether this is
                                                                                    a parent
 NodeType                            Char             1                  Yes        Node Type, Duty,
                                                                                    Task, Object. This is
                                                                                    ‘D’, ‘T’, ‘O’
 TrainingPlanID                      Int                                 No         Foreign Key
 VtecsDutyTaskID                     Varchar          10                 No         Task Number within
                                                                                    Duty OR Duty
                                                                                    Number
 OldDutyTaskID                       Varchar          10                 No         Keep
                                                                                    VtecsDutyTaskID
                                                                                    field from Legacy
                                                                                    System(CONNECT
                                                                                    1.x)
 CreatedDate                         Datetime                            Yes
 LastUpdatedDate                     Datetime                            No
 CreatedBy                           Int                                 Yes        Foreign Key. User
                                                                                    who this record is
                                                                                    created by
 LastUpdatedBy                       Int                                 No         Foreign Key. User
                                                                                    who this record is
                                                                                    updated by
 Designator                          Uniqueident                         No         system-generated
                                     ifier                                          GUID (Global
                                                                                    Unique Identifier) -
                                                                                    Designator should
                                                                                    never change when
                                                                                    exporting and
                                                                                    importing
 Change_Flag                         Uniqueident                         No         system-generated
                                     ifier                                          GUID (Global
                                                                                    Unique Identifier) -
                                                                                    Change_Flag will be
                                                                                    changed when record
                                                                                    is modified.
                                                                                                                Formatted: Bullets and Numbering
3.1.15.21.1.24.2 Relationships




Database Schema Document                                                                            Page 30 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


 Table                     Type       Enforce Referential          Cascade Update      Cascade Delete Related
                                      Integrity                    Related Fields      Records
 tblUser (CreatedBy)       n-1        Yes                          No                  No
 TblUser                   n-1        Yes                          No                  No
 (LastUpdatedBy)
 tblTrainingPlan           n-1        Yes                          No                  No
 tblStudentProgress        1-n        Yes                          No                  No

                                                                                                                   Formatted: Bullets and Numbering
3.1.161.1.25 tblStudentTrainingPlan

3.1.16.11.1.25.1 Specification
The table tblStudentTrainingPlan contains student’s assigned Training Plan, by employer.

 Field Name                           Type            Size    Unique /      Required   Note
                                                              Non
                                                              Unique
                                                              Index
 StdTrainingPlanID                    Int                     Unique        Yes        Primary key. System-
                                                                                       generated number
                                                                                       (identity).
 TrainingPlanID                       Int                                   Yes        Foreign Key
 EmploymentID                         Int                     Unique        Yes        Foreign Key
 Designator                           Uniqueident                           No         system-generated
                                      ifier                                            GUID (Global
                                                                                       Unique Identifier) -
                                                                                       Designator should
                                                                                       never change when
                                                                                       exporting and
                                                                                       importing

 Change_Flag                          Uniqueident                           No         system-generated
                                      ifier                                            GUID (Global
                                                                                       Unique Identifier) -
                                                                                       Change_Flag will be
                                                                                       changed when record
                                                                                       is modified.

 Table                     Type       Enforce Referential          Cascade Update      Cascade Delete Related
                                      Integrity                    Related Fields      Records
 tblStudentProgress        1–n        Yes                          No                  No
 tblTrainingPlan           n–1        Yes                          No                  No
 tblEmployment             1–1        Yes                          No                  No

                                                                                                                   Formatted: Bullets and Numbering




Database Schema Document                                                                               Page 31 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


3.1.171.1.26 tblStudentProgress

3.1.17.11.1.26.1 Specification
The table tblStudentProgress contains student’s Progress Reports

 Field Name                          Type             Size    Unique /      Required   Note
                                                              Non
                                                              Unique
                                                              Index
 ProgressID                          Int                      Unique        Yes        Primary key. System-
                                                                                       generated number
                                                                                       (identity).
 StdTrainingPlanID                   Int                                    Yes        Foreign Key
 PlanNodeID                          Int                                    Yes        Foreign Key
 PrgrssClmnID                        Int                                    Yes        Foreign Key.
                                                                                       Student’s Progress’s
                                                                                       Column.
 ColumnValue                         Varchar          20                    No         Value of the indicator
                                                                                       in the column
 CreatedDate                         Datetime                               Yes
 LastUpdatedDate                     Datetime                               No
 CreatedBy                           Int                                    Yes        Foreign Key. User
                                                                                       who this record is
                                                                                       created by
 LastUpdatedBy                       Int                                    No         Foreign Key. User
                                                                                       who this record is
                                                                                       updated by
 Designator                          Uniqueident                            No         system-generated
                                     ifier                                             GUID (Global
                                                                                       Unique Identifier) -
                                                                                       Designator should
                                                                                       never change when
                                                                                       exporting and
                                                                                       importing
 Change_Flag                         Uniqueident                            No         system-generated
                                     ifier                                             GUID (Global
                                                                                       Unique Identifier) -
                                                                                       Change_Flag will be
                                                                                       changed when record
                                                                                       is modified.
                                                                                                                   Formatted: Bullets and Numbering
3.1.17.21.1.26.2 Relationships

 Table                       Type    Enforce Referential           Cascade Update      Cascade Delete Related
                                     Integrity                     Related Fields      Records
 tblUser (CreatedBy)         n-1     Yes                           No                  No
 tblUser                     n-1     Yes                           No                  No
 (LastUpdatedBy)
 tblStudentTrainingPlan      n-1     Yes                           No                  No
 tblProgressColumn           n-1     Yes                           No                  No
 tblPlanNode                 n-1     Yes                           No                  No




Database Schema Document                                                                               Page 32 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


1.1.27     tblStudentWorkWage

1.1.27.1 Specification
The table tblStudentWorkWage contains student’s Work Wage Records

 Field Name                          Type              Size    Unique /   Required   Note
                                                               Non
                                                               Unique
                                                               Index
 WorkWageID                          Int                       Unique     Yes        Primary key. System-
                                                                                     generated number
                                                                                     (identity).
 EmploymentID                        Int                                  Yes        Foreign Key
 CheckDate                           Datetime                             No         Student’s work/wage
                                                                                     records date
 TotalHours                          Numeric(5,2)                         No         Total Hours
                                                                                     represented on the
                                                                                     check
 RatePerHour                         Numeric(5,2)                         No         Rate per Hour for the
                                                                                     check
 FromDate                            Datetime                             No         Hours start From
                                                                                     Date
 ToDate                              Datetime                             No         Hours start To Date
 WrkWgTypeID                         Int                                  No         Foreign Key. Check
                                                                                     Type
 Comments                            text                                 No         Student’s work/wage
                                                                                     record comments
 CreatedDate                         Datetime                             Yes
 LastUpdatedDate                     Datetime                             No
 CreatedBy                           Int                                  Yes        Foreign Key. User
                                                                                     who this record is
                                                                                     created by
 LastUpdatedBy                       Int                                  No         Foreign Key. User
                                                                                     who this record is
                                                                                     updated by
 Designator                          Uniqueidentif                        No         system-generated
                                     ier                                             GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Designator should
                                                                                     never change when
                                                                                     exporting and
                                                                                     importing
 Change_Flag                         Uniqueidentif                        No         system-generated
                                     ier                                             GUID (Global
                                                                                     Unique Identifier) -
                                                                                     Change_Flag will be
                                                                                     changed when record
                                                                                     is modified.
1.1.27.2




Database Schema Document                                                                             Page 33 of 40
                             A Consortium for Innovative Career
                           and Workforce Development Resources


1.1.27.3 Relationships

 Table                     Type     Enforce Referential           Cascade Update        Cascade Delete Related
                                    Integrity                     Related Fields        Records
 tblUser (CreatedBy)       n-1      Yes                           No                    No
 tblUser                   n-1      Yes                           No                    No
 (LastUpdatedBy)
 tblWorkWageType           n-1      Yes                           No                    No
 tblEmployment             n- 1     Yes                           No                    No

1.1.28     tblUser

1.1.28.1 Specification
The table tblUser contains user’s information to login the system.

 Field Name                         Type             Size         Unique /   Required        Note
                                                                  Non
                                                                  Unique
                                                                  Index
 UserID                             Int                           Unique     Yes             Primary key. System-
                                                                                             generated number
                                                                                             (identity).
 FirstName                          Varchar          25                      Yes             System User’s First
                                                                                             Name
 LastName                           Varchar          30                      Yes             System User’s Last
                                                                                             Name
 MiddleName                         Varchar          1                       No              System User’s
                                                                                             Middle Initial
 Password                           Varchar          20                      Yes             System User’s
                                                                                             Password
 Comment                            text                                     No              System User’s
                                                                                             Comments
 Active                             Bit                                      Yes             Record active or
                                                                                             inactive?
 Administrator                      Bit                                      Yes             To verify whether an
                                                                                             user is administrator
                                                                                             of system.
 CreatedDate                        Datetime                                 Yes
 LastUpdatedDate                    Datetime                                 No
 CreatedBy                          Int                                      Yes             Foreign Key. User
                                                                                             who this record is
                                                                                             created by
 LastUpdatedBy                      Int                                      No              Foreign Key. User
                                                                                             who this record is
                                                                                             updated by
1.1.28.2




Database Schema Document                                                                                  Page 34 of 40
                             A Consortium for Innovative Career
                           and Workforce Development Resources


1.1.28.3 Relationships

 Table                          Type       Enforce Referential    Cascade Update   Cascade Delete
                                           Integrity              Related Fields   Related Records
 tblUser (CreatedBy)            n-1        Yes                    No               No
 tblUser (LastUpdatedBy)        n-1        Yes                    No               No
 tblUserSetting                 1-1        Yes                    No               No
 tblSchool (CreatedBy)          1-n        Yes                    No               No
 tblSchool (LastUpdatedBy)      1-n        Yes                    No               No
 tblOrganization (CreatedBy)    1-n        Yes                    No               No
 tblOrganization                1-n        Yes                    No               No
 (LastUpdatedBy)
 tblStudent (CreatedBy)         1-n        Yes                    No               No
 tblStudent                     1-n        Yes                    No               No
 tblStudent (LastUpdatedBy)     1-n        Yes                    No               No
 tblTrainingPlan (CreatedBy)    1-n        Yes                    No               No
 tblTrainingPlan                1-n        Yes                    No               No
 (LastUpdatedBy)
 tblTeacher (CreatedBy)         1-n        Yes                    No               No
 tblTeacher (LastUpdatedBy)     1-n        Yes                    No               No
 tblTrainingAgreement           1-n        Yes                    No               No
 (CreatedBy)
 tblTrainingAgreement           1-n        Yes                    No               No
 (LastUpdatedBy)
 tblCatalog (CreatedBy)         1-n        Yes                    No               No
 tblCatalog (LastUpdatedBy)     1-n        Yes                    No               No
 tblQA (CreatedBy)              1-n        Yes                    No               No
 tblQA (LastUpdatedBy)          1-n        Yes                    No               No
 tblComment (CreatedBy)         1-n        Yes                    No               No
 tblComment                     1-n        Yes                    No               No
 (LastUpdatedBy)
 tblStudentComment              1-n        Yes                    No               No
 (CreatedBy)
 tblStudentComment              1-n        Yes                    No               No
 (LastUpdatedBy)
 tblContact (CreatedBy)         1-n        Yes                    No               No
 tblContact (LastUpdatedBy)     1-n        Yes                    No               No
 tblUserDefined (CreatedBy)     1-n        Yes                    No               No
 tblUserDefined                 1-n        Yes                    No               No
 (LastUpdatedBy)
 tblTerm (CreatedBy)            1-n        Yes                    No               No
 tblTerm (LastUpdatedBy)        1-n        Yes                    No               No
 tblEmployment (CreatedBy)      1-n        Yes                    No               No
 tblEmployment                  1-n        Yes                    No               No
 (LastUpdatedBy)
 tblPlanNode (CreatedBy)        1-n        Yes                    No               No
 tblPlanNode                    1-n        Yes                    No               No
 (LastUpdatedBy)
 tblStudentProgress             1-n        Yes                    No               No
 (CreatedBy)




Database Schema Document                                                                     Page 35 of 40
                             A Consortium for Innovative Career
                           and Workforce Development Resources


 tblStudentProgress             1-n        Yes                    No   No
 (LastUpdatedBy)
 tblStudentWorkWage             1-n        Yes                    No   No
 (CreatedBy)
 tblStudentWorkWage             1-n        Yes                    No   No
 (LastUpdatedBy)
 tblUser (CreatedBy)            1-n        Yes                    No   No
 tblUser (LastUpdatedBy)        1-n        Yes                    No   No
 tblUserSetting (CreatedBy)     1-n        Yes                    No   No
 tblUserSetting                 1-n        Yes                    No   No
 (LastUpdatedBy)
 tblSystem (LastUpdatedBy)      1-n        Yes                    No   No
 tblRace (CreatedBy)            1-n        Yes                    No   No
 tblRace (LastUpdatedBy)        1-n        Yes                    No   No
 tblGrade (CreatedBy)           1-n        Yes                    No   No
 tblGrade (LastUpdatedBy)       1-n        Yes                    No   No
 tblRelatedInstruction          1-n        Yes                    No   No
 (CreatedBy)
 tblRelatedInstruction          1-n        Yes                    No   No
 (LastUpdatedBy)
 tblWorkWageType                1-n        Yes                    No   No
 (CreatedBy)
 tblWorkWageType                1-n        Yes                    No   No
 (LastUpdatedBy)
 tblContactType (CreatedBy)     1-n        Yes                    No   No
 tblContactType                 1-n        Yes                    No   No
 (LastUpdatedBy)
 tblTeacherType                 1-n        Yes                    No   No
 (CreatedBy)
 tblTeacherType                 1-n        Yes                    No   No
 (LastUpdatedBy)
 tblTrainingAgreementType       1-n        Yes                    No   No
 (CreatedBy)
 tblTrainingAgreementType       1-n        Yes                    No   No
 (LastUpdatedBy)
 tblTermType (CreatedBy)        1-n        Yes                    No   No
 tblTermType                    1-n        Yes                    No   No
 (LastUpdatedBy)
 tblSize (CreatedBy)            1-n        Yes                    No   No
 tblSize (LastUpdatedBy)        1-n        Yes                    No   No




Database Schema Document                                                    Page 36 of 40
                             A Consortium for Innovative Career
                           and Workforce Development Resources


1.1.29   tblUserSetting

1.1.29.1 Specification
The table tblUserSetting contains an individual User Settings.

 Field Name                         Type             Size    Unique /      Required   Note
                                                             Non
                                                             Unique
                                                             Index
 UserSettingID                      Int                      Unique        Yes        Primary key. System-
                                                                                      generated number
                                                                                      (identity).
 DesktopColor                       Int                                    No         Desktop Color used
                                                                                      on outlook bar and
                                                                                      headers
 ActiveTerm                         Int                                    No         The selected Active
                                                                                      Term
 DefaultPrinter                     Varchar          50                    No         Default assigned
                                                                                      Printer
 LastLogin                          Datetime                               No         Last Login Date
 UserID                             Int                                    Yes        Foreign Key.
 CreatedDate                        Datetime                               Yes
 LastUpdatedDate                    Datetime                               No
 CreatedBy                          Int                                    Yes        Foreign Key. User
                                                                                      who this record is
                                                                                      created by
 LastUpdatedBy                      Int                                    No         Foreign Key. User
                                                                                      who this record is
                                                                                      updated by
1.1.29.2 Relationships

 Table                     Type     Enforce Referential           Cascade Update      Cascade Delete Related
                                    Integrity                     Related Fields      Records
 tblUser (CreatedBy)       n-1      Yes                           No                  No
 tblUser                   n-1      Yes                           No                  No
 (LastUpdatedBy)
 TblUser                   1-1      Yes                           No                  No
 TblDesktop                1–n      Yes                           No                  No

1.1.30   tblDesktop

1.1.30.1 Specification

 Field Name                         Type             Size    Unique /      Required   Note
                                                             Non
                                                             Unique
                                                             Index
 DesktopID                          Int                      Unique        Yes        Primary key. System-
                                                                                      generated number
                                                                                      (identity).
 UserSettingID                      Int                                    Yes        Foreign Key.




Database Schema Document                                                                              Page 37 of 40
                             A Consortium for Innovative Career
                           and Workforce Development Resources


 WindowName                         Varchar          30                    Yes        Name of screen in V-
                                                                                      TECS Connect 2.0
                                                                                      system
 WHeight                            Int                                    Yes        Height of screen in
                                                                                      V-TECS Connect 2.0
                                                                                      system
 wLeft                              Int                                    Yes        Left position of
                                                                                      screen in V-TECS
                                                                                      Connect 2.0 system
 wTop                               Int                                    Yes        Top position of
                                                                                      screen in V-TECS
                                                                                      Connect 2.0 system
 wWidth                             Int                                    Yes        Width of screen in V-
                                                                                      TECS Connect 2.0
                                                                                      system
 ScreenWidth                        Int                                    Yes        Width of window’s
                                                                                      screen (desktop area).
 ScreenHeight                       Int                                    Yes        Height of window’s
                                                                                      screen (desktop area).
 SearchFlag                         Bit                                    Yes        Search button is
                                                                                      enable or disable?
1.1.30.2 Relationships

 Table                     Type     Enforce Referential           Cascade Update      Cascade Delete Related
                                    Integrity                     Related Fields      Records
 tblUserSetting            n-1      Yes                           No                  No


1.1.31   tblSystem

1.1.31.1 Specification
The table tblSystem contains Universal System Settings

 Field Name                         Type             Size    Unique /      Required   Note
                                                             Non
                                                             Unique
                                                             Index
 SystemID                           Int                      Unique        Yes        Primary key. System-
                                                                                      generated number
                                                                                      (identity).
 AccessRequireLogin                 Bit                                    Yes        Does the system
                                                                                      Require Login access
 HideInactiveUser                   Bit                                    Yes
 AllowViewOtherStu                  Bit                                    Yes
 HideInactiveTerm                   Bit                                    Yes
 LastUpdatedDate                    Datetime                               No
 LastUpdatedBy                      Int                                    No         Foreign Key. User
                                                                                      who this record is
                                                                                      updated by
 Version                            Varchar          7                     Yes        Build version
 DatabaseVersion                    Varchar          7                     Yes        Database Version




Database Schema Document                                                                              Page 38 of 40
                               A Consortium for Innovative Career
                             and Workforce Development Resources


1.1.31.2 Relationships

 Table                       Type     Enforce Referential           Cascade Update      Cascade Delete Related
                                      Integrity                     Related Fields      Records
 tblUser                     n-1      Yes                           No                  No
 (LastUpdatedBy)
 tblProgressColumn           1-n      Yes                           No                  No

1.1.32   tblProgressColumn

1.1.32.1 Specification

 Field Name                           Type             Size    Unique /      Required   Note
                                                               Non
                                                               Unique
                                                               Index
 PrgrssClmnID                         Int                      Unique        Yes        Primary key. System-
                                                                                        generated number
                                                                                        (identity).
 SystemID                             Int                                    No
 ColumnOrder                          Int                                    No         Order of column
 ColumnName                           Varchar          20                    No         Name of column
 Deleted                              Bit                                    Yes        To verify whether
                                                                                        this column is
                                                                                        displayed.
 Designator                           Uniqueident                            No         system-generated
                                      ifier                                             GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Designator should
                                                                                        never change when
                                                                                        exporting and
                                                                                        importing
 Change_Flag                          Uniqueident                            No         system-generated
                                      ifier                                             GUID (Global
                                                                                        Unique Identifier) -
                                                                                        Change_Flag will be
                                                                                        changed when record
                                                                                        is modified.
1.1.32.2 Relationships

 Table                       Type     Enforce Referential           Cascade Update      Cascade Delete Related
                                      Integrity                     Related Fields      Records
 TblSystem                   n-1      Yes                           No                  No
 TblStudentProgress          1-n      Yes                           No                  No




Database Schema Document                                                                                Page 39 of 40
                              A Consortium for Innovative Career
                            and Workforce Development Resources


1.1.33   tblTooltip

1.1.33.1 Specification
The table tblTooltip contains string resources for all component tool tips
 Field Name                          Type            Size     Unique /        Required    Note
                                                              Non
                                                              Unique
                                                              Index
 TooltipID                           Int                      Unique          Yes         Primary key. System-
                                                                                          generated number
                                                                                          (identity).
 ComponentID                         Varchar         50                       No          Widget Name
 FrmName                             Varchar         500                      No          Form Name
 Tooltip                             Varchar         50                       No          Tool Tip
 Description                         Varchar         250                      No
 CreatedDate                         Datetime                                 Yes
 LastUpdatedDate                     Datetime                                 Yes
 CreatedBy                           Int                                      Yes         Foreign Key. User
                                                                                          who this record is
                                                                                          created by
 LastUpdatedBy                       Int                                      Yes         Foreign Key. User
                                                                                          who this record is
                                                                                          created by


1.1.34   tblHTML

1.1.34.1 Specification
The table tblHTML contains URLs to the Help files for all windows

 Field Name                          Type             Size         Unique /    Required    Note
                                                                   Non
                                                                   Unique
                                                                   Index
 HTMLID                              Int                           Unique      Yes         Primary key. System-
                                                                                           generated number
                                                                                           (identity).
 WindowName                          Varchar          50                       Yes         Name (or ID) of the
                                                                                           window within the
                                                                                           software
 HelpURL                             Varchar          250                      Yes         URL to the HTML
                                                                                           Help Resource file

1.1.35 vtecs_temp
 Field Name                          Type             Size         Unique /    Required    Note
                                                                   Non
                                                                   Unique
                                                                   Index
 colID                               Int                                       No          Temporary table




Database Schema Document                                                                                 Page 40 of 40

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:11/12/2012
language:Unknown
pages:40
About Good!!!NICE!!! The best document database!