Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BUS 440H Database Management

 Project 2: Adult Soccer League (TASL) Database

Use Case:

Tulsa Adult Soccer League (TASL) is a hypothetical adult-based outdoor soccer league based in Tulsa, OK. 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 Tulsa soccer park. Most of the soccer parks in Tulsa 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. TASL 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 2023

Figure 2: Sample Rosters for 3/5/2023 Tulsa North-3 3pm Match

Instructions:

Gather the Business Requirements:

Using the sample data in Figure 1 and other hypothetical data, design and build a relational database. To do so, look at the data and the use case carefully. List the business rules in bulleted form here. (Note: Get all your questions answered about the project requirements. Do not make any assumptions.)

Design the solution:

Write out the relational schema for all the data. Include determinants using functional dependencies for each attribute.

Normalize to 3NF.

Normalize to 1NF. Show your work.

Normalize to 2NF. Show your work.

Normalize to 3NF. Names are usually not appropriate for Pks, so add a code, if needed. Assign foreign keys, as appropriate. 

Add two improvements to this design, one of which must be a new feature. For example, a new feature could be adding attributes to track the formation (e.g., 4-3-3 formation) used by each team in each soccer match. Another idea is to track whether any yellow or red cards have been issued to any player in the league. After your improvements are decided, copy the 3NF answer and paste it here. Then, add your improvements to the design in the relational schema.

Create an EER in MySQL Workbench to show your final design (model) for the database, including all entities, relationships, and columns.

Add a timestamp to the Match entity to capture when each row of the table was entered and modified. (See the MySQL Workbench Design Walkthrough video on Moodle for instructions.)

Add comments to each value in the Coach entity, along with possible values for categorical data (e.g., Gender values of M=Male; F=Female).

Complete a meta data design. It should include the data type, length (where appropriate), and constraints for each attribute. It should indicate the PK and FKs for each table and other meta data (e.g., AI, NN, etc.). All dates should have the DATE data type.

Build the schema and database from the design:

Build the database and each datab