Frage zu Assertion und Constraints

[snapdragon]

Hallo,

ich hänge an 2 Teilaufgaben einer Abgabe und bräuchte Hilfe.

Die Aufgabenstellung:

Gegeben sei das folgende Schema: Personal (PNR, Name, Gehalt, Abt, Vorges)

a) Geben Sie das CREATE TABLE Statement an, um die Tabelle Personal zu erzeugen. Folgende

Integritätsbedingungen sollen gelten:

 Name, Abteilung und Gehalt müssen angegeben werden.

 Das Gehalt darf nicht kleiner als 8000 sein.

 Der Vorgesetzte ist entweder NULL oder eine PNR aus der Tabelle Personal.

 Wird eine Person gelöscht, erhalten alle anderen, die diese Person als Vorgesetzten

hatten, den Vorgesetzten NULL.

 Ändert sich die PNR einer Person, soll sich diese Änderung ebenfalls auf alle Werte der

Vorges-Spalte von Personen auswirken, die diese Person als Vorgesetzten haben.

Formulieren Sie die folgende Bedingung mit einer SQL Assertion:

„Jede Person ist entweder in der gleichen Abteilung wie sein Vorgesetzter oder sein

Vorgesetzter ist in der Abteilung Management.“

Bei der a) habe ich als bisherige Lösung:

CREATE TABLE Personal(

PNR INT UNSIGNED AUTO_INCREMENT CHECK (),

NAME VARCHAR(50) NOT NULL,

GEHALT INT NOT NULL CHECK(GEHALT > 8000),

Abt VARCHAR(30) NOT NULL,

Vorges VARCHAR(30) CHECK(Vorges IS NULL OR Vorges = PNR)

);

Einige der Checkbedingungen bekomme ich nicht hin .

Bei der Aufgabe komme ich auch kein Stück weiter.

Viele Grüße

snap

[arlegermi]

Vorges VARCHAR(30) CHECK(Vorges IS NULL OR Vorges = PNR)

Das hier stimmt nicht mit der Aufgabe überein. Was du hier prüfst, ist, ob Vorgesetzter = NULL oder Vorgesetzter = PNR - und PNR hast du gerade erst eingefügt. Du musst also gucken, ob es den Vorgesetzten in Personal gibt - kannst du per SELECT und EXISTS (oder ANY) rausfinden.

GEHALT INT NOT NULL CHECK(GEHALT > 8000)

Kleiner Fehler, aber in der Aufgabe steht: "nicht kleiner als 8000". Genau genommen ist "> 8000" damit nicht korrekt.

Für die letzten beiden Bedingung aus a) verweise ich auf ON DELETE SET NULL / ON UPDATE CASCADE- das sollte dir weiterhelfen.

Zu : Wo genau hakt's? Wie würdest du denn ein SELECT formulieren, dass dir alle Personen ausgibt, deren Vorgesetzter nicht in der gleichen Abteilung ist? Joins sind dir bekannt? Wenn nicht, guck dir das an - daran kommst du sowieso nicht vorbei.

Bearbeitet 19. Juni 2015 von arlegermi

[snapdragon]

Hi,

danke für die Antwort aber ich kann damit leider nicht viel anfangen. Ich weis was Joins sind aber ich habe hier doch nur eine Tabelle gegeben?

[snapdragon]

Hat denn keiner eine Ahung wie die Assertion bei b aussehen muss ?

[mfk'); DROP TABLE Users;--]

Ich weis was Joins sind aber ich habe hier doch nur eine Tabelle gegeben?

Du kannst auch eine Tabelle mit sich selbst joinen.

[snapdragon]

Das bringt mich auch nicht weiter. Ich komme einfach nicht auf die Lösung, wie ich die Bedingung prüfen kann.

Ich habe bisher nur:

1b) CREATE ASSERTION meineAssertion CHECK (

EXISTS (

SELECT *

FROM personal a, personal b

WHERE

);

keine Ahnung wie ich das WHERE machen soll ...

[arlegermi]

SELECT * FROM Personal p1 

WHERE EXISTS 

    (SELECT * FROM Personal p2 

     WHERE p2.PNR = p1.Vorges 

     AND (p2.Abt = 'Management' OR p2.Abt = p1.Abt)) 

Damit lässt du dir sämtliches Personal ausgeben, auf das die Bedingung zutrifft. Geht auch mit Joins, das sähe dann ca. so aus:

SELECT * FROM 

(Personal p1 JOIN Personal p2 

ON p1.Vorges = p2.PNR 

AND (p2.Abt = 'Management' OR p2.Abt = p1.Abt))

Das musst du jetzt noch ein wenig für deinen Check anpassen, sollte aber helfen.

Bearbeitet 22. Juni 2015 von arlegermi

[snapdragon]

Hallo arlergermi,

vielen Dank für die Hilfe. Ich wollte das die ganze Zeit ohne Subquery umsetzen und habe mir daran die Zähne ausgebissen.

[dr.dimitri]

Da ist ja so ziemlich alles falsch, was man nur falsch machen kann. :old