t48Sywee

1drop table if exists etablissement;
2drop table if exists prof;
3drop table if exists niveau;
4drop table if exists eleve;
5drop table if exists exercice;
6drop table devoir;
7drop table notion;
8drop table contenu;
9drop table passage;
10
11create table tmp.ETABLISSEMENT(
12  RNE varchar(8),
13  NOM varchar(50) not null,
14  LOCALITE varchar(30),
15  constraint cp_etab Primary Key (RNE)
16);
17
18create table tmp.PROF(
19  IDP integer not null,
20  NOM varchar(20) not null,
21  ANNEE_NAISSANCE integer,
22  RNE varchar(8),
23  VILLE varchar(30),
24  constraint cp_prof Primary Key (IDP),
25  constraint ce_prof FOREIGN KEY (RNE) REFERENCES tmp.ETABLISSEMENT(RNE)
26);
27
28create table tmp.NIVEAU(
29  NIVEAU varchar(4),
30  constraint cp_niveau Primary Key (NIVEAU),
31  constraint chk_niveau check (niveau in ('6eme','5eme','4eme','3eme'))
32);
33
34create table tmp.ELEVE(
35  IDEL integer not null,
36  NOM varchar(50) not null,
37  PRENOM varchar(30),
38  COLLEGE varchar(8),
39  NIVEAU varchar(4),
40  constraint cp_eleve Primary Key (IDEL),
41  constraint ce1_eleve FOREIGN KEY (COLLEGE) REFERENCES tmp.ETABLISSEMENT(RNE),
42  constraint ce2_eleve FOREIGN KEY (NIVEAU) REFERENCES tmp.NIVEAU(NIVEAU)
43);
44
45create table tmp.EXERCICE(
46  IDEX integer not null,
47  CONTENU varchar(250),
48  NIVEAU varchar(4),
49  PROPRIETAIRE integer,
50  DATE_CREATION date,
51  constraint cp_exercice primary key (IDEX),
52  constraint ce1_exercice FOREIGN KEY (NIVEAU) REFERENCES tmp.NIVEAU(NIVEAU),
53  constraint ce2_exercice FOREIGN KEY (PROPRIETAIRE) REFERENCES tmp.PROF(IDP)
54);
55
56create table tmp.DEVOIR(
57  IDD integer not null,
58  DATE_CREATION date,
59  PROF_CREATEUR integer not null,
60  NIVEAU varchar(4),
61  constraint cp_devoir primary key (IDD),
62  constraint ce1_devoir FOREIGN KEY (PROF_CREATEUR) REFERENCES tmp.PROF(IDP),
63  constraint ce2_devoir FOREIGN KEY (NIVEAU) REFERENCES tmp.NIVEAU(NIVEAU)
64);
65
66create table tmp.NOTION(
67  IDEXO integer,
68  NOTION varchar(20),
69  constraint cp_notion primary key (IDEXO)
70);
71
72
73create table tmp.CONTENU(
74  IDD integer,
75  IDEX integer,
76  BAREME real,
77  constraint cp_contenu primary key (IDD,IDEX)
78);
79
80create table tmp.PASSAGE(
81  IDD integer,
82  IDEL integer,
83  DATE_PASSAGE date,
84  NOTE integer,
85  constraint cp_passage primary key (IDD,IDEL,DATE_PASSAGE)
86);
87
88
89set local datestyle to ISO, dmy;
90delete from etablissement;
91delete from prof;
92delete from niveau;
93delete from eleve;
94delete from exercice;
95delete from devoir;
96delete from notion;
97delete from contenu;
98delete from passage;
99
100insert into tmp.Etablissement values ('0341278E','College Arthur Rimbaud','Montpellier');
101insert into tmp.Etablissement values ('0341280G','College Les Pins','Castries');
102insert into tmp.Etablissement values ('0340008Z','College Victor Hugo','Bessan');
103insert into tmp.Etablissement values ('0301094B','College Diderot','Nimes');
104
105
106insert into tmp.Prof values (1,'James Bond',1972,'0341278E','Montpellier');
107insert into tmp.Prof values (2,'Bioman',1970,'0341278E','Lunel');
108insert into tmp.Prof values (3,'Superman',1978,'0341280G','Castries');
109insert into tmp.Prof values (4,'Batman',1972,'0340008Z','Juvignac');
110insert into tmp.Prof values (5,'Hulk',1979,'0340008Z','Montpellier');
111insert into tmp.Prof values (6,'Wonder Woman',1983,'0341280G','Montpellier');
112insert into tmp.Prof values (7,'Tintin',1974,'0340008Z','Grabels');
113insert into tmp.Prof values (8,'Zorro',1972,'0340008Z','Castries');
114
115insert into tmp.Niveau values('6eme');
116insert into tmp.Niveau values('5eme');
117insert into tmp.Niveau values('4eme');
118insert into tmp.Niveau values('3eme');
119
120insert into tmp.Eleve values (1,'RAMI','ALEXANDRE','0341278E','6eme');
121insert into tmp.Eleve values (2,'DUPONT','MAXIME','0340008Z','4eme');
122insert into tmp.Eleve values (3,'DUPONT','ARNAUD','0341278E','6eme');
123insert into tmp.Eleve values (4,'VIGROS','HELENE','0341278E','6eme');
124insert into tmp.Eleve values (5,'GOMAZ','GILLE','0341280G','5eme');
125insert into tmp.Eleve values (6,'CHOUMIN','KHALIL','0340008Z','4eme');
126insert into tmp.Eleve values (7,'MONTEO','ILONA','0340008Z','3eme');
127insert into tmp.Eleve values (8,'MONTEO','MILAN','0340008Z','5eme');
128
129insert into tmp.Exercice values (1,'Calculer 2+3','6eme',2,'08/02/2019');
130insert into tmp.Exercice values (2,'Developper 2(3x+5)','5eme',1,'07/01/2019');
131insert into tmp.Exercice values (3,'Calculer (-3)-(2)','5eme',2,'10/01/2019');
132insert into tmp.Exercice values (4,'Calculer la médiane de la série suivante : 12 - 14 - 15 - 19 -22 -25','5eme',1,'08/02/2019');
133insert into tmp.Exercice values (5,'Tracer un triangle equilatéral de 6 cm de côte','6eme',3,'08/10/2018');
134insert into tmp.Exercice values (6,'Calculer la longueur du segment [AB]','4eme',2,'10/11/2018');
135insert into tmp.Exercice values (7,'Tracer l''image de A par la translation qui transforme B en C','4eme',2,'10/11/2018');
136insert into tmp.Exercice values (8,'Simplifier l''expression suivante puis exprimer son double en fonction de x','4eme',2,'10/11/2018');
137insert into tmp.Exercice values (9,'Calculer le triple de 9','6eme',2,'12/01/2019');
138insert into tmp.Exercice values (10,'Effectuer les calculs suivants : 8 + 3 * 5 ; 17 - 3*2','6eme',5,'14/01/2019');
139insert into tmp.Exercice values (11,'Developper l''expression suivante : (2x-5)²','3eme',5,'19/01/2019');
140
141
142insert into tmp.Devoir values (1,'14/02/2019',1,'6eme');
143insert into tmp.Devoir values (2,'17/02/2019',1,'5eme');
144insert into tmp.Devoir values (3,'17/02/2019',2,'4eme');
145insert into tmp.Devoir values (4,'18/02/2019',1,'6eme');
146insert into tmp.Devoir values (5,'20/02/2019',2,'5eme');
147insert into tmp.Devoir values (6,'21/02/2019',3,'4eme');
148insert into tmp.Devoir values (7,'21/02/2019',8,'4eme');
149insert into tmp.Devoir values (8,'26/02/2019',4,'3eme');
150insert into tmp.Devoir values (9,'28/02/2019',2,'3eme');
151insert into tmp.Devoir values (10,'28/02/2019',5,'3eme');
152
153insert into tmp.Notion values (1,'calcul numerique');
154insert into tmp.Notion values (2,'calcul litteral');
155insert into tmp.Notion values (3,'calcul numerique');
156insert into tmp.Notion values (4,'calcul litteral');
157insert into tmp.Notion values (5,'geometrie');
158insert into tmp.Notion values (6,'geometrie');
159insert into tmp.Notion values (7,'geometrie');
160insert into tmp.Notion values (8,'calcul litteral');
161insert into tmp.Notion values (9,'calcul numerique');
162insert into tmp.Notion values (10,'calcul numerique');
163insert into tmp.Notion values (11,'calcul litteral');
164
165insert into tmp.Contenu values (1,1,6);
166insert into tmp.Contenu values (1,3,4);
167
168insert into tmp.Contenu values (2,2,5);
169insert into tmp.Contenu values (2,4,2);
170
171insert into tmp.Contenu values (3,6,3);
172insert into tmp.Contenu values (3,7,4);
173insert into tmp.Contenu values (3,8,3);
174
175insert into tmp.Contenu values (4,1,1);
176insert into tmp.Contenu values (4,5,2);
177insert into tmp.Contenu values (4,9,1);
178
179insert into tmp.Contenu values (5,1,2);
180insert into tmp.Contenu values (5,3,5);
181insert into tmp.Contenu values (5,6,5);
182insert into tmp.Contenu values (5,2,3);
183insert into tmp.Contenu values (5,11,5);
184
185insert into tmp.Contenu values (6,1,3);
186insert into tmp.Contenu values (6,2,6);
187insert into tmp.Contenu values (6,4,5);
188insert into tmp.Contenu values (6,8,2);
189
190insert into tmp.Contenu values (7,2,3);
191insert into tmp.Contenu values (7,5,2);
192
193insert into tmp.Contenu values (8,5,2);
194insert into tmp.Contenu values (8,2,2);
195insert into tmp.Contenu values (8,7,2);
196insert into tmp.Contenu values (8,6,2);
197insert into tmp.Contenu values (8,11,2);
198
199insert into tmp.Contenu values (9,2,2);
200insert into tmp.Contenu values (9,4,2);
201insert into tmp.Contenu values (9,8,2);
202insert into tmp.Contenu values (9,11,4);
203
204insert into tmp.Contenu values (10,1,2);
205insert into tmp.Contenu values (10,3,2);
206insert into tmp.Contenu values (10,6,2);
207insert into tmp.Contenu values (10,9,2);
208insert into tmp.Contenu values (10,10,2);
209insert into tmp.Contenu values (10,4,2);
210
211
212insert into tmp.PASSAGE values (1,1,'15/02/2019',5);
213insert into tmp.PASSAGE values (1,3,'15/02/2019',6);
214insert into tmp.PASSAGE values (1,4,'15/02/2019',4);
215insert into tmp.PASSAGE values (2,5,'18/02/2019',5);
216insert into tmp.PASSAGE values (2,2,'18/02/2019',6);
217insert into tmp.PASSAGE values (3,1,'20/02/2019',8);
218insert into tmp.PASSAGE values (3,2,'20/02/2019',8);
219insert into tmp.PASSAGE values (3,6,'20/02/2019',7);
220insert into tmp.PASSAGE values (4,3,'23/02/2019',3);
221insert into tmp.PASSAGE values (5,5,'26/02/2019',14);
222insert into tmp.PASSAGE values (6,4,'28/02/2019',11);
223insert into tmp.PASSAGE values (7,3,'22/02/2019',3);
224insert into tmp.PASSAGE values (8,4,'27/02/2019',11);
225insert into tmp.PASSAGE values (9,4,'01/03/2019',11);
226insert into tmp.PASSAGE values (10,3,'28/02/2019',11);
227
228commit;