Problem: Build a database design in $3$NF for a professional sports

league. Identify all foreign keys

The league has a commissioner and an assistant commissioner

The league has two divisions

Premier $-$ the best $20$ teams

Championship $-$ the remaining $20$ teams

Each division has their own commissioner

Each team is assigned to one division

At the end of every season:

The bottom $3$ teams in Premier Division move to Championship

The top $3$ teams in Championship Division move up to Premier

Teams have a team name, are based in a city, has one or more

owners, and a home stadium.

Each team may have a manager $($e$.$g$.,$ they won't have a manager if

they fired the previous person, and the new manager has not yet

been named$).$

Each team has one to many players.

A player in the database might not be assigned to a team $($e$.$g$.,$ a

player who was recently cut$).$

Each player has a name and a primary position.

Rubric

$25$ Points: Correctly identified the entities & their attributes

Each entity must have a clear, well$-$structured definition

$25$ Points: Correctly identified the foreign keys

$25$ Points: Correct cardinality between the entities

Ensure that each relationship is clearly defined as one$-$to$-$one, one$-$to$-$

many, or many$-$to$-$many.

$25$ Points Model conforms to third normal form. Example considerations:

All attributes are atomic $($indivisible$).$

There are no transitive dependencies.

Each non$-$prime attribute is fully functionally dependent on the

primary key.