BUS $440$ Database Management

Spring $2024$ Project $1$: Adult Soccer League $($CASL$)$ Database

Due $3/29/2024$

Use Case:

Cincinnati Adult Soccer League $($CASL$)$ is a hypothetical adult$-$based outdoor soccer league based in Cincinnati, OH$.$ The league boasts of $12$ current teams, each with at least $11$ players, playing a spring and fall season each year.

Each team is coached by one head coach and one assistant coach $($optional$)$ and sponsored by one local company. A coach is assigned to one team only. Coaches are usually selected on a volunteer basis and enrolled each season with their full name, age, and gender $($e$.$g$.,$ Fred Lasso is the Head coach the Ranchers, while the Assistant coach is Red Beard$).$

Players enroll in the league in the spring of each year and are assigned to play on one team. Each player on a team is assigned a jersey with a unique number. Players must include their full name, age, and gender when they enroll. Players must be at least $18$ years of age to be accepted in the league. One player on each team is assigned as the captain.

Matches are $90$ minutes in length and are played between two teams on one field of a park under the control of a referee $($ref$).$ If the match ends in a tie, the teams will play two extra times of $15$ minutes or go directly to penalty kicks so that a match winner is established. Teams play matches on a field of a Cincinnati soccer park. Most of the soccer parks house several fields, and a field can host several matches.

Typical positions on a team include a Goalkeeper, Forwards $($Striker$,$ Center Forward, Wingers$),$ Midfielders $($Mid$,$ Right Mid, Center Mid, Attacking Mid, Defensive Mid$),$ and Defenders $($Center back, Left Back, Right Back, and Wing Backs$).$ The coach picks a captain for each team, decides the roster $($which players play which positions, Figure $2)$ before the match, and ensures that stats are tracked for each match and for each player. Some of the most$-$commonly tracked stats include match score, goals, assists, possession $\%,$ pass counts, and passing chains. For example, in the first match of the season, Denise Stacker had $3$ goals and $2$ assists.

Sponsor company names and addresses are tracked, as well as the email of the main contact at the sponsor company. Teams wear jerseys sporting the sponsor$$s name. CASL allows companies to sponsor more than team $($e$.$g$.,$ First Union Bank sponsors both the Ranchers and Manchester teams$).$

The league director plans the season schedule for all the teams. A match schedule for the day could look like the following $($Figure $1)$ with the match score and winner filled in after the match concludes.

The league director has been keeping data in text$-$based documents $($e$.$g$.,$ MS Word or Google Sheets$)$ but is finding it increasingly difficult to get insights from the data. Thus, the league has decided to build a database to support its operations.

$$

Figure $1$: Spring Season $2024$

Match Date Match Start Time Park Field

# Field Name Home Team Name Away Team

Name Ref Match Score Winner

$3/5/20243$pm CNorth $3$ Admiral Ranchers Bulls Sam Jones $4-6$ Bulls

$3/5/20244$pm CNorth $4$ Largess Courage Knickers Colby Warren $1-5$ Knickers

$3/5/20244$pm CSouth $1$ Coastal Reds Cambridge Elite Taylor Parker $4-0$ Reds

$3/10/20245$pm CNorth $3$ Admiral Arsenal Madrid Newcastle Colby Warren $1-0$ Arsenal

$3/10/20246$pm CNorth $2$ Semgroup Munich Elite Manchester Sam Jones $4-5$ Manchester

$3/10/20246$pm CSouth $2$ Vanguard

PSG Porto Taylor Parker $4-2$ PSG

$3/15/20243$pm CNorth $3$ Admiral Ranchers Knickers Sam Jones $4-6$ Knickers

$3/15/20244$pm CNorth $4$ Largess Courage Bulls Colby Warren $1-5$ Bulls

$3/15/20244$pm CSouth $1$ Coastal Reds Madrid Newcastle Taylor Parker $4-0$ Reds

$3/20/20245$pm CNorth $3$ Admiral Arsenal Cambridge Elite Colby Warren $1-0$ Arsenal

$3/20/20246$pm CNorth $4$ Largess Munich Elite Porto Sam Jones $4-5$ Porto

$3/20/20246$pm CSouth $2$ Vanguard

PSG Bulls Taylor Parker $4-2$ Bulls

Figure $2$: Sample Rosters for $3/5/2024$ CNorth$-33$pm Match

Team Player Name Position Jersey #

Ranchers Connor K$.$ Barritt Goalkeeper $1$

Ranchers Tucker Lee Jones Defensive Midfielder $4$

Ranchers EJ Brown Left Midfielder $11$

Ranchers Leyla Greenfield Center Midfielder $8$

Ranchers Connor Barritt Right Midfielder $3$

Ranchers MJ Zuckersmith Striker $9$

Ranchers Denise Stacker Center Forward $10$

Ranchers Amanda Bonner Winger $7$

Ranchers Allie Speilberg Center back $5$

Ranchers Eli Khan Left Back $6$

Ranchers Lily Ravi Right Back $2$

Bulls Stephen Lessin Goalkeeper $1$

Bulls Wallace Jones Defensive Midfielder $4$

Bulls Wesley Browning Attacking Midfielder $12$

Bulls Gail Carlson Center Midfielder $8$

Bulls Julia Parker Right Midfielder $3$

Bulls Shannon Bohlmann Striker $9$

Bulls Olivia Curry Center Forward $10$

Bulls Jordan Bonner Winger $7$

Bulls TJ Styliano Center back $5$

Bulls Reza Laker Left Back $6$

Bulls Megan Zhang Right Back $2$

Instructions:

Gather the Business Requirements:

$1.$ Using the sample data in Figures $1$ and $2($and your own hypothetical data$),$ design and build a relatio