View source for 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 [http://http://www.kopernik.org/ 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 [http://www.techsoup.org/nonprofitsoftwaresem?gclid=CNCszcHH8LYCFQTd4Aod1HoAkw 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 [mailto:admin@kopernik.org 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 [http://www.filemaker.com/ 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 [https://en.wikipedia.org/wiki/Database_normalization#Free_the_database_of_modification_anomalies 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 [http://en.wikipedia.org/wiki/Third_normal_form 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.

[[File:databaseER.png]]


As seen on the diagram, there are actually four databases. A Student oriented one, Activity oriented one, Program, and Admin. Although, they are all in one database, and they are one database in reality, in this article, un-related table groups will be referred as different databases, to make it easier to explain and understand. 


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.


----


==== Group Table ====

This table holds info about the group that joins to activities.

-ID: ID of the group.

-Name: Name of the group.

-Street Address: Street address of the group.

-City: City that group lives in.

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

-ZIP: Postal Code of the group.

-Grade: School grade of group members.

-Number of students: Student count in  the group.



----
----

=== Student Database ===

This is the main database basically. Almost everything is attached to this database. Moreover, Contacts table was a separate table at the older database and it is also more independent than others. You shall be able to understand the relationship via ER diagram, and example on the Activity Table.


----


==== Student Table ====

-ID: ID of the student

-Parent1: Foreign Key which points a person at the Parent Table

-Parent2: Foreign Key which points a person at the Parent Table

-Parent member ID: Foreign Key to Membership Table, which points Parent’s Membership ID, if there is any and if it could be used by the student.

-First Name: First name of the student.

-Last Name: Last name of the student.

-Gender: Gender of the student.

-Date of Birth: Birthday.

-Graduation year: Grad. year used  for calculating classes.

-Medical concern: Any medical issues for the student.

-Dietary concern: Any dietary concerns for the student.

-Special concern: Any special concern with the student.

-Notes: Notes...

-Emergency contact: Emergency contact person. Right now, it is text, but might be a foreign key to a Parent.

-Date of creation: Auto generated date of creation field, which holds the date student info typed in.

-Resident/commute: Boolean to determine if s/he would stay overnight or leave for the night.

-Photo release: Boolean to determine if pictures of him/her could be used on internet and etc.

-Consent: Boolean to determine if the student accepted terms.

-Rules: Boolean to determine if the user accepted and obeys the rules

-History: Is there any historical info (probably in the notes)


----


==== Parent Table ====

-ID: ID of the parent

-Member ID: Foreign Key to Membership Table, which points Parent’s or Partners Membership ID, if there is any.

-Partner: Foreign Key which points the wife/husband at the Parent Table

-First Name: First name of the parent.

-Last Name: Last name of the parent.

-Gender: Gender of the parent.

-Street Address: Address of the parent.

-City: City parent live in.

-State: State parent live in.

-ZIP: Postal code.

-Phone1: A phone number to contact the parent.

-Phone2: Second phone number to contact the parent.

-Email: email address of the parent.

-Married: Boolean. Married or single. True if married.

-Has custody: Does this parent has right to pick up the student? Boolean.

-Note: Notes…


----


==== Classes Table ====

These are classes that students may attend.

-ID: Class ID.

-Class name: Name of the class.

-Class Date: Date of the class.

-Class Price: Price for the class.

-Class Season: Season that class will take place in.

-Discounted Price: Price for members.

-Note: Notes…


----



==== Classes Taken Table ====


This table, shows class taken by a student in one by one manner.

-Class ID: Foreign key to Class Table. Pointing which class is taken.

-Student ID: Foreign key to Student Table. Pointing which student is taking the class.

-Amount Paid: How much money paid so far.

-Amount Due: How much money will be paid.

-Discount: What is the discount student gets?

-Financial Aid: Does the student get any scholarships from Kopernik?

-Refund: Did the student give a refund? Why?

-Dinner: Will student eat dinner? Which type? (Yes/No, -allergies, no pork, etc.)

-Payment method: how was the class being paid for.

-Date Paid: When was the class paid for.

-Note: Notes…

-Confirmation send: A confirmation and information mail is sent? Boolean.


----



==== Membership Table ====

A table that is reachable by Parent Table and Student Table, holding membership values.

-ID: Membership ID.

-Member level: As known as membership type. 3 levels, Family, Individual, student/senior. Determines membership fee, and who can use the discount.

-Starting Date: Date that membership begun.

-Expiration Date: Date that membership will be over. 

-Note: Notes…


----



==== Event Table ====

Events are for parents and families. An event could be dinner, rocket lunch festival, meteor watch etc.

-ID: Event ID.

-Event name: Event name.

-Event date: Event starting date. (time may be added)

-Event Price: Price for the event (it may be ticket for the show, or price per dinner/rocket etc.)

-Event season: In which season does event take place?

-Discounted price: Discounted price for members.

-Note: Notes…


----



==== Event Bought Table ====


Events that are bought by a parent. Class-Student-ClassTaken kind of relation exists here.

-Event ID: Foreign key to Event Table, pointing event bought.

-Customer ID: Foreign key to Parent Table, pointing the person who bought the event.

-Amount bought: How many tickets (for dinner), rockets (to be fired at rocket fest) are bought?

-Amount due: How much shall the customer pay?

-Amount Paid: How much did the customer pay?

-Payment Method: How did the customer pay?

-Note: Notes…


----


==== Contact Table ====

These people could be a speaker, worker at a company that makes donations, or a person who likes to make donations.

-ID: ID of the contact.

-Company name: Where does this contact work? –why we have him in our DB-

-Contact person first name: Contact person’s first name.

-Contact person last name: Contact person’s last name.

-Contact Person title: Contact person’s title at his job.

-Street Address: Street address of the company or the person.

-City: City of the company or the person.

-State: State of the company or the person.

-ZIP: Postal code of the company or the person.

-Address Type: Business address or home address –can be a drop box-

-Phone1: Contact phone number.

-Phone2: Alternative contact phone number.

-Email: Contact’s e-mail address.

-Note: Notes…

Rest is Boolean variables to be able to be more specific about the contact.


----


==== Donation Table ====

Donation Table holds the donations that are made to Kopernik. A donation can be an object, volunteer labor hour, money and so on. A company, a parent or an unrelated (contact) person can donate something. Donations can be done for nothing, or for campaigns (Christmas Fund Raiser can be a campaign, whereas a parent may feel like donating for no special reason).

-ID: Donation ID

-Parent ID: Foreign key to Parent Table, if donation is done by a parent. Can be NULL.

-Contact ID: Foreign key to Contact Table, if donation is done by a contact (can be company as well). Can be NULL.

-Campaign ID: Foreign key to Campaign Table, if donation is done for a campaign (can be company as well). Can be NULL.

-Amount: How much money is donated?

-Type: Was it money that was donated? Or an object, or volunteer work.

-Date: Date that donation took place.

-Thank you letter: Did Kopernik send a Thank you Letter. Boolean.

-Explanation: Explanation of the donation –reason, item type, etc…-

-Note: Notes…

-Matching Fund: Yes or No. Boolean. Matching fund is the case when a company says, when a worker of mine donates x amount of money to somewhere, I will donate y amount to same place.


----



==== Campaign Table ====


Some donations may be done for campaigns. This table is used for grouping those donations.

-ID: Campaign ID.

-Campaign name: Name of the campaign.

-Date: Date of the Campaign.


----
----


== Kopernik Observatory Database Interface == 

=== Kopernik Science & Observatory Center Administration Panel ===

Kopernik Science & Observatory Center Administration Panel started to design after the completion of the database of the project.


As a starting of the Interface side of the project, first thing we did was asking from Kopernik employers to write us the ten things we need to do primarily. After we got the to do list, we started with the log in panel of the project.

[[File:kopernikLogIN2.jpg]]


In this page, basic log in rules are set, controls, warnings and the connection with the “admins” table in the database and then let the user go to the Admin Panel if he/she has permission to do that.


----


=== Administration Main Panel ===

This part of the website is the first screen that the user sees after he/she logged in to the page. This panel can be changed in the future, similar topics could be added together under the same menu, to make it more efficient.

[[File:adminMainPanel.jpg]]


----


=== Student Panel ===

This panel is to:


- Student Look-up and Student Registration,

- Enter Student information if he\she is a new student,

-Show if student is a member or not,

-To see amount of the paid money and paid method,

-Entering student name and finding out all the class they have participated in,

-Field for a photo release for each child


This Screenshot from the Registering Student Panel:


[[File:studentPanel.jpg]]



-----


==== Student Panel Look Up ====

In order to search students by their name or surname, This Panel is Searching for students and shows their necessary information.


[[File:studentPanelLookUp.jpg]]


----

=== Membership Panel===


This panel is to:

-Membership Lookup and Registration

-Look up to see if membership is current

-Know the type of Membership

-Enter amount paid and by what method

-Listing all children’s names

-Donation History

-See which memberships are about to expire

-Create new/unique membership numbers


[[File:membershipPanel.jpg]]


----


==== Membership  Expiration Panel ====

Membership Expiration panel shows the members whose membership will expire within 30 days so that they can offer new membership or send them e-mail to let them know their membership will expire soon.

[[File:membershipExpirationPanel2.jpg]]


----

=== Parent Sign in/out Panel ===


Kopernik wants a sheet that shows the student name-surnames, parent’s e-mail address and a field for the parents to sign for each class so that they can take the attendance and know if the student is being picked up by the parent, and have ability to prove it later:

[[File:parentSignInOutPanel.jpg]]


----


==== Parent Sign in/out Panel Screen 2  ====


This Panel is for creating sheet that parents can sign sheet while his/her child getting in the class end out of the class.


[[File:parentSignInOutPanelScreen2.jpg]]


----


=== Classes & Events Panel ===

This panel is to:


-Class Listing,

-Enter class and event name and list the participants

-Show the payment status (amount paid, amount due including any financial aid offered)

-Export email addresses for the class listing

-See if they have a permission to release the photo of each child

-Confirm the sending of class confirmation email/letter


Here is an example from the registering Classes and Events page:


[[File:ClassesEvents.jpg]]


----


=== Class Look Up Panel ===


In order to see the whole classes list (or just to search for specific one) a look up page is created:

[[File:classLookUp.jpg]]


----

===  Contact Management Panel ===

Kopernik requested being able to make extensive search of the companies, members and other people to see the all kind of information they need to know. Contact Management panel will be the place for this function.


This Panel is basically to:

-Enter or look up a person by name, company, email, phone number, address

-List a person’s donation history
 

----

=== Log Out from the Panel ===


Logging out from the panel destroys your log in information and sends you back to the log in page.