MSDS7330- Mid-Term Exam Solved

Mid-Term Exam: Directions 

You are asked to develop/manage a database for an association that organizes basketball tournaments.  

 

Each year they host a number of tournaments in different locations, any number of teams can participate in any number of tournaments. Each tournament has a certain number of games.  The following you have been given minimum attributes to store in the database. This is not an inclusive list, as it does not contain any keys needed in a table. Please add any items that you feel will be beneficial.  

 

●     ToureyID,  

●     TourneyDate,  

●     TourneyLocation,  

●     TeamName,  

●     HeadCoachFirstName,

●     HeadCoachLastName,  

●     PlayerFirstName,  

●     PlayerLatName,  

●     PlayerStreetAddress,  

●     PlayerCity,  

●     PlayerZipCode,

●     GameNumber (As stated earlier, each tournament has a certain number of games)

●     CourtID (This is the court number where the game is being played. Just remember multiple games within a tournament will be played on the same court)

●     WinningTeam (Team that won the game)

●     HomeTeam & AwayTeam (Designate which team is Home and which is away within a game)

●     PlayersScore (Score of each player in a specific game within a tournament)

 

 

Tasks to do: 

 

1.            Develop a normalized schema for the database using MySQL Workbench

2.            Create the tourney DB using the model you just created (forward engineering)

3.            Insert values into the Database from the csv files provided.  

4.            Perform the following queries:

a)                      Provide locations where the association is holding tournaments

b)                      Display all players and their address formatted suitably for a mailing list, sorted by zip code.

c)                      Display teams and the name of their head coach.

d)                      Show tournaments that have not been played yet.

e)                      Display name of top 10 scorers (Players who scored highest) along with their score.

f)                       Display players’ names along with their highest score  

g)                      List the player (names) whose highest score in a game is more than 10 points higher than their average.

 

5.            Create a view of all the tournaments that have been played at Red Rooster.

6.            Connect to this database using Python

a) Display name of all the tournaments that were previously held.   

Data: 

 

You will find csv files that should give you most of the attributes that you need to complete the test.