LIS 543: Final Project Marathon Database (week 10 -prototype

1. LIS 543: Final ProjectMarathon Database(week 10 -prototype Presented By: David Bassett

2. Reiteration of Real World Problem Definition • Of all the organizational/logistical problems with setting up a marathon, the management of information on the runners themselves presents a complex problem. • Most marathons have to manage not only who is running the race, but what each individual runner’s times (start, finish, expected finish etc…) are, as well as billing information, emergency contact information, photographs taken of the runners, and demographic information. • Separating these types of information in tables while still being able to combine pieces of each table is the goal of my database. Perspective • I have taken this problem and designed a simple database and associated tables to deal with this problem. The tables use the Runner (and Associated Bib Number), Timing Chip Number, and Photo Account Number as Primary Keys.

3. Changes in design • Change 1: I realized I could not use the Photo Account Number as a primary key due to the fact I had multiple photos per account (common during marathons) • Change 2: I realized that address information was probably overkill and not necessary. Marathon race packets are usually picked up the day before the race at an expo.

4. Table: RUNNER This table contains basic demographic (name, age, gender), contact (email, phone), and account information (Race Bib #, Timing Chip, and Photo Account)

5. TABLE: TIME This table contains timing information for each runner organized by the primary key (timing chip) and the runner’s expected pace time, finish time, time across the start line and finish line, as well as the actual finish time, and their placement in the race

6. TABLE: PHOTO_AcCOUNT This table contains the photos associated with each individual and is organized by their photo account number

7. TABLE: EMERGENCY CONtACT This table contains the names of each individual runners’ emergency contact as well as their phone, and is organized by the runner’s Race Bib #.

8. Table Billing INfo This table contains the billing information for each runner (name, credit card number and billing phone) as well as their race bib number.

9. views • Two common views of database information that would be useful to race planners are: • A view showing the names of runners and their finish times, in order to display on a website or a printout. • A view showing the associated photos with each runner, so that runners know what photos were taken of them • The next two slides show these views.

10. View: Finish Time

11. View: photos

12. Appraisal of database • There were a few frustrations with building this database: • I tried to do too much initially with the database. • I would have liked to have a data type for time so that I could have computed prospective start and finish times from pace time. • When drafting the tables and the data for the tables, I made a few initial mistakes with entering data which caused some difficulties. However once I corrected these errors the tables and views worked well.