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