Meine MatrikelNr: 0557311

Folglich habe ich Aufgabe 1 zu bearbeiten. Diese ist:
einen Kunden erfassen.

Die Aufgabe besteht darin einen Kunden in der Lehrdatenbank unter http://sql.idv.edu zu erfassen. Es sollen keine bestehenden Kunden erfasst werden.

Ich werde zuerst das XML-Dokument programmieren. Anschließend werde ich das Dokument um die DTD erweitern. Für die gestellte Aufgabenstellung erscheint es mir eher sinnvoll eine interne DTD-Deklaration zu verwenden.

Erste Überlegungen:
Das Dokument sollte sinnvollerweise die Struktur haben, die auch die Datenbank verlangt. Ein Kunde hat eine Kundennummer, einen Vornamen, einen Nachnamen, wohnt in einer Straße und an einer bestimmten Postleitzahl.

Also würde ich mal so beginnen:


<kunde>

<nr> 9999 </nr>
<vorname> Franz </vorname>
<nachname> Huber </nachname>
<strasse> Gutshofstraße </strasse>
<plz> 4060 </plz>

</kunde>


Die Datei sieht in einer ersten Vorversion mal so aus:

newclient.xml (xml, 0 KB)

Um nun die die entsprechende DTD-Deklaration in die Datei einzubauen muss zuerst der Standard und anschließen die Deklaration der Tags erfolgen.

Der Standard ist folgender Maßen deklariert:
<?xml version="1.0" encoding="ISO-8859-1" ?>

Danach deklariere ich die entsprechenden Tag, die gebraucht werden:
<!DOCTYPE kunde [

<!ELEMENT kunde (nr, vorname, nachname, strasse, plz)>
<!ELEMENT nr (#PCDATA)>
<!ELEMENT vorname (#PCDATA)>
<!ELEMENT nachname (#PCDATA)>
<!ELEMENT strasse (#PCDATA)>
<!ELEMENT plz (#PCDATA)>

]>

Der Datentyp #PCDATA steht für 'Parsed Character Data'. Dieser Datentyp beschreibt den Inhalt eines Tags. Es kann nicht unterschieden werden, ob es sich um eine Zahl, einen Text oder ein Datum handelt.

Die genaue Deklaration der Datentypen müsste im Rahmen eines XSD-Files passieren. Das XSD (XML Schema Description) ist eine andere Möglichkeit die Datentypen zu deklarieren.
Eine genaue Erklärung was XSD ist und wozu sie verwendet werden kann gibts unter:
http://www.w3schools.com/schema/

So und jetzt ist es so weit. Ich habe mich doch entschlossen keine interne sondern eine externe DTD-Deklaration zu erstellen. Das erscheint mir hier doch der bessere Weg zu sein.

Im XML-Dokument muss man zuerst den Pfad der externen DTD-Datei angeben. Mit dem Schlüsselwort SYSTEM wird mal automatisch im gleichen Ordner nachgesehen, in der auch das XML-File liegt. Der Header der XML Datei sieht dann bei mir so aus:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE kunde SYSTEM "newClient.dtd">

Das ganze XML-File sieht nun so aus: (Leider zeigt dieser XML-Editor die Kopfzeilen nicht an - die Kopfzeilen sind oben - fett dargestellt - ersichtlich. Genau so sind sie auch im XML-Dokument drinnen)

newclient_excl_dtd.xml (xml, 0 KB)

Die dazugehörige DTD so:
<!ELEMENT kunde (nr, vorname, nachname, strasse, plz)>
<!ELEMENT nr (#PCDATA)>
<!ELEMENT vorname (#PCDATA)>
<!ELEMENT nachname (#PCDATA)>
<!ELEMENT strasse (#PCDATA)>
<!ELEMENT plz (#PCDATA)>

Betrifft Punkt 1 der Aufgabenstellung:
Meiner Ansicht nach ist die folgende SQL-Anweisung, diejenige welche den gefragten Sachverhalt beschreibt.

SELECT Kunde.Nr, Kunde.Nachname, Count(Auftrag.Nr) As Anzahl, Sum(Auftragspos.Menge*Buch.Preis) as Auftragssumme FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum > '2001-01-01' GROUP BY Kunde.Nr

Sie liefert als Ergebnis 24 Tupel.

Sie liefert nämlich aus das gleiche Ergebnis wie folgende Abfrage. Es wurden aber die Summe bzw. die beiden Tabellen Buch und Auftragspos entfernt. Würde die vorige Abfrage falsch sein, dann würden durch die 2 weggefallenen Joins andere Ergebnistupel geliefert werden --> man erwartet aber, dass das gleiche Ergebnis zurückkommt (wenn man annimmt, dass die erste Abfrage, dass gleiche Ergebnis geliefert hat)
Das Ergebnis sind 24 Tupel mit den gleichen Werten und Namen

SELECT Kunde.Nr, Kunde.Nachname, Count(Auftrag.Nr) As Anzahl FROM Kunde, Auftrag WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Datum > '2001-01-01' GROUP BY Kunde.Nr

Geht man nun auf die höchste Ebene der Auswertungsmöglichkeit - nämlich der Abfrage wie viele Aufträge nach dem 01. Jänner 2001 eingegangen sind - dann erhält man als Ergebnis 24!!

select count(Auftrag.Nr) from Auftrag where Auftrag.Datum > '2001-01-01'

Somit ist bewiesen, dass die Abfragen richtig sind.

edit - korrigierte Aufgabenstellung -----------------

ausgehend von dieser Abfrage:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

sollte eine Weiterentwicklung der Abfragen stattfinden.

1)
Es soll der Umsatz noch zusätzlich zur Anzahl der Aufträge in die Abfrage eingebaut werden.
Ich habe das mit dieser Abfrage bewerkstelligt. Das Ergebnis (=Anzahl Tupel) ist das gleich wie vorhing.

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr) as Anzahl, sum(Auftragspos.Menge*Buch.Preis) as Auftragssumme FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr ORDER BY Kunde.Nr

2) und 3)
Als nächstes sollte eine Verteilung über die Zeit stattfinden (ohne Anzahl und Umsatz der Aufträge).
Ich habe eine Verteilung der Aufträge in Jahr vorgenommen. Eine Verteilung über Einschränkungen in der Where-Klausel sind weiter oben zu sehen. Diese wollte ich nun komprimieren und erweitern.

Da die Lehrdatenbank leider keine gängige SQL-Syntax wie zB in Oracle SQL oder MySQL (nämlich die Durchführung von SubQueries) zulässt, musste ich diese Abfrage etwas komplizierter gestalten.
Da ich bei SubQueries immer eine Fehlermeldung 'use joins to connect all tables' erhalten habe und ich nicht weiß wie man mittels joins in einer Abfrage 2 mal die gleiche Tabelle mit unterschiedlichen Spalten referenzieren soll, half ich mir mit Funktionen.
Ich wollte nämlich in 2 getrennten Spalten die Anzahl der Aufträge je Jahr angezeigt haben.
Also nam ich die Funktion Year(<Date>), die mir das jeweilige Jahr des Datums liefert und zählte für die Spalte 2000 das Vorkommen des Jahres 2000 bzw. für 2001 das Vorkommen des Jahres 2001.

Die folgende Anweisung erzeugt genau das gewollte Ergebnis:

SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Aufträge2000, sum(year(Auftrag.Datum)=2001) as Aufträge2001 FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Aufträge2001>0 or Aufträge2000>0

Wiederum ändert sich die Anzahl der Tupel nicht. Als nächstes habe ich noch die Anzahl aller Aufträge und den Gesamtumsatz eingebunden.

SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Anzahl2000, sum(year(Auftrag.Datum)=2001) as Anzahl2001, count(Auftrag.Datum) as Gesamtanzahl, sum(Auftragspos.Menge*Buch.Preis) as Gesamtumsatz FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Anzahl2001>0 or anzahl2000>0

4)
Als nächstes sollten noch die durchschnittliche Anzahl von Aufträge ausgewertet werden. Ich muss gestehen, dass die Aufgabenstellung für mich sehr unverständlich geschrieben ist, da weder ein Zeitraum angegeben ist noch angegeben ist welcher Durchschnitt berechnet werden soll.
Der Durchschnitt aller Aufträge, die je gegeben wurden macht eher wenig Sinn. Durchschnittlicher Umsatz je Auftrag macht schon Sinn, aber nicht im Zusammenhang mit oben angeführter Auswertung. Es bringt nämlich nicht, wenn in jeder Zeile zusätzlich noch der durchschnittliche Umsatz je Auftrag angeführt wird. Der ist nämlich logischerweise in jeder Zeile gleich.
Ich habe mich also dazu entschlossen den durchschnittlichen Umsatz je Kunde zu errechnen. Ich hab diese Abfrage nicht mit der AVG() Funktion durchgeführt, da sonst ein falsches Ergebnis geliefert wird.

SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Anzahl2000, sum(year(Auftrag.Datum)=2001) as Anzahl2001, count(Auftrag.Datum) as Gesamtanzahl, sum(Auftragspos.Menge*Buch.Preis) as Gesamtumsatz, (sum(Auftragspos.Menge*Buch.Preis) / count(Auftrag.Datum)) as Durchschnittsumsatz FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Anzahl2001>0 or anzahl2000>0

Zuletzt zeige ich noch, dass die Abfragen auch stimmen. Ich filtere jetzt nur jene Kunden, die in beiden Jahren Aufträge an uns gegeben haben. Dazu ändere ich in der letzte Zeile (=HAVING-Klausel) das OR zu einem AND.

SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Anzahl2000, sum(year(Auftrag.Datum)=2001) as Anzahl2001, count(Auftrag.Datum) as Gesamtanzahl, sum(Auftragspos.Menge*Buch.Preis) as Gesamtumsatz, (sum(Auftragspos.Menge*Buch.Preis) / count(Auftrag.Datum)) as Durchschnittsumsatz FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Anzahl2001>0 and anzahl2000>0

Schränkt man die HAVING-Klausel auf nur Anzahl2001>0 ein, dann erhält man wieder 24 Tupel. Genau wie in allen Abfragen davor, die 2001er Aufträge abgefragt haben.

Der folgende Beitrag deckt die 2. Aufgabe der Vorlesung IV 2 ab.
Es sollen auf einem Web-Datenbank SQL abfragen durchgeführt werden. Diese Abfragen sollen beschrieben werden und die entsprechenden SQL statements sollen auch veröffentlicht werden.
Anbei meine Einfälle für Abfragen inkl. entsprechender Dokumentation.

1. Um einen ersten groben Überlblick zu erhalten werde ich als erstes einmal abfragen wieviele Kunden überhaupt in der Datenbank sind. Das heißt wieviele Kunden hab ich (als Unternehmen).

Das funktioniert folgender Maßen:

SELECT COUNT(*) FROM Kunde


2. Da ich jetzt einmal weiß wieviele Kunden ich hab, möchte ich als nächstes wissen welche Kunden mehr als 5 Bestellungen offen haben. Mit dieser Information kann ich meine Logistik und mein Bestellwesen entsprechend steuern und so versuchen eine möglichst kurze Durchlaufzeit von Bestellungen zu erreichen, damit die Kunden nicht ewig auf die Bestellung warten müssen

Ich habe die Info mit folgender SQL-Anweisung aus der Datenbank ausgelesen:

SELECT Kunde.Nr, Kunde.Vorname, Kunde.Nachname, COUNT(Auftrag.Nr) AS Anzahl
FROM Kunde, Auftrag
WHERE Kunde.Nr=Auftrag.Kundennummer
GROUP BY Kunde.Nr
HAVING(Anzahl) > 5


3. Meine nächste Abfrage beschäftigt sich damit welcher Verlag wie viele Bücher zur Verfügung stellen kann. Dabei sollen die Bücher, die der Verlag im Programm hat aufsummiert werden (Anzahlen). Diese sollen dann absteigend (die größte Anzahl zuerst) ausgegeben werden. Somit kann man feststellen welcher Verlag der am stärksten vertretene ist.
Es sollen Name (LangName) des Verlags plus die Anzahl der Bücher ausgegeben werden.

Das ensprechende Statement für diese Aufgabe ist:

SELECT Verlag.Name AS Verlag, COUNT(Buch.Nr) AS Anzahl_Buecher
FROM Buch, Verlag
WHERE Buch.Verlag = Verlag.Kurzbezeichnung
GROUP BY Verlag
ORDER BY Anzahl_Buecher DESC


4. Als nächstes möchte ich die größten Kunden wissen, die uns aktuell Bestellungen (Aufträge) gegeben haben. Dabei sollen die Kundennummer, Name , Summe der bestellten Bücher und Auftragssumme (Buch*Preis) der Bestellungen ausgegeben werden.
Es werden vorerst einmal alle Kunden ausgegeben, deren Auftragswert über EUR 100.000,00 ist. Mit dieser Abfrage kann ein eventuelle Klumpenrisiko ermittelt werden. (Ein Klumpenrisiko zeigt jene Kunden , die eine sehr große Auftragssumme offen haben. Damit kann man sehen, bei welchen Kunden es besonders wichtig ist, dass sie nicht ausfallen)

Folgendes Statement liefert das entsprechende Ergebnis:

SELECT Kunde.Nr, Kunde.Vorname, Kunde.Nachname, SUM(Auftragspos.Menge) AS Summe_Buecher, SUM(Buch.Preis*Auftragspos.Menge) AS Auftragssumme
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nr
HAVING Auftragssumme > 100000
ORDER BY Auftragssumme DESC


5. Welcher Kunde hat Bücher bestellt, die nicht auf Bestand sind oder auslaufen und der Kunde mehr bestellt hat als auf Lager sind. Diesen Kunden muss natürlich eine Nachricht geschrieben werden, in der sie darauf aufmerksam gemacht werden, dass die Bücher nicht mehr (zur Gänze) oder mit Verspätung lieferbar sind.

Statement:

SELECT Kunde.Nr AS KNr, Kunde.Nachname, Auftrag.Nr AS Auftrag, Buch.Nr AS BuchNr, Buch.Titel, Buch.Auslaufend, Buch.Bestand, Auftragspos.Menge
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
AND (Buch.Bestand=0
OR (Auftragspos.Buchnummer=Buch.Nr
AND Buch.Auslaufend="y"
AND Auftragspos.Menge > Buch.Bestand))


6. Als letztes möchte ich wissen bei welchen Büchern ich generell einen Engpass habe (Aber nur bei jenen Büchern, die auslaufen). In der letzten Abfrage sind Bücher durch den Rost gefallen, wo Kunden zwar weniger als die auf Bestand liegenden bestellt haben, in Summe über alle Bestellungen aber auch mehr Bücher bestellt wurden als auf Lager sind.

Die Engpässe ermittle ich mit folgender Anweisung:

SELECT Buch.Nr, Buch.Titel, Buch.Bestand, Buch.Auslaufend, SUM(Auftragspos.Menge) AS Menge_Bestellung
FROM Auftragspos, Buch
WHERE Auftragspos.Buchnummer=Buch.Nr AND Buch.Auslaufend="y"
GROUP BY Buch.Nr
HAVING(Menge_Bestellung)>Buch.Bestand