SQL Trigger Aufgabe - PL/SQL und ER-Diagramm - Verständnisproblem

[b0mMeL]

Hey Leute, ich bin gerade dabei für das kommende Semester (Wirtschaftsinformatik) ein paar Übungen in Datenbanken zu machen. Ich bin hier auf eine Trigger Aufgabe gestoßen, bei der ich teilweise Verständnisprobleme habe.

Ich erwarte hier natürlich nicht die Lösung, die habe ich eh schon, aber ich kann sie nicht ganz nachvollziehen, mir gehts wirklich darum, dass ich es verstehe PS: Die Lösungen, die ich hier präsentiere, sind Musterlösungen.

Hier zunächst die Aufgabenstellung:

a) Implementiere in PL/SQL einen Trigger, der beim Hinzufügen eines Rechners in einen Poolraum überprüft, ob
der Rechner darin einen Platz findet. Ist dies nicht der Fall soll mittels
RAISE_APPLICATION_ERROR(errno, msg)folgende Fehlermeldung ausgegeben werden (Beispiel):
Der Rechner mit ID 4 hat in Raum MU06 keinen Platz mehr. Die
Maximalkapazitaet von 24 Plaetzen ist bereits erreicht!

b) Das Schema soll nun so erweitert werden, so dass für jedes Softwarepacket in der Tabelle Software (außer
natürlich für Betriebssysteme selbst) das zugehörige Betriebssystem mit abgelegt wird. Erweitere dazu das
Schema aus Anhang A in geeigneter Weise. Modifiziere anschließend auch das ER-Modell entsprechend.

c) Wie kann sichergestellt werden, dass für alle Softwarepackete (ohne die Betriebssysteme) die zugehörigen
Betriebssysteme mit abgespeichert werden. Liefere hierfür eine einfache Implementierung.
Hinweis: Du kannst davon ausgehen, dass Betriebssyteme im Attribut 'art' eine 1 aufweisen.

Anhang A (siehe Bild):

http://www.bilder-upload.eu/upload/529bd5-1467328969.png

Das ER-Diagramm mit der ergänzenden Lösung (Rot) zur Aufgabe b)

http://www.bilder-upload.eu/upload/9c743f-1467331237.png



Lösung a):

create or replace trigger rechner_hat_platz
before insert or update of poolraum_fk on rechner
   for each row
declare
   num_rechner number;
   raum poolraum%rowtype;
begin

       select count(*)
         into num_rechner
         from rechner
        where poolraum_fk = :new.poolraum_fk;

       select *
         into raum
         from poolraum
        where id=:new.poolraum_fk;

      if raum.plaetze <= num_rechner then
	 RAISE_APPLICATION_ERROR(-20001, 'Der Rechner mit ID '||:new.id||
                                 ' hat in Raum '||raum.gebaeude||raum.poolraumnr||
                                 ' keinen Platz mehr. Die Maximalkapazitaet von '||
                                 raum.plaetze||' Plaetzen ist bereits erreicht!');
      end if;
end;
/

Lösung b)

alter table software 
       add  betriebssystem_fk number 
            references software(id);

Lösung c)

create or replace trigger betriebssytem_angegeben
before insert or update on software
   for each row
declare
    betriebssystem_art number := 1;
    ok number;
begin
    if :new.art != betriebssystem_art then
	select count(*) 
          into ok
          from software
          where art=betriebssystem_art and id=:new.betriebssystem_fk;
          if ok = 0 then
              RAISE_APPLICATION_ERROR(-20002, 'Zu dem Softwarepacket '||:new.titel ||
                           ' muss das Betriebssystem auf dem es laeuft mit angegeben werden');
          end if;
    end if;
end;
/
show errors

Die a) fand ich sehr einfach, mir geht es um die b) und c)

Frage zur b):
Dort wird im Prinzip ja einfach nur eine weitere Spalte mit dem Namen betriebssystem_fk in der Tabelle Software hinzugefügt.
Z.B. wenn Windows XP mit der id: 1 in der Tabelle Software eingetragen wird, wäre ja demnach bei der Spalte betriebssystem_fk ein NULL-Eintrag, da ein OS ja logischerweise nicht auf einem OS läuft, da es bereits ein OS ist.

Wenn man jetzt z.B. Office (id: 3 z.B.) in die Tabelle Software hinzufügt (INSERT) könnte man ja bei der Spalte betriebssystem_fk die 1 als Value eintragen, um damit abzubilden, dass Office eben auf Windows XP läuft.

Anhand der Aufgabenstellung, müsste das aber auch bedeuten, dass ein Softwarepaket (z.B. Office, Photoshop etc.) nur unter -einem- OS laufen kann oder nicht? Es handelt sich ja hierbei um eine Beziehung, die sich innerhalb der Entität Software selbst abspielt, da ja der Primärschlüssel von der Software-Tabelle (id) mit der neuen Spalte (betriebssystem_fk) in der gleichen Tabelle als Fremdschlüssel referenziert wird (siehe Lösung Aufgabe b)).

Dadurch ist es entsprechend der Aufgabenstellung doch unmöglich, dass ein Softwarepaket, z.B. Office (id: 3) auf mehreren Betriebssystemen laufen kann, sondern nur auf einem, eben Windows XP (id: 1)

Was natürlich Schwachsinn ist, aber man kann ja in der Spalte betriebssystem_fk nur einen Wert hinterlassen. Ich müsste Office nochmals mit anderer Software-id in die Tabelle einfügen mit identischen Attribut-Werten, nur das bei der Spalte betriebssysteme_fk statt einer 1 eben die id für ein anderes Betriebssystem eingetragen wird, z.B. id: 10 für Windows 10, welches wiederum selbst in der Tabelle genauso wie Windows XP vorkommt.

Aber das ist doch absoluter Schwachsinn (der Aufgabenstellung geschuldet ?!) oder bin ich gerade total auf dem Holzweg?


Frage zur c):

Ich kann die Lösung im BEGIN-Bereich nicht ganz nachvollziehen. Mit der ersten if-Anweisung wird überprüft, ob es sich bei dem neuen Eintragen um ein Betriebssystem handelt oder nicht. Die Spalte art in der Tabelle Software ist ja der Primärschlüssel von der Tabelle software_art (siehe Anhang A). Laut Aufgabenstellung ist ein Betriebssystem mit der Zahl 1 gekennzeichnet, sprich es befindet sich in der Tabelle software_art ein Eintrag mit der id: 1 und der Bezeichnung Betriebssystem, so wie es in der Aufgabe bereits auch steht. Soweit alles klar.

Wenn die Bedingung erfüllt wird, also wenn es sich nicht um ein OS handelt, dann wird die SELECT-Anweisung ausgeführt. Es werden nun alle Zeilen zusammenaddiert und in die Variable 'ok' gespeichert/initialisiert und zwar nur die Zeilen, bei denen die WHERE-AND Bedingung zutrifft. Danach wird geprüft ob der Wert gleich 0 ist und wenn ja, dann wird die Fehlermeldung ausgegeben.
Trotzdem versteh ich den Sinn dahinter nicht, warum man das so macht in Hinsicht zur Aufgabenstellung.

EDIT: Hab nochmal drüber nachgedacht. Durch die AND-Bedingug müssen ja beide Bedingungen erfüllt werden, damit ein Treffer selektiert werden kann. Sprich es werden alle Tupel in der Tabelle Software selektiert die unter der Spalte 'art' eine 1 stehen haben. Zudem wird unter der Spalte id die id mit dem Wert selektiert, welche gleich dem Wert für das betriebssystem_fk-Attribut aus dem Insert-Statement ist. Gibt man z.B. die 1 als Value in einem Insert-Statement für die Spalte betriebssystem_fk ein, landet man somit bei der Zeile mit id: 1 und art: 1, also die erste Zeile in der Tabelle Software. Und id: 1 bildet das Windows XP Tuchel ab mit der art: 1. Dadurch hat die Variable 'ok' mittels der Funktion count() den Wert 1 (da ein Tupel selektiert wurde, was beide Bedingungen erfüllt) und die nachfolgende IF-Bedingung samt Fehlermeldung greift nicht.

Würde man im Insert- bzw. Update Statement einen Wert für betriebssystem_fk angeben, den es gar nicht gibt (oder einfach keinen Value für das Feld betriebssystem_fk beim Insert-Statement eintragen), z.B. 20 (Die Tabelle hat z.B. erst 10 Einträge), würde es gar nicht gehen, da versucht wird eine Software-ID zu finden, die darin gar nicht existiert oder eben weil nichts eingetragen wurde und die AND-Bedingung somit nicht erfüllt ist. Sprich es wären nicht beide Bedingungen aus dem SELECT-Statement erfüllt und es wird 0 (da keine Einträge gefunden wurden) in die Variable 'ok' gespeichert womit nun die Fehlermeldung greift da => if ( ok = 0 ) then ...

Ist mein Ansatz richtig?

select count(*) 
          into ok
          from software
          where art=betriebssystem_art and id=:new.betriebssystem_fk;
          if ok = 0 then
              RAISE_APPLICATION_ERROR(-20002, 'Zu dem Softwarepacket '||:new.titel ||
                           ' muss das Betriebssystem auf dem es laeuft mit angegeben werden');
          end if;
    end if;

Bin über jede Hilfe dankbar. Danke im voraus

 

[Fame93]

Servus,

zu A) hier würde ich bei der zweiten Unterabfrafrage direkt die Anzahl der Räume in der Variable speichern und dann unten auch direkt auf diese Variable prüfen. Aber das nur als kosmetische Anmerkung.

 select plaetze into raum from poolraum where id=:new.poolraum_fk;

zu B) Sehe ich genau so. Hier hätte der Autor mit einer Hilfstabelle arbeiten müssen um zu ermöglichen, dass eine Software auf mehreren Betriebssystemen laufen kann.

zu C) Die einfachste Lösung wäre hier sicherlich ein Check-Konstraint der mithilfe eines Regulären Ausdrucks überprüft ob der Wert mit einer 1 beginnt und falls ja sicherstellt, dass der entsprechende FK gefüllt ist.

Bei einer Lösung durch einen Trigger hast du je nach Konstruktion des Triggers eine 'Table is mutating Exception'.

Gruß

 

[b0mMeL]

Okay danke

Ja bei der c) habe ich auch erst überlegt ob es mit einem CHECK-Constraint nicht einfacher und angenehmer wäre. Ich finde das gar nicht so einfach darauf zu kommen wie man das mit einem Trigger löst. Naja wird wohl Übungssache sein

 

[mental.dIseASe]

Sind halt so typisch akademische Aufgaben, um Sachen zu raffen. Insbesondere bei Aufgabe B hängt es von der genauen Spezifikation ab, daher ist die Lösung nicht zwangsläufig falsch. Wenn man definiert, dass eine Software für mehrere Plattformen jeweils ein Softwarepaket konstituiert (also dann Office mehrfach auftaucht), dann ist die beschriebene Lösung passend.

Ich hätte aber auch eher eine Assoziationstabelle gewählt. Kommt immer darauf an, was man ausdrücken will. Wenn man erreichen will, dass alle Softwarepakete zu einem Betriebssystem gelöscht werden, wenn man das Betriebssystem löscht, dann wäre zum Beispiel die vorgeschlagene Musterlösung die bessere, weil man sowas dann über eine Kaskade auf dem Constraint abfackeln kann.

DIE richtige Lösung kann es also solange nicht geben, bis man genau weiß, was alles so an Randbedingungen da ist. Und selbst dann gibt es oft mehrere widerstreitende richtige Lösungen.