Training records and requirements database specifications ========================================================= (Jim Repa - Version 0.5 - 6/3/2002) (Note: This memo was originally intended as a description of tables for a FileMaker prototype for needs assessment. Now that the needs assessment component is being implemented in the same database as the one used for the prototype Labs Database, some tables and field definitions are changing slightly, as indicated in the Notes for each table.) Tables: * EHS_FUNCTION Desc: Functions that people can perform in a lab or other facility. May be a factor in WHO_NEEDS_CERT_RULE. Source: Fed from LABS database. Fields: Name Null? Type ------------------------------- -------- ---- FUNCTION_ID NOT NULL NUMBER(6) FUNCTION_NAME VARCHAR2(30) FUNCTION_DESCRIPTION VARCHAR2(255) QUALIFIER_TYPE CHAR(4) SEQUENCE_NO NUMBER(8) MODIFIED_DATE DATE MODIFIED_BY VARCHAR2(8) Status: Implemented in prototype Labs Database. Notes: See also TRAIN_FUNCTION, which contains only functions related to training requirements. Has two additional fields: WEB_DESCRIPTION (VARCHAR2(1000)) is web-format description of function for listing on web pages, and STATUS_CODE (VARCHAR2(1)) is used to differentiate between obsolete functions still assigned to one or more users ('I') vs. active functions that can be assignee to new users ('A'). * DLC Desc: Departments, Labs, and Centers Source: Fed from another database - currently Roles DB. Fields: Name Null? Type ------------------------------- -------- ---- DEPT_ID NOT NULL NUMBER(12) DEPT_CODE NOT NULL VARCHAR2(15) DEPT_NAME VARCHAR2(50) HAS_CHILD CHAR(1) Status: Implemented in prototype Labs Database as "DEPT" table. * FACILITY Desc: Lab-suites and other facilities Source: Fed from LABS database. Fields: Name Null? Type ------------------------------- -------- ---- LAB_ID NOT NULL NUMBER(12) LAB_CODE VARCHAR2(15) LAB_NAME VARCHAR2(50) SUPERVISOR VARCHAR2(60) (a.k.a. PI) SUPERVISOR_MIT_ID VARCHAR2(9) DEPT_ID NUMBER(12) FACILITY_TYPE_NAME VARCHAR2(50) SUPERVISOR_KERBNAME VARCHAR2(8) LAB_NOTES VARCHAR2(1000) GROUP_WITHIN_DEPT VARCHAR2(15) Status: Implemented in prototype Labs Database as "LAB" table. * HAZARD_TYPE Desc: Hazard types in facilities that could be a factor in determining a person's training needs. Source: Fed from LABS database. Fields: Name Null? Type ------------------------------- -------- ---- HAZARD_ID NOT NULL NUMBER(12) HAZARD_NAME VARCHAR2(50) Status: Implemented in prototype Labs Database. * SUBJECT_IMPLIES_TRAINING Desc: An MIT academic lab or other subject (i.e., a course for credit) that can be a criterion for requiring EH&S training. May be a factor in WHO_NEEDS_CERT_RULE. Source: EH&S people choose from a list of all MIT subjects (from the Warehouse) which ones are to be represented in this table. The field INTERNAL_SUBJECT_ID is a numeric field derived from MASTER_SUBJECT_ID that can be referenced in the OBJECT_ID field of the table WHO_NEEDS_CERT_RULE.) Fields: Name Null? Type ------------------------ -------- ---- MASTER_SUBJECT_ID NOT NULL VARCHAR2(8) MASTER_SUBJECT_TITLE VARCHAR2(60) MASTER_COURSE_NUMBER VARCHAR2(3) (first part of subject_id) INTERNAL_SUBJECT_ID NOT NULL NUMBER(12) (algorithmically derived from MASTER_SUBJECT_ID) Status: Not implemented yet. * SUBJECT_XREF Desc: For a given MASTER_SUBJECT_ID, this table includes each cross-referenced SUBJECT_ID that refers to the same MIT academic subject. For example, a subject whose MASTER_SUBJECT_ID places it in the department of chemistry might be cross-referenced under a different SUBJECT_ID under the department of biology. The COURSE_ID and SUBJECT_TITLE may differ in each cross-referenced version of a course. Source: Warehouse (choose only those records from the warehouse SUBJECT_OFFERED table that correspond to MASTER_SUBJECT_IDs specified in the EH&S SUBJECT_IMPLIES_TRAINING table) Fields: Name Null? Type ------------------------------- -------- ---- SUBJECT_ID NOT NULL VARCHAR2(8) MASTER_SUBJECT_ID NOT NULL VARCHAR2(8) SUBJECT_TITLE VARCHAR2(60) COURSE_ID VARCHAR2(3) Status: Not implemented yet in EH&S databases. * REGISTERED_PERSON Desc: This table contains one record for each person who has gone through the needs assessment or taken a course. Source: Once we've identified that a person should be in this table (either because they took a needs assessment or because a feed from the KC database tells us that person signed up for a live course), we'll get E-mail address, MIT_ID, full name, etc. from the Warehouse. E-mail address could be self-reported. Sink: Sent to Traincaster. Fields: Name Null? Type ------------------------------- -------- ---- KERBEROS_NAME NOT NULL VARCHAR2(8) *MIT_ID VARCHAR2(9) *LAST_NAME NOT NULL VARCHAR2(30) *FIRST_NAME VARCHAR2(30) *MIDDLE_NAME VARCHAR2(30) *EMAIL_ADDRESS_WH VARCHAR2(60) EMAIL_ADDRESS_SELF VARCHAR2(60) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE Status: Implemented in prototype database as TRAIN_REGISTRATION. (See notes.) Note: In prototype database, the fields marked with asterisks above are excluded, since the data can be gotten by joining with PERSON2 table. An additional field, FIRST_REGISTER_DATE (DATE) indicates the first day on which the person entered the training registration web pages. * PERSON_ROLE Desc: Lists a person's role (function performed) in a lab-suite or other facility. The function part could be used by the needs assessment. Source: Self-reported and/or fed from LABS database. Fields: Name Null? Type ------------------------------- -------- ---- FUNCTION_ID NOT NULL NUMBER(6) QUALIFIER_TYPE NOT NULL CHAR(4) QUALIFIER_CODE NOT NULL VARCHAR2(15) KERBEROS_NAME NOT NULL VARCHAR2(8) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE SELF_REPORTED CHAR(1) 'Y' or 'N' Status: Two modified versions, EHS_ROLE (for info reported by others) and TRAIN_SELF_REPORT_ROLE (for self-reported info) are implemented in prototype database. * PERSON_PLACE Desc: These data are kept for reporting purposes, e.g., so we can report on all people affiliated with a given DLC, PI, or facility. A person can have more than one record (but we may only be able to send one record per person to the Traincaster's system when we implement that feed). Source: Self-reported through UI and/or fed from LABS database. We can think of the fields DEPT_ID + SUPERVISOR_MIT_ID + LAB_ID as a single pick list for the user, with the possibility of specifying * lab 21345 (which implies a DLC and PI) * "Dept. of XYZ, unknown PI and lab" * "Dept. of XYZ, PI=Joe Smith, unknown lab" I think it will help us to store these as three fields, where one or two could be null. Sink: Sent to Traincaster. Fields: Best guess -- still need to form concensus Name Null? Type ------------------------------- -------- ---- KERBEROS_NAME NOT NULL VARCHAR2(8) DEPT_ID NUMBER(12) SUPERVISOR_MIT_ID VARCHAR2(9) (a.k.a. PI) LAB_ID NUMBER(12) SELF_REPORTED NOT NULL CHAR(1) 'Y' or 'N' Status: In prototype database, we have TRAIN_SELF_REPORT_DEPT and TRAIN_SELF_REPORT_PI (for self-reported info). * PERSON_SUBJECT Desc: Each record indicates that a person is enrolled in or will be enrolled in an MIT academic course (from table SUBJECT_IMPLIES_TRAINING) that has EH&S training implications. Source: Self-reported and/or extracted from Warehouse data from the student systems. Fields: KERBEROS_NAME NOT NULL VARCHAR2(8) SUBJECT_ID NOT NULL VARCHAR2(8) TERM_CODE VARCHAR2(6) Which year/term? SELF_REPORTED NOT NULL CHAR(1) 'Y' or 'N' Status: Not yet implemented in EH&S database * CERTIFICATION_TYPE Desc: A certification_type represents an MIT-defined area in which a person needs training or other requirements. The fields START_DATE and END_DATE were added to this document on 3/7/2002. Certification_types can be phased in and out by setting START_DATE and END_DATE. Source: Filled in by EHS people via UI Fields: CERT_ID NOT NULL NUMBER(12) CERT_SHORT_NAME NOT NULL VARCHAR2(15) CERT_NAME VARCHAR2(50) +START_DATE NOT NULL DATE +END_DATE DATE Status: Not yet implemented. * WHO_NEEDS_CERT_RULE Desc: Defines the rules for who needs a certification type, based on (1) DLC (Does a person work in any lab under a given DLC?), (2) FUNCTION (Does a person have at least one PERSON_ROLE for a given FUNCTION?), (3) hazard_type (Does a person work in at least one FACILITY in which a given HAZARD_TYPE exists?), or (4) MIT subject (Is the person enrolled in a lab or other academic course that implies EH&S-related training?). Each row represents a CERTIFICATE_TYPE plus one of (DLC, FUNCTION, HAZARD_TYPE) which would require the person to obtain that CERTIFICATE_TYPE. See also CERT_TYPE_PRIORITY table. The fields START_DATE and END_DATE were added to this document on 3/7/2002. The DEPT_ID field was added on 6/28/2002. Source: Filled in by EHS people via UI Fields: Name Null? Type --------------------- ---- CERT_ID NOT NULL NUMBER(12) OBJECT_TYPE NOT NULL CHAR(1) **F, R, H, D, S, T OBJECT_ID NOT NULL NUMBER(12) +DEPT_ID NUMBER(12) (if not null, applies only to this dept) +START_DATE NOT NULL DATE (when rule goes into effect) +END_DATE DATE (when rule ends) (** F=Function R=self_reported_function H=Hazard_type D=DLC S=Subject T=job title) Status: Not yet implemented. * CERT_TYPE_PRIORITY Desc: This table allows us to indicate that a given CERTIFICATION_TYPE takes precedence over another CERTIFICATION_TYPE. For example, if you work with chemicals, you may be required to get a CERT_TYPE "Proper use of chemicals" (CERT_ID = 100). However, the Dept. of Chemistry (DLC='D_CHEM') may have its own CERT_TYPE "Proper use of chemicals within Dept. of Chemistry" (CERT_ID=101), which it wants people within its department to obtain. The latter CERTIFICATION_TYPE is a superset of the former (101 is a superset of 100). We would say that CERT_ID 101 takes precedence over CERT_ID 100. If the WHO_NEEDS_CERT_RULEs tell us that user JOE needs to get both CERT_ID 100 and CERT_ID 101, we would apply the GETTING_CERT_RULEs for CERT_ID 101 and not CERT_ID 100. Source: Filled in by EHS people via UI Fields: HIGH_PRIO_CERT_ID NOT NULL NUMBER(12) LOW_PRIO_CERT_ID NOT NULL NUMBER(12) Status: Not yet implemented. * GETTING_CERT_RULE Desc: This is a simple way of representing options for getting a certificate_type. (If we wanted to spend a lot of time on this, we would define a way of having a hierarchy of and/or groupings, but we don't want to spend a lot of time on this.) Each group of records with the same cert_id and option_set numbers represents one option for getting a certificate_type: If you get all of the course_other_equivalents represented by the course_equiv_id's, then you get the certificate_type. So, "ands" are handled by specifying more than one record with the same cert_it and same option_set number. "Ors" are handled by specifying more than one option_set for the same cert_id. Option set numbers ought to begin at 1 for each cert_id. The fields START_DATE, END_DATE_NEW and END_DATE were added to this document on 3/7/2002. START_DATE indicates that date that the rule goes into effect for evaluating certification_type fulfillment. END_DATE_NEW indicates the date after which the rule should no longer be considered for people taking a new course_equiv (but the rule might be counted toward certification fulfillment if a person took a course_equiv before this date). END_DATE indicates the date after which the rule is completely ignored, for both old course_equivs and new ones. Source: Filled in by EHS people via UI Fields: CERT_ID NOT NULL NUMBER(12) OPTION_SET NOT NULL NUMBER(2) COURSE_EQUIV_ID NOT NULL NUMBER(12) +START_DATE NOT NULL DATE (when rule goes into effect) +END_DATE_NEW DATE (rule ends for new courses) +END_DATE DATE (rule ends, new & old courses) Example: CERT_ID OPTION_SET COURSE_EQUIV_ID ------- ----------- --------------- 10 1 405 10 1 406 10 1 510 10 2 408 10 2 510 10 3 409 10 3 511 In the above example, a person could get the cert_type represented by cert_id 10 by either completing * The courses or other-reqs represented by course_equiv_ids 405 and 406 and 510 or * The courses or other-reqs represented by course_equiv_ids 408 and 510 or * The courses or other-reqs represented by course_equiv_ids 409 and 511 Status: Not yet implemented. * COURSE_OTHER_EQUIV Desc: A "course equivalent" or other requirement. This table allows us to specify "Chemical Hygiene Training" without differentiating between web-based training, live training, or "testing out" of the training requirement. This table will list all course-equivalents plus all other-requirements (e.g., eye exam, signature of radiation office, etc.) which can be specified in the table getting_cert_rules. Source: Filled in by EHS people via UI Fields: COURSE_EQUIV_ID NOT NULL NUMBER(12) SHORT_NAME NOT NULL VARCHAR2(15) LONG_NAME NOT NULL VARCHAR2(50) EXPIRE_DAYS NUMBER(5) How many days before you need to renew this? Status: Not yet implemented. * COURSE_OPTION Desc: Each course_other_equiv record has one or more corresponding records in this table. For each course, there might be separate records for web-based courses, live courses, testing out, etc. Source: Filled in by EHS people via UI Fields: COURSE_OPTION_ID NOT NULL NUMBER(12) COURSE_OPTION_TYPE CHAR(2) L=live course, W=web course, O=other COURSE_OPTION_NAME VARCHAR2(50) SHORT_NAME VARCHAR2(15) EXTERNAL_COURSE_CODE VARCHAR2(15) COURSE_EQUIV_ID NOT NULL NUMBER(12) EXPIRE_DAYS NUMBER(5) If not null, overrides EXPIRE_DAYS from COURSE_OTHER_EQUIV table Status: Not yet implemented. * COMPLETED_COURSE Desc: One record per person plus course_option that he has satisfied. Source: Web-courses from Traincaster. Live courses from KC system. Other input directly into this DB via the UI. (The EXPIRATION_DATE is calculated by adding EXPIRE_DAYS for the COURSE_OPTION_ID to COMPLETION_DATE - maybe this doesn't need to be stored in the table.) Fields: KERBEROS_NAME NOT NULL VARCHAR2(8) COURSE_OPTION_ID NUMBER(12) COMPLETION_DATE DATE EXPIRATION_DATE DATE RECORDED_BY VARCHAR2(8) Status: Not yet implemented. * UNCOMPLETED_COURSE Desc: One record per person plus web-based course_option that is needed for obtaining unfulfilled certification-types. Source: Updated by an automatic program based on rules. Let's ignore, for the moment, any concerns about courses that the person may have started or registered for but not yet completed. Let's also make the assumption we don't have to worry about different web-based course options to get the same cert_type. To avoid ambiguities, the program should do the following: (1) For each person and each unfulfilled cert-type for that person, find all of the OPTION_SETs for the given CERT_ID. (2) Look at the first (next) OPTION_SET. (If no more OPTION_SETs, go to the next unmet CERT_ID for the person. If no more of those, go to the next person.) (3) For each COURSE_EQUIV_ID within the given OPTION_SET, find all of those that are unmet by the person (4) Is there at least one COURSE_OPTION corresponding to the COURSE_EQUIV_ID that has a COURSE_OPTION_TYPE of 'W'? If not, go to (2). (5) Write a record to the UNCOMPLETED_COURSE table with the person's KERBEROS_NAME and the matching COURSE_OPTION_ID (6) Repeat for all other COURSE_EQUIV_IDs in this OPTION_SET that have matching COURSE_OPTIONs with a COURSE_OPTION_TYPE of 'W'. Sink: Sent to Traincasters. Fields: KERBEROS_NAME NOT NULL VARCHAR2(8) COURSE_OPTION_ID NUMBER(12) Status: Not yet implemented. * PERSON_CERT Desc: Each record represents a person plus a certification_type that the person needs (based on WHO_NEEDS_CERT_RULEs). A person can have more than one record in this table. The table includes both unmet and met certifications. A program will be automatically run nightly (or more frequently) to update this table by applying rules from WHO_NEEDS_CERT_RULE table and GETTING_CERT_RULE table. Source: Updated by an automatic program based on rules. OBTAINED_DATE is null unless FULFILLED='Y'. EXPIRATION_DATE is the earliest EXPIRATION_DATE for any of the COMPLETED_COURSEs used to obtain this certification_type. Fields: KERBEROS_NAME NOT NULL VARCHAR2(8) CERT_ID NUMBER(12) FULFILLED CHAR(1) 'Y' or 'N' OBTAINED_DATE DATE EXPIRATION_DATE DATE Status: Not yet implemented. - - - optional tables (will be helpful for generating reports) - - - * GOVERNMENT_REG Desc: Government regulation Source: Filled in by EHS people via UI Fields: GOV_REG_ID NOT NULL NUMBER(12) GOV_REG_NAME VARCHAR2(50) GOV_REG_DESC VARCHAR2(1000) +START_DATE NOT NULL DATE +END_DATE DATE Status: Not yet implemented. * REG_TO_CERT_LINK Desc: Links a government_reg to a certification_type. This supports a "many-to-many" relationship between the two tables. Source: Filled in by EHS people via UI Fields: CERT_ID NOT NULL NUMBER(12) GOV_REG_ID NOT NULL NUMBER(12) +START_DATE NOT NULL DATE +END_DATE DATE Status: Not yet implemented.