· 9 years ago · Oct 08, 2016, 07:28 PM
1-- -----------------------------------------------------
2-- Table `ville`
3-- -----------------------------------------------------
4DROP TABLE IF EXISTS `Villes`;
5
6CREATE TABLE IF NOT EXISTS `Villes` (
7 `id` INT AUTO_INCREMENT PRIMARY KEY,
8 `nom` VARCHAR(45) NULL,
9 `province` VARCHAR(45) NULL);
10
11
12-- -----------------------------------------------------
13-- Table `adresse`
14-- -----------------------------------------------------
15DROP TABLE IF EXISTS `Adresses`;
16
17CREATE TABLE IF NOT EXISTS `Adresses` (
18 `id` INT NOT NULL PRIMARY KEY,
19 `ville_id` INT NOT NULL,
20 `adresse` VARCHAR(45) NULL,
21 `appartement` VARCHAR(45) NULL,
22 `telephone` INT NULL,
23 `codepostal` VARCHAR(6) NULL,
24 FOREIGN KEY(ville_id) REFERENCES villes(id));
25
26
27-- -----------------------------------------------------
28-- Table `evenement`
29-- -----------------------------------------------------
30DROP TABLE IF EXISTS `Evenements`;
31
32CREATE TABLE IF NOT EXISTS `Evenements` (
33 `id` INT AUTO_INCREMENT PRIMARY KEY,
34 `ville_id` INT NOT NULL,
35 `dateDebut` DATE NULL,
36 `dateFin` DATE NULL,
37 `organisateur` VARCHAR(45) NULL,
38 FOREIGN KEY(ville_id) REFERENCES villes(id));
39
40
41-- -----------------------------------------------------
42-- Table `cavalier`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `Cavaliers`;
45
46CREATE TABLE IF NOT EXISTS `Cavaliers` (
47 `id` INT AUTO_INCREMENT PRIMARY KEY,
48 `nom` VARCHAR(45) NULL,
49 `dateNaissance` DATE NULL,
50 `email` VARCHAR(45) NULL,
51 `sexe` VARCHAR(45) NULL,
52 `adresse_id` INT NOT NULL,
53 FOREIGN KEY(adresse_id) REFERENCES adresses(id));
54
55-- -----------------------------------------------------
56-- Table `adhesion`
57-- -----------------------------------------------------
58DROP TABLE IF EXISTS `Adhesions`;
59
60CREATE TABLE IF NOT EXISTS `Adhesions` (
61 `id` INT AUTO_INCREMENT PRIMARY KEY,
62 `type` VARCHAR(45) NULL,
63 `date` DATE NULL,
64 `evenement_id` INT NULL,
65 FOREIGN KEY(evenement_id) REFERENCES evenements(id));
66
67-- -----------------------------------------------------
68-- Table `cavalier_has_adhesion`
69-- -----------------------------------------------------
70DROP TABLE IF EXISTS `Cavaliers_has_adhesions`;
71
72CREATE TABLE IF NOT EXISTS `Cavaliers_has_adhesions` (
73 `id` INT AUTO_INCREMENT PRIMARY KEY,
74 `cavalier_id` INT NOT NULL,
75 `adhesion_id` INT NOT NULL,
76 FOREIGN KEY (cavalier_id) REFERENCES cavaliers(id),
77 FOREIGN KEY(adhesion_id) REFERENCES adhesions(id));
78
79-- -----------------------------------------------------
80-- Table `jeux`
81-- -----------------------------------------------------
82DROP TABLE IF EXISTS `Jeux`;
83
84CREATE TABLE IF NOT EXISTS `Jeux` (
85 `id` INT AUTO_INCREMENT PRIMARY KEY,
86 `nom` VARCHAR(45) NULL,
87 `pointage` VARCHAR(45) NULL,
88 `obligatoire` TINYINT(1) NULL,
89 `description` VARCHAR(45) NULL,
90 `classe` VARCHAR(45) NULL,
91 `equipe` TINYINT(1) NULL);
92
93
94-- -----------------------------------------------------
95-- Table `jeux_evenement`
96-- -----------------------------------------------------
97DROP TABLE IF EXISTS `Jeux_evenements`;
98
99CREATE TABLE IF NOT EXISTS `Jeux_evenements` (
100 `id` INT AUTO_INCREMENT PRIMARY KEY,
101 `jeu_id` INT NOT NULL,
102 `evenement_id` INT NOT NULL,
103 `date` DATE NULL,
104 `bourseAjoutee` REAL NULL,
105 `coutInscription` INT NULL,
106 FOREIGN KEY(jeu_id) REFERENCES jeux(id)
107 FOREIGN KEY(evenement_id) REFERENCES evenements(id));
108
109-- -----------------------------------------------------
110-- Table `Chevaux`
111-- -----------------------------------------------------
112DROP TABLE IF EXISTS `Chevaux`;
113
114CREATE TABLE IF NOT EXISTS `Chevaux` (
115 `id` INT AUTO_INCREMENT PRIMARY KEY,
116 `nom` VARCHAR(45) NOT NULL,
117 `dateNaissance` DATE NULL);
118
119
120-- -----------------------------------------------------
121-- Table `cavalier_cheval`
122-- -----------------------------------------------------
123DROP TABLE IF EXISTS `Cavaliers_chevaux`;
124
125CREATE TABLE IF NOT EXISTS `Cavaliers_chevaux` (
126 `id` INT AUTO_INCREMENT PRIMARY KEY,
127 `cavalier_id` INT NOT NULL,
128 `cheval_id` INT NOT NULL,
129 FOREIGN KEY(cavalier_id) REFERENCES cavaliers(id)
130 FOREIGN KEY(cheval_id) REFERENCES chevaux(id));
131
132-- -----------------------------------------------------
133-- Table `position`
134-- -----------------------------------------------------
135DROP TABLE IF EXISTS `Positions`;
136
137CREATE TABLE IF NOT EXISTS `Positions` (
138 `id` INT AUTO_INCREMENT PRIMARY KEY,
139 `cavalier_cheval_id` INT NOT NULL,
140 `evenement_id` INT NOT NULL,
141 `position` INT NULL,
142 FOREIGN KEY(cavalier_cheval_id) REFERENCES cavaliers_chevaux(id)
143 FOREIGN KEY(evenement_id) REFERENCES evenements(id));
144
145
146-- -----------------------------------------------------
147-- Table `cavalier_cheval_inscrit`
148-- -----------------------------------------------------
149DROP TABLE IF EXISTS `Cavaliers_chevaux_inscrits`;
150
151CREATE TABLE IF NOT EXISTS `Cavalier_cheval_inscrit` (
152 `id` INT AUTO_INCREMENT PRIMARY KEY,
153 `cavalier_cheval_id` INT NOT NULL,
154 `jeu_evenement_id` INT NOT NULL,
155 `exhibition` INT(1) NULL,
156 FOREIGN KEY(cavalier_cheval_id) REFERENCES cavaliers_chevaux(id),
157 FOREIGN KEY(jeu_evenement_id) REFERENCES jeux_evenements(id));
158
159
160-- -----------------------------------------------------
161-- Table `resultat`
162-- -----------------------------------------------------
163DROP TABLE IF EXISTS `Resultats`;
164
165CREATE TABLE IF NOT EXISTS `Resultats` (
166 `id` INT AUTO_INCREMENT PRIMARY KEY,
167 `cavalier_cheval_inscrit_id` INT NOT NULL,
168 `temps` REAL NULL,
169 `penalite` INT NULL,
170 FOREIGN KEY(cavalier_cheval_inscrit_id) REFERENCES cavaliers_chevaux_inscrits(id));
171
172
173-- -----------------------------------------------------
174-- Table `equipe`
175-- -----------------------------------------------------
176DROP TABLE IF EXISTS `Equipes`;
177
178CREATE TABLE IF NOT EXISTS `Equipes` (
179 `id` INT AUTO_INCREMENT PRIMARY KEY,
180 `numeroEquipe` INT NULL,
181 `cavalier_cheval_inscrit_id` INT NOT NULL,
182 FOREIGN KEY(cavalier_cheval_inscrit_id) REFERENCES cavaliers_chevaux_inscrits(id));
183
184
185-- -----------------------------------------------------
186-- Table `Jeux3D`
187-- -----------------------------------------------------
188DROP TABLE IF EXISTS `Jeux3D`;
189
190CREATE TABLE IF NOT EXISTS `Jeux3D` (
191 `id` INT AUTO_INCREMENT PRIMARY KEY,
192 `jeu_id` INT NOT NULL,
193 `temps1_2` REAL NULL,
194 `temps1_3` REAL NULL,
195 `temps2D_1_2` REAL NULL,
196 FOREIGN KEY(jeu_id) REFERENCES jeux(id));
197
198
199-- -----------------------------------------------------
200-- Table `pointage`
201-- -----------------------------------------------------
202DROP TABLE IF EXISTS `Pointages`;
203
204CREATE TABLE IF NOT EXISTS `Pointages` (
205 `id` INT AUTO_INCREMENT PRIMARY KEY,
206 `points` INT NULL,
207 `cavalier_cheval_inscrit_id` INT NOT NULL,
208 FOREIGN KEY(cavalier_cheval_inscrit_id) REFERENCES cavaliers_chevaux_inscrits(id));