Entity-Relationship (E-R) Database Design

Entity-Relationship (E-R) Database Design
Okanagan Youth Soccer Club (OYSC) Parent Board
You have been asked by Andy Broadhurst, the Chairman of the OYSC Parent Board, to design (but not develop) a database system for managing volunteers for their club’s fundraising events. After your conversation with Andy, you determine that an E-R diagram would help map the requirements from a logical information perspective.
Project Scenario:
A key responsibility of being a soccer parent is getting involved with the club by serving on the Parent Board or by volunteering at fundraising events. Each OYSC event (e.g., club dance, wind-up jamboree, or bake sale) must have both parent and coach involvement. Additionally, each event must appoint an Executive Board member as the Event Chair and an OYSC Coach as the Event Vice-Chair. Lastly, each event must have a Sub-Committee made up of one or more parents, who are then responsible for managing promotions and securing volunteers.
From your initial meetings with Andy, you have been able to discern the following with respect to designing the logical Entity-Relationship model for this database system:
1. Events: Your design must capture information about each event, including the name of the event (e.g., Club Dance), a detailed description of the activities, the proposed date of the event, the location of the event, the appointed Chair and Vice-Chair, and the parent sub-committee members who have volunteered to organize the event.
2. Parents: Your design should store basic contact information for the adult parents. (Note: This database design will not store any player data, as that is handled by a different database registration system.) You will need the parent data in order to contact and solicit involvement through e-mail, post-mail, and phone calls.
3. Coaches: Some coaches will agree to be Vice-Chairs for specific events, while others will simply want to volunteer at the events (e.g., sit in the dunk tank at Jamboree). You will need to capture their personal information and team association (e.g., U16 Boys) in order to contact and solicit involvement through e-mail, post-mail, and phone calls.
4. Parent Board Executive: Your design should provide Andy with the ability to retrieve information about the currently-elected (and past) Executive Board Members (e.g., the Chairman, Vice-President, Treasurer, and Secretary.) Also, note that a parent may serve in multiple positions over several years, given the annual elections required by the board.
5. Volunteer Registration: The purpose of this database is to make registering both parent and coach volunteers for each event easier to manage. Besides identifying the sub-committee parents for each event, you must be able to query and run reports on the names of volunteers and their assigned duties (e.g., Coat Check between 5-6pm.)
While this information has been provided to help get you started, these entities/tables are not meant to be a complete list (or even the correct list) of entities/tables that you will need to solve Andy’s problems; however, they will get you started on the right track.
Assignment Deliverables:
Prepare and present a 4-page document that includes:
1. a 1-page transmittal or cover memo, with the names and student numbers of each team member (maximum of 2 persons allowed), a statement of the project’s purpose, and a list of the key business requirements, constraints, or assumptions that you have made in your E-R diagram. Your memo should be addressed to Andy Broadhurst, OYSC Parent Board.
2. a 1-page E-R diagram that shows the required entities/tables and relationships. Each entity/table should specifically identify the Primary (PK) and Foreign Keys (FK) required and the list of attributes/fields to be stored within each table. Title your graphic as “Figure 1. Entity Relationship Diagram” and provide any descriptive notes that you deem necessary below the graphic. (Note: You may use a landscape-oriented page for this diagram only.)
3. a 1- or 2-page database dictionary (prepared using an Excel worksheet or a Word table) that describes the attributes/fields to be contained within each entity/table. For each entity/table, specify each attribute’s name, description, data type (e.g., text, number, date/time, currency, Yes/No), estimated maximum width, and whether it is a key field or requires data validation (e.g., phone numbers or dates entered using a specific format). Title your data dictionary as “Table 1. Database Dictionary.”
A sample data dictionary layout appears below (one of these for each table entity):
Entity/Table Name (e.g., PLAYERS)
Attribute Key Field Data Type Width Description Validation
Player ID Primary Key Number 5 Auto-numbered Unique
First name Text 20 Parent’s first name
Last name Text 20 Parent’s last name
Email Text 50 Primary email address Requires an “@” symbol
…
Comments from Support Team: Discipli