(4,26,'2007-02-21','2007-02-21',485.00,1),(4,41,'2007-02-21','2007-02-21',835.00
(6,16,'2010-01-27','2010-01-27',200.00,1),(7,5,'2011-04-05','2011-04-05',150.00
(9,38,'2007-02-11','2007-02-11',985.00,1),(9,55,'2011-02-13','2011-02-13',140.00
(10,49,'2010-08-17','2010-08-17',140.00,0),(11,32,'2008-08-17','2010-03-09',140
(12,15,'2012-05-22',NULL,200.00,1),(12,69,'2007-09-20','2007-09-20',10.00,1),(12
(13,57,'2012-01-10','2012-01-10',700.00,1),(14,58,'2012-02-25','2012-02-25',700
UNLOCK TABLES;
CREATE TABLE VM_Revenus_annee_espece (
annee int(4) NOT NULL DEFAULT '0',
espece_id smallint(6) unsigned NOT NULL DEFAULT '0',
somme decimal(29,2) DEFAULT NULL,
nb bigint(21) NOT NULL DEFAULT '0',
PRIMARY KEY (annee,espece_id),
KEY somme (somme)
) ENGINE=InnoDB;
LOCK TABLES VM_Revenus_annee_espece WRITE;
INSERT INTO VM_Revenus_annee_espece VALUES (2007,1,485.00,1),(2007,2,1820.00,2),(
(2008,3,140.00,1),(2009,1,400.00,2),(2010,1,200.00,1),(2010,3,140.00,1),(2011,1
(2011,2,985.00,2),(2011,3,140.00,1),(2012,1,200.00,1),(2012,2,735.00,1),(2012,4
UNLOCK TABLES;
CREATE TABLE Animal_histo (
id smallint(6) unsigned NOT NULL,
sexe char(1) DEFAULT NULL,
date_naissance datetime NOT NULL,
nom varchar(30) DEFAULT NULL,
commentaires text,
espece_id smallint(6) unsigned NOT NULL,
race_id smallint(6) unsigned DEFAULT NULL,
mere_id smallint(6) unsigned DEFAULT NULL,
pere_id smallint(6) unsigned DEFAULT NULL,
disponible tinyint(1) DEFAULT '1',
date_histo datetime NOT NULL,
utilisateur_histo varchar(20) NOT NULL,
evenement_histo char(6) NOT NULL,
PRIMARY KEY (id,date_histo)
) ENGINE=InnoDB;
LOCK TABLES Animal_histo WRITE;
INSERT INTO Animal_histo VALUES (10,'M','2010-07-21 15:41:00','Bobo',NULL,1,NULL
09:02:00','Java',NULL,1,2,NULL,NULL,1,'2012-05-27 18:14:29','sdz@localhost','UPDATE'
18:10:40','sdz@localhost','UPDATE'),
(47,'F','2009-03-26 01:24:00','Scroupy','Bestiole avec une carapace très dure',
00 00:00:00','Toxi',NULL,1,NULL,NULL,NULL,1,'2012-05-27 18:12:38','sdz@localhost'
UNLOCK TABLES;
CREATE TABLE Erreur (
id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
erreur varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY erreur (erreur)
) ENGINE=InnoDB AUTO_INCREMENT=8;
LOCK TABLES Erreur WRITE;
INSERT INTO Erreur VALUES (5,'Erreur : date_adoption doit être >= à date_reservation
doit valoir \"M\", \"F\" ou NULL.');
UNLOCK TABLES;
ALTER TABLE Race ADD CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES
ALTER TABLE Animal ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES
ALTER TABLE Animal ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal
ALTER TABLE Animal ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal
ALTER TABLE Animal ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race
ALTER TABLE Adoption ADD CONSTRAINT fk_adoption_animal_id FOREIGN KEY (animal_id
Partie 7 : Gestion des utilisateurs et configuration du serveur
377/414
www.openclassrooms.com
(6,16,'2010-01-27','2010-01-27',200.00,1),(7,5,'2011-04-05','2011-04-05',150.00
(9,38,'2007-02-11','2007-02-11',985.00,1),(9,55,'2011-02-13','2011-02-13',140.00
(10,49,'2010-08-17','2010-08-17',140.00,0),(11,32,'2008-08-17','2010-03-09',140
(12,15,'2012-05-22',NULL,200.00,1),(12,69,'2007-09-20','2007-09-20',10.00,1),(12
(13,57,'2012-01-10','2012-01-10',700.00,1),(14,58,'2012-02-25','2012-02-25',700
UNLOCK TABLES;
CREATE TABLE VM_Revenus_annee_espece (
annee int(4) NOT NULL DEFAULT '0',
espece_id smallint(6) unsigned NOT NULL DEFAULT '0',
somme decimal(29,2) DEFAULT NULL,
nb bigint(21) NOT NULL DEFAULT '0',
PRIMARY KEY (annee,espece_id),
KEY somme (somme)
) ENGINE=InnoDB;
LOCK TABLES VM_Revenus_annee_espece WRITE;
INSERT INTO VM_Revenus_annee_espece VALUES (2007,1,485.00,1),(2007,2,1820.00,2),(
(2008,3,140.00,1),(2009,1,400.00,2),(2010,1,200.00,1),(2010,3,140.00,1),(2011,1
(2011,2,985.00,2),(2011,3,140.00,1),(2012,1,200.00,1),(2012,2,735.00,1),(2012,4
UNLOCK TABLES;
CREATE TABLE Animal_histo (
id smallint(6) unsigned NOT NULL,
sexe char(1) DEFAULT NULL,
date_naissance datetime NOT NULL,
nom varchar(30) DEFAULT NULL,
commentaires text,
espece_id smallint(6) unsigned NOT NULL,
race_id smallint(6) unsigned DEFAULT NULL,
mere_id smallint(6) unsigned DEFAULT NULL,
pere_id smallint(6) unsigned DEFAULT NULL,
disponible tinyint(1) DEFAULT '1',
date_histo datetime NOT NULL,
utilisateur_histo varchar(20) NOT NULL,
evenement_histo char(6) NOT NULL,
PRIMARY KEY (id,date_histo)
) ENGINE=InnoDB;
LOCK TABLES Animal_histo WRITE;
INSERT INTO Animal_histo VALUES (10,'M','2010-07-21 15:41:00','Bobo',NULL,1,NULL
09:02:00','Java',NULL,1,2,NULL,NULL,1,'2012-05-27 18:14:29','sdz@localhost','UPDATE'
18:10:40','sdz@localhost','UPDATE'),
(47,'F','2009-03-26 01:24:00','Scroupy','Bestiole avec une carapace très dure',
00 00:00:00','Toxi',NULL,1,NULL,NULL,NULL,1,'2012-05-27 18:12:38','sdz@localhost'
UNLOCK TABLES;
CREATE TABLE Erreur (
id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
erreur varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY erreur (erreur)
) ENGINE=InnoDB AUTO_INCREMENT=8;
LOCK TABLES Erreur WRITE;
INSERT INTO Erreur VALUES (5,'Erreur : date_adoption doit être >= à date_reservation
doit valoir \"M\", \"F\" ou NULL.');
UNLOCK TABLES;
ALTER TABLE Race ADD CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES
ALTER TABLE Animal ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES
ALTER TABLE Animal ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal
ALTER TABLE Animal ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal
ALTER TABLE Animal ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race
ALTER TABLE Adoption ADD CONSTRAINT fk_adoption_animal_id FOREIGN KEY (animal_id
Partie 7 : Gestion des utilisateurs et configuration du serveur
377/414
www.openclassrooms.com