9CqpgFVn

1DROP TABLE IF EXISTS employe CASCADE;
2DROP TABLE IF EXISTS participation_evenement CASCADE;
3DROP TABLE IF EXISTS statut_participation CASCADE;
4DROP TABLE IF EXISTS evenement CASCADE;
5DROP TABLE IF EXISTS reponse_question_sondage CASCADE;
6DROP TABLE IF EXISTS question CASCADE;
7DROP TABLE IF EXISTS question_sondage CASCADE;
8DROP TABLE IF EXISTS droit_acces_sondage CASCADE;
9DROP TABLE IF EXISTS membre_departement CASCADE;
10DROP TABLE IF EXISTS responsable_departement CASCADE;
11DROP TABLE IF EXISTS departement CASCADE;
12DROP TABLE IF EXISTS membre_projet CASCADE;
13DROP TABLE IF EXISTS jalon CASCADE;
14DROP TABLE IF EXISTS tache CASCADE;
15DROP TABLE IF EXISTS sous_tache CASCADE;
16DROP TABLE IF EXISTS entree_temps_tache CASCADE;
17DROP TABLE IF EXISTS entree_temps_sous_tache CASCADE;
18DROP TABLE IF EXISTS entree_temps CASCADE;
19DROP TABLE IF EXISTS projet CASCADE;
20DROP TABLE IF EXISTS sondage CASCADE;
21
22CREATE TABLE employe (
23	id SERIAL PRIMARY KEY,
24	prenom VARCHAR(100),
25	nom VARCHAR(100)
26);
27
28CREATE TABLE evenement (
29	id SERIAL PRIMARY KEY,
30	id_responsable_evenement INTEGER NOT NULL,
31	titre VARCHAR(200),
32	description VARCHAR(500),
33	
34	CONSTRAINT id_responsable_evenement_fkey FOREIGN KEY (id_responsable_evenement)
35      REFERENCES employe (id) MATCH SIMPLE
36);
37
38CREATE TABLE statut_participation (
39	id INTEGER PRIMARY KEY,
40	description VARCHAR(200)
41);
42
43CREATE TABLE participation_evenement (
44	id_statut_participation INTEGER NOT NULL,
45	id_evenement INTEGER NOT NULL,
46	id_employe INTEGER NOT NULL,
47	PRIMARY KEY (id_statut_participation, id_evenement, id_employe),
48	
49	CONSTRAINT id_employe_fkey FOREIGN KEY (id_employe)
50      REFERENCES employe (id) MATCH SIMPLE,
51	
52	CONSTRAINT id_evenement_fkey FOREIGN KEY (id_evenement)
53      REFERENCES evenement (id) MATCH SIMPLE,
54	
55	CONSTRAINT id_statut_participation_fkey FOREIGN KEY (id_statut_participation)
56      REFERENCES statut_participation (id) MATCH SIMPLE
57);
58
59CREATE TABLE sondage (
60	id SERIAL PRIMARY KEY,
61	id_employe_responsable INTEGER NOT NULL,
62	titre VARCHAR(200),
63	description VARCHAR(200),
64	
65	CONSTRAINT id_employe_responsable_fkey FOREIGN KEY (id_employe_responsable)
66      REFERENCES employe (id) MATCH SIMPLE
67);
68
69CREATE TABLE droit_acces_sondage (
70	id_sondage INTEGER NOT NULL,
71	id_employe INTEGER NOT NULL,
72	PRIMARY KEY (id_sondage, id_employe),
73	
74	CONSTRAINT id_employe_fkey FOREIGN KEY (id_employe)
75      REFERENCES employe (id) MATCH SIMPLE,
76	
77	CONSTRAINT id_sondage_fkey FOREIGN KEY (id_sondage)
78      REFERENCES sondage (id) MATCH SIMPLE
79);
80
81CREATE TABLE question (
82	id SERIAL PRIMARY KEY,
83	question VARCHAR(500)
84);
85
86CREATE TABLE question_sondage (
87	id_question INTEGER NOT NULL,
88	id_sondage INTEGER NOT NULL,
89	PRIMARY KEY (id_question, id_sondage)
90);
91
92CREATE TABLE reponse_question_sondage (
93	id_employe INTEGER NOT NULL,
94	id_question INTEGER NOT NULL,
95	id_sondage INTEGER NOT NULL,
96	reponse VARCHAR(500),
97	
98	CONSTRAINT id_employe_fkey FOREIGN KEY (id_employe)
99      REFERENCES employe (id) MATCH SIMPLE,
100	
101	CONSTRAINT id_sondage_fkey FOREIGN KEY (id_sondage)
102      REFERENCES sondage (id) MATCH SIMPLE,
103	
104	CONSTRAINT id_question_fkey FOREIGN KEY (id_question)
105      REFERENCES question (id) MATCH SIMPLE
106);
107
108CREATE TABLE departement (
109	id SERIAL PRIMARY KEY,
110	description VARCHAR(500)
111);
112
113CREATE TABLE membre_departement (
114	id_employe INTEGER NOT NULL,
115	id_departement INTEGER NOT NULL,
116	PRIMARY KEY (id_employe, id_departement),
117	
118	CONSTRAINT id_employe_fkey FOREIGN KEY (id_employe)
119      REFERENCES employe (id) MATCH SIMPLE,
120	
121	CONSTRAINT id_departement_fkey FOREIGN KEY (id_departement)
122      REFERENCES departement (id) MATCH SIMPLE
123);
124
125CREATE TABLE responsable_departement (
126	id_employe INTEGER NOT NULL,
127	id_departement INTEGER NOT NULL,
128	PRIMARY KEY (id_employe, id_departement),
129	
130	CONSTRAINT id_employe_fkey FOREIGN KEY (id_employe)
131      REFERENCES employe (id) MATCH SIMPLE,
132	
133	CONSTRAINT id_departement_fkey FOREIGN KEY (id_departement)
134      REFERENCES departement (id) MATCH SIMPLE
135);
136
137CREATE TABLE membre_projet (
138	id_employe INTEGER NOT NULL,
139	id_projet INTEGER NOT NULL,
140	PRIMARY KEY (id_employe, id_projet),
141	
142	CONSTRAINT id_employe_fkey FOREIGN KEY (id_employe)
143      REFERENCES employe (id) MATCH SIMPLE,
144	
145	CONSTRAINT id_projet_fkey FOREIGN KEY (id_projet)
146      REFERENCES projet (id) MATCH SIMPLE
147);