Draw the ER diagram and answer problem $1-5$ please.

Problem $1$: Create an ER diagram that corresponds to the following scenario. Be sure to

capture all relevant entities, relationships, attributes, primary keys, constraints, and any other

appropriate constructs as covered in class. When necessary, state any additional assumptions

you make, as long as they are reasonable and do not contradict the problem statement. You

may use either of the templates available on Canvas, or hand$-$draw your ER diagram.

A football league is trying to create a database of its players, teams, and other

employees.

Each player is identified by their player ID$,$ but the database should also store their

name, position, jersey number, and salary. Players are assigned to exactly one team.

Each team as an ID$,$ name, and mascot.

For each game, the database should store the score and the date. Games are played by

two teams at a field; fields are also stored in the database according to their address and

the name of the field. Games are officiated by referees, who should have their league

referee ID and name stored in the database. Seven referees officiate each game.

At the end of every game, a picture is taken of the winning team for the league website.

The photo is assigned an ID and given a caption. The players and the team itself should

both be associated with the picture so that fans can search based on the player's name

and team's name.

Finally, each team is managed by multiple coaches with various roles: a head coach as

well as assistant coaches for defense, offense, and special teams. Coaches can only be

associated with one team at a time due to the nature of their contracts.

Problem $2$: Identify any functional dependencies in the description of Problem $1.$ Consider any

FDs that model $($a$)$ primary key constraints and FDs that model $($b$)$ many$-$to$-$one or one$-$to$-$one

relationships. Use $$ to denote them.

Problem $3$: Two hardware stores want to merge and have overlapping product lists. Each store

has an ID$,$ a name, and an address, and each product has a store$-$specific product ID$,$ price,

and description. Some products have a record for one store, while others have conflicting

records for both stores. Draw an ERD depicting this situation and upload a scan of the solution

as an image. Hint: consider how to model this as a single strong$-$weak entity pair.

Problem $4$: Consider the following relation:

$R(A,B,C,D,E)$

And the following set of functional dependencies $F$ :

$A,CE$

$ED$

$BC,E$

$BD$

$($a$)$ Using the FDs$,$ determine the key K of R$.$ Hint: there is only one possible key for $R.$

$($b$)$ Determine a minimal basis $B$ for $F.$

$($c$)$ Using $K$ and $B$ determined above, calculate the $3NF$ of this database using the synthesis

algorithm. Show the resulting relations and their keys.

Problem $5.$ We now want to convert $R$ from Problem $4$ to $BCNF$ with respect to the minimal

basis $B$ using the splitting algorithm. The algorithm would begin as follows:

$S=\{R\}$

$R$ is not in $BCNF$ with respect to $A,CE.$ Therefore, we split it into:

$R1(A_{,}{C}_{,}D,E)$

R$2($A$,$ B$,$ C$)$

$S=\{R1,R2\}$

Note that the key of R$2$ carries over from the key of the relation that was split $($R$).$

Continue the splitting algorithm until all relations are in BCNF$.$ Show your work. Hint: you should

need to do two more splits.