PROJECT PLAN - Clarkson University

bossprettyingΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 5 χρόνια και 5 μήνες)

311 εμφανίσεις


Revision 2

Team Members

John Martins

Sahil Kumar

Bin Zan


The project consists of an improvement to the Clarkson University’s on
line directory.
This project wi
ll involve three main sections: students’ directory, faculty directory and
department directory. The objective is to facilitate access to the available information, using an
intuitive and user
friendly environment offering flexibility and power at the same


With this improved directory, students could search for all senior computer science
students for example, receiving a list of matches. They could list all faculty and staff with offices
in the Science Center. Or they could simply type “maintenance”
to have their bed frame fixed.



The major target audience for this project will be all of Clarkson’s students, staff and
faculty. Everyone connected to the school network, on any lab or in their own rooms will be able
to access this improve
d on
line directory to search for other students, faculty and departments

This is a major project of great interest to everyone in the University. Everyone will gain
from it.


The required software and hardware to run this projec
t will be the following:

Required Software

This project makes use of a database server, which should be postgreSQL. The University
already has servers running postgreSQL to serve the current database system for the on
directory. These same servers will be used once the pro
ject is put to use, but during development
phase and for testing, we’ll be using our own postgreSQL server installed on a Linux machine,
running Mandrake Linux. The project needs an Apache server as a webserver and PHP integrated
with the webserver to be a
ble to provide PHP functionality. We decided we do not need to install
Apache and PHP on our machines, since we can simply use the ones provided by Clarkson, by
storing our PHP files on our Clarkson accounts and using Clarkson’s web server to access them
uring development phase. That way, once we view our php files, they’ll be running on the
University’s web server and using the university’s PHP module, but accessing our database
running on our Linux machine.



Any PC running Linux can

act as a postgreSQL database server.
And to access the database, any user on any hardware can access the directory page on the web
and have access to the desired information.



After having started the work using mySQL, we had to swit
ch the development process to
work on a postgreSQL database to match Clarkson’s current on
line directory database. At first
we did not anticipate many problems, since both databases use similar derivations of the SQL
language. We concluded that the major
differences would be on the PHP part, since we would
have to change the required querying functions to work with a postgreSQL database. But the
major problems proved to be on the very installation of the postgreSQL database.

We needed a test machine workin
g in order to create and fine
tune a working prototype of
our database querying system. Therefore we had to install our database on this test machine and
allow remote access to it in order to use its database. This installation and configuration has

to be one of most time
consuming problems we have been faced with. After trying for a
long time to install postgreSQL on a Windows machine, which didn’t work because postgreSQL
has no versions that work under Windows, necessitating a Unix emulation layer,

such as cygwin,
we had to learn how to install and configure postgreSQL on a Linux machine and how to enable
remote access (through ssh) to that machine and then to the database so we could all work on the
same machine from different points (our own room
computers or any lab computer for example).


The project will follow a certain priority in terms of what will be implemented first.

We are initially concerned with developing the search mechanism. This will consist of
actual postgreSQL database installed in our Linux test machine and the php files containing the
appropriate forms that will process the user’s input and display the appropriate results. The
database will initially contain a simple set of data, just for

developing/testing purposes. Its format
will be as follows:

Database Format

Three tables, one for the students information, another for the departments information and a
third for the faculty information. They are detailed below.

Students table

l consist of the following columns:

ID (Primary Key)

First Name

Last Name




Phone Number

Box Number

Additional Info (*)

Password (*)

(*) There is an idea to include this field in the students’ table. This would allow any user to enter
any information about themselves that they wish to disclose, such as their AIM screen name, cell
phone number etc. The only complication here would be creating some sort of authentication
before users could enter such information. They would have to enter
a password that would have
to be an exact match of the password column.

Department table

Will consist of the following columns

ID (Primary Key)

Department Name

Phone & Fax Numbers

Current Keywords associated with that department

New Keywords suggested b
y the users

Faculty table

Will consist of the following columns

ID (Primary Key)

First Name

Last Name



Room Number

Building Name

Box Number

Phone Number


PHP file

Student Search

Once the database is setup our next concern will be with the php file for the

area. This file will allow the users to search for students in the following manner:

Display the forms that will accept the user’s s
earch criteria. (More details on searchable
fields will be specified later).

Process the user’s search criteria and query the students table on the database based on those
criteria, displaying a list of users that match those criteria.

Allow the user to pi
ck one student out of those on the results list.

Display the complete information about that specific student.
There are absolutely no legal
problems with this procedure as confirmed by Mike Griffin and Registrar Lynn Brown

Give option for student to chan
ge his additional
information column, which would prompt
him for his password before he can change it. [OPTIONAL]

Give option for student to change his password, which would prompt him for his current
password before he can set a new one. [OPTIONAL]



Department Search

Next is the php file for the

search area. This will allow the users to search for
departments in the following manner:

Display the forms that will accept the user’s search criteria. (More details on searchable
will be specified later).

Process the user’s search criteria and query the departments table on the database based on
those criteria, displaying a list of departments that match those criteria and their phone and
fax numbers.

Display the keywords that are
currently associated with that department and ask the user if
he would like to suggest a new keyword to associate with that department.

In case the user makes a suggestion, it will be stored in the suggested
column of
the Departments table. Later,

administrators could move whatever keywords they think are
reasonable from the suggested
keywords column to the actual
keywords column. (There is a
possibility of creating an interface to automate this process as mentioned below).

PHP file

Faculty Search

Next is the php file for the

search area. This will allow the users to search for faculty
in the following manner:

Display the forms that will accept the user’s search criteria. (More details on searchable
fields will
be specified later).

Process the user’s search criteria and query the faculty table on the database based on those
criteria, displaying a list of faculty members that match those criteria and all the information
about them.

The search criteria can be any
combination of the searchable fields.

Searchable Fields


First Name

Last Name



Phone Number

Searchable Fields


Current Keywords field

Searchable Fields


First Name

Last Name


Room Number

Building Name

Phone Number

What is a

All strings that have the search criteria as sub
strings are considered matches. Example:
Searching for “Powe” should find “Powers” and “Powell”. Only database items that match all
search criteria will be returned. Searches will be case insensitive
, lower and capital cases will be
treated the same when conducting searches.

After the search mechanism is completed, or maybe even at the same time it is being
completed, we will be working on the user interface, which will be these same PHP files but
bserving the standard Clarkson “look” for websites.

An optional extra will be a web
interface for the administrators, allowing them to change
the database easily and without having to type raw SQL commands at the server. This would also
include the featu
re to move keywords suggested by users to the actual
keywords field of the
departments’ table in one step.


This project will present many risks and complications. For example, the installation and
configuration of postgreSQL itself

has been extremely laborious.

Including the administrator’s web
interface mentioned above will also introduce the risk
of making the project very time

Another problem is the creation of the user
interface; this has to follow the same look
blished by Clarkson’s current web pages so as not to create a page completely distinct from
the rest. Any possible new visuals added to this page will not detract too much from the
established standard.

There were some questions regarding the legality of h
aving a certain sub
group of
students being displayed under certain search criteria, such as all computer science majors. This
is completely legal and feasible, as confirmed by Mike Griffin and Registrar Lynn Brown.

Populating our database. We’ll have to
use an automated way of doing this. A plan to deal
with this particular risk is to create a specialized PHP script that will extract the information from
the current database and store it in ours. The exact procedure used to carry out this populating
ss will be discussed with Julie Davis and Mike Griffin.

Another complication mentioned earlier is the inclusion of an “additional information”
column to the students’ table of the database. This would require the implementation of some sort
of authenticat
ion before users could enter information, to make sure that they would be entering
information only about themselves.

Yet another complication is how to accept phone number entries on the forms. Users
might use hyphens ‘
‘ between numbers when making thei
r entries or put spaces or simply
nothing at all. Suppose we store phone numbers as a string of numbers on the database, without
any hyphens or spaces, and the user decides to use a hyphen when making his entry into the
search box; the system would never f
ind the phone number he entered, simply because that would
never be a substring of any actual phone number stored in our database (due to his use of
hyphen). Our plan to account for this possibility, is to use two text area boxes; one for the first
three n
umbers and another for the last four, limiting the text length to the appropriate sizes (three
and four respectively). That way, the user will be forced to enter the phone number as only
numbers and will make the actual database query simpler, without the
need for any client
parsing to take place.


Installation and Configuration of postgreSQL

First basic PHP scripts to do basic database queries

More elaborate PHP scripts

Populating students table of database for a


Complete students search PHP scripts

Populating departments table of database for a prototype

Complete departments search PHP scripts

Populating faculty table of database for a prototype

Complete faculty search PHP scripts

Beautify user interfac

Create Administrator’s web
interface (if time allows)

There will be very little interdependencies among group members. The only obvious one is
having the database server installed and configured before any work can be done. Even the PHP
scripting and th
e user
interfaces can be worked on separately if necessary, thereby creating very
little interdependencies among different project parts.


Most of the work is being split as conditions dictate. But in general the work will be split
in the f
ollowing manner:

John Martins

All documentation (such as Project Plan, Project Design etc)

postgreSQL installation and configuration

User Interface

Sahil Kumar

PHP querying

Bin Zan

postgreSQL installation and configuration