nC1VNCQ7

1DROP DATABASE IF EXISTS data_leroy;
2CREATE DATABASE data_leroy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3USE data_leroy;
4
5CREATE USER 'johnsnow'@'localhost' IDENTIFIED BY '123';
6GRANT ALL PRIVILEGES ON * . * to 'johnsnow'@'localhost';
7FLUSH PRIVILEGES;
8
9--La table permetant de stocker les utilisateurs
10
11CREATE TABLE users 
12(
13	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
14	mail VARCHAR(30) UNIQUE NOT NULL,
15	mdp VARCHAR(191) NOT NULL,
16	nom VARCHAR(20) UNIQUE NOT NULL,
17    prenom VARCHAR(20) UNIQUE NOT NULL,
18	adress VARCHAR(60),
19	code_postal VARCHAR(15),
20	ville VARCHAR(40),
21	telephone VARCHAR(20),
22    siege_le_plus_proche VARCHAR(20),
23    PRIMARY KEY (id)
24)
25ENGINE = INNODB;
26
27--La table permetant de stocker les données relatives aux projets
28
29CREATE TABLE projets 
30(
31	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
32	user VARCHAR(30) UNIQUE NOT NULL,
33	nom_projet VARCHAR(20) UNIQUE NOT NULL,
34    desc_projet TEXT,
35    date_creation DATETIME NOT NULL,
36    date_fin DATETIME NOT NULL,
37    PRIMARY KEY (id)
38)
39ENGINE = INNODB;
40
41--La table permetant de stocker les données relatives aux produits
42
43CREATE TABLE produc_data 
44(
45	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
46	nom_produc VARCHAR(20) UNIQUE NOT NULL,
47    date_ajout DATETIME NOT NULL,
48    PRIMARY KEY (id)
49    FOREIGN KEY 
50)
51ENGINE = INNODB;
52
53--La table permetant de relier un produit à un projet
54
55CREATE TABLE projets_to_product
56(
57	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
58	id_projet INT UNSIGNED NOT NULL,
59	id_product INT UNSIGNED NOT NULL,
60    longeur INT UNSIGNED NOT NULL,
61    largeur INT UNSIGNED NOT NULL,
62    epaisseur INT UNSIGNED NOT NULL,
63    prix_HT INT UNSIGNED NOT NULL,
64    prix_TVA INT UNSIGNED NOT NULL,
65    PRIMARY KEY (id)
66)
67ENGINE = INNODB;
68
69--La table permetant de stocker les données relatives à la personnalisation des modules d’une porte
70
71CREATE TABLE data_door 
72(
73	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
74    color VARCHAR(20) UNIQUE NOT NULL,
75    id_materials INT UNSIGNED NOT NULL,
76    longeur INT UNSIGNED NOT NULL,
77    largeur INT UNSIGNED NOT NULL,
78    PRIMARY KEY (id)
79)
80ENGINE = INNODB;
81
82--La table permetant de stocker les données relatives aux matériaux
83
84CREATE TABLE materials 
85(
86	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
87    product_name VARCHAR(20) UNIQUE NOT NULL,
88    price_HT INT UNSIGNED NOT NULL,
89    price_TVA INT UNSIGNED NOT NULL,
90    PRIMARY KEY (id)
91)
92ENGINE = INNODB;
93
94--La table permetant de stocker les factures
95
96CREATE TABLE factures 
97(
98	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
99    date_creation_facture DATETIME NOT NULL,
100    mode_paiment VARCHAR(20) UNIQUE NOT NULL,
101    etat_facture VARCHAR(20) UNIQUE NOT NULL,
102    date_paiment DATETIME NOT NULL,
103    PRIMARY KEY (id)
104)
105ENGINE = INNODB;