View source for Kopernik Observatory Database

[[Media:Example.ogg]]Kopernik Observatory Database Design and Interface Senior Project Manual
Contents
1. Introduction to Kopernik Observatory	2
1.1 Introduction to Kopernik Observatory	2
1. 2 Kopernik as a nonprofit organization	2
1.3 Contact Info – Suggestions	2
2. General Information about the Project	4
2.1 Kopernik’s Need for a Database	4
2.1.1 Kopernik’s Old Database System – File Maker	4
2.1 Kopernik’s New Database - Outline	5



 
1. Introduction to Kopernik Observatory
1.1 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. .
1. 2 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.

1.3 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.
 
2. General Information about the Project

	This project has two main parts, re-modeling the database and giving an interface to that database. 
2.1 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.

2.1.1 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.
2.2 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 normalizations, 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.

2.3 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.

3.  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.
	
4. 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 can have for parents, eight grandparents, and brothers. 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.