· 7 years ago · Sep 18, 2018, 12:42 PM
1-- optional: vorhandenes Schema komplett loeschen
2
3--DROP SCHEMA public CASCADE;
4--CREATE SCHEMA public;
5--GRANT ALL ON SCHEMA public TO postgres;
6--GRANT ALL ON SCHEMA public TO public;
7
8-- namensgleiche Tabellen entsorgen
9
10DROP TABLE IF EXISTS person, studi, adresse, wohnt, buch, exemplar, leiht_aus,
11 autor, zimmer, lehrender, professor, lehrbeauftragter,
12 lehrkraft, lv, haelt, empfiehlt, besucht CASCADE;
13
14-- Tabellen anlegen
15
16SET DATESTYLE TO German;
17
18CREATE TABLE studi
19(matrnr CHAR(7) PRIMARY KEY,
20name VARCHAR(30) NOT NULL,
21vorname VARCHAR(20) NOT NULL,
22gebdat DATE NOT NULL,
23geschlecht CHAR(1) NOT NULL,
24CHECK (geschlecht IN ('m','w','x')),
25urlaubssem DECIMAL(1,0) NOT NULL
26CHECK (urlaubssem BETWEEN 0 AND 2),
27semester DECIMAL(2,0) NOT NULL
28CHECK (semester BETWEEN 1 AND 50),
29studiengang CHAR(2) NOT NULL
30CHECK (studiengang IN ('MI','PI','TI','WI')));
31
32CREATE TABLE adresse
33(strasse VARCHAR(30),
34nr VARCHAR(10),
35plz VARCHAR(5),
36ort VARCHAR(30) NOT NULL,
37PRIMARY KEY (strasse,nr,plz));
38
39CREATE TABLE wohnt
40(matrnr CHAR(7) REFERENCES studi,
41strasse VARCHAR(30),
42nr VARCHAR(10),
43plz VARCHAR(5),
44PRIMARY KEY(matrnr,strasse,nr,plz),
45FOREIGN KEY(strasse,nr,plz)
46REFERENCES adresse(strasse,nr,plz));
47
48CREATE TABLE buch
49(isbn VARCHAR(20) PRIMARY KEY,
50titelblatt BYTEA,
51bildgroesse VARCHAR(20),
52bildformat VARCHAR(10),
53titel VARCHAR(100) NOT NULL,
54text VARCHAR(1000),
55video BYTEA,
56videogroesse VARCHAR(20),
57videoformat VARCHAR(10),
58seitenanzahl DECIMAL(4,0) NOT NULL CHECK(seitenanzahl>0),
59exemplare DECIMAL(3,0) NOT NULL CHECK(exemplare>=1),
60leihfrist DECIMAL(5,0) NOT NULL);
61
62CREATE TABLE exemplar
63(exemplarnr VARCHAR(2),
64isbn VARCHAR(20) REFERENCES buch,
65leihart CHAR(1)NOT NULL
66CHECK (leihart IN ('K','N','P')),
67PRIMARY KEY(exemplarnr,isbn));
68
69CREATE TABLE leiht_aus
70(matrnr CHAR(7) REFERENCES studi,
71exemplarnr VARCHAR(2),
72isbn VARCHAR(20),
73ausleihdatum DATE NOT NULL,
74rueckgabedatum DATE NOT NULL,
75PRIMARY KEY(matrnr,exemplarnr,isbn),
76FOREIGN KEY(exemplarnr,isbn) REFERENCES exemplar(exemplarnr,isbn),
77CONSTRAINT exemplar_bereits_ausgeliehen UNIQUE(exemplarnr,isbn));
78
79CREATE TABLE person
80(name VARCHAR(30),
81vorname VARCHAR(20),
82PRIMARY KEY (name,vorname));
83
84CREATE TABLE autor
85(isbn VARCHAR(20) REFERENCES buch,
86position DECIMAL(2,0),
87name VARCHAR(30) NOT NULL,
88vorname VARCHAR(20) NOT NULL,
89PRIMARY KEY(isbn,position),
90FOREIGN KEY(name,vorname) REFERENCES
91person(name,vorname));
92
93CREATE TABLE zimmer
94(raumnr VARCHAR(5),
95gebnr VARCHAR(5),
96PRIMARY KEY (raumnr,gebnr));
97
98CREATE TABLE lehrender
99(persnr CHAR(7) PRIMARY KEY,
100name VARCHAR(30) NOT NULL,
101vorname VARCHAR(20) NOT NULL,
102telnr VARCHAR(15) NOT NULL,
103fach VARCHAR(30) NOT NULL,
104raumnr VARCHAR(5) NOT NULL,
105gebnr VARCHAR(5) NOT NULL,
106FOREIGN KEY (raumnr,gebnr) REFERENCES
107zimmer(raumnr,gebnr));
108
109CREATE TABLE professor
110(persnr CHAR(7) REFERENCES lehrender PRIMARY KEY,
111besoldungsgruppe VARCHAR(3) NOT NULL
112CHECK (besoldungsgruppe IN ('C2','C3','C4','W1','W2','W3')));
113
114CREATE TABLE lehrbeauftragter
115(persnr CHAR(7) REFERENCES lehrender PRIMARY KEY,
116sws DECIMAL(3,0),
117stufe VARCHAR(3)
118CHECK (stufe IN ('Uni','FH')));
119
120CREATE TABLE lehrkraft
121(persnr CHAR(7) REFERENCES lehrender PRIMARY KEY,
122verguetungsgruppe VARCHAR(10));
123
124CREATE TABLE lv
125(lvnr CHAR(4) PRIMARY KEY,
126bezeichnung VARCHAR(30) NOT NULL,
127dauer DECIMAL(1,0) NOT NULL CHECK (dauer BETWEEN 1 AND 4),
128art VARCHAR(2) NOT NULL
129CHECK (art IN ('VL','L')));
130
131CREATE TABLE haelt
132(lvnr CHAR(4) REFERENCES lv,
133persnr CHAR(7) REFERENCES lehrender,
134PRIMARY KEY(lvnr,persnr));
135
136CREATE TABLE empfiehlt
137(persnr CHAR(7) REFERENCES lehrender,
138isbn VARCHAR(20) REFERENCES buch,
139PRIMARY KEY(persnr,isbn));
140
141CREATE TABLE besucht
142(matrnr CHAR(7) REFERENCES studi,
143lvnr CHAR(4) REFERENCES lv,
144Note CHAR(3)
145CHECK (Note IN ('1.0','1.3','1.7','2.0',
146'2.3','2.7','3.0','3.3','3.7','4.0','5.0')),
147PRIMARY KEY(matrnr,lvnr));
148
149-- Tupel der Relation adresse
150INSERT INTO adresse
151VALUES('Ohlhofbreite','14','38642','Goslar');
152INSERT INTO adresse
153VALUES('Neuer Weg','22','38302','Wolfenbuettel');
154INSERT INTO adresse
155VALUES('Rheinring','12','31224','Peine');
156INSERT INTO adresse
157VALUES('Moorkamp','13','31224','Peine');
158INSERT INTO adresse
159VALUES('Bergfeld','47a','38239','Salzgitter');
160INSERT INTO adresse
161VALUES('Am Markt','45','29556','Suderburg');
162INSERT INTO adresse
163VALUES('Rudolfplatz','23b','38502','Braunschweig');
164INSERT INTO adresse
165VALUES('Kreuzstrasse','56','38840','Wolfsburg');
166INSERT INTO adresse
167VALUES('Bueltenweg','345','38234','Lueneburg');
168INSERT INTO adresse
169VALUES('Fasanenkamp','9','44001','Peine');
170INSERT INTO adresse
171VALUES('Ackerweg','110','38302','Wolfenbuettel');
172INSERT INTO adresse
173VALUES('Berlinerstrasse','67','29556','Suderburg');
174INSERT INTO adresse
175VALUES('Theaterwall','101','38498','Stoeckheim');
176
177
178-- Tupel der Relation buch
179INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
180VALUES('3802551230','SQL',22,2,15);
181INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
182VALUES('3499225085','Einfuehrung in die Informatik',347,4,30);
183INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
184VALUES('3100101065','UML',362,3,30);
185INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
186VALUES('3451280000','Latex - Das Standardwerk',1863,5,60);
187INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
188VALUES('3423101776','Analysis I',236,5,30);
189INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
190VALUES('3453186834','Analysis II',256,3,30);
191INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
192VALUES('3423202777','Datenbanksysteme',331,3,30);
193INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
194VALUES('3453140982','Business English',102,3,30);
195INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
196VALUES('3897212013','Perl 5 kurz und gut',70,3,30);
197INSERT INTO buch(isbn,titel,seitenanzahl,exemplare, leihfrist)
198VALUES('3897212188','CGI kurz und gut',104,3,30);
199
200-- Tupel der Relation exemplar
201INSERT INTO exemplar
202VALUES('1','3423101776','N');
203INSERT INTO exemplar
204VALUES('1','3453186834','P');
205INSERT INTO exemplar
206VALUES('1','3423202777','K');
207INSERT INTO exemplar
208VALUES('1','3453140982','N');
209INSERT INTO exemplar
210VALUES('2','3453186834','N');
211INSERT INTO EXEMPLAR
212VALUES('1','3897212188','P');
213
214-- Tupel der Relation lv
215INSERT INTO lv
216VALUES('111','Mathematik I',3,'VL');
217INSERT INTO lv
218VALUES('112','Mathematik II',3,'VL');
219INSERT INTO lv
220VALUES('113','Physik',3,'VL');
221INSERT INTO lv
222VALUES('122','BWL',4,'VL');
223INSERT INTO lv
224VALUES('123','Informatik',4,'VL');
225INSERT INTO lv
226VALUES('124','Mediendesign',4,'VL');
227INSERT INTO lv
228VALUES('125','Labor Informatik ',2,'L');
229INSERT INTO lv
230VALUES('126','Labor Datenbanken',2,'L');
231INSERT INTO lv
232VALUES('127','Labor Elektrotechnik',2,'L');
233INSERT INTO lv
234VALUES('161','Marketing',2,'VL');
235INSERT INTO lv
236VALUES('411','Datenbanken',4,'VL');
237INSERT INTO lv
238VALUES('521','Datenbanksysteme',2,'VL');
239
240-- Tupel der Relation person
241INSERT INTO person
242VALUES('Meier','F.');
243INSERT INTO person
244VALUES('Kunze','Peter');
245INSERT INTO person
246VALUES('Schmidt','Albert');
247INSERT INTO person
248VALUES('unbekannt','unbekannt');
249INSERT INTO person
250VALUES('Ahrends','Erna');
251INSERT INTO person
252VALUES('Herbert','Frank');
253INSERT INTO person
254VALUES('Schulz','Uwe');
255INSERT INTO person
256VALUES('Winter','Kiara');
257INSERT INTO person
258VALUES('Meyer','Johan');
259INSERT INTO person
260VALUES('Pape','Linda');
261
262-- Tupel der Relation studi
263INSERT INTO studi
264VALUES('2358712','Meier','Siegfried','7.10.1996','m',1,5,'PI');
265INSERT INTO studi
266VALUES('1562367','Schulze','Heiner','3.5.1996','m',2,7,'MI');
267INSERT INTO studi
268VALUES('6432753','Koenig','Mathilde','7.10.1991','w',2,12,'PI');
269INSERT INTO studi
270VALUES('7564258','Baum','Meta','12.10.1997','w',0,4,'TI');
271INSERT INTO studi
272VALUES('2356984','Dreier','Magnus','25.2.1996','m',1,8,'TI');
273INSERT INTO studi
274VALUES('5236478','Hesse','Sarah','7.10.1998','w',0,2,'PI');
275INSERT INTO studi
276VALUES('9812964','Meier','Hans','5.12.1999','m',0,1,'PI');
277INSERT INTO studi
278VALUES('9252425','Mueller','Karla','12.10.1997','w',0,5,'TI');
279INSERT INTO studi
280VALUES('9365461','Schmitt','Marc','27.8.1992','m',2,11,'PI');
281INSERT INTO studi
282VALUES('7654321','Mueller','Hans','12.3.1997','m',0,5,'MI');
283INSERT INTO studi
284VALUES('4297531','Mueller','Udo','24.7.1996','m',1,6,'MI');
285INSERT INTO studi
286VALUES('3108642','Meier','Martina','18.11.1992','w',2,12,'PI');
287INSERT INTO studi
288VALUES('1230789','Thiess','Hugo','22.4.1996','m',0,6,'TI');
289INSERT INTO studi
290VALUES('1286385','Zander','Wolfgang','12.10.1996','m',0,6,'TI');
291
292
293-- Tupel der Relation zimmer
294INSERT INTO zimmer
295VALUES('120a','5a');
296INSERT INTO zimmer
297VALUES('20','1');
298INSERT INTO zimmer
299VALUES('12','1');
300INSERT INTO zimmer
301VALUES('34','2');
302INSERT INTO zimmer
303VALUES('28','2');
304INSERT INTO zimmer
305VALUES('67','4');
306INSERT INTO zimmer
307VALUES('220','8');
308INSERT INTO zimmer
309VALUES('64','4');
310INSERT INTO zimmer
311VALUES('300','10');
312INSERT INTO zimmer
313VALUES('123','5b');
314INSERT INTO zimmer
315VALUES('45','3');
316INSERT INTO zimmer
317VALUES('25','2');
318INSERT INTO zimmer
319VALUES('10','1');
320INSERT INTO zimmer
321VALUES('18','1');
322INSERT INTO zimmer
323VALUES('230','8');
324INSERT INTO zimmer
325VALUES('124','5b');
326
327-- Tupel der Relation autor
328INSERT INTO autor
329VALUES('3802551230',1,'Meier','F.');
330INSERT INTO autor
331VALUES('3499225085',2,'Kunze','Peter');
332INSERT INTO autor
333VALUES('3100101065',3,'Schmidt','Albert');
334INSERT INTO autor
335VALUES('3451280000',4,'unbekannt','unbekannt');
336INSERT INTO autor
337VALUES('3423101776',5,'Ahrends','Erna');
338
339-- Tupel der Relation lehrender
340INSERT INTO lehrender
341VALUES('5234260','zimmer','Monika','92345','Mathematik','120a','5a');
342INSERT INTO lehrender
343VALUES('9652425','Irrgang','Rolf','12432','Datenbanken','20','1');
344INSERT INTO lehrender
345VALUES('1234567','Mueller','Hans','3456','Physik','12','1');
346INSERT INTO lehrender
347VALUES('1357924','Mueller','Udo','45367','BWL','34','2');
348INSERT INTO lehrender
349VALUES('2468013','Meier','Martina','28786','Informatik','28','2');
350INSERT INTO lehrender
351VALUES('1133556','Thein','Siegfried','38574','Mathematik','67','4');
352INSERT INTO lehrender
353VALUES('9870321','Schmidt','Manfred','23145','Elektrotechnik','220','8');
354INSERT INTO lehrender
355VALUES('1523345','Schulze','Hans','74625','Mediendesign','64','4');
356INSERT INTO lehrender
357VALUES('2314856','Maier','Franziska','52456','VLSI','300','10');
358INSERT INTO lehrender
359VALUES('3495067','Mueller','August','75362','Marketing','123','5b');
360INSERT INTO lehrender
361VALUES('4526748','Sommer','Michaela','76472','Programmieren','45','3');
362INSERT INTO lehrender
363VALUES('2563172','Rehmer','Franz','27122','Informatik','25','2');
364INSERT INTO lehrender
365VALUES('3649443','Wolitz','Petra','88122','Physik','10','1');
366INSERT INTO lehrender
367VALUES('8511227','Kehr','Wolfgang','12238','Datenbanksysteme','18','1');
368INSERT INTO lehrender
369VALUES('7281128','Wagner','Wilhelm','78222','Elektrotechnik','230','8');
370INSERT INTO lehrender
371VALUES('3975982','Prell','Verena','19784','Marketing','124','5b');
372
373-- Tupel der Relation lehrbeauftragter
374INSERT INTO lehrbeauftragter
375VALUES('9870321',6,'Uni');
376INSERT INTO lehrbeauftragter
377VALUES('1133556',6,'FH');
378INSERT INTO lehrbeauftragter
379VALUES('1523345',6,'Uni');
380INSERT INTO lehrbeauftragter
381VALUES('2314856',6,'FH');
382INSERT INTO lehrbeauftragter
383VALUES('3495067',6,'Uni');
384
385
386-- Tupel der Relation lehrkraft
387INSERT INTO lehrkraft
388VALUES('2563172','13');
389INSERT INTO lehrkraft
390VALUES('3649443','10');
391INSERT INTO lehrkraft
392VALUES('8511227','12');
393INSERT INTO lehrkraft
394VALUES('7281128','13');
395INSERT INTO lehrkraft
396VALUES('3975982','11');
397
398-- Tupel der Relation professor
399INSERT INTO professor
400VALUES('5234260','W2');
401INSERT INTO professor
402VALUES('9652425','W3');
403INSERT INTO professor
404VALUES('1234567','W3');
405INSERT INTO professor
406VALUES('1357924','W2');
407INSERT INTO professor
408VALUES('2468013','W2');
409
410-- Tupel der Relation leiht_aus
411INSERT INTO leiht_aus
412VALUES('1562367','1','3423101776','1.1.2018','30.1.2018');
413INSERT INTO leiht_aus
414VALUES('6432753','1','3453186834','2.1.2018','31.1.2018');
415INSERT INTO leiht_aus
416VALUES('7564258','1','3423202777','2.1.2018','10.1.2018');
417INSERT INTO leiht_aus
418VALUES('2358712','1','3453140982','1.2.2018','27.2.2018');
419INSERT INTO leiht_aus
420VALUES('2358712','2','3453186834','1.1.2018','30.1.2018');
421
422-- Tupel der Relation wohnt
423INSERT INTO wohnt
424VALUES('2358712','Ackerweg','110','38302');
425INSERT INTO wohnt
426VALUES('1562367','Neuer Weg','22','38302');
427INSERT INTO wohnt
428VALUES('6432753','Rheinring','12','31224');
429INSERT INTO wohnt
430VALUES('7564258','Moorkamp','13','31224');
431INSERT INTO wohnt
432VALUES('2356984','Bergfeld','47a','38239');
433
434-- Tupel der Relation besucht
435INSERT INTO besucht(MatrNr,LVNr,Note)
436VALUES('7564258','122','1.0');
437INSERT INTO besucht(MatrNr,LVNr)
438VALUES('2356984','123');
439INSERT INTO besucht(MatrNr,LVNr)
440VALUES('5236478','123');
441INSERT INTO besucht(MatrNr,LVNr)
442VALUES('1562367','123');
443INSERT INTO besucht(MatrNr,LVNr,Note)
444VALUES('5236478','124','3.3');
445INSERT INTO besucht(MatrNr,LVNr,Note)
446VALUES('9812964','125','2.0');
447INSERT INTO besucht(MatrNr,LVNr,Note)
448VALUES('9252425','126','2.0');
449INSERT INTO besucht(MatrNr,LVNr,Note)
450VALUES('6432753','411','5.0');
451INSERT INTO besucht(MatrNr,LVNr)
452VALUES('7564258','521');
453INSERT INTO besucht(MatrNr,LVNr)
454VALUES('1286385','521');
455
456-- Tupel der Relation empfiehlt
457INSERT INTO empfiehlt
458VALUES('5234260','3897212013');
459INSERT INTO empfiehlt
460VALUES('9652425','3897212188');
461INSERT INTO empfiehlt
462VALUES('1234567','3897212013');
463INSERT INTO empfiehlt
464VALUES('1357924','3897212188');
465INSERT INTO empfiehlt
466VALUES('2468013','3897212188');
467INSERT INTO empfiehlt
468VALUES('2468013','3423101776');
469INSERT INTO empfiehlt
470VALUES('4526748','3423101776');
471
472-- Tupel der Relation haelt
473INSERT INTO haelt
474VALUES('111','5234260');
475INSERT INTO haelt
476VALUES('112','1133556');
477INSERT INTO haelt
478VALUES('113','1234567');
479INSERT INTO haelt
480VALUES('122','1357924');
481INSERT INTO haelt
482VALUES('123','2468013');
483INSERT INTO haelt
484VALUES('124','1523345');
485INSERT INTO haelt
486VALUES('125','2563172');
487INSERT INTO haelt
488VALUES('126','9652425');
489INSERT INTO haelt
490VALUES('411','9652425');
491INSERT INTO haelt
492VALUES('521','8511227');