Kopernik

Introduction to Kopernik Observatory

Kopernik Observatory & Science Center is a non-profit organization which is opened in 1974. Located atop a 1720 foot hill in Vestal, NY, the Observatory has been one of the best equipped public observatories in the Northeast United States for nearly 40 years. It provides educational program for students and their families.

Information about Previous Project

Relational Database

In the previous project, previous team created a new database and combined four databases into one and implemented ten tables inside of the database. As it is mentioned in their final report, it is relation-based SQL file and it is implemented in MySQL environment. The previous project team only worked on the Kopernik Talent Search database which has 4894 rows (including some duplications). We contacted with a team member of the previous project Nagihan Cay and learned that they used Oracle MySQL Workbench Tool to extract the data from excel tables to import them into MySQL. Since the table names and its attributes might be changed in the future, the document does not consists of their information.

Entity-Relationship Model

Web Application

By reading the previous project’s user manual, we found out that they designed some new General User Interface (GUI) and also they made some improvements on the web pages which are created by the project team before them. This improvements involve more input fields and student search boxes. They created several user-friendly web pages and explained these web pages purposes in their final report. A screenshot of an example for a web page (Figure 2[2]) can be found in the appendices part.

Information about Current Project

Objective

Kopernik Observatory gives tens of educational programs to hundreds of students. The information about the students, their families, class, and memberships requires a big database which cannot be handled manually. Their old database is implemented in FileMaker Pro7. Although it is an effective technology for small companies, Kopernik has lots of data to store. The high amount of data makes the FileMaker Pro7 very inefficient. Not the database structure itself, also the user interface of the FileMaker is very complicated. It has lots of textboxes and unordered lists which makes users to confuse about what they are doing, especially the unexperienced users.

Kopernik Science & Observatory Center Database and Web Application Project is created by previous project team to improve the Kopernik’s current database system and its interface. To achieve this, the previous team started implementing a new relational database structure and a web application. Our project has three stages:

1. Database Stage – In this stage, we will start examining the database structure which is created by the previous project team, then we will continue from where they left off. Our main priority will be the reconstruct of the current database system.
2. Web Application Page Stage – In this stage, we will check the previous project team’s web pages and decided the adequacy of the current web pages. If there will be a need for more pages, we will create them.
3. Miscellaneous Stage – In the miscellaneous stage we will try to fulfill the extra specifications. This stage can rather be done or left out according to our schedule. If we can finish the first two stages, we will focus on the more “unimportant” functions or modules such as “Spell checking”, “go back buttons” etc.

Project Scope

The project specifications are defined by our customer Kopernik Science & Observatory Center’s Executive Director, Drew Deskur and our employer Professor Richard Steflik. This specifications will be used as a definition of this project. The main requests of the customer are: Integration of all databases

Combination of Databases

The current system has three different databases; Student, membership and scheduling. When users want to change something they also need to change the other two database entries manually. For example: If a user wants to add two siblings and their parent’s information to database, he/she must have to add parent information for both children. This method not only wastes time but also it is very open to false data entry. Also by adding parent information two times for both children, user makes duplication of data without his/her purpose. Also, due to duplications, when a user wants to change an information on the database, he/she must update the database for twice. In a case where user changes one of the values and forgets to change the other one, data corruption would begin in the database. This would make a critical damage to the organization if this happens to the tables where the premium members’ information hold.

Creation of the User interface

User interface of the FileMaker Pro7 is very simple and elegant for small companies with small databases but Kopernik has huge amount of data. This created a very complex user interface which involves lots of database name labels (user simply clicks these labels to view the data held inside). These labels are listed in a very scattered way with lots of different colors. Also the forms -which are used to add new data or modify the current ones- look very complex with absurd textboxes. Previous project team created a new, simple user interface with web pages. If more needed, we will continue to add new web pages.

---

In the miscellaneous stage, customer has some extra requests. These requests are:

• A function that makes a statistical analysis and creates a report about the classes, students, members etc.
• Printing a report of payment history for a specified class.
• Rename of the database table names which are showed in the user interface. For example: Instead of “SSC11” program should show “Summer Space Camp 2011”
• Printing all classes that one specific student takes.
• Printing a report which contains specific student information.
• Customer wants to see the last modification date and time of entries in order to see the members’ membership renewal time.
• Auto-fill function on text boxes.
• Field Check for preventing false or redundant entries. For example: State field should only take two characters.
• Customer wants a warning box which warns users if a field is left blank on a new entry.
• Customer wants confirmation box which asks a question such as “Are you sure” while saving a new entry or editing an old one.

As mentioned before, the miscellaneous stage consists the optional requests from the customer. These specifications which are mentioned above under the miscellaneous stage, do not have any critical role in the program.

Progress

Week 1 & 2

Installation of previous project completed with no errors. Tested every web page simultaneously with the database, in order to check every single page is working correctly. Found couple mistakes and missing parts in previous project. For example: As we understand. previous project team changed their original code to test their functions or forgot to increment some counters which caused a database corruption. In this case, if admin wanted to create a new student with two parents, it is created with 7 parents(kinda funny).

Started to add form validations on some of the pages, such as text requirements, email confirmation, etc.

Week 3

Started to work on pages that are not completely done. For example previous project cannot add new students correctly. There was some complications about students parent and family information. Changed that and now add new student page working correctly.

Our client wants to add new students to existing families. There was nothing about that in the previous project. Added necessary pages in the add new student page and modified database according to that.

Week 4

Continue working on adding new student to existing families page. Started doing an edit page in order to edit family information.

Week 5

Added a search feature which works with "Family Last Name". Completed the page which enables user(s) to add a new student to an existing family. User(s) can now see the family information without making any changes.

Form validations are implemented and tested for "Add New Student" page. With the validation function, user is warned if he/she enters a non-valid entry(ex: using a letter in a zip code input area.) On a successful attempt, the page returns a warning which states "Record is added!"

Had a meeting with Drew Deskur to discuss about our progress and new features.

Week 6

Started working on the "Group Programs" pages. Implementation of the "Membership" pages are finished.

Week 7

Redesigned "Individual Program" pages according to our customer's needs. Received "Group Programs" data from Kopernik.

Week 8

Had a meeting with Drew Deskur to discuss about our progess and new features. Started to transfer tables from FileMaker Pro to MySQL.

Week 9

Had a meeting with Drew Deskur to discuss specifically about "Individual Programs". Added make a payment page to "Individual Program" page.

Week 10

Had a meeting with Drew Deskur to discuss specifically about "Group Programs". Changed "Membership" page, added "See Membership Detail" and "Add Membership" links to family search. If the family doesn't have ant type of membership, "See Membership Detail" link won't shown in the results page.

Week 11

Added make a payment page to "Group Program" page. Separated " Add Group Program" page from "Add Group". Had a meeting with Drew Deskur to discuss specifically about "Group Programs".

Database

Tables

User database, which is called 'kopernikdb', has 14 tables.

• "users" table contains user information, which have access to the webpage. It holds username, password, created at and updated at information
• "student" table will hold basic student information that Kopernik Observatory & Science Center requires, which are first name, last name, gender, medical concern, dietary concern, special concern, notes, emergency contact, shirt size, birthdate, graduate year, balance and family id.
• "parents" table holds information about student's parent. The most important information is that it contains the local address of students. Table holds first name, last name, gender, day phone, evening phone, state, street, city, zip, single parent, email, custody, contact person and notes.
• "family" table just holds family ids.
• ""