Toastmasters Website Project

Lecture Examples and APIs

In this assignment you will work in a team of up to six members to create an updated version of the database back end for the Downtown Business Associates Toastmasters Club Intranet website.

The Database currently consists of four tab-delimited spreadsheets. The roster, availability, desires and schedule are read each week and a new schedule line is created in the schedule table by this Perl script.

You will create APIs to support these pages:

New pages will be designed by this semester’s ICS 415 class. They will be given the specifications of your APIs and they will design to them.

Database

The new database must be in BCNF so as many constraints that can be enforced using primary keys as possible.

ROSTER

Start by creating a new ROSTER table starting with just { TID, Key, Name } where “TID” is an auto-incrementing integer Primary key starting at 1.

Load the table with the union of the { Key, Name } projections of the original Schedule, Roster and Availability tables.

Make sure you ensure all the Key fields are unique so you are able to set the Key column to UNIQUE. It helps to construct the original table in Excel before uploading it to your database. Once everything works we will not be using the Key field in any of the tables anymore.

Add a new column ‘Active’, along with the other columns in the original Roster table.

Then copy the records from the original Roster table to the new ROSTER table. For each record in the original Roster table, set the ‘Active’ field to ‘1’.

ROSTER APIs

Retrieve Member Record

Update Member Attribute

Add Member

Mark Member for Deletion

ROSTER WEB PAGES

Display Table of Members

Display Member Record

DESIRES

The original duty desires table stored a toastmaster’s desires using a separate column for each duty. This makes sure each duty has a defined desire. Hardcoding duties as fields into the table may not be preferred if duties can change and their names appear in other tables.

We can start by defining a DUTIES table { DID, Duty, Order } that is populated with the current set of duties in a preferred order for display on the update form.

Then we can define a new DESIRES table with header { TID, DID, Desire }. We can also define a default value in the ROSTER table for if a tuple defining the duty desire for a toastmaster is absent.

DESIRE APIs

Retrieve Member Desires

Update Member Desire

DESIRE WEB PAGES

Display Desires for Member

SCHEDULE

The original schedule table contained a record for each meeting and a column for each meeting duty. The name of the toastmaster appears in the cell for that duty for that meeting.

We can start by defining a MEETINGS table { MID, Date, Location } that contains a record for each scheduled meeting. New records added to this table can still be automated and into the future so dates can be deleted if necessary.

An AGENDA table { AID, DID, Num, Order } contains the order of duties on a meeting agenda. Num accommodates multiple assignments of the same duty, such as Speaker or Evaluator.

Then we can define a new SCHEDULE table { MID, AID, TID} that contains toastmaster assignments to agenda duties on meeting days.

SCHEDULE APIs

Add Member to Meeting Duty

Delete Member from Meeting Duty

Modify Member Meeting Duty

SCHEDULE WEB PAGES

Display a Meeting Agenda

Display Multiple Meeting Agendas Table

AVAILABILITY

The original availability table contained one record for each toastmaster and a column for each meeting date. An ‘X’ for a meeting date indicates the toastmaster will not be available to be scheduled on that date.

We can define a new UNAVAILABLE table { MID, TID } that contains a tuple for any meeting a toastmaster is unable to attend.

AVAILABILITY APIS

Update Member Meeting Availability

AVAILABILITY WEB PAGES

Display Multiple Meeting Availability Table for Member

Display Member Availability for Multiple Meetings