Lagerhaus - Die Datenbank

Beschreibung der Datenbank

Aus einem von einem Museum genützen Lagerhaus sind mehrere wertvolle Gegenstände entwendet worden. Nachdem Ihnen der Fall zur Aufklärung übertragen wurde, haben Sie zuerst Ihre Truppe damit beauftragt alle relevanten Informationen in einer Datenbank zu sammeln. Nach Sie Ihrem Team eine ganze Nacht dafür Zeit gegeben haben, machen Sie sich jetzit ausigeschlafen daran ein paar Antworten zu finden. Die Datenbank welche Ihre Untergebenen über Nacht aufgebaut haben besitzt die folgende Struktur. Jedes Gebäude ist eindeutig identifiziert durch einen Code (GCode) und besteht aus mindestens einem Stockwerk. Jedes Stockwerk hat ein Sicherheitslevel (SLEVEL) und ist innerhalb eines Gebäudes eindeutig durch die Stockwerksnummer (Nr) identifizierbar. In jedem Stockwerk liegt wiederum mindestens ein Raum, wobei jeder Raum eine Raumnummer (Id) besitzt welche innerhalb des Stockwerks eindeutig ist. Zu jedem gelagerten Gegenstand wird seine Bezeichnung (Bezeichnung) und eine Beschreibung (Beschreibung) vermerkt. Zusätzlich wurde der Wert (Wert) eines jeden Gegenstandes erfasst. Außerdem vergibt das Museum an jeden Gegenstand eine eindeutige Inventarnummer (InvNr). Zu versicherten Gegenständen wird zusätzlich noch die Versicherungssumme (VSumme) gespeicert. Außerdem haben Sie vom Kurator eine Liste aller fehlenden Gegenstände erstellen lassen. Gegenstände werden in Räumen gelagert, wobei jeder Gegenstand genau einen Raum als Lagerort hat. Ihre Mitarbeiter haben weiterhin eine Liste mit allem Personal der Lager zusammengetragen, und alle Security-Mitarbeiter gesondert markiert. Die HR-Abteilung des Museums hat jeodch ausschließlich die eindeutige Personalnummer (PNr), die Vornamen (VName),und Nachnamen (NName) bekannt gegeben. Auch die Dienstpläne wurden übermittelt. Jede Schicht ist eindeutig identifiziert durch ihr Beginn (Beginn) gemeinsam mit ihrem Ende (Ende). Wenn ein Security-Mitarbeiter in einer Schicht arbeitet wird dies als Dienst bezeichnet. In jeder Schicht muss es mindestens drei Dienste geben. Ein Dienst bewacht ein bis zwei Stockwerke, wobei es in jedem Stockwerk schon mindestens zwei Dienste gegeben haben muss. Jeder Raum ist mit einem elektronischen Zugangskontrollsystem ausgestattet, welches sämtliche Zutrittsversuche in die Räume protokolliert. Auch diese Informationen wurden von Ihrem Mitarbeitern zusammengetragen. Dabei wurde jedem Zugangsversuch eine eindeutige Id zugewiesen. Weiters wurde das Datum des Versuchs (Datum), sowie das Ergebnis (ResultCode) gesammelt. Außerdem wird vermerkt ob die Authentifizierung mittels PIN oder einem biometrischen Merkmal (wie Finerabdruck, Irisscan, ...) vorgenommen wurde. Zu jeder biometrischen Authentifizierung wird zusätzlich noch die Qualität der Übereinstimmung (TrefferGuete) gespeichert. Natürlich wird darüber hinaus vermerkt welche Person Zutritt haben wollte, und zu welchem Raum der Zugangsversuch war. Zur Verwendung dieser Art der Authentifizierung kann jedes Mitglied des Personals verschiedene Biometrischen Daten abgeben. Jedes biometrische Merkmal ist eindeutig identifiziert durch die Person zu der es gehört so wie durch den Typ (Typ) des Merkmals. Zusätzlich werden die Daten Merkmals gespeichert.

1)

Geben Sie für jedes Stockwerk (Gebaeudecode, Stockwerknummer) in dem mindestens ein Gegenstand gelagert wird den durschnittlichen Wert der dort gelagerten Gegenstände an. Sortieren Sie das Ergebnis absteigend zuerst nach Gebäudecode und dann nach Stockwerknummer. Runden Sie den Wert der Gegenstände auf eine ganze Zahl (Hinweis: Verwenden Sie zum Runden die Funktion round(,0)).

		
			SELECT gcodelager as gebaeude, stockwerklager as stockwerk, round(avg(wert),0) as Wert 
			FROM Gegenstand
			GROUP BY gcodelager, stockwerklager
			ORDER BY gcodelager DESC, stockwerklager DESC;	
	
		

2)

Geben Sie alle Gegenstände (Bezeichnung, Beschreibung, Wert) mit einem Wert über 1 Mio aus, die in einem Raum gelagtert werden, zu dem ein Zugriffsversuch gescheitert ist (ResultCode='FALSE'). Achten Sie darauf, dass für jeden solchen Gegenstand nur ein Eintrag ausgegeben wird.

		
			SELECT distinct bezeichnung, beschreibung, wert
			FROM Zugangsversuch zv JOIN Gegenstand g on (zv.gcode = g.gcodelager AND
			        zv.stockwerk = g.stockwerklager AND zv.raumid = g.raumidLager)
			WHERE ResultCode = 'FALSE' and wert > 1000000;	
	
		

		
			SELECT bezeichnung, beschreibung, wert
			FROM Gegenstand g
			WHERE Wert > 1000000 AND
			EXISTS (SELECT *
			        FROM Zugangsversuch z
			        WHERE g.gcodelager = z.gcode AND
			              g.stockwerklager=z.stockwerk AND
			              g.raumidlager=z.raumid AND ResultCode='FALSE');	
	
		

3)

Geben Sie jenen Raum/jene Räume (Gebaeudecode, Stockwerknummer, Raumid) aus, in welchem (in Summe) die wertvollsten Gegenstände lagern.

		
			SELECT gcodelager as Gebaeude, stockwerklager as Stockwerk, raumidlager as Raum
			FROM Gegenstand
			GROUP BY gcodelager, stockwerklager, raumidlager
			HAVING sum(wert) >= all (SELECT sum(wert)
			                         FROM Gegenstand
			                         GROUP BY gcodelager, stockwerklager, raumidlager);	
	
		

		
			SELECT gcodelager, stockwerklager, raumidlager
			FROM gegenstand
			GROUP BY gcodelager, stockwerklager, raumidlager
			HAVING SUM(wert) = (SELECT MAX(sub.summe)
			                    FROM (SELECT gcodelager, stockwerklager, raumidlager, sum(wert) as summe
			                          FROM gegenstand
			                          GROUP BY gcodelager, stockwerklager, raumidlager) sub);	
	
		

4)

Geben Sie sämtliche Zutrittsversuche von SecuritymitarbeiterInnen in Stockwerken an, in denen diese zu dieser Zeit keinen Dienst hatten. Geben Sie Datum, ResultCode, Gebaeudecode, Stockwerknummer und Raumid des Zugangsversuchs zurück sowie den Vornamen und Nachnamen des/der MitarbeiterIn.

		
			SELECT zv.datum, zv.ResultCode, zv.gcode, zv.stockwerk, zv.raumid, p.vname, p.nname
			FROM Zugangsversuch zv NATURAL JOIN Security s NATURAL JOIN Personal p
			WHERE NOT EXISTS (
			    SELECT *
			    FROM bewacht b
			    WHERE b.pnr=zv.pnr AND
			          zv.datum <= ende AND zv.datum >= beginn AND
			          zv.gcode = b.gcode AND zv.stockwerk = b.stockwerk
			);	
	
		

		
			SELECT z.datum, z.resultcode, z.gcode, z.stockwerk, z.raumid, p.vname, p.nname
			FROM zugangsversuch z JOIN security s ON z.pnr = s.pnr JOIN personal p ON s.pnr = p.pnr
			WHERE z.pnr NOT IN (SELECT b.pnr FROM bewacht b
			                    WHERE b.gcode = z.gcode AND
			                          b.stockwerk = z.stockwerk AND
			                          b.beginn <= z.datum AND
			                          z.datum <= b.ende)	
	
		

		
			SELECT datum, resultcode, gcode, stockwerk, raumid, vname, nname 
			FROM Personal NATURAL JOIN
			((SELECT id, datum, resultcode, gcode, stockwerk, raumid, pnr
			FROM Zugangsversuch NATURAL JOIN SECURITY
			)
			EXCEPT
			(
			SELECT id, datum, resultcode, gcode, stockwerk, raumid, pnr
			FROM Zugangsversuch NATURAL JOIN Security NATURAL JOIN Personal NATURAL JOIN bewacht
			WHERE datum >= beginn AND datum <= ende
			)) Zugang	
	
		

5)

Geben Sie sämtliche Mitarbeiter (PNr, VName, NName) aus, von denen mindestens 10% der biometrischen Zutrittsversuche gescheitert sind (Hier besteht der Verdacht, dass sich jemand anders mit gefälschten Merkmalen Zutritt verschaffen wollte). Das Scheitern eines Zugangsversuchs wird mit dem ResultCode FALSE vermerkt. Achten Sie darauf, dass Sie keinen betroffenen Mitarbeiter doppelt ausgeben.

		
			SELECT p.pnr, p.vname, p.nname
			FROM Personal p NATURAL JOIN Zugangsversuch NATURAL JOIN Biometrisch
			WHERE ResultCode = 'FALSE'
			GROUP BY p.pnr, p.vname, p.nname
			HAVING count(id) >= 0.1*(
			    SELECT count(*)
			    FROM Zugangsversuch zv NATURAL JOIN Biometrisch
			    WHERE p.pnr = zv.pnr
			);	
	
		

		
			SELECT p.pnr, p.vname, p.nname
			FROM Personal p NATURAL JOIN Zugangsversuch NATURAL JOIN Biometrisch
			WHERE ResultCode = 'FALSE'
			GROUP BY p.pnr, p.vname, p.nname
			HAVING count(id) >= (
			    SELECT count(*)
			    FROM Zugangsversuch zv NATURAL JOIN Biometrisch
			    WHERE p.pnr = zv.pnr
			)/10.0;	
	
		

6)

Geben Sie für jeden Raum, aus dem (mindestens) ein Gegenstand fehlt, jene Person aus, welche als letzte versucht hat Zutritt zu erlangen. Geben Sie den Gebaeudecode, die Stockwerknummer und die Id des Raumes sowie die PNr, den Vornamen und den Nachnamen des Mitarbeiters an.

		
			SELECT DISTINCT zv.gcode, zv.stockwerk, zv.raumid, pnr, p.vname, p.nname
			FROM Fehlendergegenstand NATURAL JOIN Gegenstand g JOIN Zugangsversuch zv ON
			    (g.gcodelager = zv.gcode AND g.stockwerklager = zv.stockwerk AND g.raumidlager=zv.raumid) NATURAL JOIN Personal p
			WHERE NOT EXISTS (
			    SELECT *
			    FROM Zugangsversuch z2
			    WHERE z2.raumid=zv.raumid AND z2.gcode=zv.gcode AND z2.stockwerk=zv.stockwerk
			        AND z2.datum > zv.datum
			);	
	
		

		
			SELECT DISTINCT z.gcode, z.stockwerk, z.raumid, p.pnr, p.vname, p.nname 
			FROM gegenstand g NATURAL JOIN fehlendergegenstand fg JOIN
			    (SELECT pnr, gcode, stockwerk, raumid
			     FROM zugangsversuch z
			     WHERE datum >= ALL (SELECT datum FROM zugangsversuch z2 WHERE z.gcode = z2.gcode AND z.stockwerk = z2.stockwerk AND z.raumid = z2.raumid)) z ON z.gcode = g.gcodelager AND                                                                            z.stockwerk = g.stockwerklager AND
			     z.raumid = g.raumidlager NATURAL JOIN
			        personal p	
	
		

7)

Geben Sie eine Liste aller Personen (PNr, VName, NName) aus, welche seit dem 14.10.2017 06:00 in sämtlichen Räumen waren, aus denen (mindestens) ein Gegenstand fehlt. Sie können dabei annehmen dass eine Person in einem Raum war, wenn ein erfolgreicher Zugangsversuch (ResultCode = TRUE) protokolliert wurde. (HINWEIS: Benützen Sie die Funktion TO_TIMESTAMP und das Datumsformat 'DD-MM-YYYY HH24:MM:SS')

		
			SELECT pnr, vname, nname
			FROM Personal p
			WHERE NOT EXISTS(
			    SELECT *
			    FROM Gegenstand g NATURAL JOIN FehlenderGegenstand
			    WHERE NOT EXISTS(
			           SELECT *
			           FROM Zugangsversuch z
			           WHERE z.ResultCode = 'TRUE' AND z.pnr=p.pnr AND
			           g.gcodelager=z.gcode AND g.stockwerklager=z.stockwerk AND
			        g.raumidlager=z.raumid AND  z.datum > TO_TIMESTAMP('14-10-2017 06:00:00','DD-MM-YYYY HH24:MI:SS')
			    )
			);	
	
		

		
			SELECT pnr, vname, nname
			FROM Personal p NATURAL JOIN Zugangsversuch z JOIN Gegenstand g ON (g.gcodelager=z.gcode and g.stockwerklager = z.stockwerk AND g.raumidlager=z.raumid) NATURAL JOIN FehlenderGegenstand
			WHERE z.Datum > TO_TIMESTAMP('14-10-2017 06:00:00','DD-MM-YYYY HH24:MI:SS') AND ResultCode='TRUE'
			GROUP BY pnr, vname, nname
			HAVING count(distinct (gcode, stockwerk, raumid)) = (
			SELECT count (distinct (gcodelager, stockwerklager, raumidlager))
			FROM Gegenstand NATURAL JOIN FehlenderGegenstand
			)	
	
		

8)

Geben Sie jene Stockwerke (Gebaeudecode, Stockwernummer, SLevel) mit einem Sicherheitslevel von mindestens 2 an, in denen der Wert der gestohlenen Gegenstände den durchschnittlichen Wert an gestohlenen Gegenständen in Stockwerken mit einem Sicherheitslevel von mindestens 2 übersteigt.

		
			SELECT s.gcode, s.nr, s.slevel
			FROM Stockwerk s JOIN Gegenstand g ON (s.gcode=g.gcodelager AND s.nr=g.stockwerklager)
			        NATURAL JOIN FehlenderGegenstand
			WHERE SLevel >= 2
			GROUP BY s.gcode, s.nr, s.slevel
			HAVING sum(g.wert) >= (SELECT avg(wert)
			                       FROM (
			                            SELECT sum(wert) as wert
			                            FROM Stockwerk s1 JOIN Gegenstand g1 ON (s1.gcode=g1.gcodelager AND s1.nr=g1.stockwerklager)
			                            NATURAL JOIN FehlenderGegenstand
			                            WHERE SLevel >=2
			                            GROUP BY s1.gcode, s1.nr) tmp);	
	
		

		
			SELECT s.gcode, s.nr, s.slevel
			FROM Stockwerk s JOIN Gegenstand g ON (s.gcode=g.gcodelager AND s.nr=g.stockwerklager)
			        NATURAL JOIN FehlenderGegenstand
			WHERE SLevel >= 2
			GROUP BY s.gcode, s.nr, s.slevel
			HAVING sum(g.wert) >= (SELECT sum(wert)/count(wert)
			                       FROM (
			                            SELECT sum(wert) as wert
			                            FROM Stockwerk s1 JOIN Gegenstand g1 ON (s1.gcode=g1.gcodelager AND s1.nr=g1.stockwerklager)
			                            NATURAL JOIN FehlenderGegenstand
			                            WHERE SLevel >=2
			                            GROUP BY s1.gcode, s1.nr) tmp);	
	
		

9)

Geben Sie für jedes Stockwerk alle Schichten aus, in denen das Stockwerk von keinem oder nur einem einzigen Securitymitarbeiter bewacht wurde.

		
			(SELECT gcode, nr, beginn, ende
			FROM Stockwerk, Schicht)
			EXCEPT
			(SELECT gcode, stockwerk, beginn, ende
			 FROM (security NATURAL JOIN bewacht) as tab
			 GROUP BY gcode, stockwerk, beginn, ende
			 HAVING count(pnr) > 1
			);	
	
		

		
			SELECT gcode, nr, beginn, ende
			FROM Stockwerk w, Schicht s
			WHERE NOT EXISTS( SELECT *
			                  FROM security s1, security s2, bewacht b1, bewacht b2
			                  WHERE s1.pnr <> s2.pnr AND b1.pnr=s1.pnr AND b2.pnr=s2.pnr AND
			                        b1.beginn=s.beginn AND b2.ende=s.ende AND
			                        b1.gcode=b2.gcode AND b1.stockwerk=b2.stockwerk AND
			                        b1.gcode=w.gcode AND b1.stockwerk=w.nr)	
	
		

		
			(SELECT gcode, nr, beginn, ende
			FROM Stockwerk, Schicht)
			EXCEPT
			(SELECT gcode, stockwerk, beginn, ende
			 FROM bewacht
			 GROUP BY gcode, stockwerk, beginn, ende
			 HAVING count(pnr) > 1
			);	
	
		

10)

Geben Sie für alle Personen aus, welche Räume Sie seit 14.10.2017 betreten haben. Für jede Person soll das Ergebnis eine Zeile pro betretenen Raum enthalten (PNr, VName, NName, GCode, Stockwerk, Raum-ID. Sollte eine Person seit 14.10.2017 keinen Raum betreten haben, soll statt der Raum-ID 'war auf Urlaub' ausgegeben werden. Sie können dabei annehmen dass eine Person in einem Raum war, wenn ein erfolgreicher Zugangsversuch (ResultCode = TRUE) protokolliert wurde. (HINWEIS: Benützen Sie die Funktion TO_DATE und das Datumsformat 'DD-MM-YYYY'.)

		
			SELECT DISTINCT p.pnr, p.vname, p.nname, z.gcode, z.stockwerk, COALESCE(z.raumid,'war auf Urlaub')
			FROM Personal p LEFT OUTER JOIN Zugangsversuch z ON (z.pnr=p.pnr AND z.Datum >= TO_DATE('14-10-2017','DD-MM-YYYY') AND z.ResultCode='TRUE');	
	
		

		
			SELECT DISTINCT p.pnr, p.vname, p.nname, z.gcode, z.stockwerk, COALESCE(z.raumid,'war auf Urlaub')
			FROM Personal p LEFT OUTER JOIN (SELECT * FROM Zugangsversuch WHERE Datum >= TO_DATE('14-10-2017','DD-MM-YYYY') AND ResultCode='TRUE') z ON (z.pnr=p.pnr);	
	
		

11)

Geben Sie jene Stockwerke (Gebäudecode, Stockwerknummer) aus, aus denen am meisten Gegenstände verschwunden sind. Geben Sie zusätliche die Anzahl der verschwundenen Gegenstände aus. Achten Sie darauf, dass jedes Stockwerk maximal einmal ausgegeben wird. Sortieren Sie das Ergebnis zuerst aufsteigend nach dem Gebäude, und dann dem Stockwerk.

		
			SELECT gcodelager, stockwerklager, count(invnr)
			FROM Gegenstand NATURAL JOIN FehlenderGegenstand
			GROUP BY gcodelager, stockwerklager
			HAVING count(invnr) >= all (SELECT count(invnr)
			                            FROM Gegenstand NATURAL JOIN FehlenderGegenstand
			                            GROUP BY gcodelager, stockwerklager)
			ORDER BY gcodelager, stockwerklager;	
	
		

12)

Geben Sie alle Security Mitarbeiter (VName, NName) aus welche schon einmal in allen Stockwerken Dienst hatten aus denen Gegenstände verschwunden sind (diese hatten die Möglichkeit sämtliche Tatorte auszukundschaften).

		
			SELECT VName, NName 
			FROM Personal p NATURAL JOIN Security s
			WHERE NOT EXISTS ( SELECT *
			                  FROM Gegenstand g NATURAL JOIN FehlenderGegenstand
			                  WHERE NOT EXISTS
			                  ( SELECT *
			                    FROM bewacht b
			                    WHERE b.pnr=p.pnr AND g.gcodelager=b.gcode AND g.stockwerklager=stockwerk
			                  )
			);	
	
		

13)

Geben Sie sämtliche Räume (Gebäudecode, Stockwerk, Raum-ID) an, aus denen nur versicherte Gegenstände verschwunden sind.

		
			SELECT DISTINCT r.gcode, r.nr, r.id
			FROM Raum r JOIN (Gegenstand NATURAL JOIN FehlenderGegenstand) ON (r.gcode=gcodelager AND r.nr=stockwerklager AND r.id=raumidlager)
			WHERE NOT EXISTS ( SELECT *
			                  FROM Gegenstand g NATURAL JOIN FehlenderGegenstand
			                  WHERE g.gcodelager = r.gcode AND g.stockwerklager = r.nr AND g.raumidlager = r.id
			                     AND g.invnr NOT IN (SELECT invnr FROM VersicherterGegenstand )
			);	
	
		

		
			SELECT DISTINCT r.gcode, r.nr, r.id
			FROM Raum r JOIN (Gegenstand NATURAL JOIN FehlenderGegenstand) ON (r.gcode=gcodelager AND r.nr=stockwerklager AND r.id=raumidlager)
			GROUP BY r.gcode, r.nr, r.id
			HAVING count(invnr) = (SELECT count(invnr) 
			                       FROM (Gegenstand NATURAL JOIN FehlenderGegenstand NATURAL JOIN versichertergegenstand) i
			                       where i.gcodelager=r.gcode AND i.stockwerklager=r.nr AND i.raumidlager=r.id)	
	
		

14)

Geben Sie das Stockwerk (GCode, Nr, SLevel) mit dem niedrigsten Security Level aus, aus dem kein Gegenstand verschwunden ist.

		
			SELECT gcode, nr, slevel
			FROM Stockwerk s
			WHERE NOT EXISTS (SELECT *
			                 FROM Gegenstand g NATURAL JOIN FehlenderGegenstand
			                 WHERE g.gcodelager=s.gcode AND g.stockwerklager=s.nr)
			    AND
			    slevel <= (SELECT min(slevel) FROM Stockwerk s1 WHERE NOT EXISTS
			                   (SELECT * FROM Gegenstand g1 NATURAL JOIN FehlenderGegenstand
			                    WHERE g1.gcodelager=s1.gcode AND g1.stockwerklager = s1.nr));	
	
		

15)

Erstellen Sie für jede Person ein "Bewegungsprofil" des 15.10.2017. D.h., geben Sie eine Liste (Personalnummer, Gebäudecode, Stockwerknummer, Raum-ID, Zeitstempel) aus welche für jede Person sämtliche Räume enthält, welche die Persom am 15.10.2017 betreten hat. Wir gehen davon aus, dass eine Person einen Raum betreten hat, wenn der ResultCode auf einen Zugangsversuch "TRUE" lautet. Sortieren Sie die Liste zuerst nach der Person, und dann nach dem Datum des Zutritts. Geben Sie für jeden Raum in einer zusätzlichen Spalte aus, ob aus dem Raum ein Gegenstand verschwunden ist ("Tatort") oder nicht ("Sauber"). (HINWEIS: Benützen Sie die Funktion TO_DATE und das Datumsformat 'DD-MM-YYYY'.)

		
			(SELECT pnr, z.gcode, z.stockwerk, z.raumid, z.Datum, 'Tatort' as Raumart
			FROM Zugangsversuch z JOIN (Gegenstand g NATURAL JOIN FehlenderGegenstand) ON
			    (g.gcodelager=z.gcode AND g.stockwerklager=z.stockwerk AND g.raumidlager=z.raumid)
			WHERE ResultCode = 'TRUE'
			UNION
			 (SELECT pnr, z.gcode, z.stockwerk, z.raumid, z.Datum, 'Sauber' as Raumart
			  FROM Zugangsversuch z
			  WHERE ResultCode = 'TRUE' AND
			        NOT EXISTS (SELECT *
			                    FROM Gegenstand g NATURAL JOIN FehlenderGegenstand
			                    WHERE g.gcodelager=z.gcode AND g.stockwerklager=z.stockwerk AND g.raumidlager=z.raumid)
			 )
			)
			ORDER BY pnr, datum;	
	
		

16)

Geben Sie eine Liste ALLER gestohlener Gegenstände aus (Invnr, Bezeichnung, Beschreibung). Falls der Gegenstand versichert ist und die Versicherungssumme größer als der Wert ist, soll in einer zusätzlichen Spalte "Schaden gedeckt" ausgegeben werden, andernfalls soll dort "Schaden nicht gedeckt" ausgegeben werden.

		
			SELECT g.Invnr, Beschreibung, Bezeichnung, CASE WHEN v.invnr is NULL THEN 'Schaden nicht gedeckt' WHEN v.invnr IS NOT NULL AND vsumme < wert THEN 'Schaden nicht gedeckt' ELSE 'Schaden gedeckt' END as Schaden
			FROM Gegenstand g NATURAL JOIN FehlenderGegenstand LEFT OUTER JOIN VersicherterGegenstand v ON
			    (v.invnr = g.invnr);	
	
		

17)

Geben Sie alle Zugangsversuche (Id, Datum, ResultCode, GCode, Stockwerk, Raumid) mittels PIN aus welche von Mitarbeitern durchgeführt wurden, von denen mindestens ein Biometrisches Merkmal bekannt ist.

		
			SELECT DISTINCT id, datum, resultcode, zv.pnr, gcode, stockwerk, raumid
			FROM (Zugangsversuch zv NATURAL JOIN PIN) JOIN BioMerkmal bm on (zv.pnr=bm.pnr);	
	
		

18)

Geben Sie für jeden Raum, aus dem (mindestens) ein Gegenstand fehlt, jene Person aus, welche als Letzte Zutritt hatte. Geben Sie den Gebaeudecode, die Stockwerknummer und die Id des Raumes sowie die PNr, den Vornamen und den Nachnamen des Mitarbeiters an.

		
			SELECT DISTINCT zv.gcode, zv.stockwerk, zv.raumid, pnr, p.vname, p.nname
			FROM Fehlendergegenstand NATURAL JOIN Gegenstand g JOIN Zugangsversuch zv ON
			    (g.gcodelager = zv.gcode AND g.stockwerklager = zv.stockwerk AND g.raumidlager=zv.raumid) NATURAL JOIN Personal p
			WHERE ResultCode='TRUE' AND NOT EXISTS (
			    SELECT *
			    FROM Zugangsversuch z2
			    WHERE z2.raumid=zv.raumid AND z2.gcode=zv.gcode AND z2.stockwerk=zv.stockwerk
			        AND z2.datum > zv.datum AND z2.ResultCode='TRUE'
			);	
	
		

19)

Geben Sie sämtliches Securitypersonal (pnr, vname, nname) aus, welches in allen betroffenen Stockwerken (= Stockwerke in denen zumindest ein Gegenstand fehlt) einmal alleine Dienst hatten (Gelegenheit!).

		
			SELECT pnr, vname, nname
			FROM Personal NATURAL JOIN Security s
			WHERE NOT EXISTS ( SELECT *
			                   FROM Gegenstand g NATURAL JOIN FehlenderGegenstand
			                   WHERE NOT EXISTS
			                  ( SELECT *
			                    FROM bewacht b
			                    WHERE b.gcode=g.gcodelager AND b.stockwerk=g.stockwerklager AND
			                          s.pnr = b.pnr AND
			                          NOT EXISTS ( SELECT *
			                                       FROM bewacht b1
			                                       WHERE
			                                     b.gcode=b1.gcode AND b.stockwerk=b1.stockwerk AND
			                                     b.beginn=b1.beginn AND b.ende=b1.ende AND
			                                     b.pnr<>b1.pnr)
			                  )
			
			);