CS 146 Spring 2009: Project

Banner++: CS Student Admissions, Records and Registration system

CS-STARRS

Summary

The project is to implement an on-line admissions and graduation clearance system for graduate students applying to the Computer Science department. The current system allows students to apply online but the process by which the faculty reviews the applications is a paper process. Additionally the registration hold removal and the graduation clearance process is also a pure paper process (which I am sure you are not happy with!). In this project you will design a system that will automate the workflow process in the admissions, registration and graduation system for Masters degree students (you can also consider Bachelor’s degrees and PhD degrees but their graduation requirements are more complex). You will implement this project using the PhP and MySQL installations available on the SEASCF computer systems.

We can identify a number of key applications that need to be implemented:

Online Application Process

A graduate applicant goes to a website and enters their information into the database. Applicants can check their application status online – the status is one of three (i) application incomplete (ii) application complete and under review and (iii) decision (admit or reject).

Admissions Process

The graduate admissions committee reviews applications and makes a decision -- Admit, Admit with Aid, or Reject. We want this review process to be automated, wherein the faculty reviewer can enter their scores into a review form.

Online Registration

Admitted students enroll at GWU (i.e., matriculate) and are now considered Graduate students. Once the student joins GWU, they will use an online registration system to register for courses and their transcript is recorded in the database (courses and grades). Before they can register, they need to have their registration hold removed.

Graduation Process

Each student fills out an online Form 1 which lists the courses they will take to meet graduation requirements. When the student applies for graduation, the system must check to see if all graduation requirements are met. Once they are met, and the student is cleared to graduate they are then added to an alumni list. For simplicity, we will only consider graduation clearance for the Masters program.

Details of the applications and on the type of data (and the forms used for review) are provided next. Note that you have to visit the department website and check on the graduate program requirements to figure out more details for graduation process.

CS-STARRS: Project Details

I have tried to break down the details according to the process, types of users, and the typical queries (in addition to the queries required to implement the workflow process).

Summary of the Overall Workflow Process:

The Graduate Student database applications process goes all the way from application being received in the department to the student’s graduation clearance.

Here is an outline of the process:

1. An application is received by the department, and an entry is made into the database (indicating date application was received, and the data for the student including academic data such as GRE,GPA etc.).

(Note that in practice, this data could also be downloaded from Banner and uploaded into our tables.)
The applicant is emailed a password to use to login to the CS-STARRS system. Using this they can check the status of their application.

(Note: If you cannot implement this, then you can use their student ID as their password for access to STARRS).

2. A decision is made by the Admissions Committee – decision can be Admit or reject. And admit can be ‘admit with aid’ or ‘admit without aid’ – the admission date is also included in the data.
3. An applicant can check on the status of their application by going to the website and entering their password. The status is “Application Received and Decision Pending”, or “Application Materials Missing” or “Admission Decision: Accepted” or “Admission Decision: Rejected”.
4.An admitted student shows up at GWU (i.e., matriculates) to enroll for courses and is officially a grad student in the department. The executive aide, henceforth referred to as the ‘Graduate Secretary (GS)’, officially enters the student into a ‘current graduate students’ database. Note that they must first be matriculated by the GS before they can enroll.
5. Once admitted the student registers for courses using an online registration system.

The first time they register for courses (i.e., as soon as they are matriculated) they must submit an advising form listing the courses they will take and the advisor must "sign off" electronically on their advising form before they can register (to simplify matters, we will assume that this is not required for their second semester onwards). The students enrollment information is kept in the database – i.e., the courses enrolled in, year and semester, and grade (for courses they are under progress the grade is an IP).
You are required to implement a rudimentary registration system.
Students complete a Form 1 that lists the courses they wish to take to meet the degree requirements.

6. Student applies for graduation, and if cleared for graduation (the system does an ‘audit’ to check if the student meets degree requirements) the student is removed from the database (by the executive aide) and automatically added into the alumni database.
7. Alumni can visit the website and update their contact information and also retrieve their transcript.

Now let us take a closer look at the process, and information, involved in the workflow process for each of the applications that you have to examine for your project.

A prospective graduate student, henceforth referred to as a ‘graduate applicant’, visits the URL for the admissions/applications. They are presented with an online application form which they proceed to fill out if they are applying to the graduate program. Once the application is complete their application is evaluated by a faculty committee and a final decision is made. The workflow description below is what you are required to implement in the project (you will implement it in phases working in a team).

Application Process Workflow:

A graduate applicant visits a specific URL and enters a form to fill in their application for admission. When they have completed filling in all the information they submit their application.
The transcript is mailed by the applicant, and its receipt is noted in the database by the GS.
The recommendation letters are entered online by the letter writers. (A simpler way is to also have the letters mailed, as with the transcripts, but this simpler implementation will result in fewer points earned by your project.)

Note that because of the above two requirements, the student’s application can be incomplete even though the student has entered all their information.

The application is considered to be complete when all required information has been entered by the applicant, and the transcripts and recommendation letters have been received.
An applicant can check on the status. The status is updated by the admissions process.

Information to be stored:

There are two types of information that they need to fill out:

Personal Information

This includes their name, social security/student number, address (which you may choose to leave as an option if you wish – but this is a bad practical decision), contact information such telephone numbers and email address. Other personal information may be deemed useful and you have to figure this out.

Academic Information – this includes details on their academic background.

Degree sought: they specify which degree program they are applying for. For this project, we assume two programs: (1) Masters (MS) program, and (2) Doctoral (D.Sc.) program.
Prior degrees. To simplify matters we will only keep track of a maximum of 2 degrees – their Bachelors degree and their Masters degree (if applicable). The degree information should also specify the year they got their degrees, their GPA in that degree, the University/College that they received their degrees from.
GRE scores. They should enter their total GRE score and their scores in each of the three categories: Verbal, Analytical and Quantitative. GRE subject scores may also be entered if applicable. Note that GRE scores are required of Doctoral applicants but not required for students applying for the Masters program (although Masters applicants can submit GRE scores if they their scores) – thus you may not want to have this field as required for all applicants.
Prior Work experience: an applicant can specify in a sentence or two their past work experience.
Admission Date they seek: they specify which semester and year they are applying for.
Date of application – this is the date that they submit their application. It should be a system generated date.
Area of Interest: an applicant specifies an area of interest. This is not required but they may choose to specify this.
Transcripts -- an applicant must have their transcript sent directly to GW. This assumes that the transcript will be mailed and the receipt will be noted in the system by the GS (i.e., the GS will go into this field and update it to record that the transcript has been received).
Recommendation Letters -- an applicant must specify the references who will write the recommendation letters. The information supplied should be the name of the letter writer, their email address, and their title and affiliation. (Note: so as not to generate spam, you MUST enter only your email address when you are testing your application). We require that recommendation letters be submitted using an online system -- i.e., the letter writers will be sent a link where they must go and enter and submit their letter. If you cannot get this to work, then you can go with a model identical to transcripts (i.e., hardcopy mailed and then the GS updates the status) -- however, going with this simpler system means you will earn less points (although you could end up having a working system with a subset of the features specified by the client).
After filling in all the information they submit their application and are henceforth an applicant entity in the system. Ideally, when they submit their application the system should generate a unique password for them and email it to them. This password should be used by the applicant to query the system for their application status. As a minimum requirement you should at least let the applicant query the system using their student number, while noting that this may represent serious privacy concerns.
Note that the system must also keep track of their current application status.

Note: As is the case with the other applications, you should note that some of the information that has to be entered is a selection from a menu. For example, an applicant cannot enter "autumn 2004" in the application date -- rather their choices are limited to "Spring" or "Fall" and the year is the current year.

Checking on their application status:

A graduate applicant can check on the status of their application by visiting a specific URL.

They enter their student number (ideally it should be a password emailed to them by the system when they fill in an application).
The system should retrieve their status – the status can be

Application Incomplete – <field> materials missing. (the <field> can be transcript, recommendation letters, or whatever is entered by the executive aide).
Application Complete and Under Review/No Decision Yet
Congratulations you have been admitted. The formal letter of acceptance will be mailed.
Your application for admission has been denied.

Note that the status of the application is updated by the graduate admissions committee or the graduate secretary (GS). Note that updating the status implies it is connected to the admission decision process which is explained next.
Every applicant after the admissions review will end up as an admitted applicant or a rejected applicant. Note that an admitted applicant does not become a current graduate student until they actually enroll at GWU; consequently, not every admitted applicant is a current graduate student but every current graduate student was an admitted applicant at some time (year).

Admission (Application Review) Process

The application review process requires (potentially) multiple users to enter data and to reach a decision. It changes the status of an applicant to a final status of admit or reject. The final decision (of admit, admit with aid or reject) is made by a user and not by the system – although the final decision must be stored in the system.

Once an application is complete, it is ready for review for admission. The application is reviewed by a faculty admissions committee. Typically, the committee consists of 4-5 faculty.
The faculty committee will be given a list of completed (but decision pending) applications. This can be in the form of a list of student numbers. While this process seems okay, note that this again requires manual intervention.

You should think of automating this process – for example, when a reviewer logs in they can be presented with a list of pending applications which have not been reviewed (for fairness you can sort them by date received; for simplicity you can sort them by student number or name). They can click on an entry in the list to bring up the review form for that applicant.

Typically a number of reviewers can evaluate an applicant; for simplicity assume that each application is reviewed by a single reviewer. The review process starts when the reviewer brings up a student’s review form. To add bells and whistles to your system you can consider multiple reviewers (and thus earn more points for the project).

Given a student number, the system must generate the Review form – the data for the form is taken from the database based on what the applicant has entered. In practice, the reviewer also looks at the student folder at the detailed transcript, letters, etc. – in this project you can assume that they only look at the online review form!

A reviewer completes his review by filling out the various fields in the review form, and making a recommendation. The recommendation can be one of four options and represents a ranking: (1) reject, (2) borderline (to mean they are not certain), (3) admit without aid, and (4) admit with aid. The four numbers 1 through 4 in a sense correspond to a ranking of the applicant. If a number of reviewers were to review this applicant, one can calculate an average ranking. The reviewer can also add their comments (to make it simple assume that they can write about 40 characters in their comments) and recommend an advisor (again, you can simplify by assuming that this has to be one of the faculty in the department). When the review is complete the application is said to have been “reviewed” – but a final decision has NOT been made.
To get an idea of the review process and data see the sample review forms and sample (fictitious) reviews.
A final decision (of admit with aid, admit, or reject) is made by the committee as a whole – but in practice only the Chair of the admissions committee (the CAC) enters this decision into the system (which should only display admit or reject – the admit with aid is an internal note and is not actually entered into the banner system) with the help of the Grad Secretary (GS). When a final decision has been made, the status of the applicant is updated and the application review process is now complete.

Information to be stored:

The academic information about the applicant is retrieved from the applicant’s entries in the on-line application process and the final status (the status should have been created by the earlier application) is decided in the admission process. However, the admission review process itself creates new information –this information is created during the review process (see the sample review form). Some of this information is:

Reviewer ranking/recommendation

Reviewer comments

Advisor recommendation

Registration Process

An admitted graduate applicant may choose to accept GWU’s admission and enroll at GWU – the student now becomes a current graduate student in the department. Once they are a student, they enroll in courses. When they apply for graduation an “audit” is performed on the courses and they are cleared for graduation if they meet the degree requirements. A detailed description of the workflow process follows.

Workflow:

An admitted student turns up at GWU and is formally admitted, i.e., matriculated, by the Grad Secretary (GS); in terms of the STARRS system, the GS processes a form which creates an entry into a Graduate Student table with the student’s information.
Once formally admitted and registered in the system as a graduate student, they enroll for graduate courses using a web registration system.
To register for the first time (i.e., in their first semester), they must fill out an advising form and their advisor must "approve" their registration plan. You can assume that they have a "registration hold" on their account until their advisor clears the hold. Once the hold is cleared it will remain cleared during their entire time as a student (i.e., unlike the undergraduate system at SEAS-GW they do not have to get their advising hold lifted each semester). (However, if you wish to implement a system where they need to get their registration hold removed each semester then that would count as extra features in your system.) The advising form will specify their name, ID, and the list of courses they plan to take in their first semester. Once the advising hold is lifted (by the advisor) they can register for courses using the web registration system.
The web registration system enables students to enroll for courses, and faculty (or the GS) to assign grades. A student will be able to use the online registration system to register for courses, and a faculty (or the GS) can use the system only to enter the final grades. The valid final grades are (A, A-, B+, B, B-, C+, C, F).
At any time the GS, a faculty member, or the student can query the system for the current transcript of the student. Courses currently in progress show up with a grade of IP (in progress).

Graduation Process

Workflow:

A student must specify their entire program of study by filling out a Form 1. This lists the courses that they will take to meet the Degree requirements (this is somewhat similar to the curriculum sheets that undergraduates must follow to meet their degree requirements.). A sample of a Form 1 is provided here.

A student should have the ability to edit their Form 1, i.e., to change their Form 1 at any time before applying for graduation.

After completing the requirements for the degree to which they are admitted, the student formally applies for graduation by visiting a URL, “Apply for Graduation”, and entering their student number and selecting the degree to which they are applying.

Once a student has applied for graduation, the system performs an ‘audit’. Specifically the system checks to see if the student has satisfied all the degree program requirements.

This requires that the system check the courses the student has taken and compare them with the program requirements (both course requirements and GPA requirements) and compares them with the courses the student listed on their Form 1. (For example, if they have taken a different set of courses than listed on their Form 1 then they will not be cleared for graduation). You could simplify the process by checking for program requirements when they submit their Form 1 -- i.e., check if the courses listed on their Form 1 meet the course requirements of the MS program; thus, the application for graduation will only test if they have filed a Form 1 and if they satisfy the GPA rule.

To simplify the project, assume that only M.S. degree students are considered for this application (i.e., only MS students will apply for graduation).

The program requirements for an MS degree are posted on the graduate academics section of the CS-GWU department website: http://www.cs.gwu.edu

The set of requirements that you should use (at a minimum) are: student must take 10 courses (each is 3 credits), must complete at least 30 credit hours, and must take the three core courses CS210, CS211, CS212, and must have at most 2 grades below B-, and must have a minimum GPA of 3.0.

If a student has met the program requirements, they are “cleared” for graduation. Else, they are “not cleared” for graduation. If they are not cleared, then ideally the system must inform them why they have not met graduation requirements.

Once a student is cleared for graduation, the GS formally process their application and they “graduate”. (Note that, similar to the admission review process, a student can be cleared for graduation but they do not actually graduate until the GS, or another authorized user, enters this information into the system and formally clears them.)

The process of graduation must be automated; i.e., the GS need only check the “cleared for graduation” students and approve their graduation by clicking on some selection. (In practice the GS actually looks through their folder and transcript.)

When a student “graduates” they are removed from the Graduate student table and their information must be entered into an Alumni table. Note that only a summary of their academic information should be kept in the Alumni table.

In a real system, the enrollment information for a student is not removed since they may re-enroll at GWU for another degree. Thus, a graduation process would only require that their data be tagged to indicate that they have graduated with a degree while keeping all their information intact.

An alumni can only edit their personal information (such as email, address) by logging on to a URL using their student number (again, in practice it is better to give them a unique password rather than create privacy problems by using their student number).

Information to be stored for Registration and Graduation Processes:

Some of the information to be stored by this process includes:

Student number, name, address, and other personal information for the graduate student.
Information should also include the degree they are enrolled in and their status (i.e., have they graduated, have they applied for graduation, cleared, etc.), their admit date (Semester and Year).
The registration system must store information on the courses, the faculty teaching the course, the grades (if assigned), the schedule (time and day). The course registration application must check for schedule conflicts.

To simplify the registration system you can assume the following. Each course has a title, a course number (dept followed by course number) that can be treated as a unique course number, a section number, the semester it is offered in, and the number of course credits (assume maximum credits is 3). Assume that each course only has one section.
To simplify the registration system you MUST implement the list of courses provided -- with the specific titles listed in the document. You can choose to populate it with additional courses if you choose. The course list also specifies the course pre-requisites, and also specifies a time schedule that you should use for your registration system.
The enrollment/registration information must store the course, the semester, the class time, day, the student identification, the instructor information, and the grade. Note that a course offering is unique when one considers the course number and the semester&year that it was offered in. For simplicity you should assume that courses are scheduled on M, T, W, R, each course meets once a week (like most of our grad courses), and the valid time slots are 3-6pm and 6-9pm only (i.e., only two time slots). Assume that the same schedule is applied every semester (i.e., the schedule shown in the list is assumed to be in place every semester). Your system MUST allow students to take courses in the 3-6pm and 6-9pm time slot on the same day (i.e., the time gap between consecutive classes is 0 minutes).
You should implement at least the adding of a course. i.e., you should also try to implement a "drop" application.
Faculty users (or the GS) should be able to enter the grade for courses they are teaching.

Course enrollment information for graduate students. The enrollment information essentially reflects the course registration system; i.e., must store courses taken by the student, the semester and year, the final grade, the number of credit hours of the course.

IF you want to simplify the registration process, assume that (a) there is no limit on the number of students in the class, and (b) assume that students can only register for graduate credit courses (they could be numbered 100 level or 200 or 300 level). You will get extra points if you implement a more complex system.

Program requirements for the degree (in your project, only the MS degree) are stored in the system. This will allow changes to the program requirements to be made if necessary.
Alumni information must be stored. This information includes name, student number, year they graduated and degree, GPA, and personal information such as address, telephone, email etc. Alumni should also be able to view their transcript (but not add/drop courses).

Other Queries/Reports: To be implemented in final Project

Other relevant queries that may be submitted to the system in order to generate specific reports. Some of these queries are listed below – in some cases these are essential to providing a complete system (or subsystem). Note that the queries may be considered to belong to either (a) admissions or (b) registration and graduation. You will need to implement these queries in the final phase (Phase 3) of your project; but you should look at these early so that your design will allow for these queries to be implemented.

Search for an applicant using their last name or student number. This query can be submitted by the GS or by a faculty reviewer.
Update applicant’s or students' academic and personal information – an applicant may choose to update their information at any time. This can be simplified by having only the GS perform this -- but this is not an ideal solution. An applicant or current student should be able to update their information.
Given the Semester, or Year or Degree program, generate the list of graduate applicants. This query can be submitted by the GS.
Given the semester or year or degree program, generate the list of admitted students. This query can be submitted by the GS.
Given the semester or year or degree program, generate statistics such as total number of applicants, total number admitted, total number rejected, average GRE score for admitted applicants, etc. This query can be submitted by the GS.
Given the semester or year or degree program, generate the list of graduating students (i.e., those cleared for graduation). This query can be submitted by the GS.
Given the semester or year or degree program, generate the list of alumni and their email address.This query canbe submitted by the GS
Generate total list of current students (by degree or by admit year).This query can be submitted by the GS
Change the advisor for a student, given the student’s student number. This query can be submitted by the GS
Given a student’s student number, generate the transcript (list of courses and the current GPA).This query can be submitted by the GS or by the faculty advisor or by the student.
For a faculty advisor, generate list of all advisees. This query can be submitted by the GS or the faculty advisor.

Users and Roles:

Observe that there are different categories of users of the CS-STARRS system, and each type of user has specific roles and authorizations. Each of these users should be presented with an appropriate interface through which they can perform their authorized tasks.

System administrator

Has access to everything and must be able to add/drop all the types of users, add/drop courses into the course schedule (in the registration system), and any other tasks that you think are appropriate for such a user..

GS/Exec.Aide

Has complete access to applicant’s data and to current student’s data. They are responsible for (1) updating status of applicant, (2) matriculating a student (i.e., changing an admitted applicant to a current student once the student enrolls at GW), and (3) clearing a student for graduation. Note that they cannot create new users.

Faculty Reviewers (including Chair of the admissions committee -- the CAC)

They can review the student’s application – they have access to all the applicant’s information. They enter their review into a review form which is stored in the system.
Reviewers does not include all the faculty.

Faculty Advisors

These are faculty in the department.

They can view their advisees transcript but cannot update the transcript. This is the only access they are given.
They can view all information about an applicant but cannot enter a review.
They will need to lift the advising hold of a new graduate student.
Not all faculty need be advisors -- but if you assume otherwise, it is not a major problem.

Faculty Instructors

They can enter grades for the students in the courses they are teaching (i.e., courses for which they are the instructor).

Make sure you have some faculty (at least one) who are reviewers and advisors and instructors.

Applicants.

They can enter their graduate application form information, and can check on the status of their application. They are not permitted to perform any other functions.

Current Graduate Students

They can view their transcript (but cannot update it) and can apply for graduation. They can update their personal information (address, email etc.) but no other information.

Alumni

They can log into the system and edit their personal information only. They should be able to view their transcript.