· 7 years ago · Dec 10, 2018, 01:46 PM
1/*!40101 SET NAMES utf8 */;
2
3/*!40101 SET SQL_MODE=''*/;
4
5/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
6/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
7
8
9CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tdf`/*!40100 DEFAULT CHARACTER SET utf8 */;
10
11USE `tdf`;
12
13DROP TABLE IF EXISTS `equipe`;
14
15CREATE TABLE `equipe` (
16 `CodeEquipe` char(3) NOT NULL,
17 `NomEquipe` varchar(32) NOT NULL,
18 `Directeur Sportif` varchar(64) NOT NULL,
19 PRIMARY KEY (`CodeEquipe`)
20) ENGINE=InnoDB DEFAULT CHARSET=utf8;
21
22insert into `equipe`(`CodeEquipe`, `NomEquipe`, `Directeur Sportif`) values ('BAN', 'BANESTO', 'Eusebio UNZUE'), ('COF', 'COFIDIS', 'Cyrille GUIMARD'), ('CSO', 'CASINO', 'Vincent LAVENU'), ('FDJ', 'LA FRANCAISE DES JEUX', 'Marc MADIOT'), ('FES', 'FESTINA', 'Bruno ROUSSEL'), ('GAN', 'GAN', 'Roger LEGEAY'), ('ONC', 'O.N.C.E.', 'Manolo SAIZ'), ('TEL', 'TELEKOM', 'Walter GODEFROOT'), ('SEX', '&THECITY', 'Nino HAMEL');
23
24DROP TABLE IF EXISTS `coureur`;
25
26CREATE TABLE `coureur` (
27 `NumeroCoureur` int(5) NOT NULL,
28 `NomCoureur` varchar(32) NOT NULL,
29 `CodeEquipe` char(3) NOT NULL,
30 `CodePays` char(3) NOT NULL,
31 PRIMARY KEY (`NumeroCoureur`)
32) ENGINE=InnoDB DEFAULT CHARSET=utf8;
33
34insert into `coureur`(`NumeroCoureur`, `NomCoureur`, `CodeEquipe`, `CodePays`) values (8, 'ULLRICH Jan', 'TEL', 'ALL'), (31, 'JALABERT Laurent', 'ONC', 'FRA'), (61, 'ROMINGER Tony', 'COF', 'SUI'), (91, 'BOARDMAN Chris', 'GAN', 'G-B'), (114, 'CIPOLLINI Mario', 'SAE', 'ITA'), (151, 'OLANO Abraham', 'BAN', 'ESP');
35
36DROP TABLE IF EXISTS `etape`;
37
38CREATE TABLE `etape` (
39 `NumeroEtape` int(5) NOT NULL,
40 `DateEtape` date NOT NULL,
41 `VilleDepart` varchar(32) NOT NULL,
42 `VilleArrivee` varchar(32) NOT NULL,
43 `NbKm` int(5) NOT NULL,
44 PRIMARY KEY (`NumeroEtape`)
45) ENGINE=InnoDB DEFAULT CHARSET=utf8;
46
47insert into `etape`(`NumeroEtape`, `DateEtape`, `VilleDepart`, `VilleArrivee`, `NbKm`) values (1, '1997-07-06', 'ROUEN', 'FORGES-LES-EAUX', 192), (2, '1997-07-07', 'ST-VALERY-EN-CAUX', 'VIRE', 262), (3, '1997-07-08', 'VIRE', 'PLUMELEC', 224);
48
49DROP TABLE IF EXISTS `temps`;
50
51CREATE TABLE `temps` (
52 `NumeroCoureur` int NOT NULL,
53 `NumeroEtape` int NOT NULL,
54 `TempsRealise` char(8) NOT NULL,
55 PRIMARY KEY (`NumeroCoureur`, `NumeroEtape`)
56) ENGINE=InnoDB DEFAULT CHARSET=utf8;
57
58insert into `temps`(`NumeroCoureur`, `NumeroEtape`, `TempsRealise`) values (8, 3, '04:54:33'), (8, 1, '04:48:21'), (8, 2, '06:27:47'), (31, 3, '04:54:33'), (31, 1, '04:48:21'), (31, 2, '06:27:47'), (61, 1, '04:54:33'), (61, 2, '04:48:21'), (91, 3, '06:27:47'), (91, 1, '04:54:33'), (91, 2, '04:48:21'), (114, 3, '06:27:47'), (114, 1, '04:54:33'), (114, 2, '04:48:21'), (151, 3, '06:27:47'), (151, 1, '04:48:21'), (151, 2, '06:27:47');
59
60DROP TABLE IF EXISTS `pays`;
61
62CREATE TABLE `pays` (
63 `CodePays` char(3) NOT NULL,
64 `NomPays` varchar(32) NOT NULL,
65 PRIMARY KEY (`CodePays`)
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
68insert into `pays`(`CodePays`, `NomPays`) values ('ALL', 'ALLEMAGNE'), ('AUT', 'AUTRICHE'), ('BEL', 'BELGIQUE'), ('DAN', 'DANEMARK'), ('ESP', 'ESPAGNE'), ('FRA', 'FRANCE'), ('G-B', 'GRANDE BRETAGNE'), ('ITA', 'ITALIE'), ('P-B', 'PAYS-BAS'), ('RUS', 'RUSSIE'), ('SUI', 'SUISSE');
69
70/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
71/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;