Reference no: EM132675816

BUAD 283 Management Information Systems - Okanagan College

Entity-Relationship (E-R) Database Design

Kelowna Field Hockey Association's Volunteer Fundraising Event

You have been asked by Phil Park, the President of the Kelowna's Youth Field Hockey Association (KYFHA), to design a database system that will manage volunteers for their club's fundraising events. After your conversation with Phil, you determine that an E-R diagram would help map the data requirements from a logical perspective. In this assignment, you will only be designing (as opposed to developing) the database system to meet the needs of KYFHA.

Project Scenario:

A key responsibility of being the parent of a youth field hockey player is getting involved by serving on the KYFHA executive, supporting the field hockey coaching staff, and volunteering at the KYFHA fundraising events.

To be successful, each KYFHA event (e.g., bottle drive, carnival, or bake sale) must have both coach and parent volunteers. For example, each fundraising event first establishes a Sub- Committee that is made up of two or more parent volunteers. These sub-committee members are then responsible for managing the event promotions and for registering other volunteers to perform specific duties (e.g., set-up or take-down). Phil needs to be able to generate reports listing the parent members on each sub-committee, as well as the coach and parent volunteers who have been assigned roles and duties at each event.

From your second meeting with Phil, you have learned the following with respect to designing the logical Entity-Relationship data model for this database system:

1. Fundraising Events: Your design should capture information about each event, including the name of the event (e.g., Bake Sale), a full detailed description of the activities, the date and time of the event, the location (e.g., community hall), and the projected budget. Lastly, each event must have a KYFHA Coach as the Event Chair, in addition to the parent sub-committee members who are organizing the event.

2. Parents: Your design should store the personal contact information for parents of the youth players. You will use this data to contact and solicit parent involvement through e-mail, letter mail, and phone calls. (Note: You may assume a single contact person for each parent family.)

3. Coaches: Coaches are encouraged to volunteer at all KYFHA events (e.g., bake cookies). Furthermore, the club's President (Phil Park) selects a specific coach to act as the Chair for each event. (Note: A coach who chairs an event does not need to also sign up for volunteer duties at that event, but they can if they want to.) You will want to capture the personal contact information of all coaching staff, along with any specific skills they have that could be useful at fundraising events (e.g., guitar-playing, singing, or baking).

4. Volunteer Registration: The primary reason for this database system is to make registering and tracking volunteers for each event easier to manage. Besides the sub-committees' parent members who plan and promote each event, you must record the coach and parent volunteers who have specific duties to perform, such as selling tickets, baking pies, or cleaning up. Note that parents may participate on both an
event's sub-committee and perform job duties at the event itself, since most of the sub-committee work is done prior to the actual event.

While this information has been provided to help get you started, these entities or tables are not meant to be a complete list. You may need additional entities to solve Phil's data and reporting needs; however, these four will get you started on the right track.

Assignment Deliverables:

This assignment may be completed individually or with a partner. You will prepare and present a 3 to 4-page document that includes:

1. a 1-page cover memo, with the names and student numbers of each team member, a brief statement of the project's purpose (including a list of the key data and reporting needs), a description of any business rules, constraints, or assumptions that you have had to make to complete the data model. Your memo should be addressed to Phil Park, the KYFHA President.

2. a 1-page E-R diagram that shows the required entities/tables and their relationships. Each entity/table should specifically identify the Primary (PK) and Foreign Keys (FK) required and list the attributes/fields to be stored within each table. Make sure to label this diagram as "Figure 1. KYFHA Entity Relationship Diagram."

3. a 1- or 2-page database dictionary (prepared using either a Word table or an Excel worksheet) that describes the attributes/fields to be contained within each entity. For each entity, include the attribute names, descriptions, data types (e.g., text, number, currency, date, time, or Yes/No), and estimated widths in number of characters. Also, specify whether an attribute is a key field and/or requires data validation. (Note: See the lab exercises for examples of a data dictionary.)