Problem: For a long time the university tutoring program has used a paper schedule to sign students up for tutoring. Tutors identify their schedule for a two-week period, and then a schedule is printed and placed in the computer lab. Students look through the schedule for sessions that match courses they are taking and the times they have available (see Figure 1 sample signup sheet). This system has worked and continues to work, but it has several significant problems. For one, can be difficult for students to find appropriate tutoring sessions. The paper forms are difficult to navigate and understand. Additionally, it is very difficult for the tutoring program to track the students using the tutoring. It is difficult or impossible to track demographic information. It is also difficult to assure that students are enrolled in the courses they receive tutoring in. Even tracking tutors' hours can be difficult. A database with a client application could significantly improve the situation, by providing a flexible, searchable schedule for students; better tracking of demographics and eligibility; and better tracking of hours tutored. Scope: The tutoring database will manage data for the tutoring program at the university. It will track available tutors and the courses they can tutor. It will also track each tutor's tutoring schedule. The database will store demographic information for students who register for tutoring. This information will be private and used only to generate general reports which include no personal information (see Figure 2). Students who have registered will be able to sign up for available tutoring sessions for courses in which they are enrolled. The database will track whether students attended their scheduled sessions. It will also track student requests for tutoring in additional courses and subjects. Constraints: The database can be used to get the hours worked for each tutor, but it will not process any payroll information. The database will not validate student information against the school's registration database. Objectives Streamline the process by which the tutors enter their schedules and students sign up for them. Improve tracking of demographic data of students using the tutoring program. Improve tracking of tutors' hours and students' use of tutoring sessions. Track student requests for additional tutoring. Additional Information Shelley just graduated with a IS degree. She is hired to develop a database system for the tutoring program. She has been talking with Terry, the tutoring program director, to get more information about the project. Here is an excerpt from Shelley's interview with Terry. Terry: "The computer lab is one of our designated tutoring areas." She shows Shelley a clipboard containing several pieces of paper. "We have 2 pages for each week-an AM one and a PM one (Figure 1 for Am sample). At the beginning of the month, tutors enter their availability for each day, the durations for which they are available that day, and the courses they can tutor for. Students sign up for particular sessions. Tutoring is free for the students as long as they are enrolled in the class for which they are getting tutored." "How long is each tutoring session?" "Tutoring sessions are for 60 minutes each, and a tutor can only do 15 sessions or 15 hours a week." "What if you set up a time slot and nobody signs up?" "As long as the tutor shows up when scheduled, we will pay the tutor for the time. Each tutor is supposed to fill out a short tutor session report form for each session time they sign up (see Figure 3)." Shelley looks over the forms and reports. "What do you do with the schedules and tutor session reports at the end of the month?" "Actually, I take them back to my office every two weeks and type them into various spreadsheets to make term reports to the people who pay for the tutoring and to determine the pay for the tutors themselves." Shelley: "What kind of term reports do you prepare?" "Well, for example how many students are using the tutoring services by ethnicity. What courses/subjects are they taking tutoring for, and how many students each tutor saw. I also need to know how many unduplicated students were seen each term. Figure 2 is an example term report." "Unduplicated?" "Yes, individual students. A single student could get several sessions of tutoring. For some reports, we need to know how many individual students we are serving-not just how many sessions we have scheduled. We really need to know if a student actually got the tutoring they signed up for. Sometimes a student will sign up and then not show up for the actual session. It might also be good to know what courses students want tutoring in where we are not offering it. Maybe you could provide a way for students to request tutoring for courses or subjects." Figure 1 Sample Morning Tutoring for the Week of 4/11/12 to 4/15/2012 Monday Tuesday Wednesday Wednesday Thursday Friday 9:00 AM TT:Pearson TT: TT: TT:Aimes TT: 2 CL: ACCT292 CL: CL: CL:Math196 CL: ST: ST: ST: ST:Laura Jones : ST: TT: TT: TT: TT:Carson TT: CL: CL: CL: CL:IS340 CL: ST: ST: ST: ST:Laura Jones ST: TT: TT: TT: TT:Johnson TT: CL: CL: CL: CL:IS242 CL: ST: ST: ST: ST:Shanna Yaylor ST: 10:00 AM TT: TT:Johnson CL:IS242 ST: CL: TT:Carson TT: CL: IS340 ST:Peter Laws ST: TT:Johnson CL:IS242 ST: Bob Green ST: TT: TT: TT: TT: TT: CL: CL: CL: CL: CL: ST: ST: ST: ST: ST: TT: TT: TT: CL: ST: TT: CL: CL: ST: TT: CL: ST: CL: ST: ST: 11:00 AM TT: TT: TT: TT: TT: CI: CL: CL: CL: CL: ST: ST: ST: ST: ST: Note: TT-tutor; CL-Class; ST - student Figure 2 Term Report Fall Term 2012 Students 3 Total Usage Unduplicated Usage 2345 1492 Difference 916 Unduplicated Demographics Male Female Total 816 676 1492 Ethnicity White 808 AfAm 351 172 81 Asian Pacls NAmer Other 15 65 Total 1492 Subject Area 139 121 ACCT IS ENG Math CSCI 347 139 139 Fin 607 Total 1492 Figure 3 Tutor Session Report Tutor Session Report Form Tutor Name Session Date Session Time Student ID 4 Student Name (NA if no student sign up) Material covered (NA if no show) Terry would like to enforce the following business rules by the database system: (1) A student must be registered in the database for the course (or subject) he or she is planning to receive tutoring before he or she can sign up for a tutoring session. (2) No tutoring session is allowed if a student has accumulated 5 no shows (absences) (3) A tutor should not work more than 15 hours a week ***Conduct the database conceptual design*** 5 Problem: For a long time the university tutoring program has used a paper schedule to sign students up for tutoring. Tutors identify their schedule for a two-week period, and then a schedule is printed and placed in the computer lab. Students look through the schedule for sessions that match courses they are taking and the times they have available (see Figure 1 sample signup sheet). This system has worked and continues to work, but it has several significant problems. For one, can be difficult for students to find appropriate tutoring sessions. The paper forms are difficult to navigate and understand. Additionally, it is very difficult for the tutoring program to track the students using the tutoring. It is difficult or impossible to track demographic information. It is also difficult to assure that students are enrolled in the courses they receive tutoring in. Even tracking tutors' hours can be difficult. A database with a client application could significantly improve the situation, by providing a flexible, searchable schedule for students; better tracking of demographics and eligibility; and better tracking of hours tutored. Scope: The tutoring database will manage data for the tutoring program at the university. It will track available tutors and the courses they can tutor. It will also track each tutor's tutoring schedule. The database will store demographic information for students who register for tutoring. This information will be private and used only to generate general reports which include no personal information (see Figure 2). Students who have registered will be able to sign up for available tutoring sessions for courses in which they are enrolled. The database will track whether students attended their scheduled sessions. It will also track student requests for tutoring in additional courses and subjects. Constraints: The database can be used to get the hours worked for each tutor, but it will not process any payroll information. The database will not validate student information against the school's registration database. Objectives Streamline the process by which the tutors enter their schedules and students sign up for them. Improve tracking of demographic data of students using the tutoring program. Improve tracking of tutors' hours and students' use of tutoring sessions. Track student requests for additional tutoring. Additional Information Shelley just graduated with a IS degree. She is hired to develop a database system for the tutoring program. She has been talking with Terry, the tutoring program director, to get more information about the project. Here is an excerpt from Shelley's interview with Terry. Terry: "The computer lab is one of our designated tutoring areas." She shows Shelley a clipboard containing several pieces of paper. "We have 2 pages for each week-an AM one and a PM one (Figure 1 for Am sample). At the beginning of the month, tutors enter their availability for each day, the durations for which they are available that day, and the courses they can tutor for. Students sign up for particular sessions. Tutoring is free for the students as long as they are enrolled in the class for which they are getting tutored." "How long is each tutoring session?" "Tutoring sessions are for 60 minutes each, and a tutor can only do 15 sessions or 15 hours a week." "What if you set up a time slot and nobody signs up?" "As long as the tutor shows up when scheduled, we will pay the tutor for the time. Each tutor is supposed to fill out a short tutor session report form for each session time they sign up (see Figure 3)." Shelley looks over the forms and reports. "What do you do with the schedules and tutor session reports at the end of the month?" "Actually, I take them back to my office every two weeks and type them into various spreadsheets to make term reports to the people who pay for the tutoring and to determine the pay for the tutors themselves." Shelley: "What kind of term reports do you prepare?" "Well, for example how many students are using the tutoring services by ethnicity. What courses/subjects are they taking tutoring for, and how many students each tutor saw. I also need to know how many unduplicated students were seen each term. Figure 2 is an example term report." "Unduplicated?" "Yes, individual students. A single student could get several sessions of tutoring. For some reports, we need to know how many individual students we are serving-not just how many sessions we have scheduled. We really need to know if a student actually got the tutoring they signed up for. Sometimes a student will sign up and then not show up for the actual session. It might also be good to know what courses students want tutoring in where we are not offering it. Maybe you could provide a way for students to request tutoring for courses or subjects." Figure 1 Sample Morning Tutoring for the Week of 4/11/12 to 4/15/2012 Monday Tuesday Wednesday Wednesday Thursday Friday 9:00 AM TT:Pearson TT: TT: TT:Aimes TT: 2 CL: ACCT292 CL: CL: CL:Math196 CL: ST: ST: ST: ST:Laura Jones : ST: TT: TT: TT: TT:Carson TT: CL: CL: CL: CL:IS340 CL: ST: ST: ST: ST:Laura Jones ST: TT: TT: TT: TT:Johnson TT: CL: CL: CL: CL:IS242 CL: ST: ST: ST: ST:Shanna Yaylor ST: 10:00 AM TT: TT:Johnson CL:IS242 ST: CL: TT:Carson TT: CL: IS340 ST:Peter Laws ST: TT:Johnson CL:IS242 ST: Bob Green ST: TT: TT: TT: TT: TT: CL: CL: CL: CL: CL: ST: ST: ST: ST: ST: TT: TT: TT: CL: ST: TT: CL: CL: ST: TT: CL: ST: CL: ST: ST: 11:00 AM TT: TT: TT: TT: TT: CI: CL: CL: CL: CL: ST: ST: ST: ST: ST: Note: TT-tutor; CL-Class; ST - student Figure 2 Term Report Fall Term 2012 Students 3 Total Usage Unduplicated Usage 2345 1492 Difference 916 Unduplicated Demographics Male Female Total 816 676 1492 Ethnicity White 808 AfAm 351 172 81 Asian Pacls NAmer Other 15 65 Total 1492 Subject Area 139 121 ACCT IS ENG Math CSCI 347 139 139 Fin 607 Total 1492 Figure 3 Tutor Session Report Tutor Session Report Form Tutor Name Session Date Session Time Student ID 4 Student Name (NA if no student sign up) Material covered (NA if no show) Terry would like to enforce the following business rules by the database system: (1) A student must be registered in the database for the course (or subject) he or she is planning to receive tutoring before he or she can sign up for a tutoring session. (2) No tutoring session is allowed if a student has accumulated 5 no shows (absences) (3) A tutor should not work more than 15 hours a week ***Conduct the database conceptual design*** 5