Introduction to Kopernik Observatory

Introduction to Kopernik Observatory

Introduction to Kopernik Observatory

Kopernik is a nonprofit organization located in Vestal, that studies astronomy and gives educational, long term classes to pre-school &elementary students; and short term events for their students and older people.

For further information, please check Kopernik’s web site

Kopernik as a nonprofit organization

Depending on the way a company is set up, their taxation and other things may vary. For instance TechSoup is a website that offers cheaper software for non-profit companies. During the project, if you feel like you may need a product, after checking and getting approval from your bosses (which may be your senior project teacher or the person that you report at the Kopernik) you may purchase software for a cheaper deal from such places.

Moreover a nonprofit organization may supply OPT for a year, if you are working as a volunteer at the non-profit organization. Thus, as foreign seniors, you may think about keep on working there while looking for a job. For further more information, please contact Dual Diploma Program Office and your bosses for permissions.

Contact Info – Suggestions

For the address of the Kopernik Observatory, you can use their web page and give them a visit to introduce you, get contact info, etc. However, when you go there, people you should meet (Deskur Drew for us) would not be here, or there might be nobody at all –happened to us -, thus I recommend sending an e-mail first. Than you can arrange a meeting and change contact info.

Moreover, I would highly recommend have a general plan before ahead and have two types of meetings, one for giving feedback (to talk about: “we did this, this happened, we handled it this way/how do you want us to handle it, there is this problem, we didn’t understand this” kind of things) and a second type of meeting where you would talk about what to do next. This way, when you are done with what you are doing, you wouldn’t be in need of waiting to hear from Kopernik to decide what to do. Otherwise, when they are busy, you might be waiting for a long time doing nothing, so always know what you would do next and when you are at the final step, ask for more.

Secondly, I would recommend having as much questions as you could have before going to a meeting. This way, you wouldn’t go there only for one question. For example when they give you ten things to do, read them all, not only first step, and ask questions about everything. This saves tons of time, in terms of time for meeting and time for re-doing.)

Finally, in worst case, when you are in deep need of meeting them but cannot get a word from them, just showing up over there works fine for shorter meetings.  

General Information about the Project

This project has two main parts, re-modeling the database and giving an interface to that database.

Kopernik’s Need for a Database

As a company which gives classes to children and pre-teens, and events to teens and older people, they would have a crowded data. They have contact lists which would have a list of people, which could give classes or donations. Or events that are requested by one person but tens of students may attend where student names are unknown. Moreover, there are people with memberships, members who do donations, members who gifted donations etc. So, they had a pretty big database which cannot be handled manually.

In order to be able to handle such a big junk of database, they decided to switch to digital database system, and hired a professional for this.

Kopernik’s Old Database System – File Maker

The professional that Kopernik hired, set up a database program called File Maker version 7. Which probably was a good database solution at that time (at early 2000s), alas with today it is a messy big junk of data.

The way it works is pretty simple. An end user can create database values and create an interface by dragging, moving, and coloring items. However, this easy to use interface causes database to be very messy and inefficient. Although, it is unclear that if File Maker 7 enables users to have relations or different tables, we know that Kopernik had four different databases with only one table in each database.

This design, worked nice on smaller databases. However, the database that was used for holding classes and students almost had 2000 variables in one database. Which may have made it easier to list and search, alas, it also made it very un-normalized, and open for anomalies. For instance, two siblings would have same parents, and their parent info would be duplicated. For same parent, there might have been two copies of the same person with different names (Bob –Bobby-Bab-Robert may be the same name) which would cause search – update anomalies. Moreover due to duplications, when a user’s info needs to be changed, one of the values would be changed while other one stays the same, which causes the first problem. For further explanation on anomalies please check anomalies page of Wikipedia.

Moreover, although one table had 2000 variables, there was only 3600 data entry with over a thousand student info. This means, there were tons of empty – unused space in the database.

These anomalies and unused extra space was making database harder to run, query, list, and read. Moreover, due to anomalies, there were errors in the database and queries, thus a new solution was required.

Kopernik’s New Database - Outline

Due to all anomalies and wasteful usage of the database, it was necessary to have a new design. Which is decided to be a relation based SQL database, since it is very common, free, there is good documentation available and most students are already have common knowledge about it.

Kopernik requested to have one database rather than four different databases and it was mandatory to have it at least in third normal form, where anomalies and duplicates would not exist.

Due to their request of having one database, there are un-related tables in the relations based database, which looks abnormal, but, is normal.

During the normalization, their number of variables reduced dramatically (the database with 2000 variables, now has less than 100 variables). Information on new database will be given on later. For editing the database using data definition language tools it is always safer to have a copy on a PhpMyAdmin or some other database tool. MySQL Workbench is a very useful, free program which not only gives you an interface for editing the database –for users which does not feel comfortable with writing scripts-, this tool creates and edits databases, or gives writes out scripts to be run in SQL environment. MySQL Workbench also gives entity relationship diagrams automatically.

Carrying Data from Old System to New System

Due to lack of time, we were unable to find out how to get values from FileMaker Pro in way that could be used in MySQL. However, one student may work on this on his own and carry some part of the database to MySQL. How to do is pretty simple and straight forward but requires good amount of patience.

First, trial version of newest File Maker Pro shall be installed and the old database should be requested from Kopernik Observatory. Than using the control panel, user can export the data in a format which could be used in MySQL. When user imports data to MySQL, it would be really hard to read. However, most of the information is old and useless. If the user can find which values are for what, and only retrieve that from the system, even that could be useful.

So what to do is, a student shall meet with Kopernik and ask which information would they like to ‘save’ from the old database. I believe class information and etc. is un-retrievable however, student name, parent name and some other info can be retrieved. If requested items could be found, they could be carried from a dummy MySQL database to actual database that is being used by Kopernik.

Kopernik Observatory Database Project Workflow

As the starter team to the project, we have talked and decided on a way of implementing the project. It is a very simple and common way of working with a customer. After completely understanding the general outlines of the project and getting comfortable with the tools that you will be using (database querying and PHP coding), as team, a visit to Kopernik should be done, to request a list of 10 most important things that they want you to do (this list would have items like “add student”, “add membership”, “edit parent” etc.). Then, team would have a meeting to understand the requested items and how they would implement it.

After implementing the items on the list (or one of them, or some of them, depending on the size of the item/items), Kopernik should be notified and they should be given a small demo for showing what have been done, how it is used, and how it works (explaining which value is being written to which element in database and how things work). After the demo, Kopernik people shall test what have been done for a while, to determine if it is what they really wanted. After Kopernik does the testing, wrong implementations shall be fixed right away.

While implementing the 9th or 8th element of the list, Kopernik people should be asked for another list of ten new items to be done. This way, there would be a full time cycle of implementing and fixing, with no time lost while waiting input from Kopernik.

Although Drew Deskur (Person who we are, and probably you will be talking to at Kopernik) is an engineer and he has a general understanding of computerized systems, they may request “some magic” to happen. If this occurs, try to explain them why that it is not possible to do what they want –or why it would take a long time- , and explain them how it could be done.

Kopernik Observatory’s New Relational Database

Kopernik Observatory’s database would be mainly used for students and their classes and everything related to them; with some data for events, classes, programs (or Friday night events), contacts and group activities. Although it looks simple now, it gets quite complicated.

The main problem is family relationship. A student may have four parents -two by blood and two parents in law-, which means eight grandparents, and several siblings. Right now, a student can only have two parents, which should be change. Some parents may or may not have the custody of the child and there might be same sex couples. These kind of relationships makes the database complex.

You can see the relation in the database below, which will be explained after that.

As seen on the diagram, there are actually four databases. A Student oriented one, Activity oriented one, Program, and Admin.

Admin database –or table- is basically being used for logging in to the website. If the username entered matches the password, user gets access to database.

Program database –or table- is for events which are called Friday Night Events -or program-. These events are mostly not astronomy oriented, and sometimes a special speaker may speak there.

Activity database is consisting of three tables. These tables are Activity, Leader, and Group tables. Activity is an event, which used to be held in a database called EventScheduling in the old database system of Kopernik Observatory. An activity, could be about anything –which includes a birthday with astronomy theme, or an unrelated dinner- created by a Leader and most likely to have a group, where group member’s information is not necessary. For example, there could be an event, for Boy Scouts, created by their leader. Kopernik would only like to have basic info about the group and contact info of the leader.

Student database is a merge of two old databases: TalentSearch and ContactManagement with several new add-ons. Basically, a student can have two parents (which shall be changed in a way that lets a student to have up to four parent, and necessary amount of grandparents, or no grandparents) in either gender. These parents may have memberships, which may or may not affect the student, or the wife. And parents may do donations, or join events, whereas students may join to classes. Moreover, Contact’s may be speakers, who may come to give a speech about some topic, or make donations, whereas donations can be done for no reason, or for a campaign.

Further explanation on tables are given below;

Admin Database

Admin Table

This table is pretty straight forward. There are pairs of username and passwords with an ID attached to each one of them

Program Database

Program Table

This table again is pretty straight forward. However, later on speaker should be a foreign key to either Contact table or some other table, thus speaker’s info can be automatically retrieved.

ID is the id for the table.

Program name is the name of the program (exp: Ham Radio training)

Attendance: Number of people attended to the program.

Explanation: Detailed explanation of the program.

Area: Program topic’s area.

Date: Program’s date; that it took place.

Speaker: Name of the speaker at the event (right now it is assumed there is only one speaker, but what if there is more than one speaker?)

Price: If any, price for attending to event.

Discounted Price: Discounted price for the members.

Activity Database

In this database, there are three tables: Activity, Leader and, Group. A leader can have more than one activities and one group can join more than one activity with different or same leader. However, an activity can have one leader and one group.

Activity Table

Activity table holds the general information about the activity and information about who created the activity for whom.

-ID: ID of the activity.

-Leader ID : Foreign key to Leader table’s ID. Basically, this value holds a Leader.

-Group ID: Foreign key to Group table’s ID. Basically, this value holds a Group.

-Activity Fee: If any; fee of the activity.

-Type: Type of the activity (may be changed to a drop box, rather than a text box), such as star watch, birthday, rockets etc.

-Attending Students: Number of students that will attend.

-Attending Adults: If any, number of adults that will attend.

-Title: Title of the activity.

-Date: Date of the activity.

-Time: Time of the activity –hour-.

-Room Needed: Number of rooms –classrooms for example- needed for the activity.

Leader Table

This table holds the information about the leader –organizer- of the activity.

-ID: ID of the leader.

-First name: First name of the leader.

-Last name: Last name of the leader.

-Phone1: A phone number to contact the leader.

-Phone2: Second phone number to contact the leader.

-Street Address: Street address of the leader.

-City: City that leader lives in.

-State: State that the leader lives in (may switch to a drop box where default is NY)

-ZIP: Postal Code of the leader.

-E-mail: e-mail address of the leader.

-Note: Notes.