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