· 6 years ago · Aug 14, 2019, 01:52 PM
1-- namensgleiche Tabellen entsorgen
2
3DROP TABLE IF EXISTS person, studi, adresse, wohnt, buch, exemplar, leiht_aus,
4 autor, zimmer, lehrender, professor, lehrbeauftragter,
5 lehrkraft, lv, haelt, empfiehlt, besucht CASCADE;
6
7-- Tabellen anlegen
8
9CREATE TABLE Studi
10 (MatrNr CHAR(7) PRIMARY KEY,
11 Name VARCHAR2(30) NOT NULL,
12 Vorname VARCHAR2(20) NOT NULL,
13 GebDat DATE NOT NULL,
14 Geschlecht CHAR(1) NOT NULL,
15 CHECK (geschlecht IN ('m','w','x')),
16 UrlaubsSem NUMBER(1,0) NOT NULL
17 CHECK (UrlaubsSem BETWEEN 0 AND 2),
18 Semester NUMBER(2,0) NOT NULL
19 CHECK (Semester BETWEEN 1 AND 50),
20 Studiengang CHAR(2) NOT NULL
21 CHECK (Studiengang IN ('MI','PI','TI','WI')));
22
23CREATE TABLE Adresse
24 (Strasse VARCHAR2(30),
25 Nr VARCHAR2(10),
26 PLZ VARCHAR2(5),
27 Ort VARCHAR2(30) NOT NULL,
28 PRIMARY KEY (Strasse,Nr,PLZ));
29
30CREATE TABLE wohnt
31 (MatrNr CHAR(7) REFERENCES Studi,
32 Strasse VARCHAR2(30),
33 Nr VARCHAR2(10),
34 PLZ VARCHAR2(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 VARCHAR2(20) PRIMARY KEY,
41 Titelblatt BLOB,
42 Bildgroesse VARCHAR2(20),
43 Bildformat VARCHAR2(10),
44 Titel VARCHAR2(100) NOT NULL,
45 Text VARCHAR2(1000),
46 Video BLOB,
47 Videogroesse VARCHAR2(20),
48 Videoformat VARCHAR2(10),
49 Seitenanzahl NUMBER(4,0) NOT NULL CHECK(Seitenanzahl>0),
50 Exemplare NUMBER(3,0) NOT NULL CHECK(Exemplare>=1),
51 Leihfrist NUMBER(5,0) NOT NULL);
52
53CREATE TABLE Exemplar
54 (ExemplarNr VARCHAR2(2),
55 ISBN VARCHAR2(20) REFERENCES Buch,
56 Leihart CHAR(1)NOT NULL
57 CHECK (Leihart IN ('K','N','P')),
58 PRIMARY KEY(ExemplarNr,ISBN));
59
60CREATE TABLE leiht_aus
61 (MatrNr CHAR(7) REFERENCES Studi,
62 ExemplarNr VARCHAR2(2),
63 ISBN VARCHAR2(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 VARCHAR2(30),
72 Vorname VARCHAR2(20),
73 PRIMARY KEY (Name,Vorname));
74
75CREATE TABLE Autor
76 (ISBN VARCHAR2(20) REFERENCES Buch,
77 Position NUMBER(2,0),
78 Name VARCHAR2(30) NOT NULL,
79 Vorname VARCHAR2(20) NOT NULL,
80 PRIMARY KEY(ISBN,Position),
81 FOREIGN KEY(Name,Vorname) REFERENCES
82 Person(Name,Vorname));
83
84CREATE TABLE Zimmer
85 (RaumNr VARCHAR2(5),
86 GebNr VARCHAR2(5),
87 PRIMARY KEY (RaumNr,GebNr));
88
89CREATE TABLE Lehrender
90 (PersNr CHAR(7) PRIMARY KEY,
91 Name VARCHAR2(30) NOT NULL,
92 Vorname VARCHAR2(20) NOT NULL,
93 TelNr VARCHAR2(15) NOT NULL,
94 Fach VARCHAR2(30) NOT NULL,
95 RaumNr VARCHAR2(5) NOT NULL,
96 GebNr VARCHAR2(5) NOT NULL,
97 FOREIGN KEY (RaumNr,GebNr) REFERENCES
98 Zimmer(RaumNr,GebNr));
99
100CREATE TABLE Professor
101 (PersNr CHAR(7) REFERENCES Lehrender PRIMARY KEY,
102 Besoldungsgruppe VARCHAR2(3) NOT NULL
103 CHECK (Besoldungsgruppe IN ('C2','C3','C4','W1','W2','W3')));
104
105CREATE TABLE Lehrbeauftragter
106 (PersNr CHAR(7) REFERENCES Lehrender PRIMARY KEY,
107 SWS NUMBER(3,0),
108 Stufe VARCHAR2(3)
109 CHECK (Stufe IN ('Uni','FH')));
110
111CREATE TABLE Lehrkraft
112 (PersNr CHAR(7) REFERENCES Lehrender PRIMARY KEY,
113 Verguetungsgruppe VARCHAR2(10));
114
115CREATE TABLE LV
116 (LVNr CHAR(4) PRIMARY KEY,
117 Bezeichnung VARCHAR2(30) NOT NULL,
118 Dauer NUMBER(1,0) NOT NULL CHECK (Dauer BETWEEN 1 AND 4),
119 Art VARCHAR2(2) NOT NULL
120 CHECK (Art IN ('VL','L')));
121
122CREATE TABLE haelt
123 (LVNr CHAR(4) REFERENCES LV,
124 PersNr CHAR(7) REFERENCES Lehrender,
125 PRIMARY KEY(LVNr,PersNr));
126
127CREATE TABLE empfiehlt
128 (PersNr CHAR(7) REFERENCES Lehrender,
129 ISBN VARCHAR2(20) REFERENCES Buch,
130 PRIMARY KEY(PersNr,ISBN));
131
132CREATE TABLE besucht
133 (MatrNr CHAR(7) REFERENCES Studi,
134 LVNr CHAR(4) REFERENCES LV,
135 Note CHAR(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-- Tupel der Relation Studi
253INSERT INTO Studi
254 VALUES('2358712','Meier','Siegfried',TO_DATE('7.10.1996'),'m',1,5,'PI');
255INSERT INTO Studi
256 VALUES('1562367','Schulze','Heiner',TO_DATE('3.5.1996'),'m',2,7,'MI');
257INSERT INTO Studi
258 VALUES('6432753','Koenig','Mathilde',TO_DATE('7.10.1991'),'w',2,12,'PI');
259INSERT INTO Studi
260 VALUES('7564258','Baum','Meta',TO_DATE('12.10.1997'),'w',0,4,'TI');
261INSERT INTO Studi
262 VALUES('2356984','Dreier','Magnus',TO_DATE('25.2.1996'),'m',1,8,'TI');
263INSERT INTO Studi
264 VALUES('5236478','Hesse','Sarah',TO_DATE('7.10.1998'),'w',0,2,'PI');
265INSERT INTO Studi
266 VALUES('9812964','Meier','Hans',TO_DATE('5.12.1999'),'m',0,1,'PI');
267INSERT INTO Studi
268 VALUES('9252425','Mueller','Karla',TO_DATE('12.10.1997'),'w',0,5,'TI');
269INSERT INTO Studi
270 VALUES('9365461','Schmitt','Marc',TO_DATE('27.8.1992'),'m',2,11,'PI');
271INSERT INTO Studi
272 VALUES('7654321','Mueller','Hans',TO_DATE('12.3.1997'),'m',0,5,'MI');
273INSERT INTO Studi
274 VALUES('4297531','Mueller','Udo',TO_DATE('24.7.1996'),'m',1,6,'MI');
275INSERT INTO Studi
276 VALUES('3108642','Meier','Martina',TO_DATE('18.11.1992'),'w',2,12,'PI');
277INSERT INTO Studi
278 VALUES('1230789','Thiess','Hugo',TO_DATE('22.4.1996'),'m',0,6,'TI');
279INSERT INTO Studi
280 VALUES('1286385','Zander','Wolfgang',TO_DATE('12.10.1996'),'m',0,6,'TI');
281
282-- Tupel der Relation Zimmer
283INSERT INTO Zimmer
284 VALUES('120a','5a');
285INSERT INTO Zimmer
286 VALUES('20','1');
287INSERT INTO Zimmer
288 VALUES('12','1');
289INSERT INTO Zimmer
290 VALUES('34','2');
291INSERT INTO Zimmer
292 VALUES('28','2');
293INSERT INTO Zimmer
294 VALUES('67','4');
295INSERT INTO Zimmer
296 VALUES('220','8');
297INSERT INTO Zimmer
298 VALUES('64','4');
299INSERT INTO Zimmer
300 VALUES('300','10');
301INSERT INTO Zimmer
302 VALUES('123','5b');
303INSERT INTO Zimmer
304 VALUES('45','3');
305INSERT INTO Zimmer
306 VALUES('25','2');
307INSERT INTO Zimmer
308 VALUES('10','1');
309INSERT INTO Zimmer
310 VALUES('18','1');
311INSERT INTO Zimmer
312 VALUES('230','8');
313INSERT INTO Zimmer
314 VALUES('124','5b');
315
316-- Tupel der Relation Autor
317INSERT INTO Autor
318 VALUES('3802551230',1,'Meier','F.');
319INSERT INTO Autor
320 VALUES('3499225085',2,'Kunze','Peter');
321INSERT INTO Autor
322 VALUES('3100101065',3,'Schmidt','Albert');
323INSERT INTO Autor
324 VALUES('3451280000',4,'unbekannt','unbekannt');
325INSERT INTO Autor
326 VALUES('3423101776',5,'Ahrends','Erna');
327
328-- Tupel der Relation Lehrender
329INSERT INTO Lehrender
330 VALUES('5234260','Zimmer','Monika','92345','Mathematik','120a','5a');
331INSERT INTO Lehrender
332 VALUES('9652425','Irrgang','Rolf','12432','Datenbanken','20','1');
333INSERT INTO Lehrender
334 VALUES('1234567','Mueller','Hans','3456','Physik','12','1');
335INSERT INTO Lehrender
336 VALUES('1357924','Mueller','Udo','45367','BWL','34','2');
337INSERT INTO Lehrender
338 VALUES('2468013','Meier','Martina','28786','Informatik','28','2');
339INSERT INTO Lehrender
340 VALUES('1133556','Thein','Siegfried','38574','Mathematik','67','4');
341INSERT INTO Lehrender
342 VALUES('9870321','Schmidt','Manfred','23145','Elektrotechnik','220','8');
343INSERT INTO Lehrender
344 VALUES('1523345','Schulze','Hans','74625','Mediendesign','64','4');
345INSERT INTO Lehrender
346 VALUES('2314856','Maier','Franziska','52456','VLSI','300','10');
347INSERT INTO Lehrender
348 VALUES('3495067','Mueller','August','75362','Marketing','123','5b');
349INSERT INTO Lehrender
350 VALUES('4526748','Sommer','Michaela','76472','Programmieren','45','3');
351INSERT INTO Lehrender
352 VALUES('2563172','Rehmer','Franz','27122','Informatik','25','2');
353INSERT INTO Lehrender
354 VALUES('3649443','Wolitz','Petra','88122','Physik','10','1');
355INSERT INTO Lehrender
356 VALUES('8511227','Kehr','Wolfgang','12238','Datenbanksysteme','18','1');
357INSERT INTO Lehrender
358 VALUES('7281128','Wagner','Wilhelm','78222','Elektrotechnik','230','8');
359INSERT INTO Lehrender
360 VALUES('3975982','Prell','Verena','19784','Marketing','124','5b');
361
362-- Tupel der Relation Lehrbeauftragter
363INSERT INTO Lehrbeauftragter
364 VALUES('9870321',6,'Uni');
365INSERT INTO Lehrbeauftragter
366 VALUES('1133556',6,'FH');
367INSERT INTO Lehrbeauftragter
368 VALUES('1523345',6,'Uni');
369INSERT INTO Lehrbeauftragter
370 VALUES('2314856',6,'FH');
371INSERT INTO Lehrbeauftragter
372 VALUES('3495067',6,'Uni');
373
374-- Tupel der Relation Lehrkraft
375INSERT INTO Lehrkraft
376 VALUES('2563172','13');
377INSERT INTO Lehrkraft
378 VALUES('3649443','10');
379INSERT INTO Lehrkraft
380 VALUES('8511227','12');
381INSERT INTO Lehrkraft
382 VALUES('7281128','13');
383INSERT INTO Lehrkraft
384 VALUES('3975982','11');
385
386-- Tupel der Relation Professor
387INSERT INTO Professor
388 VALUES('5234260','W2');
389INSERT INTO Professor
390 VALUES('9652425','W3');
391INSERT INTO Professor
392 VALUES('1234567','W3');
393INSERT INTO Professor
394 VALUES('1357924','W2');
395INSERT INTO Professor
396 VALUES('2468013','W2');
397
398-- Tupel der Relation leiht_aus
399INSERT INTO leiht_aus
400 VALUES('1562367','1','3423101776',TO_DATE('1.1.2018'),TO_DATE('30.1.2018'));
401INSERT INTO leiht_aus
402 VALUES('6432753','1','3453186834',TO_DATE('2.1.2018'),TO_DATE('31.1.2018'));
403INSERT INTO leiht_aus
404 VALUES('7564258','1','3423202777',TO_DATE('2.1.2018'),TO_DATE('10.1.2018'));
405INSERT INTO leiht_aus
406 VALUES('2358712','1','3453140982',TO_DATE('1.2.2018'),TO_DATE('27.2.2018'));
407INSERT INTO leiht_aus
408 VALUES('2358712','2','3453186834',TO_DATE('1.1.2018'),TO_DATE('30.1.2018'));
409
410-- Tupel der Relation wohnt
411INSERT INTO wohnt
412 VALUES('2358712','Ackerweg','110','38302');
413INSERT INTO wohnt
414 VALUES('1562367','Neuer Weg','22','38302');
415INSERT INTO wohnt
416 VALUES('6432753','Rheinring','12','31224');
417INSERT INTO wohnt
418 VALUES('7564258','Moorkamp','13','31224');
419INSERT INTO wohnt
420 VALUES('2356984','Bergfeld','47a','38239');
421
422-- Tupel der Relation besucht
423INSERT INTO besucht(MatrNr,LVNr,Note)
424 VALUES('7564258','122','1.0');
425INSERT INTO besucht(MatrNr,LVNr)
426 VALUES('2356984','123');
427INSERT INTO besucht(MatrNr,LVNr)
428 VALUES('5236478','123');
429INSERT INTO besucht(MatrNr,LVNr)
430 VALUES('1562367','123');
431INSERT INTO besucht(MatrNr,LVNr,Note)
432 VALUES('5236478','124','3.3');
433INSERT INTO besucht(MatrNr,LVNr,Note)
434 VALUES('9812964','125','2.0');
435INSERT INTO besucht(MatrNr,LVNr,Note)
436 VALUES('9252425','126','2.0');
437INSERT INTO besucht(MatrNr,LVNr,Note)
438 VALUES('6432753','411','5.0');
439INSERT INTO besucht(MatrNr,LVNr)
440 VALUES('7564258','521');
441INSERT INTO besucht(MatrNr,LVNr)
442 VALUES('1286385','521');
443
444-- Tupel der Relation empfiehlt
445INSERT INTO empfiehlt
446 VALUES('5234260','3897212013');
447INSERT INTO empfiehlt
448 VALUES('9652425','3897212188');
449INSERT INTO empfiehlt
450 VALUES('1234567','3897212013');
451INSERT INTO empfiehlt
452 VALUES('1357924','3897212188');
453INSERT INTO empfiehlt
454 VALUES('2468013','3897212188');
455INSERT INTO empfiehlt
456 VALUES('2468013','3423101776');
457INSERT INTO empfiehlt
458 VALUES('4526748','3423101776');
459
460-- Tupel der Relation haelt
461INSERT INTO haelt
462 VALUES('111','5234260');
463INSERT INTO haelt
464 VALUES('112','1133556');
465INSERT INTO haelt
466 VALUES('113','1234567');
467INSERT INTO haelt
468 VALUES('122','1357924');
469INSERT INTO haelt
470 VALUES('123','2468013');
471INSERT INTO haelt
472 VALUES('124','1523345');
473INSERT INTO haelt
474 VALUES('125','2563172');
475INSERT INTO haelt
476 VALUES('126','9652425');
477INSERT INTO haelt
478 VALUES('411','9652425');
479INSERT INTO haelt
480 VALUES('521','8511227');
481
482
483COMMIT;