CREATE DATABASE IF NOT EXISTS RecordsOlympiques;
USE RecordsOlympiques;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Records;
DROP TABLE IF EXISTS Athletes;
DROP TABLE IF EXISTS Sports;
DROP TABLE IF EXISTS Nationalites;
SET FOREIGN_KEY_CHECKS = 1;


CREATE TABLE Nationalites (
    idNationalite INTEGER PRIMARY KEY,
    nomPays VARCHAR(80)
);

CREATE TABLE Sports (
    idSport INTEGER PRIMARY KEY,
    sport VARCHAR(80),
    epreuve VARCHAR(80)
);

CREATE TABLE Athletes (
    idAthlete INTEGER PRIMARY KEY,
    nom VARCHAR(80),
    prenom VARCHAR(80),
    sexe CHAR(1),
    dateNaissance DATE,
    idNationalite INTEGER,
    CONSTRAINT fk_athlete_nationalite
        FOREIGN KEY (idNationalite) REFERENCES Nationalites(idNationalite)
);

CREATE TABLE Records (
    idAthlete INTEGER,
    idSport INTEGER,
    record VARCHAR(40),
    dateRecord DATE,
    PRIMARY KEY (idAthlete, idSport, record, dateRecord),
    CONSTRAINT fk_record_athlete
        FOREIGN KEY (idAthlete) REFERENCES Athletes(idAthlete),
    CONSTRAINT fk_record_sport
        FOREIGN KEY (idSport) REFERENCES Sports(idSport)
);


INSERT INTO Nationalites VALUES (1, 'France');
INSERT INTO Nationalites VALUES (2, 'USA');
INSERT INTO Nationalites VALUES (3, 'Chine');
INSERT INTO Nationalites VALUES (4, 'Russie');
INSERT INTO Nationalites VALUES (5, 'Japon');
INSERT INTO Nationalites VALUES (6, 'Jamaïque');
INSERT INTO Nationalites VALUES (7, 'Royaume Uni');
INSERT INTO Nationalites VALUES (8, 'Brésil');
INSERT INTO Nationalites VALUES (9, 'Canada');
INSERT INTO Nationalites VALUES (10, 'Ethiopie');
INSERT INTO Nationalites VALUES (11, 'Roumanie');
INSERT INTO Nationalites VALUES (12, 'Australie');
INSERT INTO Nationalites VALUES (13, 'Afrique du sud');
INSERT INTO Nationalites VALUES (14, 'Pays-Bas');
INSERT INTO Nationalites VALUES (15, 'Kenya');

INSERT INTO Sports VALUES(1, 'Athletisme', '100 m');
INSERT INTO Sports VALUES(2, 'Athletisme', '200 m');
INSERT INTO Sports VALUES(3, 'Athletisme', 'Saut à la perche');
INSERT INTO Sports VALUES(4, 'Athletisme', '5000 m');
INSERT INTO Sports VALUES(5, 'Athletisme', '10000 m');
INSERT INTO Sports VALUES(6, 'Natation', '50 m nage libre');
INSERT INTO Sports VALUES(7, 'Natation', '100 m nage libre');
INSERT INTO Sports VALUES(8, 'Natation', '100 m papillon');
INSERT INTO Sports VALUES(9, 'Natation', '100 m brasse');
INSERT INTO Sports VALUES(10, 'Athletisme', 'Marathon');
INSERT INTO Sports VALUES(11, 'Athletisme', '110 m haies');
INSERT INTO Sports VALUES(12, 'Athletisme', 'Saut en longueur');
INSERT INTO Sports VALUES(13, 'Natation', '200 m nage libre');
INSERT INTO Sports VALUES(14, 'Natation', '400 m nage libre');
INSERT INTO Sports VALUES(15, 'Natation', '100 m dos');
INSERT INTO Sports VALUES(16, 'Natation', '200 m 4 nages');
INSERT INTO Sports VALUES(17, 'Natation', '400 m 4 nages');
INSERT INTO Sports VALUES(18, 'Natation', '200 m papillon');

INSERT INTO Athletes VALUES (1, 'Bolt', 'Usain', 'M', '1986-08-21', 6);
INSERT INTO Athletes VALUES (2, 'Lavillenie', 'Renaud', 'M', '1986-09-18', 1);
INSERT INTO Athletes VALUES (3, 'Bekele', 'Kenenisa', 'M', '1982-06-13', 10);
INSERT INTO Athletes VALUES (4, 'Griffith-Joyner', 'Florence', 'F', '1959-12-21', 2);
INSERT INTO Athletes VALUES (5, 'Isinbayeva', 'Yelena', 'F', '1982-06-03', 4);
INSERT INTO Athletes VALUES (6, 'Szabó', 'Gabriela', 'F', '1975-11-14', 11);
INSERT INTO Athletes VALUES (7, 'Dibaba', 'Tirunesh', 'F', '1985-10-01', 10);
INSERT INTO Athletes VALUES (8, 'Cielo', 'César', 'M', '1987-01-10', 8);
INSERT INTO Athletes VALUES (9, 'Sullivan', 'Eamon', 'M', '1985-08-30', 12);
INSERT INTO Athletes VALUES (10, 'Phelps', 'Michael', 'M', '1985-06-30', 2);
INSERT INTO Athletes VALUES (11, 'van der Burgh', 'Cameron', 'M', '1988-05-25', 13);
INSERT INTO Athletes VALUES (12, 'Kromowidjojo', 'Ranomi', 'F', '1990-08-20', 14);
INSERT INTO Athletes VALUES (13, 'Vollmer', 'Dana', 'F', '1987-11-13', 2);
INSERT INTO Athletes VALUES (14, 'Jones', 'Leisel', 'F', '1985-08-30', 12);
INSERT INTO Athletes VALUES (15, 'Wanjiru', 'Samuel', 'M', '1986-11-10', 15);
INSERT INTO Athletes VALUES (16, 'Xiang', 'Liu', 'M', '1983-07-13', 3);
INSERT INTO Athletes VALUES (17, 'Beamon', 'Bob', 'M', '1946-08-29', 2);
INSERT INTO Athletes VALUES (18, 'Gelana', 'Tiki', 'F', '1987-10-22', 10);
INSERT INTO Athletes VALUES (19, 'Joyner-Kersee', 'Jackie', 'F', '1962-03-03', 2);
INSERT INTO Athletes VALUES (20, 'Yang', 'Sun', 'M', '1991-12-01', 3);
INSERT INTO Athletes VALUES (21, 'Grevers', 'Matthew', 'M', '1985-03-26', 2);
INSERT INTO Athletes VALUES (22, 'Schmitt', 'Allison', 'F', '1990-06-07', 2);
INSERT INTO Athletes VALUES (23, 'Muffat', 'Camille', 'F', '1989-10-28', 1);
INSERT INTO Athletes VALUES (24, 'Franklin', 'Missy', 'F', '1995-05-10', 2);
INSERT INTO Athletes VALUES (25, 'Shiwen', 'Ye', 'F', '1996-03-01', 3);

INSERT INTO Records VALUES(1, 1, '9 s 63', '2012-08-05');
INSERT INTO Records VALUES(1, 2, '19 s 30', '2008-08-20');
INSERT INTO Records VALUES(2, 3, '5,97 m', '2012-08-10');
INSERT INTO Records VALUES(3, 4, '12 m 57 s 82', '2008-08-23');
INSERT INTO Records VALUES(3, 5, '27 m 1 s 17', '2008-08-17');
INSERT INTO Records VALUES(4, 1, '10 s 62', '1988-09-24');
INSERT INTO Records VALUES(4, 2, '21 s 34', '1988-09-29');
INSERT INTO Records VALUES(5, 3, '5,05 m', '2008-08-18');
INSERT INTO Records VALUES(6, 4, '14 m 40 s 79', '2000-09-25');
INSERT INTO Records VALUES(7, 5, '29 m 54 s 66', '2008-08-15');
INSERT INTO Records VALUES(8, 6, '21 s 30', '2008-08-16');
INSERT INTO Records VALUES(9, 7, '47 s 05', '2008-08-13');
INSERT INTO Records VALUES(10, 8, '50 s 58', '2008-08-16');
INSERT INTO Records VALUES(11, 9, '58 s 46', '2012-07-29');
INSERT INTO Records VALUES(12, 6, '24 s 05', '2012-08-04');
INSERT INTO Records VALUES(12, 7, '53 s 00', '2012-08-02');
INSERT INTO Records VALUES(13, 8, '55 s 98', '2012-07-29');
INSERT INTO Records VALUES(14, 9, '1 m 5 s 17', '2008-08-12');
INSERT INTO Records VALUES(15, 10, '2 h 6 m 32 s', '2008-08-24');
INSERT INTO Records VALUES(16, 11, '12 s 91', '2004-08-27');
INSERT INTO Records VALUES(17, 12, '8,90 m', '1968-10-15');
INSERT INTO Records VALUES(18, 10, '2 h 23 m 07', '2012-08-05');
INSERT INTO Records VALUES(19, 12, '7,40 m', '1988-09-29');
INSERT INTO Records VALUES(10, 13, '1 m 42 s 96', '2008-08-12');
INSERT INTO Records VALUES(20, 14, '3 m 40 s 14', '2012-07-28');
INSERT INTO Records VALUES(21, 15, '52 s 16', '2012-07-30');
INSERT INTO Records VALUES(10, 16, '1 m 54 s 23', '2008-08-15');
INSERT INTO Records VALUES(22, 13, '1 m 53 s 61', '2012-07-31');
INSERT INTO Records VALUES(23, 14, '4 min 1 s 45', '2012-07-29');
INSERT INTO Records VALUES(24, 15, '58 s 33', '2012-07-30');
INSERT INTO Records VALUES(25, 16, '2 m 7 s 57', '2012-07-31');

CREATE DATABASE IF NOT EXISTS Pizzerias;
USE Pizzerias;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Livreur;
DROP TABLE IF EXISTS Vehicule;
DROP TABLE IF EXISTS Livraison;
DROP TABLE IF EXISTS Pizzeria;
DROP TABLE IF EXISTS Modele;
DROP TABLE IF EXISTS Ville;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE Ville (
    nomVille VARCHAR(40) PRIMARY KEY,
    codeCommune INTEGER,
    nombreHabitants INTEGER
);

CREATE TABLE Modele (
    nomModele VARCHAR(10) PRIMARY KEY,
    marque VARCHAR(10),
    puissance INTEGER
);

CREATE TABLE Pizzeria (
    idPizzeria INTEGER PRIMARY KEY,
    nomPizzeria VARCHAR(40),
    nombreLivreursMax INTEGER,
    nomVille VARCHAR(40),
    CONSTRAINT fk_pizzeria_ville
        FOREIGN KEY (nomVille) REFERENCES Ville(nomVille)
);

CREATE TABLE Livraison (
    idPizzeria INTEGER,
    villeDesservie VARCHAR(40),
    PRIMARY KEY (idPizzeria, villeDesservie),
    CONSTRAINT fk_livraison_pizzeria
        FOREIGN KEY (idPizzeria) REFERENCES Pizzeria(idPizzeria),
    CONSTRAINT fk_livraison_ville
        FOREIGN KEY (villeDesservie) REFERENCES Ville(nomVille)
);

CREATE TABLE Livreur (
    idLivreur INTEGER,
    idPizzeria INTEGER,
    nom VARCHAR(80),
    prenom VARCHAR(80),
    ville VARCHAR(40),
    PRIMARY KEY (idLivreur, idPizzeria),
    CONSTRAINT fk_livreur_pizzeria
        FOREIGN KEY (idPizzeria) REFERENCES Pizzeria(idPizzeria)
);

CREATE TABLE Vehicule (
    idVehicule INTEGER,
    idPizzeria INTEGER,
    capacite INTEGER,
    modele VARCHAR(10),
    PRIMARY KEY (idVehicule, idPizzeria),
    CONSTRAINT fk_vehicule_pizzeria
        FOREIGN KEY (idPizzeria) REFERENCES Pizzeria(idPizzeria),
    CONSTRAINT fk_vehicule_modele
        FOREIGN KEY (modele) REFERENCES Modele(nomModele)
);


insert into Ville values ('Nantes', 44109, 282047);
insert into Ville values ('Rezé', 44143, 38214);
insert into Ville values ('Saint-Sébastien-sur-Loire', 44190, 24726);
insert into Ville values ('Saint-Herblain', 44162, 43119);
insert into Ville values ('Sainte-Luce-sur-Loire', 44172, 11679);
insert into Ville values ('Orvault', 44114, 24332);

insert into Modele values ('Mob50', 'ChinaMody', 4);
insert into Modele values ('Mot125', 'KCM', 15);
insert into Modele values ('Voit3p', 'Toyoda', 64);

insert into Pizzeria values (1, 'Speed Turtle Pizza', 6, 'Nantes');
insert into Pizzeria values (2, 'Slop Pizza', 2, 'Nantes');
insert into Pizzeria values (3, 'Pizza Express', 3, 'Nantes');
insert into Pizzeria values (4, 'Tradition Pizza', 2, 'Rezé');
insert into Pizzeria values (5, 'La Pizza en Carton', 4, 'Saint-Sébastien-sur-Loire');
insert into Pizzeria values (6, 'Pizza Pizza', 2, 'Saint-Herblain');
insert into Pizzeria values (7, 'Supra Pizza', 1, 'Sainte-Luce-sur-Loire');
insert into Pizzeria values (8, 'Pizza hot', 3, 'Orvault');

insert into Livraison values (1, 'Nantes');
insert into Livraison values (1, 'Rezé');
insert into Livraison values (2, 'Nantes');
insert into Livraison values (3, 'Nantes');
insert into Livraison values (3, 'Saint-Sébastien-sur-Loire');
insert into Livraison values (4, 'Rezé');
insert into Livraison values (4, 'Nantes');
insert into Livraison values (5, 'Saint-Sébastien-sur-Loire');
insert into Livraison values (6, 'Saint-Herblain');
insert into Livraison values (6, 'Orvault');
insert into Livraison values (7, 'Sainte-Luce-sur-Loire');
insert into Livraison values (8, 'Orvault');
insert into Livraison values (8, 'Nantes');
insert into Livraison values (8, 'Saint-Herblain');

insert into Livreur values (1, 1, 'Pierre', 'Martin', 'Nantes');
insert into Livreur values (2, 1, 'Jacques', 'Machin', 'Nantes');
insert into Livreur values (3, 1, 'Philippe', 'Truc', 'Rezé');
insert into Livreur values (4, 1, 'Martin', 'Bidulle', 'Saint-Sébastien-sur-Loire');
insert into Livreur values (5, 2, 'Yohann', 'Moreau', 'Nantes');
insert into Livreur values (6, 2, 'Fabrice', 'Huet', 'Nantes');
insert into Livreur values (7, 3, 'Fabien', 'Malin', 'Nantes');
insert into Livreur values (8, 3, 'Nicolas', 'Pchit', 'Nantes');
insert into Livreur values (9, 3, 'Adrien', 'Bouguat', 'Orvault');
insert into Livreur values (10, 4, 'Emmanuel', 'Filou', 'Nantes');
insert into Livreur values (11, 5, 'Béatrice', 'Gentil', 'Nantes');
insert into Livreur values (12, 5, 'Chantal', 'Manif', 'Saint-Sébastien-sur-Loire');
insert into Livreur values (13, 5, 'Solenn', 'Nouveau', 'Saint-Sébastien-sur-Loire');
insert into Livreur values (14, 6, 'Grégory', 'Rock', 'Saint-Herblain');
insert into Livreur values (15, 7, 'Fabien', 'Boby', 'Sainte-Luce-sur-Loire');
insert into Livreur values (16, 8, 'Pierre', 'Perno', 'Orvault');
insert into Livreur values (17, 8, 'Christine', 'Petit', 'Orvault');
insert into Livreur values (18, 8, 'Abdel', 'Voisin', 'Saint-Herblain');

insert into Vehicule values (1, 1, 3, 'Mob50');
insert into Vehicule values (2, 1, 3, 'Mob50');
insert into Vehicule values (3, 1, 5, 'Mot125');
insert into Vehicule values (4, 1, 10, 'Voit3p');
insert into Vehicule values (5, 2, 3, 'Mob50');
insert into Vehicule values (6, 2, 4, 'Mot125');
insert into Vehicule values (7, 3, 3, 'Mob50');
insert into Vehicule values (8, 3, 2, 'Mob50');
insert into Vehicule values (9, 4, 3, 'Mob50');
insert into Vehicule values (10, 5, 3, 'Mob50');
insert into Vehicule values (11, 5, 2, 'Mob50');
insert into Vehicule values (12, 5, 7, 'Mot125');
insert into Vehicule values (13, 6, 3, 'Mob50');
insert into Vehicule values (14, 6, 3, 'Mob50');
insert into Vehicule values (15, 7, 10, 'Voit3p');
insert into Vehicule values (16, 8, 3, 'Mob50');
insert into Vehicule values (17, 8, 3, 'Mob50');
insert into Vehicule values (18, 8, 3, 'Mob50');