5. Create Set of Student Term Records (1-5 Completion Bonus)

Student Term Records Creation

ObejectivesObjectives

Make a new set of student term records for a new student (status = planned) or
Make a new set of ~~studnet~~student terms when a student progresses into a new grade span.
Make one student term record for every term the student is planning to attend in the associated grade span.
Associate the new student term records to student.
Associate the new student term records to the school term.
note: students are only associated to school terms through the student term association

From the student table, we know the following fields:

Student's first term - calculated by looking up the school term based on the start date
Grade level - the students current grade level
Grade span - the planning horizon for that student based on their grade level
Grade span end grade - the end grade field from the associated grade span record

From the school term we know the following fields:

Ordinate - the sequence of school terms, adding 1 to the ordinate finds the next record in the sequence
Flag field - official end term is referenced from the associated term label record

Required records (must already be created)

Academic year records until the end of the grade span
School Term Records until the end of the grade span

New Students

For a new student, there are no student terms records in the system. The first student term begins is the student's start term. The school term name orresponding to the first student term is entered into the student record field, "student start term." The student sequence field of the student term of this first student term will be 1.

New students may not have school terms made for a complete year. If they enrolled in the last term of the school year, then for that school year, there will only be one school term created.

Recurring Students

A recuring student already has at least one student term in the system. The last student term is found by the maximum student sequence field value.

Grade Spans

The student terms records are created from the point the student enters the school until the end of the student's assigned grade span. Grade spans are automatically updated when the student's grade level increased to land in a new grade span.

For example, If the student is promoted from grade 5 to grade 6, and grade 6 is in a different grade span (like US middle school) then this ichange will trigger that all the student term records will be created until the student has completed 8th grade. The student terms for the next grade span (high school) will not be created until the student is promoted into the next grade span.

Student Term's Student Sequence

The student term's student sequence stores the sequence the student progresses through the terms. Student Sequence is a continuously increasing integer value from 1, the first term at the school, until the student has graduated from all available grade levels. For example, a student enters the fall term of kindergarden, the school has defined two school terms. 13 years later, the student's last term (13x2 = 26) will have a student sequence of 26.

Student Term at_grade

The student term must store the predicted grade level of the student for that specific term in the at_grade field. At first, the at_grade is the starting grade level of the student, after the end of the academic year, the student's at_grade will increase.

Trigger to increase the at_grade

When a student term is created, the record is associated to a school term, which is associated to a term label record. If that term label record is flagged (flag field) as the official end term of the school year, the student's grade level will increase by one. With the completion of the official end term, the student is promoted, and so the at_grade vaue increases for the next student term.

Determining the end of the grade span

A student is at the end of a grade span when their grade level is promoted to the next grade level that falls into the next grade span. The predicted grade level is stored in the student term at_grade field. When the (at_grade field value + 1) is greater that the last grade level in the grade span, then the algoritym does not make the student term record.

So if the student's predicted at_grade is greater than the end grade of the associated grade_span, then the student's predicted grade level exceeds the grade span, student term records do not need to be created for the next span.

Optional and Required School terms

Schools may configure optional terms, for example a summer term that students may or may not take. Student terms are made for both optional and required terms.

Student Term Name

The student term name captures the most ~~relvent~~relevent information, the school term label, the student ~~inforamtion,~~information, and the ~~gade~~grade the student was at during that term.

Set the student term name to concatenate: Associated Term Name, Associated student initials, Student Code, “GL” grade level number (use at_grade value), example from Kassidy’s Record: Spring 2023 KKB14 GL3

For New Students

Key Trigger: New student record added to the student table.

Find the school term with the same name as that in the student start term field. Store the ordinate of the school term record. Note: From the student start date, the school start term has already been determined.

Create a student term record and set the student sequence to 1.

Associate the student term record with the school term record found above and the student

Set the at_grade field = student's current grade_level

Check the associated school term record's flag field, if the flag field value is "official end" then at_grade = previous value + 1

Check the new at_grade value. If the new at_grade value is greater than the student's grade_span's end grade, then stop. note: continue if the new at_grade value is equal to the end grade, this representst the last year of the grade span.

Find the next school term with ordinate = last school term ordinate + 1.

Create a student term record and set student sequence = last student sequence + 1.

Associate the student term record with the school term record found above and the student

Set the at_grade field in the student term record to the latest value

Check the associated school term record's flag field, if the flag field value is "official end" then at_grade = previous value + 1

Check the new at_grade value. If the new at_grade value is greater than the student's grade_span's end grade, then stop. Else repeat.

For Students being promoted into a new grade span

When a student is promoted from one grade span to another, PEMS will make the student term records associated with the new grade span.

Key Trigger: Student grade span has changed.

Find the student's student term with the maximum student sequence, this is the last student term record created. Store the studen term record's student sequence. Store the ordindate of the associated school term record.

Create a new student term record, set the student sequence the the maximum value (found above)+1 (create the next sequencial student term)

Associate the new student term record to the school term with ordinate equal to the stored value + 1. (the next sequential school term)

Set the at_grade field in the student term record to the student's current grade level

Check the associated school term record's flag field, if the flag field value is "official end" then at_grade = previous value + 1

Check the new at_grade value. If the new at_grade value is greater than the student's grade_span's end grade, then stop.

Find the school term record with ordinate = last value + 1. Set the student sequence to last value + 1. Repeat until at_grade is greater than the student's new grade span end grade.

Potential for Errors

If the ordinate fields of the school term records do not increase sequentially by one, then program will not find the school term that the student term must be assoicated to.

If there is no term label desingated at the official end term, then the program does not know to increase the grade level and will never get to the end of the grade span

Pre-existing records - if some students terms have already been created, for example in our data migration, this automation will not address the fact that some records already exist

Start-date change - until the start date has passed in time, the start date may be changed for incomming students. The fields will recalculate the first start term if the start date slips into the next school term. The student terms will need to be re-ordered sequentially.

Future Problems

For high school students, they need terms made up for the transcript records (equivalent to enrollment records) for the past, prior to enrollment so that their reocrds will show these courses taken. This function is not needed for K8 students.