Tables and views related to the Labs Database ============================================= revised 2/26/2003 Below is a list of tables and views related to the Labs Database. Views begin "WH_" (defined for the Warehouse), and they usually show the same fields as an underlying table (not shown), except that the field names have been changed to reflect our current terminology. (For example, the table might have LAB_ID, but the view has the same field renamed as ROOM_SET_ID.) ----------- WH_ROOM_SET ----------- Contains one record per Room Set. Name Null? Type ----------------------------------------- -------- ------------- ROOM_SET_ID NOT NULL NUMBER(12) ROOM_SET_CODE VARCHAR2(15) ROOM_SET_NAME VARCHAR2(50) DLC_ID NUMBER(12) ROOM_SET_TYPE NUMBER(12) SUPERVISOR_KERBNAME VARCHAR2(8) ROOM_SET_NOTES VARCHAR2(1000) GROUP_WITHIN_DEPT VARCHAR2(15) EHS_REP_KERBNAME VARCHAR2(8) SHOW_HOME_PHONES_GREENCARD VARCHAR2(1) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE ---------------- WH_ROOM_SET_TYPE ---------------- Contains a list of all possible room set types. Join ROOM_SET_TYPE field from WH_ROOM_SET table with ROOM_SET_TYPE_ID in this table. Name Null? Type ----------------------------------------- -------- ------------- ROOM_SET_TYPE_ID NOT NULL NUMBER(12) ROOM_SET_TYPE_NAME VARCHAR2(50) ---------------- WH_ROOM_SET_ROOM ---------------- Links a room (BUILDING_ROOM) to a room_set. Name Null? Type ----------------------------------------- -------- ------------- BUILDING_ROOM NOT NULL VARCHAR2(16) ROOM_SET_ID NUMBER(12) ----------- WH_ALL_ROOM ----------- Contains a list of all rooms and subrooms. (Rooms are extracted from the Insite data and subrooms are extracted from the Labs Database WH_SUBROOM view.) Name Null? Type ----------------------------------------- -------- ------------- BUILDING_ROOM NOT NULL VARCHAR2(16) BUILDING_NUMBER VARCHAR2(8) FLOOR VARCHAR2(3) USAGE VARCHAR2(16) AREA NUMBER(13,2) DEPT_ID NUMBER(12) SPACE_UNIT_CODE VARCHAR2(6) IS_SUBROOM NOT NULL VARCHAR2(1) PARENT_ROOM VARCHAR2(16) CHILD_ROOM_COUNT NUMBER(5) STATUS NOT NULL VARCHAR2(1) ---------- WH_SUBROOM ---------- Contains a list of all subrooms. Name Null? Type ----------------------------------------- -------- ------------- BUILDING_SUBROOM NOT NULL VARCHAR2(16) PARENT_ROOM NOT NULL VARCHAR2(16) SUBROOM_DESCRIPTION VARCHAR2(1000) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE ----------------------- WH_DLC_WITH_SCHOOL_AREA ----------------------- Contains a list of all DLCs. DLC_ID is an internal number that should only be used as a key for joining tables. DLC_CODE is the D_xxxxxx code for a DLC. IS_DLC is 'Y' for DLCs, 'N' for schools, areas, and other nodes. STATUS_CODE is 'A' for active DLCs, 'I' for inactive ones that cannot be deleted because they are still linked to other tables. Name Null? Type ----------------------------------------- -------- ------------- DLC_ID NOT NULL NUMBER(12) DLC_CODE NOT NULL VARCHAR2(15) DLC_NAME VARCHAR2(50) HAS_CHILD VARCHAR2(1) IS_DLC VARCHAR2(1) STATUS_CODE VARCHAR2(1) SCHOOL_AREA_CODE NOT NULL VARCHAR2(15) SCHOOL_AREA_DEPT_ID NOT NULL NUMBER(12) SCHOOL_AREA_NAME VARCHAR2(50) ---- DEPT ---- Contains a list of all DLCs. The view WH_DLC_WITH_SCHOOL_AREA represents a join between the DEPT table and the WH_DLC_CHILD view. Name Null? Type ----------------------------------------- -------- -------------- DEPT_ID NOT NULL NUMBER(12) DEPT_CODE NOT NULL VARCHAR2(15) DEPT_NAME VARCHAR2(50) HAS_CHILD CHAR(1) STATUS_CODE VARCHAR2(1) ----------------- DEPT_ORG_SIS_LINK ----------------- This table stores links between the D_xxxxxx code and other representations of a DLC, such as 6-digit org unit numbers (LINK_TYPE = 'ORGU'), academic course numbers (LINK_TYPE = 'SISO'), and profit center numbers (LINK_TYPE = 'PC'). For example, it shows that D_EECS maps into Org Unit 064000, Profit Centers P064000 and P064045, and course 6. Name Null? Type ----------------------------------------- -------- ------------- DEPT_CODE NOT NULL VARCHAR2(15) LINK_TYPE NOT NULL VARCHAR2(4) LINK_CODE NOT NULL VARCHAR2(15) ------------ WH_DLC_CHILD ------------ Each record is a parent/child relationship between two records in the DEPT table. Name Null? Type ----------------------------------------- -------- ------------- PARENT_ID NOT NULL NUMBER(12) CHILD_ID NOT NULL NUMBER(12) -------------- WH_ROOM_HAZARD -------------- This links hazard types to rooms. Name Null? Type ----------------------------------------- -------- ------------- BUILDING_ROOM NOT NULL VARCHAR2(16) HAZARD_ID NOT NULL NUMBER(12) -------------- WH_HAZARD_TYPE -------------- Contains a list of all hazard_types. The SEQUENCE field is used for setting the sort order of hazard_types within a group. Name Null? Type ----------------------------------------- -------- ------------- HAZARD_ID NOT NULL NUMBER(12) HAZARD_NAME VARCHAR2(50) SEQUENCE NUMBER(5) HAZARD_GROUP_ID NUMBER(5) -------------------- WH_HAZARD_TYPE_GROUP -------------------- Contains a list of all hazard type groups. It represents a way of grouping hazard types. SEQUENCE is used for setting the sort order of groups. Name Null? Type ----------------------------------------- -------- ------------- HAZARD_GROUP_ID NOT NULL NUMBER(12) HAZARD_GROUP_NAME VARCHAR2(50) SEQUENCE NUMBER(5) ------------------- WH_ROOM_SET_CONTACT ------------------- Maps contact people to a Room Set. SEQUENCE determines their order on the Green Card. Name Null? Type ----------------------------------------- -------- ------------- ROOM_SET_ID NOT NULL NUMBER(12) KERBEROS_NAME NOT NULL VARCHAR2(8) SEQUENCE NUMBER(5) --------------------- WH_CONTACT_ADDR_PHONE --------------------- Lists addresses and phone numbers for each contact person. (Note that a phone number or address for each contact person is recorded only once, and displays on the Green Cards for all Room Sets for which the person is a contact.) Phone types can be Home, Office, Cell, or Other. Data source is 'User' (where the actual username is in the MODIFIED_BY field) or 'Warehouse'. Name Null? Type ----------------------------------------- -------- ------------- KERBEROS_NAME NOT NULL VARCHAR2(8) PHONE_TYPE_CODE VARCHAR2(1) PHONE_TYPE VARCHAR2(6) PHONE_NUMBER NOT NULL VARCHAR2(20) OFFICE_LOCATION VARCHAR2(30) SEQUENCE NUMBER(5) DATA_SOURCE VARCHAR2(12) SUPPRESS_DISPLAY VARCHAR2(1) SHOW_ON_GREENCARD VARCHAR2(1) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE ---------------- WH_ROOM_SET_ROLE ---------------- Shows people with special roles in a Room Set, currently SECONDARY SUPERVISOR or SECONDARY EHS REP. Join with WH_EHS_FUNCTION to get the Function_name, and join with WH_ROOM_SET to get the Room Set code, name, etc.. Name Null? Type ----------------------------------------- -------- ------------- KERBEROS_NAME VARCHAR2(8) FUNCTION_ID NOT NULL NUMBER(6) ROOM_SET_ID NOT NULL NUMBER(12) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE --------------- WH_EHS_FUNCTION --------------- List of all possible functions to be associated with roles within a Room Set. In the future, may also include functions for roles at other levels. Currently, QUALIFIER_TYPE = 'FACI' (which is the internal code for Room Set, formerly called Facility). SEQUENCE_NO determines the sort order. Name Null? Type ----------------------------------------- -------- ------------- FUNCTION_ID NOT NULL NUMBER(6) FUNCTION_NAME VARCHAR2(30) FUNCTION_DESCRIPTION VARCHAR2(255) MODIFIED_BY VARCHAR2(8) MODIFIED_DATE DATE QUALIFIER_TYPE CHAR(4) SEQUENCE_NO NUMBER(8) ----------------- EHS_AUTHORIZATION ----------------- Lists authorizations extracted from the Roles Database which control access to Labs Database. QUALIFIER_TYPE can be FACI (Room Set) or DEPT (DLC). For QUALIFIER_TYPE = 'FACI', join with WH_ROOM_SET view where EHS_AUTHORIZATION.QUALIFIER_ID = WH_ROOM_SET.ROOM_SET_ID. For QUALIFIER_TYPE = 'DEPT', join with DEPT table where EHS_AUTHORIZATION.QUALIFIER_ID = DEPT.DEPT_ID. Name Null? Type ----------------------------------------- -------- ------------- KERBEROS_NAME NOT NULL VARCHAR2(8) FUNCTION_NAME NOT NULL VARCHAR2(30) QUALIFIER_ID NOT NULL NUMBER(12) QUALIFIER_TYPE NOT NULL VARCHAR2(4) EFFECTIVE_DATE NOT NULL DATE EXPIRATION_DATE DATE --------- WH_PERSON --------- List of all employees, students, and other people with Kerberos usernames at MIT. (A pseudo-Kerberos username is algorithmically generated from the MIT ID number for employees or students who do not have a Kerberos username.) Name Null? Type ----------------------------------------- -------- ------------- MIT_ID VARCHAR2(9) KERBEROS_NAME NOT NULL VARCHAR2(8) LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(30) MIDDLE_NAME VARCHAR2(30) EMAIL_ADDRESS VARCHAR2(60) PRIMARY_OFFICE_LOCATION VARCHAR2(30) PRIMARY_OFFICE_PHONE VARCHAR2(20) SECONDARY_OFFICE_LOCATION VARCHAR2(30) SECONDARY_OFFICE_PHONE VARCHAR2(20) EMPLOYEE_UNIT_ID VARCHAR2(6) EMPLOYEE_UNIT_NAME VARCHAR2(50) EMPLOYEE_TITLE VARCHAR2(51) APPOINTMENT_TYPE VARCHAR2(30) STUDENT_YEAR VARCHAR2(1) STUDENT_DEPARTMENT VARCHAR2(4) TYPE VARCHAR2(8) DATE_LAST_UPDATED DATE HOME_PHONE VARCHAR2(20) STATUS_CODE CHAR(1) SUPPRESS_EMPL_HOME VARCHAR2(1) ------------ WH_QUAL_DESC ------------ Shows relationships between DLCs, other DLCs, and Room Sets. For each Room Set (where CHILD_QUAL_TYPE = 'FACI' and CHILD_ID is the room_set_id), includes one record for each DLC, school or area, etc. which is linked to the Room Set. This table is used to evaluate a person's authority to view or maintain room set information -- often authorizations are at the DLC or higher level, and we need a quick way of determining what DLCs or higher objects are associated with a Room Set. Name Null? Type ----------------------------------------- -------- ------------- PARENT_QUAL_TYPE NOT NULL VARCHAR2(4) PARENT_ID NOT NULL NUMBER(12) CHILD_QUAL_TYPE NOT NULL VARCHAR2(4) CHILD_ID NOT NULL NUMBER(12) --------------------- WH_ROOM_SET_ACL_BASIC --------------------- This view is derived from EHS_AUTHORIZATION. It has a record for each username who has "basic" access to Warehouse tables related to the Labs Database, i.e., it includes each username who can view or maintain at least one Room Set. Name Null? Type ----------------------------------------- -------- ------------- KERBEROS_NAME NOT NULL VARCHAR2(8) --------------- WH_ROOM_SET_ACL --------------- For each person, lists all Room Sets for which the person can view data, but not necessarily suppressed home phone number data. This view is used by the Warehouse to control access to room set data. Name Null? Type ----------------------------------------- -------- ------------- KERBEROS_NAME NOT NULL VARCHAR2(8) ROOM_SET_ID NOT NULL NUMBER(12) --------------------- WH_ROOM_SET_PHONE_ACL --------------------- Same as WH_ROOM_SET_ACL, but also gives a person the authority to view suppressed home phone numbers for contact people related to a Room Set. Used by the Warehouse. Name Null? Type ----------------------------------------- -------- ------------- KERBEROS_NAME NOT NULL VARCHAR2(8) ROOM_SET_ID NOT NULL NUMBER(12)