Part 1

Detailed Instructions (follow carefully)

This assignment refers to an implementation of the motorsport database as designed in the the first as- signment. To answer the questions of this assignment you must run the SQL scripts a2-setup .sql and a2-setup-additional .sql that define the tables that your code will rely on. For the com- pletion of this assignment it will be helpful to inspect the table structure set up by this script. Do not modify the structure of the tables when you write your answers unless explicitly told to do so.

Note that a few data records have been inserted into the tables to help you test your answers.  It is recommended that you test your code with additional sample data you insert into the tables yourself. However, do not include any of the test data or the corresponding insert statements in your submission. Also, you must not include the code of a2-setup .sql in your answer.

For each question you should provide only one SQL statement as answer. Place this statement directly below the corresponding question comment, e.g. --@@01 if you answer Question 1, on a new line in the template file.  Below is an example where to put your answer to Question 1 and Question 3 and how to leave Question 2 unanswered:

--  @@01

SELECT  myanswertoQ1  FROM  mytable  WHERE  1=1

--  @@02

--  @@03

SELECT  myanswertoQ3  FROM  mytable  WHERE  1=1

--  @@04

Do not remove any of the template comments as they drive the testing rig.  Where a query is very complex, you might wish to add comments to help the marker appreciate what you have done in case your query is not correct.

Marking Guidance

•  Marking will be mainly driven by testing your code.

•  Statements that are not parsing, i.e. throw a syntax error, receive 0 marks!

•  Correctly running statements will receive marks proportional to how close their result is to the correct answer.

There are 12 questions in Part 1, which you will find on the following pages.

1.  Write one SQL statement to set up table MoSpo HallOfFame according to the following Rela- tional Schema:

MoSpo HallOfFame(hoFdriverId, hoFYear, hoFSeries, hoFImage,

hoFWins, hoFBestRaceName, hoFBestRaceDate)

primary key (hoFdriverId,hoFYear)

foreign key (hoFdriverId) references MoSpo Driver(driverId)

foreign key (hoFBestRaceName,hoFBestRaceDate) references

MoSpo Race(raceName,raceDate)

Your code must execute without error, assuming that all other tables have been set up by running

script a2-setup .sql.                                                                                                  [12 marks]

The data types you choose for the columns should be most appropriate for the data they will contain. You must also accommodate the following requirements:

(a)  For table and column names you must pick exactly the names used in the schema above (otherwise you will lose marks as tests will fail).

(b)  hoFYear is a 4-digit number representing a year between 1901 and 2155 (or 0000).

(c)  hoFSeries is one of the following strings:  BritishGT, Formula1, FormulaE, SuperGT. Please make sure you use the correct spelling. These column values, when ordered, should always appear in the order they have been listed above. Values for this attribute must not be missing.

(d)  hoFWins is a positive integer number and never larger than 99. The default is 0 but values can be missing.

(e)  hoFImage is a path to an image document which is a string never longer than 200 charac- ters. This value can be missing.

(f)  Equip any foreign key constraints with constraint names of your choosing.

(g)  Ensure that if a driver is deleted from the database their corresponding hall of fame entries are deleted automatically too.

(h)  Ensure that if a race is deleted from the database then foreign key values in hall of fame entries that reference it are automatically set to null.

Instructions for Question 2– 11

For each of the tasks specified below write one single SQL query, respectively, that solves the task. You can use nested queries (i.e. subselects and subqueries) wherever you like.

You must not CREATE any tables of any form and you must not use (nor declare) any stored procedures or functions in this section.

You must produce column headings as specifiedfor each query.

Double check that headings are exactly as specified.

It is important that your queries will work correctly with any data (according to the schema).    All references to time, when not explicit, are relative and refer to the time of running the query.

2.  The weight of drivers has been omitted from the MoSpo Driver table.  Without deleting and recreating the table, add a column driverWeight to the already created table that allows values to be missing.

Take into consideration that a driver’s weight is always in the range 0.0 to 99.9.     [4 marks]

3.  Change the postcode of the racing team Beechdean Motorsport to (the following string) HP135PN. [4 marks]

4.  Remove all drivers with last name Senna and first name Ayrton (whatever the capitalisation) from the database.  [4 marks]

5.  Find out how many racing teams are on the database.  The heading must be numberTeams. [3 marks]

6.  List all racing drivers (driver id, name and dob) whose last name begins with the same letter as their first name. The name of the driver should be given as a string consisting of the initial from the first name, followed by a blank, followed by their last name.  So a driver with first name Alan and last name Turing would be listed as A Turing. The headings must look like this: [6 marks]

driverId  driverName  driverDOB

7.  List for each racing team how many drivers they have associated with them. Only include teams with more than one driver. The headings must look like this:                                         [6 marks]

teamName  numberOfDriver

8.  For each race list the fastest lap time. The information provided should include race name, race date, lap time. No races must appear for which there is no proper such minimal time available. The headings must look like this: [6 marks]

raceName    raceDate    lapTime

9.  Given a race (name) and a year, ‘total pitstops’ is the total number of pitstops of all cars in the given race that year. For each race name compute the average of the number of ‘total pitstops’ based on the years we have data for. The headings must look like this:                         [6 marks]

raceName  avgStops

10.  A car (of a race entry) retires in a lap if the corresponding attribute lapInfoCompleted has value 0. Find out all the (different) makes of cars that had to retire in a race in the year 2018. The heading must look like this:        [6 marks]

carMake

11.  For each race, compute the highest number of pitstops any car had. Provide race name and date as well as the highest number of stops.  Races with no pitstops recorded at all should appear with a 0. The headings must look like this:                                  [6 marks]

raceName    raceDate    mostPitstops

Additional Instructions (Stored Procedures) Question 12

For developing answers to Questions 12 you can use any delimiter you like (e.g. $$).

Note that successfully declaring a stored procedure does not necessarily mean it runs without error.  You need to run and test your procedures to ensure that.  Strictly name the stored pro- cedure as indicated in the question.  You are not allowed to include any other stored routine definitions.

12.  Write a stored function totalRaceTime that, given a racing number, the name of a race, and the date of a race, returns the total race time for the car specified by the racing number in the given race.  If the given race does not exist, the routine should throw an appropriate error.  If the specified racing number did not take part in the existing race, the routine should throw an appropriate error.

In the case that not all required lap times for the (existing) car in the (existing) race are available either until race finish or retirement, the routine should throw an appropriate error.

If the (existing) race was not completed by the (participating) car in the race due to retirement but all lap times were available until retirement, the routine must not throw an error but return null.

The total race time should be returned as an integer denoting milliseconds. Note that this stored routine has three arguments and you must declare them in the order given above.     [12 marks]