VvmN3y5W

1CREATE DATABASE gestion_conges CHARACTER SET 'utf8';
2USE gestion_conges;
3CREATE TABLE IF NOT EXISTS Salaries(
4id_salarie SMALLINT(4) UNSIGNED ZEROFILL  NOT NULL AUTO_INCREMENT,
5password LONGTEXT NOT NULL,
6nom VARCHAR(40) NOT NULL,
7prenom VARCHAR(40) NOT NULL,
8email VARCHAR(60) NOT NULL UNIQUE,
9telephone CHAR(10) NOT NULL,
10fonction VARCHAR(30) NOT NULL,
11contrat CHAR(3) NOT NULL,
12date_embauche DATE NOT NULL,
13rtt_restants TINYINT UNSIGNED  NOT NULL,
14conges_payes_restants TINYINT UNSIGNED NOT NULL,
15code_postal CHAR(5),
16ville VARCHAR(60),
17rue VARCHAR (100),
18nationalite VARCHAR(30),
19sexe CHAR(5),
20situation VARCHAR(20),
21date_naissance DATE,
22PRIMARY KEY(id_salarie)
23)
24ENGINE = INNODB;
25CREATE TABLE IF NOT EXISTS Commentaires(
26id_comm SMALLINT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
27salarie SMALLINT(4) UNSIGNED NOT NULL,
28objet VARCHAR(60) NOT NULL,
29commentaire TEXT NOT NULL,
30date_com DATETIME NOT NULL,
31PRIMARY KEY(id_comm),
32CONSTRAINT fk_commentaires_salaries FOREIGN KEY (salarie) REFERENCES Salaries(id_salarie)
33)
34ENGINE = INNODB;
35CREATE TABLE IF NOT EXISTS Conges(
36id_conge SMALLINT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
37demande_conge DATETIME NOT NULL,
38debut_conge DATE NOT NULL,
39fin_conge DATE NOT NULL,
40type_conge VARCHAR(60) NOT NULL,
41etat_conge BINARY(1),
42comm TEXT,
43salarie SMALLINT(4) UNSIGNED NOT NULL,
44PRIMARY KEY(id_conge),
45CONSTRAINT fk_conges_salaries FOREIGN KEY (salarie) REFERENCES Salaries(id_salarie)
46)
47ENGINE = INNODB;
48CREATE TABLE IF NOT EXISTS Directeur(
49id_directeur SMALLINT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
50email VARCHAR(60) NOT NULL UNIQUE,
51password LONGTEXT NOT NULL,
52fonction VARCHAR(30) NOT NULL,
53PRIMARY KEY(id_directeur)
54)
55ENGINE = INNODB;
56INSERT INTO Directeur(id_directeur, email,password, fonction)
57VALUES (NULL,'admin@admin.com', '$2y$10$utQ10iAKENSHTwprVSn2Iug1jJ1EuX2qQ6zaLA7djzn6UVFed4eHi', 'directeur');