Class/Course Module.

re: 07/26/02

The cl_course class module will contain the information that is to be tracked concerning the COURSES offered and the PEOPLE that took those courses.
MFS will be able to enter courses into the system that are scheduled in the future. Members of the MFS users will be able to search for courses on-line, sign up for them, and drop them. After a course is offered, MFS will be able to enter the participants in that course into the system for reporting purposes. Entry of information will be through the ORACLE interface. The online interface will allow searching for courses by date offered and location offered. Course locations must be classified by COUNTY.

The COURSE relationship diagram is depicted below:

CL ER

Table Definitions:

A summary of the table definitions depicted in the ER diagram above, is listed below. The complete definition of these tables can be found on the Max Mart, Inc. project web site and will be included in the final CL documentation.

There are three major themes modeled by these tables including INSTRUCTORS, CONTINUING ED, and COURSES.

INSTRUCTOR Information:

The instructor information is stored in CL_INSTRUCTOR. This table provides the additional information needed to track the instructors that offer courses. All instructors are also in the PS_PERSON table with their basic information being stored there. The only EXTRA information stored on an INSTRUCTOR is their ALTERNATE_ID which can be the SSN or BANNER ID to use for payments, etc.

Table: CL_INSTRUCTOR

Description: This is a subtype of person. An instructor is a person who can take classes and check out items.

NAME	TYPE	WIDTH	UNIQUE	REF	DESCRIPTION
ID	NUMBER	10	PRIMARY KEY	PS_PERSON (ID)	The instructor is a subtype of person so this ID will match the person's ID.
ALTERNATE_ID	VARCHAR2	15	UNIQUE		This can be the Instructor's SSN or Banner ID number for use in paying the instructor.

COURSE Information:

The course information is stored in the CL_COURSE table. The scheduled courses are stored in the CL_SESSION. Continuing ED credit equivilances are stored in the CL_CONTINUING_ED_CREDIT table. Enrollment and Instructors for the courses are stored in CL_ENROLLMENT and CL_INSTRUCTOR_SESSION tables respectively.

The structure of the system allows for multiple instructors in a course and multiple students in a course.

Table: CL_COURSE

Description: Holds the courses that are offered i.e. Fire Fighter 1 Introduction.

NAME	TYPE	WIDTH	NULL	UNIQUE	CHECK	DESCRIPTION
ID	VARCHAR2	10		PRIMARY KEY	ID = UPPER( ID )	TRIGGER to UPPERCASE. User entered value for the ID of a class. We may want to have this id be generated and have an alternate id entered by the user.
UNAME	VARCHAR2	100	NOT NULL	UNIQUE		UPPER case name of the course to enforce UNIQUENESS of names.
NAME	VARCHAR2	100	NOT NULL
DESCRIPTION	VARCHAR2	4000				The course description. Can type anything here and it is not required.

Table: CL_SESSION

Description: The sessions of classes that are offered in the different locations. A session is a single offering of a course. Much like Semester/Section at college. A course can be scheduled many times by entering a new SESSION for the course. Each SESSION can have a different date, location, price, instructor, students, etc.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	NUMBER	10		NOT NULL	Generated ID for this course session.
CL_COURSE_ID	VARCHAR2	10		NOT NULL	The course that is being taught.
PS_ORGANIZATION_ID	NUMBER	10			The organization that is sponsoring this course. DO WE WANT THIS REQUIRED?
OFFERED	DATE	0		NOT NULL	Date that the course was/is offered
HOURS	Number	6	2	Not Null	Contact hours that this course will/was offered for.
COST	NUMBER	6	2	NOT NULL	This is the cost of a student attending this course. 9999.99 max value
RESERVED	NUMBER	3		NOT NULL	Number of seats that are reserved by an organization(s) that plans to attend but doesn't have the names yet.
RESERVED_COMMENT	VARCHAR2	800			Comment about the reserved number... could enter the organization, etc that is reserving seats.
DROP_DAY	DATE				This date is to establish how long a person has to be able to drop from a session once they are registered.
LOC_STREET_1	VARCHAR2	40			location address: Street1
LOC_STREET_2	VARCHAR2	40			Location Address: Steet 2
LOC_CITY	VARCHAR2	50			Location Address: CITY
LOC_STATE	VARCHAR2	2			Location Address: State
LOC_ZIP	VARCHAR2	10			Location Address: ZIP
SY_COUNTY_ID	VARCHAR2	5		NOT NULL	County that this course is being offered in. For REPORT purposes!

Table: CL_ENROLLMENT

Description: INTERSECT table to link the person and session for what classes they.

NAME	TYPE	WIDTH	PREC	DESCRIPTION
CL_SESSION_ID	NUMBER	10		Session of the course that is being taken.
PS_PERSON_ID	NUMBER	10		Person who is signed up for the course.
COST	NUMBER	6	2	Cost for this person to enroll in the course. Copy from the cost of the SESSION as the default.
GRADE	VARCHAR2	1		Grade for the course - A-attended, C-completed, I-incomplete

Table: CL_INSTRUCTOR_SESSION

Description: INTERSECT table to link instructor and session. A session can have more than one instructor.

NAME	TYPE	WIDTH	DESCRIPTION
CL_INSTRUCTOR_ID	NUMBER	10	Who is teaching this course
CL_SESSION_ID	NUMBER	10	What session is being taught.

CONTINUING EDUCATION Information:

The information regarding CONTINUING EDUCATION credits is in the CL_CONTINUING_EDUCATION_CREDITS table. The courses can be offered at different continuing education values. These are DATE dependent meaning that the course after a given date will be worth the continuing education credits that were in affect during that date period.

Table: CL_CONTINUING_ED_CREDIT

Description: Indicates which courses have CECs associated with them and the effective date along with the hours of credit.

NAME	TYPE	WIDTH	PREC	CHECK	DESCRIPTION
CL_COURSE_ID	VARCHAR2	10			Course that has the credit
CREDIT_HOURS	NUMBER	4	1	credit_hours >=0	The number of CEC hours that is earned when this course is completed. 999.9 max value
COURSE_NAME	VARCHAR2	20			The equivelant course name to report to the state to get the CECs.
EFFECTIVE	DATE	0			Effective date of CECs. The CECs are available from this date forward until the next record that changes them

CL FUNDING SOURCE

Table that contains names of funding sources for tracking grant funded course sessions.