Visual SQL - Eine ER-basierte Einführung in die

Visual SQL - Eine ER-basierte Einführung in die
Datenbankprogrammierung
Teil I
Bernhard Thalheim
Computer Science Institute, Brandenburg University of Technology at Cottbus
PostBox 101344, D-03013 Cottbus, Germany;
[email protected]
Preprint BTU Cottbus, Computer Science Institute, I-08-2003
23. Mai 2003
Zusammenfassung
Datenbank-Programmierung ist für den Novizen und auch den Programmierer oft ein Buch mit sieben
Siegeln, sobald die Programmieraufgabe etwas komplexer wird. Der Grund liegt auf der Hand: Es ist unmenschlich, eine oder auch mehrere Seiten Code zu lesen, diesen zu verstehen und auch in allen Facetten
zu erfassen. Meist sind viele kleinere Nebenbedingungen mit zu erfassen bzw. im Auge zu behalten. Oft
erschweren Unzulänglichkeiten von SQL auch die Formulierung.
In diesem Preprint wird eine andere Auffassung begründet, die sich mehr auf die Möglichkeiten der
multimedialen Welt stützt und dem Benutzer durch eine graphische Gestaltung entgegen kommt. Es wird
basierend auf dem ER-Modell ein andere Formulierung von komplexen SQL-Anfragen vorgenommen, die
sich stark an das ER-Paradigma anlehnt.
Inhaltsverzeichnis
1 Einführende Bemerkungen
1.1 SQL - Möglichkeiten und Grenzen . . . . . . . . . . . . . . . . . .
1.2 Das verwendete Hauptbeispiel: Das Universitätsverwaltungsschema
1.3 Visualisierung - Möglichkeiten und Grenzen . . . . . . . . . . . . .
1.4 Visual SQL und seine Vorläufer . . . . . . . . . . . . . . . . . . . .
2 Einführung in SQL und in Visual SQL
2.1 Definition und Modifikation von Datenbanken . . . . . . . . .
2.2 Anfragen an DBS . . . . . . . . . . . . . . . . . . . . . . . . .
2.3 Weitere Typen von Anfragen: Vereinigung, Korrelation, ALL
2.4 Sichten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Literatur
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2
2
2
4
5
.
.
.
.
7
7
21
43
48
59
Teil II
3
Erweiterungen von Visual SQL
3.1 Pfad-Variable
3.2 Meta-Anfragen
3.3 Definition, Kontrolle und Pflege von Integritätsbedingungen
4
Formale Semantik von Visual SQL
1
2
1
1.1
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Einführende Bemerkungen
SQL - Möglichkeiten und Grenzen
SQL ist wie jede lineare Sprache beschränkt überschaubar. Schemata sind wesentlich besser geeignet, um
strukturelle Zusammenhänge einfach und zugleich konsistent zu beherrschen. Deshalb wird eine Sprache Visual
SQL entwickelt, mit der die Datenbankprogrammierung visualisiert werden kann.
SQL ist als Kompromiss entstanden. SQL hat als Sprache der vierten Generation viel aus den Fehlern
anderer Programmiersprachen gelernt [MeS93, MeS02]. Durch eine auf Kompromiss ausgerichtete Standardisierung sind allerdings auch ‘features’ entstanden, die wenig durchdacht erscheinen und deren Anwendung auch
eine gute Ausbildung und viel Praxiserfahrung erfordert. Die Vor- und Nachteile von relationalen Sprachen
sind sehr gut in [Heu92] erläutert. Die Einschränkung auf relational vollständige Sprachen ist an der Einfachheit und relativen problemlosen Realisierbarkeit der relationalen Algebra orientiert. Zum anderen ist SQL
eine Sprache, die weit über die Mächtigkeit der Prädikatenlogik der ersten Stufe hinausgeht [Libk01]. Damit
ist bereits der gut ausgebildete Informatiker relativ einfach zu überfordern. Grund für diese ungerechtfertigte Mächtigkeit sind Aggregations- und Gruppierungsoperationen, denen eine wohldurchdachte Ausarbeitung
nicht zugesprochen werden kann [LeN98, LeT01]. Noch schlimmer sind Nullwerte, die mit amerikanischer Unbedarftheit eingeführt worden sind, die in der praktischen Benutzung stark mit unterschiedlichen Bedeutungen
belastet werden und aufgrund der fehlenden Dokumentationserzwingung von SQL auch dann einen Benutzer
zum Kunden des Orakels von Delphi werden lässt. Typisch für den letzteren Fall sind Unterscheidungen, die
ein Benutzer zwischen NOT IN und NOT EXISTS vornehmen muss, sobald Nullwerte erlaubt sind.
SQL ist bewusst so beschränkt worden, damit alle Algorithmen, die die Datenbankoperationen unterlegen,
auch mit einer maximalen Komplexität von O(n log n) realisiert werden können. Dazu gehört auch die
Nichtaufnahme einer Rekursion in SQL’92.
Wie jeder Visualisierung ist auch Visual SQL eine Grenze gesetzt. Visualisierung ist nur dann sinnvoll,
wenn sie überschaubar bleibt, d.h. auf ein Teilschema begrenzt, das sich auf einer Seite repräsentieren lässt.
Visualisierung ist nutzlos für sehr grosse Zusammenhänge, ersetzt auch nicht das Nachdenken und Formulieren, sondern dient nur als Hilfestellung. Die undurchdachte Losung, dass durch ein Bild tausend Worte
ersetzt werden, ist von Denkfaulen leicht akzeptiert, hält aber keiner genaueren Betrachtung stand. SQL und
Programmierung erfordern eine Ausbildung und können weder in Jauch’schen Quiz-Shows noch in bebilderten
Knoff-Hoff-Sendungen gelernt werden.
Jede Gemeinschaft lebt von der Entwicklung, so auch die SQL-Programmierer- und -Anwender-Gemeinschaft.
SQL wurde bereits in der Variante von SQL’92 mit Konstrukten versehen, die einer theoretischen Basis entbehren. So wird z.B. die Erzwingung von Integritätsbedingungen mit einer Vielzahl von Varianten unterstützt, die
nicht ohne weiteres unterschieden werden und modelliert werden können. Es können Integritätsbedingungen
vor der Ausführung von Anweisungen kontrolliert werden und so auch die Ausführung vollständig blockieren, solange die Datenbank sich in einem Zustand befindet, der inkonsistent zu den Bedingungen ist. Damit
wird eine globale Semantik des Datenbankverhaltens unterstützt. Dies war sicherlich nicht die Intention von
SQL’92. Die Isolationsmodi für Transaktionen sind ein anderes Beispiel. Da ein Programm auch die Kontrolle
der Integritätsbedingungen mit steuern kann, werden auch dynamische Integritätsbedingungen unterstützt.
Ein Problemkreis von SQL muss mit besonderer Vorsicht behandelt werden: SQL nutzt Multimengen
(“bag”) und nicht nur Mengen. Demzufolge haben die relationalen Operationen andere Gesetzmässigkeiten
als die SQL-Operationen. Damit werden auch Auswertungsoperationen anders spezifizierbar, kommutieren
nicht und müssen auf andere Art programmiert werden.
1.2
Das verwendete Hauptbeispiel: Das Universitätsverwaltungsschema
Es wird eine kleine Universitätsanwendung als Referenzbeipiel angenommen. In dieser Anwendung werden
Studenten, deren Beziehungen, Universitätsmitarbeiter, Studiengänge, Institute, Vorlesungen und Projekte
durch eine Datenbank verwaltet.
Ein Beschreibung des Entity-Relationship-Modelles ist in [Thal00] zu finden.
c
Visual SQL °by
β
3
Ordnungen(Prüfung,Studien,Praktikum,Immatrikulation)
Berater
SName
µ
Name(Vornamen < Vorname >,Familienname,{Titel})
PrüfungsamtMitarbeiter
Studiengang
*
Person
Adresse(Staat,PLZ,Ortschaft,Strasse(Name,Nr))
Geburtsdaten(Datum, Ort, Staat)
K
6
K I
Bis
EingeschriebenIn Nebenfach
Betreuer
Von
Von
RaumSekretariat
?/
Postkasten
Student
Verantwortlich
)
MatrNr
j
-
Institut
Fakultät
y
Bezeichnung
¾
-
In
Kostenstelle
Spezialisierung
Professor
>
O
Telefon{Tel}
IName
¾
6
Juniorprofessor
Leiter bzw. Befristung(Von,Bis)
Verantwortlicher
Lehrstuhl
Sprecher
Mitarbeiter
L
Fristen(Einschreibung,Examen)
Resultat
-
Belegt
Zeit(Tag,Paar)
Vorlesung
Hat
Betrag
Vertrag(Von,Bis,Wochenstunden)
Status
9
+
Semester
Jahr
Jahreszeit
?
Raum
Nr
Gebäude
KursNr
Kurs
6
KursBezeichnung
6
Zuwendungsbescheid
Projekt
SetztVoraus
IstVorausgesetzt
Voraussetzung
?
Beginn
Kostenstelle
Name
Nummer
Ende
ProjektBezeichnung
Bild 1: Das HERM-Diagramm des Universitätsinformationssystemes
4
B. Thalheim,
1.3
Preprint BTU - Informatik - I-08-2003
Visualisierung - Möglichkeiten und Grenzen
Visualisierung ist ein hervorragendes Hilfsmittel, um eine Übersicht über die verwendeten Bausteine zu behalten. Visualisierung ist bereits auch für SQL mehrfach vorgeschlagen worden. Der erste Versuch geht auf
QBE (Query-by-example) von [Zlo77] zurück. Obwohl dieser Ansatz für einfache Anfragemengen schon sehr
gute Resultate zeigte, ist er in der Praxis und bei der Standardisierung nicht angewandt worden. QBE hat
allerdings in den letzten Jahren eine viel grössere Popularität erlangt, weil mit Web-Anwendungen wieder
eine graphische Oberfläche eingefordert wurde. Der beste Versuch in [ChT02] stellt SQL-Anfragen in einer
visualisierten Form dar. Dieser Versuch hat auch unsere Bemühungen zu Visual SQL angespornt. [ChT02] ist
leider steckengeblieben bei der einfachen Graphik. Es sind keine Versuch unternommen worden, eine Semantik zur Visualisierung vorzulegen. Weiterhin ist unterblieben, die unterschiedlichen Visualisierungsansätze zu
harmonisieren. Es sind leider auch z.T. widersprüchliche Notationen verwendet worden, so dass der Ansatz
von [ChT02] nicht so weit trägt, wie die Autoren behaupten.
Es gibt eine ganze Reihe von Versuchen, SQL in einer visualisierten Form darzustellen. [Den95] orientiert
auf ein visuelles Interface zu SQL.
Viele dieser Versuche sind allerdings eher auf halbem Wege steckengeblieben: [Fel02] versucht z.B. eine
partielle Visualisierung. [CCL00] erlaubt eine Visualisierung für einige Konstrukte.
[BOO02] untersucht, inwieweit sich eine visualisierte Sprache direkt implementieren lässt. Dieser Versuch
ist noch nicht vollständig beendet, erfordert aber eine umfangreiche Systemunterstützung. Der am weitesten
gehende Ansatz wurde in [Vis02] implementiert. Er orientiert sich allerdings noch an der tabellarischen Form
und der Filestruktur, so dass die Formulierung von Anfragen nicht unterstützt wird.
Visualisierung bietet demzufolge eine Reihe von Vorteilen:
Überblick und Gesamtübersicht: Mit einer graphischen Darstellung kann der Zusammenhang einer mittleren
Anzahl von Datenbankobjekten relativ einfach dargestellt werden. Der Überblick bleibt selbst für Anfragen erhalten, die etwas mehr als nur einfache Verbundoperationen umfassen.
Zusammenhänge werden erhalten: Da ein Schema nicht selten mehrere hundert Datenbankobjekte umfasst und
da diese Objekte nicht selten auch relativ problemreich miteinander verbunden sind, ist eine zusammenhängende Darstellung von Relationen, die innerhalb einer Anfrage verwendet werden, die einzige
Möglichkeit, den Überblick zu behalten.
Einfache Pflege: Anfragen werden relativ oft wieder verwendet. Wird ein Datenbank-Schema im Verlaufe der
Evolution des Datenbank-Systemes verändert, dann müssen sich auch die wieder verwendeten Anfragen
ändern. Eine solche Veränderung ist nur noch möglich, wenn man den Überblick erhält.
Einfache Erweiterung: Anfragen werden auch relativ oft als Kernbestandteile anderer Anfragen verwendet.
Diese Art von Wiederverwendung ist nur möglich, wenn man sich auch gleichzeitig über die Implikationen
der Wiederverwendung informieren kann.
Einfache Korrektheitsprüfung: Eine Korrektheitsprüfung einer grösseren Anfrage kann durchaus auch einen
erprobten Programmierer überfordern.
Visualisierung hat auch einige, nicht zu übersehende Nachteile:
Anwendbarkeit für mittlere Anwendungen: Grosse und sehr grosse Anwendungen erfordern eine andere Visualisierung und eine ausgefeiltere Technik der Abstraktion. Beobachtungen von [Mood01] zeigen, dass
bereits ab etwa 30 Entity- und Relationship-Typen in einer Übersicht der Überblick verloren geht. diese Einschränkung lässt sich allerdings durch die Beobachtung relativieren, dass auch Anfragen relativ
selten eine derartige Vielfalt von Typen erfordern.
Initialer Aufwand: Eine graphische Sprache macht wenig Sinn, wenn das Schema einen relativ einfachen Weltausschnitt darstellt. Dann ist der Aufwand für das Zeichnen und Erstellen zu hoch und der Nutzeffekt zu
gering. So wird es auch in vielen Beispielen der kommenden Kapitel sein. Wir versuchen, die gesamte
c
Visual SQL °by
β
5
Sprachvielfalt von SQL durch Visual SQL zu unterlegen. Dazu bedarf es jedoch auch einer Grundlegung aller Konstrukte. Wir versuchen hierbei, möglichst einfache Beispiel zu verwenden. Damit wird
der initiale Aufwand noch sichtbarer.
Visualisierung mit der falschen Granularität: Oft werden auch interne Schema-Beschränkungen in einer Anfrage
wirksam. Die inneren Zusammenhänge müssten dann ebenso visualisiert werden wie die Beteiligung der
Relationen. Dazu bedarf es dann anderer Techniken, die bereits im Rahmen der Computergeometrie
ausgearbeitet wurden, allerdings noch keine Berücksichtigung in der Datenbank-Technologie gefunden
haben.
Visualisierung ist kein Universalmittel zur Lösung aller Probleme. “Ein Bild sagt mehr als 1000 Worte” ist nur
solange wahr, wie das Bild eine unmissverständliche Semantik besitzt.
Fehlende Vergleichsmöglichkeiten, wie z.B. die in den Physikbüchern verwendete Darstellung der Gravitationskraft oder die Darstellung der Gesetze von Moore’s, Gilder’s oder Metcalfe’s ohne Kontext,
bringen wenig.
Farbsemantik ist oft schwieriger, als man gemeinhin meint. ‘Rot’, ‘grün’, ‘weiss’ und ‘schwarz’ haben in
unterschiedlichen Kulturen z.B. sehr unterschiedliche Semantik.
Die Repräsentation von komplexen Strukturen - wie z.B. in den Schaubildern der Medizin - erreicht oft
das Gegenteil.
Eine exklusive Verwendung von Graphiken wie z.B. bei ER-Diagrammen verwischt oft die Inhalte.
Die Einfachheit von mind maps bzw. topic maps oder von baum-strukturierten Ontologien, wie z.B.
Carl von Linne’s biologische Klassifikation, verhindert tieferes Verständnis.
Fernsehen, Massenmedien oder auch Filme basieren auf Bildern, denen oft die Semantik - z.T. auch
bewusst - genommen wurde.
1.4
Visual SQL und seine Vorläufer
Visual SQL ist keine vollständig neue Erfindung, sondern hat einige Vorgänger, die hier kurz erwähnt und
betrachtet werden sollen. Die Entwicklung visueller Anfragesprachen hatte einen Höhepunkt zu Beginn der
90er Jahre und wurde dann wieder eingestellt. Dafür gab es einige Gründe:
Die objekt-relationale Technologie hat sich erst um 2000 durchgesetzt. Visuelle Anfragesprachen wurden jedoch
viel früher eingeführt
Die Industrie verwertet gute Ideen langsam und ggf. auch nur bei harten Anforderungen, weil eine Investition
erforderlich ist.
Monopolisten sind nicht am Kunden interessiert und wollen eher einen höheren Profit durch grössere Abhängigkeit aufgrund schlechterer Infrastruktur erreichen.
Die Objekt-Orientierung hat die Forschung in die falsche Richtung gelenkt und damit die konzeptionelle Arbeit
in den Hintergrund.
Eine Visualisierung ist schwieriger zu implementieren und zu verwalten wegen einiger Defizite in der Forschung
und auch bei der vorhandenen Infrastruktur.
Die Visualisierung führt zu höherem Aufwand bei der Berechnung und bei der Entwicklung von Software.
Viele dieser Gründe sind jedoch heute sehr stark überholt. Deshalb kann auch eine Visualisierung heute unter
ganz anderen Gesichtspunkten benutzt werden.
6
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Die Vielzahl der universitären Prototypen ist in den 90ern nicht nur an der fehlenden Hard- und SoftwareInfrastruktur gescheitert, sondern auch an den eigenen Beschränkungen, wie die folgende Tabelle zeigt:
Sprache
Disjunktion
Group
By
ForAll
Anfrage
Min
Max
Count
Graphische Sprachen
STBE
Y
S
S
Y
Y
PASTA
NA
NA
lim
Y
Y
G+
Y
NA
NA
Y
Y
DOODLE Y
Y
NA
?
?
OrionQL
?
NA
NA
?
Y
G-WHIZ
NA?
S
lim
?
?
VQL
Y
Y
Y?
Y
Y
WbQL
S
S
Y
S
S
Lineare Sprachen
XSQL
Y
S
S
Y
Y
O2 QL
Y
Y
Y
Y
Y
Postgres
Y
Y
S?
Y
Y
Hilog
S
S
S
S
NA?
Erklärung: Y (explizit definiert), NA (nicht definiert),
ALL
SOME
Mengen- Rekur- Foropera- sion
male
tionen
Semantik
Negation
ParaSchach- Schemetri- temasierung lung
Anfrage
S
S
NA
NA
?
lim
Y
S
Y
S
NA
NA
?
NA
NA
S
S
lim
S
NA
NA
lim
Y?
S
NA
NA?
NA
Y
NA
NA
lim
S
NA
lim
lim
Y
?
lim
NA
Y
Y
NA
Y
Y
?
?
Y?
Y
Y
NA
Y
Y
?
lim
lim
Y
Y
Y
S?
lim
NA
NA?
Y
Y
Y
S?
lim
Y
Y
Y
Y?
lim?
S
lim
?
?
Y
S
Y
Y
Y
?
Y
Y
lim (eingeschränkte Form), ? (unklar), S (simulierbar)
NA
NA
NA
Y
NA
NA
lim
Y
Y
NA?
lim
Y
STBE [OMO89] hat eine recht ausgefeilte visuelle Sprache, besitzt auch eine formale Semantik und unterstützt die Schachtelung. G+ [CMW88] konzentriert sich auf binäre Relationship-Typen, ist aber relativ
unscharf definiert. DODDLE [Cru92] geht hier einen Schritt weiter und nutzt die F-Logik [KiL89] als formale
Grundlage. Pasta-3 [Kun89] ist die einzige der betrachteten Sprachen, die auf einem ER-Modell basiert. Sie
ist die mächtigste der betrachteten Sprachen, wurde aber in die obige Übersicht nicht aufgenommen, weil
keine formale Definition irgendeines Konstruktes der Sprache existiert. VQL [VAO93] besitzt eine ähnliche
Mächtigkeit wie Pasta-3, ist aber von den Konstrukten her unglücklich entworfen. G-WHIZ (grid with hierarchies, imitating Zloof) [HeR85] ähnelt in der Anlage QBE, ist aber ebenso beschränkt in der Ausdruckskraft
wie QBE.
Alle Forschungsansätze sind unvollständig geblieben. Es fehlen meist Konstrukte
zur Darstellung der universellen Quantifizierung durch FOR ALL oder auch ANY mit einer Ausdrucksfähigkeit
analog zu SQL,
zur Darstellung rekursiver Anfragen und deren Auflösung,
zur Behandlung der Negation und deren Übersetzung,
zur Einführung von Sichten und temporären Tabellen sowie deren Ablage,
zur Unterstützung objekt-relationaler Sprachen einschliesslich von Translationsverfahren und
zur Einführung von Abstraktionen z.B. durch strukturelle Rekursion [BBN91].
Alle Forschungsansätze verwenden ein eigenes graphisches Format, das erst erlernt werden muss. Bis auf
Pasta-3 werden Methoden der konzeptionellen Modellierung nicht verwandt, so dass die Sprachen eigenständig
stehen, aber kaum eine Berührung mit Schemata der konzeptionellen Modellierung vorweisen.
Diesen Forschungssprachen stehen industrielle Entwicklungen gegenüber. Verschiedene DBMS unterstützen
zumindest einige der Konstrukte. Besonders zu nennen sind dabei die folgenden Konstrukte:
Ad-hoc-Anfragen: DB2/QMF, (ORACLE), INGRES, Parabase, DBM/QM, DBase ..., Paradox, MS Access,
Superbase, Q&A
Teilanfragen: (Oracle), Parabase (via SQL)
Anfrage-Wiederverwendung: (INGRES), (Parabase), (Paradox), (MS Access), (Superbase)
c
Visual SQL °by
β
7
Universal-quantifizierte Anfragen: Paradox
Negation: (DB2/QMF), (((ORACLE))), (INGRES), (Paradox)
Lokalisierungsabstraktion (Scoping): (((ORACLE)))
Gruppierung: (((ORACLE))), (INGRES), (Paradox)
Geschachtelter Output: (((ORACLE)))
Mengenoperationen: (((ORACLE)))
Features: INGRES (einfache Meta-Anfragen zum Schema), Q&A (natürlichsprachige Anfragen).
2
Einführung in SQL und in Visual SQL
Im Folgenden beschränken wir uns stärker auf die Einführung von Visual SQL. Es wird eine rudimentäre
Kenntnis von SQL - zumindest SQL’92 - vorausgesetzt. Zu SQL existieren viele Bücher, die dem individuellen
Lernstil angepasst sind. Wir enthalten uns daher auch einer umfangreichen Literaturempfehlung. Für den
Datenbankkurs empfehlen wir für die Einführung in die Technologie der Datenbanksysteme [KeE01] und
[GUW02], als Überblick über die Datenbanksysteme [ElN00, GUW02], sowie für eine Einführung zu den
Grundlagen [Bisk95, LevL99, GUW02]. Für die Kurse Datenbanken I und Datenbanken III sind [KeE01] und
[GUW02] aus unserer Sicht die beste Literaturempfehlung. Es gibt auch viele weitere Bücher, sowie auch
Beispiele von Anti-Büchern, die selbst mit der vierten Auflage noch viele weitere Fehler zu den vorhandenen
hinzufügen.
2.1
2.1.1
Definition und Modifikation von Datenbanken
Definition von Schemata in Visual SQL
Visual SQL soll auch eine Definition der Datenbank selbst ermöglichen. Der wichtigste Definitionskonstrukt
wird in Bild 2 dargestellt. Relationen werden durch ein Rechteck mit einem Namen der Relation aufgeführt.
Der Primärschlüssel wird durch ein Schlüsselsymbol beim Attribut angezeigt. Jedem Attribut kann sein Datentyp hinzugefügt werden.
Weiterhin werden jedem Typ auch seine referentiellen Integritätsbedingungen angefügt. Da ggf. auch mehrere Attribute eine referentielle Integritätsbedingung formen, sind mehrere Pfeile von einer referenzierenden
Relation zur referenzierten Relation zugelassen. In unserem Beispiel ist z.B. die Inklusionsabhängigkeit
Professor [Name, Geburtsdatum] ⊆ Person [Name, Geburtsdatum]
durch zwei Pfeile angezeigt.
Die entsprechenden SQL-Defintionen für dieses Schema sind dann direkt durch die folgenden Konstrukte
gegeben:
CREATE TABLE Person (
Name
char[40]
Primary Key,
Gebdatum
date
Primary Key,
Geburtsort
char[20],
Adresse
char[60]
);
CREATE TABLE Professor (
Name
char[40]
Primary Key,
Gebdatum
date
Primary Key,
Spezialisierung varchar[50],
InIName
char[15]
Foreign Key References Institut(IName),
Foreign Key (Name,Gebdatum) References Person
);
8
B. Thalheim,
I
Preprint BTU - Informatik - I-08-2003
Studiengang
◦
SName
char[20]
verantwortlichInstitut char[15]
I
EingeschriebenIn
◦
SName
Person
Name
Gebdatum
◦
Student
char[40]
◦
date
Geburtsort
char[20]
Adresse
char[60]
Y
M
Y
M
◦
◦)
MatrNr
Name (
Gebdatum (
char[7]
char[40]
◦)
À
]
Bis
date
Betreuer
date
Professor
Gebdatum
¾
◦
char[20]
date
◦
Betr.Name
◦
char[7]
Von
MatrNr
Name
◦
Stud.MatrNr
Betr.Gebdatum
char[40]
ª
date
ª
Spezialisierung
varchar[50]
In.Iname
char[15]
Von
◦
char[7]
◦
char[40]
◦ date
date
Bild 2: Definition der relationalen Schemata
CREATE TABLE Betreuer (
MatrNr
BetrName
BetrGebdatum
Von
);
char[7]
char[40]
date
date
Primary Key
Primary Key
Primary Key
CREATE UNIQUE INDEX StudentSecondary
Foreign Key References Student,
Foreign Key References Professor,
Foreign Key References Professor,
ON Student (Name, Gebdatum);
Zu einem Attribut oder auch einer Gruppe von Attributen kann auch ein Index definiert werden. Diese
Definition wird auch graphisch wie in Bild 3 vorgenommen. Eine Attributdefinition umfasst insgesamt folgende
Facetten:
Attributname: Der Name des Attributes muss innerhalb einer Relation eindeutig sein. Eine Verwendung des
gleichen Namens in einer anderen Relation sollte auf einen möglichen Zusammenhang verweisen.
Datentyp: Jedes Attribut ist einem Datentyp zugeordnet. Mit einer Definition des Datentyps ist auch eine
Algebra oder auch relationale Struktur zu diesem Datentyp gegeben. Typische Operationen wie die Addition oder Konkatenation, typische Vergleichs- und Ordnungsrelationen sollten für alle selbst definierten Typen erklärt werden. Jedes kommerzielle System offeriert eine Vielzahl von Typen. Da relationale
DBMS streng typisiert sein sollten, ist in diesem Fall auch mit jedem Typen ein Menge von Typkonversionsoperationen verbunden. Übliche Datentypen in relationalen DBMS über die bereits verwendeten
sind u.a.:
NATIONAL CHARACTER, INT, INTEGER, SMALLINT,
REAL, FLOAT, DECIMAL(p,d), NUMERIC(precision,decimal), DOUBLE PRECISION,
BIT, BIT VARYING,
DATE, TIME, DATETIME, TIME WITH TIME ZONE, TIMESTAMP WITH TIMEZONE,
INTERVAL
c
Visual SQL °by
β
9
Darstellungstyp: Jeder Wert kann auf unterschiedliche Art dargestellt werden. Kommerzielle Systeme erlauben
meist nur eine Darstellungsform, so dass weitere Darstellungstypen selbst definiert und durch entsprechende Darstellungsrelationen unterlegt werden müssen.
Defaultwerte: Innnerhalb einer Relation können bestimmte Werte für spezielle Zwecke definiert werden. Defaultwerte erlauben z.B. beim Fehlen eines Wertes bei neu eingefügten Tupeln das Hinzufügen der entsprechenden Werte.
Primär- und Sekundärschlüssel: Primärschlüssel sollten für jede Relation ausgezeichnet werden. Ausserdem
können weitere Schlüssel als Sekundärschlüssel ausgezeichnet werden. Wir verwenden dazu das Schlüsselsymbol mit einer Nummer, die für i-ten Sekundärschlüssel die entsprechenden Attribute annotiert.
Bedeutung des Attributes: Da zu einer Definition auch eine Dokumentation gehört, sollte auch jedem Attribut
seine Bedeutung innerhalb der Relation erklärt werden.
Nullwerte: Nullwerte tragen in Relationen eine Reihe von Bedeutung:
• dne: Wert existiert nicht (‘does not exist’) für dieses Objekt. Z.B. ist der Geburtsname von unverheirateten Personen identisch mit dem Familiennamen. Unterlegt man der Angabe des Geburtsnamen, dass eine Person verheiratet sein muss, dann existiert für unverheiratete Personen kein
Geburtname.
• unk : Wert ist unbekannt (‘unknown’) für dieses Objekt. Ein Wert kann temporär unbekannt sein,
noch nicht bekannt sein, z.Z. in der Veränderung sein oder auch einfach noch nicht eingebracht
sein. Alle diese Varianten werden als unbekannte Werte zusammengefasst.
• inc: Ein Wert kann zu einem Zeitpunkt auch inkonsistent (‘inconsistent’) sein.
• many: Einem Objekt kann anstelle eines Wertes eine Menge von möglichen Werten zugeordnet
sein. In diesem Fall müsste mit einer Mengenbeziehung die Kandidatenmenge zugeordnet werden.
• ni : Für ein Objekt ist eine Information (‘no information’) nicht vorhanden, falls der Wert unbekannt
ist oder nicht existiert.
Wertebereichsbeschränkungen: Da mit der Angabe eines Wertebereiches oft nicht alle Einschränkungen, die
für den Typ gelten, erfasst werden können, werden zusätzliche Wertebereichsbeschränkungen wie in Bild
4 durch eine CHECK-Klausel angegeben.
Erzwingungsstrategie: Die Gültigkeit von Integritätsbedingungen muss auch mit Mitteln des Datenbanksystemes sichergestellt werden. Dazu sind mit den Bedingungen auch die Erzwingungsstrategien anzugeben,
falls diese von den vorgegebenen Bedingungen abweichen oder falls eine Vielzahl möglich sind.
Weitere Abhängigkeiten: Alle anderen Abhängigkeiten sollten ebenso in einem Schema angegeben werden.
Oft wird die Gültigkeit einer Integritätsbedingung separat durch entsprechende Pflegemechanismen des
DBMS unterstützt. Ist keine zusammenfassende Übersicht vorhanden, dann entsteht sehr schnell ein
inkonsistentes Schema.
In Visual SQL Schema in Bild 3 wird z.B. für die referentielle Integritätsbedingung
Studiengang [ID_Institut] ⊆ Institut [IName]
die folgende Strategie zur Erzwingung der Integrität angewandt:
• Soll ein Studiengang eingefügt werden, für den kein verantwortliches Institut existiert, dann kann dieser
Studiengang nicht eingefügt werden.
• Soll in der Relation Institut ein Institut gestrichen werden, das für einen Studiengang verantwortlich
ist, dann ist dies nicht erlaubt.
• Wird ein Institutsname verändert, dann wird dieser Update auch kaskadierend im Studiengang wirksam.
10
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Studiengang
◦
ID Studiengang
◦)
StudiengangName (
Betreuer (
2
◦)
◦)
*
char(18)
3
PruefungsamtMitarb
ID Institut (
TIMESTAMP (Default = CURRENT) (iStudiengang =NoAction,
dInstitut = Restrict,
uInstitut = Cascade,
char(18) uStudiengang = NoAction)
varchar(18) ¬0 (Default = ’nicht zugeordnet’)
3
char(10) ¬0
INDEX
Bild 3: Vollständige Datentypen von Attributen mit Attributbedingungen
• Wird für einen Studiengang ein Institutsname geändert, dann wird die Operation zurückgewiesen.
Wir erhalten aus dieser Spezifikation heraus die folgende Darstellung des entsprechenden relationalen Schemas:
CREATE SCHEMA Unibeispiel AUTHORIZATION Sigmund
DEFAULT CHARACTER SET German
(
....
CREATE TABLE Studiengang (
ID_Studiengang
TIMESTAMP
DEFAULT CURRENT_TIMESTAMP,
StudiengangName
char(25)
NOT NULL UNIQUE,
Betreuer
char(18) ,
PruefungsamtMitarb varchar(18) NOT NULL
DEFAULT "nicht zugeordnet",
ID_Institut
char(10)
NOT NULL,
PRIMARY KEY (ID_Stu),
UNIQUE (Institut, Betreuer),
FOREIGN KEY (ID_Institut) REFERENCES Institut
ON DELETE RESTRICT
ON UPDATE CASCADE);
...
CREATE INDEX InstStudiengang ON Studiengang(ID_Institut);
....
)
Die Übersetzungstrategie für die Übersetzung eines ER-Schemas in ein relationales Schema ist in [Thal00]
ausführlich dargestellt. Es sind eine Reihe von Parametern einstellbar:
Übertragung komplexer Attributtypen: Komplexe Attribute können in atomare Attributtypen übertragen werden durch
Ausnutzung von Kardinalitätsbeschränkungen und damit verbunden einem Aufspleissen von Typen wie
z.B. für eine Liste von Vornamen zu Vorname1, Vorname2, RestlicheVornamen,
Abbildung auf eine Konkatenation von Werten, wie dies z.B. oft für Namen praktiziert wird,
Einführung eines separaten Schemas mit entsprechenden Identifikationsmechanismen und einer Verbindung der Originaltabelle mit den Identifikatoren wie z.B. für eine Menge von akademischen Titeln
einer Person, oder durch
Beibehaltung der Attributtypen, falls das DBMS UDT’s (User Defined Types) zulässt.
c
Visual SQL °by
β
11
Behandlung von Hierarchien: Hierarchien können aufgelöst, partiell aufgelöst oder beibehalten werden. Je nach
Strategie mit dem Event-Nonseparation-Zugang, dem Event-Separation-Zugang, dem Union-Zugang
oder dem Universalrelationen-Zugang werden die Schemata erzeugt.
Einführung kontrollierter Redundanz: Für die Verbesserung der Performanz von Datenbanksystemen ist eine
explizite Einführung redundanter Relationen oft eine gut geeignete Strategie.
Unterstützung von Nullwerten: DBMS unterstützen auf unterschiedliche Art und Weise Nullwerte. Werden
Nullwerte nicht in allen geforderten Facetten unterstützt, dann muss auch eine entsprechende Umformung des Schemas vorgenommen werden.
Erzwingung von Integritätsbedingungen: Es gibt eine Reihe von Optionen, die je nach DBMS-Wahl zur Erzwingung der Integritätsbedingungen entweder auf prozeduralem oder deklarativem Niveau ausgewählt
werden können.
Namenkonventionen unterstützen die automatische Generierung von Typnamen, so dass dann der Zusammenhang im Schema konsistent beibehalten werden kann. Dazu zählen nicht nur Präfix- oder Postfixnotationen für die Auszeichnung von Namen für bestimmte Schemakomponenten wie Indizes, Integritätsbedingungen, sondern auch Abkürzungsregeln und Erweiterungsregeln für z.B. Attributnamen mit einer
Fremdschlüsselassoziation.
Schwache Typen werden nicht nur durch eigene Komponenten identifizierbar, sondern insbesondere auch durch
Assoziationen zu identifizierenden Typen. Schwache Typen können durch Erweiterung der Elterntypen
oder durch eine erweiterte Identifikationsbeziehung dargestellt werden.
Relationship-Typen werden als eigenständige Relationen, durch Einbettung in andere Relationen oder durch
Verschmelzung von Relationen relational dargestellt. Besonders für Relationship-Typen gilt, dass eine
generelle und einheitliche Übersetzungsstrategie für ein gesamtes Diagramm nicht existieren muss.
Cluster-Typen werden durch eigenständige Relationen, durch Zusammenführung von relationalen Typen oder
durch eine Einbettung dargestellt, wobei im Falle der Darstellung durch einen eigenständigen Typen
eine Harmonisierung der Primärschlüssel erfolgen muss.
Behandlung von komplexen Primärschlüsseln: Komplexe Primärschlüssel führen zu relativ komplexen Indexierungsmechanismen und sollten deshalb vermieden werden.
Die letzte Option zur Übertragung von ER-Schemata in relationale wollen wir an folgendem Beispiel darstellen.
Meist ist auch die Einführung künstlicher Schlüssel besser geeignet. Ein typisches Beispiel wäre dann das
folgende Schema:
CREATE TABLE Person (
ID_Per char(10) not null,
Geburtsort char(15) not null,
Adresse char(40) not null,
Personenname char(25) not null,
Geburtsdatum date not null,
primary key (ID_Per));
CREATE TABLE Professor (
ID_Pro char(10) not null,
ID_Per char(10) not null,
Spezialisierung char(1) not null,
primary key (ID_Pro),
unique (ID_Per));
CREATE TABLE Student (
ID_Stu char(10) not null,
12
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
ID_Per char(10) not null,
MatrNr char(7) not null,
primary key (ID_Stu),
unique (ID_Per));
CREATE TABLE Betreuer (
ID_Pro char(10) not null,
ID_Stu char(10) not null,
von date not null,
bis date,
Thema varchar(30) not null,
primary key (ID_Pro, ID_Stu));
ALTER TABLE Student ADD CONSTRAINT FKPer_Stu
FOREIGN KEY (ID_Per) REFERENCES Person;
ALTER TABLE Professor ADD CONSTRAINT FKPer_Pro
FOREIGN KEY (ID_Per) REFERENCES Person;
ALTER TABLE Betreuer ADD CONSTRAINT FKBet_Stu
FOREIGN KEY (ID_Stu) REFERENCES Student;
ALTER TABLE Betreuer ADD CONSTRAINT FKBet_Pro
FOREIGN KEY (ID_Pro) REFERENCES Professor;
CREATE UNIQUE INDEX StudID ON Student (ID_Stu);
CREATE UNIQUE INDEX FKPer_Stu ON Student (ID_Per);
CREATE UNIQUE INDEX ID ON Professor (ID_Pro);
CREATE UNIQUE INDEX FKPer_Pro ON Professor (ID_Per);
CREATE UNIQUE INDEX ID ON Person (ID_Per);
CREATE UNIQUE INDEX IDBetreuer ON Betreuer (ID_Pro, ID_Stu);
CREATE UNIQUE INDEX FKBet_Stu ON Betreuer (ID_Stu);
CREATE UNIQUE INDEX FKBet_Pro ON Betreuer (ID_Pro);
ALTER TABLE Student ADD CONSTRAINT
CHECK(EXISTS(SELECT * FROM EingeschriebenIn
WHERE EingeschriebenIn.E_S_ID_Stu = ID_Stu));
ALTER TABLE Professor ADD CONSTRAINT
CHECK(EXISTS(SELECT * FROM In
WHERE In.ID_Pro = ID_Pro));
Die gleiche Lösung kann auch durch eine Schema-Darstellung, die Integritätsbedingungen aus den relationalen Schemata herauslöst, erreicht werden. In diesem Fall wird dann eine relationale Darstellung wie folgt
vorgeschlagen:
CREATE TABLE Person (
ID_Per char(10) NOT NULL,
Geburtsort char(15) NOT NULL,
Adresse char(40) NOT NULL,
Personenname char(25) NOT NULL,
Geburtsdatum date NOT NULL,
PRIMARY KEY (ID_Per));
CREATE UNIQUE INDEX
ID ON Person (ID_Per);
CREATE TABLE Student (
ID_Stu char(10) NOT NULL,
ID_Per char(10) NOT NULL,
c
Visual SQL °by
β
13
MatrNr char(7) NOT NULL,
PRIMARY KEY (ID_Stu),
UNIQUE (ID_Per));
ALTER TABLE Student ADD CONSTRAINT FKPer_Stu
FOREIGN KEY (ID_Per) REFERENCES Person;
CREATE UNIQUE INDEX
CREATE UNIQUE INDEX
StudID ON Student (ID_Stu);
FKPer_Stu ON Student (ID_Per);
CREATE TABLE Studiengang (
ID_Stu char(10) NOT NULL,
SName char(25) NOT NULL,
Betreuer char(18) NOT NULL,
PruefungsamtMitarb varchar(18) NOT NULL,
ID_Ins char(10) NOT NULL,
PRIMARY KEY (ID_Stu));
ALTER TABLE Studiengang ADD CONSTRAINT FKverantwortlichFuer
FOREIGN KEY (ID_Ins) REFERENCES Institut;
CREATE TABLE EingeschriebenIn (
E_S_ID_Stu char(10) NOT NULL,
ID_Stu char(10) NOT NULL,
von date NOT NULL,
bis date NOT NULL,
PRIMARY KEY (ID_Stu, E_S_ID_Stu));
ALTER TABLE Student ADD CONSTRAINT
CHECK(EXISTS(SELECT * FROM EingeschriebenIn
WHERE EingeschriebenIn.E_S_ID_Stu = ID_Stu));
Die Beziehungen für die andere Personengruppe kann man in analoger Form darstellen. Wir verwenden
für das folgende Schema eine andere Namenkonvention und auch eine analoge Strategie für die Integritätsbedingungen.
CREATE TABLE Person (
ID_Per char(10) NOT NULL,
Geburtsort char(15) NOT NULL,
Adresse char(40) NOT NULL,
Personenname char(25) NOT NULL,
Geburtsdatum date NOT NULL,
PRIMARY KEY (ID_Per));
CREATE TABLE Professor (
ID_Pro char(10) NOT NULL,
ID_Per char(10) NOT NULL,
Spezialisierung char(1) NOT NULL,
PRIMARY KEY (ID_Pro),
UNIQUE (ID_Per));
ALTER TABLE Professor ADD CONSTRAINT FKPer_Pro
14
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
FOREIGN KEY (ID_Per) REFERENCES Person;
CREATE UNIQUE INDEX
CREATE UNIQUE INDEX
ID ON Professor (ID_Pro);
FKPer_Pro ON Professor (ID_Per);
CREATE TABLE In (
ID_Pro char(10) NOT NULL,
Seit char(4) NOT NULL,
ID_Ins char(10) NOT NULL,
PRIMARY KEY (ID_Pro));
ALTER TABLE Professor ADD CONSTRAINT
CHECK(EXISTS(SELECT * FROM In
WHERE In.ID_Pro = ID_Pro));
CREATE TABLE Institut (
ID_Ins char(10) NOT NULL,
RaumSekret char(8) NOT NULL,
Kostenstelle char(12),
Telefon numeric(4) NOT NULL,
IName char(1) NOT NULL,
Sprecher char(15) NOT NULL,
Fakultt char(1) NOT NULL,
PRIMARY KEY (ID_Ins));
Integritätsbedingungen sind meist ein relativ schwer zu verstehen. Lokale Bedingungen haben oft Auswirkungen auf das gesamte Schema. Der Zusammenhang zwischen Integritätsbedingungen ist oft komplexer
als es vom Entwerfer verstanden wird. Eine Visualisierung kann die Verständnisschwierigkeiten mildern. Wie
in Bild 4 dargestellt, kann die Kardinalitätbedingung, die konstatiert, dass ein Student mindestens in einem
Studiengang eingeschrieben sein muss, aber höchstens in zwei Studiengängen eingeschrieben sein kann, durch
eine Visualisierung relativ einfach mit der Erzwingungsstrategie verknüpft werden.
Student
◦
◦)
MatrNr
µ
(1,.): (iStud =C,dEing =R,uStud =R,uEing =R)
Name (
(.,2): (iEing =R,uStud =C,uEing =R)
Gebdatum (
char[7]
char[40]
◦)
EingeschriebenIn
◦
Stud.MatrNr
SName
◦
char[7]
char[20]
Von
date
Bis
date
check ( Bis
>
Bild 4: Constraint-Definition in Visual SQL
Von )
date
c
Visual SQL °by
β
15
Die Erzwingungsstrategie wie in Bild 4 dargestellt, kann auch durch SQL direkt deklarativ dargestellt
werden. Diese deklarative Umsetzung ist allerdings nur in solchen Systemen möglich, die auch CHECKKlauseln mit Teilanfragen zulassen.
CREATE TABLE EingeschriebenIn (
StudMatrNr
char[7]
FOREIGN KEY REFERENCES Student(MatrNr),
...
Bis
date
CHECK ( Von < Bis )
PRIMARY KEY (SName, StudMatrNr)
);
ALTER TABLE Student ADD CONSTRAINT
CHECK(EXISTS(SELECT * FROM EingeschriebenIn
WHERE EingeschriebenIn.StudMatrNr = MatrNr));
ALTER TABLE EingeschriebenIn ADD CONSTRAINT
CHECK(NOT EXISTS(
SELECT * FROM EingeschriebenIn E1,
WHERE EXISTS(
SELECT * FROM EingeschriebenIn E2
WHERE E1.StudMatrNr = E2.StudMatrNr
AND E1.SName <> E2.SName
AND EXISTS(
SELECT * FROM EingeschriebenIn E3
WHERE E3.StudMatrNr = E2.StudMatrNr
AND E1.SName <> E3.SName
AND E2.SName <> E3.SName))));
In analoger Form können auch partielle Fremdschlüsselbeziehungen dargestellt werden. In Bild 5 werden
diese Beziehungen dargestellt. Es kann zusätzlich die Erzwingungsstrategie noch visualisiert werden. Der
Entity-Typ kann natürlich auch aufgrund der funktionalen Abhängigkeiten dekomponiert werden.
Institut
-
RaumSekretariat
Telefon
6
(Partial) Foreign Key
-
Sprecher
Professor
6
?
-
IName
Foreign Key
Fakultaet
-
Struktureinheit
6
?
?
Kostenstelle
Postkasten
Bild 5: Constraint-Definition in Visual SQL: Funktionale Abhängigkeiten
Die Abhängigkeiten werden in relationalen Schemata wie folgt dargestellt:
16
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
CREATE TABLE Institut (
RaumSekret
char(8)
not null,
Kostenstelle
char(12) not null,
Telefon
numeric(4) not null,
IName
char(15) PRIMARY KEY not null,
Sprecher
char(15) not null,
Fakultaet
char(1) not null,
Anschrift
char(20) );
ALTER TABLE Institut ADD CONSTRAINT FakultFK
FOREIGN KEY (Fakultaet) REFERENCES Fakultaet(FName);
ALTER TABLE Institut ADD CONSTRAINT SprecherPartFK
CHECK(EXISTS(SELECT * FROM Professor
WHERE Professor.Name = Sprecher
AND Professor.InIName = IName));
2.1.2
Zuordnung und Streichen von Rechten
Eine Datenbank wird von seinem Besitzer angelegt. Dieser kann Rechte zur Modifikation der Daten, zur
Anfrage und auch für Transaktionen weitergeben. Wenn man Rechte besitzt und auch das Recht, Rechte
weiterzugeben, dann kann man diese Rechte auch anderen Benutzern zuordnen. Operationen werden in Visual
SQL durch eine explizite Angabe angetragen. In Bild 6 wird eine Operation an der Sicht Raum Planung an
den Benutzer Schenk weitergegeben. Rechte können auch entzogen werden. In Visual SQL wird die explizite
Vergabe von Rechten dargestellt. Der Entzug von Rechten kann entweder explizit durch eine ausgestrichene
Operation dargestellt werden oder durch eine Darstellung von vorhandenen Rechten mit der Annahme, dass
diese Liste vollständig ist.
grant insert, update to
RaumPlanung
√1
KursNr
◦
Dozent
◦
√2
√3
Schenk
with grant option
Raum
√4
Zeit
Bild 6: Die Zuordnung von Rechten
Die Weitergabe von Rechten kann auch mit SQL in analoger Form dargestellt werden.
GRANT
...
REVOKE
INSERT, UPDATE
ON
RaumPlanung
TO
UPDATE
ON
RaumPlanung
FROM
Schenk WITH GRANT OPTION;
Schenk;
In analoger Form kann auch eine Folge von Rechtevergaben und Rechteentzug dargestellt werden
GRANT
INSERT, UPDATE
ON
RaumPlanung
TO
Schenk WITH GRANT OPTION;
GRANT
GRANT
GRANT
SELECT
DELETE
ALL
ON
ON
ON
RaumPlanung
RaumPlanung
RaumPlanung
TO
TO
TO
PUBLIC;
Gutheil;
Pawell WITH GRANT OPTION;
c
Visual SQL °by
β
17
GRANT
ALL
ON
RaumPlanung (Raum)
TO
Grahne
REVOKE
UPDATE
ON
RaumPlanung
FROM
REVOKE
ALL
ON
RaumPlanung
FROM PUBLIC;
;
Schenk;
Die Rechtevergabe lässt sich relativ gut graphisch darstellen. Es kann dem Schema direkt ein Rechtegraph
zugeordnet werden, der die explizite Freigabe von Rechten visualisiert.
2.1.3
Modifikation von Datenbanken mit Visual SQL
Datenbanksysteme verfügen über drei atomare Modifikationsoperationen: Insert, Delete und Update. Diese
Operationen werden in Visual SQL direkt durch eine Angabe der Datenmengen, mit denen die Relation
modifiziert wird, dargestellt.
Modifikationsoperationen müssen typkompatibel sein. Im Fall einer Insert-Anweisung müssen die neuen
Werte zu der Deklaration der Tabelle passen.
Im Falle der Insert-Operation kann ein direktes Insert oder ein Insert einer Menge, die aus einer Anfrage
gewonnen wird, in der folgenden Art abgebildet werden:
INSERT
Student
MatrNr
Name
Geburtsdatum
VALUES
VALUES
007
0815
“Alfons ”
“Amanda ”
29.2.1982
24.12.1982
Bild 7: Insert von Werten in Tabellen
Dieser Operation entspricht die SQL-Anweisung:
INSERT INTO Student
VALUES (007,"Alfons",29.2.1982),
(0815,"Amanda",24.12.1982);
Eine Insert-Anweisung wird wirksam, wenn nicht Integritätsbedingungen eine Zurückweisung erforderlich
machen.
Insert-Anweisungen können auch durch Anfrageergebnisse gestützt werden. Wie in Bild 8 kann dann eine
Anfrage direkt zum Insert führen.
Diese Insert-Anweisung lässt sich in SQL auch wie folgt darstellen:
INSERT INTO Student (MatrNr,Name,Geburtsdatum)
SELECT A,B,C FROM .... WHERE ... ;
Eine Update-Operation ist spezifiziert durch eine Qualifikationsbedingung und durch eine Angabe der
neuen Werte, die allen Objekten, die sich durch die Bedingung qualifiziert haben, zugewiesen werden. Ein
18
B. Thalheim,
Student
Preprint BTU - Informatik - I-08-2003
INSERT
MatrNr
Name
Geburtsdatum
SELECT A,B,C FROM ... WHERE ...
Bild 8: Insert von (argument-kompatiblen) Anfragergebnissen
komplexe Bedingung-Wert-Verknüpfung ist in SQL für eine einzelne Update-Anweisung nicht vorgesehen. Sie
muss durch eine Folge von Anweisungen explizit dargestellt werden. In Bild 9 wird einem Studenten mit einer
bestimmten Matrikelnummer eine neue Matrikelnummer zugewiesen.
MatrNr = 2012003
Student
UPDATE
MatrNr
MatrNr =
0815
Name
Geburtsdatum
Bild 9: Modifikation von Werten in Tabellen
Die in Bild 9 dargestellte Update-Anweisung kann in SQL direkt auf die folgende Art angegeben werden:
UPDATE Student
SET MatrNr = 2012003
WHERE MatrNr = 0815;
Die Qualifikationsbedingungen können auch komplexer sein, wie in Bild 10 dargestellt. DBMS realisieren
Update-Anweisungen oft durch eine Folge von Delete-Insert-Anweisungen, die zurückgewiesen werden, wenn
die Folge der Operationen zu einer Zerstörung der Konsistenz führt.
Die Update-Anweisung in Bild 10 lässt sich durch eine SQL-Anweisung wie folgt darstellen:
UPDATE Person
SET Geburtsort = "Muenchen",
Adresse = "03055 Cottbus-Sielow"
WHERE Name = "Gerhard Lappus"
AND Gebdatum = 4.12.1953;
c
Visual SQL °by
β
19
UPDATE
Person
Name
Gebdatum
Geburtsort =
“München”
Geburtsort
Adresse = “0355
Cottbus-Sielow”
Adresse
“Gerhard”
“Lappus”
Name =
Gebdatum =
4.12.51
Bild 10: Modifikation von mehreren Werten in Tabellen
Update-Anweisungen können auch die entsprechenden Werte aus Anfragen erhalten. So kann z.B. die
Einschreibung des Studenten, der als Person durch seinen Namen und sein Geburtsdatum identifiziert ist, in
einem Studiengang durch eine Anweisung wie in Bild 11 dargestellt werden.
EingeschriebenIn
UPDATE
Stud.MatrNr
SName
Von =
7.10.2002
Von
NULL
Bis
=
Student
√
MatrNr
Name
=
“Hans Meyer”
Geburtsdatum
=
13.12.1982
Bild 11: Modifikation von Werten mit einer Anfrage
Die in Bild 11 dargestellte Modifikationsoperation kann direkt durch eine Anfrage in SQL dargestellt
werden. Damit werden alle Studenteneinschreibungsdaten modifiziert, falls diese mit der Bedingung übereinstimmen. In unserem Fall wird die Einschreibung von “ Hans Meyer”, der am “ 13.1.21982” geboren wurde
geändert, unabhängig davon ,ob dieser Student in einem oder mehreren Studiengängen eingeschrieben ist.
UPDATE Student
SET Von = 7.10.2002,
Bis = Null
WHERE MatrNr =
( SELECT MatrNr
FROM Student
WHERE Name = "Hans Meyer"
AND GebDatum = 13.12.1982);
Durch die Delete-Anweisung werden Objekte gestrichen, wenn das Resultat der Delete-Anweisung nicht zu
einem Zustand der Datenbank führt, in dem die Integritätsbedingungen nicht gelten. So werden mit der folgenden Anweisung in Bild 12 alle Einschreibungen von Studenten in Studiengänge gestrichen, die ihr Studium
vor dem “7. 10 1996” aufgenommen haben und noch nicht beendet haben.
Die entsprechende SQL-Spezifikation ist dann die folgende:
20
B. Thalheim,
EingeschriebenIn
Preprint BTU - Informatik - I-08-2003
DELETE
Stud.MatrNr
SName
Von
<
7.10.1996
Bis
IS
NULL
Bild 12: Streichen von Objekten mit Bedingung
DELETE FROM Eingeschrieben
WHERE Von < 7.10.1996 AND Bis = Null;
Vorsicht, mit diesem DELETE werden auch alle entsprechenden Studenten aufgrund der vorn spezifizierten
Integritätsbedingungen gestrichen!
In analoger Form können auch Anfragen zum Streichen von Objekten mit heran gezogen werden. In
der Anweisung in Bild 13 werden alle Studenten gestrichen, die in noch keinem Studiengang eingeschrieben
sind. Eigentlich sollten aufgrund der Integritätsbedingungen, die wir vorn spezifiziert haben, keine StudentenObjekte auf diese Bedingungen passen.
DELETE
Student
MatrNr
}
Name
Geburtsdatum
EingeschriebenIn
NOT EXISTS
√
Stud.MatrNr
SName
Von
Bis
Bild 13: Streichen von Objekten mit einer Anfrage
Die Anweisung in Bild 13 wird in SQL wie folgt dargestellt:
DELETE FROM Student S
WHERE NOT EXISTS
(SELECT StudMatrNr
c
Visual SQL °by
β
21
FROM EingeschriebenIn E
WHERE E.StudMatrNr = S.MatrNr) ;
Die folgende Anweisung erlaubt ein Streichen von Einschreibungen von Studenten, die vor einem Zeitpunkt
oder nach dem 7. 10. 2002 erfolgten.
EingeschriebenIn
DELETE
Stud.MatrNr
SName
Von
> 7.10.2002
OR < 1.1.1993
Bis
Bild 14: Streichen von Objekten mit Bedingung
Die Visual SQL- Anweisung in Bild 14 wird durch folgende SQL-Anweisung spezifiziert.
DELETE FROM Eingeschrieben
WHERE Von > 7.10.2002
OR Von < 1.1.1993;
Die bislang dargestellten SQL-Anweisungen zeigen aufgrund ihrer Einfachheit, dass eine Visualisierung
ggf. in einem erhöhten Aufwand mündet, der einen Einsatz von Visual SQL nicht rechtfertigt. Dieser Zusatzaufwand wird jedoch für komplexere Anfragen gerechtfertigt.
2.2
2.2.1
Anfragen an DBS
Allgemeines Herangehen an das Formulieren von Anfragen
Anfragen werden in einer Anfragesprache Q = LS1 ,S2 formuliert, die über Schemata Si definiert ist.
Das Schema S1 wird Input-Schema genannt, das Schema S2 Output-Schema.
Gegeben seien weiterhin die Menge InstSi aller Datenbanken über Si .
Wir können die Berechnung von Anfragen als Relation auffassen:
RSQ1 ,S2 = { (q, D1 , D2 ) | q ∈ Q ∧ D1 ∈ InstS1 ∧ D2 ∈ InstS2 }.
Eine Funktion f : Q × InstS1 → P(InstS2 ) realisiert eine Relation RSQ1 ,S2 falls für jedes q ∈ Q und
jede Datenbank D1 ∈ InstS1 gilt, dass
(1) falls kein y ∈ RSQ1 ,DS2 existiert mit (x, D1 , y) ∈ RSQ1 ,S2 dann f (x) = λ und
(2) f (x) = y für (x, D1 , y) ∈ RSQ1 ,S2 mit y 6= λ.
Eine Realisierung muss demzufolge nicht die Menge aller möglichen Resultate berechnen, sondern mindestens
eines, falls es existiert.
Eine Anfrage q ist in einer Anfragesprache Q über einem Schema S formuliert und liefert für eine Datenbank
D über S ein oder mehrere Resultate.
Auf der Grundlage de obigen Beobachtungen kann eine Anfrageanforderung als eine spezifische Suchrelation definiert werden:
RΠ = { (x, s) | x ∈ DΠ ∧ s ∈ SΠ (x) }.
Eine Funktion f : Σ∗ → Σ∗ realisiert eine Relation R falls für jedes x ∈ Σ∗ gilt, dass
(1) falls kein y ∈ Σ+ existiert mit (x, y) ∈ R dann f (x) = λ und
22
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
(2) f (x) = y für (x, y) ∈ R mit y 6= λ.
Eine Anfrageanforderung besteht intentional aus zwei Komponenten:
Suchkonzept: Ein Suchkonzept beschreibt die Suchanforderung. Wir haben dazu Anfrageformen eingeführt.
Resultatkonzept: Ein Resultatkonzept beschreibt die Einbettung von Anfrageresultaten in ein Medienobjekt,
das neben einem Tupelraum zur Aufnahme der Daten auch über eine eigenständige Repräsentation
verfügt, sowie Strukturierungs- und Repräsentationsfunktionen.
Die Forschung zur Datenbankanfrage-Unterstützung hat sogar eine allgemeinere Formulierung für Anfragen hervorgebracht, wie in Bild 15 illustriert.
Datenbank
schema
Suchanforderung
Such: konzept
- Anfrageform
?
- SQL- ¾
anfrage
?
zResultatkonzept
-Antwortform
?
SQL-Antwortmenge
? )
Antwort
auf Suche
DBMS-Anfrageinterface
q
DBMS-Antwortrepräsentation
Bild 15: Konzeptbasiertes Berechnen von Anfragen im klassischen Zugang und mit Anfrage- und Antwortformen
Der Zugang über Anfrage- und Antwortformen wird durch folgende Abbildungsvorschriften unterstützt:
map
compile
:
:
process
:
7→
query form
(query form , database schema)
map
output
search concept
:
result concept
:
SQL query
7→
7→
7→
SQL query
answer form
SQL answer set
(SQL answer set , answer form)
7→
answer to search
Ziele einer Anfrageformulierung sind demzufolge:
Eingrenzung des Nichtdeterminismus: Jede Anfrage sollte so eindeutig wie nur möglich gestellt werden.
Berechenbarkeit: Jede Anfrage sollte in eine Anfragevorschrift transformierbar sein, die ein Computer berechnen kann.
Effektive Berechenbarkeit: Anfragen sollten auch in hinreichend kurzer Zeit bemessen am zu bewältigenden
Datenumfang berechnet werden.
Abbildung auf die Anfragesprache: Die Anfragen sollten in der zur Verfügung stehenden Anfragesprache formulierbar sein.
Demzufolge ist die Formulierung einer Anfrage stets ein Vierschrittverfahren:
1. Ergänzung der Anfrageäusserung zu einer genau formulierten Anfrage durch
• Disambiguierung von Fragesätzen,
c
Visual SQL °by
β
23
• Ergänzung der Ellipsen zu vollständigen Sätzen,
• Klärung, inwieweit eine Closed-World-Assumption oder eine partiell offene Datenwelt in der Datenbank, unterlegt wird (Behandlung von Nullwerten), und
• Schärfung der Formulierung von Aggregationsfunktionen;
2. Reformulierung der Anfrage in eine existentiell geprägte Form wobei
• nicht alle Generalisierungen aufgelöst werden müssen, sondern über ALL und ANY abgebildet
werden können, und
• ggf. auch besser überschaubare Boolesche Bedingungen erzeugt werden, indem z.B. die Negation
möglichst weit zu den atomaren Formeln gezogen wird.
3. Abbildung der Anfragebegriffe auf das Datenbank-Schema wobei ggf.
• Spezifika der Schema-Definition mit beachtet werden, wie z.B.
• Nullwerte und Default-Werte, die eine Anfrageberechnung verändern können, und
• referentielle Inklusionsabhängigkeiten, die zur Verkürzung der Anfragepfade mit herangezogen
werden können,
• auch Hilfstabellen temporär für die Anfrage gebildet werden oder Sichten als Tabellen für eine
‘Nebenrechnung’ bereitgestellt werden, sowie
• eine Schrittfolge zur Berechnung der Resultate durch eine Prozedur bereitgestellt wird.
4. Abbildung der Resultatskonzepte auf Antwortformen und Repräsentation dieser in SQL-Form.
2.2.2
Einfache Anfragen ohne Verknüpfung mehrerer Relationen
Ehe wir zu schwierigen Anfragen kommen, wollen wir die grundlegenden Begriffe lernen. Dazu werden wir die
Formulierung für Ausdrücke der relationalen Algebra an einfachen Beispielen zuerst üben.
SQL stellt die Projektion und Selektion als einfachste Operationen bereit:
SELECT
Name, Geburtsdatum
FROM Student S;
Die Auswahl aller Attribute kann mit dem ∗-Zeichen dargestellt werden.
SELECT
*
FROM Student S;
Diese Konstrukte sind in Visual SQL ebenso darstellbar, wobei wir uns an der Entity-Darstellungsform von
√
HERM orientieren. In Bild 16 wird eine einfache Projektion vorgestellt. Mit dem -Zeichen werden die
Ausgabekomponenten gekennzeichnet.
Student
MatrNr
√
√
Name
Geburtsdatum
Bild 16: Projektion in Klassen
Eine etwas komplexere Auswahl wird in Bild 17 vorgestellt. Sie entspricht der SQL-Anweisung:
24
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Student
√
MatrNr
Name
√
Geburtsdatum
=
Alfons Müller
>
13.12.1982
Bild 17: Auswahl in Klassen
SELECT
MatrNr, Geburtsdatum
FROM Student S
WHERE Name = "Alfons Mueller"
AND Gebdatum > 13.12.1982;
Student
√
MatrNr
Name
√
=
Alfons Müller
>
13.12.1982
AND
Geburtsdatum
Bild 18: Auswahl in Klassen mit vollem AND
Bedingungen in der WHERE-Klausel sind meist Boolesch verknüpft. Ein Boolescher Ausdruck kann beliebig
komplex sein. Oft muss eine explizite Klammerschreibweise verwendet werden. Diese lässt sich relativ schwer
graphisch darstellen. Diese Einschränkung ist jedoch relativ einfach zu umgehen, wenn man den Junktoren
ein Niveau im Ausdruck zuordnet und dieses Niveau explizit durchnummeriert.
Diese Herangehensweise entspricht auch der deutschen Nebensatzkonstruktion. Wir können diese Notation
an folgenden Beispielen verdeutlichen:
Klammerschreibweise
Nummernschreibweise
Präfixnotation
Postfixnotation
A ∧ B
A ∧1 B
∧AB
AB∧
A ∨ B
A ∨1 B
∨AB
AB∨
¬A
¬1 A
¬A
A¬
A ∨ (B ∨ (C ∧ D))
A ∨1 B ∨1 C ∧2 D
∨A ∨ B ∧ C D
AB ∨ C D ∧ ∨
A ∨ B ∨ (C ∧ D)
A ∨1 B ∨1 C ∧2 D
∨A ∨ B ∧ C D
AB ∨ C D ∧ ∨
(A ∧ B) ∨ C
A ∧2 B ∨1 C
∧ ∨ AB C
AB ∧ C ∨
A ∧ B ∨ C
A ∧2 B ∨1 C
∧ ∨ AB C
AB ∧ C ∨
(¬(A ∧ B)) ∨ C
¬2 A ∧3 B ∨1 C
∨¬ ∧ AB C
A B ∧ ¬C ∨
¬((A ∧ B) ∨ C)
¬1 A ∧3 B ∨2 C
¬ ∨ ∧AB C
AB ∧ C ∨ ¬
(¬A ∧ B) ∨ C
¬3 A ∧2 B ∨1 C
∨ ∧ ¬A B C
A¬B ∧ C ∨
¬A ∧ B ∨ C
¬3 A ∧2 B ∨1 C
∨ ∧ ¬A B C
A¬B ∧ C ∨
Die Nummerierung kann weggelassen werden, wenn sie offensichtlich ist. Die Klammersparregeln können in
beiden Fällen angewandt werden, wobei durch die explizite Nummerierung sogar weitergehende Klammernsparregeln anwendbar werden, wie das letzte Beispiel in den letzten beiden Zeilen der Tabelle zeigt. Diese
beiden Zeilen stellen äquivalente Ausdrücke dar. Auch die beiden Ausdrücke in Zeile 4 und 5 der Tabelle sind
c
Visual SQL °by
β
25
äquivalent, wobei die Klammernschreibweise in Zeile 5 nur eine Klammersparregel für Disjunktionen auf gleicher Ebene einsetzt. Die Klammernschreibweise wird auch als Infixnotation bezeichnet und lässt sich einfach
in entsprechende Bäume transformieren.
Gesucht sind Telefon und Raum des Sekretariates eines Institutes, das sich im LG 2 befindet oder Informatik
im Namen hat oder zur Fakultät 1 gehört, wobei dann der Sprecher mit “K” beginnt.
Die Bedingung in dieser Anfrage entspricht der Bedingung in der vierten bzw. fünften Zeile der obigen
Tabelle.
Institut
IName
√
Telefon
LIKE
“% nformatik%”
LIKE
“%LG 2%”
OR1
Anschrift
Fakultät
AND2
Sprecher
=
LIKE
1
“K%”
Postkasten
Kostenstelle
√
RaumSekretariat
Bild 19: Auswahl in Klassen mit AND und OR
SELECT
FROM
WHERE
Telefon, RaumSekretariat
Institut
Anschrift LIKE "%LG 2%"
OR
(Fakultaet = 1
AND
OR
IName LIKE ‘‘%_nformatik\%’’
Sprecher LIKE "K%");
Konjunktive Normalformen sind ausreichend. Sie sind jedoch nicht immer einfach darzustellen. Deshalb lassen
wir alle Booleschen Ausdrücke zu, konzentrieren uns allerdings auf konjunktive Normalformen der Form
i
∧ni=1 ∨m
j=1 αi,j
mit Elementarausdrücken αi,j , die Vergleiche mit den Prädikaten ≤, ≥, <, >, =, 6= über Attributen bzw. Komponenten bzw. Werten sind.
Gesucht sind Telefon und Raum des Sekretariates eines Institutes, das sowohl “Informatik” heisst oder
nicht zu den Fakultäten 2,3 bzw. 4 gehört und zugleich im LG2 das Sekretariat hat oder eine Kostenstelle
ausserhalb des 2-er und 3-er Bereiches hat.
SELECT
Telefon, RaumSekretariat
FROM
Institut
WHERE
(IName LIKE "%_nformatik%" OR
Fakultaet NOT IN (2,3,4))
AND (RaumSekretariat LIKE "LG 2%"
OR
Kostenstelle NOT BETWEEN 2000000 AND 3999999);
Gesucht sind absteigend geordnet Namen und Sprecher von Instituten, die einen Sprecher haben.
SELECT
IName, Sprecher
FROM
Institut
WHERE Sprecher IS NOT NULL
ORDER BY Sprecher DESC;
26
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Institut
IName
√
Telefon
LIKE
OR
Fakultät
NOT
“% nformatik%”
IN
2...4
Anschrift
Sprecher
Postkasten
Kostenstelle
√
RaumSekretariat
NOT
OR
2000000
BETWEEN AND 3999999
LIKE
“LG 2%”
Bild 20: Auswahl in Klassen mit AND, OR und NOT
Institut
√
IName
√
Sprecher
ORDER BY Sprecher desc
6=
NULL
...
RaumSekretariat
Bild 21: Auswahl mit NOT NULL und Ordnung
Betreuer
√
3
√
MatrNr
BetrName
1
ORDER BY BetrName, MatrNr
≈
“Vierhaus”
≈
“Datenbank”
...
√
2
Thema
Bild 22: Auswahl mit NOT NULL und Ordnung
c
Visual SQL °by
β
27
Gesucht sind die Betreuungsbeziehungen von “Prof. Vierhaus” zum Thema “Datenbanken”. Die Lösung
wird in Bild 22 und der folgenden SQL-Anweisung dargestellt.
SELECT
BetrName, Thema, MatrNr
FROM
Betreuer
WHERE BetrName LIKE "%Vierhaus%"
ORDER BY BetrName, MatrNr;
AND
Thema LIKE
"%Datenbank%"
Als Beispiel für aggregierte Daten betrachten wir die folgende Aufgabe: Es sollen alle Hiwis mit ihrem
Weihnachtsgeld und ihrem Salär ausgegeben werden.
Die Lösung in Bild 23 verwendet ein abgeleitetes Attribut, das durch eine gestrichelte Box dargestellt
wird.
Hiwi
√
MatrNr
..
√
√
Betrag
Weihnachtsgeld = Betrag * .15
Bild 23: Ausgabe mit arithmetischer Operation
SELECT
FROM
MatrNr, Betrag * .15 AS Weihnachtsgeld, Betrag
Hiwi ;
Es können mehr als ein abgeleitetes Attribut eingeführt werden.
Es soll in einer Zeile ohne Spaltennamen die maximalen Salärs, die Summe dieser, der Durchschnitt dieser
und die Anzahl dieser für alle “DaMiT”-Hiwis ausgegeben werden.
Diese Anfrage wird in Bild 24 visualisiert.
SELECT
FROM
WHERE
MAX(Betrag), SUM(Betrag), AVG(Betrag), COUNT(*)
Hiwi
ProjektName = "DaMiT";
Aggregationsoperationen können auch auf Klassen angewandt werden.
Wieviele verschiedene Hiwis werden im “DaMiT”-Projekt beschäftigt?
Diese Anfrage wird in Bild 24 angedeutet.
SELECT
FROM
WHERE
COUNT(DISTINCT MatrNr)
Hiwi
ProjektName = "DaMiT";
In einfacher Form lässt sich diese Herangehensweise auf Aggregationsoperationen mit Gruppierung erweitern:
Im “Kurshandelssystem” sollen für das Wintersemester 2002/2003 für alle Kurse und Dozenten die Teilnehmerzahl, die Durchschnittsnote und der erwirtschaftete Betrag berechnet werden.
Diese Anforderung wird in folgender SQL-Anweisung dargestellt und kann wie in Bild 26 in Visual SQL
dargestellt werden.
28
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Hiwi
MatrNr
..
ProjektName
=
“DaMiT”
Betrag
√
MAX(Betrag)
√
√
SUM(Betrag)
AVG(Betrag)
√
COUNT(∗)
Bild 24: Ausgabe mit arithmetischen Operationen und Auswahl
Hiwi
MatrNr
..
ProjektName
=
“DaMiT”
Betrag
√
COUNT(DISTINCT MatrNr)
Bild 25: Ausgabe mit Zählung verschiedener Tupel in einer Auswahl
c
Visual SQL °by
β
29
Belegt
MatrNr
√
KursNr
Semester
√
Dozent
group by
◦
◦
◦
◦
=
KursNr, Dozent
¨ WS2002/03 ¨
...
Note
√
√
√
AnzTeiln = COUNT(*)
DurchschnNote = AVG(Note)
SemKursErtrag = COUNT(*) * 30
Bild 26: Ausgabe mit Zählung verschiedener Tupel in einer Auswahl mit weiterer Ausgabe
SELECT
KursNr, Dozent, COUNT(*) AS AnzTeiln, AVG(Note) AS DurchschnNote,
COUNT(*) * 30 AS SemKursErtrag
FROM
Belegt
WHERE
Semester = "WS2002/03"
GROUP BY KursNr,Dozent;
Wir können auch in Visual SQL Gruppierung und Ordnung miteinander verbinden. Wir gehen dazu von
einer Anordnung aus, bei der eine Gruppierung stärker bindet als eine Ordnung. Ist eine andere Ordnung
erforderlich, dann wird dies über Teilausdrücke analog zur üblichen Klammerschreibweise realisiert, bei der
eine “Klammer” um den inneren Ausdruck als Box gesetzt wird. Als Beispiel betrachten wir die Anfrage:
Gesucht sind alle Kurse im Wintersemester 2002/2003 und Dozenten mit einer Mindest-Durchschnittsnote
“gut”, sowie die Anzahl der Teilnehmer.
Diese Anfrage wird in Bild 27 und der folgenden SQL-Anweisung dargestellt. Wir nutzen dazu eine
Auflösung der Ellipse für das Prädikat “gut”.
SELECT
KursNr, Dozent, COUNT(*) AS AnzTeiln
FROM
Belegt
WHERE
Semester = "WS2002/03"
GROUP BY KursNr,Dozent
HAVING
AVG(Note) < 2.50
ORDER BY Dozent;
Die Klammerung wird in Bild 28 am Beispiel des kartesischen Produktes angeführt, mit dem folgender
Ausdruck berechnet wird:
Gesucht ist eine “Gesamt”übersicht der Kurse und der Personen.
Dieses kartesische Produkt wird in Bild 28 vorgestellt.
SELECT
FROM
KursNr, Semester, Name, Gebdatum
Belegt, Person;
30
B. Thalheim,
order by
group by KursNr, Dozent
Belegt
Dozent
MatrNr
√
KursNr
Semester
√
Dozent
Preprint BTU - Informatik - I-08-2003
◦
◦
◦
◦
=
¨ WS2002/03 ¨
...
Note
√
AnzTeiln = COUNT(*)
AVG(Note)
having AVG(Note) < 2.50
Bild 27: Ausgabe mit Zählung verschiedener Tupel in einer Gruppe mit Nebenbedingung
Belegt
MatrNr
√
KursNr
√
Semester
◦
◦
◦
Person
√
√
Name
Gebdatum
...
Geburtsort
Note
Adresse
◦
◦
Bild 28: Kartesisches Produkt mit Projektion
c
Visual SQL °by
β
31
Soweit haben wir Visual SQL nur für einfache Ausdrücke eingeführt, die allerdings so einfach sind, dass
eine Verwendung von Visual SQL einen überflüssigen Luxus darstellt. Wir werden jedoch in den nächsten
Teilkapiteln sehen, dass sich für komplexe Ausdrücke die Einführung von Visual SQL tatsächlich lohnt.
2.2.3
Anfragen mit einer Verknüpfung mehrerer Relationen
Die Verknüpfung mehrerer Relationen ist gewöhnlich mit längeren Pfaden in Schemata verbunden, mitunter
sogar mit ganzen Teilschemata. Diese Teilschemata können auch selbst wieder Aggregate und Teilschemata
enthalten. Wir betrachten als erstes eine einfache Anfrage:
Was leistet die BTU für die echten Cottbuser?
Diese Anfrage kann wieder durch einige Interpretationen ergänzt werden. Eine Form der Ergänzung wird
in Bild 29 und der folgenden SQL-Anweisung dargestellt. Mit ≈ wird ein Vergleichsprädikat verwendet, das
noch weiter verfeinert werden kann.
Belegt
√
Semester
√
KursNr
MatrNr
Person
Student
=
Geburtsort
MatrNr
...
Name
Note
Gebdatum
Cottbus
Adresse
√
Name
=
=
≈
√
Gebdatum
Bild 29: Natürlicher Verbund mit Projektion
SELECT
FROM
WHERE
KursNr, Semester, Person.Name, Person.Gebdatum
Belegt, Student, Person
Belegt.MatrNr = Student.MatrNr AND Student.Name = Person.Name
AND
Person.Gebdatum = Student.Gebdatum
AND
Geburtsort LIKE "%Cottbus%";
SQL’92 erlaubt hierfür auch eine andere Darstellung. Damit wird einem Visual SQL Interpreter auch eine
systemgerechte Übersetzung abverlangt. Wir sehen allerdings gleichzeitig auch, dass Visual SQL unterschiedliche Übersetzungen je nach verwendeten DBMS zulässt.
SELECT
FROM
WHERE
KursNr, Semester, Person.Name, Person.Gebdatum
Belegt JOIN Student ON (Belegt.MatrNr = Student.MatrNr)
JOIN Person ON (Student.Name = Person.Name AND Person.Gebdatum = Student.Gebdatum)
Geburtsort LIKE "%Cottbus%";
SQL ist als orthogonale Sprache konzipiert worden, d.h. es soll potentiell jeder Konstrukt mit jedem anderen Konstrukt kombiniert werden können. Dies unterstützen wir auch in Visual SQL. Als Beispiel betrachten
wir die folgende Anfrage, deren Auflösung in Bild 30 in der Kombination von Verbund und abgeleiteten
Attributen dargestellt wird.
Wieviele Studenten nehmen im Wintersemester 2002/2003 am Kurs “Datenbanken I” teil?
SELECT
FROM
WHERE
Titel, COUNT(MatrNr) AS teilnehmerAnz
Belegt, Kurs
Belegt.KursNr = Kurs.KursNr AND Semester = "WS2002/2003"
AND
Titel = "Datenbanken I";
32
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Belegt
WS2002/2003
=
Semester
√
KursNr
√
Kurs
=
MatrNr
KursNr
√
Titel
...
...
=
Datenbanken I
Note
teilnehmerAnz = COUNT(MatrNr)
Bild 30: Natürlicher Verbund mit abgeleiteten Attribut
Der Vorteil von Visual SQL wird voll sichtbar, wenn man Pfadanfragen betrachtet:
Gesucht sind alle Kurse aller Semester zusammen mit allen Personen absteigend geordnet nach Semester
und aufsteigend nach Kurs.
Die Ordnungsbeziehung erfordert eine Blockstruktur mit einem Teilschema als Zwischenresultat.
order by
Semester DESC, KursNr
Belegt
MatrNr
√
KursNr
√
Semester
◦
◦
◦
Person
√
√
Name
Gebdatum
...
Geburtsort
Note
Adresse
◦
◦
Bild 31: Kartesisches Produkt mit Projektion und ORDER BY
SELECT
KursNr, Semester, Name, Gebdatum
FROM
Belegt, Person
ORDER BY Semester DESC, KursNr;
Wir betrachten nun eine weitere Pfadanfrage.
Welcher Student (mit Namen) wird von wem betreut (mit Namen)?
SELECT
FROM
WHERE
P1.Name, P2.Name
Person P1, Person P2, Student, Professor, Betreuer
P1.Name = Student.Name AND P1.Gebdatum = Student.GebDatum
AND Student.MatrNr = Betreuer.MatrNr
AND Betreuer.BetrName = Professor.Name
AND Betreuer.BetrGebDatum = Professor.Gebdatum
AND P2.Name = Professor.Name AND Professor.GebDatum = P2.Gebdatum;
c
Visual SQL °by
β
33
Betreuer
Student
Person P1
√
Name
Gebdatum
MatrNr
=
=
MatrNr
Name
=
Professor
=
BetrName
Gebdatum
BetrGebdatum
...
Geburtsort
...
=
=
Name
Person P2
√
Name
=
Gebdatum
...
Gebdatum
...
Bild 32: Natürlicher Verbund mit Alias-Namen
Diese Pfadanfrage kann auch automatisch mit einer Abkürzung wie in Bild 33 angegeben werden.
Betreuer
Student
Person P1
√
Name
Gebdatum
=
MatrNr
=
=
MatrNr
=
Name
BetrName
Gebdatum
BetrGebdatum
...
Geburtsort
...
Person P2
√
Name
=
Gebdatum
...
Bild 33: Natürlicher Verbund mit Alias-Namen und Verkürzung
Wir verwenden die folgenden Konventionen zur verkürzten Darstellung:
• Es werden per default die kürzesten Pfade im ER- bzw. Visual SQL Schema verwendet.
• Gibt es nur einen kürzesten Pfad, dann wird dieser verwandt.
• Gibt es mehrere Pfade, die gleich lang sind, und keinen Pfad, der kürzer ist als diese Pfade, dann wird
durch einen Visual SQL Typ die Auswahl des Pfades bestimmt.
Mit diesen Verkürzungsregeln können wir auch das Schema in Bild 34 verwenden.
In diesem Falle wird die Matrikelnummer der Studenten ebenfalls ergänzt. Mit der Gleichheit von Person
Betreuer
Person P1
√
Name
Gebdatum
Geburtsort
...
=
MatrNr
BetrName
BetrGebdatum
...
=
=
Person P2
√
Name
Gebdatum
...
Bild 34: Natürlicher Verbund mit Alias-Namen und voller Verkürzung
P1 und MatrNr wird die Verbindung nur angegeben. Ein Schemaeditor kann dann diese Anfrage automatisch
ergänzen.
Wir werden im Weiteren diese Möglichkeit zur Erläuterung von Visual SQL nich verwenden. Sie ist jedoch
von praktischer Bedeutung und sollte deshalb in dieser Form Anwendung finden.
34
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Wir wenden uns nun etwas komplexeren Anfragen zu, die auch die Mächtigkeit von Visual SQL demonstriert. Gleichzeitig können wir auch Beschränkungen von SQL überwinden.
Welcher Student belegt alle Lehrveranstaltungen, die sein Betreuer gab bzw. gibt? bzw. Welcher Student ist
mindestens so schlau wie sein Betreuer?
Diese Anfrage ist relativ schnell formuliert, wenn Mengen zugelassen sind. In diesem Falle lautet eine
Reformulierung:
Für welche Studenten enthält die Menge der belegten Lehrveranstaltungen die Menge der Lehrveranstaltungen des Betreuers?
In Bild 35 wird diese Option zur Anfragedarstellung ausgenutzt.
Vorlesung V1
Person P1
√
Name
Gebdatum
MatrNr
=
=
KursNr
Betreuer
Student S1
=
Name
MatrNr
=
BetrName
Gebdatum
BetrGebdatum
...
Geburtsort
...
Professor P2
√
Name
=
Gebdatum
...
{ (S1,V1) } ⊆ { (S2,V2)}
Belegt
Student S2
MatrNr
Name
Gebdatum
=
Semester
Name
GebDatum
=
Name
Gebdatum
...
oder
(S1, V1.KursNr)
⊆
(S2, V2.KursNr)
Vorlesung V2
MatrNr
KursNr
Semester
=
=
=
=
=
KursNr
Semester
Name
Gebdatum
...
Bild 35: Komplexere Anfrage mit der Verwendung von Mengen zur Formulierung
SQL erlaubt leider die Darstellung von universellen Prädikaten nicht, sondern verwendet nur existentielle
Prädikate. Dadurch ist die Anfrage mit SQL viel trickreicher. Um eine existentiell formulierte Anfrage zu
erzeugen, bedienen wir uns eines Tricks, den jeder SQL-Programmierer am Anfang lernt:
∀ P (x)
⇐⇒
¬ ∃ ¬ P (x)
Wir müssen auf die Negation zurückgreifen und handeln uns ggf. Probleme mit Nullwerten und Defaultwerten ein. Diese Probleme werden durch eine weitere Aufblähung der Anfragebedingungen und auch ggf. durch
eine tiefere Schachtelung bewältigt. Damit wird die SQL-Anfrage jedoch unübersichtlich und unverständlich.
Die oben angegebene Formel ist nur dann richtig, wenn keine Nullwerte oder Defaultwerte in den Anfrageattributen erlaubt sind. Bei den Nullwerten kommt als weitere Schwierigkeit hinzu, dass für Tupel x = (x1 , ..., xn )
mit n > 1 nicht etwa die Äquivalenz
( x 6= NULL )
⇐⇒
∀ i ( xi 6= NULL )
c
Visual SQL °by
β
35
gilt, sondern die Äquivalenzen
( x 6= NULL )
⇐⇒
∃ i ( xi 6= NULL )
sowie die
x = NULL
⇐⇒
∀ i ( xi = NULL )
gelten.
SELECT
FROM
WHERE
P1.Name, P2.Name
Person P1, Professor P2, Student S1, Betreuer, Vorlesung V1
P1.Name = Student.Name AND P1.Gebdatum = Student.GebDatum
AND S1.MatrNr = Betreuer.MatrNr
AND Betreuer.BetrName = Professor.Name
AND Betreuer.BetrGebDatum = Professor.Gebdatum
AND P2.Name = Professor.Name AND P2.GebDatum = P2.Gebdatum
AND V1.Name = Professor.Name AND V1.Gebdatum = Professor.Gebdatum
AND
(V1.KursNr, V1.Semester, V1.Name, V1.Gebdatum,S1.MatrNr)
IN
(SELECT V2.KursNr, V2.Semester, V2.Name, V2.Gebdatum, S2.MatrNr
FROM Student S2, Belegt, Vorlesung V2
WHERE
S2.MatrNr = Belegt.MatrNr AND
Belegt.KursNr = V2.KursNr AND
Belegt.Semester = V2.Semester AND
Belegt.Name = V2.Name
AND
Belegt.Gebdatum = V2.Gebdatum
);
Jede Anfrage kann in unterschiedlicher Form gestellt werden. Diese Variabilität wird mit den Möglichkeiten von Visual SQL vergrössert. Wir können z.B. Teilschemata einführen und damit die Übersichtlichkeit
verbessern, gleichzeitig aber zu syntaktisch anderen Konstrukten kommen. So ist die folgende Anfrage bereits
in Bild 32 angegeben.
Alle Studentennamen mit den Namen ihrer Betreuer:
Sie wird durch die folgende SQL-Anfrage berechnet.
SELECT
FROM
WHERE
P1.Name, P2.Name
Person P1, Person P2, Student, Professor, Betreuer
P1.Name = Student.Name AND P1.Gebdatum = Student.GebDatum
AND Student.MatrNr = Betreuer.MatrNr
AND Betreuer.BetrName = Professor.Name
AND Betreuer.BetrGebDatum = Professor.Gebdatum
AND P2.Name = Professor.Name AND Professor.GebDatum = P2.Gebdatum;
Syntaktisch sind jedoch Bild 32 und Bild 36 verschieden. Diese Darstellung wird durch die folgende SQLAnfrage berechnet:
SELECT
FROM
WHERE
P1.Name, P2.Name
Person P1, Person P2
(P1.Name, P1.Gebdatum, P2.Name, P2.Gebdatum)
IN
(SELECT S1.Name, S1.Gebdatum, P3.Name, P3.Gebdatum
36
B. Thalheim,
Student S1
Person P1
√
Name
MatrNr
P1 ∗ S1
Gebdatum
=
Betreuer
MatrNr
Name
BetrName
Gebdatum
BetrGebdatum
...
Geburtsort
...
Preprint BTU - Informatik - I-08-2003
=
=
Professor P3
P2 ∗ P3
Name
Gebdatum
...
Person P2
√
Name
Gebdatum
...
Bild 36: Natürlicher Verbund mit Sicht über Teilverbund
FROM Student S1, Betreuer, Professor P3
WHERE Student.MatrNr = Betreuer.MatrNr
AND Betreuer.BetrName = P3.Name
AND Betreuer.BetrGebDatum = P3.Gebdatum
);
Teilschemata sind jedoch nicht nur geeignet, um geschachtelte Anfragen darzustellen, sondern dienen
insbesondere der Berechnung von Zwischenresultaten. Wir wollen dies mit der folgenden Anfrage illustrieren:
Welcher Mitarbeiter verdient weniger als der Durchschnitt der studentischen Mitarbeiter?
Um diese Anfrage zu berechnen, muss zuerst der Gehaltsdurchschnitt aller studentischen Mitarbeiter
berechnet werden. Bild 37 stellt damit eine Lösung dar.
StudMitarbeiter
MatrNr
MitarbID
Mitarbeiter
=
MitarbID
Von
...
Mitarbeiter
√
MitarbID
...
Gehalt
...
AVG(Gehalt)
Bild 37: Teilanfrage mit Vergleich
Diese Lösung entspricht der folgenden SQL-Anfrage:
SELECT
FROM
WHERE
Mitarbeiter.MitarbID, Mitarbeiter.Gehalt
Mitarbeiter
Gehalt <
(SELECT AVG(Gehalt)
FROM Mitarbeiter M1
WHERE M1.MitarbID
IN (SELECT S1.MitarbID
FROM StudMitarbeiter S1 ));
>
√
Gehalt
c
Visual SQL °by
β
37
Anfragen, die eine Generalisierung enthalten, sind oft etwas schwieriger formulierbar. Wir wollen dies an
der folgenden Anfrage untersuchen.
Welcher Student hat eine saubere Bilanz, d.h. alle Lehrveranstaltungen mit der Note 1 absolviert?
Visual SQL lässt auch den ANY-Konstrukt zu und führt dann zu folgender Anfrage in Bild 38.
hoert
KursNr
Student
Person
√
Name
Gebdatum
...
MatrNr
=
=
= ANY
Name
Gebdatum
...
Semester
√
1 MatrNr
Note
...
=
1
Bild 38: Teilanfrage mit Vergleich
Diese Anfrage entspricht der folgenden SQL-Anfrage.
SELECT
FROM
WHERE
Name
Person, Student
Person.Name = Student.Name AND Person.Gebdatum = Student.Gebdatum AND
MatrNr = ANY
(SELECT S1.MatrNr
FROM hoert S1
WHERE Note = 1);
Beide Lösungen sind jedoch relativ schwierig verständlich für den Anfänger. Deshalb können wir auch eine andere Anfrage verwenden, die oft einfacher formulierbar ist. In Bild 39 wird eine alternative Lösung angegeben.
Diese Anfrage zeigt aber auch, dass insbesondere bei überschaubaren Wertebereichen einfachere Möglichkeiten existieren. In Bild 40 wird dazu eine Lösung angegeben, die den kleinen Wertebereich für Noten ausnutzt.
Visual SQL entfaltet seine Möglichkeiten, wenn komplexere Anfragen zu Verschachtelungen führen, die
ihrerseits wieder geschachtelt sind. Wir betrachten dazu die folgende Anfrage, deren Lösung in Bild 41 und
der folgenden SQL-Anweisung angegeben ist.
Gesucht sind alle Namen von Studenten, die einen mindestens ebenso guten Durchschnitt haben wie der
kleinste Durchschnitt aller Studenten.
SELECT
FROM
WHERE
Person.Name, AVG(Note)
Person, Student, hoert
Person.Name = Student.Name AND Person.Gebdatum = Student.Gebdatum AND
Student.MatrNr = hoert.MatrNr AND
hoert.Note IS NOT NULL
GROUP BY Person.Name
HAVING
AVG(Note) <= ANY
(SELECT AVG(Note)
FROM hoert H1
WHERE H1.Note IS NOT NULL
GROUP BY H1.MatrNr};
38
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
hoert H1
KursNr
Student S1
Person
√
Name
Gebdatum
...
MatrNr
=
=
=
Name
Semester
√
1 MatrNr
Note
...
Gebdatum
...
6=
NULL
{ H1 } = { H2 }
S1 = S2
KursNr
Student S2
MatrNr
hoert H2
=
Name
Semester
√
1 MatrNr
Gebdatum
...
Note
...
=
1
Bild 39: Teilanfrage mit Vergleich über eine Menge
Student S1
Person
√
Name
Gebdatum
...
MatrNr
=
=
Name
Gebdatum
...
NOT IN
hoert H2
Student S2
√
MatrNr
KursNr
=
Name
Semester
√
1 MatrNr
Gebdatum
...
Note
...
Bild 40: Teilanfrage mit Differenzbildung
∈ {2,3,4,5}
c
Visual SQL °by
β
39
Student
group by
Name
Person
√
Name
Gebdatum
...
hoert
=
MatrNr
=
=
MatrNr
Name
Semester
Gebdatum
...
KursNr
having
Note
...
√
1 AVG(Note)
AVG(Note)
IS
NOT NULL
<= ANY
hoert
group by
MatrNr
MatrNr
Semester
KursNr
Note
...
√
2 AVG(Note)
IS
NOT NULL
Bild 41: Teilanfrage mit Vergleich, ANY und HAVING
Die folgende Anfrage sucht dagegen nach den permanent schlechteren Studenten.
Gesucht sind alle Namen und Noten von Studenten, die Noten erhalten haben, die nicht kleiner sind als der
grösste Durchschnitt der Noten ermittelt über alle Studenten.
Eine Lösung wird in Bild 42 dargestellt. Diese Lösung verwendet den ALL-Operator. Analog zur Lösung
in Bild 40 kann aber auch eine Lösung über die Negation gefunden werden.
SELECT DISTINCT
Person.Name, AVG(Note)
FROM
Person, Student, hoert
WHERE
Person.Name = Student.Name AND Person.Gebdatum = Student.Gebdatum
Student.MatrNr = hoert.MatrNr AND
hoert.Note IS NOT NULL AND
Note >=
(SELECT AVG(Note)
FROM hoert H1
WHERE H1.Note IS NOT NULL
GROUP BY H1.MatrNr};
2.2.4
AND
Anfragen unter Einbeziehung von temporären Hilfstabellen
SQL erlaubt eine explizite Einführung temporärer Tabellen. Dieser Konstrukt ist dem von Sichten ähnlich.
Sichten werden i.a. nicht materialisiert, d.h. berechnet. Temporäre Tabellen werden dagegen berechnet und
nach der Berechnung wieder gelöscht. Sie werden durch Transaktionen benutzt. Die Erzeugung wird durch
gestrichelte Rechtecke mit einer Kennzeichnung durch temporary in Visual SQL dargestellt. Diesem Rechteck wird eine Relation durch Ausgabeattribute zugeordnet. Die Tiefe der Ausgabeattribute wird, wie bereits
√
dargestellt, ggf. durch Indizes an den
-Symbolen gekennzeichnet.
40
B. Thalheim,
Student
DISTINCT
Person
√
Name
Gebdatum
...
=
DISTINCT
hoert
=
MatrNr
=
Preprint BTU - Informatik - I-08-2003
MatrNr
Name
Semester
Gebdatum
...
KursNr
√
Note
...
IS
NOT NULL
>= ALL
hoert
group by
MatrNr
MatrNr
Semester
KursNr
Note
...
√
1 AVG(Note)
IS
NOT NULL
Bild 42: Teilanfrage mit Vergleich und ALL
Temporäre Tabellen können zu einer effizienteren Berechnung führen, insbesondere bei DBMS, deren
Anfrageoptimierer nicht eigenständig solche Tabellen erzeugen. Es werden Zwischenresultate erzeugt, die
dann auch zeitweilig wie eigenständige Tabellen behandelt werden können. Als Beispiel betrachten wir einmal
die folgende Anfrage, deren Lösung mit SQL wie folgt und mit Visual SQL in Bild 43 angegeben wird.
Welcher Student hat den grössten GPA (grade-point-average)? Welche Studenten sind unsere Besten?
CREATE TEMPORARY TABLE Leistung
(MatrNr char(7) PRIMARY KEY,
Name
char(40),
gpa
float
);
INSERT INTO Leistung
SELECT
MatrNr, Name,
SUM((5-H.Note) * V.Credits) / SUM(V.Credits) AS GPA
FROM
Student, hoert H, Vorlesung V
WHERE Student.MatrNr = H.MatrNR
AND
H.Semester = V. Semester AND H.KursNr = V.KursNr AND
H.Note IS NOT NULL
GROUP BY MatrNr;
SELECT
MatrNr, Name, gpa, "BestStudent" AS Studentenkategorie
FROM
Leistung
WHERE gpa IN
(SELECT
MAX(gpa)
FROM Leistung);
DROP TEMPORARY TABLE Leistung;
In analoger Form können auch temporäre Tabellen selbst rekursiv aufgerufen und damit schrittweise
c
Visual SQL °by
β
41
TEMPORARY
TEMPORARY
√
MatrNr
√1
1 Name
gpa
Leistung
char(7)
char(40)
insert
float
Vorlesung
group by MatrNr
Leistung
√
MatrNr
√
Name
√
gpa
IN
√
1
Student S
√
MatrNr
√2
Name
2
Gebdatum
...
hoert
MatrNr
Semester
KursNr
...
NOT NULL
√
=
=
=
Dozent
Credits
Semester
KursNr
...
Note
gpa = SUM((5-Note)*Credits)/SUM(Credits)
2
MAX(gpa)
Bild 43: Temporäre Tabellen zur Formulierung von komplexen Anfragen
geschachtelt werden. Dazu betrachten wir eine Anfrage, die - wie in Bild 44 dargestellt - zu einer mehrstufigen
geschachtelten Anfrage führt:
Welcher Professor welcher Institute vergibt die besten Noten im Durchschnitt? Wer ist unser Liebling?
CREATE TEMPORARY TABLE Liebling
(Durchschn float,
Dozent
char(40),
Gebdatum
date,
Anzahl
smallint
PRIMARY KEY (Dozent, Geburtsdatum, Anzahl));
INSERT INTO Liebling
SELECT
Durchschn = AVG(Note), Dozent, Gebdatum,
Anzahl = COUNT(Note)
FROM
hoert H, Vorlesung V
WHERE H.Semester = V. Semester AND H.KursNr = V.KursNr AND
H.Note IS NOT NULL
GROUP BY Dozent, Gebdatum;
SELECT
FROM
WHERE
Dozent, Gebdatum, InIName AS Institut
Professor P, Liebling
P.Name = L.Dozent AND P.Gebdatum = L.Gebdatum AND
Durchschnitt =
(SELECT
MIN(Durchschnittsnote)
FROM Liebling);
DROP TEMPORARY TABLE
Liebling;
42
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
TEMPORARY
TEMPORARY
Liebling
Durchschn
float
Anzahl
√2
1 Dozent
√3
1 Gebdatum
smallint
char(40)
insert
date
Professor
Vorlesung
Name
Gebdatum
√
InIName
...
Semester
KursNr
...
∗
Liebling
√2
1 Name
√3
1
Dozent
hoert
MatrNr
Gebdatum
=
Durchschn
√
1
=
=
Gebdatum
Semester
KursNr
...
Note
√
2 AVG(Note)
√
2 Anzahl = COUNT(Note)
NOT NULL
Anzahl
group by Dozent, Gebdatum
MIN(Durchschnittsnote)
Bild 44: Temporäre Tabellen zur vierstufigen Formulierung von komplexen Anfragen
2.2.5
Anfragen unter Einbeziehung des äusseren Verbundes und von Theta-Verbunden
Der natürliche Verbund führt zum Streichen von Objekten in der Anfrageberechnung, denen keine anderen
Objekte entsprechen. Oft wird dies nicht in dieser Form benötigt. Deshalb wird neben dem natürlichen
Verbund auch der äussere Verbund, der linke bzw. rechte äussere Verbund, sowie auch der Theta-Verbund mit
einem Verbund-Prädikat zugelassen. Zur Kennzeichnung dieser Verbunde verwenden wir “Krähenfüsse” auf
der Seite, die dem äusseren Verbund entsprechen. Das Verbundprädikat wird ebenso wie der Theta-Verbund
direkt mit der verbindenden Linie dargestellt.
Die Anfrage
Gesucht ist eine vollständige Liste aller Professoren mit ihren Vorlesungen im WS2002/2003. Diejenigen, die
nicht unterrichten, sollen ebenso aufgezeigt werden.
wird in Bild 45 und der folgenden SQL-Anweisung berechnet.
Vorlesung V
ORDER Name,
BY
Gebdatum
Professor P
√
Name
√
Gebdatum
Dozent
P.Name = V.Dozent
P.Gebdatum=V.Gebdatum
Gebdatum
InIName
...
Semester
√
KursNr
...
Bild 45: Linker äusserer Verbund
SELECT
FROM
WHERE
ORDER BY
P.Name, P.Gebdatum, KursNr
Professor P LEFT OUTER JOIN Vorlesung V
ON P.Name = V.Dozent AND P.Gebdatum=V.Gebdatum
V.Semester = "WS2002/2003"
P.Name, P.Gebdatum;
Die Anfrage
=
WS2002/2003
c
Visual SQL °by
β
43
Gesucht sind für alle Kurse die Lehrverantwortlichen.
führt zu einem rechten äusseren Verbund, den wir in Bild 46 darstellen.
Vorlesung V
ORDER
BY
Name,
Gebdatum
Dozent
Professor P
√
Name
√
Gebdatum
P.Name = V.Dozent
P.Gebdatum=V.Gebdatum
Gebdatum
InIName
Semester
√
KursNr
...
...
Bild 46: Rechter äusserer Verbund
SELECT
FROM
ORDER BY
2.3
V.KursNr, P.Name, P.Gebdatum
Professor P RIGHT OUTER JOIN Vorlesung V
ON P.Name = V.Dozent AND P.Gebdatum=V.Gebdatum
KursNr;
Weitere Typen von Anfragen: Vereinigung, Korrelation, ALL
SQL stellt auch die Mengenoperatoren für Anfragen über typengleichen Ausdrücken bereit. Diese Möglichkeit
ist nicht in allen DBMS in der gleichen Form implementiert. In Visual SQL können wir in vollständig analoger
Form auch die entsprechenden Ausdrücke bereitstellen. Wir betrachten dazu eine Anfrage, die die Vereinigung
von Mengen benötigt:
Gesucht sind alle Namen und Geburtsdaten aller Studenten und Professoren.
Student S
MatrNr
√
Name
√
Gebdatum
...
∪
Professor P
√
Name
√
Gebdatum
InIName
...
Bild 47: Vereinigung von zwei vollständig typengleichen Relationen
SELECT
FROM
UNION
SELECT
FROM
S.Name, S.Gebdatum
Student S
P.Name, P.Gebdatum
Professor P;
Analog auch kann man den Durchschnitt von Mengen und die Mengendifferenz darstellen. Diesen entsprechen
die SQL-Operatoren:
INTERSECT
EXCEPT
MINUS
44
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
Rekursion einer fest beschränkten Tiefe kann in SQL durch entsprechende Pfadausdrücke dargestellt werden. Bild 48 erzeugt für die folgende Anfrage die entsprechende Lösung:
Für jeden Professor soll eine Liste der Kollegen ausgegeben werden, die mehr erhalten.
Professor P2
√
Name
√
Gebdatum
√
Gehalt
<
Professor P1
√
Name
√
Gebdatum
√
Gehalt
...
...
Bild 48: Rekursiver Verbund einer Relation
SELECT
FROM
WHERE
P1.Name AS Name_Wenigerbezahlt, P1.Gebdatum AS Geburtsdatum_Wenigerbezahlt,
P1.Gehalt, P2.Gehalt AS Name_Besserbezahlt,
P2.Gebdatum AS Geburtsdatum_Unterbezahlt, P2.Gehalt
Professor P1, Professor P2
P1.Gehalt < P2.Gehalt;
Visual SQL erlaubt auch die Einführung beliebiger SQL-Prädikate wie exists, in not exists und not
in für die Verbinder. In Bild 49 wird dazu eine korrelierte Lösung für die folgende Anfrage bereitgestellt.
Welche Studenten haben bislang noch keine Lehrveranstaltung erfolgreich besucht?
hoert
MatrNr
Student S
√
MatrNr
√
Name
Gebdatum
...
NOT EXISTS
=
S.MatrNr
IS
NOT NULL
Semester
KursNr
Note
...
Bild 49: Korrelierte Teilanfrage über NOT EXISTS
SELECT
FROM
WHERE
S.Name, S.Gebdatum
Student S
NOT EXISTS
(SELECT *
FROM
hoert H
WHERE S.MatrNr = H.MatrNr
AND H.Note IS NOT NULL);
Die Korrelation bzw. Assoziation über einen Theta-Verbund ist eine relativ ausdrucksstarke Operation. Sie
kann in Visual SQL in relativ einfacher Form über einen Verbinder eingeführt werden. Die folgende Anfrage
wird in Bild 50 mit den Mitteln von Visual SQL berechnet:
Welche Studenten haben eine bessere Note als der Durchschnitt in ihren Lehrveranstaltungen?
c
Visual SQL °by
β
Student S
=
MatrNr
√
Name
√
Gebdatum
...
hoert H
MatrNr
Semester
KursNr
Note
...
45
order by KursNr, Semester
<
hoert
MatrNr
Semester
= H.Semester
KursNr
= H.KursNr
Note
IS NOT NULL
...
√
1 AVG(Note)
Bild 50: Korrelierte Teilanfrage über direkten Vergleich
SELECT
FROM
WHERE
S.Name, H.Note, H.KursNr, H.Semester
Student S, hoert H
S.MatrNr = H.MatrNR
AND
NOTE <
(SELECT
AVG(Note)
FROM
hoert H1
WHERE H.KursNr = H1.KursNr AND
AND H1.Note IS NOT NULL)
ORDER BY H.KursNr, H.Semester;
H.Semester = H1.Semester
Eine Anfragesprache ist orthogonal, wenn jeder Anfragekonstrukt mit jedem kombiniert werden kann. So
sollte z.B. der Mengenvergleich auch mit Aggregationen formulierbar sein. Als Beispiel betrachten wir die
folgende Anfrage:
Gesucht sind alle Namen von Paaren von Studenten, die nur gemeinsam Lehrveranstaltungen erfolgreich
besuchen.
Die Lösung in Bild 51 ist viel einfacher als die folgende SQL-Lösung. Die Gleichheit von Mengen kann
man in Visual SQL direkt ausdrücken. In SQL muss man dagegen auf die doppelte Negation zurückgreifen,
wobei auch dabei noch die Nullwerte einer gesonderten Behandlung unterzogen werden müssen.
SELECT
FROM
WHERE
P1.Name, P2.Name
Person P1, Person P2, Student S1, Student S2, hoert H1, hoert H2
P1.Name = S1.Name AND P1.Gebdatum = S1.Gebdatum AND
S1.MatrNr = H1.MatrNr AND H1.Note IS NOT NULL AND
P2.Name = S2.Name AND P2.Gebdatum = S2.Gebdatum AND
S2.MatrNr = H2.MatrNr AND H2.Note IS NOT NULL
AND NOT EXISTS
(SELECT *
FROM hoert H3
WHERE H3.Note IS NOT NULL AND
H3.MatrNr NOT IN
(SELECT H4.MatrNr
FROM hoert H4
WHERE H4.MatrNr = H2.MatrNr
AND H4.Note IS NOT NULL)
AND H1.MatrNr = H3.MatrNr)
AND NOT EXISTS
(SELECT *
FROM hoert H5
WHERE H5.Note IS NOT NULL AND
H5.MatrNr NOT IN
46
B. Thalheim,
Person P1
√
Name
Gebdatum
...
=
=
Student S1
MatrNr
Name
Gebdatum
...
=
Gebdatum
...
hoert
MatrNr
Semester
KursNr
Note
...
IS NOT NULL
==
<>
Person P2
√
Name
Preprint BTU - Informatik - I-08-2003
=
=
Student S2
MatrNr
Name
Gebdatum
...
=
hoert
MatrNr
Semester
KursNr
Note
...
IS NOT NULL
Bild 51: Korrelierte Teilanfragen: Visual SQL hat eine bessere Syntax als SQL
(SELECT H6.MatrNr
FROM hoert H6
WHERE H6.MatrNr = H1.MatrNr
AND H4.Note IS NOT NULL)
AND H2.MatrNr = H5.MatrNr)
AND S1.MatrNr < S2.MatrNr
GROUP BY P1.Name, P2.Name;
Die Korrelation kann auch mit den Anfrageoperatoren any, all, some erzeugt werden. So wird z.B. in
Bild 52 eine Korrelation mit all für die folgende Frage gelöst:
Welcher Kurs wird nur von Informatik-Studenten besucht?
SELECT
FROM
WHERE
K.KursBez
Kurs K
"Informatik" = ALL
(SELECT
SName
FROM
hoert, Student, EingeschriebenIn
WHERE K.KursNr = hoert.KursNr
AND Student.MatrNr = hoert.MatrNr
AND EingeschriebenIn.StudMatrNr = Student.MatrNr );
In analoger Form verwendet Bild 53 den Operator exists zur Darstellung der folgenden Frage:
Welche Studenten nehmen an mehr als vier Vorlesungen im WS2002/2003 teil?
SELECT
FROM
WHERE
S. Name, S.MatrNr
Student S
EXISTS
(SELECT
COUNT(*)
FROM
hoert
WHERE
hoert.Semester = "WS2002/2003"
c
Visual SQL °by
β
47
Kurs K
KursNr
√
KursBez
Beschreib
...
√
1 Informatik
= ALL
EingeschriebenIn
StudMatrNr
√
1 SName
Von
Student
=
hoert
=
MatrNr
MatrNr
Name
KursNr
Gebdatum
Semester
Bis
=
K.KursNr
Note
...
Bild 52: Korrelierte Teilanfragen mit ALL
hoert
MatrNr
Student S
√
MatrNr
EXISTS
=
S.MatrNr
=
WS2002/2003
KursNr
Semester
Name
Note
Gebdatum
...
√
1
COUNT(*)
HAVING
COUNT(*)>4
Bild 53: Korrelierte Teilanfragen mit EXISTS und HAVING
48
B. Thalheim,
HAVING
2.4
Preprint BTU - Informatik - I-08-2003
AND S.MatrNr = hoert.MatrNr
COUNT(*) > 4);
Sichten
Sichten werden in Datenbanksystemen aus einer ganzen Reihe von Gründen eingeführt:
1. Berechnungssichten stellen Zwischenausdrücke dar, mit denen eine wiederholte Angabe des gleichen Berechnungsausdruckes vermieden werden kann. Es werden dabei zwei Typen unterschieden:
1.a. Materialisierte Sichten erlauben eine schnellere Berechnung von Anfragen durch die Vorberechnung
von häufig benötigten Informationen.
Es sind bei der Entwicklung von Systemen eine ganze Reihe von Zugängen möglich:
Inkrementelle Datenbanksysteme bauen schalenartig ihre Relationen auf. Solche Datenbanksysteme sind immer dann anzutreffen, wenn die Fachabteilungen Daten anderer Fachabteilungen
verwenden, ohne diese zu verändern. Eine typische Anwendung dieser Art ist das Facility Management im Bauwesen.
Inkrementierte Sichten erlauben ein schrittweises Nachführen von Datenbeständen je nach Modifikation der Grunddaten.
1.b. Virtuelle Sichten stellen Berechnungsvorschriften zur Verfügung, die bei Bedarf in die Anfrage ‘expandiert’ werden.
2. Modifikationssichten werden benutzt, wenn eine direkte Modifikation der Grunddaten nicht erlaubt oder aus
Sicherheitsgründen gesperrt ist. Sie müssen neben den Anforderungen an die Formulierbarkeit auch die
Identifizierbarkeit der zu modifizierenden Objekte sichern. Oft werden dazu die relationalen Operatoren
eingeschränkt eingesetzt. Diese Einschränkung ist jedoch oft nicht in einer strikten Form möglich.
Im Co-Design-Zugang werden Modifikationssichten wie in Bild 54 zur Unterstützung der Interaktivität
benutzt.
6
lokal
Sichten
Sichten
zum Retrieval
-
6 Unterstützung
von
ManipulationsFiltrierung
anforderungen
Konstruktion
durch
ª Sichten
global
Dialoge
6
unterstützte
Prozesse
Datenbank- Prozesse
Schema
abgeleitete
Prozesse
dynamisch
statisch
-
Bild 54: Co-Design von Daten und Prozessen
3. Sicherungssichten: Sicherungssichten dienen der Absicherung von Daten. Eine pragmatische Grundregel
sicherer Datenbank-Anwendungen ist z.B., dass nur genau die autorisierten Benutzer die Grunddatenbestände ändern dürfen. Eine ganze Reihe von kleineren Systemen erlauben allerdings keine Sichten und
demzufolge sind Daten allen Benutzern zugänglich.
c
Visual SQL °by
β
49
4. Archivierungssichten: Archivierungssichten dienen nicht nur der ‘Endlagerung’ von Datenbeständen, sondern
auch insbesondere dem Ablegen von sicherheitsrelevanten Daten für den Fall von Eindringlingen, von
Programmierfehlern und für die Verwaltung von Daten.
5. Initialisierungssichten sind spezifische Sichten auf Datenbestände, die zur Initialisierung anderer Datenbanken herangezogen werden.
In der Datenbankliteratur werden Sichten meist als ein Gesamtkonzept offeriert. Diese Auffassung besitzt
einigen Charme für den Theoretiker, ist aber für den Praktiker in hohem Masse unbefriedigend. Werden
Sichten sowohl zur Berechnung von Daten als auch zur Modifikation von Datenbanken herangezogen, dann
entstehen selbstgeschaffene Probleme wie z.B. das Modifizierbarkeitsproblem über Sichten.
2.4.1
Sichten zur Berechnung von Anfragen
In Visual SQL werden Sichten als Relationen definiert, die als gestricheltes Rechteck dargestellt werden. Sie
werden mit einer Anfrage verbunden und analog zur Einfügeoperation behandelt. Bild 55 zeigt eine Sicht zur
Erzeugung der Postanschrift der Professoren einer Fakultät.
ProfAnschrift
√1
Name
√2
InIName
√3
Fakultät
√4
Postkasten
◦
Professor
√1
Name
Institut
Gebdatum
Kostenstelle
√3
Fakultät
√2
IName
Sprecher
Spezialisierung
InIName
=
Telefon
√4
Postkasten
Bild 55: Sichtendefinition über eine Anfrage
CREATE VIEW
SELECT
FROM
ProfAnschrift (Name, Institut, Fakultaet, Postkasten) AS
Name, IName, Fakultaet, Postkasten
Professor JOIN Institut ON (Professor.InIName = Institut.IName);
Die Berechnung einer Anfrage wird in DBMS durch Expansion der Sichtendefinition realisiert. So
wird z.B. die Anfrage in Bild 56
SELECT Name, ’Institut fuer ’+ Institut, Fakultaet, ’BTU Cottbus’
FROM ProfAnschrift
WHERE Name LIKE "%Thalheim%";
über durch die Sichtendefinition bzw. durch die SQL-Definition
CREATE VIEW
SELECT
FROM
ProfAnschrift (Name, Institut, Fakultaet, Postkasten) AS
Name, IName, Fakultaet, Postkasten
Professor JOIN Institut ON (Professor.InIName = Institut.IName);
zur folgenden Anfrage in Bild 57 expandiert:
50
B. Thalheim,
ProfAnschrift
√1
Name
√2
Institut
√3
Fakultät
√4
Postkasten
Preprint BTU - Informatik - I-08-2003
Professor
√1
Name
Institut
Gebdatum
Kostenstelle
√3
Fakultät
√2
IName
Sprecher
Spezialisierung
InIName
=
Telefon
√4
Postkasten
Bild 56: Sichtendefinition über eine Anfrage
LIKE
Thalheim
Professor
√1
Name
Institut
Gebdatum
Kostenstelle
√3
Fakultät
√2
IName
Sprecher
Spezialisierung
InIName
=
Telefon
Postkasten
Bild 57: Anfrage wird in die Sichtendefinition expandiert
AS ’Institut fuer’ + IName
c
Visual SQL °by
β
SELECT
FROM
WHERE
51
Name, ’Institut fuer ’+IName AS Institut, Fakultaet, ’BTU Cottbus’
Professor JOIN Institut ON (Professor.InIName = Institut.IName)
Name LIKE "%Thalheim%";
Modifikationsoperationen über Sichten sind nur unter bestimmten Bedingungen zugelassen:
• Eine from-Klausel ist nur über einer Tabelle definiert.
• group by, having, distinct, Aggregationsfunktionen sind nicht erlaubt.
• Die Anfrage ist nicht geschachtelt.
Modifikationssichten werden in SQL durch eine Kaskadierung mit einer Kontrolle von Integritätsbedingungen angegeben. Dann sind die Daten auch über Sichten unter bestimmten Bedingungen direkt pflegbar.
CREATE VIEW name (...) AS
SELECT ...
WITH [ CASCADED | LOCAL ] CHECK OPTION;
Ein Beispiel einer erlaubten Pflege durch Sichten wird in Bild 58 angegeben.
Vorlesung
√1
KursNr
Semester
√2
Dozent
◦
◦
◦
=
WS2003/2004
with
check
option
VorlesPlanung
√1
KursNr
√2
Dozent
insert
◦
◦
VALUES
Raum
1-3-3-1
Zeit
Thalheim
Bild 58: Eine erlaubte INSERT-Operation für eine Sicht (Einfügen der Vorlesung “Informatik III’)
Die entsprechende SQL-Anweisung ist:
CREATE VIEW VorlesPlan AS
SELECT
KursNr, Dozent
FROM
Vorlesung WHERE Semester = ’’WS2003/2004’’
WITH CASCADED CHECK OPTION;
INSERT INTO VorlesPlanung VALUES (’1-3-3-1’, ’Thalheim’);
In analoger Form kann auch eine Update-Anweisung direkt auf die Datenbank eingespielt werden. Bild 59
und die folgende SQL-Anweisung zeigen diese Möglichkeit auf.
CREATE VIEW RaumPlanung AS
SELECT
KursNr, Dozent, Raum, Zeit
FROM
Vorlesung WHERE Semester = ’’WS2003/2004’’
WITH LOCAL CHECK OPTION;
UPDATE RaumPlanung
SET
Raum =’’AudiMax’’ AND Zeit = ’’Mo, 1. + 2. DS’’
WHERE
KursNr = ’’1-3-3-1’’ AND Dozent = ’’THalheim’’;
52
B. Thalheim,
KursNr
Semester
√2
Dozent
√3
Raum
√4
Zeit
RaumPlanung
√1
KursNr
√2
Dozent
√3
Raum
√4
Zeit
◦
◦
Vorlesung
√1
Preprint BTU - Informatik - I-08-2003
◦
◦
◦
=
WS2003/2004
with
check
option
update
=
1-3-3-1
=
Thalheim
VALUES
AudiMax II
Mo, 1. + 2. DS
Bild 59: Eine erlaubte UPDATE-Operation für eine Sicht (Raumplanung der Vorlesung “Informatik III’)
DELETE
hoertOhne
√1
√2
MatrNr
Semester
√3
KursNr
◦
◦
◦ with
check
option
hoert
√1
MatrNr
√2
Semester
√3
KursNr
Note
◦
◦
◦
IS
NULL
...
Bild 60: Die DELETE-Operation kann in die Sichtendefinition expandiert werden
c
Visual SQL °by
β
53
Auch ein Streichen von Objekten kann aus Sichten an die Datenbank wie in Bild 60 und der folgenden
SQL-Anweisung erfolgen.
CREATE VIEW
SELECT
FROM
WHERE
DELETE FROM
hoertOhne AS
MatrNr, Semester, KursNr
hoert
NOTE IS NULL;
hoertOhne ;
Eine Sicht kann ebenso wie eine Tabelle, ein Index, eine Integritätsbedingung etc. auch gestrichen werden.
Die drop-Anweisung wird in Bild 61 illustriert.
hoertOhne
√1
MatrNr
√2
Semester
√3
KursNr
◦
◦
◦
Bild 61: Die DROP-Operation für jeden persistenten SQL-Befehl werden
DROP VIEW
hoertOhne ;
Oft sind Sichten ein gutes Mittel, um komplexere Anfragen überhaupt realisieren zu können.
Wir betrachten z.B. die folgende Anfrage: Gesucht sind für die Planung von Blockveranstaltungen Räume,
die an einem Tag für n Paare hintereinander frei sind.
Die Lösung wird in Bild 62 vorgestellt. Es wird eine Sicht mit allen noch nicht besetzten Räumen für
entsprechende Tage und Paare gewonnen. Wir nehmen dazu an, dass nur solche Räume, Tage und Paare
berücksichtigt werden, die in der Planung bereits eine Rolle spielten. Ausserdem wird angenommen, dass
jeder Tag die gleiche Anzahl von Paaren hat. Dann ist die Lösung relativ einfach durch eine Bestimmung aller
Tripel zu finden, die potentiell in die Planung eingehen könnten, aber noch nicht sind. Daraus können wir
dann einen n−Block bestimmen durch einen Vergleich mit den besetzten Paaren für die entsprechenden Tage
und Räume. Bild 62 stellt diese Lösung vor.
Die Lösung wird komplexer, wenn nicht alle Tage die gleiche Anzahl von Paaren besitzen und wenn Räume
nur zu gewissen Zeiten freigegeben werden können. In diesem Fall wird die Sichtendefinition etwas komplexer.
Diese Anfrage wurde durch eine zweifache Negation definiert. Sind Nullwerte erlaubt bei der Vorlesungsplanung, dann muss für die NOT EXISTS Begrenzung bei der Anfrage ggf. eine Ersetzung durch eine NOT
IN Begrenzung vorgenommen werden.
Wie bereits im Vierschrittverfahren angegeben, ist die Formulierung einer SQL-Anfrage durch eine Disambiguierung bzw. Verdeutlichung, eine Auflösung der Ellipsen und durch eine Reformulierung vorzubereiten.
In unserem Falle wurde dies erreicht durch:
• eine Klärung, inwieweit die Planungsstruktur regelmässig ist und dies auch ausgenutzt werden kann,
• eine Einschränkung auf Blöcke, die sich innerhalb eines Tages abbilden lassen,
• eine Hilfskonstruktion, die eine Formulierung vereinfacht, und
• eine Reformulierung in der folgenden Form: “Gesucht sind alle Folgen von Tripeln der Länge n
bestehend aus Raum, Tag und Vorlesungszeit (Paar) innerhalb eines Tages und für einen Raum, an dem
keine Vorlesung im Sommersemester 2002 geplant ist, aber potentiell gehalten werden kann.”
54
B. Thalheim,
RaumFrei F
√1
Raum
√2
Tag
√3
Paar
◦
◦
◦
NOT EXISTS
=
=
Vorlesung
Raum
Tag
BETWEEN F.Paar + 1 AND F.Paar + ( :n - 1)
Paar
Semester
Vorlesung
√1
Raum
...
=
Vorlesung
√2
Tag
...
Vorlesung
Raum
Tag
Paar
=
“SS2003”
Vorlesung
√3
Paar
...
NOT IN
Semester
Preprint BTU - Informatik - I-08-2003
“SS2003”
Bild 62: Auslastungsanfrage
c
Visual SQL °by
β
55
Blockvorlesungen können auch in unterschiedlichen Räumen stattfinden, z.B. im gleichen Gebäude. Dann ist
die Anfrage anders zu formulieren. Blockvorlesungen können auch definiert sein als Vorlesungen, die über eine
Woche verteilt sind. Deshalb ist eine Klärung der Frage vorher zu erreichen, um eine Anfrage dann eindeutig
formulieren zu können.
Die obige Anfrage wird durch folgende Sichtendefinition und folgende Anfrage realisiert:
CREATE VIEW
SELECT
FROM
WHERE
RaumFrei (Raum, Tag, Paar) AS
V1.Raum, V2.Tag, V3.Paar
Vorlesung V1, Vorlesung V2, Vorlesung V3
(V1.Raum, V2.Tag, V3.Paar)
NOT IN
(SELECT Raum, Tag, Paar
FROM Vorlesung
WHERE Semester = "SS2003")
;
SELECT Raum, Tag, Paar AS Beginn_Frei, ’Bis’, Paar+ ( :n -1) AS Ende_Frei
FROM RaumFrei F
WHERE NOT EXISTS
(SELECT *
FROM Vorlesung V
WHERE V.Raum = F.Raum AND V.Tag = F.Tag AND
V.Paar BETWEEN (F.Paar + 1) AND (F.Paar + ( :n -1))
AND V.Semester = "SS2003"
)
;
Wir wollen als abschliessendes Beispiel ein etwas komplexeres Berechnungsproblem lösen.
Eine etwas komplexere Sicht entsteht zur Abrechnung der Rechenzeiten anhand von Tabellen Accounting
(PersonID, SessionID, login, logout) und Person (PersonID, Name, ...)
Der Preis für die Rechenzeiten ergibt sich aus den Sekunden für die Rechenzeit multipliziert mit dem
relativen Login-Preis.
Eine nicht kompakte Lösung über zwei Sichten wird in Bild 63 und in Bild 64 illustriert.
Accounting
SessionID
Skala
Moment
PersonID
√
Login
Accounting
◦
SessionID
PersonID
∪
Logout
Bild 63: Erfassung von Momenten aus der Accounting-Tabelle
Darauf kann die Lösung in Bild 65 erzeugt werden.
Die entsprechenden SQL-Anweisungen sind dann:
Login
√
Logout
◦
56
B. Thalheim,
Auslastung
√1
Begin
√2
Ende
√3
AnzParallel
group by Begin, Ende
Skala S2
√2
Preprint BTU - Informatik - I-08-2003
Skala S1
>
Moment
Ende = Moment
√1
Login ≤ Moment
AND Logout > Moment
Moment
Accounting
SessionID
Beginn = Moment
PersonID
NOT EXISTS
S1.Moment < S3.Moment < S2.Moment
Login
Skala S3
Logout
Moment
√3
AnzParallel = COUNT(*)
Bild 64: Erfassung der Auslastung aus den Momenten
group by
Accounting
√3
SessionID
Auslastung
Begin
Preise
Anzahl
Ende
=
PersonID
≥
Login
≤
Logout
=
√1
Person
PersonID
√2
Name
...
AnzParallel
Betrag
√4
Betrag = SUM(Betrag * (Ende-Beginn))
Bild 65: Abrechnung für jeden Nutzer (mit Ausgabe der SessionID)
PersonID
SessionID
c
Visual SQL °by
β
57
CREATE VIEW Skala AS
SELECT Login
UNION
SELECT Logout
AS Moment
FROM
Accounting
AS Moment
FROM
Accounting;
CREATE VIEW Auslastung AS
SELECT Beginn, Ende, COUNT(*) AS AnzParallel
FROM Skala S1, Skala S2, Accounting A
WHERE S1.Moment < S2.Moment
AND NOT EXISTS
(SELECT * FROM Skala S3
WHERE S1.Moment < S3.Moment AND S3.Moment < S2.Moment)
AND A.Login <= S1.Moment
AND A.Logout > S1.Moment
GROUP BY Beginn, Ende;
PersonID
0815
Session
aa
Login
...
Logout
...
0815
ab
...
...
007
...
...
...
Begin
...
...
...
...
...
...
...
Ende
...
...
...
...
...
...
...
AnzParallel
...
...
...
...
...
...
...
Betrag
...
...
...
...
...
...
...
SELECT PersonID, Name, SessionID, SUM(Betrag * (Ende - Beginn)) AS Betrag
FROM Person Pe, Preise Pr, Accounting Ac, Auslastung Au
WHERE
Ac.PersonID = Pe.PersonID AND
Ac.Login <= Au.Beginn AND Ac.Logout >= Au.Ende
AND Pr.Anzahl = Au.AnzParallel
AND A.Logout > S1.Moment
GROUP BY PersonID, SessionID;
PersonID
0815
Session
aa
Login
...
Logout
...
...
...
ab
007
...
Beginn
...
...
...
...
...
...
...
Ende
...
...
...
...
...
...
...
AnzParallel
...
...
...
...
...
...
...
Betrag
...
...
...
...
...
...
...
SELECT PersonID, Name, SUM(Betrag * (Ende - Beginn)) AS Betrag
FROM Person Pe, Preise Pr, Accounting Ac, Auslastung Au
WHERE
Ac.PersonID = Pe.PersonID AND
Ac.Login <= Au.Beginn AND Ac.Logout >= Au.Ende
AND Pr.Anzahl = Au.AnzParallel
AND A.Logout > S1.Moment
GROUP BY PersonID;
58
B. Thalheim,
2.4.2
Preprint BTU - Informatik - I-08-2003
Modifikation über Sichten durch Trigger
Oracle und andere Systeme erlauben eine Modifikation über Sichten durch Triggeranwendung:
CREATE VIEW Studenteinschreibung AS
SELECT Student ATURAL JOIN EingeschriebenIn
ON Student.MatrNr = EingeschreibenIn.MatrNr
FROM Student, EingeschriebenIn;
CREATE TRIGGER StudEinschreib
INSTEAD OF INSERT ON Studenteneinschreibung
FOR EACH ROW
BEGIN
INSERT INTO Student VALUES (
:new.MatrNr, :new.Name, :new.GebDatum);
INSERT INTO EingeschriebenIn (StudMatrNr, SName, Von)
VALUES (:new.MatrNr, :new.SName, ’’WS2002’’);
END;
/
Diese Lösung kann in analoger Form für Visual SQL angegeben werden.
c
Visual SQL °by
β
59
Literaturverzeichnis
[ACS90] M. Angelaccio, T. Catarci, and G. Santucci, QBD: A graphical query language with recursion. IEEE Trans.
Software Eng. 16, 1990, 10.
[AmB90] A.L. Ambler and M.M. Burnett, Visual programming, programming by example, and programming by visualization: A taxonomy. Proc. Cisual Programming Environments: Paradigms and Systems (ed. E.P. Glinert), IEEE
Computer Society Press, 1990.
[BBN91] V. Breazu-Tannen, P. Buneman, and S. Naqvi, Structural recursion as a query language primitive. Workshop
on DBPL, 1991.
[BCC91] C. Batini, T. Catarci, M.F. Costabile, and S. Levialdi, Visual query systems. Technical report, Departimento
de Informatica e Sistemica, Universita Degli Studi Di roma “La Sapienza”, 1991.
[Bisk95] J. Biskup, Foundations of Information Systems. Vieweg, Braunschweig, 1995 (in German).
[BOO02] N.H. Balkir, G. Ozsoyoglu, and Z.M. Ozsoyoglu, A graphical query language: VISUAL and its query processing.
IEEE Transactions on Knowledge and Data Engineering, 14, 2002, 5, 955 - 979.
[BSV96] U. Brinkschulte, M. Siormanolakis, and H. Vogelsang, Visualization and manipulation of database contents
using corresponding symbols. Preprint University of Karlsruhe, Computer Science, Institute for Microcomputers
and Automation, 1996.
[CCL97] T. Catarci, M.F. Costabile, S. Levialdi, and C. Batini, Visual query systems for databases: A survey. J. Query
Languages and Computing, 8, 1997, 2, 215-260.
[CCL00] P.-K. Chen, G.-D. Chen, and B.-J. Liu, HVQS: The hierarchical visual query system for databases. J. Query
Languages, 11, 2000, 1-26
[Cha93] Chang et. all, Unpublished manuascript, 1993.
[ChT02] D.Chappell and J.H.Trimble Jr., A visual introduction to SQL. Wiley, NY, 2002.
[CKW89] W. Chen, M. Kifer, and D. Warren, Hilog as a platform for database languages. Workshop on DBPL, 1989.
[CMW88] I.F. Cruz, A.O. Mendelzon, and P.T. Wood, G+: Recursive queries without recursions. Second Int. conference
on Expert Database Systems, 1988, 645-666.
[Cru92] I. Cruz, Doodle: A visual language for object-oriented databases. ACM SIGMOD, 1992.
[Den95] Y. Dennebouy et al., SUPER: Visual interfaces for object+relationship data models. J. Visual Languages and
Computing, 5, 1995, 73-99.
[ElN00] R. Elmasri and S. Navathe, Fundamentals of database systems. Addison-Wesley, Reading, 2000.
[Goy97] N. Goyal et al., Picture programming project. Proc. ACM SIGMOD, 1997, 514-516.
[GPG90] M. Gyssens, J. Paredaens, and D. van der Gucht, A graph oriented object database model. ACM PODS, 1990.
[GUW02] H. Garcia-Molina, J. Ullman, and J. Widom, Database systems: The complete book. Prentice Hall, Upper
Saddle River, 2002.
[Fel02] C. Fehily, SQL: Visual quick start guide. 2002.
[HeR85] S. Heiler and A. Rosenthal, G-whiz, a visual interface for the functional model with recursion. Proc. VLDB
1985.
[Heu92] A. Heuer, Objektorientierte Datenbanken - Konzepte, Modelle, Systeme. Addion-Wesley, Bonn, 1992.
[JaW83] B.E. Jacobs and A.C. Walczak, A generalized query by example data manipulation language based on database
logic. IEEE TSE (1), Jan. 1983.
[KeE01] A. Kemper and A. Eikler, Datenbanksysteme. Oldenbourg Verlag, München, 2001.
[KiL89] M. Kifer and G. Lausen, F-Logic: A higher-order language for reasonig about objects, inheritance, and scheme.
ACM SIGMOD, 1989.
[Kun89] M. Kuntz, Pasta-3 gaphical query language. VLDB 89.
[LeN98] H.-J. Lenz and M. Neiling, Zur Summierbarkeit von Hufigkeiten im data cube (in multi-dimensionalen) Tabellen.
Diskussionsbeitrge des Fachbereichs Wirtschafts-Wissenschaft der Freien Universität Berlin, Nr. 1998/28, 1998.
60
B. Thalheim,
Preprint BTU - Informatik - I-08-2003
[LeT01] H.-J. Lenz and B. Thalheim, OLAP Databases and Aggregation Functions. SSDBM XIII, 2001, Fairfax.
[LevL99] M. Levene and G. Loizou: A Guided Tour of Relational Databases and Beyond. Springer, Berlin , 1999.
[Libk01] L. Libkin, Expressive Power of SQL. Proc. ICDT’2001, LNCS 1973, Springer, Berlin, 2001, 1-21.
[LoO91] Y. Lou and Z.M. Ozsoyoglu, Llo: An object-oriented deductive language with methods and method inheritance.
ACM SIGMOD 1991.
[LSS96] L.V.S. Lakshmanan, F. Sadri, and I.N. Subramanian, SchemaSQL - A language for interoperability in relational
multi-database systems. Proc. 22nd VLDB Conference, Mumbai, 1996, 239-250.
[MeS93] J. Melton and A.R. Simon, Understanding the new SQL: A complete guide. Morgan Kaufman, San Francisco,
1993.
[MeS02] J. Melton and A.R. Simon, SQL: 1999 - Understanding relational language components. Morgan Kaufman,
San Francisco, 2002.
[MGP98] N. Murray, C. Goble, and N.W. Paton, A framework for describing visual interfaces to databases. J. Visual
Languages and Computing, 9, 1998, 429-456.
[Mood01] D.L. Moody, Dealing with Complexity: A Practical Method for Representing Large Entity-Relationship Models. PhD., Dept. of Information Systems, University of Melbourne, 2001.
[Neu96] K. Neumann, Datenbanktechnik für Anwender. Hanser Verlag, Wien, 1996.
[OMO89] G. Ozsoyoglu, V. Matos, and Z.M. Ozsoyoglu, Query processing techniques in summary tables by example
database query language. ACM TODS, 14 (4), Dec. 1989.
[OOM87] G. Ozsoyoglu, Z.M. Ozsoyoglu, and V. Matos, Extending relational algebra and relational calculus with setvalued attributes and aggregation. ACM TODS, 12 (4), Dec. 1989.
[OzW93] G. Ozsoyoglu and H. Wang, Example-based graphical database query languages. IEEE Computer, July 1993.
[Sen95] A. Sengupta, Design and implementation of a database environment for the manipulation of structured documents. Preprint, University of Bangalore, 1995.
[SGB93] V.M. Sarathy, D. van Gucht, and A. Badia, Extended query graphs for declarative specification of set oriented
queries. Workshop on DBPL, 1993.
[Soc93] G.H. Sockut et. al., GRAQULA: A graphical query language for entity-relationship or relational databases. J.
Data and Knowledge Eng. 11, 1993.
[Thal00] B. Thalheim, Entity-Relationship Modeling - Fundamentals of Database Technology. Springer, Berlin, 2000.
[ThK01] B. Thalheim and T. Kobienia, Generating DB queries for web NL requests using schema information and DB
content. Proc. NLDB’2001, LNI 3, Springer, 2001, 205-209.
[VAO93] K. Vadaparty, Y.A. Aslandogan, and G. Ozsoyoglu, Towards a unified visual database access. Proc. ACM
SIGMOD Conf. 1993.
[Vis02] VisualSoft: http://www.visualsoftru.com
[WMS92] K.Y. Whang, A. Malhotra, G.H. Sockut, L. Burns, and K. Choi, Two-dimensional specification of universal
quantification in graphical database query languages. IEEE TSE, 18(3), March 1992.
[Zlo77] M.M. Zloof, Query-by-example: A database language. IBM Systems J. 21, 1977, 3.