TdE SOL BD1 12 01 2022 ENG SOLUTIONS PART 1

Page 1

WINTER OLYMPIC GAMES The following schema stores information about time-based competitions of the Winter Olympic Games. ATHLETE ( AthleteID, Name, Surname, Country, BirthDate ) COMPETITION ( CompID, Discipline, Date, ItIsTheFinal ) PARTICIPATION( AthleteID, CompID, Time ) SQL 1. Specify the commands for creating table PARTICIPATION, defining the tuple and domain constraints deemed appropriate and expressing any referential integrity constraints towards the other tables. [2p] 2. Find the ID, Name and Surname of the athletes who took part in at least two competitions, but never in a final. [4p] 3. For each Country, show the number of gold medals obtained by its athletes. Athletes obtain a gold medal when they obtain the best (shortest) time in a final. [5p] 4. Express the constraint that verifies that no Discipline has two finals. [4p] Formal languages 5. Write query n. 2 in two formal languages of your choice. [4p] 1. Create table Participation ( AthleteID integer references Athlete(AthleteID) on delete no action on update cascade ), CompID integer references Competition(CompID) on delete no action on update cascade ), Time interval hour to millisecond, primary key ( AthleteID, CompID ) ) It is reasonable to assume that in non-completed performances ( like when in slalom an athlete fails to fully pass a gate, and “does not finish” ) either Time is set to NULL or the tuple is not listed (as if the athlete didn’t even take part in the competition). 2. select AthleteID, Name, Surname from ATHLETE natural join PARTICIPATION where AthleteID not in ( select AthleteID from PARTICIPATION natural join COMPETITION where ItIsTheFinal = ‘yes’ ) group by AthleteID having count(*) > 1 3. select Country, count(*) as NumOfGolds from ATHLETE natural join PARTICIPATION P natural join…