ss Management Q From 2000-2015, the client was the Scholarship Billing Co-ordinator for the Division of Finance Office of Student Accounts and University Receivables (University Cashier) at the University of X. His responsibilities included scholarship billing during the academic terms 1. Fall (August - December) 2. Spring (January - May) 3. Summer I (May - June) 4. Summer II (June - August) for various scholarships. Historically, billing was done manually. In 2000, the University adopted the Student Information System (SIS), a large mainframe database used by various departments for administrative purposes. Ideally, upon implementation of this database, a systems analyst would have been assigned the task of designing a module dedicated to special scholarship billing; this has not been the case. Currently, scholarship billing relied on 3 large Excel spreadsheets (one each for Athletic Grant-in-Aid, Morehead, and the others listed above). This was a files-based system, with dozens of auxiliary Excel spreadsheets and Word documents used to generate invoices and reports. The aim is create a system to automate the process, improve efficiency, and take advantage of the information contained in the SIS database. Requirements: 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 2005, there were approximately 160 students; 90-100 participated in Summer Session II 2005. 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 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: 1) The coach recruits an athlete, they sign an Athletic Scholarship Agreement; 2) The UNC Department of Athletics copies this agreement to the Office of Scholarships and Student Aid and the University Cashier; 3) 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); 4) The coaches prepare lists requesting off-campus/payments for room/board. 5) The University Cashier receives copies of the award letters, collates them with the blue copy of the Athletic Scholarship Agreement for 1st-time athletes; 6) The award information is interpreted, entered into the database, then the information on the room/board lists is manually entered into the database; 7) 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. Deliverables: 1. Analyse the System's requirements. 2. Suggest how the system can be improved. 3. Create an ERD for the suggested system 4. Create a Logical Schema