super baseball league. the super baseball league wants to keep track on informat
ID: 3801356 • Letter: S
Question
super baseball league. the super baseball league wants to keep track on information about its players, and its teams, and the minor league teams (which we will call minor league "clubs" to avoid using the word "team" twice). minor league clubs aren't part of the super baseball league but players train in them with the hope of eventually advancing to a team in the super baseball league. the intent in this problem is to keep track only of the current team on which a player plays in the super baseball league. however, the minor league clubs for which a player has played. team names, minor league club names, manager names, and stadium names are assumed to be unique, as of course, is a player number. design a well structured relational database for this super baseball league environment using the data normalization teachnique. progress from first to second normal form and then from second to third normal form justifying your design decisions at each step based on the rules of data normalization. the attributes and functional dependencies in this environment are as follows:
attributes:
player number, player name, player age, team name, manager name, stadium name, minor league club name, minor league club city, minor league club owner, minor league club year founded, start date, end date, batting average
functional dependencies:
player number ---> player name
player number ---> age
player number ---> team name
player number ---> manager name
player number ---> stadium name
minor league club name ---> city
minor league club name ---> owner
minor league club name ---> year founded
team name ---> manager name
team name ---> stadium name
player number, minor league club name ---> start date, end date, batting average
*Please use a table in word or excel, if you can, I found the other answer to this question confusing. thanks
Explanation / Answer
Make table:
Player Number
Player Name
Player Number
Player Age
player Number
Team Name
Player Number
Batting Average
Player Number
start date in Club
Player Number
end date in club
Player with columns
Player Number | Player Name | Player Age | Team Name | Batting Average |Start date in Club | End Date in Club
where Player Number is primary key
Club Name
City
Club Name
Owner
Club Name
Year Founded
Club Name
Start Date of Player
Club Name
End Date of Player
make a table Club with following Columns
Club name | City |Owner | Year Founded | Start Date of Player | End date of player
where Club Name is primary key.
Team name-
Manager Name
Team name
Stadium Name
make a table Team with following Columns:
First Normal Form
All the tables are in First normal form as there are atomic values for all columns
Second Normal Form
The tables Player and Club are not in 2nd normal form because the start date and end date of a player playing for a club is neither fully functionally dependent on Player Number or Club Name.
so create a new table Player_ Club with these Columns
Player _Club
Player Number | Club Name | Start Date | End Date
where Player Number and Club Name is the composite primary key
3rd Normal Form
There is no transitive dependency in the tables Player, Club ,Team and Club Player.
So all these tables are in 3rd normal form
In 3rd normal form there should not be any transitive functional dependency in the tables.
Player Number
Player Name
Player Number
Player Age
player Number
Team Name
Player Number
Batting Average
Player Number
start date in Club
Player Number
end date in club
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.