Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

How to solve the below question in Relational Algebra? Relations . Team(country, coach) A tuple in this relation represents the team of a country that

How to solve the below question in Relational Algebra?

image text in transcribed

image text in transcribed

image text in transcribed

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, datelssued, timeIssued, MID) A tuple in this relation represents a ticket that was purchased. TID is the ticket's id, datels- sued is the date that it was purchased, timelssued 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] C Team (country Match SID] Ticket[MIDMatch MID . Stadium SID] . CompetesMI Match MID . Competes|countryl C Team[country Competes(country2] C Team country] 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 i the number of goals when competed with each other at a match. If there are ties, report all of them 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 6. 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 name of the player of the country Spain' with the second largest 9. Report the MID(s) of the matches for which at least two tickets were bought on the date of 10. Consider al teams that have won at least one match. For each of these teams, report its number of goals among players of that country the match country, the position of its player with the largest number of goals and the number of goals he/she has scored. 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, datelssued, timeIssued, MID) A tuple in this relation represents a ticket that was purchased. TID is the ticket's id, datels- sued is the date that it was purchased, timelssued 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] C Team (country Match SID] Ticket[MIDMatch MID . Stadium SID] . CompetesMI Match MID . Competes|countryl C Team[country Competes(country2] C Team country] 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 i the number of goals when competed with each other at a match. If there are ties, report all of them 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 6. 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 name of the player of the country Spain' with the second largest 9. Report the MID(s) of the matches for which at least two tickets were bought on the date of 10. Consider al teams that have won at least one match. For each of these teams, report its number of goals among players of that country the match country, the position of its player with the largest number of goals and the number of goals he/she has scored

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

AWS Certified Database Study Guide Specialty DBS-C01 Exam

Authors: Matheus Arrais, Rene Martinez Bravet, Leonardo Ciccone, Angie Nobre Cocharero, Erika Kurauchi, Hugo Rozestraten

1st Edition

1119778956, 978-1119778950

More Books

Students also viewed these Databases questions

Question

Was there an effort to involve the appropriate people?

Answered: 1 week ago

Question

18. If you have power, then people will dislike and fear you.

Answered: 1 week ago