Project Description & Software Specification
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%).
- 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: 220.127.116.11 (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.
theSIS shall be designed and coded under the following
- 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
- 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
- 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
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
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.
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.
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.
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
- 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.
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:
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
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.
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
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
- 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
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
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
- 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
- address - Core; physical addresses
- address_types - Core; enumeration/lookup table for various
types of addresses
- adult_address - Core; linking table: multiple adults to multiple
- 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.
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
- 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
- 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
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
- 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
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.
All "Teacher" plus:
- Manage students (these functions may be combined as appropriate for the
- AdmStuNew: New student
- AdmStuUpd: Update student
- AdmStuDel: Delete student
- Manage Adults (these functions may be combined as appropriate for the user
- AdmAduNew: New adult
- AdmAduUpd: Update adult
- AdmAduDel: Delete adult
- Manage Domiciles (these functions may be combined as appropriate for the
- AdmDomNew: New domicile
- AdmDomUpd: Update domicile
- AdmDomUpd: Delete domicile
- Manage Employees (these functions may be combined as appropriate for the
- 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)
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,
- 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
- 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
Manage user accounts
- RootBackup: Manual database backup
- RootRestore: Manual database restore
- RootModules: Manage modules
- RootEnHRAtt: Enable attendance
- 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
- 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.
- 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
- 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.
Director of Technology
Collegium Charter School
West Chester, PA, USA, 19380