database design process

Step 1 consists of collecting the relevant data and preparing a statement of the situation.

 

The Requirements for the Athletic Grant-in-Aid Component

There are roughly 450 scholarship athletes during the Fall and Spring terms. In Summer Session I of 1999, there were approximately 160 students; 90-100 participated in Summer Session II 1999. A full scholarship is defined as tuition, fees, room, board, and books (with no restrictions on any component). Athletes may qualify for tuition, fees, room, board, books, insurance (for aliens receiving full scholarships), and reimbursement of their application fees (typically, this is for a small number of athletes receiving full scholarships). In addition, athletes may receive any combination of tuition, fees, room, board, or books, with any imaginable combination of caps (such as fees, and $1,000/tuition, or fees, books, and $500/room).

Many of the design features of the database are controlled by the the mainframe Student Information System (SIS) database used by various departments at UNC, as the Access database is intended to draw data from SIS in the form of imported Excel spreadsheets (such fields include Citizenship, Classification, College, Major, Scholarship Codes, Special Program, Sport, and Tax Status). Further complicating this process are adjustments; award letters may be revised after the term has begun or in some cases after the term has ended. Residency status may change. Students may drop, add, withdraw, or re-enroll retroactively at any point.

My current understanding is that the award process works as follows:

the coach recruits an athlete, they sign an Athletic Scholarship Agreement;

the UNC Department of Athletics copies this agreement to the Office of Scholarships and Student Aid and the University Cashier;

Student Aid generates an award letter to the student, sends a copy to the Cashier (for returning athletes, academic eligibility may be a factor; Student Aid also monitors the total aid package for the student, such as Pell Grants or other awards);

(someone notifies the UNC bookstore of the athletes receiving books);

the coaches prepare lists requesting off-campus/Granville Towers payments for room/board.

The University Cashier receives copies of the award letters, collates them with the blue copy of the Athletic Scholarship Agreement for 1st-time athletes;

the award information is interpreted, entered into the database, then the information on the room/board lists is manually entered into the database;

the database operator requests room/board checks (the process for aliens is more involved, requiring submission of individual check requests for each student to 440 West Franklin Street, together with the required forms for that student, verified for timeliness and accuracy of the SSN -- aliens must apply for SSNs if they do not already have one); formerly, checks for registration (whether the students receiving housing checks in fact are living in University housing, etc.) were performed manually; in AY 98-99, this led to at least 3 errors in which students either received more money than awarded (i.e., a person awarded a $1200.00 room allowance actually received $1300.00, the board allowance amount) or students living on campus received $1200.00 off-campus housing allowance checks.

Reducing the paper component of the process (specifically, in the University Cashier's Office) and moving toward award lists generated via Excel spreadsheets (or Access databases) dramatically improved the process. Combining the 2 lists (awards, room/board requests) into 1 spreadsheet that is compatible for importing into Access would speed processing times while reducing errors. Ideally, Athletics would generate a spreadsheet, send it to Student Aid for verification of eligibility, and Student Aid would forward the list to the University Cashier and the UNC Student Stores.