Identify the candidate keys of each relation

Assignment Help Basic Computer Science

Reference no: EM131269896

Super Baseball League Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on the teams, work experience of the coaches, bats belonging to each team, and which players have played on which teams. Note the following facts about this environment:

• The database keeps track of the history of all of the teams that each player has played on and all of the players who have played on each team.

• The database keeps track of only the current team that a coach works for.

• Team Number, Team Name, and Player Number are each unique attributes across the league.

• Coach Name is unique only within a team (and we assume that a team cannot have two coaches of the same name).

• Serial Number (for bats) is unique only within a team.

• In the AFFILIATION relation, the Years attribute indicates that number of years that a player played on a team; the Batting Average is for the years that a player played on a team

a. Identify the candidate keys of each relation.

b. Identify the primary key and any alternate keys of each relation.

c. How many foreign keys does each relation have?

d. Identify the foreign keys of each relation.

e. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key?

f. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data.

g. Assume that we add the following STADIUM relation to the Super Baseball League relational database. Each team has one home stadium, which is what is represented in this relation. Assume that a stadium can serve as the home stadium for only one team. Stadium Name is unique across the league.

What kind of binary relationship exists between the STADIUM relation and the TEAM relation? Could the data from the two relations be combined into one without introducing data redundancy? If so, how?
h. Using the informal relational command language described in this chapter, write commands to:

i. Retrieve the record for team number 12.

ii. Retrieve the record for coach Adams on team number 12.

iii. List the player number and age of every player.

iv. List the work experience of every coach.

v. List the work experience of every coach on team number 25.

vi. Find the age of player number 42459.

vii. List the serial numbers and manufacturers of all of the Vultures' (the name of a team) bats.

viii. Find the number of years of college coaching experience that coach Taylor of the Vultures has.

Reference no: EM131269896

Questions Cloud

How much of the final amount is interest : How much will be in the account 40 years later, when you retire at age 65? How much of the final amount is interest?

Describe the concept of the unary one-to-many relationship : How is a unary one-to-many relationship constructed in a relational database?

Health services for special populations : Population groups are disproportionately disadvantaged in accessing healthcare. Identify a vulnerable population and describe some of the key challenges in the delivery of services to that population. Given the challenges you note, what measures coul..

Compute the interest earned in problem : Find the future value at 3.25% interest, compounded continuously for 4 years, of the continuous income stream with rate of flow f(t) = 1.65e0.02t.

Identify the candidate keys of each relation : What kind of binary relationship exists between the STADIUM relation and the TEAM relation? Could the data from the two relations be combined into one without introducing data redundancy? If so, how?

Discuss the underlying assumptions and implications : Discuss the purpose and philosophical approach. Discuss the underlying assumptions and implications. If referring to a research article, present the methodology, where relevant, and discuss any practical issues or reasons to question the author's ..

Control discretion and investigate police misconduct : With respect to police decision-making, what is discretion? What makes it good or bad? Can it be both? In which types of situations do police officers exercise the most discretion? At which level do officers have the most discretion? After reading th..

Compute the interest earned in problem : Compute the interest earned in Problem.- Find the future value, at 2.95% interest, compounded continuously for 6 years, of the continuous income stream with rate of flow f(t) = 2,000e0.06t.

Create a market for the drugs economists can provide : Because legalization would create a market for these drugs, economists can provide helpful commentary based on consumer choice and demand.

User Account

All Pages