In order to be a student in CUNYfirst, certain records must exist. These records are ACAD_PROG, ACAD_PLAN, STDNT_CAR_TERM and STDNT_ENRL.
Every student must have a program record, ACAD_PROG, the layout of which appears below.
A) ACAD_PROG
Field Description
EMPLID - Empl ID (key)
ACAD_CAREER - Academic Career (key)
STDNT_CAR_NBR - Student Career Nbr (key)
EFFDT - Effective Date (key; the current record is the maximum effective date, less than or equal to today, for the EMPLID, ACAD_CAREER and STDNT_CAR_NBR)
EFFSEQ - Effective Sequence (key; this field allows multiple transactions for the EFFDT. The current record is the maximum effective sequence for the EFFDT.)
INSTITUTION - Academic Institution (this is not a key but should be considered one because there are multiple institutions in CUNYfirst. Always delimit your selection by INSTITUTION.)
ACAD_PROG - Academic Program (the student's program - MPA, MA, MBA, UGRD, etc.)
PROG_STATUS - Academic Program Status ('AC' - active – a student MUST be active to be term activated; 'CM' - completed – the student has graduated; 'CN' - cancelled; 'DC' - discontinued; 'DM' - dismissed. This field is derived from PROG_ACTION.)
PROG_ACTION - Program Action (what is happening, such as MATR - matriculation, DATA - data change, DISC - discontinuation, etc.)
ACTION_DT - Action Date (the date of the action, always defaults to today's date)
PROG_REASON - Action Reason (why the action is taking place)
ADMIT_TERM - Admit Term (the term when the student started)
EXP_GRAD_TERM - Expected Graduation Term (the term when the student should graduate)
REQ_TERM - Requirement Term (the term which governs the rules for graduation.)
ACAD_LOAD_APPR - Approved Academic Load
CAMPUS - Campus
DEGR_CHKOUT_STAT - Degree Checkout Status (if a student has applied for graduation, or has graduated; 'AG' - applied, 'AW' - awarded, etc.)
COMPLETION_TERM - Completion Term (the term in which the student graduated)
ACAD_PROG_DUAL - Dual Academic Program
JOINT_PROG_APPR - Joint Program Approved
ADM_APPL_NBR - Application Nbr (if this student came through the Admissions process, this number can be used to retrieve the application.)
APPL_PROG_NBR - Application Program Nbr
DATA_FROM_ADM_APPL - Data from Admissions Appl
SSR_RS_CANDIT_NBR - Candidate Number
SSR_APT_INSTANCE - APT Instance
SSR_YR_OF_PROG - Year of Program
SSR_SHIFT - Academic Shift
SSR_COHORT_ID - Cohort Tag
SCC_ROW_ADD_OPRID - Created By (the user who added the record.)
SCC_ROW_ADD_DTTM - Created (when the record was added.)
SCC_ROW_UPD_OPRID - Updated By (the user who change the record last.)
SCC_ROW_UPD_DTTM - Last Update Date/Time (when the record was changed last.)
B) 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. ACAD_PLAN is owned by ACAD_PROG, so the five keys of ACAD_PROG MUST equal the first five keys of ACAD_PLAN. The table layout is below.
Field Description
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.
Key Description
A) Working backwards, the STDNT_ENRL contains the following keys:
- EMPLID
- ACAD_CAREER
- INSTITUTION
- STRM
- CLASS_NBR
- T
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.
D) Every student must have an ACAD_PROG record. The keys to this table are;
- EMPLID
- ACAD_CAREER
- STDNT_CAR_NBR
- EFFDT
- EFFSEQ
- Other related tables which contain important information are:
- ACAD_PROG_TBL – details about the program.
- ACAD_PLAN_TBL – details about the plan.