· 8 years ago · Apr 09, 2018, 05:56 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema TP3
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema TP3
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `TP3` DEFAULT CHARACTER SET utf8 ;
15USE `TP3` ;
16
17-- -----------------------------------------------------
18-- Table `TP3`.`tblVille`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `TP3`.`tblVille` (
21 `idVille` INT NOT NULL AUTO_INCREMENT,
22 `nomVille` VARCHAR(50) NOT NULL,
23 PRIMARY KEY (`idVille`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `TP3`.`tblAbonne`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonne` (
31 `idAbonne` INT NOT NULL AUTO_INCREMENT,
32 `nomAbonne` VARCHAR(50) NOT NULL,
33 `prenomAbonne` VARCHAR(25) NULL DEFAULT NULL,
34 `codePostal` VARCHAR(7) NOT NULL,
35 `telephone` VARCHAR(15) NOT NULL,
36 `courriel` VARCHAR(50) NOT NULL,
37 `typeAbonne` CHAR(1) NOT NULL,
38 `idVille` INT NOT NULL,
39 PRIMARY KEY (`idAbonne`),
40 INDEX `FK_tblAbonne_idVille` (`idVille` ASC),
41 CONSTRAINT `FK_tblAbonne_idVille`
42 FOREIGN KEY (`idVille`)
43 REFERENCES `TP3`.`tblVille` (`idVille`))
44ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48-- Table `TP3`.`tblVehicule`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `TP3`.`tblVehicule` (
51 `idVehicule` VARCHAR(7) NOT NULL,
52 `marque` VARCHAR(25) NOT NULL,
53 `modele` VARCHAR(25) NOT NULL,
54 `couleur` VARCHAR(15) NOT NULL,
55 `idAbonnement` INT NOT NULL,
56 PRIMARY KEY (`idVehicule`),
57 INDEX `FK_tblVehicule_idAbonne` (`idAbonnement` ASC),
58 CONSTRAINT `FK_tblVehicule_idAbonne`
59 FOREIGN KEY (`idAbonnement`)
60 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `TP3`.`tblMethodPaiement`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `TP3`.`tblMethodPaiement` (
68 `idPaiement` INT NOT NULL AUTO_INCREMENT,
69 `typePaiement` CHAR(1) NOT NULL,
70 `descPaiement` VARCHAR(25) NOT NULL,
71 PRIMARY KEY (`idPaiement`))
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `TP3`.`tblOccasionnel`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `TP3`.`tblOccasionnel` (
79 `idOccas` INT NOT NULL,
80 `dateDebutOccas` DATE NOT NULL,
81 `dateFinOccas` DATE NULL,
82 `idTransaction` INT NOT NULL,
83 PRIMARY KEY (`idOccas`),
84 INDEX `FK_tblOccasionnel_idTransaction` (`idTransaction` ASC),
85 CONSTRAINT `FK_tblOccasionnel_idTransaction`
86 FOREIGN KEY (`idTransaction`)
87 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
88ENGINE = InnoDB;
89
90
91-- -----------------------------------------------------
92-- Table `TP3`.`tblTransaction`
93-- -----------------------------------------------------
94CREATE TABLE IF NOT EXISTS `TP3`.`tblTransaction` (
95 `idTransaction` INT NOT NULL AUTO_INCREMENT,
96 `montant` DECIMAL(5,2) NOT NULL,
97 `numPaiement` VARCHAR(16) NULL,
98 `idPaiement` INT NOT NULL,
99 `idAbonne` INT NULL,
100 `idOccas` INT NULL,
101 PRIMARY KEY (`idTransaction`),
102 INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
103 INDEX `FK_tblTransaction_idAbonne` (`idAbonne` ASC),
104 INDEX `FK_tblTransaction_idOccas` (`idOccas` ASC),
105 CONSTRAINT `FK_tblTransaction_idPaiement`
106 FOREIGN KEY (`idPaiement`)
107 REFERENCES `TP3`.`tblMethodPaiement` (`idPaiement`),
108 CONSTRAINT `FK_tblTransaction_idAbonne`
109 FOREIGN KEY (`idAbonne`)
110 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`),
111 CONSTRAINT `FK_tblTransaction_idOccas`
112 FOREIGN KEY (`idOccas`)
113 REFERENCES `TP3`.`tblOccasionnel` (`idOccas`))
114ENGINE = InnoDB;
115
116
117-- -----------------------------------------------------
118-- Table `TP3`.`tblAbonnement`
119-- -----------------------------------------------------
120CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonnement` (
121 `idAbonnement` INT NOT NULL AUTO_INCREMENT,
122 `dateDebutAbonne` DATE NOT NULL,
123 `dateFinAbonne` DATE NOT NULL,
124 `idAbonne` INT NOT NULL,
125 `idVehicule` VARCHAR(7) NOT NULL,
126 `idPlace` SMALLINT NOT NULL,
127 `idTransaction` INT NOT NULL,
128 PRIMARY KEY (`idAbonnement`),
129 INDEX `FK_tblAbonnement_idAbonne_tblAbonne` (`idAbonne` ASC),
130 INDEX `FK_tblAbonnement_idVehicule` (`idVehicule` ASC),
131 INDEX `FK_tblAbonnement_idPlace` (`idPlace` ASC),
132 INDEX `FK_tblAbonnement_idTransaction` (`idTransaction` ASC),
133 CONSTRAINT `FK_tblAbonnement_idAbonne_tblAbonne`
134 FOREIGN KEY (`idAbonne`)
135 REFERENCES `TP3`.`tblAbonne` (`idAbonne`),
136 CONSTRAINT `FK_tblAbonnement_idVehicule`
137 FOREIGN KEY (`idVehicule`)
138 REFERENCES `TP3`.`tblVehicule` (`idVehicule`),
139 CONSTRAINT `FK_tblAbonnement_idPlace`
140 FOREIGN KEY (`idPlace`)
141 REFERENCES `TP3`.`tblPlace` (`idPlace`),
142 CONSTRAINT `FK_tblAbonnement_idTransaction`
143 FOREIGN KEY (`idTransaction`)
144 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
145ENGINE = InnoDB;
146
147
148-- -----------------------------------------------------
149-- Table `TP3`.`tblPlace`
150-- -----------------------------------------------------
151CREATE TABLE IF NOT EXISTS `TP3`.`tblPlace` (
152 `idPlace` SMALLINT NOT NULL,
153 `typePlace` VARCHAR(25) NOT NULL,
154 `idAbonne` INT NOT NULL,
155 PRIMARY KEY (`idPlace`),
156 INDEX `FK_tblPlace_idAbonne` (`idAbonne` ASC),
157 CONSTRAINT `FK_tblPlace_idAbonne`
158 FOREIGN KEY (`idAbonne`)
159 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
160ENGINE = InnoDB;
161
162
163SET SQL_MODE=@OLD_SQL_MODE;
164SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
165SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
166
167SET FOREIGN_KEY_CHECKS = 0;
168
169use TP3;
170
171-- -----------------------------------------------------
172-- Fonctions
173-- -----------------------------------------------------
174DELIMITER $$
175CREATE FUNCTION FnNbPlaceAbonne()
176RETURNS int
177BEGIN
178 DECLARE v_nb int;
179 SELECT count(*) as v_nb
180 FROM tblAbonnement as abon
181 WHERE abon.dateFinAbonne > now()
182 into v_nb;
183RETURN v_nb;
184END $$
185DELIMITER ;
186
187DELIMITER $$
188CREATE FUNCTION FnNbPlaceOccas()
189RETURNS int
190BEGIN
191 DECLARE v_nb int;
192 SELECT count(*) as v_nv
193 FROM tblOccasionnel as ocas
194 WHERE ocas.dateFinOccas is null
195 into v_nb;
196RETURN v_nb;
197END $$
198DELIMITER ;
199
200-- -----------------------------------------------------
201-- Procédures
202-- -----------------------------------------------------
203-- CompterPlaceDispo
204DELIMITER $$
205CREATE PROCEDURE `CompterPlaceDispo`()
206BEGIN
207 DECLARE v_nbOccas int;
208 DECLARE v_nbAbon int;
209 DECLARE v_nbTotal int;
210
211 SET v_nbOccas = FnNbPlaceOccas();
212 SET v_nbAbon = FnNbPlaceAbonne();
213 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
214 SELECT v_nbTotal as 'Stationnement disponible';
215END$$
216DELIMITER ;
217
218-- SommesPercuesParType
219DELIMITER $$
220CREATE PROCEDURE `SommesPercuesParType`(IN p_DateDebut Date, IN p_DateFin Date)
221BEGIN
222 DECLARE v_dateDebut date;
223 DECLARE v_dateFin date;
224 DECLARE v_type varchar(8);
225 DECLARE v_nbAbon int;
226 DECLARE v_nbOccas int;
227 DECLARE v_somme decimal(9,2);
228 DECLARE v_total int;
229
230 SET v_dateDebut = p_DateDebut;
231 SET v_dateFin = p_DateFin;
232
233 SELECT * FROM tblAbonne as ab
234 INNER JOIN tblAbonnement as abt
235 ON ab.idAbonne = abt.idAbonne
236 WHERE abt.dateDebutAbonne >= v_dateDebut and abt.dateFinAbonne <= v_dateFin;
237END$$
238DELIMITER ;
239
240-- AjoutAbonnement
241DELIMITER $$
242CREATE PROCEDURE `AjoutAbonnement`()
243BEGIN
244END$$
245DELIMITER ;
246
247-- MajAbonnement
248DELIMITER $$
249CREATE PROCEDURE `MajAbonnement`()
250BEGIN
251END$$
252DELIMITER ;
253
254-- MajOccasionnel
255DELIMITER $$
256CREATE PROCEDURE `MajOccasionnel`()
257BEGIN
258END$$
259DELIMITER ;
260
261-- AjoutOccasionnel
262DELIMITER $$
263CREATE PROCEDURE `AjoutOccasionnel`()
264BEGIN
265END$$
266DELIMITER ;
267
268-- AjoutAbonne
269DELIMITER $$
270CREATE PROCEDURE `AjoutAbonne`()
271BEGIN
272END$$
273DELIMITER ;
274
275-- AjoutPlace
276DELIMITER $$
277CREATE PROCEDURE `AjoutPlace`()
278BEGIN
279END$$
280DELIMITER ;
281
282-- AjoutTransaction
283DELIMITER $$
284CREATE PROCEDURE `AjoutTransaction`()
285BEGIN
286END$$
287DELIMITER ;
288
289#################################################
290############## Insertion données ################
291#################################################
292INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
293
294INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
295 VALUES ("Harveys",NULL,"Q4A2O7","1-195-388-6491","nec@Nulla.co.uk","S","3"),("Valentine","Cooper","K6A6V3","1-398-119-9628","habitant.morbi@diamluctuslobortis.com","P","3"),("Hedley","Harrell","B9D5S6","1-630-751-9399","leo@cursusaenim.ca","P","8"),("Gary","Roberts","Q2R2V3","1-965-296-3762","Morbi.accumsan.laoreet@fermentum.co.uk","P","7"),("Francis","Austin","P8O0F3","1-892-685-0409","tempor.diam@sociis.org","P","1"),("Edward","Mosley","S8J5W1","1-385-850-5982","et@urnaet.ca","P","3"),("Benedict","Bell","U0K6N4","1-570-196-4909","nunc.sit.amet@penatibusetmagnis.co.uk","P","8"),("Stewart","Soto","B1A4D5","1-893-189-9061","nec.metus.facilisis@enimcondimentumeget.ca","P","1"),("Neville","Marquez","O1J0H8","1-868-749-2997","ipsum.ac@diamnunc.edu","P","3"),("Zachery","Zimmerman","Y6T5A4","1-765-696-4719","et.tristique.pellentesque@dignissimtemporarcu.edu","P","5");
296INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
297 VALUES ("Simons",NULL,"Q9R5F5","1-878-506-6536","dictum.eu@a.edu","S","1"),("Steven","Hampton","T0V9T4","1-736-971-6586","Lorem.ipsum@ante.com","P","9"),("Jamal","Ramirez","V3F5Y9","1-826-233-8755","Donec.consectetuer@fringilla.net","P","2"),("Norman","Fields","M9C8J1","1-411-164-1888","a.feugiat@erosnectellus.net","P","2"),("Wyatt","Christensen","J8E3J3","1-746-245-0521","feugiat.Lorem.ipsum@nullaInteger.ca","P","1"),("Bert","Frazier","O0S9T6","1-781-873-6623","Aliquam.fringilla@Quisquelibero.com","P","7"),("Louis","House","I8M6E3","1-974-211-5774","ultrices.mauris@Integervitae.net","P","9"),("Alden","Cote","F3J7B6","1-893-631-0078","tristique.ac@quisturpis.net","P","5"),("Dolan","Fischer","L9Z4M5","1-141-234-6710","risus.Quisque@odiosagittissemper.co.uk","P","4"),("Zane","Manning","L6Y8S3","1-595-890-4447","imperdiet@placerataugueSed.com","P","4");
298INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
299 VALUES ("Sears",NULL,"Q7J7T4","1-337-614-9853","lacinia.vitae@malesuada.co.uk","S","6"),("Channing","Chapman","I9D6T3","1-530-608-2930","velit.Quisque@lectus.edu","P","7"),("Declan","Gamble","F4T1A0","1-431-958-2193","Fusce@lectusantedictum.co.uk","P","1"),("Zeph","Grant","U6Q2T3","1-662-303-3018","elit.Aliquam@nibh.ca","P","6"),("Reuben","Crane","Q2M9Z2","1-108-887-7001","dui.semper.et@felisDonec.co.uk","P","1"),("Cairo","Hensley","Z6V4Q0","1-193-858-1665","erat@nonluctussit.org","P","1"),("Acton","Garza","S5P7Q3","1-410-750-3337","neque@Quisque.net","P","1"),("Victor","Beach","B6V2T6","1-361-585-6601","at.risus@nonquamPellentesque.net","P","9"),("Brett","Doyle","O9P4R4","1-929-469-2034","aliquam@semmolestie.edu","P","9"),("Quentin","Haynes","X1D1L4","1-489-636-2755","nisl.Nulla.eu@vitaedolorDonec.edu","P","1");
300INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
301 VALUES ("Canac",NULL,"Y1C5C7","1-321-806-6027","In.ornare@adipiscingMaurismolestie.co.uk","S","6"),("Amery","Fletcher","A7V3H7","1-922-766-4167","ut.sem@sitametconsectetuer.edu","P","8"),("Lawrence","Conley","N2J8S1","1-309-234-5089","nec@luctuslobortisClass.net","P","9"),("Herman","Clarke","C4Q6N4","1-594-470-9280","Morbi.metus@dolor.net","P","9"),("Alvin","Garrison","I9N0E9","1-807-832-5544","augue.malesuada.malesuada@Nuncsedorci.co.uk","P","7"),("Jesse","Meyers","G0O1X7","1-593-459-8094","adipiscing.ligula@semper.edu","P","6"),("Martin","Montgomery","L8Z8W1","1-951-412-8004","molestie@Donecconsectetuer.net","P","9"),("Yuli","Wiley","T8I8E4","1-397-165-4584","diam.luctus@nec.org","P","8"),("Kareem","Singleton","Z3H8Q9","1-199-250-4773","et.libero.Proin@leo.edu","P","4"),("Ezekiel","Hardin","N3A6B8","1-425-440-1465","euismod@placerat.co.uk","P","4");
302INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
303 VALUES ("Ardenne",NULL,"A0G8T1","1-520-529-5925","Sed.nunc.est@antebibendumullamcorper.com","S","4"),("Donovan","Boyle","V9U4T5","1-116-449-6440","iaculis.odio.Nam@ipsumdolor.ca","P","5"),("Zachary","Wagner","I5I8I3","1-768-867-8790","Integer.aliquam@auguescelerisque.co.uk","P","4"),("Josiah","Pickett","W5L4F1","1-859-110-2038","enim.Suspendisse@leo.ca","P","7"),("Demetrius","Reed","K6E8S2","1-979-137-2222","Sed.nunc.est@laciniaatiaculis.org","P","6"),("Ezra","Strong","H7C2N6","1-403-362-0716","laoreet@metusAliquam.org","P","4"),("Slade","Mann","P7C1V8","1-812-124-1004","venenatis@laciniavitae.org","P","8"),("Emmanuel","George","U2R8C6","1-477-621-3758","feugiat.nec.diam@non.net","P","8"),("John","Warner","P8D4H6","1-276-604-5994","mi@feugiat.co.uk","P","3"),("Lucius","Rocha","U2D1F6","1-191-164-7881","erat.volutpat@posuerevulputatelacus.com","P","7");
304
305INSERT INTO tblVehicule VALUES("Q4A2O7", "Honda", "Civic", "Bleu", 1),("Q9R5F5", "Mazda", "Protégé", "Gris", 11),("K6A6V3", "Pontiac", "Sundance", "Gris", 2)
306 ,("T0V9T4", "Chevrolet", "Cavalier", "Orange", 12),("I9D6T3", "Pontiac", "Sunfire", "Rose", 21);
307
308INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
309
310INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas) VALUES('115.50','4000000000000002','1','1',null),('15.00','4000000000000002','1',NULL,'1');
311
312INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
313#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
314
315INSERT INTO tblPlace VALUES('1','P','1');
316
317INSERT INTO tblOccasionnel VALUES('201804061',curdate(),NULL,2);
318
319select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
320select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
321call CompterPlaceDispo();