Project Description & Software Specification
30-Jan-2003
v1.1.9
SourceForge Project Page

Introduction

This is a software specification for the development of a modular, district-level Student Information System. This plan covers development of a core database and web-based interfaces for student/family data, and staff data. In addition, this specification includes the first module, a homeroom-based attendance.  This version of the plan assumes that work begins with a partially completed project (60%).

General Information

• Project Name: theSIS
• Database: PostgreSQL
• DB Editor: phpPgAdmin
• Scripting Language: PHP4
• Web Server: Apache
• Host OS: Linux
• License: GPL
• Project Page: http://sourceforge.net/projects/k12thesis/
• Development Server: 207.245.73.21 (ssh & http)

This project shall implement a central database for all existing student records and provide secure, web-based means to manipulate and view those records. The project will be designed for use at a school district level, such that multiple schools, student transfers among schools/buildings/homerooms, and school-based reports are possible and easy to implement. The web-based user interface shall be simple, uncluttered, and functional.

Collegium Charter School shall retain all intellectual property rights for software developed during this project and shall license the software to the public under the GNU General Public License.

Design Goals

theSIS shall be designed and coded under the following guidelines:

• Based on Open Source tools and intended to be an Open Source project under the GPL, with releases appearing on SourceForge.net.
• Browser independant, browser agnostic; pure HTML-4 (or XHTML-1 as appropriate) as designated by the W3C.
• Tested under IE6, IE5.5, Mozilla 1.2, Netscape 6, Netscape 7, Opera 6
• All server-side functionality; no Java or Javascript
• No dependance on proprietary tools
• Modular architecture has a core database of student information, with modules which can be enabled or disabled. Initial version will contain a homeroom-based attendance module.
• Database schema implemented to maintain student<->adult<->address relationships even in complex family situations
• Multi-level security based on PostgreSQL groups and protected database fields.
• Use under 800x600 resolution possible

Security

The project shall be designed with four levels of security utilizing PostgreSQL users and groups:

• Viewer: Read-Only access to non-privileged information, including students' free/reduced lunch status, Special Education status, and physical address if different from mailing address, as well as all employee data except for name and homeroom.
• Teacher: Viewer privileges plus read access to data for students, as well as teacher-specific functions in modules such as attendance.
• Administrator: Teacher privileges plus full write access to all records
• root: Administrative privileges plus access to system configuration (superuser)

Access to forms, menu items, buttons, and other screen items will be rendered as appropriate for the security level of each logged in user. Apache should probably be configured to only allow https communications for all theSIS users.

Architecture

The project shall be constructed in a modular manner, with the student information located in the core component. A single module, attendance, is included in the project. The project will be designed to allow easy addition of other modules in the future, including student health records, grade/report cards, etc. System configuration will include a mechanism to enable or disable individual modules.  

The database schema shall be described and documented to allow other developers to easily add features and modules to the project. All scripts shall be liberally commented to allow other developers to understand and modify code.

User interface text will be located in separate language tables (or files, if necessary), to facilitate simple multi-language implementations and modifications without making code changes. Thus, UI text is entirely separate and distinct from PHP, and text items for screen display are fetched as needed rather than coded in-line.

Where possible, code should be written so as not to exclude porting to other databases, particularly MySQL, though design should utilize features available in PostgreSQL such as transactions where appropriate to enhance stability and reliability.

Photographs

Student and staff photographs are an integral and necessary part of theSIS. For security purposes, it is necessary to implement theSIS with photographs stored in the database as secured object fields. Otherwise, they are flat files easily harvested from a web server. The application should be capable of uploading a photograph from the user's local filesystem and incorporating it into the database. It should also be possible to strictly define the size of the photos displayed in the browser.

User Interface

The user interface must be intuitive and make use of links wherever possible (mailto, other views, etc.). The navigation bar must offer instant access to major functional areas. Enumerated types will be drawn from tables defined by the superuser. High-end graphics design is not a requirement for this initial version of the project. Simple text links in the navigation frame are adequate. We are interested mainly in functionality and can replace basic HTML setup with nicer graphics at a later date. While the interface should be intuitive, uncluttered, and businesslike, it does not have to be "pretty." A frames-based approach is desired for the user interface. Three frames will be used:

• Title Frame: A top frame spanning the top of the window, containing a logo and status information. This frame is not scrollable.
• Navigation Frame: A navigation frame will occupy the lower left of the browser. This frame is not scrollable.
• Display Frame: The display space will be the target window for theSIS interactive output. This frame is scrollable.

Core Component

The core component of theSIS shall include the database with all district information, student records, student photos, and staff/homeroom assignments, a mechanism to load comma-separated-value data and photos (or photo file names for web server retrival) into the database, and a web-based user interface to enter additional data and to view/manipulate existing data, freeform query capabilities, and reports. The core component will maintain a unique identifier for students, which will become the student's identification number. This identifier will not be related to any student data.

The core component will allow teachers to view student data by room (their assigned homeroom by default) or by specific student. The homeroom page will offer teachers a table of thumbnail student photos of all students assigned to her homeroom. This table will be static, with photos on the left and basic contact information on the right, with one student per row.  The intent is to offer the teacher a single-page view of all necessary contact information about her students. Displayed information will include address, parents/guardians, email addresses, teacher notes, etc. An edit button will provide the teacher access to the notes field. She will not have write access to any other student data in the core component.

The core component must provide accurate data and immediate access to comply with Pennsylvania Department of Education requirements on reporting, in accordance with these forms:

• PDE-4552CS: Listing of Students in Charter School

The core component shall include an automated cron-based mechanism to dump all configuration, security, user, and student data to an external file for daily backup. The backup data must give root the ability to completely restore the database, user account and group information, and all theSIS functionality after reinstallation of theSIS script files of correct version.

The core component will offer views/screens by student and adult (“manage students”, “manage domiciles”, “manage adults”). Domiciles and adults listed in the student view will be links to their respective views. Students listed in the domicile and adult views will be links to their respective views. That is, the user must be able to easily click their way through the students' adult and domicile relationships without navigating menus or starting at the top of the hierarchy. Adult views must show all students associated with the adult. Domicile views must show all students associated with the domicile.

The project is designed from the school district point of view.   A top-level district is defined. Schools are assigned to the top-level district. Buildings are assigned to schools. Homerooms are assigned to buildings. Students and teachers are both assigned to homerooms. Views showing students or homerooms should also show the appropriate teacher, and vice versa.

All configuration information is stored in the database.

Attendance Module

The Attendance module will be used both by Teachers and by office staff. Upon login to theSIS in the morning, teachers will start the attendance function where they will have a table of student thumbnail photos. Unlike the student information table in the core component, this table will fit in a single data frame. Each photo will have beside it a radio-button set, by default set to P (present). For those students who are absent, the teacher will select A (absent). When all missing students are marked, she will press a submit button, after which the table will be refreshed with a red border around each missing student photo. The teacher will be able to amend this attendance view until a set attendance deadline (say, 8:45AM).

The attendance table will be updated with a new timestamped entry for the student.

Each thumbnail photo in all teacher views shall be a hyperlink to the student's detailed information, attendance record (query to the attendance table) and contact information.

After the attendance deadline, attendance data for the day will be changed by administrator-level personnel only. A new screen, similar to the table presented to teachers during the attendance function, will be available to administrators. This screen will show the thumbnail photos of all students reported absent by teachers, and will include a different radio-button set:

• A - absent
• AE - absent/excused
• T - tardy
• TE - tardy/excused
• ISS - in-school suspension
• OSS - out-of-school suspension

Students arriving late will be marked tardy by administrative staff only, not by teachers. All daily attendance exceptions (tardy & absent, and excused status) will be part of the student's permanent record and will appear on various reports.

After the deadline, all teachers will have access to all homeroom attendance records in a static view much like the attendance screen with red borders around pictures, so they can see which students are not in school and thus can be prepared for missing students all day.

Before and after the deadline, administrators will also have a report showing which classroom teachers have not yet submitted attendance for the morning, to allow administrators to call and remind them to report.

The attendance system must provide accurate data and immediate access to comply with Pennsylvania Department of Education requirements on reporting, in accordance with these forms:

• PDE-4002CS: Charter School Summary Report of Aggregate Days Membership
• PDE-4062CS: Charter School Annual Attendance and Membership Report

PDE forms and their instructions are available here:

• http://www.pde.state.pa.us/child_acct/cwp/view.asp?a=3&Q=73006&child_acctNav=|3329|&child_acctNav=|3322|3329|

Tables

• general_information - Core; District and configuration information, administrator email, etc.
• school - Core; general info, address, etc. on schools
• building - Core; info on buildings, link to school
• homeroom - Core; info on homeroom, link to building, link to building and teacher in user_details
• user_details - Core; detail information on employees (teachers, etc.; should be renamed to "users")
• student - Core; main student information table; does not include parents or addresses
• adult - Core; main parent/guardian information table; does not include students or addresses
• adult_type - Core; enumeration/lookup table for adult types (mother, father, stepmother, etc.).
• guardianship - Core; linking table: multiple students to multiple adults
• address - Core; physical addresses
• address_types - Core; enumeration/lookup table for various types of addresses
• adult_address - Core; linking table: multiple adults to multiple physical addresses
• attendance - Attendance Module; absence records (this table should be renamed to "absences")
• rooms_reporting - Attendance Module; daily list of rooms reporting attendance to be used in query for non-reporting rooms
• valid_state - enumeration/lookup table for US states
• ethnicity - enumeration/lookup table for student ethnicity
• text_en - English text for user display; a better method of introducing languages is requested.
  

Enumeration tables will be used wherever possible to make data consistent across the DB, allowing meaningful queries (for US states or ethnicity, for example).  See schema dump for progress to date. Enumeration tables will be edited solely by the superuser.

Schema/Concepts

As stated earlier, all variable data in theSIS, including configuration, photographs, and data are to be stored in the database. Only PHP and HTML are to be stored in flat files on the server. The core component features these ideas:

• The general_information table contains top-level information about the school district and is stand-alone.
• All schools defined in the school table are assumed to be part of the district. Reports by school will utilize district information where appropriate, such as the name of the district, but will use school information such as address and contact information.
• Schools are many-many with buildings. This allows multiple schools in a building, or multiple buildings to comprise a school. This may require a linking table.
• Buildings are 1-many with homerooms. Homerooms are assigned to the building where they are physically located.
• Homerooms are 1-many with students, who are assigned to the homerooms.
• Homerooms are 1-many with users, who are also assigned to the homerooms.
• Guardianship records are 1-many with students
• Guardianship records are 1-many with adults - this linking table eliminates redundancy for students to be many-many with adults
• Addresses are 1-many with adults
• Types of addresses are enumerated in the address_types table; for example, home, work, PO-Box, etc.
• US States are enumerated in the valid_state table
• Language is extracted from the appropriate text table.

The homeroom attendance module includes these additional items:

• Each student absence is logged by the teacher pressing her attendance "submit" button in the attendance (future change to "absences") table. Student ID, date, and time are logged. Students are assumed present unless logged. Queries on student absences will search this table for all occurences of the Student ID.
• Each "submit" from a teacher will be logged in the rooms_reporting table, which will then be used by a query to determine which rooms haven't submitted attendance. The complete list of rooms will come from the homeroom table.

User Screens & Information

All HTML output will be called "screens" for purposes of this document. Each bullet item below lists a screen name and description. Screens are genreated using the navigation frame widgets.

• Login: The first screen received when accessing the Apache server html root will be the theSIS login page, consisting of the logo (to be updated), a username, and a password.
• Logout: This screen is a courtesy screen indicating that a user's session is complete. It should contain a link to log in again. It should also contain another link, defined in the general_information table, of an "exit link" to take the user back to the company home page or intranet page.

Once users are logged in, they will be presented with the frames-based user screen, populated according to the user's access priviledges as defined by the security level assigned to the user.

Viewers (all users)

This is the lowest security level.

• Account: Change employee password, see their personal data (“my data”, read only), and a form to request a data change from the administrator
• Students: Homeroom or query-based student lookup. Prohibited are all private data except pictures, non-privileged student information (read only)
  • VStu1: View students by homeroom
  • VStu2: View students by grade
  • VStu3: View students by building
  • VStu4: View students by school
  • VStu5: Attendance information (read only)
• Absentees: List of absent students

Teacher Screens

All "viewers" screens, plus:

• RptAtt: Report daily Attendance (read/write, includes writable“notes to office” field for each day's attendance submission)
• Tstu1: Replaces VStu1 and can write student_teacher_notes field, r/w, and also defaults to Teacher's assigned homeroom. Dropdown homeroom selector still available to look at other homerooms.

Administrator Screens/Capabilities

All "Teacher" plus:

• Manage students (these functions may be combined as appropriate for the user interface)
  • AdmStuNew: New student
  • AdmStuUpd: Update student
  • AdmStuDel: Delete student
• Manage Adults (these functions may be combined as appropriate for the user interface)
  • AdmAduNew: New adult
  • AdmAduUpd: Update adult
  • AdmAduDel: Delete adult
• Manage Domiciles (these functions may be combined as appropriate for the user interface)
  • AdmDomNew: New domicile
  • AdmDomUpd: Update domicile
  • AdmDomUpd: Delete domicile
• Manage Employees (these functions may be combined as appropriate for the user interface)
  • AdmEmpNew: New adult
  • AdmEmpUpd: Update adult
  • AdmEmpDel: Delete adult
• AdmStuAbs: View students by Absent today (read/write, used to check in late students and to apply excused status to absent/tardy)
• AdmStuDbus: View students by departure bus
• AdmStuAbus: View students by arrival bus
• AdmAttEd: View/edit individual student attendance history
• AdmClrmNrpt: View classrooms by not reporting attendance (if attendance module enabled)
• AdmViewNot: View daily notes from classrooms (lists notes to office from teachers during attendance, if attendance module enabled)

Root Screens/Capabilities

Some of these may be combined into overall configuration screens, as appropriate. All "Administrator" plus:

• Student Data management
  • RootImport: CSV data import from file
  • RootExport: CSV data export to file
• Root setup
  • RootPwd: Set root's PostgreSQL password
  • RootEmail: Set root's email address
  • RootStuDatEmail: Set student data manager's email (used for student data change requests)
  • RootEmpDatEmail: Set employee data manager's email (used for employee data change requests)
• Root Information
  • RootLang: Specify default language
  • RootLogo: Specify logo graphic location
  • RootID: Specify default student ID (i.e., 10001, 200001, etc.)
  • RootAttDeadl: Specify attendance deadline time (if attendance module enabled)
  • RootDistName: District name
  • RootDistAddr:District administrative address, city, state/province, zip, country
  • RootDistInfo: District phone, fax, emergency number, URL (should be a link), email (should yield mailto: under mail icon or text in appropriate areas)
  • RootSYInfo: School year info (year, static calendar as text)
  • RootDfSt: Specify state/province/locality (i.e., Pennsylvania; eventually used to define report formats for attendance)
  • RootDefAC: default area code (used everywhere a phone number is entered)
• Root Administration
  • RootBackup: Manual database backup
  • RootRestore: Manual database restore
  • RootModules: Manage modules
    • RootEnHRAtt: Enable attendance
  Manage user accounts
  • RootUserNew: New user
  • RootUserMod: Modify user
  • RootUserDel: Delete user
• Manage Schools
  • RootSchNew: New school
  • RootSchUpd: Update school
  • RootSchDel: Delete school
• Manage buildings (navigate by school)
  • RootBldgNew: New building
  • RootBldgUpd: Update building
  • RootBldgDel: Delete building
• Manage homerooms (navigate by school->building)
  • RootHRNew: New homeroom
  • RootHRUpd: Update homeroom
  • RootHRDel: Delete homeroom

Reports & Queries

Reports are to be available at the administrator and root security levels. An initial group of reports are defined here. Reports will be rendered in PDF format for viewing in the browser, and printing if desired. The following definitions are assumed:

• Report: A report is generally thought of as a formal document, suitable for printing, containing a standardized, pre-built format and data selection, and that is used relatively infrequently. They're part of the system. Its data will of course change to reflect current data in the database when run. Examples might include "students by homeroom" or "students by grade level."
• Standard Query: Like a formal report, a standard query is pre-built and is part of the system. Unlike a report, it is not presented in a fancy format and is generally thought to be used more frequently, and rarely saved for reference. An example might be "students absent/late today" or "homerooms not reporting attendance today."
• Ad-Hoc Query: Often, users will want to make their own queries of the database. A user may for example want theSIS to "show me all students who ride bus 112 and are in homeroom 200." This is an ad-hoc query, and may not be repeated often or used in everyday situations. An ad-hoc query is generated using a query form.
• Query Form: An ad-hoc query is generated using a Query Form. This is a web page containing free-form text fields, radio buttons, dropdown lists, etc., that when filled out and submitted generates an ad-hoc query. This form needs to be filled out each time a query is requested. Often, ad-hoc queries are needed often, at which time they can be implemented as standard queries by the technology department.

Standard Reports

• Students by Grade Level
• Student Attendance (single student)
• Student Attendance Summary
• PDE-4002CS: Charter School Summary Report of Aggregate Days Membership
• PDE-4062CS: Charter School Annual Attendance and Membership Report
• PDE-4552CS: Listing of Students in Charter School

Standard Queries

• Homerooms not reporting attendance today
• Students absent/late today

Ad-Hoc Query Form

A Query Form will be provided to users with teacher-level access and above to create their own ad-hoc queries. This form will offer drop-down lists for enumerated types and open fields to match database fields. The result of the Submit button in the form will be a SQL statement and the results viewed in the Display Frame.