dCCB0p20

1CREATE TABLE IF NOT EXISTS Employe (
2	id INT PRIMARY KEY AUTO_INCREMENT,
3	matricule CHAR(6) NOT NULL,
4	prenom VARCHAR(50) NOT NULL,
5	nom VARCHAR(50) NOT NULL,
6	dateEmbauche DATE NOT NULL,
7	salaire DECIMAL(9,2) NOT NULL
8);
9ALTER TABLE Employe ADD CONSTRAINT matricule_unique UNIQUE(matricule);
10
11CREATE TABLE IF NOT EXISTS Commercial(
12	id INT PRIMARY KEY,
13	caAnnuel DECIMAL(10,2),
14	performance INT
15);
16
17ALTER TABLE Commercial ADD CONSTRAINT fk_employe_commercial FOREIGN KEY(id) 
18
19REFERENCES Employe(id);
20CREATE TABLE IF NOT EXISTS Manager (
21	id INT PRIMARY KEY
22);
23
24ALTER TABLE Manager ADD CONSTRAINT fk_employe_manager FOREIGN KEY(id) 
25REFERENCES Employe(id);
26
27CREATE TABLE IF NOT EXISTS Technicien (
28	id INT PRIMARY KEY,
29	manager_id INT,
30	grade TINYINT UNSIGNED,
31	CONSTRAINT fk_manager_technicien FOREIGN KEY(manager_id) REFERENCES Manager(id),
32	CONSTRAINT fk_employe_technicien FOREIGN KEY(id) REFERENCES Employe(id)
33);