03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
1
Datenbanken
• Einführung und Begriffsbildung
– Konventionelle DV-Systeme versus Datenbanksysteme
• Das Relationale Modell – Relationale Objekte
– Relationale Operationen und ihre Formulierung in SQL
• Die Datenbankzugriffssprache SQL
• Datenbankdesign – Normalformentheorie
– Datenmodellierung (ER-Diagramme)
• Die Datenbankbeschreibungssprache SQL
• Datenintegrität
– Strukturelle Integritätsbedingungen – Transaktionen
– Recovery
• Moderne Datenbankkonzepte
03.04.2002 Datenbanken 2
Einführung und Begriffsbildung
• Daten:
– Objekte der realen Welt (Kunden, Rechnungen, Mitarbeiter, Flüge, ...) – Ihre Beziehungen (Beispiele!)
• Daten
BANK
:– „ordentliche“ Aufbewahrung der Daten – Schutz vor Verlust und unberechtigtem Zugriff – Zugang ist jederzeit möglich!
• Daten müssen verwaltet werden Datenbankverwaltungssystem Definitionen
Ein Datenbankmanagementsystem (DBMS) stellt Programme zur Speicherung, Wiederauffindung und Schutz von Datensammlungen zur Verfügung
Datenbanksystem = Daten + DBMS
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
3
Einführung und Begriffsbildung
•Mehrbenutzerbetrieb
•An die Daten kommt man ausschließlich über das DBMS
Ein Datenbanksystem ist eine Sammlung von Daten, die untereinander in
Beziehung stehen und von einem eigenen Datenbankverwaltungssystem verwaltet werden
Beispiel: Kundendatei Daten
• Kundenstammdaten (Name, Adresse, Telefonnummer)
• Kontoführung (Kontonummer mit jeweiligen Bewegungen)
München 8000
Sommerstr. 80 Schmidt
14
Nürnberg 8500
Goethestr. 13 Meier
1
Ort PLZ Straße
Name KundenNr
Speicherung der Daten bei relationalen DB-Systemen in Tabellen
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
5
Beispiel: Kundendatei Funktionen der Datenbank
• Wiederauffinden der Daten: Das DB-System stellt Mittel zur Verfügung, um Anfragen zu beantworten:
o Die Adresse des Kunden mit der Nummer 14 o Alle Kunden des Unternehmens in Nürnberg
• Pflege der Daten
o Der Kunde Hans Werner, Münchnersr. 17, 6900 Heidelberg soll in die DB aufgenommen werden
o Der Kunde mit der Nummer 14 ist von der DB zu löschen
• Schutz der Daten: Das DB-System stellt Mittel zur Verfügung, um z.B. folgende Anforderungen sicherzustellen:
o Jede Filiale eines Unternehmens darf nur die Daten der eigenen Kunden pflegen
o Ein Systemausfall kann keinen Datenverlust zur Folge haben
03.04.2002 Datenbanken 6
Anforderungen an einer Datenbank Beispiel: Getränkehersteller
Datenbanktabelle
Attribute (Felder)
Satz, Tupel, record
Bestellung: bestimmte Biersorte vorhanden, Lagerbestand ausreichend, Ausliefern, Bestand reduzieren
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
7
Anforderungen an einer Datenbank Beispiel: Getränkehersteller (in SQL)
9 Erdinger Weißbier
7 Paulaner Weißbier
Anzahl Hersteller
Sorte
Bestand an Weißbier SELECT Sorte, Hersteller, Anzahl FROM Getränkelager
WHERE Sorte = Weißbier.
Neue Weißbiersorte hinzufügen INSERT
INTO Getränkelager
VALUES (43, ‘Dunkel‘, ‘Kaltenberg‘,
‘Träger‘, 6).
Bestand einer Sorte erhöhen UPDATE Getränkelager
SET Anzahl = Anzahl + 12 WHERE Nr = 11.
Eine Sorte aus dem Lager entfernen DELETE
FROM Getränkelager WHERE Nr = 47.
SQL (Structured Query Language) Erfinder: E.F. Codd (1970, IBM) Normierung: 1986, 1989, 1992 Nächster Schritt: SQL-3 (OO- Konzepte)
Anforderungen an einer Datenbank
Forderung: globale Verfügbarkeit der DatenKonventionell(Dateisystem): Jede Anwendung hat ihre Daten; Daten müssen ggf.
Mehrfach gehalten werden
Datenbanksystem: Daten und deren Beschreibungen werden global für alle Anwender und Anwendungen bereitgestellt
Daten 3 Programm 3
Daten 1 Programm 1
Daten 2 Programm 2
Programm 1 Programm 2
DBMS
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
9
Konventionelle DV-Systeme: Beispiel
Verwaltung der EDV-Geräte der Uniklinik
Die angelieferten Geräte werden in eine Access-Datenbank aufgenommen.
Hier wird dann bei der Auslieferung eingetragen, welche Abteilung das Gerät bekommt und wo es aufgestellt wird.
Die Finanzbuchhaltung bekommt nach der Auslieferung eine Mitteilung (auf Papier), wer das Gerät bezahlen muss und verwaltet die entsprechenden Informationen (inkl. Abschreibung...) in SAP.
Die meisten PCs (und manche Drucker) werden über TCP/IP angesprochen und brauchen daher einen eindeutigen Rechnernamen und eine IP-Adresse. Hierfür werden wiederum der Standort des Gerätes, eine Telefonnummer und noch ein Paar andere Daten erfasst - dieses Mal allerdings in einer ASCII-Datei.
... (die weiteren Stellen, an denen PC-Daten verwaltet werden, möchte ich nicht mehr einzeln aufzählen,
aber zwei gibt es mindestens noch).
03.04.2002 Datenbanken 10
Anforderungen an einer Datenbank
Forderung: Physische Datenunabhängigkeit Konventionell: Das Programm greift direkt auf die Datei zu. Änderung derDateiorganisation führt zu Programmanpassungen!
Datenbanksystem: Der Anwender kennt die physische Struktur der Daten nicht.
Es reicht ihm die logische Struktur der Daten zu kennen Adresse Name
KundenNr
Programm
SELECT Name FROM Kunde
WHERE KundenNr = 14
DBMS
Adresse Name
KundenNr
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
11
Anforderungen an einer Datenbank
Forderung: Logische DatenunabhängigkeitDatenbanksystem: Die zentrale Datenspeichrung darf nicht dazu führen, das jede Anwendung den gesamten Datenbestand „sieht“ Benutzer und Anwendungen können eigene „Sichten“ auf die Datenbank definieren.
Programm Leistungen Programm
Kundendatei
Kundendatei Leistungen
DBMS
Anforderungen an einer Datenbank
DatenkonsistenzDatenkonsistenz (logische Richtigkeit der Daten)
Problem: Bedingungen, die die Richtigkeit der Daten garantieren („Jedem Konventionell: Die Anwendungen müssen für die Einhaltung der Regel sorgen!
Datenbanksystem: programmunabhängige Definition von Konsistenzbedingungen
Programm 1 Programm 2
DBMS
Konsistenzbedingungen
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
13
Anforderungen an einer Datenbank
weitereForderungenProblem: Konkurrierende Zugriffe auf die gleichen Datenbestände (Beispiele!)
Konventionell: Die Anwendungen müssen das regeln!
Datenbanksystem: garantieren einen geregelten Mehrbenutzerbetrieb.
Jeder Benutzer muss der Eindruck haben, dass ihm alleine die Daten zur Verfügung stehen!
Mehrbenutzerbetrieb
Problem: Systeme und Anwendungen sind i.a. fehlerhaft
Konventionell: Die Datenbestände bleiben nach einem Systemfehler oft in einem inkonsistenten Zustand
Datenbanksystem: Transaktion: eine Veränderung der Daten geschieht entweder ganz oder gar nicht
Recovery: Protokollierung der Änderungen im laufenden Betrieb, um im Fehlerfall die Datenbank wieder in einen konsistenten Zustand zu versetzen und einen Datenverlust zu vermeiden!
Datensicherheit
03.04.2002 Datenbanken 14
Anforderungen an einer Datenbank
weitereForderungenProblem: Nicht alle Anwender dürfen auf den gesamten Datenbestand zugreifen
Konventionell: bei lokaler Datenhaltung existiert das Problem nicht... Stehen die Daten mehreren Applikationen zur Verfügung, so müssen die Applikationen für den Datenschutz sorgen. Das
Betriebssystem kann nur auf der Ebene von Dateien einen Zugriffsschutz gewähren!
Datenbanksystem: zentrale Vergabe von Zugriffsrechten auf Datenbeständen Datenschutz
Ist der Begriff „Datenschutz“, wie er in der Öffentlichkeit diskutiert wird, etwas ganz anderes?
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
15
Probleme bei der integrierten Datenhaltung
• Sicherung der Integrität bei Schreibzugriffen
– wirksame Sperrmechanismen bieten als auch ggf. dafür sorgen, dass – Datensätze bei Schreibvorgängen automatisch sperren
• Transaktionskonzept:
– Änderungen bei logisch zusammenhängenden Daten als „Block“ durchführen – Durch Datensatzsperren können deadlocks entstehen!
• Solche Situationen entstehen durch fehlerhafte Programme
• Sie müssen von DBMS erkannt und aufgelöst werden!
Beispiel
A sperrt Patient P1 B sperrt Fall F1
A versucht Fall F1 zu sperren (und wartet, da er das ja gerade nicht tun kann) B versucht Patient P1 zu sperren (und wartet ebenfalls, da er das ja gerade nicht tun
kann)
Probleme bei der integrierten Datenhaltung
Lesekonsistenz von Daten
Eine Firma hat eine Tabelle mit Kundendaten, in der aus Performancegründen auch zu jedem Kunden die Summe aller offenen Posten gespeichert wird (nicht ganz sauber, aber als Beispiel geeignet und in der Praxis gerne gemacht). Wird eine Rechnung bzw.
Zahlung gebucht, dann ändert die Software BF automatisch auch die Summe beim entsprechenden Kunden. Ausgangspunkt sei etwa die folgende Tabelle:
50 4
20 3
0 2
10 1
Offene Posten
Kunde Das Programm SUM will nun die Summe der offenen
Posten ermitteln
• SUM liest Kunde 1
• BF schreibt OP von Kunde 2 auf 10,-
• SUM liest Kunde 2 und 3
• BF schreibt OP von Kunde 3 auf 0,-
• SUM liest Kunde 4
Was kommt dabei heraus? Was soll dabei herauskommen?
Ein DBMS muss also (da es ja nicht auf das Ende der Suche hin hellsehen kann) sicherstellen, dass ein Lesevorgang stets die Daten liest, wie sie zu Beginn des
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
17
Das 3-Schichten-Modell nach ANSI-SPARC:
grundsätzlicher Aufbau eines Datenbanksystems
Externes Schema
Im externen Schema werden die verschiedenen Sichten auf die Datenbank definiert. Auf diese Weise ist es möglich für die verschiedenen Anwendungen unterschiedliche Ansichten auf die Datenstrukturen zu schaffen, die in der konzeptionellen Ebene definiert sind. Anders ausgedrückt:Das externe Schema vermittelt zwischen den Programmen, die von außen auf die Datenbank zugreifen und den Strukturen in denen die Daten gespeichert werden.
Konzeptionelles Schema: Beschreibung der Daten und deren Beziehungen;
insbesondere Konsistenzbedingungen
Internes Schema: hier sind die Einzelheiten der Implementierung angegeben:
•Die Aufteilung der Datenbank auf verschiedene Platten bzw. Platten-Systeme
•Plattenspiegelung / Striping (RAID-Level)
•Anlegen von Indizes um den Zugriff zu beschleunigen
•Konfiguration der Verwendung von Speicher zur Datenpufferung
03.04.2002 Datenbanken 18
Datenbankmodelle
Hierarchische Datenbanksysteme
Abteilung Einkauf
Allgemeiner Bedarf Technischer Einkauf
Bernhard. A.
Maier H.
Winter B.
Schmidt C.
Bernhard. A.
Die strikte Baumstruktur kann mehrfache Datenhaltung zur Folge haben Wie geht eine Operation: „Bernhard. A. Scheidet aus dem Unternehmen aus“?
Beispiele: IMS (IBM), MUMPS
Idee naheliegend: durch die Baumstruktur logarithmische Suchzeiten!
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
19
Datenbankmodelle Netzwerkdatenbanksysteme
Abteilung Einkauf
Allgemeiner Bedarf Technischer Einkauf
Bernhard. A.
Maier H.
Winter B.
Schmidt C.
Die Modelle werden schnell unübersichtlich!
Datenbankmodelle
Relationale Datenbanksysteme
• Objekte und deren Beziehungen werden ausschließlich mit Hilfe von Tabellen (Relationen) dargestellt.
• Relationale Modelle sind einfach zu verstehen, sehr flexibel und verfügen über ein ausgezeichnetes theoretisches Fundament.
• Wir werden uns im Rahmen dieser Vorlesung (fast) ausschließlich mit relationalen Datenbanksystemen befassen.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
21
Datenbankmodelle
Objektorientierte Datenbanksysteme
• Objekt ist der zentrale Begriff
• Komplexe Objekte können zusammenhängend abgelegt und verwaltet werden
• Vorteil: anschaulicher Aufbau des Datenmodells
• Derzeit erfahren relationale Datenbanksysteme (uns auch SQL) Erweiterungen in diese Richtung (Objektrelationale Datenbanksysteme)
03.04.2002 Datenbanken 22
Relationale Datenbanksysteme:
wie wird auf die Daten zugegriffen?
• Abfrage: ein Ausschnitt (eine oder mehrere Tabellen) der Datenbank wird ausgewählt und gelesen. Das Ergebnis ist wiederum eine Tabelle. Die Datenbank bleibt unverändert:
SELECT Spalten FROM Tabelle WHERE Bedingungen;
• Mutation: ein Ausschnitt (eine oder mehrere Tabellen) der Datenbank wird ausgewählt; anschließend wird eine ändernde Operation ausgeführt (Einfügen, ändern, löschen)
• Transaktion: ist eine konsistenzerhaltende Operation auf einer Datenbank. Sie besteht aus einer beliebigen Anzahl von Abfragen und Mutationen.
– Alle Operationen in einem DB-System müssen konsistenzerhaltend sein!
– Konsistenz muss auch nach einer Fehlersituation hergestellt werden können.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
23
Das relationale Modell
Zur Datenmodellierung bietet das relationale Modell
• Objekte (= Tabellen = Relationen), um Informationen und deren Beziehungen zu modellieren
• Operationen, um die Objekte zu manipulieren
• Integritätsbedingungen, die bei der Anwendung der Operationen eingehalten werden müssen.
Relationale Objekte
Datenbanktabelle (Relation)
Attribute (Felder, Spalten)
Satz, Tupel, record Wertebereich
Kardinalität
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
25
Relationale Objekte: Beispiel
CREATE TABLE Patient (
PatID INTEGER NOT NULL PRIMERY KEY,
Name CHAR(20) NOT NULL,
Vorname CHAR(15),
Geschlect CHAR(1) NOT NULL “Wert darf nicht unbekannt sein );
Die Relation Patient hat die Attribute PatID, Name, Vorname, Geschlecht, das Attribut PatID ist der Primärschlüssel der Relation, der Grad der Relation beträgt 4, Name und Geschlecht dürfen beim Anlegen eines Satzes nicht unbekannt sein.
03.04.2002 Datenbanken 26
Relation: Beispiel
Ist das alles wirklich so einfach??
• Was passiert, sollte Herr/Frau Meier heiraten und einen anderen Namen annehmen wollen / müssen?
• Was passiert, sollte sich die Firma entschließen, keinen Staubsauger mehr zu produzieren?
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
27
Relation: Definition
Definition
Eine Relation ist eine Tabelle mit folgenden Eigenschaften:
• Jede Zeile einer Tabelle ist in der Tabelle nur einmal vorhanden.
• Die Werte der Attribute sind elementare („atomare“) Objekte – Ein Attribut Adresse mit PLZ, Straße und Ort wäre unzulässig
• Die Reihenfolge der Attribute spielt keine Rolle. Sie werden über ihre Bezeichnungen angesprochen.
SELECT Hersteller FROM Getränkelager WHERE Nr = 8.
• Die Reihenfolge der Zeilen einer Tabelle spielt keine Rolle. Sie werden über die Werte ihrer Attribute angesprochen.
SELECT * FROM Getränkelager WHERE Sorte = ‚Hell‘.
Normalisierte Relation
Relation: Definition
• Das Hinzufügen einer neuen Zeile ist sehr einfach. Wir haben gar keine Möglichkeit, uns einen komplexen Algorithmus zu überlegen!
• Das Hinzufügen einer Spalte ist ebenfalls einfach: es ist egal, an welcher Stelle das Attribut platziert wird.
• Die Atomarität der Attribute macht uns Probleme...
Reihenfolge der Attribute und der Zeilen spielt keine Rolle: wichtige Voraussetzung, um die Unabhängigkeit zwischen der logischen und der physischen Anordnung der Daten zu erreichen!
Alle Zeilen einer Relation sind gleich lang, hier nicht...
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
29
Relation: Eigenschaften
• Schlüsselkandidat: ist ein Attribut, oder eine Attributkombination, die einen Satz der Tabelle eindeutig identifizieren. Die Schlüsselkombination muss „minimal“ sein:
durch das Wegnehmen eines Attributs geht die Schlüsseleigenschaft verloren.
Nr
Sorte + Hersteller + Typ
• Primärschlüssel: ein ausgezeichneter Schlüsselkandidat. Seine Wahl ist dem Programmierer überlassen. Folgende Eigenschaften sind damit verbunden:
– Der Primärschlüssel darf keine NULL-Werte annehmen
– Über ihn ist der direkte Zugriff auf genau eine Zeile der Tabelle möglich – Mit Hilfe von Primärschlüsseln werden Tabellen miteinander verknüpft.
– Kann man immer einen einfachen Primärschlüssel haben?
• Sekundärschlüssel: aus Performancegründen kann es notwendig sein, für weitere Attribute bzw. Attributkombinationen Zugriffspfade einzurichten, um schnell an die gespeicherte Information zu kommen
Patientenrelation: Zugriffspfade über Name, GebDatum
03.04.2002 Datenbanken 30
Relationen: Beispiel
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
31
Relation: weitere Differenzierung
• Basisrelationen: real existierende Tabellen, die dauerhaft abgelegt sind und Daten enthalten
• Sichten (Views): „virtuelle“ Relationen; sie speichern selbst keine Daten; ihre Daten werden „bei Bedarf“ von den Basisrelationen abgeleitet.
• Abfrageergebnisse: .... Sind immer Tabellen!
• Temporäre Tabellen: existieren nur vorübergehend bis zum Ende einer Transaktion.
Relationale Datenbank: Definition
Eine relationale Datenbank ist eine Datenbank, die der Benutzer als Ansammlung von normalisierten Relationen erkennt.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
33
Relationales Modell: Integritätsregeln
• Physische Integrität
– Korrekte Speicherung der Daten durch das Datenbanksystem – Aufgabe des DBMS
• Ablaufintegrität
– Korrektheit der ablaufenden Programme (Endlosschleifen, Mehrbenutzerbetrieb)
– Aufgabe des Programmierers
• Zugriffsberechtigung
– Korrekte Vergabe der Zugriffsrechte – Aufgabe des DB-Administrators
• Semantische Integrität
– Korrekte Abbildung der realen Welt auf den Rechner – Der Punkt ist schwierig...
– Datenbankhersteller muss geeignete Sprachelemente anbieten (Prüfung der Eingaben bereits auf Maskenebene, Trigger-Konzept)
03.04.2002 Datenbanken 34
Ablaufintegrität
• Keine zwei Tupel einer Relation können den selben Primärschlüssel haben
• Primärschlüssel dürfen keine undefinierten Werte (NULL) annehmen
• Diese Regel gilt nur für den Primärschlüssel, nicht für alle Schlüsselkandidaten
Entitätsregel
Frage: muss denn eine Relation überhaupt einen Primärschlüssel haben?
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
35
Entitätsregel : Beispiel
Keine 2. Zeile mit Nr = 1 Keine Zeile mit Nr = NULL Eine Zeile mit undefinierten Werten
In (Sorte, Hersteller, Typ) ist grundsätzlich erlaubt!
Referenzielle Integrität
• Fremdschlüssel: ein Attribut, oder Attributkombination, das folgende Eigenschaften hat:
– Alle Attribute des zusammengesetzten Schlüssels haben einen definierten Wert, oder keins
– Es gibt eine Basisrelation mit diesem Schlüssel als Primärschlüssel, so dass jeder definierter Wert des Fremdschlüssels auch in der Basisrelation vorkommt.
• Über Fremdschlüssel werden die Tabellen einer Datenbank miteinander verknüpft.
• Die Relation mit dem Primärschlüssel heißt Vaterrelation, die mit dem Fremdschlüssel abhängige Relation
• Referentielle Integrität: Es gibt keinen definierten Wert eines Fremdschlüssels, der in dem dazugehörigen Primärschlüssel nicht existiert.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
37
Referenzielle Integrität:
Beispiel
Fremdschlüssel
Hat keinen Vorgesetzten
03.04.2002 Datenbanken 38
Referenzielle Integrität:
Beispiel
Fremdschlüssel
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
39
Referenzielle Integrität:
Beispiel
Damit kann man noch nicht viel anfangen...
Referenzielle Integrität:
Beispiel
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
41
Referenzielle Integrität:
Beispiel
03.04.2002 Datenbanken 42
Referenzielle Integrität:
Beispiel
• Frau Köster verlässt das Unternehmen. Man kann nicht einfach die entsprechende Zeile löschen!
• Das System hat folgende Möglichkeiten (um weiterhin die Konsistenz zu wahren):
– Zurückweisen der Operation delete
– Parallel löschen aller darauf verweisenden Tupel in abhängigen Relationen – Setzen aller Tupel in abhängigen Relationen auf NULL, die darauf verweisen
• In SQL-2 (Löschen bzw. Ändern)
ON DELETE NO ACTION ON UPDATE NO ACTION
ON DELETE CASCADE ON UPDATE NO ACTION
ON DELETE SET NULL ON UPDATE SET NULL
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
43
Integritätsbedingungen:
Zusammenfassung
Integritätsbedingungen sind Teil des konzeptionellen Schemas. Sie werden hier formuliert und deren Einhaltung und vom DBMS automatisch erzwungen
Welche Bedingungen kennen wir insgesamt?
•Jede Zeile einer Relation existiert nur einmal (per Definition, Entitätsregel)
•Der Primärschlüssel darf keine NULL Werte annehmen (per Definition, Entitätsregel)
CREATE TABLE Projektzugehöigkeit ( Mnr INTEGER NOT NULL, PNr INTEGER NOT NULL, PRIMARY KEY (MNr, PNr), ...
)
Integritätsbedingungen:
Zusammenfassung
Die Attribute einer Relation müssen (per Definition)Werte aus ihren Wertebereichen annehmen:
CREATE TABLE Artikel (
ArtNummer SMALLINT CHECK ( ArtNummer >= 0 ), ... )
¾Damit kann man sicherstellen, dass die Werte „lokal“ sinnvoll sind.
¾Damit kann man nicht sicherstellen, dass die Angaben immer sinnvoll sind!
Benutzerdefinierte Integritätsregeln: Beziehungen zwischen Datenbankelementen
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
45
Integritätsbedingungen:
Zusammenfassung
Referentielle Integrität: jeder Wert des Fremdschlüssels muss auch als Wert im zugehörigen Primärschlüssel vorkommen.
CREATE TABLE Mitarbeiter (
MNr INTEGER NOT NULL PRIMARY KEY, ....
Abteilung INTEGER FOREIGN KEY (AbtNr) REFERENCES (Abteilungen) ...
)
Löschvorgänge
¾... werden zurückgewiesen: ein Tupel kann nicht gelöscht werden, solange es referenziert wird
¾... führen zu „Kettenreaktionen“: auch alle Tupel werden gelöscht, die auf diesen Wert referenzieren!
¾... Führen zu NULL-Werten bei Fremdschlüsseln.
03.04.2002 Datenbanken 46
Das relationale Modell
Zur Datenmodellierung bietet das relationale Modell
• Objekte (= Tabellen = Relationen), um Informationen und deren Beziehungen zu modellieren
• Operationen, um die Objekte zu manipulieren
• Integritätsbedingungen, die bei der Anwendung der Operationen eingehalten werden müssen.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
47
Relationale Operationen und ihre Formulierung in SQL
Relationale Operationen werden auf einer Reihe von Relationen angewendet. Das Ergebnis ist stets eine Tabelle.
Division Verbund (join)
Kartesisches Produkt Produkt
Mengendifferenz Differenz
Mengendurchschnitt Durchschnitt
Mengenvereinigung Vereinigung
Spalten auswählen Projektion
Zeilen auswählen Restriktion
Bedeutung Name
Relationale Operationen Selektion (Restriktion)
Selektion (Restriktion)
Syntax:
REST (Relation, Bedingung) Semantik:
Sie wählt einige Zeilen der Tabelle aus. Das Ergebnis ist eine Tabelle, die alle Spalten der ursprünglichen Tabelle enthält und genau die selektierten Zeilen. Die Bedingung bezieht sich auf die Werte einiger Attribute der Relation.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
49
Relationale Operationen Selektion (Restriktion)
3-234-12985-0 H. Braun
Compiler 3
3-600-12345-2 C. H. Chen
Entity-Relationship-Model 2
2-509-50342-7 N. Wirth
Die Programmiersprache PASCAL
1
ISBN Autor
Titel Nr
Bücher
2-509-50342-7 N. Wirth
Die Programmiersprache PASCAL
1
ISBN Autor
Titel Nr
REST (Bücher, ISBN = 2-509-50342-7).
SELECT * FROM Bücher
WHERE ISBN = ‘2-509-50342-7‘.
03.04.2002 Datenbanken 50
Relationale Operationen Projektion
Selektion (Projektion)
Syntax:
PROJ (Relation, Attribut1, ..., Attributn) Semantik:
Sie wählt einige Spalten der Tabelle aus. Das Ergebnis ist eine Tabelle, die alle Zeilen der ursprünglichen Tabelle enthält und genau die selektierten Spalten.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
51
Relationale Operationen Projektion
3-234-12985-0 H. Braun
Compiler 3
3-600-12345-2 C. H. Chen
Entity-Relationship-Model 2
2-509-50342-7 N. Wirth
Die Programmiersprache PASCAL
1
ISBN Autor
Titel Nr
Bücher
PROJ (Bücher, Autor).
SELECT Autor FROM Bücher.
H. Braun C. H. Chen N. Wirth
Autor
Relationale Operationen Vereinigung (union)
Selektion (Vereinigung)
Syntax:
UNION (Relation1, Relation2) Semantik:
Die Ergebnisrelation enthält die Zeilen beider Tabellen. Gleiche Tupel werden nur einmal übernommen. Diese Operation ist nur dann zulässig, wenn die Relationen „vereinigungskompatibel“ sind:
¾gleiche Anzahl von Spalten
¾die Wertebereiche stimmen überein.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
53
Relationale Operationen Vereinigung
H. Braun C. H. Chen N. Wirth
Autor
3-234-12985-0 Compiler
3
3-600-12345-2 Entity-Relationship-Model
2
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
Bücher-Informatik
A. Einstein N. Wirth
Autor
3-600-12345-2 Informatik für Physiker
12
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
Bücher-Physik
3-600-12345-2 A. Einstein
Informatik für Physiker 12
H. Braun C. H. Chen N. Wirth
Autor
3-234-12985-0 Compiler
3
3-600-12345-2 Entity-Relationship-Model
2
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
UNION(Bücher-Informatik, Bücher-Physik)
03.04.2002 Datenbanken 54
Relationale Operationen Vereinigung: SQL
SELECT *
FROM Bücher-Informatik UNION
SELECT *
FROM Bücher-Physik.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
55
Relationale Operationen Durchschnitt
Selektion (Durchschnitt)
Syntax:
INTERSECTION (Relation1, Relation2) Semantik:
Ermittelt die gemeinsamen Tupel der Relation.
Relationale Operationen Durchschnitt
H. Braun C. H. Chen N. Wirth
Autor
3-234-12985-0 Compiler
3
3-600-12345-2 Entity-Relationship-Model
2
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
Bücher-Informatik
A. Einstein N. Wirth
Autor
3-600-12345-2 Informatik für Physiker
12
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
Bücher-Physik
Autor ISBN
Titel Nr
INTERSECTION (Bücher-Informatik, Bücher-Physik)
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
57
Relationale Operationen Durchschnitt
SELECT *
FROM Bücher-Informatik Where Nr IN (
SELECT Nr
FROM Bücher-Physik )
Die Menge der Elemente von Bücher-Informatik, die auch in der Menge der Physik-Bücher vorkommen
03.04.2002 Datenbanken 58
Relationale Operationen Differenz
Selektion (Differenz)
Syntax:
DIFFERENCE (Relation1, Relation2) Semantik:
Das Ergebnis ist eine Relation, die die Tupel der ersten enthält, die in der zweiten nicht vorkommen.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
59
Relationale Operationen Differenz
H. Braun C. H. Chen N. Wirth
Autor
3-234-12985-0 Compiler
3
3-600-12345-2 Entity-Relationship-Model
2
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
Bücher-Informatik
A. Einstein N. Wirth
Autor
3-600-12345-2 Informatik für Physiker
12
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
Bücher-Physik
3-600-12345-2 C. H. Chen
Entity-Relationship-Model 2
3-234-12985-0 H. Braun
Compiler 3
Autor ISBN
Titel Nr
DIFFERENCE (Bücher-Informatik, Bücher-Physik)
Relationale Operationen Differenz
SELECT *
FROM Bücher-Informatik Where Nr NOT IN ( SELECT Nr
FROM Bücher-Physik )
Die Menge der Elemente von Bücher-Informatik, die in der Menge der Physik-Bücher nicht vorkommen
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
61
Relationale Operationen Produkt
Selektion (Produkt)
Syntax:
PRODUCT (Relation1, Relation2) Semantik:
Diese Operation bildet das kartesische Produkt der beiden Relationen:
Jede Zeile der ersten Relation wird mit allen Zeilen der zweiten Relation kombiniert.
Das Produkt von zwei Relationen mit der Kardinalität von m und n ist m x n!
Das Produkt wird in der „reinen“ Form in der Praxis kaum benutzt...
03.04.2002 Datenbanken 62
Relationale Operationen Produkt
SELECT *
FROM Mitarbeiter, Abteilung
Fragestellung: gesucht sind alle Mitarbeiter des Unternehmens (Personalnummer, Name, Alter), die älter als 30 Jahre sind und in München arbeiten
SELECT P.Mnr, P.Name, P.Alter FROM Mitarbeiter P, Abteilung A
WHERE P.Abteilung = A.AbtNr AND
P.Alter > 30 AND
A.Ort = ‚München‘;
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
63
Relationale Operationen Verbund (join)
Selektion (Verbund)
Syntax:
JOIN (Relation1, rel(attr1, attr2), Relation2) Semantik:
Die Verbundoperation liefert als Ergebnis die Tupel aus dem Produkt der beiden Relationen, die in der Beziehung rel stehen.
Die Attribute, über die die Verbundoperation durchgeführt wird (join-Attribute), müssen keine Schlüsselkandidaten sein
Regeln
Die join-Attribute müssen nicht den gleichen Namen haben Die join-Attribute müssen die selben Wertebereiche haben AUTO-join: JOIN (Relation1, rel(attr1, attr2), Relation1)
Relationale Operationen Verbund (join)
H. Braun C. H. Chen N. Wirth
Autor
3-234-12985-0 Compiler
3
3-600-12345-2 Entity-Relationship-Model
2
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr Bücher
3-234-12985-0 3-600-12345-2 2-509-50342-7 ISBN
Addison-Wesley Hanser Hanser
Verlag Buchverlage
JOIN (Bücher, (Bücher.ISBN = Buchverlage.ISBN), Buchverlage)
Fragestellung: Alle Bücher mit Informationen über Titel, Autor, ISBN-Nummer und Verlag
Hanser Verlag N. Wirth
Autor
2-509-50342-7 Die Programmiersprache PASCAL
1
ISBN Titel
Nr
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
65
Relationale Operationen Verbund (join)
SELECT B.Nr, B.Titel, B.Autor, V.Verlag, B.ISBN FROM Bücher B, Buchverlage V
WHERE B.ISBN = V.ISBN.
In der allgemeinen Form können weitere Beziehungen verwendet werden:
=, <>, <=, >=
03.04.2002 Datenbanken 66
Relationale Operationen Verbund (join)
Abt1 Ulm
Mozer Sibylle 2855
Abt2 München
Kaufmann Brigitte 0258
Abt2 Augsburg
Meier Rainer 0903
Abt2 Augsburg
Probst Andreas 2934
Stuttgart Landshut München Ort
Abt1 Müller Gabrielle
1831
Abt3 Huber Petra
1010
Abt3 Keller Hans
2534
Abteilung Name
MNr
Abteilung
München Freigabe
Abt3
München Diagnose
Abt2
Stuttgart Beratung
Abt1
Ort AbtBez
AbtNr
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
67
Relationale Operationen Verbund (join)
Fragestellung: alle Mitarbeiter, die nicht am Standort ihrer Abteilung wohnen
SELECT M.MNr, M.Name, M.Ort, M.Abteilung FROM Mitarbeiter M, Abteilung A
WHERE M.Abteilung = A.AbtNr AND M.Ort <> A. Ort.
Abt1 Ulm
Mozer Sibylle 2855
Abt2 Augsburg
Meier Rainer 0903
Abt2 Augsburg
Probst Andreas 2934
Landshut Ort
Abt3 Huber Petra
1010
Abteilung Name
MNr
Relationale Operationen Division
Selektion (Division)
Syntax:
DIVIDE (R1, R1.Attr = R2.Attr, R2) Semantik:
Das Ergebnis ist eine Relation, die alle Attribute von R1 enthält, die in R2 nicht vorkommen und die im Attribut Attr den selben Wert haben.
P2 L2
P1 L2
P3 L1
P2 L1
P1 L1
P L
P1 P DIVIDE
L2 L1 L
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
69
Wie geht man vor bei der Formalisierung einer Fragestellung?
Schritt1: kartesisches Produkt bilden über alle Tabellen, die in der Fragestellung vorkommen
T1 := PRODUCT (R1, R2) T2 := PRODUCT (T1, R3) ...
Schritt2: Restriktionen bilden: durch die Angabe von Bedingungen die Ergebnismenge einschränken
T3 := REST (T2, Bedingung) ....
Schritt3: Projektion bilden: Auswahl von Spalten aus der zuletzt entstandenen temporären Tabelle
Ergebnis := PROJ (T3, Spaltenliste)
03.04.2002 Datenbanken 70
Wie geht man vor bei der Formalisierung einer Fragestellung?
Fragestellung: gesucht sind alle Mitarbeiter des Unternehmens (Personalnummer, Name, Alter), die älter als 30 Jahre sind und in München arbeiten
T1 := PRODUCT (Mitarbeiter, Abteilung) T2 := REST (T1, Bedingung), wobei
Bedingung := ...
Ergebnis := PROJ (T2, MNr, Name, alter)
PROJ ( REST (PRODUCT (Mitarbeiter, Abteilung), Bedingung ), NNr, Name, Alter )
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
71
Wie geht man vor bei der Formalisierung einer Fragestellung?
PRODUCT PROJ (MNr, Name, Alter)
REST (P.Alter > 30 AND A.Ort = ‚München‘ )
Auswertung des Baums von unten nach oben!
Abteilung Mitarbeiter
Eine kleine Optimierung...
Hat das Unternehmen 100 Abteilungen und 10.000 Mitarbeiter, so entstehen gleich zu Beginn 1.000.000 Datensätze!
T1 := JOIN (Mitarbeiter, Abteilung = AbtNr, Abteilung) T2 := REST (T1, Ort = ‚München‘),
T3 := REST (T2, Alter > 30) E := PROJ (T3, MNr, Name, alter)
T1 erzeugt lediglich 10.000 Datensätze!
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
73
Übersicht
03.04.2002 Datenbanken 74
Beispiel natürlicher Verbund
JOIN (Personal, Personal.Persnr = Auftrag.Persnr, Auftrag)
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
75
Beispiel:
outer-join
Outer-join: Die Tupel der einen Relation bleiben in der Ergebnissliste, auch wenn sie Keinen „passenden“ Eintrag in der 2. Tabelle haben. Sie erhalten dann für die Attribute der 2. Tabelle NULL-Werte
JOIN (Personal, Personal.Persnr = Auftrag.PersNr, Auftrag)
Die Datenbankzugriffssprache SQL
• SQL =
Structured Query Language• Ursprung: SEQUEL aus dem Jahr 1974
• erste Implementierung IBM 1977, ORACLE etwa zur gleichen Zeit
• erste relationalen Datenbank: ORACLE 1979 (mit SQL)
• Es gibt praktisch keinen großen Hersteller mehr mit einer relationalen Datenbank auf den Markt, die SQL nicht in der einen oder anderen Form beherrscht.
• Die Sprache ist normiert. Die Normbeschreibung umfasst über 600 Seiten!
• Derzeit ist die SQL-3-Norm in Entwicklung. Sie enthält objektrelationale Erweiterungen.
(Erfolgs-)Geschichte
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
77
Die Datenbankzugriffssprache SQL
• SQL ist nicht prozedural. Der Zugriff auf die Daten erfolgt ausschließlich über deren Attribute (Physische Datenunabhängigkeit).
• SQL verarbeitet grundsätzlich eine Menge von Tupeln d.h. beispielsweise eine Abfrage liefert immer eine gewisse Menge von Tupeln als Ergebnis. Ein gezieltes Springen zwischen Datensätzen ist in SQL selbst nicht möglich. Dafür benötigt man eine Programmiersprache, die SQL implementiert hat.
• SQL ist größtenteils formatfrei und reiner ASCII-Text d.h. SQL-Befehle kann man mit jedem Texteditor erstellen.
• Groß/Kleinschreibung wird nicht unterschieden
• SQL-Statements werden immer durch einen Strichpunkt abgeschlossen (das ist im SAP-System nicht so, ist aber auch nicht so wichtig...)
• Strings in SQL werden durch einfache Hochkommas eingeschlossen.
Technische Eigenschaften
03.04.2002 Datenbanken 78
Der Abfragebefehl SELECT Die allgemeine Syntax
• Kann man alles mit einem Befehl machen?
• Wie man aus der folgenden Zeichnung erkennen kann, ist der Befehl mit allen seinen Variationen recht umfangreich...
• Wir wollen hier „nur„ die wichtigsten Sachen machen...
• In den Übungen werden wir zugleich eine Programmiersprache (ABAP/4) kennen lernen, die
– Nur eine Teilmenge der Sprachelemente realisiert
– Zusätzliche Möglichkeiten für die Verarbeitung der Daten vorsieht
• Das müssen alle so, oder so ähnlich machen (siehe PL/SQL von ORACLE).
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
79
Der SELECT-Befehl ...
Aus den Originalunterlagen von ORACLE, etwas vereinfacht
Wir müssen lernen, solche Syntaxdiagramme zu lesen!
Einschub:
Wie lese ich ein Syntaxdiagramm?
• Von links nach rechts und von oben nach unten
• Bei Verzweigungen muss man sich für eine Richtung entscheiden
• Wiederholungen eines Blockes sind durch einen Pfeil gekennzeichnet
• Kursiv gesetzte Begriffe stehen für weitere (meist nicht ganz einfache) SQL-Befehle oder Ausdrücke
• Optionale Teile stehen in eckigen Klammern []
• Reservierte Schlüsselwörter werden groß geschrieben
• Nur nicht verzweifeln - sieht schlimmer aus, als es ist
• Wir werden uns freiwillig einschränken.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
81
Beispiele
Alle Mitarbeiter
SELECT Name, Ort FROM Personal;
In dieser „reinen“ Form wird SQL selten benutzt. Die Daten müssen i.d.R. in der Sprache Weiterverarbeitet werden, in der SQL eingebettet ist!
SELECT * FROM Personal;
Großgeschriebene Wörter sind reservierte Schlüsselwörter einer Sprache. Sie dürfen als Variablen nicht verwendet werden!
Groß- und Kleinschreibung spielt keine Rolle!
SELECT Name Ort FROM Personal.
03.04.2002 Datenbanken 82
Der Aufbau des SELECT-Befehls:
Hauptteil
SELECT [ DISTINCT] Spaltenauswahlliste
FROM Tabellenliste
[WHERE Bedingung]
• Reservierte Schlüsselwörter werden groß geschrieben
• Alternativen werden mit einem senkrechten Strich | gekennzeichnet
• Optionale Teile stehen in eckigen Klammern []
• Stehen mehrere Optionen zur Auswahl, von denen eine auf jeden Fall zutrifft, so muss der Ausdruck in geschweiften Klammern stehen {A | B | C}
• Wiederholungen werden mit * gekennzeichnet {A | B | C}*
• Nach der obigen Notation treten folgende Klauseln in einem SELECT-Befehl zwingend auf:
– SELECT-Klausel – FROM-Klausel
• Tabellennamen, Attributnamen und weitere Bezeichner:
– Beginnen stets mit einem Buchstaben gefolgt von – Weiteren Buchstaben, Ziffern und Unterstrichzeichen _
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
83
Logische Abarbeitungsreihenfolge in einem SELECT-Befehl
Die SELECT- und die FROM-Klausel
• SELECT entspricht einer Projektion
• FROM Produkt mehrerer Relationen
• FROM Tabelle [, Tabelle]*
– Es muss mindestens eine Tabelle angegeben werden
– Werden mehrere Tabellen angegeben, so sind sie durch ein Komma zu trennen
• Wenn dieselbe Tabelle genommen werden muss (siehe Vorgesetztenrelation), oder Tabellen gleichnamige Attribute haben, muss ein alias verwendet werden:
– FROM Tabelle [Aliasname] [, Tabelle [Aliasname] ]*
FROM Mitarbeiter M, Mitarbeiter V FROM Mitarbeiter, Produkt
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
85
Die SELECT- und die FROM-Klausel
SELECT *
FROM Mitarbeiter ; SELECT Name, Abteilung FROM Mitarbeiter ; SELECT [DISTINCT] Spaltenauswahlliste
SELECT Mitarbeiter.*, Abteilung.Ort FROM Mitarbeiter, Abteilung ;
• Sind mehrere Tabellen beteiligt, so muss man für die Eindeutigkeit der Attribute sorgen:
– Tabelle.* alle Attribute werden mit dem Tabellennamen qualifiziert – Tab.Attr Das Attribut Attr wird mit dem Tabellennamen qualifiziert
• Die Qualifizierung der Attribute ist nur dann zwingend erforderlich, wenn
Mehrdeutigkeiten beseitigt werden müssen, sollte aber immer verwendet werden!!
03.04.2002 Datenbanken 86
SELECT-Anweisung Beispiele
Liste der Aufträge (Autragsnummer, Datum) mit Angabe des Mitarbeiters (Name, Wohnort), der beteiligt war
SELECT AuftrNr, Datum, Name, Ort FROM Auftrag, Personal;
Liste der Flugverbindungen mit Angabe des Abflug- und Zielortes sowie der Abflugzeit; Was tun, wenn mehrere Fluggesellschaften zur selben Zeit die gleiche Strecke fliegen?
SELECT cityfrom, cityto, deptime FROM spfli;
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
87
Die SELECT- und die FROM-Klausel
Lediglich beim Attribut Persnr besteht die Notwendigkeit, über die Qualifizierung die Eindeutigkeit herzustellen
Die folgenden Anweisungen sind äquivalent
Die SELECT- und die FROM-Klausel
SELECT [ DISTINCT] Spaltenauswahlliste
• Durch die Projektion auf bestimmte Spalten können Zeilen der Ergebnistabelle mehrfach vorkommen
• Die Option DISTINCT verhindert dies.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
89
Die SELECT- und die FROM-Klausel
3-156-65789-1 N. Wirth
Die Programmiersprache Modula-2
4
3-234-12985-0 H. Braun
Compiler 3
3-600-12345-2 C. H. Chen
Entity-Relationship-Model 2
2-509-50342-7 N. Wirth
Die Programmiersprache PASCAL
1
ISBN Autor
Titel Nr
Bücher
N. Wirth H. Braun C. H. Chen N. Wirth
Autor
SELECT DISTINCT Autor FROM Bücher.
H. Braun C. H. Chen N. Wirth
Autor SELECT Autor
FROM Bücher.
03.04.2002 Datenbanken 90
SELECT-Anweisung Beispiele
Liste der Wohnorte der Mitarbeiter SELECT Ort
FROM Personal;
Liste der Wohnorte der Mitarbeiter ohne Mehrfachnennungen SELECT DISTINCT Ort FROM Personal;
Passau Kelheim Landshut Nürnberg Straubing Regensburg
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
91
Die SELECT- und die FROM-Klausel:
abgeleitete Spalten
Ausdrücke sind als Spalten auch zulässig. Sie beziehen sich auf die Attributwerte und können über einen Aliasnamen festgehalten werden
SELECT Name, 12 * Gehalt AS Jahresgehalt FROM Personal
Das Ergebnis ist eine zweispaltige Tabelle!
Die SELECT- und die FROM-Klausel:
Ausdrücke zur Bildung abgeleiteter Spalten
Praktisch herstellerabhängig!
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
93
Die SELECT- und die FROM-Klausel:
Ausdrücke zur Bildung abgeleiteter Spalten
03.04.2002 Datenbanken 94
Die SELECT- und die FROM-Klausel:
Ausdrücke zur Bildung abgeleiteter Spalten Statistikfunktionen
Wir werden ohne Beispiele nicht auskommen...
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
95
SELECT-Anweisung Beispiele
SELECT Name, 12 * Gehalt AS Jahresgehalt FROM Personal;
Eine Tabelle mit den Jahresgehältern aller Mitarbeiter
SELECT Name, 12 * Gehalt + 1000 * (6 – Beurteilung) FROM Personal;
Eine Tabelle mit den Jahresgehältern aller Mitarbeiter. Es gibt eine Leistungszulage von maximal 5.000 DM, wenn die
Beurteilung eine „1“ ist. Für jede weitere Notenstufe gibt es einen Abschlag von 1.000 DM)
SELECT-Anweisung:
Aggregatfunktionen
• Aggregatfunktionen
– Liefern immer nur eine Zeile!
– Beziehen sich auf einer Spalte, oder einen Spaltenausdruck
– Sie stehen alleine oder mit anderen Aggregatfunktionen zusammen in der SELECT- Klausel
– Die beziehen sich immer auf das in der FROM-Klausel ermittelte Produkt
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
97
SELECT-Anweisung:
Aggregatfunktionen
SELECT SUM (12 * Gehalt + 1000 * (6 – Beurteilung)) AS Personalkosten FROM Personal;
Personalausgaben des Unternehmens insgesamt
SELECT count(*) AS Mitarbeiterzahl FROM Personal;
Die Anzahl der Mitarbeiter des Unternehmens
SELECT count(DISTINCT Ort) FROM Personal;
Die Anzahl der (unterschiedlichen) Wohnorte der Mitarbeiter
Count kann ohne Angabe eines Spaltenausdrucks (mit einem „*“) oder mit Angabe eines Spaltenausdrucks verwendet werden
03.04.2002 Datenbanken 98
SELECT-Anweisung:
Aggregatfunktionen
Die folgenden Anweisungen liefern unterschiedliche Ergebnisse SELECT COUNT(*) FROM Personal;
SELECT COUNT(Vorgesetzt) FROM Personal;
SELECT COUNT(DISTINCT Vorgesetzt) FROM Personal;
9 7 2
Die NULL-Werte werden nicht berücksichtigt!
Interpretieren Sie diese Zahlen!
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
99
SELECT-Anweisung:
Aggregatfunktionen
SELECT MIN ( Gehalt ) AS MIN_Gehalt FROM Personal;
Das kleinste Gehalt im Unternehmen
SELECT MAX ( Gehalt ) AS MAX_Gehalt FROM Personal;
Das größte Gehalt im Unternehmen
SELECT AVG ( Gehalt ) AS Mittel_Gehalt FROM Personal;
Der mittlere Verdienst im Unternehmen
SELECT-Anweisung:
Aggregatfunktionen
SELECT AVG ( Gehalt ) AS Mittel_Gehalt FROM Personal;
Der mittlere Verdienst im Unternehmen
SELECT COUNT(DISTINCT Vorgesetzt) FROM Personal;
Die Anzahl der Vorgesetzten im Unternehmen
SELECT MAX ( Datum ) FROM Auftrag;
Wann wurde zuletzt ein Auftrag geholt?
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
101
SELECT-Anweisung:
Aggregatfunktionen
SELECT COUNT ( DISTINCT PersNr ) FROM Auftrag;
Wie viele Mitarbeiter haben Aufträge bekommen?
Was liefert die Anweisung:
SELECT COUNT ( AVG (Gehalt) ) FROM Personal;
SELECT MAX( Distance ), COUNT(*) FROM SPFLI;
Die längste Flugverbindung und die Anzahl der Flugverbindungen
03.04.2002 Datenbanken 102
SELECT-Anweisung:
Aggregatfunktionen
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
103
SELECT-Anweisung:
Aggregatfunktionen
REPORT Z1D99_SELECT line-count 65 line-size 80 no standard page heading.
tables: sflight, spfli, sbook.
data: maximum like sflight-price.
data: minimum like sflight-price.
data: mittel like sflight-price.
data: g like sflight-carrid.
data: anzahl type i.
data: anzahl1 type i.
data: dates type i.
data: connid like sflight-connid.
************ Los! - Flugpreise
**********************************
select min( price ) max( price )
avg( price )
into (minimum, maximum, mittel) from sflight.
write:/15(10) minimum,
40(10) maximum, 60(10),mittel.
****Fluggesellschaften, Flugdaten *****
Select count(*)
count( distinct fldate )
count( distinct carrid ) into (anzahl, dates, anzahl1) from sflight.
*************** Ausgabe ************
skip 2.
write: / 'Anzahl der Flüge: ', 40 anzahl.
write: / 'Anzahl verschiedener Abflugdaten: ', 40 dates.
write: / 'Anzahl verschiedener Gesellschaften: ', 40 anzahl1.
*************** Seitenkopf ************
top-of-page.
write: / sy-uline.
write: / 'Gesel.', 17 'Minimum', 39 'Maximum', 60 'Mittel'.
write: / sy-uline.
*************** Buchungen **********
Select count(*) sum( loccuram )
into (anzahl, mittel)
from sbook.
skip 2.
write: / 'Anzahl Buchungen: ', 40 anzahl.
write: / 'Summe aller Buchungen: ', 40 mittel.
SELECT-Anweisung:
Funktionen im Beispiel von ORACLE
• SQL kennt die mathematischen Verknüpfungen +, -, * und / sowie den Operator ||
zum Verknüpfen zweier Strings. Wendet man diese Operatoren auf zwei Ausdrücke an, so erhält man einen neuen Ausdruck.
• Jedes Ergebnis einer Oracle-Funktion (angewendet auf einen Ausdruck) ist wieder ein Ausdruck im Sinne dieses Kapitels und kann damit in Selects etc. verwendet werden. Selbiges gilt analog für selbstgeschriebene Funktionen (Stored
Procedures).
• Hier einige Beispiele für Standard-Funktionen - ohne Anspruch auf Vollständigkeit und ohne vollständige Beschreibung aller Besonderheiten.
Mathematische Funktionen
• ABS(n) Berechnet den Absolutbetrag einer Zahl
• COS(n) Cosinus (analog SIN, TAN, COSH, SINH, EXP, LOG, LN...)
• MOD(m,n) Modulo-Operator (m modulo n) = Rest bei Ganzzahl-Division
• POWER(m,n) mn
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
105
SELECT-Anweisung:
Funktionen im Beispiel von ORACLE
Zeichenketten-Funktionen
• LOWER(s) Wandelt den String s in Kleinbuchstaben (analog UPPER)
• LPAD(s,n,t) Füllt den String s von links her auf n Zeichen auf, wobei zum Auffüllen das Zeichen bzw. der String t verwendet wird (Default t=Space).
• RPAD(n,s,t) Analog für Auffüllen von rechts
• LTRIM(s,t) Entfernt von links beginnend alle Zeichen aus s, die in t enthalten sind. Das erste nicht in t enthaltene Zeichen beendet den Vorgang. Wird t nicht angegeben, dann werden Leerzeichen entfernt.
• RTRIM(s,t) Analogon - von rechts her arbeitend.
• SUBSTR(s,m,n) Liefert einen Teilstring von s, beginnend an Position m und n Zeichen lang. Ist m<0, dann rechnet Oracle die Startposition vom String-Ende nach links. Ist n nicht angegeben, dann liefert Oracle alle Zeichen ab der Position m.
• LENGTH(s) Liefert die Länge des Strings s in Zeichen.
03.04.2002 Datenbanken 106
SELECT-Anweisung:
Funktionen im Beispiel von ORACLE
Datums- und Zeit-Funktionen
• ADD_MONTHS(d, n) Liefert das Datum d erhöht um exakt n Monate
• LAST_DAY(d) Liefert den letzten Tag des Monats, der das Datum d enthält - wiederum als Datum.
• NEXT_DAY(d) Analog für den nächsten Tag
• MONTHS_BETWEEN(d,e) Liefert die Anzahl der Monate (als Gleitkommazahl) die zwischen dem ersten Datum d und dem zweiten Darum e liegen. ist e<d, dann ist das Ergebnis negativ. Die Berechnung ist allerdings nicht ganz exakt, da Oracle bei der Bruchteilsberechnung immer auf einem 31-Tage-Monat basiert.
• SYSDATE Liefert das aktuelle Systemdatum
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
107
SELECT-Anweisung:
Funktionen im Beispiel von ORACLE
Konversions-Funktionen
• TO_CHAR(d, fmt) Konvertiert das Datum d in einen String und verwendet dafür das in fmt angegebene Format. Beispielsweise liefert das Format 'DD.MM.YYYY HH24:MI' ein typisch deutsches Datum mit minutengenauer Uhrzeit.
• TO_DATE(s, fmt) Wandest den String s in ein Datum um, wobei das in fmt angegebene Format für die Konversion benutzt wird.
SELECT-Anweisung:
die Group-by-Klausel
************ Los! - Flugpreise
**********************************
select min( price ) max( price )
avg( price )
into (minimum, maximum, mittel) from sflight.
write:/15(10) minimum,
40(10) maximum, 60(10),mittel.
Das brauchen wir pro Fluggesellschaft!
************ Los! - Flugpreise *****
select carrid connid min( price ) max( price ) avg(
price )
into (g, c, minimum, maximum, mittel) from sflight
group by carrid connid.
write: / g, 15(10) minimum, 40(10) maximum, 60(10) mittel.
endselect.
03.04.2002 Datenbanken
Studiengang Technische Informatik / Wirtschaftsinformatik
109
SELECT-Anweisung:
die Group-by-Klausel
03.04.2002 Datenbanken 110