The Light

1. The Light An easy to use web-based tool for tracking members and jobs

2. Our Team • Kevin Tiller • Senior in Computer Science, responsible for Tier 1: web-site design, server, and UI implementation • David Thomas • Senior in Computer Engineering, reponsible for Tier 2: PHP design, Documentation • Clinton Thomas • Senior in Computer Engineering, responsible for Tier 3: MySQL server, database design and SQL implementation

3. Website DesignUser interface Kevin Tiller

4. Why The Light? • Looking to be a part of the crowd of people crazy about other people? Yearning for unity? You've come to the right place! The Light is a group devoted to bringing people together, regardless of association with campus ministries, to fellowship together while learning about our Lord and Savior Jesus Christ. We believe that having a grounded understanding of our faith is essential to being able to know Him and make Him known to others.

5. Why Tabs? • Single load • Once loaded the site no longer requires internet connection for basic functionality. • jQuery Implementation • Using the jQuery library simplifies the design of a “Tabbed” interface • Aesthetically pleasing UI • Leadership of “The Light” liked the way it looked • Easy organization • Allows for grouping and sub-grouping while staying simple enough for non-technical leaders to edit.

6. Why so much work? • Why 700 lines of interface for such a relatively small database? • UI is what people see and what they remember • Nobody will use the most efficient database if they can’t figure out how to get in and get stuff out • JavaScript is awesome?

7. Demo • Check it out at: www.thelight.pcriot.com

8. Middle Layer David Thomas

9. Decisions on Code • Middle Layer acts as the logical base for database insertion and query • Choices available - Java Server Pages - PHP (Hypertext Preprocessor) - CGI (compiled C/C++) • Originally intended on using JSP, but hosting service does not support • Decision was reached to use PHP

10. PHP Magic • PHP can be inserted inline with HTML, or called as a script from the HTML design file • Commands interpreted by server with a PHP language processor module • Lightweight, lazy typed scripting langauge with rich history and plenty of examples available online • Easy to use query interface to MySQL databases

11. Example MySQL Insert - PHP • This example connects to our local database • Inserts record from HTML form into the database using SQL INSERT statement • Handles errors thrown by SQL server • Displays a success message if completed without error • Code can be executed inline with HTML, or as a separate script <?php $con = mysql_connect("localhost","dbadmin_group14","group14"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("dbadmin_thelight", $con); $sql="INSERT INTO Tasks ( taskID, taskName, taskType, taskDate) VALUES ('NULL','$_POST[taskname]','$_POST[tasktype]','$_POST[taskdate]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "Task sucessfully created!"; mysql_close($con) ?>

12. Example MySQL Insert Page

13. Query-Insert Example • In this example, we ask the user for their first and last name, as well as the task name and date they would like to register for • The information is brought it from the HTML, and a query for the memberID and taskID values is generated • The returned results are inserted into the relation-table VolunteersFor <?php $con = mysql_connect("localhost","dbadmin_group14","group14"); if (!$con) { die('Could not connect: ' . mysql_error()); } $dbname="dbadmin_thelight"; mysql_select_db($dbname, $con); $memberquery ="select memberID from Members WHERE firstname=$_POST[firstname] AND lastname=$_POST[lastname]"; $memberresult = mysql_db_query($dbname, $memberquery) or die("Failed Query of " . $memberquery); //do the query $mID=mysql_fetch_row($memberresult); $taskquery ="select taskID from Tasks WHERE taskName=$_POST[taskName] AND taskDate=$_POST[taskDate]"; $taskresult = mysql_db_query($dbname, $taskquery) or die("Failed Query of " . $taskquery); //do the query $tID=mysql_fetch_row($taskresult); $sql="INSERT INTO VolunteersFor ( taskID, memberID) VALUES ('$tID','$mID')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "Volunteer sucessfully registered!"; mysql_close($con) ?>

14. Query-Insert Example Page

15. Database DesignAnd implementation Clinton Thomas

16. Database Selection • For the data management section of the design, MySQL was chosen as the backend DBMS • Reasons for selection- well documented- multi platform (Windows and Linux)- supported by our hosting service • XML and Microsoft SQL Server also considered • Reasons for not being selected- XML is slower when the DB grows much larger- Microsoft = lame

17. MySQL • Most widely used DBMS by Internet pages, due to open-source design and strong documentation • Accepts most standard SQL queries, offers several options for collation, data engines, etc. • Offers powerful, community developed creation tools for use in database design • MySQL server allows for local and remote execution of search queries

18. MySQL Database Schema • Members (memberID: integer, firstname: string, lastname: string, email: string, address: string, phone: string) • Groups (groupID: integer, groupName: string, groupSize: integer) • Expenses (expenseID: integer, taskID: integer, amount: real) • Tasks (taskID: integer, taskName: string, taskType: string, taskDate: string) • Donations (donationID: integer, amount: real) • VolunteersFor (memberID: integer, taskID: integer) • ClaimsExpense (memberID: integer, expenseID: integer) • LeaderOf (memberID: integer, groupID: integer, since: string) • ClaimsDonation (memberID: integer, donationID: integer) • EventSchedule(scheduleID: integer, date: string, topic: string, leader: string)

19. MySQL Database Model

20. MySQL Database Generation • #SQL commands to generate "The Light" database • # delete the database if it already exists • drop database if exists thelight; • #create a new database named thelight • create database thelight; • #switch to the new database • use thelight; • #Tables • CREATE TABLE Members ( • memberID INT AUTO_INCREMENT, • firstname VARCHAR(64), • lastname VARCHAR(64), • phone VARCHAR(16), • email VARCHAR(64), • address VARCHAR(64), • PRIMARY KEY (memberID, firstname, lastname) ); • CREATE TABLE Tasks ( • taskID INT AUTO_INCREMENT, • taskName VARCHAR(64), • taskType VARCHAR(64), • taskDate DATE, • PRIMARY KEY (taskID) ); • CREATE TABLE VolunteersFor ( • taskID INT, • memberID INT, • PRIMARY KEY (taskID, memberID), • FOREIGN KEY (taskID) REFERENCES Tasks (taskID), • FOREIGN KEY (memberID) REFERENCES Members (memberID) ); • CREATE TABLE Donations ( • donationID INT AUTO_INCREMENT, • amount FLOAT, • PRIMARY KEY (donationID) ); • CREATE TABLE ClaimsDonation ( • memberID INT, • donationID INT, • PRIMARY KEY (memberID, donationID), • FOREIGN KEY (memberID) REFERENCES Members (memberID), • FOREIGN KEY (donationID) REFERENCES Donations (donationID) ); • CREATE TABLE Expenses ( • expenseID INT AUTO_INCREMENT, • taskID INT, • amount FLOAT, • PRIMARY KEY (expenseID, taskID) ); • CREATE TABLE ClaimsExpense ( • memberID INT, • expenseID INT, • PRIMARY KEY (memberID, expenseID), • FOREIGN KEY (memberID) REFERENCES Members (memberID), • FOREIGN KEY (expenseID) REFERENCES Expenses (expenseID) ); • CREATE TABLE Groups ( • groupID INT AUTO_INCREMENT, • groupName VARCHAR(64), • groupSize INT, • PRIMARY KEY (groupID) ); • CREATE TABLE LeaderOf ( • memberID INT, • groupID INT, • since DATE, • PRIMARY KEY (memberID, groupID), • FOREIGN KEY (memberID) REFERENCES Members (memberID), • FOREIGN KEY (groupID) REFERENCES Groups (groupID) ); • CREATE TABLE EventSchedule (scheduleID INT, date DATE, topic VARCHAR(128), leader VARCHAR(48), PRIMARY KEY (scheduleID, date) );