CREATE TABLE MoSpo_RaceCourse (

raceCourseName VARCHAR(30) PRIMARY KEY,

raceCourseLocation VARCHAR(30),

raceCourseLength DEC(5,3) UNSIGNED

);

CREATE TABLE MoSpo_Race (

raceName VARCHAR(30),

raceDate DATE,

raceTime TIME,

raceVenue VARCHAR(30),

raceLaps TINYINT UNSIGNED,

PRIMARY KEY (raceName,raceDate),

CONSTRAINT MoSpo_Race_Location

FOREIGN KEY (raceVenue) REFERENCES MoSpo_RaceCourse(raceCourseName)

);

CREATE TABLE MoSpo_Lap (

lapNo TINYINT UNSIGNED,

lapRaceName VARCHAR(30),

lapRaceDate DATE,

CONSTRAINT MoSpo_Lap_Race

FOREIGN KEY (lapRaceName,lapRaceDate) REFERENCES MoSpo_Race(raceName, raceDate),

PRIMARY KEY(lapNo,lapRaceName,lapRaceDate)

);

CREATE TABLE MoSpo_RacingTeam (

teamName VARCHAR(30) primary key,

teamPostcode CHAR(9),

teamStreet VARCHAR(30),

teamHouseNo CHAR(4)

);

CREATE TABLE MoSpo_Driver(

driverId INTEGER UNSIGNED PRIMARY KEY,

driverDOB DATE,

driverLastname VARCHAR(30),

driverFirstname VARCHAR(30),

driverNationality VARCHAR(20),

driverTeam VARCHAR(30),

CONSTRAINT MoSpo_Driver_Team

FOREIGN KEY (driverTeam) REFERENCES MoSpo_RacingTeam(teamName)

);

CREATE TABLE MoSpo_Car(

carId INTEGER UNSIGNED PRIMARY KEY,

carMake VARCHAR(30),

carTeam VARCHAR(30),

CONSTRAINT MoSpo_Car_Team

FOREIGN KEY (carTeam) REFERENCES MoSpo_RacingTeam(teamName)

);

CREATE TABLE MoSpo_RaceEntry(

raceEntryNumber TINYINT UNSIGNED,

raceEntryRaceName VARCHAR(30),

raceEntryRaceDate DATE,

raceEntryDriverId INTEGER UNSIGNED NOT NULL,

raceEntryCarId INTEGER UNSIGNED NOT NULL,

raceEntryTyreType ENUM('soft','hard','wet','intermediate','medium','super-soft'),

PRIMARY KEY (raceEntryNumber,raceEntryRaceName,raceEntryRaceDate),

CONSTRAINT MoSpo_RaceEntry_Race

FOREIGN KEY (raceEntryRaceName,raceEntryRaceDate) REFERENCES MoSpo_Race(raceName,raceDate),

CONSTRAINT MoSpo_RaceEntry_Driver

FOREIGN KEY (raceEntryDriverId) REFERENCES MoSpo_Driver(driverId),

CONSTRAINT MoSpo_RaceEntry_Car

FOREIGN KEY (raceEntryCarId) REFERENCES MoSpo_Car(carId)

);

CREATE TABLE MoSpo_LapInfo (

lapInfoLapNo TINYINT UNSIGNED,

lapInfoRaceName VARCHAR(30),

lapInfoRaceDate DATE,

lapInfoRaceNumber TINYINT UNSIGNED,

lapInfoFuelConsumption DECIMAL(4,2),

lapInfoTime INT UNSIGNED, -- milliseconds

lapInfoCompleted TINYINT UNSIGNED NOT NULL DEFAULT 1,

PRIMARY KEY (lapInfoLapNo, lapInfoRaceName, lapInfoRaceDate, lapInfoRaceNumber),

CONSTRAINT MoSpo_LapInfo2_Lap

FOREIGN KEY (lapInfoLapNo, lapInfoRaceName, lapInfoRaceDate) REFERENCES MoSpo_Lap(lapNo,lapRaceName,lapRaceDate),

CONSTRAINT MoSpo_LapInfo2_Car

FOREIGN KEY (lapInfoRaceNumber, lapInfoRaceName, lapInfoRaceDate) REFERENCES MoSpo_RaceEntry(raceEntryNumber, raceEntryRaceName,raceEntryRaceDate)

);

CREATE TABLE MoSpo_PitStop (

pitstopLapNo TINYINT UNSIGNED,

pitstopRaceName VARCHAR(30),

pitstopRaceDate DATE,

pitstopRaceNumber TINYINT UNSIGNED,

pitstopDuration INT UNSIGNED, -- milliseconds

pitstopChangedParts SET('front_wing','rear_wing','nose','steering','suspension','shock_absorber','tyre'),

PRIMARY KEY (pitstopLapNo, pitstopRaceName, pitstopRaceDate, pitstopRaceNumber),

CONSTRAINT MoSpo_PitStop2_Lap

FOREIGN KEY (pitstopLapNo, pitstopRaceName, pitstopRaceDate) REFERENCES MoSpo_Lap(lapNo,lapRaceName,lapRaceDate),

CONSTRAINT MoSpo_PitStop2_Car

FOREIGN KEY (pitstopRaceNumber, pitstopRaceName, pitstopRaceDate) REFERENCES MoSpo_RaceEntry(raceEntryNumber, raceEntryRaceName,raceEntryRaceDate)

);

7. List for each racing team how many drivers they have associaned with them. Only inclade teams with more than one driver. The headings must look like this: 16 marks } teamaiane numberofDriver 8. For each race lit the factest lap time. The isformution provided shorald include race name, race date, lap time. No races must appear for which there is no proper sach minimal time available. The hoadings most look lake this: 16 markt 1 raceliane racebace laptine 9. Given a race (ane) and a year, 'Total pibiops' is the total nuanher of pibsops of all cas in the givea race that year. For each race name conpule the average of the mumber of tietal pitstogn" based on the yeurs we have duta for. The headings mast look like this: [6 marks] raceliame avgstops 10. A car (of a noce coury) retires in a lap if the corresponding anribute lap I nfocompleted has value 0. Find oat all the (iffferent) 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, conpone the highest number of pitstops any car had. Provide race nume and date as well as the highest aumber of seops. Races with no pitstops recoeded at all shurlld appear with a o. The headings must lonk like this: [6 marks) racellane raceDate mostpitatops Additional Instructions (Stored Procedures) Question 12 For developiag anvwers to Qoestions 12 you can use any delimiter you like (e.g. 55). Nose that successfully declaring a stored procedure does not necessarily mean it nans without enror. You need to run and test your procedures to ensure that. Strictly name the stoved procodure as indicated in the question. You are not allowed to include any other stoned routine definitions. 12. Write a stured function to a a hacet ime that, given a nuking nuaber, the name of a rave, and the date of a rase, neturns the total nuee time for the car specified by the racing number is the given race. If the givel race does not exis, the rudine should difon an appropriate entr. If the spocified racise sumber did not take part in the existing nace, the routine showld thros an appropriate enve. In the case that eot all tequired lap times for the (existing) cat in the (existing) race art availuble either antil race finiah of etirement, the routine should throw an appropriale enos. If the (existing) nace was not completed by the (participating) car in the nace due on retirenent but all lap times wete available until retirenent, the rootine must nat throw an erroe bet netam noll. The total race time should be returned as an integer denoting milliseconds. Note that thes stared