· 6 years ago · Mar 21, 2019, 08:44 AM
1--
2-- PostgreSQL database dump
3--
4
5SET statement_timeout = 0;
6SET client_encoding = 'UTF8';
7SET standard_conforming_strings = on;
8SET check_function_bodies = false;
9SET client_min_messages = warning;
10
11--
12-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
13--
14
15CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
16
17
18--
19-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
20--
21
22COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
23
24
25SET search_path = public, pg_catalog;
26
27SET default_tablespace = '';
28
29SET default_with_oids = false;
30
31--
32-- Name: assistant; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
33--
34
35CREATE TABLE assistant (
36 empid integer NOT NULL,
37 name character varying(30) NOT NULL,
38 area character varying(30),
39 boss integer
40);
41
42
43ALTER TABLE public.assistant OWNER TO postgres;
44
45--
46-- Name: course; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
47--
48
49CREATE TABLE course (
50 courseid integer NOT NULL,
51 title character varying(30),
52 ects integer,
53 taughtby integer
54);
55
56
57ALTER TABLE public.course OWNER TO postgres;
58
59--
60-- Name: grades; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
61--
62
63CREATE TABLE grades (
64 studid integer NOT NULL,
65 courseid integer NOT NULL,
66 empid integer NOT NULL,
67 grade numeric(2,1),
68 CONSTRAINT grades_grade_check CHECK (((grade >= 0.7) AND (grade <= 5.0)))
69);
70
71
72ALTER TABLE public.grades OWNER TO postgres;
73
74--
75-- Name: professor; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
76--
77
78CREATE TABLE professor (
79 empid integer NOT NULL,
80 name character varying(30) NOT NULL,
81 rank character(2),
82 office integer NOT NULL,
83 CONSTRAINT professor_rank_check CHECK ((rank = ANY (ARRAY['C2'::bpchar, 'C3'::bpchar, 'C4'::bpchar])))
84);
85
86
87ALTER TABLE public.professor OWNER TO postgres;
88
89--
90-- Name: requires; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
91--
92
93CREATE TABLE requires (
94 predecessor integer NOT NULL,
95 successor integer NOT NULL
96);
97
98
99ALTER TABLE public.requires OWNER TO postgres;
100
101--
102-- Name: student; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
103--
104
105CREATE TABLE student (
106 studid integer NOT NULL,
107 name character varying(30) NOT NULL,
108 semester integer
109);
110
111
112ALTER TABLE public.student OWNER TO postgres;
113
114--
115-- Name: takes; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
116--
117
118CREATE TABLE takes (
119 studid integer NOT NULL,
120 courseid integer NOT NULL
121);
122
123
124ALTER TABLE public.takes OWNER TO postgres;
125
126--
127-- Name: test; Type: VIEW; Schema: public; Owner: postgres
128--
129
130CREATE VIEW test AS
131 SELECT grades.studid, grades.courseid, grades.empid, grades.grade FROM grades;
132
133
134ALTER TABLE public.test OWNER TO postgres;
135
136--
137-- Data for Name: assistant; Type: TABLE DATA; Schema: public; Owner: postgres
138--
139
140COPY assistant (empid, name, area, boss) FROM stdin;
1413002 Platon Ideology 2125
1423003 Aristoteles Syllogistics 2125
1433004 Wittgenstein Language Theory 2126
1443005 Rhetikus Planetary Motion 2127
1453006 Newton Kepler's laws 2127
1463007 Spinoza God and Nature 2134
147\.
148
149
150--
151-- Data for Name: course; Type: TABLE DATA; Schema: public; Owner: postgres
152--
153
154COPY course (courseid, title, ects, taughtby) FROM stdin;
1555001 Basics 4 2137
1565041 Ethics 4 2125
1575043 Theory of Cognition 3 2126
1585049 DBS 2 2125
1594052 Logics 4 2125
1605052 Theory of Science 3 2126
1615216 Bioethics 2 2126
1625259 Advanced Algorithms 2 2133
1635022 Belief and Knowledge 2 2134
1644630 Constructive Criticism 4 2137
165\.
166
167
168--
169-- Data for Name: grades; Type: TABLE DATA; Schema: public; Owner: postgres
170--
171
172COPY grades (studid, courseid, empid, grade) FROM stdin;
17328106 5001 2126 1.0
17425403 5041 2125 2.0
17527550 4630 2137 2.0
176\.
177
178
179--
180-- Data for Name: professor; Type: TABLE DATA; Schema: public; Owner: postgres
181--
182
183COPY professor (empid, name, rank, office) FROM stdin;
1842125 Socrates C4 226
1852126 Russel C4 232
1862127 Kopernikus C3 310
1872133 Popper C3 52
1882134 Augustinus C3 309
1892136 Curie C4 36
1902137 Kant C4 7
191\.
192
193
194--
195-- Data for Name: requires; Type: TABLE DATA; Schema: public; Owner: postgres
196--
197
198COPY requires (predecessor, successor) FROM stdin;
1995001 5041
2005001 5043
2015001 5049
2025041 5216
2035043 5052
2045041 5052
2055052 5259
206\.
207
208
209--
210-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: postgres
211--
212
213COPY student (studid, name, semester) FROM stdin;
21424002 Xenokrates 18
21525403 Jonas 12
21626120 Fichte 10
21726830 Aristoxenos 8
21827550 Schopenhauer 6
21928106 Carnap 3
22029120 Theophrastos 2
22129555 Feuerbach 2
222\.
223
224
225--
226-- Data for Name: takes; Type: TABLE DATA; Schema: public; Owner: postgres
227--
228
229COPY takes (studid, courseid) FROM stdin;
23026120 5001
23127550 5001
23227550 4052
23328106 5041
23428106 5052
23528106 5216
23628106 5259
23729120 5001
23829120 5041
23929120 5049
24029555 5022
24125403 5022
24229555 5001
243\.
244
245
246--
247-- Name: assistant_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
248--
249
250ALTER TABLE ONLY assistant
251 ADD CONSTRAINT assistant_pkey PRIMARY KEY (empid);
252
253
254--
255-- Name: course_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
256--
257
258ALTER TABLE ONLY course
259 ADD CONSTRAINT course_pkey PRIMARY KEY (courseid);
260
261
262--
263-- Name: grades_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
264--
265
266ALTER TABLE ONLY grades
267 ADD CONSTRAINT grades_pkey PRIMARY KEY (studid, courseid);
268
269
270--
271-- Name: professor_office_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
272--
273
274ALTER TABLE ONLY professor
275 ADD CONSTRAINT professor_office_key UNIQUE (office);
276
277
278--
279-- Name: professor_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
280--
281
282ALTER TABLE ONLY professor
283 ADD CONSTRAINT professor_pkey PRIMARY KEY (empid);
284
285
286--
287-- Name: requires_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
288--
289
290ALTER TABLE ONLY requires
291 ADD CONSTRAINT requires_pkey PRIMARY KEY (predecessor, successor);
292
293
294--
295-- Name: student_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
296--
297
298ALTER TABLE ONLY student
299 ADD CONSTRAINT student_pkey PRIMARY KEY (studid);
300
301
302--
303-- Name: takes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
304--
305
306ALTER TABLE ONLY takes
307 ADD CONSTRAINT takes_pkey PRIMARY KEY (studid, courseid);
308
309
310--
311-- Name: pk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
312--
313
314CREATE INDEX pk ON assistant USING btree (empid);
315
316ALTER TABLE assistant CLUSTER ON pk;
317
318
319--
320-- Name: prof index; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
321--
322
323CREATE INDEX "prof index" ON professor USING btree (empid, name);
324
325ALTER TABLE professor CLUSTER ON "prof index";
326
327
328--
329-- Name: assistant_boss_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
330--
331
332ALTER TABLE ONLY assistant
333 ADD CONSTRAINT assistant_boss_fkey FOREIGN KEY (boss) REFERENCES professor(empid);
334
335
336--
337-- Name: course_taughtby_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
338--
339
340ALTER TABLE ONLY course
341 ADD CONSTRAINT course_taughtby_fkey FOREIGN KEY (taughtby) REFERENCES professor(empid);
342
343
344--
345-- Name: grades_courseid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
346--
347
348ALTER TABLE ONLY grades
349 ADD CONSTRAINT grades_courseid_fkey FOREIGN KEY (courseid) REFERENCES course(courseid);
350
351
352--
353-- Name: grades_empid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
354--
355
356ALTER TABLE ONLY grades
357 ADD CONSTRAINT grades_empid_fkey FOREIGN KEY (empid) REFERENCES professor(empid);
358
359
360--
361-- Name: grades_studid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
362--
363
364ALTER TABLE ONLY grades
365 ADD CONSTRAINT grades_studid_fkey FOREIGN KEY (studid) REFERENCES student(studid) ON DELETE CASCADE;
366
367
368--
369-- Name: requires_predecessor_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
370--
371
372ALTER TABLE ONLY requires
373 ADD CONSTRAINT requires_predecessor_fkey FOREIGN KEY (predecessor) REFERENCES course(courseid) ON DELETE CASCADE;
374
375
376--
377-- Name: requires_successor_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
378--
379
380ALTER TABLE ONLY requires
381 ADD CONSTRAINT requires_successor_fkey FOREIGN KEY (successor) REFERENCES course(courseid) ON DELETE CASCADE;
382
383
384--
385-- Name: takes_courseid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
386--
387
388ALTER TABLE ONLY takes
389 ADD CONSTRAINT takes_courseid_fkey FOREIGN KEY (courseid) REFERENCES course(courseid) ON DELETE CASCADE;
390
391
392--
393-- Name: takes_studid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
394--
395
396ALTER TABLE ONLY takes
397 ADD CONSTRAINT takes_studid_fkey FOREIGN KEY (studid) REFERENCES student(studid) ON DELETE CASCADE;
398
399
400--
401-- Name: public; Type: ACL; Schema: -; Owner: postgres
402--
403
404REVOKE ALL ON SCHEMA public FROM PUBLIC;
405REVOKE ALL ON SCHEMA public FROM postgres;
406GRANT ALL ON SCHEMA public TO postgres;
407GRANT ALL ON SCHEMA public TO PUBLIC;
408
409
410--
411-- PostgreSQL database dump complete
412--