· 10 years ago · Sep 26, 2016, 09:24 AM
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 mydb
9-- -----------------------------------------------------
10DROP SCHEMA IF exists `mydb`;
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`Abonné`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Abonné` (
21 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
22 `Nom` VARCHAR(45) NOT NULL,
23 `Prenom` INT NOT NULL,
24 `Birthday` DATETIME NOT NULL,
25 `AdhesionDate` DATETIME NOT NULL,
26 `Phone` VARCHAR(15) NULL,
27 `Mail` VARCHAR(45) NULL,
28 `ExpirationDate` DATETIME NOT NULL,
29 `Actif` TINYINT(1) NOT NULL,
30 PRIMARY KEY (`ID`))
31ENGINE = InnoDB;
32
33CREATE UNIQUE INDEX `ID_UNIQUE` ON `mydb`.`Abonné` (`ID` ASC);
34
35CREATE UNIQUE INDEX `Mail_UNIQUE` ON `mydb`.`Abonné` (`Mail` ASC);
36
37CREATE UNIQUE INDEX `Phone_UNIQUE` ON `mydb`.`Abonné` (`Phone` ASC);
38
39
40-- -----------------------------------------------------
41-- Table `mydb`.`Auteur`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `mydb`.`Auteur` (
44 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
45 `NomAuteur` VARCHAR(45) NOT NULL,
46 `PrénomAuteur` VARCHAR(45) NULL,
47 `Birthday` DATETIME NULL,
48 `Deathday` DATETIME NULL,
49 PRIMARY KEY (`ID`))
50ENGINE = InnoDB;
51
52CREATE UNIQUE INDEX `ID_UNIQUE` ON `mydb`.`Auteur` (`ID` ASC);
53
54
55-- -----------------------------------------------------
56-- Table `mydb`.`Genre`
57-- -----------------------------------------------------
58CREATE TABLE IF NOT EXISTS `mydb`.`Genre` (
59 `idGenre` INT UNSIGNED NOT NULL AUTO_INCREMENT,
60 `Intitulé` VARCHAR(45) NOT NULL,
61 PRIMARY KEY (`idGenre`))
62ENGINE = InnoDB;
63
64CREATE UNIQUE INDEX `idGenre_UNIQUE` ON `mydb`.`Genre` (`idGenre` ASC);
65
66CREATE UNIQUE INDEX `Intitulé_UNIQUE` ON `mydb`.`Genre` (`Intitulé` ASC);
67
68
69-- -----------------------------------------------------
70-- Table `mydb`.`Titre`
71-- -----------------------------------------------------
72CREATE TABLE IF NOT EXISTS `mydb`.`Titre` (
73 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
74 `IDAuteur` INT UNSIGNED NOT NULL,
75 `Titre` VARCHAR(45) NOT NULL,
76 `AnneeParution` DATETIME NULL,
77 `Genre` INT UNSIGNED NOT NULL,
78 PRIMARY KEY (`ID`),
79 CONSTRAINT `fk_Titre_Auteur1`
80 FOREIGN KEY (`IDAuteur`)
81 REFERENCES `mydb`.`Auteur` (`ID`)
82 ON DELETE NO ACTION
83 ON UPDATE NO ACTION,
84 CONSTRAINT `fk_Titre_Genre1`
85 FOREIGN KEY (`Genre`)
86 REFERENCES `mydb`.`Genre` (`idGenre`)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION)
89ENGINE = InnoDB;
90
91CREATE INDEX `fk_Titre_Auteur1_idx` ON `mydb`.`Titre` (`IDAuteur` ASC);
92
93CREATE INDEX `fk_Titre_Genre1_idx` ON `mydb`.`Titre` (`Genre` ASC);
94
95CREATE UNIQUE INDEX `ID_UNIQUE` ON `mydb`.`Titre` (`ID` ASC);
96
97
98-- -----------------------------------------------------
99-- Table `mydb`.`Editeur`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `mydb`.`Editeur` (
102 `idEditeur` INT UNSIGNED NOT NULL AUTO_INCREMENT,
103 `NomEditeur` VARCHAR(45) NOT NULL,
104 `AdresseEditeur` VARCHAR(45) NULL,
105 PRIMARY KEY (`idEditeur`))
106ENGINE = InnoDB;
107
108CREATE UNIQUE INDEX `idEditeur_UNIQUE` ON `mydb`.`Editeur` (`idEditeur` ASC);
109
110CREATE UNIQUE INDEX `NomEditeur_UNIQUE` ON `mydb`.`Editeur` (`NomEditeur` ASC);
111
112
113-- -----------------------------------------------------
114-- Table `mydb`.`Livre`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `mydb`.`Livre` (
117 `ID Rayon` INT UNSIGNED NOT NULL AUTO_INCREMENT,
118 `ID Catalogue` INT UNSIGNED NULL,
119 `Editeur` INT UNSIGNED NOT NULL,
120 `IDTitre` INT UNSIGNED NOT NULL,
121 `AnneeEdition` DATETIME NULL,
122 `DateAchat` DATETIME NULL,
123 `Etat` VARCHAR(1) NOT NULL DEFAULT 'N',
124 PRIMARY KEY (`ID Rayon`),
125 CONSTRAINT `fk_Livre_Titre1`
126 FOREIGN KEY (`IDTitre`)
127 REFERENCES `mydb`.`Titre` (`ID`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `fk_Livre_Editeur1`
131 FOREIGN KEY (`Editeur`)
132 REFERENCES `mydb`.`Editeur` (`idEditeur`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION)
135ENGINE = InnoDB;
136
137CREATE INDEX `fk_Livre_Titre1_idx` ON `mydb`.`Livre` (`IDTitre` ASC);
138
139CREATE UNIQUE INDEX `ID Rayon_UNIQUE` ON `mydb`.`Livre` (`ID Rayon` ASC);
140
141CREATE INDEX `fk_Livre_Editeur1_idx` ON `mydb`.`Livre` (`Editeur` ASC);
142
143
144-- -----------------------------------------------------
145-- Table `mydb`.`Emprunt`
146-- -----------------------------------------------------
147CREATE TABLE IF NOT EXISTS `mydb`.`Emprunt` (
148 `IDBook` INT UNSIGNED NOT NULL,
149 `IDAbonnee` INT UNSIGNED NOT NULL,
150 `DateEmprunt` DATETIME NOT NULL,
151 `TempsEmprunt` INT UNSIGNED NOT NULL DEFAULT 15,
152 `Date de retour` DATETIME,
153 PRIMARY KEY (`IDBook`, `IDAbonnee`, `DateEmprunt`),
154 CONSTRAINT `fk_Emprunt_Livre1`
155 FOREIGN KEY (`IDBook`)
156 REFERENCES `mydb`.`Livre` (`ID Rayon`)
157 ON DELETE NO ACTION
158 ON UPDATE NO ACTION,
159 CONSTRAINT `fk_Emprunt_Abonné1`
160 FOREIGN KEY (`IDAbonnee`)
161 REFERENCES `mydb`.`Abonné` (`ID`)
162 ON DELETE NO ACTION
163 ON UPDATE NO ACTION)
164ENGINE = InnoDB;
165
166CREATE INDEX `fk_Emprunt_Livre1_idx` ON `mydb`.`Emprunt` (`IDBook` ASC);
167
168CREATE INDEX `fk_Emprunt_Abonné1_idx` ON `mydb`.`Emprunt` (`IDAbonnee` ASC);
169
170-- -----------------------------------------------------
171-- Table `mydb`.`Reservation`
172-- -----------------------------------------------------
173CREATE TABLE IF NOT EXISTS `mydb`.`Reservation` (
174 `ID Abonné` INT UNSIGNED NOT NULL,
175 `ID Titre` INT UNSIGNED NOT NULL,
176 `DateReservation` DATETIME NOT NULL,
177 `DateLiberation` DATETIME NULL,
178 PRIMARY KEY (`ID Abonné`, `ID Titre`, `DateReservation`),
179 CONSTRAINT `fk_Reservation_Abonné1`
180 FOREIGN KEY (`ID Abonné`)
181 REFERENCES `mydb`.`Abonné` (`ID`)
182 ON DELETE NO ACTION
183 ON UPDATE NO ACTION,
184 CONSTRAINT `fk_Reservation_Titre1`
185 FOREIGN KEY (`ID Titre`)
186 REFERENCES `mydb`.`Titre` (`ID`)
187 ON DELETE NO ACTION
188 ON UPDATE NO ACTION)
189ENGINE = InnoDB;
190
191CREATE INDEX `fk_Reservation_Titre1_idx` ON `mydb`.`Reservation` (`ID Titre` ASC);
192
193
194-- -----------------------------------------------------
195-- Table `mydb`.`MotsClé`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `mydb`.`MotsClé` (
198 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
199 `Intitulé` VARCHAR(45) NOT NULL,
200 PRIMARY KEY (`ID`))
201ENGINE = InnoDB;
202
203CREATE UNIQUE INDEX `ID_UNIQUE` ON `mydb`.`MotsClé` (`ID` ASC);
204
205CREATE UNIQUE INDEX `Intitulé_UNIQUE` ON `mydb`.`MotsClé` (`Intitulé` ASC);
206
207
208-- -----------------------------------------------------
209-- Table `mydb`.`Décrit`
210-- -----------------------------------------------------
211CREATE TABLE IF NOT EXISTS `mydb`.`Décrit` (
212 `Titre_ID` INT UNSIGNED NOT NULL,
213 `MotsClé_ID` INT UNSIGNED NOT NULL,
214 PRIMARY KEY (`Titre_ID`, `MotsClé_ID`),
215 CONSTRAINT `fk_Titre_has_MotsClé_Titre1`
216 FOREIGN KEY (`Titre_ID`)
217 REFERENCES `mydb`.`Titre` (`ID`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION,
220 CONSTRAINT `fk_Titre_has_MotsClé_MotsClé1`
221 FOREIGN KEY (`MotsClé_ID`)
222 REFERENCES `mydb`.`MotsClé` (`ID`)
223 ON DELETE NO ACTION
224 ON UPDATE NO ACTION)
225ENGINE = InnoDB;
226
227CREATE INDEX `fk_Titre_has_MotsClé_MotsClé1_idx` ON `mydb`.`Décrit` (`MotsClé_ID` ASC);
228
229CREATE INDEX `fk_Titre_has_MotsClé_Titre1_idx` ON `mydb`.`Décrit` (`Titre_ID` ASC);
230
231
232SET SQL_MODE=@OLD_SQL_MODE;
233SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
234SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;