can someone write queries in relational algebra on a database?

Read carefully before you bid :

Relations

• Team(country, coach)

A tuple in this relation represents the team of a country that participates in the competition.

country is the team’s country name, and coach is the name of the team’s coach.

• Player(PID, fname, lname, position, goals, country)

A tuple in this relation represents a football player who is participating in the contest. PID

is the player’s ID, fname is their first name, lname is their last name, position is the position

they are playing on the pitch, goals is the number of goals the player has scored so far in the

competition, and country is the country of the team they are playing for.

• Stadium(SID, capacity, city)

A tuple in this relation represents a stadium where a match takes place. SID is the stadium’s

ID, capacity is the capacity of the stadium, and city is the city where the stadium is located.

• Match(MID, date, time, SID)

A tuple in this relation represents a match. MID is the id of the match, date is the date the

match is scheduled on, time is the time the match is scheduled on, and SID is the SID of the

stadium where the match takes place.

• Ticket(TID, dateIssued, timeIssued, MID)

A tuple in this relation represents a ticket that was purchased. TID is the ticket’s id, dateIssued is the date that it was purchased, timeIssued is the time that it was purchased, and MID

is the MID of the match it was purchased for.

• Competes(MID, country1, country2, goals1, goals2)

A tuple in this relation represents which teams are competing in a match. MID is the id of

the match, country1 is the country of the first team, country2 is the country of the second

team, goals1 is the number of goals scored by the first team, and goals2 is the number of goals

scored by the second team.

Integrity constraints

• Player[country] ⊆ Team[country]

• Match[SID] ⊆ Stadium[SID]

• Ticket[MID] ⊆ Match[MID]

• Competes[MID] ⊆ Match[MID]

• Competes[country1] ⊆ Team[country]

• Competes[country2] ⊆ Team[country]

PART 1

If a constraint cannot be expressed using this notation,

simply write “cannot be expressed”1. No team can play against itself.

2. All tickets for a match have to be purchased before the time of the match.

3. The number of tickets purchased for a match should not exceed the capacity of the stadium

where the match takes place.

4. A coach can only coach one team.

5. A player’s position should be one of ‘G’, ‘D’, ‘M’ or ‘S’ representing a goalkeeper, defender,

midfielder or striker, respectively.

PART 2

Write the queries below in relational algebra.

Your queries should work for any database that satisfies those constraints.

1. Report the country of the team that has played in every stadium. If there are ties report all

of them.

2. Report the MID of the match for which the highest number of tickets was purchased. If there

are ties report all of them.

3. Report the PID(s) of the player(s) of the team(s) that didn’t play in any match.

4. Report the SID(s) of the stadium(s) where exactly one match took place.

5. Report the coaches of the teams with the highest difference in the number of goals when

competed with each other at a match. If there are ties, report all of them.

6. Report the fname and lname of the players whose position is ‘D’ and have scored the largest

number of goals among all players (in any team) who play at the same position.

7. Find the winner country of the match for which the very first ticket out of all the tickets in

the database was purchased. If there was a tie in the match, report nothing.

8. Report the fname and lname of the player of the country ‘Spain’ with the second largest

number of goals among players of that country.

9. Report the MID(s) of the matches for which at least two tickets were bought on the date of

the match.

10. Consider all teams that have won at least one match. For each of these teams, report its

country, the position of its player with the largest number of goals and the number of goals

he/she has scored.

Administracija podatkovnih baz Razvoj podatkovne baze Programiranje podatkovnih baz MySQL SQL

ID projekta: #18674472

Več o projektu