The database you will use for this assignment contains information related to Ma

Question

The database you will use for this assignment contains information related to Major League Baseball (MLB) about players, teams, and games. The relations are: Players(playerID, playerName, team, position, birthYear) playerID is a player identifier used in MLB, and all players throughout the history of baseball have a unique ID playerName is player’s name team is the name of the MLB team the player is currently playing on (or the last team the player played for if they are not currently playing) position is the position of the player birthYear is the year that player was born Teams(teamID, teamName, home, leagueName) teamID is a unique ID internal to MLB. teamName is the name of the team home is the home city of the team leagueName is the league the team is in, i.e. either “National” or “American”, which stands for “National League” and “American League”, respectively Games(gameID, homeTeamID, guestTeamID, date) gameID is a unique ID used internally in MLB homeTeamID is the ID of the hometeam guestTeamID is the ID of the visiting team date is the date of the game. A sample instance of this database is given at the end Question: (c) Note that there is no way to represent the fact that a player may have played on several different teams (for example, Javy Lopez played for the Braves, Orioles and RedSox before retiring), or that they are currently retired. How would you modify the schema to take this into account? (Hint: try to do it in a way that information is not repeated unnecessarily.)

Explanation / Answer

i cannot find (a),(b) here so i'm assuming we are only required to answer this part

Note that there is no way to represent the fact that a player may have played on several different teams (for example, Javy Lopez played for the Braves, Orioles and RedSox before retiring), or that they are currently retired. How would you modify the schema to take this into account?

-----------------

To store the information about a player who has played in several teams , we need to create a new relational database table.

But we must do this without having to repeat multiple values as required in the question.

we'll require the concept of primary keys and foreign key here.

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records. A primary key's main features are: It must contain a unique value for each row of data. It cannot contain null values. (source for definition techopedia)

a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.(source for definition techopedia)

what we shall do is relate two tables using primary keys! So in our new table let us call it mult_team, we will have playerID to relate it to the Players table, but how do you store multiple teamID to represent the teams a player has played for?

what we do for this is, make the playerID as unique for mult_team. And the teamID is our foreign key so it can be a set of fields , hence, we can now store multiple teams for a single playerID. So, now the table will be look like this,

--------------------------------

thank you

mult_team table sample playerID(primary key) teamID 3333 (say) US8 (say),US9, US10 2312 US9 2424 US8,US1

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.