DBMS-ERD design

ERD Homework Assignment #1

Mike has completed a course in Database Design.

He has decided to design a database to store the data that he has currently entered in an Excel spreadsheet to track the U. S. Baseball teams and the games they play.

Mike is a baseball fan and knows the following:

a. Each baseball team can have 1 or more coaches. A baseball team always has at least one Coach. Each coach is hired for a specific purpose

b. Each baseball team has many players. Each player is assigned to a specific job for the team and can only play for one team at any given time

c. A team can play many games but plays only one game per day. Each game is played against one other team and at a specific location

Mike would like also to track who won each game that was played and what the score was. For example on 3/28/2019 NY Mets played a game against the Washington Nationals. The game was played in Queens and the NY Mets won the game 2-0

Draw an ERD that identifies

1. Each entity about which Mike is maintaining data

2. How the entities are related to each other (foreign keys)

3. What field or fields can be used to uniquely identify each entity (primary keys)

4. What other fields / data you might consider storing about any given entity

MCON343 fall 2020\ERD HW1 Baseball Games.docx ©mplonczak

TeamNamePlayerNamePlayer RoleCoachCoachCoachGameDateTeamPlayedGameLocationGame2DateTeamPlayedGameLocationGame3DateTeamPlayedGameLocationTeamLocation

NY MetsJacob DeGromPitcherHensley Meulens Bench CoachChili Davis Hitting Coach3/28/2019Washington NationalsQueens4/2/2019Miami MarlinsQueensQueens

NY MetsPete AlonsoFirst BasemanHensley Meulens Bench CoachChili Davis Hitting Coach3/28/2019Washington NationalsQueens4/2/2019Miami MarlinsQueensQueens

NY MetsqMarcus StromanPitcherHensley Meulens Bench CoachChili Davis Hitting Coach3/28/2019Washington NationalsQueens4/2/2019Miami MarlinsQueensQueens

Washington NationalsRyan ZimmermanThird BasemanDavid Martinez Bench CoachTimothy Bogar Hitting Coach

Paul Menhart

Pitching Coach3/28/2019NY MetsQueens4/2/2019Philadelphia PhilliesPhilidelphia4/14/2019

Pittsburgh

PiratesPittsburghWashington D.C.

Washington NationalsKurt Suzuki CatcherDavid Martinez Bench CoachTimothy Bogar Hitting Coach

Paul Menhart

Pitching Coach3/28/2019NY MetsQueens4/2/2019Philadelphia PhilliesPhilidelphia4/14/2019

Pittsburgh

PiratesPittsburghWashington D.C.