· 6 years ago · Jun 18, 2019, 09:24 AM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Jun 6 20:22:04 2019
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema OC_Pizza
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema OC_Pizza
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `OC_Pizza` DEFAULT CHARACTER SET utf8 ;
18USE `OC_Pizza` ;
19
20-- -----------------------------------------------------
21-- Table `OC_Pizza`.`client`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `OC_Pizza`.`client` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `email` VARCHAR(45) NOT NULL,
26 `mot_de_passe` VARCHAR(45) NOT NULL,
27 `nom` VARCHAR(45) NOT NULL,
28 `adresse` VARCHAR(45) NULL,
29 `pizzeria_favorite` VARCHAR(45) NULL,
30 PRIMARY KEY (`id`, `email`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `OC_Pizza`.`commande`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `OC_Pizza`.`commande` (
38 `id` INT NOT NULL AUTO_INCREMENT,
39 `date` DATE NOT NULL,
40 `prix_total` DECIMAL GENERATED ALWAYS AS () VIRTUAL,
41 `taux_tva` DECIMAL NOT NULL,
42 `payee` TINYINT NOT NULL,
43 `pizzeria` VARCHAR(45) NOT NULL,
44 `retrait _sur_place` TINYINT NOT NULL,
45 `id_client` VARCHAR(45) NOT NULL,
46 `client_id` INT NOT NULL,
47 PRIMARY KEY (`id`),
48 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
49 INDEX `fk_commande_client_idx` (`client_id` ASC) VISIBLE,
50 CONSTRAINT `fk_commande_client`
51 FOREIGN KEY (`client_id`)
52 REFERENCES `OC_Pizza`.`client` (`id`)
53 ON DELETE NO ACTION
54 ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `OC_Pizza`.`employe`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `OC_Pizza`.`employe` (
62 `id` INT NOT NULL AUTO_INCREMENT,
63 `nom` VARCHAR(45) NOT NULL,
64 `mot_de_passe` VARCHAR(45) NOT NULL,
65 `role` INT NOT NULL,
66 PRIMARY KEY (`id`))
67ENGINE = InnoDB;
68
69
70-- -----------------------------------------------------
71-- Table `OC_Pizza`.`livraison`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS `OC_Pizza`.`livraison` (
74 `id` INT NOT NULL AUTO_INCREMENT,
75 `adresse` VARCHAR(45) NOT NULL,
76 `livreur` VARCHAR(45) NOT NULL,
77 `etat` VARCHAR(45) NOT NULL,
78 `commande_id` INT NOT NULL,
79 `employe_id` INT NOT NULL,
80 PRIMARY KEY (`id`),
81 INDEX `fk_livraison_commande1_idx` (`commande_id` ASC) VISIBLE,
82 INDEX `fk_livraison_employe1_idx` (`employe_id` ASC) VISIBLE,
83 CONSTRAINT `fk_livraison_commande1`
84 FOREIGN KEY (`commande_id`)
85 REFERENCES `OC_Pizza`.`commande` (`id`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION,
88 CONSTRAINT `fk_livraison_employe1`
89 FOREIGN KEY (`employe_id`)
90 REFERENCES `OC_Pizza`.`employe` (`id`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `OC_Pizza`.`ingredient`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `OC_Pizza`.`ingredient` (
100 `id` INT NOT NULL AUTO_INCREMENT,
101 `nom` VARCHAR(45) NOT NULL,
102 `quantite_en_stock` INT NOT NULL,
103 PRIMARY KEY (`id`))
104ENGINE = InnoDB;
105
106
107-- -----------------------------------------------------
108-- Table `OC_Pizza`.`article`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `OC_Pizza`.`article` (
111 `id` INT NOT NULL AUTO_INCREMENT,
112 `nom` VARCHAR(45) NOT NULL,
113 `prix` VARCHAR(45) NOT NULL,
114 `taux_tva` DECIMAL NOT NULL,
115 `commande_id` INT NOT NULL,
116 PRIMARY KEY (`id`),
117 INDEX `fk_article_commande1_idx` (`commande_id` ASC) VISIBLE,
118 CONSTRAINT `fk_article_commande1`
119 FOREIGN KEY (`commande_id`)
120 REFERENCES `OC_Pizza`.`commande` (`id`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION)
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `OC_Pizza`.`recette`
128-- -----------------------------------------------------
129CREATE TABLE IF NOT EXISTS `OC_Pizza`.`recette` (
130 `id` INT NOT NULL AUTO_INCREMENT,
131 `temps_cuisson` INT NOT NULL,
132 `détails` BLOB NOT NULL,
133 `article_id` INT NOT NULL,
134 PRIMARY KEY (`id`),
135 INDEX `fk_recette_article1_idx` (`article_id` ASC) VISIBLE,
136 CONSTRAINT `fk_recette_article1`
137 FOREIGN KEY (`article_id`)
138 REFERENCES `OC_Pizza`.`article` (`id`)
139 ON DELETE NO ACTION
140 ON UPDATE NO ACTION)
141ENGINE = InnoDB;
142
143
144-- -----------------------------------------------------
145-- Table `OC_Pizza`.`recette_ingredient`
146-- -----------------------------------------------------
147CREATE TABLE IF NOT EXISTS `OC_Pizza`.`recette_ingredient` (
148 `recette_id` INT NOT NULL,
149 `ingredient_id` INT NOT NULL,
150 `quantite` INT NOT NULL,
151 PRIMARY KEY (`ingredient_id`, `recette_id`),
152 INDEX `fk_recette_has_ingredient_ingredient1_idx` (`ingredient_id` ASC) VISIBLE,
153 INDEX `fk_recette_has_ingredient_recette1_idx` (`recette_id` ASC) VISIBLE,
154 CONSTRAINT `fk_recette_has_ingredient_recette1`
155 FOREIGN KEY (`recette_id`)
156 REFERENCES `OC_Pizza`.`recette` (`id`)
157 ON DELETE NO ACTION
158 ON UPDATE NO ACTION,
159 CONSTRAINT `fk_recette_has_ingredient_ingredient1`
160 FOREIGN KEY (`ingredient_id`)
161 REFERENCES `OC_Pizza`.`ingredient` (`id`)
162 ON DELETE NO ACTION
163 ON UPDATE NO ACTION)
164ENGINE = InnoDB;
165
166
167SET SQL_MODE=@OLD_SQL_MODE;
168SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
169SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;