OIS STUDENT DATABASE

OIS INSTRUCTOR DATABASE

A Repository of Data Pertaining to

University of Washington Instructors, Classes and Class Enrollment

as of July 11, 1996

TABLE OF CONTENTS

Page

Introduction 3

Purpose and Limitations of the Database 4

Structure of the OIS Instructor Database - An Overview 5

OIS Instructor Database Data Tables 7

Table: OIS_Faculty_Activity 11

Table: OIS_Instructor_Class_Meetings 14

Table: OIS_FWS_Enrollment 19

Appendix A

Lookup Tables for Use In Conjunction With

OIS Instructor Database Data Tables 21

Appendix B

OIS Instructor Database - Primary and Foreign

Key Relationships 22

OIS INSTRUCTOR DATABASE

A Repository of Data Pertaining to

University of Washington Instructors, Classes and Class Enrollment

as of July 11, 1996

Introduction

The OIS Instructor Database covers all persons reported as giving credit instruction beginning with Autumn Quarter, 1993. Persons giving credit instruction are instructors. Credit instruction includes that offered through Educational Outreach (formerly known as University Extension) and other self-sustaining programs. The OIS Instructor Database covers instructors at the Seattle, Bothell and Tacoma campuses and instructors affiliated with the same campuses regardless of the campus sponsoring the instruction.

All data in the OIS Instructor Database is taken from the University's Faculty Workload System. The Faculty Workload System is an integral part of the Student Information System. The OIS Instructior Database is constructed once each Quarter (including Summer) and is updated within two weeks of the end of each academic term.

All data is held in tables. A table is composed of columns and rows with the intersection of a column and row being either empty (null) or holding one and only one value. A table represents a thing, either tangible or intangible, such as an instructor. A column represents attributes or characteristics about the thing the table represents. A row represents a specific instance, such a particular student, represented by the table. Hence, every row and column intersection is a fact. Facts in a table may be accessed by instance (row), attribute (column) or instance and attribute combination.

An example of a table, representing baseball players who are pitchers, is shown below:

Pitcher	Hits	Strike Outs	Walks	Runs	Earned Runs	Innings Pitched
A	25	10	5	15	14	40
B	50	27	35	32	28	89
C	87	23	8	56	56	123
D	34	45	67	7	6	56
E	0	1	4	1	1	1

An example of accessing facts by column (attribute) is shown below:

How many runs were scored?

				Runs
				15
				32
				56
				7
				1

An example of accessing facts by instance (row) is shown below:

What are the statistics of pitcher C?

Pitcher	Hits	Strike Outs	Walks	Runs	Earned Runs	Innings Pitched


C	87	23	8	56	56	123

An example of accessing facts by column (attribute) and instance (row) is shown below:

What Pitchers had more than twenty (20) strike outs and how many strike outs did they have?

Pitcher		Strike Outs

B		27
C		23
D		45

Two or more tables may be used together. Use of two or more tables together is called a join. A join is accomplished by merging tables or parts of tables together on the basis of keys. Keys permit joining tables together on the basis of data values or, as they are sometimes called, facts.

In addition to the OIS Instructor Database a user may be able to access data in the OIS Finance, OIS Class Meeting and Space Use, OIS Student and other databases as they become operational.

Purpose and Limitations of the Database

The purpose of the OIS Instructor Database and related OIS databases is to provide a research, analytic and management data resource. These databases are organized around subjects such as instructor, class meetings or course offerings. They are integrated in that subjects are related to one another. They are non-volatile in that data is composed of condition snap shots and they are time-variant in that all data is stated as being current as of a particular time point.

These databases are a resource which should not be used to support ongoing administrative processes. The databases are not a transaction processing system and reflect transactions processed only at the time the database is updated. As an example the database should not be used to determine the instructor of a given class then currently meeting but may be used to determine past classes instructed and courses of those classes by a given instructor or group of instructors.

All data in the OIS Instructor database are taken from University information systems. The accuracy of the database is no more accurate that the underlying data first created and stored in University information systems at the time the data is extracted. Every effort has been made to reliably and properly extact data from these information systems. But the OIS Instructor Database can be no more accurate than this underlying sources.

Structure of the OIS Instructor Database - An Overview

The OIS Instructor Database is organized around three (3) primary tables. These tables are:

OIS_Faculty_Activity

OIS_Instructor_Class_Meetings, and

OIS_FWS_Enrollment

These three (3) tables, for purposes of this documentation, are called data tables.[1] The interrelationships of these data tables are shown on Chart 1.

In addition to the three (3) data tables the Database contains tables translating various coding schemes used in the data tables into English language phases and labels. These coding scheme translation tables are called, for the purposes of this documentation, 'lookup' tables. Lookup tables used in the Database in conjunction with the data tables are listed in Appendix A. Also shown are the data table keys that should be used to navigate to the lookup tables.

The OIS Instructor Database tables are stored in a relational database. The primary and foreign keys used to navigation between tables in this database are shown in Appendix B.

the OIS Instructor Database may be accessed on the University of Washington campus network by addressing:

parnassus.opb.washington.edu

To access the Database, an authorized user is required to use any Oracle Structured Query Language (SQL) tool or similar Oracle (version 7) compliant database Structured Query Language (SQL) tool. SQL tools may run on any operating system or platform (Windows, DOS, Unix, OS/2, Mac, etc.) utilizing an Open System Interface and TCP/IP communication protocol.[2] Example Oracle compliant SQL tools are MS Access with ODBC (Windows), GQL (Mac, Windows, and Unix), Q+E (Windows), Pablo Report Writer (Mac) and SQL Assist Report Wrtier (Unix and Windows).

To receive authorization to access OIS Instructor Database tables please contact:

Andrea Sparling

Database Administrator

UW Tower, T-12

Box 359445

(206) 221-4667

amspar@u.washington.edu

For assistance in using the OIS Instructor Database please contact:

Phil Hoffman

Office of Institutional Studies

UW Tower, T-12

Box 359445

(206) 685-9956

hoffphil@u.washington.edu

OIS INSTRUCTOR DATABASE DATA TABLES

The three (3) data tables in the database are:

OIS_Faculty_Activity

OIS_Instructor_Class_Meetings, and

OIS_FWS_Enrollment

OIS_FACULTY_ACTIVITY

Each instance (sometimes called a row) in OIS_FACULTY_ACTIVITY is uniquely identified by Year, Quarter and Employee_Id. There is one and only one instance for each combination of Year, Quarter and Employee_Id. Each combination of Year, Quarter and Employee_Id represents a person holding a University appointment who was or who could have potentially been an instructor[3] during the indicated Year and Quarter. Instructors recorded in the OIS_FACULTY_ACTIVITY table are for Autumn Quarter, 1993 and thereafter. The data in OIS_FACULTY_ACTIVITY are things about instructors which do not change or vary on the basis of the class or classes instructed. An example would be the instructor’s name. The OIS_FACULTY_ACTIVITY table is updated Quarterly generally within two weeks of the Quarter’s conclusion.

OIS_Faculty_Activity instance attributes are:

Data Length

Attributes Type (In Bytes)

YEAR Text 4

QUARTER Text 1

EMPLOYEE_ID Text 9

HOME_DEPT_BUDGET Text 6

NAME Text 30

JOB_CLASS Text 6

UPDATE_DATE Text 8

OTHER_FAC_STUD_CONTACT_HRS Number (Double 8

OTHER_INST_REL_ACTIVITIES_HRS Number (Double 8

TOTAL_DIRECT_INSTR_FTE Number (Double 8

INST_RELATED_RES_SCHOLAR_HRS Number (Double 8

INST_RELATED_RES_SCHOLAR_FTE Number (Double 8

ADMIN_SRV_HRS Number (Double 8

ADMIN_SRV_FTE Number (Double 8

ST_BUD_RES_HRS Number (Double 8

ST_BUD_RES_FTE Number (Double 8

ST_BUD_PUB_SRV_HRS Number (Double 8

ST_BUD_PUB_SRV_FTE Number (Double 8

SPONSORED_RES_HRS Number (Double 8

SPONSORED_RES_FTE Number (Double 8

OTHER_NON_ST_FUNDED_HRS Number (Double 8

OTHER_NON_ST_FUNDED_FTE Number (Double 8

TOTAL_APPT_FTE Number (Double 8

COMPLETENESS_LEVEL Text 1

TEACHING_THIS_QUARTER Text 1

FUND_SOURCE Text 1

OIS_INSTRUCTOR_CLASS_MEETINGS

Each instance (or row) in OIS_INSTRUCTOR_CLASS_MEETINGS is uniquely identified by a combination of:

· Year,

· Quarter,

· Employee_Id,

· Faculty_Sequence_No,

· Course_Branch,

· Curric_Abbr,

· Course_No,

· Section_Id and,

· Meeting_No.

This combination is the primary key. As in the OIS_FACULTY_ACTIVITY table, Year, Quarter, Employee_Id represents an instructor but in the table OIS_INSTRUCTOR_CLASS_MEETINGS an instructor is instructing an enumerated class meeting. This class meeting is identified by Faculty_Sequence_No, Course_Branch, Curric_Abbr, Course_No, Section_Id and Meeting_No portion of the primary key. There are one or more instances in OIS_INSTRUCTOR_CLASS_MEETINGS for any given Year, Quarter, and Employee_Id but there is one and only one for the complete primary key. This means that an instructor may instruct one or more class meetings for the stated Year and Quarter. In addition, a class meeting (identified by Faculty_Sequence_No, Course_Branch, Curric_Abbr, Course_No, Section_Id, and Meeting_No) will have at least one and may have many instructors. Each instructor has an instance in the table OIS_ regardless of the number class meetings instructed.

Instructor_Class_Meetings attributes are those things about instructors that vary with class meeting being instructed. An example would be contact hours.

Notes on use of the table OIS_INSTRUCTOR_CLASS_MEETINGS:

Frequently the terms course, section, or class meeting are used interchangably to mean the body of students (or single student) being instructed and the subject matter being instructed. Use of such terms interchangeably, in the context of a database, lead to confusion, errors and incorrect data use and applicaton. In this database:

Instructors instruct class meetings.

A section is an administrative unit of enrollment accounting (also referred to as a course offering). A section accounts for enrollment in one or more class meetings (example: section AB of ECON 200 accounting for 25 enrolled students in a large lecture of 350 and a quiz class meeting having 25 students enrolled).

A course is the subset of a body of knowledge for presentation (example: ECON 200). A course is the class meeting purpose.

A class meeting is a group of students assembled for taking instruction (generally, but not always, in a classroom, lab or other physical space) with an instructor giving instruction to the assembled group[4] or, in the case of independent study, the individualized teaching and learning relationship of an enrolled student and instructor.

A section or couse offering is represented in the OIS Student Database in the table Time_Schedule. A course is represented by the combination of the attributes Curric_Abbr and Course_No. Attributes of courses are to be represented in the OIS Student Database in the table Courses effective Fall, 1996.

OIS_Instructor_Class_Meetings instance attributes are:

Data Length

Attribute Type (In Bytes)

YEAR Text 4

QUARTER Text 1

EMPLOYEE_ID Text 9

FACULTY_SEQUENCE_NO Text 5

COURSE_BRANCH Text 1

CURRIC_ABBR Text 6

COURSE_NO Text 3

SECTION_ID Text 3

MEETING_TYPE Text 2

MEETING_NO Text 1

PERCENT_INVOLVEMENT Number (Intege 2

CREDIT Text 9

HRS Number (Double 8

FTE Number (Double 8

UPDATE_DATE Text 8

EVENING_DEGREE Text 1

OIS_FWS_ENROLLMENT

Each instance (or row) in OIS_FWS_ENROLLMENT represents a class meeting for which there was one or more reported (as of the tenth instructional day of the quarter) enrolled students. An instance is uniquely represented by the combination of:

· Year,

· Quarter,

· Course_Branch,

· Curric_Abbr,

· Course_No,

· Section_Id,

· Faculty_Sequence_No, and

· Meeting_No.

A class meeting may exist for which the the instructor is unknown (see Table OIS_Instructor_Class_Meeting). Because a course offering may be instructed in one or more class meetings, the sum of enrollment for all instances in OIS_FWS_ENROLLMENT is greater than the sum of course enrollment.

A instance in the table OIS_FWS_ENROLLMENT is either:

An assembly of instructor(s) and students at a scheduled time or place, or

All independent study registrations for the indicated faculty sequence number for the indicated course

(see Notes above on use of the Table OIS_Instructor_Class_Meetings.)

Attributes in OIS_FWS_ENROLLMENT are:

Data Length

Attribute Type (In Bytes)

YEAR Text 4

QUARTER Text 1

COURSE_BRANCH Text 1

CURRIC_ABBR Text 6

COURSE_NO Text 3

SECTION_ID Text 3

FACULTY_SEQUENCE_NO Text 5

MEETING_NO Text 1

ENROLL_10 Number (Double 8

SCH_10 Number (Double 8

TABLE: OIS_FACULTY_ACTIVITY

Entity Represented: A person holding an University appointment who was or who could have potentially been an instructor[5] during the indicated Year and Quarter.

An Entity Is Uniquely Represented By (primary key): Year, Quarter and Employee_Id.

Instance Attributes, in this table, are:

YEAR is the annual calendar period.

QUARTER is the academic term. (See Lookup Table - OIS_Quarter).

EMPLOYEE_ID an unique identifier of a person. (Note: this identifier is most often the individual’s social security number but not necessiarly so. Not all University instructors are required to provide a social security number. In such circumstances the individual is assigned an alternative unique 9 digit identifier. Hence this attribute is named EMPLOYEE_ID, not social security number).

HOME_DEPT_BUDGET is a budget number assigned in the Higher Education Personel and Payroll System (HEPPS) identifing the Instructor’s home department. The home department is responsible for personnel decisions and actions for the indicated Instructor. (Note: an Instructor may be paid from budget sources outside that of the home department). (See Lookup Table FI_BUDGET_INDEX).

NAME is the name associated with the EMPLOYEE _ID as recorded in the Higher Education Personnel and Payroll System.

JOB_CLASS identifies the Instructor’s class of duties (see Lookup Table OIS_JOB_CLASS) expected to be performed. The JOB_CLASS shown in that of the first appointment, if more than one, as recorded in the Higher Education Personnel and Payroll System.

UPDATE_DATE this attribute is not now supported and should not be used. It is reserved for future use.

OTHER_FAC_STUD_CONTACT_HRS this attribute is not now supported and should not be used. It is reserved for future use.

OTHER_INST_REL_ACTIVITIES_HRS this attribute is not now supported and should not be used. It is reserved for future use.

TOTAL_DIRECT_INSTR_FTE this attribute is not now supported and should not be used. It is reserved for future use.

INST_RELATED_RES_SCHOLAR_HRS this attribute is not now supported and should not be used. It is reserved for future use.

INST_RELATED_RES_SCHOLAR_FTE this attribute is not now supported and should not be used. It is reserved for future use.

ADMIN_SRV_HRS this attribute is not now supported and should not be used. It is reserved for future use.

ADMIN_SRV_FTE this attribute is not now supported and should not be used. It is reserved for future use.

ST_BUD_RES_HRS this attribute is not now supported and should not be used. It is reserved for future use.

ST_BUD_RES_FTE this attribute is not now supported and should not be used. It is reserved for future use.

ST_BUD_PUB_SRV_HRS this attribute is not now supported and should not be used. It is reserved for future use.

ST_BUD_PUB_SRV_FTE this attribute is not now supported and should not be used. It is reserved for future use.

SPONSORED_RES_HRS this attribute is not now supported and should not be used. It is reserved for future use.

SPONSORED_RES_FTE this attribute is not now supported and should not be used. It is reserved for future use.

OTHER_NON_ST_FUNDED_HRS this attribute is not now supported and should not be used. It is reserved for future use.

OTHER_NON_ST_FUNDED_FTE this attribute is not now supported and should not be used. It is reserved for future use.

TOTAL_APPT_FTE this attribute is not now supported and should not be used. It is reserved for future use.

COMPLETENESS_LEVEL this attribute is not now supported and should n