In order to be an enrolled student in CUNYfirst, certain records must exist. These records are ACAD_PROG, ACAD_PLAN, STDNT_CAR_TERM and STDNT_ENRL. Every student must have one program and one plan – a student may have multiple plans but one is required. A student must be active in a program in order to be term activated. That process creates the STDNT_CAR_TERM record and a student will have one of these records for each term he/she is term activated. Finally, for each term and each class in which a student is enrolled, a STDNT_ENRL record is created.
A) Working backwards, the STDNT_ENRL contains the following keys:
- EMPLID
- ACAD_CAREER
- INSTITUTION
- STRM
- CLASS_NBR
For each class, a student will have one of these records. This does not mean that the student is enrolled. For that, the field STDNT_ENRL_STATUS must be checked. If a student is enrolled, the status will be 'E'; if a student is waitlisted for a class, the status will be 'W'; if the student was enrolled and subsequently dropped the class, the status would be 'D'. Therefore, you must check the STDNT_ENRL_STATUS for 'E' to determine in which classes a student is enrolled or even if this is an enrolled student for the term.
STDNT_ENRL does not contain much class information, such as subject, catalog number or description. For this detail, you must join STDNT_ENRL to CLASS_TBL, which contains class information. Or, instead of using STDNT_ENRL, use the view CLASS_TBL_SE_VW, which joins STDNT_ENRL to CLASS_TBL for you.
B) Term activation creates STDNT_CAR_TERM. A student MUST be term activated in order to enroll in class. There is a one-to-many relationship between STDNT_CAR_TERM and STDNT_ENRL; for each term a student may have one or more classes. STDNT_CAR_TERM contains the following keys:
- EMPLID
- ACAD_CAREER
- INSTITUTION
- STRM
Another field which should be considered as a key is STDNT_CAR_NBR, which is a key in ACAD_PROG. In our environment, a student may have multiple careers at the same institution, so this field is important.
STDNT_CAR_TERM keeps a running total of term and career information: term and career GPAs; term and career credits taken and passed for GPA, non-GPA, financial aid; academic level, etc. One important field is UNT_TAKEN_PRGRSS. A positive value in this field means that the student is enrolled. If the student is enrolled (STDNT_ENRL) in a three-credit class, UNT_TAKEN_PRGRSS will be three. If the student is enrolled (STDNT_ENRL) in four three-credit class, UNT_TAKEN_PRGRSS will be twelve. Of course, this is not always a reliable test of enrollment. A student could be enrolled in non-credit classes, and therefore be considered an enrolled student but UNT_TAKEN_PRGRSS would be zero.
C) ACAD_PLAN contains the student's plan, which is the major or minor. A student MUST have one plan but there could be more than one. The keys to this table are;
- EMPLID
- ACAD_CAREER
- STDNT_CAR_NBR
- EFFDT
- EFFSEQ
- ACAD_PLAN
The first five keys are the same as ACAD_PROG, which owns ACAD_PLAN, and these values must be the same as the owning ACAD_PROG. There is a one-to-many relationship between ACAD_PROG and ACAD_PLAN – for each program, a student must have one or more plans. The field, ACAD_PLAN, indicates the major or minor to which the student is attached.
D) Every student must have an ACAD_PROG record. The keys to this table are;
- EMPLID
- ACAD_CAREER
- STDNT_CAR_NBR
- EFFDT
- EFFSEQ
The EFFDT allows history for the student to be stored and EFFSEQ allows multiple transactions for the same EFFDT. These are reserve words in PeopleSoft and have special functions. Query recognizes this special-ness and creates code accordingly. STDNT_CAR_NBR is important in the CUNYfirst environment because a student may have multiple careers. Another important field, but not a key, is INSTITUTION. Because CUNfirst has many institutions, this field MUST be considered in order to narrow down the selection and to join to other table.
Other important fields in this table are: ACAD_PROG (i.e., UGRD, MBA, etc.), ADMIT_TERM, DEGR_CHKOUT_STAT and PROG_STATUS. PROG_STATUS could be 'AC' (active – a student MUST be active to be term activated), 'CM' (completed – the student has graduated), 'CN' (cancelled), 'DC' (discontinued) or 'DM' (dismissed). Another field of interest is the ADM_APPL_NBR. If this ACAD_PROG record was created through the application process, this field will have a value. It allows the user to return to the application to find data captured there, such as the ADMIT_TYPE.
- Other related tables which contain important information are:
- ACAD_PROG_TBL – details about the program.
- ACAD_PLAN_TBL – details about the plan.