Wirtschaftsinformatik (Bachelor-Studiengang): Grundlagen der Datenbankanwendung (3. Semester)

Sie sind hier: Startseite › Wirtschaftsinformatik › Grundlagen der Datenbankanwendung

PZ / CM, Kurs vom 01.04.2003 - 30.09.2003

Grundlagen der Datenbankanwendung: Einführung (Client-Server-Betrieb bei DBMS, SQL, Grundstruktur von SQL, SELECT-Anweisung (Prinzip-Beispiel)), Informationssuche elementar, Informationssuche mit Berechnung/Funktion, Informationssuche mit Joinings (Informationssuche mit einfachem Join, Informationssuche mit mehrfachem Join und zusätzlicher Bedingung, Arbeit mit dominanten Tabellen), Ausnahmen ermitteln (Variante 1: "Offene Posten", Variante 2: "Keine Referenz", Variante 3: "Grenzwert nicht erreicht"), Fristberechnungen (Kalendertagesfristen, Zeitraumberechnungen, Zeitstempel, Berechnungen von Wochentagen, Berechnung von Quartalen, Berechnungen von Wochentagsfristen), Externe Sichten (View) (Syntaxerklärung am Beispiel "Katalog", "fremder" Datenbankzugriff am Beispiel "Katalog", Extremwertermittlung, Rangfolgeproblem), Stored Procedures, Trigger.

1. Einführung
2. Informationssuche elementar
3. Informationssuche mit Berechnung/Funktion
4. Informationssuche mit Joinings
5. Ausnahmen ermitteln
6. Fristberechnungen
7. Externe Sichten (View)
8. Stored Procedures
9. Trigger

Einführung

Client-Server-Betrieb bei DBMS

Server-DBMS arbeiten Aufgaben(Task)-orientiert:

Bildbeschreibung "Server-Datenbankmanagementsystem": Server-DBMS arbeiten Aufgaben-orientiert. Je nach Aufgabenstellung wird auf verschiedene Datenbanken zugegriffen.

Bildbeschreibung "Zuweisen von Aufgaben": Das DBMS kann unterschiedlichen Aufgaben unterschiedliche Datenbanken zuweisen.

Bildbeschreibung "Zugriff von Datenbank auf Datenbank": Eine Aufgabe kann jedoch auch aus einer DB auf eine andere DB zugreifen.

Grundlegende Voraussetzung für die Nutzung aller DBMS-Funktionen ist, dass der Nutzer (User) einer Aufgabe die erforderlichen Rechte auf das DBMS und die zu nutzenden DB besitzt:

Bildbeschreibung "Rechte als Voraussetzung": Aufgabe 1 wird ausgelöst durch einen Nutzer. Voraussetzung ist, dass er die erforderlichen Rechte für den DBMS-Zugriff hat.

SQL

• Standardisierte Datenbanksprache für relationale Datenbanken, zunächst Sequel (Structured English Query Language)
• 1986 unter Federführung der ISO fertiggestellt (SQL-1 als ISO/IEC-9075-Norm)
• 1989 ergänzt (SQL-1+Norm)
• 1992 erweitert zu SQL-2 in den drei Stufen entry level, intermediate level, full level (ISO 9075: Database Language SQL 2)
• 1999 erneute Erweiterung zu SQL-3
• in Arbeit Standard SQL-4 mit objektrelationalen Erweiterungen

Grundfunktionen (Basis SQL-2):

• Datenbank erstellen
• Tabellen erstellen
• Datenbank aktualisieren
• Daten selektieren
• Views erstellen
• Dateien indizieren
• sortieren
• Daten schützen
• Metadaten katalogisieren
• Einbettung in Wirtsprachen

SQL - eine mengenorientierte Sprache:

Bildbeschreibung "SQL - eine mengenorientierte Sprache": Grafische Darstellung der Datenbank als eine Menge.

• Strukturiert durch Teilmengen Table/View und Spaltennamen.
• Informationsdefinition innerhalb einer Teilmenge (Zeilenbestimmung) durch Bedingungen!

Grundstruktur von SQL

Grundstruktur von SQL

Datenbankschema definieren und ändern	Daten manipulieren	Daten auswerten
Create Database ... Create Table ... Create View ... Create Index ... Alter Table ... Drop ... Grant ... Revoke ...	Insert ... Update ... Delete ...	Select ...
	Create Procedure ... Create Trigger ... Execute Procedure ... Drop ... Begin Work ... Commit/Rollback ...

Trennzeichen:

• 1 bis n Leerzeichen in einer SQL-Anweisung
• ; (Semikolon) zwischen SQL-Anweisungen

Kommentare:

• { . . . } oder für Informix -- am Zeilenbeginn

SELECT-Anweisung (Prinzip-Beispiel)

SELECT Nummer, Name FROM Adressen
Wähle Spalten von Tabelle

Bildbeschreibung "SELECT-Anweisung (Prinzip-Beispiel)": Die Tabelle Adressen enthält beispielsweise die Spalten Nummer, Name und Postleitzahl.

Formatierungszeichen:

1. Leerzeichen (1 bis n) = Trennzeichen / Füllzeichen
2. Komma (exakt 1) = Trennzeichen für Listenelemente

Gleichwertige Ausdrücke (Beispiele):

• select regnr, name from adressen
• SELECT REGNR,NAME FROM ADRESSEN
• Select adressen.regnr, name From AdreSSen
• SELECT Regnr, name
from Adressen

|

Informationssuche elementar

Beispiel: Gesucht seien alle Lieferanten mit einem Konto bei der Berliner Sparkasse (Bankleitzahl = 10050000).

Beispiel 1 überspringen

SELECT regnr, ktonr { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE blz = '10050000'; { Auswahlbedingung }

Es sind weitere Vergleichsoperatoren möglich, z.B.:

Beispiel 2 überspringen

SELECT regnr, ktonr, blz { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE blz <= '10050000'; { Auswahlbedingung }

Aus einfachen Bedingungen werden komplexe Bedingungen durch Verknüfen mit and (und) or (oder) und not (nicht):

Beispiel 3 überspringen

SELECT regnr, ktonr, blz { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE blz= '10050000' { das ist unmöglich!!! }
and blz= '10020000';

Richtig wäre z.B.:

Beispiel 4 überspringen

SELECT regnr, ktonr, blz { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE blz= '10050000' { Auswahlbedingung }
or blz= '10020000';

Auch geklammerte Ausdrücke sind möglich:

Beispiel 5 überspringen

SELECT regnr, ktonr, blz { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE (blz= '10050000' { Auswahlbedingung }
or blz= '10020000')
and regnr= '01050';

Das ist etwas anderes als:

Beispiel 6 überspringen

SELECT regnr, ktonr, blz { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE BLZ= '10050000' { Auswahlbedingung }
OR BLZ= '10020000'
AND regnr='01050';

... denn die Anweisung würde logisch folgendermaßen interpretiert:

Beispiel 7 überspringen

SELECT regnr, ktonr, blz { angezeigte Spalten }
FROM konto { betroffene Tabelle }
WHERE BLZ= '10050000' { Auswahlbedingung }
OR (BLZ= '10020000'
AND regnr='01050');

Nur für Zeichenketten gibt es auch noch:

Beispiel 8 überspringen

SELECT * { angezeigte Spalten }
FROM blz { betroffene Tabelle }
WHERE BANK_NAME matches '*BERLIN*'; { Auswahlbedingung }

Ein anderes besseres(?) Ergebnis liefert:

Beispiel 9 überspringen

SELECT * { angezeigte Spalten }
FROM blz { betroffene Tabelle }
WHERE BANK_NAME matches '*BERLIN'; { Auswahlbedingung }

|

Informationssuche mit Berechnung/Funktion

Beispiel: Gesucht seien alle Bestellungen des Monats Oktober im laufenden Jahr.

Beispiel 10 überspringen

SELECT * { Spaltenauswahl }
FROM bkopf { betroffene Tabelle }
WHERE MONTH(bdat) = 10; { Auswahlbedingung }

Hinweis: Der Vergleichwert 10 (für Monat Oktober) wird nicht in Anführungszeichen (‘’) gesetzt, da Datumsangaben numerische Werte sind.

Hinweis: Funktionen können auch in der Spaltenauswahl angegeben werden. In diesem Falle ist ein Aliasname für die Benennung der Spalte in der Ausgabetabelle erforderlich.

Soll nur ein spezielles Jahr angezeigt werden, muss man formulieren:

Beispiel 11 überspringen

SELECT * { Spaltenauswahl }
FROM bkopf { betroffene Tabelle }
WHERE MONTH(bdat) = 10 AND Year(bdat) = 2002; { Auswahlbedingung }

Man kann aber auch einen Bezug auf das aktuelle Datum legen:

Beispiel 12 überspringen

SELECT * { Spaltenauswahl }
FROM bkopf { betroffene Tabelle }
WHERE MONTH(bdat) = 10 { Auswahlbedingung }
AND Year(bdat) = Year(today) - 1;

Man kann übrigens auch Funktionen in der Ausgabespaltenliste benutzen:

Beispiel 13 überspringen

SELECT *, today AS bezogen_auf { Spaltenauswahl }
FROM bkopf { betroffene Tabelle }
WHERE MONTH(bdat) = 10 { Auswahlbedingung }
AND Year(bdat) = Year(today) - 1;

Ich bin heute zum Beispiel die folgende Anzahl Tage alt:

Beispiel 14 überspringen

SELECT DISTINCT today - { Spaltenauswahl }
"21.08.1979" as Tagesalter
FROM bkopf; { betroffene Tabelle }

|

Informationssuche mit Joinings

Informationssuche mit einfachem Join

Beispiel: Ausweis aller Lieferantenadressen mit den zugehörigen Konten.

Beispiel 15 überspringen

SELECT adressen.regnr, name, blz, ktonr { Spaltenauswahl }
FROM adressen, konto { betroffene Tabellen }
WHERE konto.regnr = adressen.regnr; { Auswahlbedingung }

Mehrfaches Joining ist z.B. erforderlich, wenn wir nur die Lieferanten ausweisen wollen:

Beispiel 16 überspringen

SELECT adressen.regnr, name, konto.blz, bank_Name, ktonr
FROM adressen, konto, liefer, blz
WHERE konto.regnr = liefer.regnr AND
liefer.regnr = adressen.regnr AND
konto.blz = blz.blz;

Informationssuche mit mehrfachem Join und zusätzlicher Bedingung

Beispiel: Anzeige aller Angebote unter 1.000,00 € mit Lieferantennamen und Artikelbezeichnungen

Tabellenverbund:

1. Tabelle ADRESSEN enthält REGNR
2. Tabelle ARTIKEL enthält ANR
3. Tabelle ANGEBOT enthält REGNR und ANR

Beispiel 17 überspringen

SELECT artikel.anr, aname, angebot.regnr, name, preis
FROM artikel, angebot, adressen
WHERE adressen.regnr = angebot.regnr AND
angebot.anr = artikel.anr AND preis < 1000;

Hinweis: Mehrere (einfache) Join-Bedingungen sind immer AND-verknüpft.

Eine besser dokumentierte und vielleicht auch nutzerfreundlichere Lösung:

Beispiel 18 überspringen

SELECT artikel.anr as Artikelnummer,
artikel.aname as Artikelname,
angebot.regnr as Lieferantennummer,
adressen.name as Lieferant,
angebot.preis as Nettopreis,
round((angebot.preis*1.16),2) as Bruttopreis
FROM artikel, angebot, adressen
WHERE adressen.regnr = angebot.regnr AND
angebot.anr = artikel.anr AND preis < 1000;

Hinweis: Numerische Angaben mit Dezimalstellen werden in SQL mit Dezimalpunkt (nicht Komma) geschrieben. An Stelle von 1000 könnte folglich auch 1000.00 stehen (falsch wäre 1000,00)!

Arbeit mit dominanten Tabellen

Beispiel: Es sollen alle Lieferanten nach Firmennamen sortiert mit den gegenwärtig an sie adressierten Bestellungen angezeigt werden.

Zunächst der natürliche Join zur Anzeige aller Bestellungen:

Beispiel 19 überspringen

Select name, bnr
FROM bkopf, adressen
WHERE bkopf.regnr = adressen.regnr
ORDER BY name, bnr;

Und nun sollen alle Lieferanten angezeigt werden:

Beispiel 20 überspringen

Select name, bnr
FROM adressen, liefer, OUTER bkopf
WHERE bkopf.regnr = liefer.regnr AND liefer.regnr = adressen.regnr
ORDER BY name, bnr;

Hinweis: OUTER bezeichnet die untergeordnete Tabelle.

|

Ausnahmen ermitteln

Variante 1: "Offene Posten"

Beispiel: Prüfen, ob es Adressen ohne Mail gibt.

Lösungsvoraussetzung: Zulässigkeit von Nullwerten in der selektierten Spalte.

Beispiel 21 überspringen

SELECT *
FROM adressen
WHERE mail IS NULL;

Bitte nicht verwechseln mit:

Beispiel 22 überspringen

Select * FROM adressen
WHERE mail = "   ";

Hinweis: null bedeutet keine Angabe ("Null" würde hingegen die Zeichenkette "Null" bedeuten)!!!

Variante 2: "Keine Referenz"

Beispiel: Ermittlung der bestellten Artikel.

Select distinct anr FROM bart;

Beispiel 23 überspringen

SELECT anr, aname { Spaltenauswahl }
FROM artikel { betroffene Tabellen }
WHERE NOT exists { Auswahlbedingung }
(SELECT bart.anr FROM bart { Unter-Select-Anweisung }
WHERE artikel.anr = bart.anr);

Vorsicht, logisch falsch ist hingegen:

Beispiel 24 überspringen

SELECT anr, aname { Spaltenauswahl }
FROM artikel { betroffene Tabellen }
WHERE NOT exists { Auswahlbedingung }
(SELECT bart.anr FROM bart,artikel { Unter-Select-Anweisung }
WHERE artikel.anr = bart.anr);

Logisch völlig gleichwertig ist hingegen:

Beispiel 25 überspringen

SELECT anr, aname { Spaltenauswahl }
FROM artikel { betroffene Tabellen }
WHERE anr NOT IN
(Select distinct anr FROM bart); { Unter-Select-Anweisung }

Variante 3: "Grenzwert nicht erreicht"

Problem: Summierung erforderlich, sofern sich Grenzwert aus Einzelpositionen berechnet.

Beispiel: Ausweis aller Bestellungen unter 15.000,- €

Lösungsansatz: Stufenweise Lösung zur Dekomposition der Problemstellung.

Selektion BESTELL1: Berechnung der Werte je Bestellposten
Selektion BESTELL2: Berechnung der Summe je Bestellung

Lösung mittels einer komplexen Select-Anweisung:

Beispiel 26 überspringen

SELECT bart.bnr,sum(menge*preis) bestellwert
FROM bart,bkopf,angebot
WHERE bart.bnr=bkopf.bnr AND
bkopf.regnr=angebot.regnr AND
bart.anr=angebot.anr
GROUP BY bart.bnr
HAVING sum(menge*preis) < 15000;

Hinweis: Mit den bisher bekannten Formulierungsmitteln muss das Ergebnis der ersten Selektion (BESTELL1) temporär in einer Tabelle gespeichert werden.

Zusammenfassung von Teilmengen mittels "GROUP BY"-Klausel:

"GROUP BY" kann jeweils eine Gruppe durch den gleichen (systematisierenden oder klassifizierenden) Schlüssel definierter Tupel (Zeilen) zusammenfassen und als einen Ergebnistupel in die Ergebnistabelle ausgeben.

Gruppierungsfunktionen sind SUM, AVG, COUNT, MIN, MAX.

Beispiel 27 überspringen

SELECT bnr, COUNT(*) posten { Count=Zählen }
FROM bart
GROUP BY bnr { Gruppierung nach bnr )
INTO TEMP bestell; { temporäre Tabelle }

|

Fristberechnungen

Kalendertagesfristen

Unproblematisch mit Grundrechenarten.

Beispiel: Wieviel Tage sind es bis Weihnachten?

schlechte Lösung:

Beispiel 28 überspringen

Select Distinct "24.12.2003" - today AS tage_bis_xmas
FROM bkopf;

bessere Lösung:

Beispiel 29 überspringen

Select Distinct MDY(12,24,year(today)) - today AS tage_bis_xmas
FROM bkopf;

Zeitraumberechnungen

Mittels Funktionen, z.B. Bestellungen der letzten 3 Monate:

Beispiel 30 überspringen

SELECT * FROM bkopf
WHERE (year(bdat)* 12) + month(bdat) > (year(today)* 12) + month(today) -3;

Spezielles zur Zeitraumberechnung:

Die Berechnung mittels Funktionen, z.B. die Bestellungen der letzten 3 Monate mittels

Beispiel 31 überspringen

SELECT * FROM bkopf
WHERE (year(bdat)* 12) + month(bdat) >(year(today)* 12) + month(today) -3;

kann nur quartalsunabhängig durchgeführt werden.

Eine Fixierung z.B. auf das aktuelle Quartal ist jedoch durch Nutzung von Hilfstabellen möglich:

Beispiel 32 überspringen

CREATE TABLE Quartab (
Monat serial NOT NULL PRIMARY KEY ,
Abzug int NOT NULL );
INSERT INTO Quartab (Abzug) VALUES(0);
INSERT INTO Quartab (Abzug) VALUES(1);
INSERT INTO Quartab (Abzug) VALUES(1); ...
SELECT regnr, bnr, bdat FROM bkopf,Quartab AS QR
WHERE year(bdat)= year(today) AND
QR.Monat = month(today) AND
month(bdat) >= month(today)- Abzug;

Zeitstempel

Nutzung des Date-Time-Datentypes.

• Zeitstempel werden genutzt für Registrierungen.
• "year to second" gibt an, welche Bestandteile des Datums angezeigt werden sollen.
• Aber nur hintereinanderliegende Bereiche!
• Wichtig für Banken und Online-Handel, damit nachweisbar wann Dienste genutzt wurden.

Beispiel 33 überspringen

Create Table REGISTER
(nutzer char(8), zeit datetime year to second);
Insert Into register Values
("Max", Current);
Insert Into register Values
("Moritz", datetime(2003-05-15 14:35:10) year to second);
Select * FROM register;

Berechnungen von Wochentagen

Unproblematisch mit Funktionen bzw. Formatierung, aber nicht standardisiert. Um den Wochentagsnamen zu ermitteln, muss eine Tabelle erstellt werden, in der Werte entsprechende Namen zugeordnet werden.

Select *,weekday(bdat) as wochentag FROM bkopf;

Berechnung von Quartalen

Quartalsberechnungen sind nur mit Steuertabellen (Hilfstabellen) möglich.

Beispiel: Anlegen einer Quartalssteuertabelle.

Beispiel 34 überspringen

Create Table Quarttab(Monat serial NOT NULL Primary Key,Abzug int NOT NULL);
Für den 1. Monat des Quartals:
Insert Into Quarttab(Abzug) VALUES(0);
Für den 2. Monat des Quartals:
Insert Into Quarttab(Abzug) VALUES(1);
Für den 3. Monat des Quartals:
Insert Into Quarttab(Abzug) VALUES(2);

... 4x ausführen, um Werte für alle Quartale eines Jahres zu erhalten.

Und jetzt ermitteln wir alle Bestellungen des aktuellen Quartals:

Beispiel 35 überspringen

SELECT regnr, bnr, bdat, year(bdat)
as jahr, month(bdat) as monat
FROM bkopf, quarttab as QR
WHERE year(bdat) = year(today) AND
qr.monat = month(today) AND
month(bdat) >= month(today)-abzug
ORDER BY bnr,jahr,monat

Berechnungen von Wochentagsfristen

Sinnvoll nur funktional lösbar.

|

Externe Sichten (View)

Beispiel: Die aus dem Joining der Tabellen ADRESSEN und LIEFERANTEN gebildete Sicht auf die Adressen nur der Lieferanten soll ständig als "virtuelle Tabelle" unter dem Namen LIEFERADR verfügbar sein.

Beispiel 36 überspringen

CREATE VIEW lieferadr { Name der View }
(liefernr, name, plz, ort, telefon, mail, typ) { Spaltennamen }
AS SELECT liefer.regnr, name, plz, ort, telefon, mail, typ
FROM liefer, adressen
WHERE liefer.regnr = adressen.regnr

Hinweis: Die View wird als virtuelle Tabelle in den Systemkatalog der Datenbank eingetragen. Folglich können nicht mehrere Views mit gleichem Namen existieren. Die Berechnung der Daten einer View erfolgt neu bei jedem Aufruf der View.

Entwickeln einer View:

Bildbeschreibung "Entwickeln einer View": Verschiedene Tabellen sind Grundlage für die externe Sicht (View). Der Beschreibung erfolgt permanent, der Inhalt ist daher temporär.

Beispiel 37 überspringen

CREATE VIEW viewname
[ ( spaltenname1, ... spaltennameN ) ]
AS SELECT selectanweisung

Syntaxerklärung am Beispiel "Katalog"

Gegeben sind die Tabellen ANGEBOT, ARTIKEL und ADRESSEN. Mittels einer View sollen die Angebote mit der Artikelnummer (ANR), dem Artikelnamen (ARTNAME), dem Namen des Lieferanten (LIEFERER), der Mengeneinheit (ME) und dem Artikelpreis (PREIS) angezeigt werden.

Lösung:

Beispiel 38 überspringen

CREATE VIEW katalog { Viewname }
(anr, artname, lieferer, me, preis) { View-Spalten }
AS SELECT { Datenquelle ... }
angebot.anr, aname, name, me, preis { ... Spalten }
FROM angebot, adressen, artikel { ... Tabellen }
WHERE angebot.anr = artikel.anr { Join für aname }
AND angebot.regnr = adressen.regnr;

"fremder" Datenbankzugriff am Beispiel "Katalog"

Gegeben sind die Tabellen ANGEBOT, ARTIKEL und ADRESSEN in der Datenbank dzeinkauf.

Mittels einer View sollen die Angebote mit der Artikelnummer (ANR), dem Artikelnamen (ARTNAME), dem Namen des Lieferanten (LIEFERER), der Mengeneinheit (ME) und dem Artikelpreis (PREIS) in einer eigenen Datenbank angezeigt werden.

Lösung:

Beispiel 39 überspringen

CREATE VIEW katalog { Viewname }
(anr, artname, lieferer, me, preis) { View-Spalten }
AS SELECT { Datenquelle ... }
angebot.anr, aname, name, me, preis { ... Spalten }
FROM dzeinkauf:angebot angebot, { ... Tabellen }
dzeinkauf:adressen adressen,
dzeinkauf:artikel artikel
WHERE angebot.anr=artikel.anr { Join für aname }
AND angebot.regnr=adressen.regnr;

Gemeinsamkeiten und Unterschiede zwischen View und temporärer Tabelle:

Grundsätze der Aufgabenverteilung für die Arbeit mit Views in Client-Server-Umgebungen:

1. Datentransfer zwischen Client-Server minimieren.
2. Aggregationen auf dem Server ausführen.
   Ausnahme: Auf dem Client sollen Einzelposten und Aggregationen bearbeitet werden.
3. Entschlüsselungen (Klartextangabe) von Daten
   • bei großen Schlüsseltabellen auf dem Server,
   • bei kleinen Schlüsseltabellen auf dem Client.
4. Datenkonvertierungen innerhalb der ODBC-Umgebung möglichst vermeiden. Sofern unvermeidbar, auf dem Client in ursprünglichen Server-Datentyp konvertieren.
   Beispiel: Währungsangaben mit 32 Stellen werden in Zeichenketten konvertiert. Auf dem Client Microsoft Access 2000 kann in einer Abfrage mit der Funktion Val(textvariable) in SQL-Ansicht bzw. Wert(textvariable) in Entwurfsansicht der Inhalt einer Spalte vom Typ Text in eine Zahl konvertiert und als Währungsangabe formatiert werden.

Extremwertermittlung

Extremwerte sind das Minimum bzw. Maximum innerhalb einer definierten Gruppe von Tupeln (Zeilen) einer Relation (Tabelle). In betriebswirtschaftlichen Anwendungen werden Extremwerte zur Entscheidungsfindung genutzt.

Beispiel: Gesucht wird das günstigste (preislich niedrigste) Angebot für jeden Artikel.

Gegeben sind die Tabellen ANGEBOT, ARTIKEL und ADRESSEN.

Ermittelt werden sollen die günstigsten Angebote mit den Attributen ANR (Artikelnummer), ANAME (Artikelname), NAME (Lieferantenname), PREIS (niedrigster Preis je Artikel).

Lösung:

Beispiel 40 überspringen

CREATE VIEW minang1 (anr, minpreis)
AS SELECT anr, MIN(preis) FROM angebot
GROUP BY anr;

Ermittlung, welcher Lieferant den Minimalpreis bietet:

Beispiel 41 überspringen

CREATE VIEW minang2 (anr,aname,name,preis)
AS SELECT minang1.anr, aname, name, minpreis
FROM minang1, artikel, adressen, angebot
WHERE minang1.anr=artikel.anr AND
minang1.anr=angebot.anr AND
minang1.minpreis=angebot.preis AND
angebot.regnr=adressen.regnr;

Hinweis: In der View minang2 werden die den Preisen zugeordneten Lieferanten durch Joining ermittelt.

Zum Schluss noch eine Knobelaufgabe: Es sollen nur Artikel angezeigt werden, die mehr als einmal angeboten werden.

Beispiel 42 überspringen

select * FROM angebot WHERE anr in
(select anr FROM angebot group by anr having count(*) > 1)
order by anr;

Rangfolgeproblem

Innerhalb einer Tabelle/View sollen die der Rangfolge nach n kleinsten/größten Werte ermittelt werden.

Zwei Lösungsansätze existieren für dieses Problem:

1. Lösungsansatz:

Eine sehr kleine Anzahl von Rangfolgen kann mittels einer Kaskade von Views ermittelt werden.

Beispiel: Ermittlung der drei Angebote mit dem niedrigsten Preis.

Beispiel 43 überspringen

CREATE VIEW MIN1 (preis)
AS SELECT min(preis) FROM angebot;
CREATE VIEW MIN2 (preis)
AS SELECT min(angebot.preis) FROM angebot, MIN1
WHERE angebot.preis > MIN1.preis;
CREATE VIEW MIN3 (preis)
AS SELECT min(angebot.preis) FROM angebot, MIN2
WHERE angebot.preis > MIN2.preis;
SELECT anr,angebot.preis FROM angebot, MIN3
WHERE angebot.preis <= MIN3.preis
ORDER BY angebot.preis

2. Lösungsansatz:

Eine gößere Anzahl von Rangfolgen kann mittels Sortierung temporärer Tabellendaten ermittelt werden.

Beispiel: Ermittlung der zehn Angebote mit dem niedrigsten Preis.

Beispiel 44 überspringen

CREATE TABLE sortpreis
(anr char (6), preis money);
BEGIN WORK; { Beginn Transaktion }
DELETE FROM sortpreis WHERE preis >= 0;
SELECT anr,preis FROM angebot
ORDER BY preis INTO TEMP zw;
SELECT min(rowid) +9 AS nr FROM zw
INTO TEMP grenze;
INSERT INTO sortpreis (anr,preis)
SELECT anr,preis FROM zw,grenze
WHERE zw.rowid <= nr;
DROP TABLE zw; DROP TABLE grenze;
COMMIT WORK;
SELECT * FROM sortpreis

Hinweis: Die Einkleidung in eine Transaktion dient der Erhöhung der Verarbeitungssicherheit.

Hinweis: ROWID ist ein interner Zeilenzähler.

|

Stored Procedures

1. Erstellen einer Stored Procedure:

CREATE PROCEDURE procedurname()
anweisung;
...
END PROCEDURE
DOCUMENT "text"

2. Ausführen einer Stored Procedure:

EXECUTE PROCEDURE procedurname()

Hinweis: Beachte: Das Klammerpaar für Parameter muss auch angegeben werden, wenn die Stored Procedures ohne Parameter arbeitet!

Beispiel: Eine Stored Procedure zur Datenkontrolle. Wenn die letzte Zeile einer Bestellung gelöscht wird, soll die gesamte Bestellung gelöscht werden.

Create Procedure Bartkontr(pbnr char (5))
Define anz int;
LET anz = (Select count(*) FROM bart WHERE bnr=pbnr);
IF anz = 0 -- kein Posten zur Bestellung vorhanden
THEN Delete FROM bkopf WHERE bnr = pbnr;
END IF;
END Procedure;

Nun testen wir unsere Stored Procedure und schaffen uns zunächst ein Testbeispiel:

Insert into bkopf values ("98765", "01000", today);
Select * FROM bkopf WHERE bnr = "98765";
Execute procedure bartkontr("98765");

Durch die Stored Procedure bartkontr müsste die Zeile in bkopf gelöscht werden, wir überprüfen es:

Select * FROM bkopf WHERE bnr = "98765";

Die Bestellung "00001" dürfte hingegen nicht gelöscht werden, da sie 3 Positionen enthält:

Select * FROM bkopf WHERE bnr = "00001";
Execute procedure bartkontr("00001");
Select * FROM bkopf WHERE bnr = "00001";

|

Trigger

Beispiel 1: Trigger für die Stored Procedure BartKontr.

Create Trigger TBartKontr
Delete ON Bart
REFERENCING old AS merke
for each row
(execute procedure Bartkontr(merke.bnr));

Beispiel 2: Kaskadentrigger zu Weitergabe von Bestellnummer-Änderungen in bkopf an bart.

CREATE Procedure KBKOPF(pnbnr char(5), pabnr char(5))
UPDATE bart set bnr = pnbnr WHERE bnr = pabnr;
end procedure;

Und nun den Trigger:

CREATE Trigger TKBKOPF
UPDATE OF bnr ON bkopf
REFERENCING new AS NEU old AS ALT
FOR EACH ROW (execute Procedure KBKOPF(NEU.bnr, ALT.bnr));

Beispiel 3: Trigger zur Gewährleistung von gültigen Bestellungen (nur gültige Angebote dürfen bestellt werden).

Stored Procedure und Trigger für Neuaufnahme von Posten:

Create Procedure Angebref(pbnr char(5), panr char(6))
DEFINE vregnr char (5);
Define anz integer;
Define Fehler char (41);
Let vregnr = (Select distinct regnr FROM bkopf WHERE bnr = pbnr);
Let anz = (Select count(*) FROM angebot WHERE anr = panr AND regnr = vregnr);
if anz=0 then -- Fehlendes Angebot
Let Fehler = "Artikel " || panr || " fehlt im Angebot von " || vregnr;
Raise Exception -746,0,fehler;
END IF;
End Procedure;

Test der Procedur mit richtigen Daten:

select regnr,bkopf.bnr,anr FROM bart,bkopf
WHERE bart.bnr="00003" AND bart.bnr = bkopf.bnr;
Select * FROM angebot WHERE regnr="01460";
Execute procedure angebref("00003","000110");

Test der Procedur mit falschen Daten:

Select * FROM artikel;
Execute procedure angebref("00003","300101");
Execute procedure angebref("00003","987AA0");
Execute procedure angebref("08493","300101"); -- falsch

Zur Sicherheit verbessern wir unsere Stored Procedure:

Drop Procedure Angebref;
Create Procedure Angebref(pbnr char(5), panr char(6))
DEFINE vregnr char (5);
Define anz integer;
Define Fehler char (41);
Let anz = (Select count(*) FROM bkopf WHERE bnr=pbnr);
if anz = 0 then
Raise exception -746,0,"Fehler in Tabelle bkopf";
End If;
Let vregnr = (Select distinct regnr FROM bkopf WHERE bnr = pbnr);
Let anz = (Select count(*) FROM angebot WHERE anr = panr AND regnr = vregnr);
if anz=0 then -- Fehlendes Angebot
Let Fehler = "Artikel " || panr || " fehlt im Angebot von " || vregnr;
Raise Exception -746,0,fehler;
END IF;
End Procedure;

Trigger auf die Tabelle BART:

Create Trigger Tangebref
Insert on bart
Referencing NEW AS neu
For each row(execute procedure angebref(neu.bnr,neu.anr));