MYSQL assignment

PLEASE DON"T COPY AND PAST OTHER IRRELEVANT ANSWERS OR I WILL REPORT YOU AS A SPAM

-- TABLE DEFINITIONS ---

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) );

SAMPLE DATA

INSERT INTO MoSpo_RaceCourse VALUES ('Autodromo Nazionale Monza','Monza',3.6), ('Silverstone Circuit','Silverstone',3.667), ('Nrburgring','Nrburg',5.148), ('Hockenheimring','Hockenheim',2.842);

INSERT INTO MoSpo_Race VALUES ('German Grand Prix','2018-07-20','14:00:00','Hockenheimring',60), ('German Grand Prix','2017-07-07','14:00:00','Nrburgring',60), ('German Grand Prix','2016-07-06','14:00:00','Nrburgring',60), ('British Grand Prix','2017-06-30','13:00:00','Silverstone Circuit',52), ('British Grand Prix','2018-07-06','13:00:00','Silverstone Circuit',52), ('British Grand Prix','2016-07-05','13:00:00','Silverstone Circuit',52), ('British GT Championship','2018-06-01','15:00:00','Silverstone Circuit',52), ('British GT Championship','2017-06-02','15:00:00','Silverstone Circuit',52), ('Italian Grand Prix','2018-09-07','13:00:00','Autodromo Nazionale Monza',67);

INSERT INTO MoSpo_RacingTeam VALUES ('Mercedes AMG F1','NN137BD','Main Street','1'), ('Ferrari','I-41043','Via Abeto Inferiore','4'), ('Aston Martin Racing','OX163ER','Racing Avenue','100A'), ('Beechdean Motorsport','HP144NL','North Dean','35'), ('Williams Martini Racing','OX120DQ','Wantage Road','1'), ('Infiniti Red Bull Racing','MK78BJ','Bradbourne Drive','2B'), ('Geoff Steel Racing','DN214BD','Station Road',NULL), ('BMW DTM','D-566521','Brohlahtstrasse','60');

INSERT INTO MoSpo_Driver VALUES (10,'1987-07-03','Vettel','Sebastian','German', 'Ferrari'), (20,'1989-07-01','Ricciardo','Daniel','Australian', 'Infiniti Red Bull Racing'), (30,'1985-06-27','Rosberg','Nico','German', 'Mercedes AMG F1'), (40,'1985-01-07','Hamilton','Lewis','British', 'Mercedes AMG F1'), (50,'1983-10-15','Senna','Bruno','Brazilian', 'Ferrari'), (100,'1982-12-06','Wolff','Susie','British','Williams Martini Racing'), (103,'1960-05-01','Hamilton', 'Lewis','British', 'Beechdean Motorsport'), (1030,'1991-09-22','Hamilton', 'Mike','Irish', 'Beechdean Motorsport'), (60,'1993-01-26','Powell','Alice','British','Aston Martin Racing'), (22,'1992-01-02','Eaton','Abbie','British','Geoff Steel Racing'), (42,'1969-05-14','Schmitz','Sabine','German','BMW DTM');

INSERT INTO MoSpo_Car VALUES (1,'Renault','Infiniti Red Bull Racing'), (4,'Renault','Infiniti Red Bull Racing'), (9,'Mercedes','Mercedes AMG F1'), (92,'Mercedes','Mercedes AMG F1'), (93,'Mercedes','Mercedes AMG F1'), (22,'Porsche','Beechdean Motorsport'), (12,'BMW','Beechdean Motorsport'), (13,'BMW','BMW DTM'), (14,'BMW','BMW DTM'), (20,'Lotus','Geoff Steel Racing'), (100,'BMW','Geoff Steel Racing'), (63,'BMW','Geoff Steel Racing'), (99,'Aston Martin','Aston Martin Racing');

INSERT INTO MoSpo_RaceEntry VALUES (5,'German Grand Prix','2018-07-20',10,1,'soft'), (3,'German Grand Prix','2018-07-20',20,4,'soft'), (6,'German Grand Prix','2016-07-06',30,92,'soft'), (5,'German Grand Prix','2016-07-06',10,1,'super-soft'), (44,'German Grand Prix','2018-07-20',40,93,'super-soft'), (5,'British Grand Prix','2018-07-06',10,1,'medium'), (3,'British Grand Prix','2018-07-06',20,4,'medium'), (6,'British Grand Prix','2016-07-05',30,92,'medium'), (44,'British Grand Prix','2018-07-06',40,93,'medium'), (1,'British GT Championship','2018-06-01',103,22,'medium'), (1,'British GT Championship','2017-06-02',1030,22,'medium'), (3,'Italian Grand Prix','2018-09-07',20,4,'soft'), (5,'Italian Grand Prix','2018-09-07',10,1,'super-soft'), (1,'Italian Grand Prix','2018-09-07',103,12,'medium');

INSERT INTO MoSpo_Lap VALUES (1,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (2,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (3,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (4,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (5,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (6,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (7,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (8,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (9,'German Grand Prix','2018-07-20'); INSERT INTO MoSpo_Lap VALUES (10,'German Grand Prix','2018-07-20'); ............

INSERT INTO MoSpo_PitStop VALUES (18,'German Grand Prix','2018-07-20',5,'120010',NULL), (26,'German Grand Prix','2018-07-20',44,'126567','tyre,nose'), (42,'German Grand Prix','2018-07-20',44,'133345','front_wing'), (50,'German Grand Prix','2018-07-20',44,'120122',NULL);

Specification A race has a name and must take place at a single race course at a specific date and at a specific starting time. A race has a certain num- ber of laps (around the race course) and a total length in miles. No two races of the same name take place on the same day. A race course has a unique name, a location, and a length in miles. For each driver, we keep the name, consisting of first and last name, nationality, date of birth and a unique driver identifier. Drivers always belong to a single racing team. We need to find out which driver drove which car in which race. We also need to store in which place they ar- rived at the finish (non-finishers are recorded as arrived in 0-th place). Drivers drive at most one car in a race but not all drivers do. There are no driver swaps during a race but at different races different drivers may be driving the same car. The type of tyres on the car at the start of the race is recorded as well as the car's racing number for that particular race. Cars have a unique identifier and must belong to a racing team. The make of the engine of a car is relevant too. Not every car is necessarily driven in a race. A car may complete a lap of a race in which case the lap time and fuel consumption are to be recorded. A car may have a pit stop during a lap in a race, in which case the duration of the pit stop and the items that have been changed during the pit stop (tyres, front nose, etc.) are recorded. A car may retire in a lap of a race, in which case the reason for the retirement is to be recorded. In all these cases, we need to know in which lap of which race this occurred. Important aspects of a racing team are its name and the address of the team's headquarter consisting of postcode, street name, house num- ber. Racing teams may be entered on the database before any drivers or cars are assigned to them. For any given care make m and time period t, let RetirementsRatem(t) be the total number of retirements of cars of make m divided by the total number of cars of make m taking part in a race during time t. In case no car of make m participated in race r during period t this number is undefined (NULL). For example, let t be the year 2000 and m = Porsche. Assume that in the year 2000 there were two races with Porsche cars involved. In the first race 2 cars of that make raced and 1 had a retirement. In the second race 3 cars of that make raced with 0 retirements. Therefore, we get that RetirementsRate Porsche(t) = { = 0.2. For a period t, let AverageRetirementRate(t) be the average of re- tirement rates for period t across all makes m, i.e. the average of RetirementsRatem(t) ignoring undefined values, over all makes m. List for each car make m the retirement rate Retirement Ratem(t) where t is the current year. Only select car makes m with a retirement rate above the average retirement rate across all makes for the same pe- riod t, i.e. where Retirement Ratem(t) > AverageRetirement Rate(t). The headings must look like this: carMake retirementRate Specification A race has a name and must take place at a single race course at a specific date and at a specific starting time. A race has a certain num- ber of laps (around the race course) and a total length in miles. No two races of the same name take place on the same day. A race course has a unique name, a location, and a length in miles. For each driver, we keep the name, consisting of first and last name, nationality, date of birth and a unique driver identifier. Drivers always belong to a single racing team. We need to find out which driver drove which car in which race. We also need to store in which place they ar- rived at the finish (non-finishers are recorded as arrived in 0-th place). Drivers drive at most one car in a race but not all drivers do. There are no driver swaps during a race but at different races different drivers may be driving the same car. The type of tyres on the car at the start of the race is recorded as well as the car's racing number for that particular race. Cars have a unique identifier and must belong to a racing team. The make of the engine of a car is relevant too. Not every car is necessarily driven in a race. A car may complete a lap of a race in which case the lap time and fuel consumption are to be recorded. A car may have a pit stop during a lap in a race, in which case the duration of the pit stop and the items that have been changed during the pit stop (tyres, front nose, etc.) are recorded. A car may retire in a lap of a race, in which case the reason for the retirement is to be recorded. In all these cases, we need to know in which lap of which race this occurred. Important aspects of a racing team are its name and the address of the team's headquarter consisting of postcode, street name, house num- ber. Racing teams may be entered on the database before any drivers or cars are assigned to them. For any given care make m and time period t, let RetirementsRatem(t) be the total number of retirements of cars of make m divided by the total number of cars of make m taking part in a race during time t. In case no car of make m participated in race r during period t this number is undefined (NULL). For example, let t be the year 2000 and m = Porsche. Assume that in the year 2000 there were two races with Porsche cars involved. In the first race 2 cars of that make raced and 1 had a retirement. In the second race 3 cars of that make raced with 0 retirements. Therefore, we get that RetirementsRate Porsche(t) = { = 0.2. For a period t, let AverageRetirementRate(t) be the average of re- tirement rates for period t across all makes m, i.e. the average of RetirementsRatem(t) ignoring undefined values, over all makes m. List for each car make m the retirement rate Retirement Ratem(t) where t is the current year. Only select car makes m with a retirement rate above the average retirement rate across all makes for the same pe- riod t, i.e. where Retirement Ratem(t) > AverageRetirement Rate(t). The headings must look like this: carMake retirementRate