Skript: Oracle 9i - Einführung - Leibniz Universität IT Services

RRZN
Regionales Rechenzentrum
D-30159 Hannover
für Niedersachsen
Schloßwender Straße 5
Universität Hannover
Timo Meyer
– Database Administrator (IHK) –
Kurs
Oracle 9i – Einführung
2. überarbeitete Auflage
Sommersemester 2005
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Inhaltsverzeichnis
1
Einleitung ............................................................................................................. 06
2
Einführung Datenbanken ...................................................................................
2.1
Was ist eine Datenbank (DB)? ...................................................................
2.2
Wichtige Begriffe ........................................................................................
Redundanz ............................................................................................
Konsistenz ............................................................................................
Datenintegrität .......................................................................................
Datensicherheit .....................................................................................
Datenschutz ..........................................................................................
2.3
Datenbanktypen .........................................................................................
Hierarchische Datenbanken ..................................................................
Netzwerkdatenbanken ..........................................................................
Relationale Datenbanken ......................................................................
2.4
Aufbau und Organisation einer Datenbank .................................................
3-Ebenen-Modell ...................................................................................
Weitere Komponenten des DBMS ........................................................
2.5
Physische Datenbankarchitektur ................................................................
Zentralisierte DBS .................................................................................
Verteilte DBS ........................................................................................
Client-Server DBS .................................................................................
Parallele DBS ........................................................................................
2.6
Übungen .....................................................................................................
07
07
08
08
08
08
08
08
08
09
09
10
10
11
12
12
12
13
13
14
14
3
Der Datenbankentwurf ........................................................................................
3.1
Datenbank-Lebenszyklus und Entwurfsphasen ..........................................
Anforderungsanalyse ............................................................................
Konzeptioneller Entwurf ........................................................................
Logischer Entwurf .................................................................................
Physischer Entwurf ...............................................................................
Test und Validation ...............................................................................
Anwendung und Wartung ......................................................................
3.2
Entity-Relationship-Modell (ER-Modell) ......................................................
Elemente und grafische Darstellung .....................................................
Kardinalitäten ........................................................................................
Beziehungstypen ..................................................................................
Datenbankentwurf .................................................................................
3.3
Übungen .....................................................................................................
3.4
ER-Diagramm der Nordwind Ltd. London ...................................................
15
15
15
15
15
15
15
15
16
16
18
19
20
21
22
2
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4
Das relationale Datenbankmodell ......................................................................
4.1
Relationen und Schlüssel ...........................................................................
Relation .................................................................................................
Attribute und Tupel ................................................................................
Schlüssel ...............................................................................................
4.2
Umwandlung des ER-Modells in ein relationales Modell ............................
Entitätsmengen .....................................................................................
1:1- und 1:n-Beziehungen .....................................................................
m:n-Beziehungen ..................................................................................
Generalisierung/Spezialisierung (is-a-Beziehung) ................................
4.3
Normalisierungsprozess .............................................................................
4.4
Übungen .....................................................................................................
23
23
23
23
23
24
24
24
26
26
27
30
5
Datenbanken ........................................................................................................
5.1
Datenbankabfragesprache „Structured Query Language“ (SQL) ...............
5.2
Installation einer Oracle-Datenbank ...........................................................
5.3
Anmelden an Oracle-Datenbank mittels SQL*Plus Worksheet ..................
5.4
Anmelden an Oracle-Datenbank mittels Eingabeaufforderung ...................
5.5
Nordwind-Datenbank ..................................................................................
5.6
Starten und Herunterfahren von Oracle .....................................................
31
31
31
32
33
33
34
6
Tablespaces erstellen und verwalten ................................................................ 36
6.1
Tablespaces erstellen und löschen ............................................................ 36
6.2
Tablespaces Offline und Online setzen ...................................................... 36
7
Tabellen erstellen und verwalten .......................................................................
7.1
Tabellen erstellen .......................................................................................
Einfache Tabellen erstellen ...................................................................
Datentypen ............................................................................................
Integritätsregeln in Tabellen verwenden ...............................................
7.2
Vorhandene Tabellen anzeigen, ändern und löschen ................................
Vorhandene Tabellen anzeigen ............................................................
Vorhandene Tabellen ändern ................................................................
Vorhandene Tabelle löschen ................................................................
7.3
Übungen .....................................................................................................
37
37
37
38
39
40
40
40
41
41
8
Datenpflege .........................................................................................................
8.1
Daten einfügen, aktualisieren und löschen .................................................
Mehrere Datensätze einfügen ...............................................................
Daten aktualisieren ...............................................................................
Daten löschen .......................................................................................
8.2
Übungen .....................................................................................................
42
42
42
42
43
44
9
Einfache Datenabfrage .......................................................................................
9.1
SELECT – Anweisung verwenden .............................................................
Doppelte Datensätze vermeiden ...........................................................
Anzahl der Datensätze beschränken ....................................................
Berechnungen ausführen ......................................................................
45
45
46
46
47
3
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
9.2
Bedingung definieren .................................................................................
Vergleichsoperatoren ............................................................................
Operator ................................................................................................
Bereichsprüfung ....................................................................................
Elementprüfung .....................................................................................
Mustervergleich .....................................................................................
Logische Operatoren ............................................................................
Gruppieren bzw. Sortieren der Abfrageergebnisse ....................................
Gruppieren ............................................................................................
Sortieren ...............................................................................................
Spalten umbenennen ............................................................................
Übungen .....................................................................................................
48
48
49
49
49
50
50
51
51
52
52
53
10
Constraints und Indizes .....................................................................................
10.1 Constraints .................................................................................................
Primärschlüssel erstellen und löschen ..................................................
Fremdschlüssel erstellen und löschen ..................................................
10.2 Übungen: Constraints .................................................................................
10.3 Indizes ........................................................................................................
Indizes erstellen ....................................................................................
Indizes löschen .....................................................................................
10.4 Übungen: Indizes .......................................................................................
54
54
54
55
56
57
57
57
58
11
Funktionen in Abfragen ......................................................................................
11.1 Standard-SQL-Funktionen ..........................................................................
Aggregatsfunktionen .............................................................................
Aggregatsfunktionen in Bedingungen ...................................................
11.2 Nicht-standardisierte Funktionen ................................................................
11.3 Übungen .....................................................................................................
59
59
59
60
60
61
12
Datenabfragen über mehrere Tabellen ..............................................................
Datenbestände in mehreren Tabellen ...................................................
Verknüpfungen von Tabellen über Mengenoperatoren .........................
12.1 Verbund von Tabellen (Joins) .....................................................................
Einfaches Verknüpfen von Tabellen .....................................................
Ersatznamen für Tabellen mit dem Schlüsselwort AS definieren ..........
Ersatznamen für Tabellen ohne das Schlüsselwort AS definieren ........
Full-Join ................................................................................................
Inner-Join (Equi-Join) ............................................................................
Natural-Join ...........................................................................................
Theta-Join .............................................................................................
Outer-Join .............................................................................................
Self-Join ................................................................................................
12.2 Tabellen vereinigen ....................................................................................
12.3 Übungen .....................................................................................................
62
62
62
63
65
67
67
67
68
69
70
70
71
72
73
9.3
9.4
4
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
13
Sichten ................................................................................................................. 74
13.1 Sichten erstellen und löschen ..................................................................... 74
13.2 Daten über Sichten einfügen und löschen .................................................. 74
14
Zugriffsrechte und Benutzer verwalten .............................................................
14.1 Benutzer und Profile anlegen und Verwalten .............................................
Benutzer anlegen und verwalten ...........................................................
Profile anlegen und verwalten ...............................................................
14.2 Zugriffsrechte an Benutzer vergeben .........................................................
14.3 Übungen .....................................................................................................
76
76
76
77
78
79
15
Anhang .................................................................................................................
15.1 Glossar .......................................................................................................
15.2 Wichtige SQL – Befehle .............................................................................
15.3 Literaturhinweise ........................................................................................
80
80
87
91
16
Lösungen zu den Übungen ................................................................................ 92
Kapitel 2: Einführung Datenbanken .................................................................... 92
Kapitel 3: Datenbankentwurf .............................................................................. 93
Kapitel 4: Relationales Datenbankmodell ........................................................... 94
Kapitel 7: Tabellen einfügen und verwalten ........................................................ 97
Kapitel 8: Datenpflege ........................................................................................ 98
Kapitel 9: Einfache Datenabfragen ................................................................... 100
Kapitel 11: Funktionen in Abfragen .................................................................... 102
Kapitel 12: Datenabfragen über mehrere Tabellen (Joins) ................................. 103
17
Stichwortverzeichnis ........................................................................................ 106
5
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
1
Einleitung
Ob E-Commerce, Internet-Suchmaschinen, prozessorientierte IT-Systeme in Unternehmen: Alles funktioniert nur deshalb, weil ein anforderungsgerecht entwickeltes Datensystem zugrunde liegt, das intensiv gepflegt und ständig überwacht wird. Datenbankadministratoren und Datenbankprogrammierer sind für den reibungslosen Betrieb verantwortlich.
Durch ständig wachsende Datenmengen, welche mit IT-Systemen verwaltet werden, sind
IT-Spezialisten zunehmend unverzichtbar geworden.
Das Seminar richtet sich in erster Linie an Anfänger sowie an Datenbank-Fachleute, die
ihre grundlegenden Datenbankkenntnisse auffrischen oder erweitern möchten. Nach diesem Kurs können Sie die Sprache SQL in einer Oracle-Umgebung (Version 8 und 9) einsetzen und installieren. Sie beherrschen Daten und deren Strukturen zu erstellen, pflegen
und abzufragen.
Im Kurs werden folgende Themen behandelt:
¾
¾
¾
¾
¾
¾
¾
¾
¾
Entity-Relationship-Modell (ER-Modell)
Relationales Datenbankmodell
Installation von Oracle 9.2.0.1.0 (Oracle 9i)
Verwendung von interaktivem SQL (SQL*Plus Worksheet etc.)
Erstellung und Verwendung von Datenbankobjekten (Tabellen, Indizes etc.)
Anlegen, Ändern und Löschen von Daten
Erstellen von vordefinierten und komplexen Datenbankabfragen
Verwendung vordefinierter SQL-Funktionen
Zugriffsrechte und Benutzerverwaltung
®
Hinweis: Alle Rechte für die Datenbank „Nordwind“ liegen bei der Microsoft Corporation
und wurden nur für Übungszwecke dieses Kurses für die Datenbank der Oracle® Corpora-
tion konvertiert. Alle vorliegenden Informationen werden für Schulungszwecke ohne Rücksicht auf einen evtl. Patentschutz veröffentlicht. Es wurde extra in Anlehnung an die Datenbank „Nordwind“ eine gleichnamige Musterfirma Nordwind Ltd. und deren ITTochtergesellschaft gegründet. Die Wiedergabe von Gebrauchsnamen, Handelsnamen,
Warenbezeichnungen usw. in diesem Skript berechtigt auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne der Warenzeichen- und Markenschutz-Gesetzgebung als frei zu betrachten wären und daher von jedermann benutzt
werden dürften.
Meinen herzlichen Dank spreche ich aus gegen die Herren Dipl.-Inf. Stefan HIETEL und
Reno FÖLLMER von der dama.go GmbH, Potsdam, (http://www.damago.de), und Walter
ISCHNER vom Computer Service Ischner, Barsinghausen, (http://www.ischner.de) für Ihren
freundlichen Rat, Hilfe und die Überlassung einiger Übungsaufgaben.
Timo Meyer
Hannover, den 07. Dezember 2004
6
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2
Einführung Datenbanken
2.1
Was ist eine Datenbank (DB)?
¾
¾
¾
Eine Datenbank ist eine strukturierte und organisierte Sammlung von Daten. In
jeder größeren Einrichtung (Banken, Behörden, Transportunternehmen etc.) müssen viele Mitarbeiter Zugriff auf Daten haben, z.B. Konten, Lagerbestände, Verwaltungsdaten etc.
Ein solcher Zugriff bedeutet Lesen, Anwenden, Verändern von Daten.
Dabei unterliegen einige Daten dem Datenschutz (nur bestimmte Mitarbeiter haben Zugriff auf bestimmte Datenbestände) Ö bestimmte Daten dürfen nicht oder
nur durch Berechtigung verändert werden und dürfen nicht verloren gehen.
Eine Datenbank besteht aus:
¾
¾
¾
einer Datenbasis Ö dem eigentlichen Datenbestand
dem Datenbank-Management-System (DBMS) Ö gewährt Zugriff auf die Daten
sowie dem Data Dictionary (auch: Row-Cache) Ö speichert Informationen über
die Datenbank (Sichten, Datenbank-Schema etc.) und deren Verwaltung (Zugriffsrechte etc.)
Anwendungen greifen über die Datenbanksoftware auf die Daten zu, die in der Datenbank gespeichert sind.
7
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Mit speziellen Abfragesprachen (z.B. SQL Ö Structured Query Language) werden Anfragen an die Datenbasis über das DBMS erstellt und dadurch werden die Informationen in
definierten Ausgabeformen als
¾
¾
¾
Listen
Bildschirmausgaben
Drucktexten
dem Nutzer zur Verfügung gestellt.
Die Aufgaben einer Datenbank sind die Verarbeitung von Nutzeranfragen. Lieferung von
Daten (Informationsfluss), die Möglichkeit der Rechtevergabe, die Protokollierung der
Transaktionen, die Überprüfung der Daten auf Widersprüche, sowie die Möglichkeit zum
Mehrbenutzerbetrieb.
2.2
Wichtige Begriffe
Bei der Datenbankentwicklung sind folgende Sachverhalte zu beachten:
Redundanz:
¾ mehrfache physische Speicherung von Daten Ö dadurch werden Änderungen
aufwendig, da die gleichen Daten an verschiedenen Stellen geändert werden
müssen.
¾ Datenbestand wird fehleranfällig, wenn mehrfach gespeicherte Daten nur an einer
Stelle geändert werden Ö kann zur Inkonsistenz der Daten führen.
Konsistenz:
¾ logische Richtigkeit der Daten.
Datenintegrität:
¾ umfasst die Menge der Maßnahmen die dafür sorgen, dass Daten während der
Verarbeitung nicht beschädigt werden können. Zum Beispiel: Kopie von Datenpaketen anlegen.
Datensicherheit:
¾ beschreibt die Sicherheit vor dem Verlust von Datenbeständen durch technische
Ausfälle.
Datenschutz:
¾ Schutz der Daten vor unberechtigten Zugriff und Verfälschung.
8
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2.3
Datenbanktypen
Hierarchische Datenbanken:
¾ zwischen den Datensätzen besteht eine untergeordnete Rangfolge.
¾ dieses System dient der Verarbeitung von Datensätzen, die eine unterschiedliche
Länge besitzen können.
Allgemeine Darstellung
Darstellung mit Beispieldaten
Netzwerkdatenbanken:
¾ zwischen den Tabellen besteht 1:n-Beziehung.
¾ Relationen werden als SETS bezeichnet.
Allgemeine
Darstellung
Darstellung
mit Beispielen
9
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Relationale Datenbanken:
¾ Daten werden in Tabellen (Relationen) gespeichert, zwischen denen Beziehungen (Integritäten) bestehen.
¾ SQL stellt das Hauptbindeglied zwischen den Daten und Resultaten dar.
Allgemeine Darstellung
2.4
Darstellung mit Beispieldaten
Aufbau und Organisation einer Datenbank
Eines der wichtigsten Ziele eines Datenbanksystems ist die Datenunabhängigkeit. Diese
wird durch die Trennung der Daten von den Anwendungsprogrammen, die die Datenbank
verwalten, erreicht.
Beispiel:
Ein Großhandel, wie unsere
NORDWIND LDT., hat verschiedene Abteilungen. Jede Abteilung braucht nur Teile der Datenbank, bestimmte Sichten.
So verwendet jede Abteilung
nur Daten, die sie auch benötigt. Zum Beispiel braucht die
Abteilung „Rechnungswesen“
keine Informationen zum „Lagerbestand“.
Logische Gesamtsicht auf die DB der Großhandelsfirma NORDWIND Ltd.
10
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
3-Ebenen-Model:
Hinweis:
¾
¾
¾
Externe Ebene (Data Manipulation Language, Veränderung der Daten Ö schreiben, löschen, hinzufügen)
Konzeptionelle Ebene (Data Definition Language, Beschreibung der Daten und
ihrer logischen Zusammenhänge: „Ausschnitt aus der realen Welt“)
Interne Ebene (Data Administration Language, beschreibt Organisation der Daten auf Speichermedium und Zugriffsmöglichkeiten auf die Daten; Zugriffsrechte
werden vergeben)
11
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Datenbankmanagementsystem (DBMS):
¾
¾
¾
ist ein Softwarepaket, welches die Verwaltung der Datenbank übernimmt und alle
Zugriffe darauf regelt.
nimmt Benutzeranfragen entgegen, ermittelt die gefragten Daten aus der Datenbank und liefert sie dem Benutzer bzw. dem Anwendungsprogramm zurück.
weitere Aufgaben des DBMS sind:
9 Wahrung der Integrität (in sich richtige und widersprüchliche Daten)
9 Datensicherung (Wiederherstellung nach Systemabsturz)
9 Synchronisation (gleichzeitige Zugriffe verwalten, sodass die Integrität gewahrt bleibt)
9 Datenschutz (bestimmte Daten nur für berechtigte Personen zugänglich)
Weitere Komponenten des DBMS:
¾
¾
2.5
Data Dictionary (speichert Informationen über die Daten der DB und deren Verwaltung)
Logbuch (speichert Informationen über alle Transaktionen, dient zur
Wiederherstellung)
Physische Datenbankarchitektur
Zentralisierte DBS:
Das gesamte DBMS, die Daten und die Anwendungen liegen auf einem Zentral-Computer.
An den anderen Standorten befinden sich Terminals, die nur der Ein- und Ausgabe dienen. Probleme sind hier die Antwortzeiten und die Ausfallsicherheit!
Netz mit Terminals und zentralisierter Datenbank
12
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Verteilte DBS:
Verteilte DBS sind mehrere logisch zusammengehörige Teildatenbanken, die in einem
Netz auf mehreren lokal getrennten Computern verteilt sind. Die Verteilung der Daten wird
vor dem Anwender verborgen und erscheint daher als ein zentralisiertes DBS.
Verteiltes DBS
Client-Server DBS:
Hier stellt der Client eine Anforderung an den Server, der Server bearbeitet die Anforderung und gibt die gewünschte Antwort an den Client zurück. Der Server stellt also die
Dienstleistungen zur Verfügung und der Client nimmt sie in Anspruch. Die Dienste des
Servers können von mehreren Clients gleichzeitig genutzt werden.
13
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Parallele DBS:
In einem parallelen DBS sind mehrere Prozessoren, Platten und Hauptspeicher über eine
sehr schnelle Leitung miteinander verbunden. Die Daten werden auf die verfügbaren Platten verteilt. Datenbank-Abfragen und Transaktionen werden so zerlegt, dass sie auf mehreren Prozessoren gleichzeitig abgearbeitet werden können. Dadurch werden die Antwortzeiten verkürzt.
2.6
Übungen: Einführung Datenbanken
(Lösung)
1.
Welche Datenbanktypen kennen Sie? Wodurch sind sie gekennzeichnet?
2.
Nennen Sie die Namen der 3 Ebenen des 3-Ebenen-Modells, und geben Sie an, was
in jeder Ebene dargestellt wird.
3.
Was ist ein Datenbankmanagementsystem?
4.
Was ist ein Data Dictionary, und wozu wird es benötigt?
5.
Welche physischen Datenbankarchitekturen kennen Sie? Erläutern Sie eine!
14
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
3
Der Datenbankentwurf
3.1
Datenbank-Lebenszyklus und Entwurfsphasen
Anforderungsanalyse:
¾
Grenzt die Inhalte der neuen DB ein und dient der Festlegung von Benutzergruppen und Anwendungen. Datenobjekte, deren Eigenschaften und Beziehungen,
sowie mögliche Vorgänge (Aktualisierungen, Abfragen) und Randbedingungen
werden ermittelt … Resultat ist der Datenbankansatz.
Konzeptioneller Entwurf:
¾
Umfasst die Modellierung und Integration der Sichten. Dafür werden meist EntityRelationship-Diagramme (ER-Diagramm) erstellt.
Logischer Entwurf:
¾
Grafische Darstellungen eines konzeptionellen Entwurfs werden in das Datenbankmodell des Ziel-Datenbanksystems transformiert. Die gesamte DB wird für
eine effektive Speicherung aufbereitet (Normalisierung siehe 4.3).
Physischer Entwurf/Implementierung:
¾
DB wird erstellt und benötigte Anfragen werden formuliert. Für ein relationales
DBS geschieht dies in der Abfragesprache SQL, welche verschiedene Dialekte
kennt: Access, SQL-Server, MySQL und ORACLE.
Test und Validation:
¾
DB und die erstellten Abfragen werden nun getestet und auf ihre Gültigkeit bezüglich der Anforderungen geprüft (validiert).
Anwendung und Wartung:
¾
In der Phase der Anwendung muss die DB ständig gewartet werden, denn im
Laufe der Zeit können sich Änderungen des Datenbankschemas ergeben oder es
müssen neue Benutzer angelegt und verwaltet werden.
15
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
3.2
Entity-Relationship-Modell (ER-Modell)
Das Entity-Relationship-Modell1 (kurz ER-Modell oder ERM) ist das bekannteste und
meistverwendete grafische Hilfsmittel für den Datenbankentwurf. Es ermöglicht, konzeptionelle Entwürfe einer Datenbank auf leicht verständliche Art grafisch darzustellen.
Elemente und grafische Darstellung:
Entität (Entity):
¾
unterscheidbare (identifizierbare) Dinge aus der realen Welt (z.B. Personen, Gegenstände, Firmen); wird auch als Objekt bezeichnet.
Beispiel:
¾
Entitäten:
Abteilung Forschung
Mitarbeiter Andrew Füller
Projekt CX „Konkurrenzanalyse“
Entitätsmengen:
Alle Abteilungen
Alle Mitarbeiter
Alle Projekte
Entity-Typen:
Abteilung
Mitarbeiter
Projekte
Eigenschaften charakterisieren eine Entität, einen Entity-Typ, eine Beziehung
bzw. einen Beziehungstyp.
Eigenschaften:
Abteilung
Abteilungsnummer
Projektnummer
Projektname
Projektbeginn
Domänen:
Abteilung:
Abteilungsnummer:
Projektnummer:
Projektname::
1
Einkauf
Verkauf
Personal
1-999
1-9999
{A-Z|1-9}
Das ER-Modell wurde 1968 von dem amerikanischen Mathematiker Cheen erfunden.
16
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Schlüssel und Primärschlüssel:
Ein Schlüssel setzt sich aus einem oder mehreren Attributen zusammen.
Der Primärschlüssel ermöglicht die eindeutige Identifizierung einer Entität, einer Entitätsmenge dadurch, dass sein Wer in einer Entitätsmenge nur ein einziges Mal vorkommt. Ein
Entity-Typ kann mehrere Fremdschlüssel besitzen, die für bestimmte Abfragen oder Sortierungen benötigt werden, aber nur einen Primärschlüssel.
Entity-Typ PROJEKT besitzt den Primärschlüssel Projektnummer
Entity-Typ PERSON besitzt einen Primärschlüssel, der sich aus den
Attributen Name und Geburtsdatum zusammensetzt
Der Primärschlüssel, welcher sich aus zwei Attributen zusammensetzt
17
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Beziehung:
Durch Beziehungen werden die Wechselwirkungen von Entitäten ausgedrückt. Unterschieden wird durch die Beziehungsmenge und den Beziehungstyp.
Beziehung:
Mitarbeiter Fuller arbeitet an Projekt CX Konkurrenzanalyse.
Beziehung ohne und mit Attribut:
¾ Mitarbeiter arbeitet an Projekt.
¾
Mitarbeiter arbeitet an Projekt in der Tätigkeit als … und zu … Prozent
¾
Produkt setzt sich zusammen aus Bauteilen, die geliefert werden von Lieferant.
Kardinalitäten:
Über die Kardinalitäten wird festgelegt, wie viele Entitäten einer Entitätsmenge mit Entitäten einer anderen Entitätsmenge in Beziehung stehen können. Zum Beispiel wie viele Mitarbeiter an einem Projekt mitarbeiten. In der Regel erfolgt die Kennzeichnung von Kardinalitäten durch folgende Angaben:
1
n,m
c
c,n
genau eine Zuordnung
eine oder mehrere Zuordnungen
eine oder keine Zuordnung
keine oder mehrere Zuordnungen
18
– einfach
– multiple
– konditionell
– multiple-konditionell
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Daraus ergeben sich folgende Möglichkeiten für die Darstellung der Beziehungen (ohne
Kardinalitäten):
1:1 – eins-zu-eins-Beziehung
1:n – eins-zu-n-Beziehung
n:m – n-zu-m-Beziehung
Jede Entität einer Entitätsmenge ist genau einer
Entität einer anderen Entitätsmenge zugeordnet.
Jede Entität einer Entitätsmenge ist eine oder sind
mehrere Entitäten einer anderen Entitätsmenge
zugeordnet.
Eine oder mehrere Entitäten einer Entitätsmenge
können eine oder mehreren Entitäten einer anderen
Entitätsmenge zugeordnet werden.
Kardinalitäten:
1 Abteilung besteht aus n Mitarbeitern.
n Mitarbeiter arbeiten an m Projekten.
Beispiel für Kardinalitäten:
Für eine Kardinalität kann auch eine Zahl festgelegt werden, z.B. 2, wenn immer genau 2
Entitäten einer Entitätsmenge in Beziehung stehen.
Besteht die Möglichkeit, dass es für einige Entitäten keine Zuordnung in Beziehungen gibt,
kann den Angaben 1, n oder m eine 0 hinzugefügt werden. Zum Beispiel 0,n, dass 0, 1,
2, …, n Entitäten in Frage kommen.
19
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Übersicht:
Entity-Typ (Klassifizierung)
Attribut (Eigenschaft)
Primärschlüssel (Identifikation)
Beziehung (Relation, Relationship, Assoziation)
Part-of-Beziehung (Ist-Teil-von-Beziehung, Aggregation
Is-a-Beziehung (Ist-ein-Beziehung, Spezialisierung
oder Generalisierung, Teilmengenbeziehung)
Datenbankentwurf:
Ist die Anforderungsanalyse abgeschlossen, können Sie das konzeptionelle Schema der
Datenbank mit dem ER-Modell entwerfen. Dabei kann nach der Top-Down- oder nach der
Button-Up-Methode vorgegangen werden. Bei der Top-Down-Methode wird das grob entworfene Modell schrittweise verfeinert. Diese Verfeinerung wird auf die Entität, die Attribute und Beziehungen verwendet. Dabei werden beispielsweise Entity-Typen und Beziehungstypen zerlegt bzw. zusammengefügt, Spezialisierungen bzw. Generalsierungen und
Aggregationen aufgebaut und Attribute sowie Schlüssel festgelegt.
Beispiel:
Kunden und Projektverwaltung
In einem Unternehmen sind Sachbearbeiter und Mitarbeiter angestellt. Die Sachbearbeiter
betreuen die Kunden und nehmen Aufträge entgegen. Der jeweilige Auftrag wird an eine
Abteilung weitergeleitet, aus dem Auftrag wird ein Projekt. An dem Projekt arbeiten mehrere Mitarbeiter. Zur Verwaltung aller Informationen und Ereignisse soll eine Datenbank erstellt werden. In der folgenden Abbildung sind alle Elemente der Datenbank und deren
Beziehungen untereinander zusammengestellt.
20
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Textbeschreibung eines Datenmodells
Um eine kompakte Beschreibung der Entity-Typen, Attribute und Schlüsselfelder zu erhalten, kann folgende textuelle Form (die Mengenbeschreibung aus der Mathematik) verwendet werden. Zu Beginn stehen der Name des Entity-Typs und in Klammern die Attribute,
wobei der oder die Primärschlüssel unterstrichen sind.
Entity-Typen:
ANGESTELLTER (PersonalNr, Name, GebDat, PLZ, Wohnort, Strasse, Gehalt, Beruf)
MITARBEITER (PersonalNr, Name, GebDat, PLZ, Wohnort, Strasse, Gehalt, Beruf)*
SACHBEARBEITER (PersonalNr, Name, GebDat, PLZ, Wohnort, Strasse, Gehalt, Beruf)*
ABTEILUNG (AbtNr, AbtName)
KUNDE (KnNr, Name, PLZ, Wohnort, Strasse)
AUFTRAG (AuftragsNr, AuftrDat, Beschreib, FertigDat)
PROJEKT (ProjNr, Name, BegDatum, EndDatum)
* Die Attribute erben diese Entity-Typen von dem Entity-Typ ANGESTELLTER.
Beziehungstypen:
is-a (ANGESTELLTER, MITARBEITER, SACHBEARBEITER)
arbeitet_ in (ABTEILUNG, SACHBEARBEITER, Position)
arbeitet_in (ABTEILUNG, MITARBEITER, Position)
betreut (KUNDE, AUFTRAG)
erteilt (KUNDE, AUFTRAG)
bearbeitet_als (AUFTRAG, PROJEKT)
arbeitet_an (MITARBEITER, PROJEKT, als, prozAnteil)
betreut (ABTEILUNG, PROJEKT)
3.3
Übungen: Datenbankentwurf
(Lösung)
1.
Welche Phasen werden beim Entwurf von Datenbanken durchlaufen?
2.
Wozu dient das Entity-Relationship-Modell?
3.
Erstellen Sie ein grafisches ER-Modell für das Beispiel: Kunden und Projektverwaltung (siehe Seite 20, Datenbankentwurf)
21
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
3.4
ER-Diagramm der Nordwind Ltd. London
22
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4
Das relationale Datenbankmodell
Eine DB besteht im relationalen Modell aus einer Menge von Relationen, in denen die logisch zusammengehörigen Dateien gespeichert werden.
4.1
Relationen und Schlüssel
Relation:
Eine Relation ist im Sinne einer relationalen DB eine Menge von Tupeln (Datensätze). Sie
hat die Form einer Tabelle und ist damit eine Konstruktion aus Spalten und Zeilen. Sowohl Entitäten als auch Beziehungen des ER-Modells werden über Relationen modelliert.
Datentechnisch bezieht sich also eine Relation NICHT auf die Beziehung (Verknüpfung)
zwischen Tabellen. In einer Relation werden Daten gespeichert.
Attribute und Tupel:
Eine Tabelle besteht aus Spalten und Zeilen. Bei relationalen Datenbanken werden Spalten ATTRIBUTE oder auch FELDER genannt. Die Zeilen der Tabelle werden als TUPEL
oder DATENSATZ bezeichnet.
Schlüssel:
Die Menge der Attribute, die ein Tupel eindeutig identifizieren, heißen Schlüsselkandidaten. An Schlüssel wird die Minimalitätsanforderung gestellt, d.h. ein Schlüssel muss so
kurz wie möglich sein.
Primärschlüssel:
Der Primärschlüssel ist ein Schlüssel, der einen Datensatz eindeutig identifiziert, z.B. eine
Artikelnummer. Häufig handelt es sich dabei um eine fortlaufende Nummer.
Sekundärschlüssel:
Ähnlich wie beim Primärschlüssel müssen sich die Datensätze auch in den Sekundärschlüsseln unterscheiden. Sie dienen dazu, Redundanzen in den gespeicherten Daten zu
vermeiden, da in einem Schlüsselfeld ein Wert nicht mehrfach vorkommen darf.
Fremdschlüssel:
Ein Fremdschlüssel bezeichnet die Übereinstimmung eines Datenfeldes in einer Tabelle
mit dem Primärschlüssel einer anderen Tabelle. Im relationalen Datenmodell werden häufig so genannte Lookup-Tabellen, die zwei Tabellen miteinander verbinden.
Index:
Für ein oder mehrere Datenfelder angelegtes Inhaltsverzeichnis zum beschleunigten Filtern, Gruppieren oder Sortieren einer Tabelle.
23
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4.2
Umwandlung des ER-Modells in ein relationales Modell
Das folgende ER-Modell soll in ein relationales Modell überführt werden.
Die Anzahl der Relationen, die aus dem ER-Modell erzeugt werden, hängt von den definierten Entitätsmengen und den Beziehungstypen zwischen den Entitätsmengen ab.
Entitätsmengen:
Für jede Entitätsmenge wird eine Relation erstellt, welche für jedes Attribut eine Spalte
besitzt. Die Primärschlüssel werden übernommen.
Relationenname
ABTEILUNG
MITARBEITER
Relationenschema
Beispiel
Attribut
AbteilungsNr
Datentyp
Zahl
Text
AbteilungsNr
001
002
003
Attribut
PersonalNr
Name
Vorname
Anschrift
Datentyp
Zahl
Text
Text
text
PersonalNr
0001
0002
0003
0004
0005
Attribut
ProjektNr
Beschreibung
Datentyp
Zahl
Text
Projekt2
AZ
BY
CX
Bezeichnung
Personal
Einkauf
Verkauf
Name
King
Dovalio
Fuller
Suyama
Callahan
Vorname
Robert
Nancy
Andrew
Michael
Laura
Anschrift
London
Seattle
Tacoma
London
Seattle
PROJEKTE
Beschreibung
Interview
Sales Fair
Analysis
1:1- und 1:n- Beziehungen:
Für die Umsetzung der beiden Beziehungstypen gibt es zwei Möglichkeiten:
Es wird eine neue Relation erzeugt, welche als Attribute (Spalten) die Primärschlüssel der
beiden Relationen, die in Beziehung stehen, enthält. Zusätzlich kann die Relation beschreibende Attribute in einer zusätzlichen Spalte aufnehmen. Die Darstellungsform hat
den Vorteil, dass keine Nullwerte auftreten, und den Nachteil, dass eine weitere Relation
benötigt wird.
2
Projekt = ProjektNr. / Projektkennziffer.
24
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Relationenname
besteht_aus
Relationenschema
Attribut
PersonalNr
AbteilungsNr
Position
Beispiel
Datentyp
Zahl
Zahl
Text
PersonalNr
0001
0002
0003
0004
0005
AbteilungsNr
001
002
002
003
003
Position
Leiter
Sachbearbeiter
Mitarbeiterin
Leiter
Mitarbeiter
Eine der beiden Relationen wird um ein Attribut (Spalte) erweitert. Bei 1:1-Beziehungen ist
es egal, welche der beiden Relationen erweitert wird. In der 1:n-Beziehung wird die Relation erweitert, bei der das n steht. Wird das Attribut an die Relation beschreibende Attribute,
so werden diese zusätzlich noch bei der erweiterten Relation angefügt, bei der die 1 steht,
treten Redundanzen auf, die Sie unbedingt vermeiden sollten. Besitzt die Relation beschreibende Attribute, so werden diese zusätzlich noch bei der erweiterten Relation angegeben.
Bei dieser Darstellungsform können Nullwerte auftreten; diese hat aber den Vorteil, dass
keine zusätzliche Relationen benötigt wird. Für das obige Beispiel bedeutet das, dass die
Relation MITARBEITER erweitert werden muss.
Relationenname
MITARBEITER
Relationenschema
Besipiel
Attribut
Pers
Nr
0001
0002
0003
0004
…
PersonalNr
Name
Vorname
Anschrift
AbtNr
Position
Datentyp
Zahl
Text
Text
Text
Zahl
Text
Name
King
Dovalio
Fuller
Suyama
…
Vorname
Robert
Nancy
Andrew
Michael
…
Anschrift
London
Seattle
Tacoma
London
…
Abt
Nr
001
002
002
003
…
Position
Leiter
Sachbe.
Mitarb.
Leiter
...
Bei Erweiterung der RELATION ABTEILUNG entstehen redundante Daten.
ABTEILUNG
Attribut
AbteilungsNr
Bezeichnung
PersonalNr
Position
Datentyp
Zahl
Text
Text
text
AbteilungsNr
Bezeichnung
001
002
003
004
…
Personal
Einkauf
Einkauf
Verkauf
…
Personal
Nr
0001
0002
0003
0004
…
Position
Leiter
Sachbe.
Mitarb.
Leiterin
...
Ein Nullwert kann in diesem Beispiel auftreten, wenn ein Mitarbeiter keiner Abteilung zugeordnet ist.
25
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
m:n-Beziehungen:
Bei einer m:n-Beziehung ist immer eine zusätzliche Relation erforderlich, um die betreffenden Entitäten zu verknüpfen. Diese Relation enthält die Primärschlüssel der beiden
Relationen und kann zusätzlich noch beschreibende Attribute enthalten.
Für das obige Beispiel wird eine Relation arbeiten_an benötigt. Das Relationsschema wird
aus den beiden Primärschlüsseln und den Attributen Tätigkeit und prozAnteil (prozentualer
Anteil der Arbeitszeit) aufgebaut.
Relationenname
arbeitet_an
Relationenschema
Attribut
PersonalNr
ProjektNr
Tätigkeit
prozAnteil
Beispiel
Datentyp
Zahl
Zahl
Text
text
PersonalNr
0004
0003
0005
0001
0002
ProjektNr
AZ
AZ
AZ
BY
BY
0004
0002
0003
CX
CX
CX
Tätigkeit
Leiter
Mitarbeiterin
Mitarbeiter
Leiter
Präsentationsvorbereitung
Leiter
Sachbearbeit.
Mitarbeiterin
prozAnteil
25
50
50
25
100
25
50
50
Generalisierung/Spezialisierung (is-a-Beziehung):
Für eine Überführung einer is-a-Beziehung gibt es mehrere Möglichkeiten, die von dem
jeweiligen Schema und von dem Kontext abhängig sind. In diesem Fall ist aber keine zusätzliche Relation für die Beziehung nötig.
Es wird für jede Entitätsmenge eine Relation mit den relevanten Attributen angelegt. Den
Teilmengen (Spezialisierungen) wird der Primärschlüssel der Obermenge (Generalisierung) als Fremdschlüssel hinzugefügt, um die Zuordnung zu sichern.
Relationenname
PERSONAL
Relationenschema
Attribut
PersonalNr
Name
Vorname
Anschrift
GebDatum
Beispiel
Datentyp
Zahl
Text
Text
Text
Datum
Personal
Nr
0001
0002
0003
0004
0005
…
26
Name
King
Dovalio
Fuller
Suyama
Callahan
…
Vorname
Robert
Nancy
Andrew
Michael
Laura
…
Anschrift
London
Seattle
Tacoma
London
Seattle
…
GebDatum
29.05.1960
08.12.1948
19.02.1952
02.07.1963
09.01.1958
…
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Relationenname
ARBEITER
Relationenschema
Attribut
PersonalNr
Schicht
Arbeitsplatz
ANGESTELLTER
Attribut
PersonalNr
ZimmerNr
TelefonNr
Beispiel
Datentyp
Zahl
Zahl
Text
PersonalNr
0011
0012
0015
0019
0037
Schicht
1
2
3
2
3
Arbeitsplatz
01
02
02
01
01
Datentyp
Zahl
Zahl
Text
PersonalNr
0001
0002
0005
0009
0007
Schicht
1
8
3
5
3
Arbeitsplatz
369
265
235
302
236
4.3
Normalisierungsprozess
Ziele:
¾
¾
¾
¾
Anomalien zu beheben
Redundanzen zu vermeiden
Einen übersichtlichen und möglichst einfachen Aufbau der Relationen zu erhalten
Eine einfache Datenpflege zu ermöglichen
Normalisierungsprozess:
¾ Die Normalisierung eines relationalen Schemas wird in mehreren Stufen vollzogen.
Dabei müssen die Daten in den Relationen in jeder Stufe bestimmte Bedingungen
erfüllen. Das Resultat der Anwendung dieser Regeln wird als Normalform des Relationsschemas bezeichnet.
¾ Beim Normalisierungsprozess werden die Daten einer Relation auf mehrere Relationen verteilt:
BestellNr
01
02
03
04
KdNr
1076
Name
Müller
Kundengr
1
Rabatt
0%
4904
4100
Henriot
Wong
2
3
5%
10 %
06
3901
v. Behr
1
0%
ArtNr
72
35
56
72
40
40
72
35
56
Bezeichnung
Mozarella
Starkbier
Gnocchi
Mozarella
Fleisch
Fleisch
Mozarella
Starkbier
Gnocchi
Preis
34,80 €
18,00 €
38,00 €
34,80 €
18,40 €
18,40 €
34,80 €
18,00 €
38,00 €
Menge
8
10
5
4
9
6
2
1
1
Ö bei BestellNr 06 bestellt der Kunde mit einer Bestellung unter einer Bestell-Nummer 4 Artikel.
27
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
1. Normalform:
¾ Wenn die Relation zweidimensional ist.
¾ Wenn sich in jedem Datensatz nur Daten befinden, die zu einem Objekt der realen
Welt gehören.
¾ Für jedes Attribut nur ein Wert eingetragen ist.
¾ Die ungeordnete Tabelle wird mit den fehlenden Eigenschaften ergänzt.
BestellNr
01
02
03
04
05
06
06
06
06
KdNr
1076
1076
4904
4100
4100
3901
3901
3901
3901
Name
Müller
Müller
Henriot
Wong
Wong
v. Behr
v. Behr
v. Behr
v. Behr
Kundengr
1
1
2
3
3
1
1
1
1
Rabatt
0%
0%
5%
10 %
10 %
0%
0%
0%
0%
ArtNr
72
35
56
72
40
40
72
35
56
Bezeichnung
Mozarella
Starkbier
Gnocchi
Mozarella
Fleisch
Fleisch
Mozarella
Starkbier
Gnocchi
Preis
34,80 €
18,00 €
38,00 €
34,80 €
18,40 €
18,40 €
34,80 €
18,00 €
38,00 €
Menge
8
10
5
4
9
6
2
1
1
2. Normalform:
¾ Eine Relation befindet sich in zweiter Normalform, wenn jedes Nicht-Schlüsselfeld
vom ganzen Primärschlüssel abhängig ist.
¾ Die Grundtabelle wird in mehrere Tabellen aufgeteilt, in denen wieder Primärschlüssel vergeben werden.
¾ Man erstellt eine weitere Tabelle mit einem neuem Primärschlüssel und fügt die
Primärschlüssel den anderen Tabellen als Fremdschlüssel in die zweite Normalform ein (es können auch weitere Attribute vorkommen Ö siehe Spalte Menge)
Beispiele:
KUNDE
KdNr
1076
4904
4100
3901
Name
Müller
Henriot
Wong
v. Behr
Kundengr
1
2
3
1
Rabatt
0%
5%
10 %
0%
Primärschlüssel
ARTIKEL
ArtNr
35
40
56
72
Bezeichnung
Starkbier
Fleisch
Gnocchi
Mozarella
Preis
18,00 €
18,40 €
38,00 €
34,80 €
28
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
BESTELLUNG
BestellNr
01
02
03
04
05
06
06
06
06
KdNr
1076
1076
4904
4100
4100
3901
3901
3901
3901
ArtNr
72
35
56
72
40
40
72
35
56
Menge
8
10
5
4
9
6
2
1
1
3. Normalform:
Eine Tabelle befindet sich in der 3. Normalform, wenn alle Datenfelder nur von einem
Schlüssel abhängig sind und untereinander keine Abhängigkeiten auftreten.
Sobald ein Nicht-Schlüsselfeld nur über ein anderes Nicht-Schlüsselfeld identifiziert ist,
wird von transitiver Abhängigkeit gesprochen.
Transitive Abhängigkeiten (direkt untereinander abhängige Attribute werden in einer neuen
Tabelle zusammengefasst).
Beispiel:
RABATT
Kundengr
1
2
3
Rabatt
0%
5%
10 %
Primärschlüssel
Weitere Normalformen:
Boyce-Codd-Normalform (Abhängigkeit von einzelnen Schlüsseln oder Schlüsselattributen)
4. und 5. Normalform:
In der 4. Normalform werden mehrwertige Abhängigkeiten von Attributmengen zu einem
so genannten Superschlüssel vereinigt.
In der 5. Normalform werden weitere Primärschlüssel hinzugefügt.
29
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4.4
1.
Übungen: Relationales Datenbankmodell3
(Lösung)
Erstellen Sie für eine CD-Sammlung eine Datenbank. Gehen Sie folgendermaßen
vor:
¾
¾
Erstellen Sie ein ER-Diagramm
Wenden Sie die Normalformen an (bis zur 3. Normalform)
Um die Datenbank aufzubauen, steht Ihnen die folgende Feldliste zur Verfügung
(Sänger bedeutet der Interpret oder der Gruppenname; bei einer Gruppe entfällt die
Eingabe des Vornamens):
Sängernummer, Name des Sängers, Vorname des Sängers, Bild des Sängers,
Bemerkungen zum Sänger, CD-Nummer, Titel der CD, Laufzeit der CD, Bild des
Covers, Liednummer, Name des Liedes, Laufzeit des Lieds, Managernummer,
Name des Managers, Geschlecht des Managers und Adresse des Managers.
Als Regel gilt: Ein Sänger kann einen oder keinen Manager haben. Ein Manager
kann jedoch mehrere Sänger beraten.
2.
3.
3
Welche Aussagen treffen für eine m:n-Beziehung zu?
…
Eine m:n-Beziehung kann in der Praxis nicht erstellt werden.
…
In einer m:n-Beziehung kann die referentielle Integrität nicht eingeschaltet
werden.
…
Eine m:n-Beziehung liegt vor, wenn zwischen zwei Entitäten die
Primärschlüssel der beiden Tabellen mehrfach in der Relation vorkommen.
…
Eine m:n-Beziehung wird in Access oder Oracle benutzt, um zwei Tabellen
zu verknüpfen.
Kennzeichnen Sie die falschen Aussagen!
…
In einer Datenbank werden die Daten in Feldern strukturiert abgespeichert.
…
Die referentielle Integrität bedeutet, dass in einem Feld ein Wert eingegeben
werden muss.
…
Ein Standardwert soll die Arbeit erleichtern. Der Standardwert wird in jeden
neuen Datensatz für ein Feld eingegeben.
© ISCHNER, W., Prüfungsaufgaben zu Datenbankgrundlagen (Schulung dama.go GmbH Potsdam: Database Administrator Oracle), 2004.
30
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
5
Datenbanken
5.1
Datenbankabfragesprache „Structured Query Language“ (SQL)
Die Datenbankabfragesprache SQL wurde aus der Abfragesprache SEQUEL (Structured
English QUEry Language) der Firma IBM entwickelt. 1986 wurde SQL von ANSI (American National Standard Institute) als Standard SQL1 festgelegt. Die Weiterentwicklung der
Sprache wurde von den Standardisierungsorganisationen ISO (Internantional Standards
Organisation) und ANSI unter der Bezeichnung ISO/IEC 9075:1992 (DIN 66315) veröffentlicht. Sie wird als SQL2 bezeichnet.
Zum Sprachumfang von SQL gehören 4 Befehlsgruppen:
Erstellen von Datenbanken, Tabellen (Relationen)
und Indizes
Abfragen von Daten
Anlegen, Ändern und Löschen von Datensätzen
Anlegen von Benutzern und Vergabe von
Zugriffsrechten
DDL (Data Definition Language)
DQL (Data Query Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
Installation einer Oracle-Datenbank4
5.2
Der erste Schritt beim Erstellen einer Datenbank ist das Anlegen der Datenbank selbst. In
ihr werden später die Daten in Form von mehreren Tablespaces und Tabellen gespeichert.
Es können beliebig viele Tablespaces und Tabellen in einer Oracle-Datenbank erstellt
werden.
In diesem ersten Abschnitt werden Sie Oracle mit Hilfe des Oracle Universal Installers installieren und dabei eine erste DB namens testdbx erstellen:
1)
2)
3)
4)
5)
6)
4
Starten Sie die Datei Setup.exe von CD.
Im Fenster Willkommen klicken Sie bitte auf Weiter.
Im Fenster Dateiverzeichnisse überprüfen Sie bitte die Quelle und das Ziel, verändern bitte nichts und klicken anschließend auf Weiter.
Im Fenster Verfügbare Produkte wählen Sie bitte Oracle Database 9.2.0.1.0
aus und klicken danach auf Weiter.
Im Fenster Installationsarten wählen Sie bitte Enterprise und klicken anschließend auf Weiter.
Im Fenster Datenbank-Konfiguration geben Sie bitte Universal ein klicken anschließend auf Weiter.
© HIETEL, S., Übungen (Schulung dama.go GmbH Potsdam: Database Administrator Oracle), 2004.
31
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
7)
8)
9)
10)
11)
12)
13)
14)
Im Fenster Oracle Services for MS Transaction Server klicken Sie auf Weiter.
Im Fenster Speicherort der Datenbankdatei klicken Sie bitte auf Weiter.
Im Fenster Datenbank-Identifikation geben Sie bitte als Globaler Datenbankname
testdbx (wobei X die Nummer ist, die Ihrem Computer zugeordnet wurde - eventuell beim Trainer erfragen) ein und klicken anschließend auf Weiter.
Im Fenster Speicherort der Datenbankdatei klicken Sie bitte auf Weiter.
Im Fenster Datenbank-Zeichensatz klicken Sie bitte auf Weiter.
Im Fenster Überblick klicken Sie bitte auf Installieren.
Geben Sie für das Konto sys das Kennwort sys ein und für das Konto system
bitte system.
Zum Abschluss klicken Sie bitte auf Weiter und anschließend auf Beenden.
Anmelden an Oracle-Datenbank mittels SQL*Plus Worksheet2
5.3
Im letzten Teil werden Sie lernen, wie man sich mit Hilfe des Worksheets mit dem OracleServer verbindet und arbeitet.
1)
2)
3)
4)
Starten Sie das SQLPlus Worksheet.
(Start Ö Programme Ö Oracle - OraHome92 Ö Application Development
Ö SQLPlus Worksheet).
Geben Sie als Benutzernamen den Namen sys und als Kennwort das Kennwort
sys ein. Geben Sie unter Dienst den Namen testdbx ein und unter Anmelden bitte SYSDBA auswählen. Klicken Sie anschließend auf OK.
Probieren Sie eine kleine Abfrage auszuführen. Geben Sie hierfür select * from
scott.emp; ein und bestätigen Sie die Anweisung mit F5.
Schließen Sie das SQLPlus Worksheet mit exit.
32
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
5.4
Anmelden an Oracle-Datenbank mittels Eingabeaufforderung
In diesem Teil werden Sie lernen, wie man sich mit Hilfe der MS-DOS Eingabeaufforderung mit dem Oracle-Server verbindet und dort arbeitet.
1)
2)
3)
4)
5)
6)
5.5
Starten Sie die MS-DOS-Eingabeaufforderung
(Start Ö Ausführen Ö CMD Ö OK)
Geben Sie sqlplus /nolog ein und bestätigen Sie mit Enter.
Im Feld Benutzernamen eingeben geben Sie bitte
connect sys/sys@testdb as sysdba ein und bestätigen Sie mit Enter.
Probieren Sie eine kleine Abfrage auszuführen. Geben Sie hierfür select * from
scott.emp; ein und bestätigen Sie die Anweisung mit Enter.
Schließen Sie sqlplus mit exit.
Nordwind-Datenbank
Im Folgenden werden Sie die Tabellen der Übungsdatenbank „Nordwind“ via Skript unter
Oracle installieren. Melden Sie sich mit Hilfe der Eingabeaufforderung an der Oracle Datenbank an. Geben Sie bitte folgende Syntax an dem SQL-Prompt ein, um die NordwindTabellen via Skript5 zu installieren:
SQL> start C:\nordwind_skript\nordwind.txt
5
Kommentare werden in Text-Skripten (.txt) mit „#“ und in SQL-Skripten (.sql) mit „--“ eingeleitet. Auch
kann unter der DOS-Oberfläche bei SQL*Plus ein Editor mit „edit“ aufgerufen werden.
33
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
5.6
Starten und Herunterfahren von Oracle6
Der Startvorgang von Oracle wird in drei Stati unterteilt:
Status 1 – NOMOUNT
SQL> STARTUP NOMOUNT;
Der erste Status ist der Status NOMOUNT. In diesem wird die entsprechende Startdatei (INI-Datei Ö „pfile“) gelesen, die entsprechenden physischen Strukturen (Datendateien, Redo-Log-Dateien und Control-Dateien)
identifiziert, die Speicherstrukturen (SGA Ö System Global Area) initialisiert
und die Hintergrundprozesse (DBWn Ö Database Writer, LGWR Ö Logwriter, CKPT Ö Checkpoint, SMON Ö SystemMONitor, PMON Ö ProzessMONitor und ARCn Ö ARChiver) gestartet. Hiermit wird eine Instanz gestartet. Sie können mit dem Parameter pfile= die entsprechend zu öffnende INI-Datei angeben.
Status 2 – MOUNT (DB angehängt)
SQL> STARTUP MOUNT;
Hier werden die Control-Dateien geöffnet und die Speicherorte der Datendateien sowie der Online-Redo-Log-Dateien gelesen.
Status 3 – OPEN (DB geöffnet)
SQL> STARTUP OPEN;
Hier werden die Datendateien sowie die Online-Redo-Log-Dateien geöffnet
und Sperren etc. gesetzt.
Eine Oracle-Datenbank wird hochgefahren und geöffnet.
6
© HIETEL, S., Skript zu Oracle Performance Tuning (Schulung dama.go GmbH Potsdam: Database Administrator Oracle), 2004.
34
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Ähnlich wie die Stati beim Hochfahren einer Datenbank gibt es auch vier Stati beim Herunterfahren der Datenbank:
Status 1 – Shutdown normal
SQL> SHUTDOWN NORMAL;
Wenn Sie die Option „Shutdown normal“ benutzen, so werden keine neuen
Benutzersitzungen zugelassen. Oracle wartet, bis sich alle Benutzer abgemeldet haben. Benutzer werden zum Abmelden jedoch nicht gedrängt
oder von Oracle informiert. Sind alle Benutzer abgemeldet, so wird die Datenbank geschlossen, die Instanz abgehängt (Control-Datei dismountet)
und die Instanz heruntergefahren (reservierter Speicher für die SGA freigegeben und alle Hintergrundprozesse beendet).
Status 2 – Shutdown immediate
SQL> SHUTDOWN IMMEDIATE;
Wenn Sie die Option „Shutdown immediate“ benutzen, so werden wie auch
bei der ersten Statusoption keine neue Benutzersitzungen zugelassen.
Oracle beendet alle Benutzersitzungen. Alle nicht abgeschlossenen Transaktionen werden beendet. Sind alle Benutzer abgemeldet, so wird die Datenbank geschlossen, die Instanz abgehängt (dismountet) und die Instanz
heruntergefahren.
Status 3 – Shutdown transactional
SQL> SHUTDOWN TRANSACTIONAL;
Wenn Sie die Option „Shutdown transactional“ benutzen, so werden wie
auch bei der ersten Statusoption keine neuen Besitzersitzungen zugelassen. Es werden keine neuen Transaktionen zugelassen. Oracle wartet bis
die aktuellen Transaktionen durch ein „commit“ (schreiben der Daten in die
Datenbank) beendet sind. Danach werden alle Benutzer abgemeldet, die
Datenbank geschlossen, die Instanz abgemeldet und heruntergefahren.
Status 4 – Shutdown abort
SQL> SHUTDOWN ABORT;
Die Datenbank wird sofort geschlossen. Es wird weder auf den Abschluss laufender SQL-Anweisungen gewartet, noch eine Speicherung für die abgeschlossenen Aktionen durchgeführt.
35
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
6
Tablespaces erstellen und verwalten
6.1
Tablespaces erstellen und löschen
Tablespaces fassen Datendateien zusammen. Sie dienen als „Speichereinheit“ für Objekte. Benutzern werden generell in einem Standard-Tablespace zugeordnet, aber nie im
System-Tablespace angelegt. Wenn nun diese Benutzer ein Objekt erstellen, so wird dieser Standard-Tablespace hierfür genutzt. Die wichtigsten Anweisungen sollen kurz an
dieser Stelle vorgestellt und erläutert werden:
Beispiel:
CREATE TABLESPACE testdbs
DATAFILE ’c:\oracle\oradata\testdb\testdbs01.dbf’
SIZE 10M ONLINE;
Es wird ein Tablespace namens „testdbs“ erstellt, welcher aus der Datendatei (Dateiname
ist gleich der Pfadname der Datei) „c:\oracle\oradata\testdb\testdbs01.dbf“ besteht. Der
Tablespace hat eine Initialgröße von 10 MByte und wurde auf Online gesetzt.
Beispiel:
DROP TABLESPACE testdbs;
Der Tablespace testdbs wird gelöscht. Die entsprechenden Dateien im Tablespace werden nicht mitgelöscht. Diese können anschließend manuell gelöscht werden.
6.2
Tablespaces Offline und Online setzen
Manchmal müssen Tablespaces Offline gesetzt werden. Dies kann notwendig werden,
wenn mehrere Datendateien eines Tablespaces beschädigt sind und das Öffnen der Datenbank verhindern. In diesem Fall können Sie die Datenbank „mounten“, den entsprechenden Tablespace Offline setzen, und anschließend die DB öffnen.
Beispiel:
1. ALTER TABLESPACE testdbs01 OFFLINE;
Der Tablespace „testdbs01“ wird OFFLINE gesetzt.
2. ALTER TABLESPACE testdbs01 ONLINE;
Der Tablespace „testdbs01“ wird ONLINE gesetzt.
36
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
7
Tabellen erstellen und verwalten
7.1
Tabellen erstellen
Tabellen (Relationen) sind die einzigen Objekte einer Datenbank, in denen Daten gespeichert werden.
Eine Tabelle besteht aus einzelnen Feldern. Durch die Namen und Datentypen der Felder
wird die Struktur bzw. das Schema der Tabelle festgelegt. Zusammengehörige Daten werden in einer Tabelle eingetragen. Diese bilden einen Datensatz bzw. ein Tupel.
Einfache Tabelle erstellen:
Eine Tabelle mit den dazugehörigen Datenfeldern wird mit der Anweisung CREATE
TABLE erstellt. Die Anweisung besitzt sehr viele Optionen, die z.B. das Definieren von
Standardwerten erlauben.
Einfachste Form:
CREATE TABLE Tabellenname (
datenfeld 1 datentyp 1,
datenfeld 2 datentyp 2
…
datenfeld x datentyp x);
Beispiel:
CREATE TABLE test1 (
vorname VARCHAR (100),
name VARCHAR (100));
Erstellen einer neuen Tabelle mit SQL*Plus / ORACLE.
Die Anweisung im Beispiel erstellt eine Tabelle der Anlieferfirmen „shippers“ mit den Spalten (Feldern) „shipperid“, „companyname“ und „phone“. Hinter dem Feld steht der Datentyp, den das Feld annehmen soll.
37
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Folgende optionale Parameter sind bei der Definition von Datenfeldern möglich:
Mit diesem Parameter wird eine Eingabe eines Wertes für das
entsprechende Datenfeld erzwungen. Die Angabe NOT NULL
ist für die Schlüsselfelder unbedingt anzugeben.
Mit diesem Parameter wird festgelegt, dass das Datenfeld
standardmäßig keinen Wert (auch nicht 0 oder eine leere
Zeichenkette enthält.
Der Parameter DEFAULT definiert einen Standardwert. Enthält
das Datenfeld bei der Eingabe keinen Wert, so wird automatisch
dieser Standardwert verwendet.
NOT NULL
NULL
DEFAULT standardwert
Datentypen:
Beim Erstellen einer Tabelle muss für jedes Datenfeld ein Datentyp angegeben werden.
Möglich sind hierbei:
Numerische Datentypen:
Datentypen
SMALLINT
INTEGER (INT)
FLOAT
Speicherbedarf
2 Byte
4 Byte
4 Byte
DATE
8 Byte
Wertebereich / Beschreibung
-32.768 + 32.767
- 2.147.483.648 + 2.147.483.648
Fließkommazahlen zwischen -1,79E + 308 und
1,79E + 308 (7 signifikante Stellen).
Interner Oracle-Datentyp zum eines Datums mit
Oder ohne Zeitanteil.
Datumswerte:
Eingabeformat
DD.MM.YYYY
Day, DD.MM.YY
Day, DD Mounth YYYY
Beispiel
18.09.2004
Samstag, 18.09.04
Samstag, 18 September 2004
Datentypen für Zeichen und Texte:
Datytyp
CHAR (Länge)
VARCHAR (Länge)
VARCHAR2 (Länge)
BLOB
Erklärung
Zum Speichern beliebiger Textinformationen. Die maximale Länge wird
dabei als Parameter übergeben.
Wie CHAR, nur wird der tatsächlich verbrauchte Speicherplatz belegt,
falls der eingegebene Text kleiner als die Maximallänge ist.
Intern verwendete Typenbezeichnung zum Speichern variabler Zeichenfolgen,
d.h., beim VARCHAR-Typ handelt es sich im Prinzip um ein kompatibles
Synonym für VARCHAR2. Wird empfohlen zur Standardbenutzung!
Zum Speichern großer, auch binärer Datenmengen, z.B. sehr große
Textdateien, Grafiken, Bilder oder Videos. Maximale Größe in Oracle 4 GByte.
38
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Integritätsregeln in Tabellen verwenden:
Beim erstellen einer Tabelle können bereits Regeln für die zu speichernden Daten definiert
werden. Eine Gültigkeitsprüfung wird nach der Definition der Datenfelder mit dem Schlüsselwort CONSTRAINT (engl. Zwang, Nebenbedingung) eingeleitet. Damit wird erreicht,
dass bereits bei der Eingabe nur gültige Werte in der Tabelle gespeichert werden können.
Beispiel:
CREATE TABLE test2 (
test2id INT NOT NULL,
name VARCHAR (30),
vorname VARCHAR (20),
CONSTRAINT test2_ck CHECK (test2id > 2));
Es wird eine Tabelle mit den Feldern „test2id“, „name“ und „vorname“ erstellt. Beim Feld
„test2id“ werden nur Werte größer als 2 akzeptiert.
Beispiele für Gültigkeitsbedingungen:
Bedingung
categoryid <= 100
categoryname NOT LIKE ’%A&’
description IN (’Apfel’, ‚Orange’)
Erklärung
Wahr, wenn die categoryid kleiner oder gleich 100.
Wahr, wenn der categoryname den Buchstaben A nicht enthällt.
Wahr, wenn description den Wert Apfel oder Orange trägt.
Eine weitere Möglichkeit ist das Erstellen von berechneten Spalten, deren Inhalt nicht in
der Datenbank gespeichert wird. Durch berechnete Spalten kann Platz eingespart und
Inkonsistenzen vermieden werden.
Beispiel:
CREATE TABLE test3 (
test3id INT NOT NULL,
stueck INT DEFAULT 1,
preis FLOAT NOT NULL,
wert COMPUTED BY (stueck*preis));
Es wird eine Tabelle mit den Feldern „test3id“, „stueck“ (Defaultwert=1), „preis“ (muss einen Wert enthalten) und dem Feld „wert“ in dem das Ergebis von „stueck“ * „preis“ berechnet wird.
Berechnete Datenfelder werden nicht von allen SQL-Dialekten
unterstützt !!!
39
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
7.2
Vorhandene Tabellen anzeigen, ändern und löschen
Vorhandene Tabellen anzeigen:
Über die Anweisung DESCRIBE (DESC) Tabellename kann man den Inhalt der in der Datenbank vorhandene Tabellen auflisten lassen:
Beispiel:
DESCRIBE [schema.]object[@datenbank];
DESC sys.v$tablespace;
Vorhandene Tabellen ändern:
Die Struktur einer Tabelle kann jederzeit über die Anweisung ALTER TABLE geändert
werden. Dabei ist es möglich:
¾ Datenfelder hinzuzufügen oder löschen
¾ Datenfelddefinitionen verändern
¾ Gültigkeitsprüfungen hinzufügen oder löschen
¾ Schlüssel und Indizes hinzufügen oder löschen
ALTER TABLE tabellenname
[ADD datenfelddefinition]
[ADD CONSTRAINT constraintname CHECK (gültigkeits-bedingung)]
[DROP objektname];
Beispiele:
1. ALTER TABLE test3
ADD name VARCHAR (20);
Das Datenfeld „name“ wird der Tabelle „test4“ hinzugefügt.
2. ALTER TABLE test3
ADD PRIMARY KEY (test3id);
Das vorhandene Datenfeld „test3id“ wird zum Primärschlüssel
gemacht.
3. ALTER TABLE test3
MODIFY name VARCHAR (20) NOT NULL;
Für das Datenfeld „name“ soll eine Eingabe erforderlich sein.
40
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4. ALTER TABLE test3
ADD CONSTRAINT test3_ck CHECK (test3id > 1);
Für das vorhandene Datenfeld „test3id“ wird die Bedingung
> 1 festgelegt.
5. ALTER TABLE test3 DROP name;
[ALTER TABLE test3 DROP COLUMN name;]
Das Datenfeld „name“ wird aus der Tabelle gelöscht.
Vorhandene Tabelle löschen:
Mit der Anweisung DROP TABLE lassen sich Tabellen löschen. Dabei werden die Definitionen der Tabellenstruktur und alle in der Tabelle gespeicherten Datensätze gelöscht.
DROP TABLE tabellenname;
7.3
Übungen: Tabellen
(Lösung)
1.
Erstellen Sie eine neue Tabelle „t_artikel“ mit den Datenfeldern „artid“, „artname“ und
„artpreis“. Verwenden Sie geeignete Datentypen.
2.
Löschen Sie das Datenfeld „artpreis“.
3.
Erstellen Sie eine Tabelle „t_personen“ mit den Datenfeldern „persid“, „vorname“ und
„nachname“. Verwenden Sie geeignete Datentypen. Für alle Datenfelder soll dabei
eine Eingabe erforderlich sein.
4.
Ändern Sie die Tabelle und fügen Sie ein zusätzliches Datenfeld „lebenslauf“ hinzu.
In dem Feld soll eine größere Datenmenge – z.B. ein Word-Dokument – gespeichert
werden können.
5.
Fügen Sie ein neues Datenfeld „angestellt_seit“ in die Tabelle ein. Es soll ein Datumswert gespeichert werden können.
6.
Löschen Sie die Tabellen „t_artikel“ und „t_personen“.
41
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
8
Datenpflege
8.1
Daten einfügen, aktualisieren und löschen
Um Datensätze in eine Tabelle einzufügen, verwendet man die Anweisung INSERT INTO.
Dabei ist es möglich, einen vollständigen Datensatz oder nur Werte für ausgewählte Datenfelder einzufügen.
INSERT INTO tabellenname (feld1, …, feldx) VALUES (wert1, …, wertx);
Beispiel:
INSERT INTO wappenrolle (name,vorname, wappen, wohnort)
VALUES (’Freiherr v. Furtenbach’, ‘Franz’,
’Goldener Wellenbalken auf blauen Felde’,
’Hannover’);
Mehrere Datensätze einfügen:
Die zweite Form der INSERT INTO-Anweisung ermöglicht das Einfügen mehrerer Datensätze. Die einzufügenden Daten werden dabei mit Hilfe einer Abfrage aus einer existierenden Tabelle gewonnen.
INSERT INTO tabellenname (feld1, …, feldx) SELECT [datenfelder]
FROM tabellenname [WHERE bedingung];
Beispiel:
INSERT INTO wappenrolle (name,vorname, wappen, wohnort)
SELECT name, vorname, wappen
FROM adelsarchiv WHERE ort LIKE ’Hannover’;
In die Tabelle „wappenrolle“7 werden alle Datensätze der Tabelle „adelsarchiv“ eingefügt,
bei denen der Wohnort Hannover ist.
Ö SELECT wird in Kapitel 7 genauer erklärt!
Daten aktualisieren:
Beim Ausführen der INSERT INTO-Anweisung wird stets ein neuer Datensatz eingefügt.
Man kann aber auch existierende Datensätze bearbeiten und verändern. Mit der UPDATEAnweisung lassen sich ein oder mehrere Datensätze aktualisieren. Die Auswahl der Datensätze erfolgt über eine WHERE-Bedingung. Wird keine Bedingung angegeben, so werden alle Datensätze aktualisiert.
7
Eine „Wappenrolle“ ist ein Lexikon mit Wappendarstellungen und dazugehörigen Familieninformationen.
42
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
UPDATE tabellenname SET feld1 = wert1, …, feldx = wertx [WHERE bedingung];
Der Wert kann dabei eine Zahl sein, ein Text, ein berechneter Ausdruck oder eine
Unterabfrage sein.
Beispiele:
1. UPDATE wappenrolle SET wohnort = ’Leutkirch’
WHERE name = ’Furtenbach’
AND vorname = ’Franz’;
Der Ort von Franz v. Furtenbach wird auf Leutkirch geändert.
2. UPDATE wappenrolle SET wohnort = ’Leutkirch’;
Alle Datensätze der Tabelle bekommen als Ort Leutkirch
eingetragen.
Beispiel für die Wertzuweisung:
Wertzuweisung in UPDATE-Funktion
SET name = ’Georg’
SET anzahl = 100
SET anzahl = anzahl*2
SET vorfahren = (SELECT ahnenid FROM ahnen
WHERE ahnenid = 20)
Erläuterung
Zuweisen einer Textinformation
Speichern der Zahl 100 im Feld anzahl
Der aktuelle Wer des Feldes anzahl wird mit 2
Multipliziert und im Feld anzahl gespeichert.
Mittels einer Unterabfrage wird aus der Tabelle
ahnen ein Datensatz abgefragt und der ermittelte
Wert im Datenfeld vorfahren gespeichert.
Daten löschen:
Mit Hilfe der DELETE-Anweisung können ein oder mehrere Datensätze einer Tabelle gelöscht werden. Die Auswahl der Datensätze kann wie bei der UPDATE-Anweisung mit Hilfe der WHERE-Bedingung eingegrenzt werden.
DELETE FROM tabellenname [WHERE bedingung];
Beispiel:
1. DELETE FROM wappenrolle WHERE vorname = ‘Georg’;
Alle Datensätze der Tabelle Wappenrolle, bei denen der Wert im
Feld „vorname“ gleich Georg ist, werden gelöscht.
2. DELETE FROM wappenrolle;
Wird keine Bedingung angegeben, so werden alle Datensätze
der Tabelle Wappenrolle gelöscht.
43
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
8.2
Übungen: Datenpflege
(Lösung)
1.
Erstellen Sie eine neue Tabelle „wappenrolle“ mit den Datenfeldern „name“, „vorname“, „titel“, „wappen“ und „geboren“. Verwenden Sie geeignete Datentypen. Für alle
Datenfelder soll dabei eine Eingabe erforderlich sein.
2.
Fügen Sie folgende Informationen in die Tabelle „wappenrolle“ ein.
Name
v. Furtenbach
de Montifichet
Newton
v. Metternich
Romanow
Hannover
Bonaparte
Habsburg
Vorname
Franz
Richard
Isaac
Klemens
Michael
Georg II.
Napoleon
Friedrich III.
Titel
Freiherr
Lord
Sir
Prinz
Zar
König
Kaiser
Kaiser
Wappen
Wellenbalken
drei goldene Sparren
zwei gekreuzte Knochen
drei Muscheln
Doppelkopfadler
Silbernes Pferd
Adler
Doppelkopfadler
geboren
1880
1200
1642
1173
1597
1587
1769
1415
3.
Führen Sie eine einfache Datenabfrage durch, um das erfolgreiche Einfügen der
neuen Datensätze zu überprüfen.
4.
Ändern Sie den Titel „Zar“ in „Kaiser“ und das Geburtsdatum „1597“ in „1587“.
5.
Löschen Sie den Datensatz des Freiherrn Franz v. Furtenbach.
6.
Was bewirkt folgende Anweisung:
INSERT INTO wappenrolle SELECT * FROM wappenrolle;
7.
Wieviele Datensätze befinden sich jetzt in der Tabelle Wappenrolle? Löschen Sie
anschließend die Tabelle.
44
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
9
Einfache Datenabfrage
9.1
SELECT – Anweisung verwenden
Mit Hilfe der SELECT-Anweisung können Sie alle oder ausgewählte Datenfelder von Tabellen abfragen. Die SELECT-Anweisung muss die gewünschten Daten dabei nicht nur
aus einer einzigen Tabelle ermitteln. Es sind auch verknüpfte Anfragen über zwei oder
mehr Tabellen möglich.
Die mit einer SELECT-Anweisung abgefragten Daten können Sie gezielt über so genannte
WHERE-Bedingungen eingrenzen. Außerdem ist das Sortieren und Gruppieren des Abfrageergebnisses möglich.
Beispiel Datenabfrage 1:
Mit dieser Anweisung werden alle Datenfelder und alle Datensätze aus der Tabelle gelesen und angezeigt.
SELECT * FROM Tabellenname;
Die einfachste SELECT-Anweisung lautet SELECT * FROM Tabellenname. Damit rufen
Sie schnell alle Datensätze einer gewünschten Tabelle ab.
Beispiel Datenabfrage 2:
Mit dieser Anweisung werden folgende Datenfelder companyname und phone aus der Tabelle gelesen und angezeigt.
SELECT companyname, phone FROM shippers;
45
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Syntax einer einfachen Datenabfrage
Doppelte Datensätze vermeiden:
Um doppelte Datensätze zu vermeiden, können Sie nach dem Schlüsselwort SELECT die
Angabe DISTINCT hinzufügen. Dabei ist es ausreichend, wenn sich die Daten in einem
Datenfeld unterscheiden, um von der Datenbank als unterschiedlich erkannt zu werden.
1.
2.
SELECT * FROM Tabellenname;
SELECT [DISTINCT] * FROM Tabellenname;
Anzahl der Datensätze beschränken:
Die SELECT-Abfrage liefert standardmäßig alle Datensätze einer Tabelle, bzw. alle Datensätze auf die die Bedingung zutrifft. Mit dem Schlüsselwort LIKE kann die Datensätze
ausgeben, welche eine bestimmte Bedingung erfüllen.
46
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
SELECT DISTINCT productname FROM products
WHERE productname LIKE ’Ch%’ ORDER BY productname;
Alle Produkte werden ausgegeben, bei denen der Produktname mit „Ch“ beginnt.
Berechnungen ausführen:
In einer SELECT-Abfrage können Sie verschiedene Berechnungen durchführen. Die Ergebnisse werden wie der Inhalt eines Datenfeldes behandelt. Für Berechnungen können
Sie alle numerischen Datenfelder der Tabelle sowie konstante Werte verwenden.
Es werden alle Datensätze der Tabelle Produkte mit Netto- und Bruttopreisen angezeigt.
SELECT unitprice AS Netto, unitprice*1.16 AS Brutto FROM products;
In der Tabelle werden alle Datensätze mit Netto- und Bruttopreisen angezeigt. Um
den Bruttowert zu erhalten, wird zu dem in der Tabelle gespeicherte Preis 16 %
Mehrwertsteuer hinzugefügt.
Folgende Operatoren können Sie bei Berechnung in SQL verwenden „-“ „+“ „*“ „/“
47
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
9.2
Bedingungen definieren
Mit Hilfe der WHERE-Bedingung können Sie die mit einer SELECT-Abfrage zurückgelieferten Daten einschränken bzw. filtern. Dies ist vor allem bei sehr großen Datenmengen
wichtig und wird von einer SQL-Datenbank auch bei mehreren Millionen Datensätzen sehr
schnell ausgeführt, wenn diese dafür optimiert wurde (z.B. durch Verwendung von Indizes).
Möglichkeiten für WHERE-Bedingungen sind in folgenden Tabellen zu finden.
Bedingung
Vergleichsoperatoren
Beispiel
unitprice < 100
name = King
Bereichsprüfung
unitprice BETWEEN 30
AND 50
Elementprüfung
city IN (’London’)
Mustervergleich
productname LIKE ’S%’
Nullwertprüfung
unitprice IS NULL
Logische Operatoren
supplierid=1 AND categoryid=1
Erklärung
Vergleicht den Wert eines
Datenfeldes mit einem
vorgegebenen Wert.
Prüft, ob der Wert eines Feldes
innerhalb eines bestimmten
Bereichs liegt.
Prüft, ob der Wert eines Feldes
sich in der angegebenen Liste
befindet.
Überprüft einen Feldinhalt auf
Übereinstimmung mit einem
angegebenen Muster.
Prüft einen Feldinhalt auf den
Wert NULL (Datenfeld enthält
keinen Wert).
Verknüpfen mehrerer
Bedingungen.
Vergleichsoperatoren:
Operator
<
>
<>
=
>=
<=
Erklärung
kleiner als
größer als
ungleich
ist gleich
größer oder gleich
kleiner oder gleich
Beispiele:
1.
2.
SELECT * FROM products WHERE unitprice < 50;
SELECT * FROM employees WHERE city = ‘Berlin’;
1. Die Abfrage liefert alle Datensätze, bei denen der Preis kleiner als 50 ist.
2. Hier wird die Mitarbeitertabelle der Angestellten nach allen Datensätzen gefiltert,
bei denen der Ort Berlin ist.
48
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Bereichsprüfung:
Die Operatoren BETWEEN und IN erleichtern das Definieren von Wertebereichen oder
Wertelisten, indem sie mehrere verbundene Vergleichsausdrücke ersetzen. Mit dem Operator BETWEEN prüfen Sie, ob der Wer eines Datenfeldes in einem bestimmten Wertebereich liegt. Der Operator ersetzt dabei die Bedingung x >= Wert AND x <= Wert.
Beispiel:
1.
2.
3.
SELECT * FROM products WHERE unitprice >=30 AND unitprice <=50;
SELECT * FROM products WHERE unitprice BETWEEN 30 AND 50;
SELECT * FROM employees WHERE lastname BETWEEN ‘Ca’ AND ‘Da’;
1. Bei dieser Bedingung wird mit zwei Vergleichen geprüft, ob der Preis größer oder
gleich 30 und gleichzeitig kleiner oder gleich 50 ist.
2. Der BETWEEN-Operator vereinfacht diesen Ausdruck.
3. Auch ein alphabetischer Bereich kann auf diese Weise angegeben werden. Es
werden hier die Datensätze ausgewählt, bei denen die Namen zwischen „Ca“ und
„Db“ liegen. Das sind alle Namen, die mit „Ca“ beginnen bis zu den Namen, die mit
„Da“ beginnen. Auch der Wert „Db“ wird als richtig erkannt. Namen, welche mit „Bo“
beginnen, werden nicht mit ausgewählt, da sie außerhalb der Bereichsgrenze liegen.
Elementprüfung:
Sie können auch prüfen, ob der Wert eines Datenfeldes in einer Liste von Werten enthalten ist. Dazu definieren Sie eine Werteliste, die alle möglichen Werte enthält. Mit Hilfe des
In-Operators testen Sie in einer Bedingung, ob ein Wert in der Liste enthalten ist.
1.
2.
SELECT * FROM customers WHERE country = ‚’Brazil’ OR country = ’France’;
SELECT * FROM customers WHERE country IN (‘Brazil’,’France’);
1. Hier werden zwei verbundene Bereiche durch eine OR-Verknüpfungen miteinander
geprüft, ob der Wert des Datenfeldes gleich „Brazil“ oder „France“ ist.
2. Übersichtlicher wird die gleiche Bedingung mit dem IN-Operator formuliert. Die Liste
enthält die zwei gewünschten Orte.
49
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Mustervergleich:
Speziell für Textwerte existiert der LIKE-Operator, der einen flexiblen Vergleich mit einem
vorgegebenen Muster ermöglicht. Dies ist besonders dann nützlich, wenn Ihnen als Suchkriterium nur ein Teil einer Information zur Verfügung steht.
1.
2.
SELECT DISTINCT * FROM categories WHERE categoryname LIKE ’Co%’;
SELECT DISTINCT * FROM categories WHERE categoryname LIKE ’C%on_’;
1. Die Abfrage liefert alle Produktkategorien, deren Name mit „Co” beginnt.
2. Die Abfrage liefert alle Produktkategorien, deren Name mit „C“ beginnt und auf „on“
und ein beliebiges letztes Zeichen endet.
Logische Operatoren:
Mit Hilfe von logischen Operatoren können Sie mehrere Bedingungen miteinander verbinden. Auf diese Weise erhalten Sie sehr komplexe Suchkriterien.
SQL ermöglicht den Einsatz der folgenden logischen Operatoren.
AND
OR
NOT
UND-Verknüpfung: Beide Bedingungen müssen
erfüllt sein
ODER-Verknüpfung: Mindestens eine der
Bedingungen muss erfüllt sein.
NICHT-Operator: Die nachfolgende Bedingung
wird negiert (aus wahr wird falsch und aus falsch
wird wahr).
50
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Beispiel:
1.
2.
3.
SELECT * FROM employees WHERE lastname LIKE ‘D%’ AND city <> ‘Seattle;
SELECT * FROM employees WHERE lastname LIKE ‘D%’ AND
(city = ‘Hannover’ OR city = ‘London’);
SELECT * FROM employees WHERE NOT (lastname LIKE ‘D%’);
1. Die Abfrage liefert alle Mitarbeiter, deren Name „D“ beginnt und die nicht in Seattle
wohnen.
2. Mit dieser Abfrage werden alle Mitarbeiter ermittelt, deren Name mit „D“ beginnt
und die in Hannover oder London wohnen.
3. Hier werden alle Mitarbeiter gesucht, deren Name nicht mit „D“ beginnt.
9.3
Gruppieren bzw. Sortieren der Abfrageergebnisse
Gruppieren:
Um die Datensätze einer Tabelle nach einem oder mehreren Kriterien zusammenzufassen
(Gruppen zu bilden), verwendet man die GROUP BY-Anweisung. Auf diese Weise sucht
man alle Datensätze, die in einem bestimmten Datenfeld den gleichen Wert besitzen und
fassen diesen zu einer Gruppe zusammen.
Beispiel:
Es soll ermittelt werden, wie viele Mitarbeiter aus einem Ort kommen. Dazu werden alle
Datensätze nach dem jeweiligen Ort gruppiert (zusammengefasst) und danach wird die
Summe der verbliebenen Datensätze gebildet.
SELECT city, COUNT(lastname) FROM employees GROUP BY city;
Die Datensätze der Tabelle werden mit Hilfe der Anweisung GROUP BY nach dem
Ort gruppiert. Für jede Gruppe wird mit der Aggregatfunktion COUNT die Anzahl der
51
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Mitarbeiter ermittelt, die in diesem Ort wohnen. Der Name des Feldes, auf welches
die Aggregatfunktion angewendet werden soll, folgt der Funktionsname in runden
Klammern.
Sortieren:
Die SELECT-Anweisung liefert die Datensätze in keiner definierten Reihenfolge. Meist
verwendet die Datenbank dabei die Reihenfolge, in der die Daten in die Tabelle eingetragen wurden. Um das Abfrageergebnis nach einem oder mehreren Datenfeldern zu sortieren, kann man die ORDER BY-Klausel verwenden.
Beispiel:
1.
2.
3.
SELECT firstname, lastname, postalcode, city FROM employees ORDER BY
lastname, firstname;
SELECT * FROM products ORDER BY unitprice DESC;
SELECT COUNT(lastname) FROM employees GROUP BY city ORDER BY city;
1. In dieser Anweisung wird zuerst nach dem Familiennamen und dann nach dem
Vornamen der Mitarbeiter sortiert.
2. Mit dem Schlüsselwort DESC / ASC wird eine absteigende / aufsteigende Sortierung erreicht. Alle Datensätze der Tabelle werden ausgewählt und nach dem Preis
in absteigender Reihenfolge sortiert.
3. Die Sortierung lässt sich auch bei gruppierten Daten anwenden. Hier wird die Ausgabeliste nach dem Ort sortiert.
Spalten umbenennen:
Es wird eine Abfrage ausgewählter Datensätze und Datenfelder mit neuen aussagekräftigen Feldnamen erzeugt.
SELECT companyname AS shippers FROM shippers;
52
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
9.4
Übungen: Datenabfragen
(Lösung)
1.
Schreiben Sie eine Abfrage, die alle Datensätze der Produkttabelle mit der
Lieferantennummer = 2 und Kategorienummer = 1 liefert.
2.
Schreiben Sie eine Abfrage, die alle Produktnamen mit deren Kategorie-ID auflistet.
Dabei soll jeweils folgender Satz ausgegeben werden. Das Produkt X gehört zur Kategorie Y. Benutzen Sie als Hilfe die Funktion „Pipe – Pipe “ : ||
3.
Erstellen Sie eine Abfrage, die Ihnen alle Produkte ausgibt, die mit „C“, „D“ oder „E“
anfangen.
4.
Erstellen Sie eine Abfrage, die Ihnen alle Produkte ausgibt, bei denen der 5. Buchstabe kein „c“ ist.
5.
Erstellen Sie eine Abfrage, die Ihnen alle Produkte ausgibt, bei denen der 5. Buchstabe ein „c“ oder „d“ ist. Diese sollen aufsteigend nach dem Produktnamen sortiert
sein.
6.
Schreiben Sie eine Abfrage, die Ihnen die Spalte Ort der Kundentabelle ausgibt. Es
soll keine Stadt mehrmals auftauchen.
53
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
10
Constraints und Indizes
10.1
Constraints
Primärschlüssel erstellen und löschen:
Primärschlüssel werden in der Regel beim Erstellen einer Tabelle definiert. Dazu können
Sie die Anweisung PRIMARY KEY verwenden, die an das Ende der Tabellendefinition
gesetzt wird. Ein PRIMARY KEY ist eine besondere Art eines CONSTRAINT. Jede Tabelle besitzt eine Spalte oder zumindest eine Spaltenkombination, die eindeutig ist. Das bedeutet, mit dieser Spaltenkombination ist es möglich, jeden Datensatz der Tabelle eindeutig von den anderen Datensätzen abzugrenzen. Diese Spalte bzw. Spaltenkombination
wird als Primärschlüssel bezeichnet und darf nicht doppelt innerhalb einer Tabelle auftauchen. Der Primärschlüssel darf nicht NULL sein. Bei einem zusammengesetzten Primärschlüssel darf keine der am Primärschlüssel beteiligten Spalten NULL-Werte beinhalten.
Im Zuge des Erstellens eines Primärschlüssel wird automatisch von Oracle auf diese Spalte bzw. Spaltenkombination ein eindeutiger Index erstellt.
1.
2.
ALTER TABLE tabellenname
ADD CONSTRAINT constraintname
PRIMARY KEY datenfeldname;
ALTER TABLE tabellenname
DROP CONSTRAINT constraintname;
1. Die Anweisung wird mit den Schlüsselwörtern ALTER TABLE eingeleitet. Danach
folgt der Tabellenname. Mit ADD CONSTRAINT und PRIMARY KEY wird dann der
Primärschlüssel definiert.
2. Mit der Angabe DROP CONSTRAINT gefolgt vom Namen des Constraints wird ein
vorhandener Primärschlüssel gelöscht.
Beispiel:
1. ALTER TABLE employees
ADD CONSTRAINT empl_pk
PRIMARY KEY (employeeid);
54
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2. ALTER TABLE employees
DROP CONSTRAINT empl_pk;
Die Tabelle „employees“ wird verändert und ein neuer Primärschlüssel „empl_pk“ wird erstellt auf die Spalte Mitarbeiterkennung „employeeid“. Dies ist nur dann möglich, wenn es
noch keinen Primärschlüssel in dieser Tabelle gibt.
Fremdschlüssel erstellen und löschen:
Im relationalen Datenbankmodell werden die Datenbestände meist auf mehrere Tabellen
aufgeteilt, um sie möglichst effizient zu speichern und Redundanzen zu vermeiden. In einigen Tabellen werden dadurch nur Verweise auf die Primärschlüssel anderer Tabellen
gespeichert. Diese Verweise werden Fremdschlüssel (FOREIGN KEY) genannt.
ALTER TABLE tabellenname
ADD CONSTRAINT constraintname
FOREIGN KEY (spalte1, spalte2 ...)
REFERENCES tabellenname(spalte1, spalte2 ...);
Die Anweisung wird wieder mit den Schlüsselwörtern ALTER TABLE eingeleitet. Danach
folgt der Tabellenname.
Die ALTER TABLE-Anweisung erlaubt verschiedene Änderungen an der Tabellenstruktur.
Mit der Angabe ADD CONSTRAINT und FOREIGN KEY wird ein neuer Fremdschlüssel
definiert. Danach folgt in runden Klammern der Name des Datenfeldes in der aktuellen
Tabelle, das auf die Primärschlüssel einer anderen Tabelle verweist.
Danach folgt das Schlüsselwort REFERENCES und der Name der Tabelle, auf die verwiesen wird. In runden Klammern folgt der Name des Primärschlüsselfeldes der Tabelle.
Beispiel:
ALTER TABLE products
ADD CONSTRAINT prod_fk_cat
FOREIGN KEY (categoryid)
REFERENCES categories(categoryid);
Die Tabelle „products“ wird verändert und ein neuer Fremdschlüssel „empl_pk“ wird erstellt auf die Spalte Produktkennung „categoryid“ in der Tabelle „products“. Der Constraint sollte immer den
Namen beider Tabellen beinhalten mit denen er verbunden ist. Ebenso ein Kürzel um was für einen Constraint-Typ es sich handelt:
PK Ö PRIMARY KEY / FK Ö FOREIGN KEY / CK Ö CHECK
Constraints werden über folgende Sicht abgefragt:
SELECT table_name FROM dba_constraints;
55
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
10.2
Übungen: Constraints8
Im Folgenden werden Sie die Funktionsweise des Deaktivierens der verschiedenen Aktivierungszustände von Constraints kennen lernen. Arbeiten Sie folgende Übung mit dem
SQLPlus Worksheet durch.
1)
2)
Melden Sie sich an Ihrer Datenbank als sys an.
(Start Ö Programme Ö Oracle - OraHome92 Ö Application Development
Ö SQLPlus Worksheet).
Erstellen Sie eine Tabelle test1 im lokal verwalteten Tablespace test1 mit einer
Datendatei von 50 MByte. Die Tabelle soll eine Spalte name besitzen mit dem
Datentyp varchar(20):
CREATE TABLESPACE test1 DATAFILE ’c:\test1_dbf’ SIZE 50 M;
CREATE TABLE test1 (name VARCHAR(20)) TABLESPACE test1;
3)
Geben Sie folgende Namen in die Spalte name ein: Klaus, Carla, Berndt, Saskia.
INSERT INTO test1 VALUES (’Klaus’); etc.
4)
Erstellen Sie ein Check-Constraint für die Spalte name, der verhindern soll, dass
die Namen Carla und Berndt eingefügt werden können:
ALTER TABLE test1 ADD CONSTRAINT ck_name_test1
CHECK (name not in (’Anna’,’Bert’));
5)
Versuchen Sie nun den Namen Anna in die Tabelle einzufügen.
Was passiert und warum?
_________________________________________________________________
6)
Schalten Sie nun die Connstraint-Prüfung aus:
ALTER TABLE test1
DISABLE NOVALIDATE CONSTRAINT ck_name_test1;
7)
Versuchen Sie nun wiederum den Namen Anna in die Tabelle einzufügen.
Was passiert und warum?
_________________________________________________________________
8)
Schalten Sie die Constraint-Prüfung nun wieder ein. Aktivieren Sie sie so, dass
bestehende Datensätze nicht durch das Constraint geprüft werden:
ALTER TABLE test1
ENABLE NOVALIDATE CONSTRAINT ck_name_test1;
9)
Können Sie den Namen Bert nun in der Tabelle einfügen?
_________________________________________________________________
10) Löschen Sie den Constraint, die Tabelle und den Tablespace.
8
© FÖLLMER, R., Übungen (Schulung dama.go GmbH Potsdam: Database Administrator Oracle), 2004.
56
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
10.3
Indizes
Indizes erstellen:
Ein Index ähnelt dem Inhaltsverzeichnis eines Buches, in dem Suchbegriffe geordnet vorliegen und auf die entsprechenden Seiten des Buches verwiesen wird. Default-Index ist
immer der B*Tree- oder (B*Baum-)Index, welcher eine Baumstruktur hat, die einen direkten Zugriff auf ein Datensatz einer Tabelle ermöglicht. Indizes sollten immer in einem separaten Tablespace gespeichert werden, der auf einer separaten Platte liegen sollte.
CREATE INDEX indexname ON tabellenname (datenfeldname);
Die Anweisung zum erstellen eines neuen Index beginnt mit CREATE INDEX. Falls ein
absteigender Index gewünscht wird, kann die Anweisung CREATE DESC INDEX verwendet werden.
Danach folgt der gewünschte Indexname. Dieser Name wird nur zur internen Verwaltung
der Indizes verwendet. Über den Indexnamen kann ein Index später wieder gelöscht werden.
Nach dem Schlüsselwort ON werden der Name der Tabelle sowie in runden Klammern der
Name des Datenfeldes angegeben, für das der Index erzeugt werden soll.
Beispiel:
CREATE INDEX id_products
ON products (productid);
Beispiel für einen B*Tree-Index, der auf dem Datenfeld „productid“ der Tabelle „products“
angelegt worden ist um die Suchzeit nach bestimmten Produkten zu optimieren.
Indizes löschen:
Mit der DROP INDEX-Anweisung können Sie einen bestehenden Index löschen.
DROP INDEX indexname;
57
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
10.4
Übungen: Indizes
Im Folgenden werden Sie die Funktionsweise von Indizes und deren Verwendbarkeit kennen lernen. Arbeiten Se folgende Übung mit dem SQL*Plus Worksheet durch.
1)
Melden Sie sich an Ihrer Datenbank als sys an.
2)
Erstellen Sie eine Tabelle test2 im lokal verwalteten Tablespace test2 mit einer
Datendatei von 500 MByte. Die Tabelle soll eine Spalte name besitzen mit dem
Datentyp char(2000):
CREATE TABLESPACE test2 DATAFILE ’c:\test2_dbf’ SIZE 500 M;
CREATE TABLE test2 (name CHAR(2000)) TABLESPACE test2;
3)
Geben Sie folgendes Kommando ein, welches die Zeit in Minuten/Sekunden der
Befehlsausführung anzeigt:
SET TIMING ON;
4)
Geben Sie folgende Namen in die Spalte name ein: Klaus, Carla, Berndt, Saskia.
INSERT INTO test2 VALUES (’Klaus’); etc.
5)
Fügen Sie weitere Datensätze der Tabelle hinzu, indem Sie folgenden Befehl
ausführen:
INSERT INTO test2 SELECT * FROM test2;
Führen Sie diesen Befehl insgesamt 14 mal aus. Danach haben Sie ca. 80.000
Datensätze in Ihrer Tabelle.
6)
Fügen Sie einen weiteren Datensatz mit Bert als Namen ein:
INSERT INTO test2 VALUES (’Bert’);
7)
Sie werden nun eine Abfrage starten, bei der ein „full table scan“ durchgeführt
werden muss. Dabei wird die gesamte Tabelle nach dem entsprechenden
Datensatz durchsucht:
SELECT * FROM test2 WHERE name=’Bert’;
Wie lange benötigt die Abfrage zur Ausführung?
_________________________________________________________________
8)
Erstellen Sie nun einen normalen Index auf die Spalte name Ihrer Tabelle test2.
CREATE INDEX id_test2 ON test2 (name);
9)
Wiederholen Sie die in Punkt 7 getätigte Abfrage und notieren Sie sich die zur
Ausführung benötigte Zeit. Vergleichen Sie beide Zeiten und interpretieren Sie
das Ergebnis.
_________________________________________________________________
10) Löschen Sie den Index, die Tabelle und den Tablespace.
58
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
11
Funktionen in Abfragen
11.1
Standard-SQL-Funktionen
Aggregatfunktionen:
In vielen Fällen werden nicht die einzelnen Datensätze einer Abfrage benötigt, sondern
beispielsweise nur die Information über die Anzahl der ermittelten Datensätze oder über
einen Minimal- bzw. Maximalwert. Für diesen Zweck existieren in SQL Aggregatfunktionen, welche Berechnungen über alle oder ausgewählte Datensätze durchführen.
SELECT aggregatfunktionen(datenfeld), … FROM tabellenname;
Folgende Aggregatfunktionen stehen zur Verfügung:
Funktion:
COUNT( )
COUNT(DISTINCT)
AVG( )
MIN( )
MAX( )
SUM( )
Erklärung:
Liefert die Anzahl der Werte (außer den Wert
NULL) in der Ergebnismenge einer SELECTAbfrage bzw. einer Gruppierung.
Liefert die Anzahl der unterschiedlichen Werte
In einer Abfrage oder Gruppierung.
Liefert den Durchschnittswert eines
Datenfeldes einer Abfrage oder Gruppierung.
Liefert den kleinsten, bzw. größten Wert eines
Datenfeldes einer Abfrage oder Gruppierung.
Liefert die Summe der Werte eines
Datenfeldes in der Abfrage oder Gruppierung.
Beispiel:
SELECT COUNT(*)
FROM products;
SELECT
COUNT(DISTINCT lastname)
FROM employees;
SELECT AVG(unitprice)
FROM products;
SELECT MIN(unitprice)
FROM products;
SELECT SUM(unitprice)
FROM products
WHERE unitsinstock > 10;
In Abfragen können entweder nur Datenfelder oder nur Aggregatfunktionen angegeben werden. Eine Kombination wie im folgendem
Beispiel funktioniert nicht:
SELECT city, COUNT (lastname) FROM employees;
Wenn in einer SELECT-Abfrage eine GROUP BY-Anweisung angegeben wird, gruppiert
man die Datensätze der Tabelle anhand der Werte des angegebenen Datenfeldes. In
gruppierten SELECT-Abfragen ist eine Kombination von Datenfeldern und Aggregatfunktionen möglich. Die Aggregatfunktionen werden jeweils auf diese Gruppen angewendet.
Beispiel:
1. SELECT * FROM employees;
Datensätze de Tabelle „employees“ werden zurückgeliefert.
59
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2. SELECT COUNT(employeeid) FROM employees;
Durch Verwenden der Funktion COUNT() ohne GROUP BY wird
die Anzahl der Datensätze in der Tabelle ermittelt.
3. SELECT lastname, COUNT(employeeid) FROM employees
GROUP BY lastname;
Die Tabelle „employees“ wird mit Hilfe von GROUP BY nach
dem Datenfeld „lastname“ gruppiert. Für jeden dieser Gruppen
wird die Anzahl der Datensätze und damit die Anzahl der Mitarbeiter-Kennziffern pro Mitarbeiter ermittelt.
Aggregatfunktionen in Bedingungen:
Aggregatfunktionen können nicht in einer WHERE-Klausel verwendet werden. Eine Auswahl der Datensätze über das Ergebnis einer Aggregatfunktion ist jedoch mit Hilfe der
HAVING-Klausel möglich.
Beispiel:
SELECT productname, avg(unitprice) FROM products
GROUP BY productname HAVING avg(unitprice) > 75;
Durch das Hinzufügen der HAVING-Klausel werden nur die Projekte angezeigt, bei denen der Preis größer als 75 ist.
11.2
Nicht-standardisierte Funktionen
Um das Ergebnis einer Funktion zu erhalten, muss die SELECT-Abfrage verwendet werden. Im einfachsten Fall wird dabei der Funktionsaufruf nach dem Schlüsselwort SELECT
angegeben:
SELECT funktion();
Soll sich die Funktion auf den Datenbestand einer Tabelle beziehen, ist die Angabe der
FROM-Klausel notwendig. Der Funktionsaufruf erfolgt zwischen den Schlüsselwörtern
SELECT und FROM.
SELECT funktion( ) FROM tabellenname;
60
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Man kann auch Datenfelder und Funktionen mischen.
SELECT datenfeld1, … funktion() FROM tabellenname;
Auch kann man Funktionen in einer Bedingung anwenden.
SELECT datenfeld FROM tabellenname WHERE funktion()=wert;
Mathematische Funktionen die zur Auswahl stehen sind:
Funktion:
ABS(zahl)
CEIL()
FLOOR(zahl)
LOG(m,n)
MOD(zahl1, zahl2)
ROUND(zahl)
ROUND(zahl, stellen)
SIGN(zahl)
SIN(zahl)
COS(zahl)
TAN(zahl)
SQRT(zahl)
11.3
Erklärung:
Gibt den absoluten Wert einer Zahl wieder,
d.h. den Wert ohne Vorzeichen.
Rundet den Wert auf die nächste größere
Zahl.
Rundet auf die nächste kleinere Zahl.
Ermittelt den natürlichen Logarithmus zur
Basis e {vgl. „n“ Ö n=m(log(m,n))}
Liefert den Rest der Ganzzahldivision von
Zahl1 und Zahl2 {vgl. „2“ Ö 11/3 =3 Rest 2}
Rundet nach üblichen mathematischen
Regeln auf die ganze Zahl.
Rundet nach üblichen mathematischen
Regeln auf die angegebene Anzahl Dezimalstellen.
Ermittelt das Vorzeichen einer Zahl und
liefert daraufhin den Wert -1 oder 1
Berechnet
für
die
entsprechende
Winkelfunktion den angegebenen Wert.
Ermittelt die Quadratwurzel
übergebene Zahl.
für
die
Beispiel:
SELECT ABS(unitprice)
FROM products;
SELECT CEIL (3.22),
CEIL (-3.22) FROM dual;
SELECT FLOOR(unitprice)
FROM products;
SELECT LOG(7,3)
FROM dual;
SELECT MOD(11,3)
FROM dual;
SELECT ROUND(3.45)
FROM dual;
SELECT ROUND (3.456,2)
FROM dual;
SELECT SIGN(-4), SIGN(0),
SIGN(53) FROM dual;
SELECT SIN(5) FROM dual;
SELECT COS(3) FROM dual;
SELECT TAN(13) FROM dual;
SELECT SQRT(144)
FROM dual;
Übungen: Funktionen in Abfragen
(Lösung)
1.
Listen Sie alle Lieferanten auf, die mehr als 5 Produkte liefern!
2.
Listen Sie die Lieferanten mit Ihren jeweils teuersten Produkt auf. Es sollen nur die
Lieferanten angezeigt werden, bei denen das teuerste Produkt billiger als 10,- EURO
ist.
3.
Listen Sie den Nachnamen des Mitarbeiters auf, der am längsten angestellt war!
61
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
12 Datenabfragen über mehrere Tabellen
Datenbestände in mehreren Tabellen:
Bei relationalen Datenbanken werden die Daten in der Regel auf mehrere, logisch zusammengehörige Tabellen verteilt. In einer Abfrage müssen diese Daten so zusammengefügt werden, dass eine brauchbare Ergebnismenge entsteht. Eine wichtige Rolle spielen
dabei die Primär- und Fremdschlüssel, die die Verbindung von Datensätzen in mehreren
Tabellen vereinfachen. Die Verknüpfung von mehreren Tabellen miteinander ist ein wichtiger Aspekt des relationalen Datenbankmodells.
Die SELECT- Abfrage kann auf verschiedene Weise erweitert werden, um den Zugriff auf
mehrere Tabellen gleichzeitig zu ermöglichen. Diese Abfragen können sich auf zwei oder
auch mehr Tabellen beziehen.
Verknüpfungen von Tabellen über Mengenoperationen:
Die Datenbestände in den einzelnen Tabellen stellen Mengen von Datensätzen (Tupeln)
dar. Durch SQL-Anweisungen können diese unterschiedlich miteinander verknüpft werden. Voraussetzung für die Durchführung einer Mengenoperation ist, dass beide Tabellen
vereinigungskompatibel sind. Dies bedeutet, dass beide Tabellen die gleiche Struktur aufweisen müssen: Feldnamen und Wertebereiche müssen übereinstimmen. Wenn Sie zwei
oder mehr vereinigungskompatible Mengen addieren, erhalten Sie einen Datenbestand,
der alle Datensätze aus allen Tabellen erhält. Sie können auch Schnittmengen bilden, um
nur diejenigen Datensätze zu ermitteln, die in allen Tabellen vorhanden sind. Folgende
drei Mengenoperationen werden beim Verknüpfen von Tabellen am häufigsten eingesetzt:
Vereinigungsmenge:
Verbindung von zwei oder mehr Tabellen zu einem Datenbestand.
Beispiel: Es sollen alle Städte der Kunden und Lieferanten
der NORDWIND LTD. ausgegeben werden.
Schnittmenge:
Aus zwei oder mehr Mengen werden nur die Datensätze
herausgesucht, die in allen Mengen gleich sind.
Beispiel: Es sollen nur diejenigen Städte herausgesucht
werden, in denen sowohl Kunden als auch Lieferanten der
NORDWIND LTD. wohnen.
Differenzmenge:
Aus zwei oder mehr Mengen werden alle Datensätze ermittelt, die zwar in der einen Menge, jedoch nicht in der
anderen Menge enthalten sind.
Beispiel: Es sollen alle Städte der Kunden angezeigt werden, die nicht in der Lieferantentabelle vorhanden sind.
62
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
12.1
Verbund von Tabellen (Joins)
Der Verbund mehrerer Tabellen wird als Join (engl. Verbinden, vereinigen) bezeichnet.
Dazu werden jeweils ein oder mehrere Felder Tabellen miteinander verglichen. Die Datensätze, die der Vergleichsbedingung genügen, werden in die Ergebnistabelle aufgenommen. Es werden die Kundennamen mit dem jeweiligen zuständigen Kundenberater der
NORDWIND LTD. angezeigt und durch verschiedene Joins verbunden.
Nr:
0001
0002
0003
0005
Name:
King
Dovalio
Fuller
Callahan
Vorname:
Robert
Nancy
Andrew
Laura
Nr:
0001
0007
0010
Mitarbeiter-Tabelle „Employee“ der Nordwind Ltd
Natural-Join
Name:
King
Vorname: Nr:
Robert
0001
Name:
Wong
Von der ersten (linken) Tabelle werden alle Datensätze in
die Ergebnismenge aufgenommen. Von der zweiten (rechten) Tabelle werden nur die dazugehörigen Datensätze übernommen. Die Felder der zweiten Tabelle bleiben, wenn
kein passender Datensatz vorhanden ist.
Nr:
0001
0002
0003
0005
Right-Outer-Join
Rechte InklusionsVerknüpfung
Kunden-Tabelle „Customer“ der Nordwind Ltd.
Der Naural-Join arbeitet wie der Inner-Join – mit dem Unterschied, dass in der Ergebnistabelle keine identischen
Zeilen enthalten sind.
Nr:
0001
Left-Outer-Join
Linke InklusionsVerknüpfung
Name:
Wong
Ottlieb
Devon
Name:
King
Dovalio
Fuller
Callahan
Vorname:
Robert
Nancy
Andrew
Laura
Nr:
0001
NULL
NULL
NULL
Name:
Wong
NULL
NULL
NULL
Von der zweiten (rechten) Tabelle werden alle Datensätze
in die Ergebnismenge aufgenommen. Von der ersten (linken) Tabelle werden nur die dazugehörigen Datensätze
übernommen. Die Felder der ersten Tabelle bleiben leer,
wenn kein passender Datensatz vorhanden ist.
Nr:
0001
0002
0003
Name:
King
NULL
NULL
Vorname:
Robert
NULL
NULL
63
Nr:
0001
0007
0010
Name:
Wong
Ottlieb
Devon
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Full-Outer-Join =
Full-Join
Der Full-Join ist eine Kombination aus dem Left-Outer-Join
und dem Right-Outer-Join. Alle Datensätze beider Tabellen
werden in die Ergebnismenge übernommen. Passen Datensätze aus beiden Tabellen laut Vergleichsoperator zusammen, so werden diese verbunden.
Nr:
0001
0002
0003
0005
NULL
NULL
Semi-Join
Vorname:
Robert
Nancy
Andrew
Laura
NULL
NULL
Nr:
0001
NULL
NULL
NULL
0007
0010
Name:
Wong
NULL
NULL
NULL
Ottlieb
Devon
Um einen Semi-Join9 zu erhalten, werden zwei Tabellen
über einen Natural-Join verbunden. Anschließend erfolgt
eine Projektion auf die Spalte der ersten Tabelle.
Nr:
0001
Self-Join
Name:
King
Dovalio
Fuller
Callahan
NULL
NULL
Name:
King
Vorname:
Robert
Der Self-Join ist einer der zuvor genannten Joins, bei dem
nicht zwei verschiedene Tabellen miteinander verbunden
werden, sondern zweimal dieselbe.
Nr:
0001
0002
0003
0005
Name:
King
Dovalio
Fuller
Callahan
Vorname:
Robert
Nancy
Andrew
Laura
Chef:
NULL
0001
0002
0003
Mitarbeiter-Tabelle „Employees“ um eine Spalte erweitert.
Nr:
0001
0002
0003
0005
Name:
King
Dovalio
Fuller
Callahan
Vorname:
Robert
Nancy
Andrew
Laura
Chef:
NULL
King
Dovalio
Fuller
Self-Join auf Mitarbeiter-Tabelle.
Cartesien-Join
oder
Cross-Join10
9
10
Mit diesem Verbund wird das kartesische Produkt beider
Tabellen gebildet. Das heißt, jeder Datensatz der einen Tabelle wird mit jedem Datensatz der anderen Tabelle kombiniert.
Wird bei einem Join auf eine Nicht-Übereinstimmung geprüft, dann spricht man von einem Anti-Join.
Z.B. SELECT a.food FROM table1 a WHERE NOT EXISTS (SELECT * FROM table2 b WHERE a.food
= b.food);
Es gibt drei Hauptklassen von Joins: Natural-Join, Anti-Join (z.B. Anti-Semi-Join), und Cartesien-Join.
Alle anderen fünf Typen von Joins sind Spezialformen von diesen.
64
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Theta-Join
Es werden bestimmte Datensätze aus dem kartesischen
Produkt zweier Tabellen durch eine Bedingung ausgewählt.
In dieser Bedingung wird eine Spalte aus der einen und
eine Spalte aus der anderen Tabelle über eine logische
Operation verglichen, z.B. „Employee“ Nr < „Customer“
Nr. Wird auf Gleichheit geprüft, so erhalten Sie eine Spezialform des Theta-Join, den Equi-Join. Für das Beispiel
„Employee“ Nr < „Customer“ Nr erhalten Sie folgende
Ergebnistabelle:
Nr:
0001
0001
0002
0002
0003
0003
0005
0005
Inner-Join =
Equi-Join
Name:
King
King
Dovalio
Dovalio
Fuller
Fuller
Callahan
Callahan
Vorname:
Robert
Robert
Nancy
Nancy
Andrew
Andrew
Laura
Laura
Nr:
0007
0010
0007
0010
0007
0010
0007
0010
Name:
Ottlieb
Devon
Ottlieb
Devon
Ottlieb
Devon
Ottlieb
Devon
Die Datensätze aus beiden Tabellen werden verbunden,
wenn ein oder mehrere gemeinsame Felder den gleichen
Wert haben (deshalb auch Equi-Join – äquivalent). Dieser
Join ist der am häufigsten verwendete Verbund. Für das
Beispiel „Employees“ Nr = „Customers“ Nr erhalten Sie
folgende Ergebnistabelle.
Nr:
0001
Name:
King
Vorname: Nr:
Robert
0001
Name:
Wong
Einfaches Verknüpfen von Tabellen:
Durch eine SELECT-Anweisung können zwei oder mehrere Tabellen verbunden werden,
indem Felder aus mehreren Tabellen selektiert werden. Dazu lassen sich in der WHEREKlausel Bedingungen für die Verknüpfung der beteiligten Tabellen angeben. Ist die Bedingung für zwei Datensätze der beteiligten Tabellen erfüllt, so werden diese miteinander
verbunden.
Wenn in der WHERE-Klausel keine Felder angegeben werden, erhält man einen Full-Join.
SELECT datenfeldliste FROM tabelle1, tabelle2, …
WHERE tabelle1.datenfeld = tabelle2.datenfeld [AND …];
65
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Beispiel:
1. SELECT employee.lastname, employee.firstname,
employee.employeeid,
customert.customerid, customer.lastname
FROM employee, customer
WHERE employee.employeeid = customer.customerid;
Die zwei Tabellen „employee“ und „customer“ werden verknüpft, sodass ein einfacher
Equi-Join entsteht. Die Ergebnismenge beinhaltet alle Datensätze, in denen die Felder
„employeeid“ und „customerid“ der beiden Tabellen die gleichen Werte besitzen.
Beispiel:
SELECT * FROM tabelle1, tabelle2 …
SELECT tabelle1.feld1, tabelle1.feld2, tabelle2.* …
SELECT tabelle1.*, tabelle2.* …
Beispiel:
Erklärung:
Liefert alle Felder aus allen beteiligten Tabellen.
Liefert die angegebenen Felder der Tabelle „tabelle1“ und alle Felder der Tabelle „tabelle2“.
Gleichbedeutend mit der Angabe SELECT *.
2. SELECT employee.lastname, employee.firstname,
employee.employeeid, customer.customerid,
customer.lastname, customer.city
FROM employee, customer
WHERE employee.employeeid = customer.customerid
AND customer.city = ’London’;
Auswahl über zwei Tabellen mit einer zusätzlichen Bedingung.
66
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Es werden zwei Tabellen verknüpft und die Datensätze durch eine zusätzliche Bedingung
gefiltert.
Ersatznamen für Tabellen11 mit dem Schlüsselwort AS definieren:
SELECT e.lastname, e.firstname, e.employeeid, c.*
FROM employee AS e, customer AS c
WHERE e.employeeid = c.customerid;
Ersatznamen für Tabellen ohne das Schlüsselwort AS definieren:
SELECT e.lastname, e.firstname, e.employeeid, c.*
FROM employee AS e, customer AS c
WHERE e.employeeid = c.customerid;
Full-Join:
Beim Full-Join oder Cross-Join wird das kartesische Produkt aus beiden Tabellen gebildet.
SELECT datenfelder FROM tabelle1 CROSS JOIN tabelle2;
Beispiel:
SELECT e.lastname, e.employeeid, p.*
FROM employee e CROSS JOIN project p
WHERE e.projnr = p.projnr;
Gibt an, welche Mitarbeiter an welchen Projekten mitarbeiten.
11
Beispieltabellen, welche in Anlehnung der Nordwind-Datenbank benannt worden sind.
Vorsicht Verwechselungsgefahr !
67
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Inner-Join (Equi-Join):
SELECT datenfeldliste
FROM tabelle1 INNER JOIN tabelle2
ON tabelle1.datenfeld = tabelle2.datenfeld [WHERE …];
Beispiel:
SELECT e.lastname, e.deptno, d.*
FROM employee e INNER JOIN dept d
ON e.deptno = d.deptno;
Die zwei Tabellen sollen verknüpft werden.
68
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Inner-Join über mehr als zwei Tabellen:
SELECT datenfelder
FROM (
(tabelle1 INNER JOIN tabelle2 ON bedingung)
INNER JOIN tabelle3 ON bedingung
);
Beispiel:
SELECT e.lastname, p.description, p.end_time
FROM emp_proj ep INNER JOIN employee e
ON ep.employeeid = e.employeeid;
INNER JOIN project p
ON ep.projnr = p.projnr
ORDER BY p.description;
Es soll ermittelt werden, welche Mitarbeiter an welchen Projekten beteiligt sind. Die Namen der Mitarbeiter sind in der Tabelle „employee“ gespeichert, die Namen der Projekte in
der Tabelle „project“. Da ein Mitarbeiter in mehreren Projekten tätig sein kann, ist eine zusätzliche Tabelle „emp_proj“ notwendig, um die Beziehungen zwischen den Projekten und
den Mitarbeitern herzustellen.
Natural Join:
Beispiel:
SELECT DISTINCT e.lastname, e.deptno, d.*
FROM employee e NATURAL | INNER JOIN dept d
ON e.deptno = d.deptno;
Ein Natural-Join ist ein Inner-Join, der gleiche Datensätze nur einmal enthält. Er lässt sich
auf einfache Art erzeugen, indem der SELECT-Anweisung das Schlüsselwort DISTINCT
hinzugefügt wird.
69
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Theta-Join:
Beispiel:
SELECT e.lastname, e.firstname, e.employeeid,
c.customerid, c.lastname, c.firstname
FROM employee e INNER JOIN customer c
ON e.employeeid < c.customerid;
Beim Theta-Join wird in der ON-Klausel nicht auf Gleichheit geprüft, sondern ein anderer
logischer Operator eingesetzt (Operatoren: >, <, >=, <=, <>). In unserem Bespiel wird der
Kunde „Wong“ nicht angezeigt, da er die Zahl „1“ als Kundennummer hat, welche ebenfalls Mitarbeiterkennung vom Präsident King ist.
Outer-Join:
SELECT datenfeldliste
FROM tabelle1 LEFT | RIGT OUTER JOIN tabelle2
ON bedingung;
Beispiel:
SELECT e.lastname, e.firstname, d.description
FROM employee e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
ORDER BY e.lastname;
70
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Hiermit wird eine Mitarbeiterliste erzeugt, die für jeden Mitarbeiter die entsprechende Abteilung enthält. Zwei Mitarbeiter (Buchanan und Dodsworth) sind keiner Abteilung zugeordnet.
Mitarbeiterliste als Ergebnis des Outer-Joins.
Self-Join:
SELECT e1.lastname, e2.lastname, e1.city
FROM employee e1 INNER JOIN employee e2
ON e1.city = e2.city WHERE e1.deptno <> e2.deptno;
Der Self-Join ist dann sinnvoll, wenn sich ein Feld einer Tabelle auf ein anderes Feld in
der gleichen Tabelle bezieht.
In der Tabelle „employee“ werden die Mitarbeiternamen und die jeweiligen Orte gespeichert. Der Self-Join wird verwendet um alle Mitarbeiter herauszufinden, die einer Abteilung
mitarbeiten und deren Standorte.
71
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
12.2
Tabellen vereinigen
Mit einem Join verknüpft man zwei oder mehr Tabellen, sodass bestimmte Datensätze aus
den verschiedenen Tabellen miteinander kombiniert werden.
Man kann auch zwei Abfragen vereinigen, sodass eine Ergebnismenge entsteht, die sowohl Datensätze der ersten als auch der zweiten Abfrage enthält.
SELECT e.lastname, e.firstname
FROM employee e
WHERE e.city = ’London’
UNION SELECT c.lastname, c.firstname
FROM customer c;
Die erste SELECT-Anweisung ermittelt die Datensätze aus der Tabelle „employee“, bei
denen der Ort London ist. Es wurden nur die Felder „lastname“ und „firstname“ projiziert.
Über das Schlüsselwort UNION wird die Ergebnismenge der zweiten Abfrage angefügt.
Die zweite Abfrage liefert die Felder „lastname“ und „firstname“ der Tabelle „customer“
aus.
Es werden alle Mitarbeiter und Kunden der NORDWIND LTD. aus den Tabellen „employee“
und „customer“ in einer Abfrage vereinigt, welche in London wohnen.
72
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
12.3
Übungen: Datenabfragen über mehrere Tabellen (Joins)
(Lösung)
1.
Welche Kunden „contactname“ haben Produkte der Kategorie „beverages“ bestellt.
Benutzen Sie NATURAL JOINs, soweit es möglich ist.
2.
Welcher Kunde hat noch nie etwas bestellt? Benutzen Sie einen OUTER JOIN.
3.
Welche Kunden wurden bisher von Speedy Express beliefert? Benutzen Sie INNER
JOIN und NATURAL JOIN, soweit es möglich ist. Erstellen Sie eine Hilfs-View hierfür.
4.
Erstellen Sie eine Abfrage, die Ihnen rechts den Angestellten und links den Vorgesetzten ausgibt. Der Chef sollte in der Spalte Vorgesetzter „BOSS“ zu stehen haben.
5.
Schreiben Sie eine Abfrage, die Ihnen die Kontaktnamen der Lieferanten ausgibt, die
Produkte der Kategorie 1 oder 2 oder 3 liefern!
6.
Schreiben Sie eine Abfrage, die Ihnen die Kundennamen, die entsprechenden Bestellnummern „orderid“ und den Namen des Angestellten „employees“, der die Bestellung bearbeitet hat, ausgibt. Es soll nach dem „contactname“ geordnet sein!
7.
Was machen folgende Abfragen?
a)
SELECT lastname
FROM employees GROUP BY city;
b)
SELECT discontinued, sum(unitprice*unitsinstock)
FROM products
GROUP BY discontinued;
c)
SELECT sum(unitprice*unitsinstock)
FROM products
GROUP BY supplierid;
73
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
13
Sichten
Abfragen sind eine Möglichkeit, die benötigten Daten bereitzustellen. Sie müssen aber
immer wieder neu eingegeben werden. Um diese sich wiederholende Arbeit einzusparen,
kann man vordefinierte Abfragen, sogenannte Sichten (engl. Views) verwenden. Sichten
sind gespeicherte SELECT-Anweisungen, die bis auf ihre Definition keinen zusätzlichen
Speicherplatz benötigen. Sie stellen Ergebnismengen von Abfragen dar, werden aber wie
Tabellen verwendet. Man kann Daten aus vorhandenen Sichten abfragen und teilweise
auch Datensätze mit Hilfe von Sichten aktualisieren bzw. neu einfügen.
13.1
Sichten erstellen und löschen
Sichten lassen sich mit der SQL-Anweisung CREATE VIEW erstellen. Dabei wird ein Name eingegeben, über den man im weiteren Verlauf auf die Sicht zugreifen kann.
CREATE VIEW viewname AS
SELECT abfrageanweisung;
Sichten werden mit der Anweisung DROP VIEW gelöscht. Danach folg der Name der
Sicht. Es wird dabei ausschließlich die Definition der Sicht gelöscht und nicht die zugrunde
liegenden Daten der Tabelle.
DROP VIEW viewname;
13.2
Daten über Sichten einfügen und ändern
Unter bestimmten Voraussetzungen können Sichten auch zum Eingeben und Ändern von
Daten verwendet werden. Die INSERT-, UPDATE- und DELETE-Anweisungen sind jedoch nur in folgenden Fällen erlaubt:
¾
In SELECT-Anweisungen der Sicht wird nicht das Schlüsselwort DISTINCT verwendet.
¾
Die Sicht bezieht sich nur auf eine Tabelle. Joins sind beim Ändern von Daten
nicht gestattet.
¾
Die Abfrage verwendet keine Unterabfragen in der Bedingung.
74
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Beispiel:
1.
2.
3.
CREATE VIEW exployeeview AS
SELECT employeeid, lastname, firstname
FROM employees;
UPDATE employeesview SET lastname=’Meyer’
WHERE employeesid=20
INSERT INTO employeesview (employeeid, lastname, firstname)
VALUES (20,’Meyer’,’Timo’);
1. Die Sicht „employeesview“ wird erstellt und enthält die Felder „employeeid“,
„lastname“ und „firstname“ aus der Tabelle „employees“.
2. Mit der UPDATE-Anweisung wird in dem Datensatz mit der „employeeid“
gleich 20 der Name auf Meyer gesetzt, falls diese vorhanden.
3. Ein neuer Datensatz wird mit der INSERT INTO-Anweisung eingefügt.
Sichten werden nicht von allen SQL-Dialekten unterstützt !!!
75
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
14
Zugriffsrechte und Benutzer verwalten
Die Benutzerverwaltung übernimmt in der Regel der Datenbank- oder der Systemadministrator. Er verfügt über die meisten Rechte bzw. Privilegien im System und kann diese anderen Benutzern (engl. user) zuweisen oder entziehen.
Für den Datenbankadministrator wird beim Installieren des Datenbanksystems automatisch ein Benutzerkonto erstellt. Dieser Benutzer hat ein vom verwendeten Datenbanksystem abhängigen Namen.
Datenbanksystem:
Interbase
Microsoft SQL-Server
MySQL
ORACLE
14.1
Benutzername des Administrators:
SYSDBA
sa
Root
1) sys
2) system
Standard-Passwort:
Masterkey
–
–
change_on_install
manager
Benutzer und Profile anlegen und verwalten
Benutzer anlegen und verwalten:
In Oracle besteht genau wie in vielen anderen Datenbanksystemen, die Benutzer und Rollen mit Hilfe spezieller SQL-Anweisungen anzulegen bzw. zu bearbeiten. Das schöne dabei ist, dass man sich hierfür kaum spezielle Befehle merken muss, denn ähnlich wie das
Anlegen, das Ändern oder Löschen von sonstigen Datenbankobjekten beginnen die Befehle zur entsprechenden Benutzerbearbeitung auch mit CREATE, ALTER und DROP.
Beginnen wir damit einen neuen Benutzer in der Datenbank anzulegen, was Sie mit Hilfe
der CREATE USER-Anweisung erledigen können. Neben der Nennung der neuen Benutzkennung sollten Sie hierbei wenigsten auch die IDENTIFIED BY-Klausel zur Vorgabe
des zugehörigen Passworts verwenden.
CREATE USER chef IDENTIFIED BY12 manager
DEFAULT TABLESPACE13 usr
TEMPORARY TABLESPACE14 temporary
PROFILE15 default ACCOUNT16 unlock
QUOTA unlimited ON usr
QUOTA unlimited ON indx
QUOTA unlimited ON temporary;
12
13
14
15
16
identified by
default tablespace
temporary tablespace
profile
account
:
:
:
:
:
Password für Benutzer vergeben
Legt einen Standard-Tablespace fest (Standard „system“).
Speichert temporäre Objekte (z.B. Zwischenergebnisse)
Name des zugeordneten Profils (z.B. default“)
Freigabe oder Sperren des Benutzerprofils.
76
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Löschen können Sie ein nicht mehr benötigtes Benutzerkonto mit Hilfe der DROP USERAnweisung , der wenigstens der Name der zu löschenden Benutzer-ID folgt.
DROP USER chef CASCADE;
Die zusätzliche CASCADE-Klausel sorgt dafür, dass das Löschen auch dann funktioniert,
wenn der Benutzer Eigentümer irgendwelche Datenbankobjekte, beispielsweise Tabellen
ist, denn aufgrund dieser Klausel werden seine Objekte ebenfalls aus der Datenbank entfernt.
Profile anlegen und verwalten:
Auch Benutzerprofile können Sie mit Hilfe von SQL-Anweisungen anlegen, ändern oder
löschen. Das folgende Beispiel zeigt Ihnen, wie einfaches Profil mit dem Namen „aushilfe“
erstellt wird.
CREATE PROFILE aushilfe limit
SESSIONS_PER_USER 1
PRIVATE_SGA 200K;
Das neue Profil enthält einmal abgesehen von zwei Ausnahmen für alle anderen Standardeinstellungen. Die Anlage des neuen Profils erfolgt durch die Anweisung CREATE
PROFILE, auf die der Name des neuen Profils und anschließend das Schlüsselwort „limit“
erfolgt. Danach erfolgt die Aufzählung der eingeschränkten Parameter, wobei ich in unserem Beispiel vor allem dafür gesorgt wurde, dass sich Benutzer mit diesem Profil nur einmal an der Datenbank anmelden können.
Genau wie beim Anlegen neuer Benutzer haben Sie auch bei den Profilen die Möglichkeit,
ein Profil zunächst einmal anzulegen und die einzelnen Parameter in einem zweiten
Schritt durch entsprechende ALTER PROFILE-Befehle zu verändern.
ALTER PROFIL aushilfe limit
FAILES_LOGIN_ATTEMPTS 5
PASSWORD_LOG_TIME 1;
Das letzte Beispiel kontrolliert die Anzahl der fehlerhaften Anmeldeversuche und sperrt
den Benutzer nach fünf vergeblichen Versuchen für einen Tag.
77
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
14.2
Zugriffsrechte an Benutzer vergeben
Mit der Anweisung GRANT werden Rechte für verschiedene SQL-Anweisungen vergeben,
die sich auf ein ausgewähltes oder alle Datenbankobjekte beziehen. So kann beispielsweise einem Benutzer schreibender Zugriff auf eine bestimmte Tabelle ermöglicht werden
und lesender Zugriff auf alle Tabellen und Datenbanken erlaubt werden. Standardmäßig
können Benutzerrechte nur vom Datenbankadministrator vergeben werden.
GRANT rechteliste ON datenbankobjekt TO benutzername;
Beispiel:
1.
2.
3.
GRANT ALL ON employees TO king;
GRANT SELECT, UPDATE ON products TO dovalio;
GRANT SELECT ON employees TO callahan;
1. Benutzer „king“ erhällt alle Rechte für die Tabelle „employees“.
2. Dem Benutzer „dovalio“ wird das Ausführen der SELECT- und UPDATEAnweisung für die Tabelle „products“ gestattet.
4. Der Benutzer „callahan“ erhält an dieser Stelle Leserechte für die Tabelle
„employees“.
Folgende Rechte können mit der GRANT-Anweisung vergeben werden:
Privileg:
ALL
INSERT
REVOKE
SELECT
UPDATE
REFERENCES
Erklärung:
Gewährt alle Rechte für das entsprechende Datenbankobjekt.
Recht zum Einfügen neuer Datensätze, Ausführen der INSERT-Anweisung.
Recht zum Löschen von Datensätzen, Ausführen der REVOKE-Anweisung.
Leserecht, Recht zum Ausführen der SELECT-Anweisung.
Recht zum Ändern von Datensätzen, Ausführen der UPDATE-Anweisung.
Recht zum Definieren von Regeln für die referentielle Integrität.
Seien Sie vorsichtig mit der Vergabe von Rechten und Privilegien.
Oft reicht eine SELECT-Zuzuweisen, damit der Benutzer alle seine
Aufgaben erfüllen kann !!!
78
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
14.3
Übungen: Verwaltung von Zugriffsrechten und Benutzern
In der folgenden Übung werden Sie zwei Benutzer namens „anna“ und „berta“ erstellen.
1)
2)
3)
4)
5)
6)
7)
8)
Öffnen Sie die Oracle Enterprise Manager-Konsole (OEM).
(Start Ö Programme Ö Oracle - OraHome92 Ö Enterprise Manager Console).
Wählen Sie Standalone starten und klicken Sie auf OK.
Erweitern Sie die Datenbank testdbx. Melden Sie sich an testdb01 mit Benutzernamen SYS und dem Kennwort sys als SYSDBA (Anmelden als) an.
Erweitern Sie Sicherheit und führen Sie einen Rechtsklick auf Benutzer aus.
Wählen Sie Erstellen.
Geben Sie unter Name: anna unter Kennwort ebenfalls anna.
Klicken Sie auf Erstellen und anschließend auf OK.
Melden Sie sich unter sqlplus als anna@testdbx an.
Geben Sie bei Aufforderung das Kennwort anna ein. Was passiert und warum?
_________________________________________________________________
_________________________________________________________________
Versuchen Sie, als Anna eine Tabelle zu erstellen. Geben Sie hierfür unter
SQLPLUS create table annatest(s1 int); ein und schließen Sie diese Anweisung
mit ENTER ab. Was passiert und warum?
_________________________________________________________________
_________________________________________________________________
9) Melden Sie sich bei SQLPLUS wieder ab. Geben Sie hierfür exit ein.
10) Im folgenden werden Sie anna unbegrenzten Speicherplatz auf dem Tablespace
users geben. Klicken Sie hierfür auf den Benutzer anna im OEM. Wählen Sie nun
die Registerkarte quota aus. Klicken Sie auf users und unten auf Unbegrenzt.
Klicken Sie nun auf Anwenden.
11) Versuchen Sie, als Anna eine Tabelle zu erstellen. Geben Sie hierfür unter
SQLPLUS create table annatest(s1 int); ein und schließen Sie diese Anweisung
Mit ENTER ab. Was passiert und warum?
_________________________________________________________________
_________________________________________________________________
12) Fügen Sie zwei Datensätze in die Tabelle annatest hinzu und die
Daten festschreiben. Geben Sie hierfür in SQLPLUS folgendes ein:
INSERT INTO annatest VALUES(1);
INSERT INTO annatest VALUES(2);
COMMIT;
13) Erstellen Sie selbstständig einen Benutzer berta als berta eine Tabelle namens
bertatest. Geben Sie Berta das Recht die Tabelle annatest abzufragen. Testen
Sie dies anschließend selbständig mit einer SELECT-Anweisung:
OEM : Berta Ö Objekt Ö Tabelle Ö Annatest zu Berta mit SELECT hinzufügen.
79
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
15
Anhang
15.1
Glossar
IT-Fachbegriff:
Erklärung:
3-Ebenen-Modell
(auch 3-Ebenen-Architektur)
nach ANSI-SPARC:
Dieses Modell besteht aus drei unterschiedlichen Abstraktionsebenen für die Darstellung des Datenbankschemas: der internen,
der konzeptionellen und der externen Ebene. Auf jeder Ebene wird eine andere
Sichtweise auf die Daten verwendet: die
physische Datenorganisation, die logische
Gesamtsicht und die logische Benutzersicht
der Daten.
Aggregatfunktion:
Funktionen, die der statistischen Auswertung der Werte eines Datenfeldes oder einer Gruppe innerhalb einer Abfrage dienen.
Archiver (ARCn):
Der Archiver Prozess sichert die OnlineRedo-Log-Datei, die gerade nicht aktuell ist.
Dies geschieht nur, wenn sich die Datenbank im ArchiveLog-Modus (es werden die
jeweils aktuellen Redo-Log-Dateien gesichert) befindet und der Archiver-Prozess
gestartet wurde. Der Archiver-Prozess wird
gestartet, indem die Anweisung „ARCHIVE
LOG START“ ausgeführt wird. Der Archiver-Prozess kann auch automatisch beim
Hochfahren der Datenbank gestartet werden.
Hierfür
muss
der
Eintrag
„LOG_ARCHIVE_START = TRUE“ in die
entsprechende INI-Datei eingefügt werden.
Attribut:
Spalte einer Relation, Eigenschaft einer
Entität oder Beziehung im ER-Modell.
Checkpoint-Prozess (CKPT):
Der Checkpoint stellt sicher, dass nach Abschluss des Checkpoint alle Datenänderungen aus Transaktionen, die zum Zeitpunkt des Checkpoint „commited“ waren, in
die entsprechenden Datendateien auf der
Platte geschrieben wurden.
80
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
IT-Fachbegriff:
Erklärung:
Client-Server-DBS:
Die meisten heutigen Datenbanksysteme
arbeiten nach dem Client-Server-Konzept,
bei dem der Datenbank-Server seinen
Clients Dienste zur Verfügung stellt. Beispielsweise werden Datenbank-Abfragen
an den Datenbank-Server gerichtet und
dieser liefert die entsprechenden Daten an
den Client zurück.
Commit:
Speichern und festschreiben der Daten in
der Datenbank.
Database-Buffer-Cache:
Einer der wichtigsten Bestandteile der SGA
(System-Speicherstruktur). Immer, wenn
Daten gelesen oder geändert werden,
schaut Oracle nach, ob die entsprechenden
Blöcke sich schon im Database-BufferCache befinden. Ansonsten lädt Oracle die
beteiligten Blöcke in den Cache wegen
schnellerem Zugriff und speicher diese dort
bis ein „Commit“ erfolgt.
Data-Dictionary-Cache:
(auch: Row-Cache)
Speichert Informationen über die Datenbank (Sichten, Datenbank-Schema etc.)
und deren Verwaltung (Zugriffsrechte etc.).
Database-Writer-Prozess (DBWn):
Der Prozess DatabaseWriter schreibt die
veränderten Blöcke („Dirty Blocks“) aus
dem Database-Buffer-Cache in die entsprechenden Datendateien. Dadurch werden die Dirty-Buffers wieder Free-Buffers
und können andere Blöcke zwischenspeichern. Unter folgenden Situationen schreibt
der DBW-Prozess Dirtys-Blocks in die Datendateien:
- Auftreten eines Checkpoint
- Ein anderer Block soll im Cache gespeichert werden und es findet sich kein freier
Block.
Datenbank (DB):
a) Sammlung logisch-zusammengehöriger
Daten, die physische zusammenhängend
auf einem externen permanenten Speichermedium abgelegt sind.
81
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
IT-Fachbegriff:
Erklärung:
Datenbank (DB):
b) Oberstes Objekt in der Datenbankhierarchie, welches einen Container für die darin
gespeicherten Tabellen bzw. weiteren Datenbankobjekte darstellt.
Datenbankmanagementsystem (DBMS):
Datenbanksoftware, mit der die Daten der
Datenbank und die Datenbank selbst erstellt, bearbeitet, verwaltet und gepflegt
werden.
Datenbanksicht:
Unterschiedliche Betrachtungsweisen einer
Datenbank.
Datenbanksystem (DBS):
Kombination aus den Datenbanken und
dem Datenbankmanagementsystem.
Datenfeld (Attribut):
Entspricht der Spalte einer Tabelle und
enthält Werte für alle dazugehörigen Datenfelder.
Datensatz (Tupel):
Entspricht der Zeile einer Tabelle und enthält die Werte für alle dazugehörigen Datenfelder.
Datenmodell:
Hilfsmittel zur Abstraktion der Daten aus
der realen Welt. Es wird eine Struktur aus
den relevanten Daten – deren Beziehungen
und Bedingungen – erzeugt.
Datentyp:
Durch den Datentyp wird festgelegt, welche
Art von Daten (Zahlen, Zeichenketten etc.)
in einem Feld gespeichert werden können.
Datenunabhängigkeit:
Die Anwendungsprogramme und die physische Speicherung der verwendeten Daten
sind voneinander unabhängig. Die Datenverwaltung wird von einem anderen Programm (z.B. DBMS) übernommen.
Entity:
Entität, Datensatz, Tupel, Zeile einer Tabelle.
ER-Modell:
Entity-Relationship-Modell: Hilfsmittel zur
grafischen Darstellung eines Datenbankentwurfs.
82
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
IT-Fachbegriff:
Erklärung:
Fremdschlüssel:
a) Referenziert den Primärschlüssel einer
anderen Tabelle. Über Fremdschlüssel wird
eine Beziehung zwischen zwei Tabellen
hergestellt.
b) Ein Fremdschlüssel ist ein Attribut einer
Relation, das einer anderen Relation als
Primärschlüssel dient. Mit Hilfe von Fremdschlüsseln lassen sich Beziehungen zwischen Relationen herstellen.
Hierarchisches DBS:
Die Daten werden nach streng hierarchischem Prinzip in einer baumartigen Struktur
abgelegt.
Index:
a) Ein Attribut bzw. eine Attributkombination
einer Relation wird als Index gespeichert,
um z.B. Sortierfunktionen schneller durchführen zu können.
b) Für ein oder mehrere Datenfelder angelegtes Inhaltsverzeichnis (in Form eines B*Baumes) zum beschleunigten Filtern, Gruppieren oder Sortieren einer Tabelle.
Integrität:
Liegt vor, wenn Daten in sich richtig (stimmig), widerspruchsfrei und vollständig sind
(logische Integrität Ö siehe Konsistenz).
Integritätsbedingungen:
Integritätsbedingungen sind Bestimmungen, die eingehalten werden müssen, um
die Korrektheit und die logische Richtigkeit
der Daten zu sichern.
Konsistenz/Inkonsistenz:
Konsistenz (referentielle Integrität) ist die
Übereinstimmung von mehrfach gespeicherten Daten. Werden bei Änderungen
nicht alle mehrfach gespeicherten Daten
geändert, ist der Datenbestand inkonsistent, d.h., es existieren unterschiedliche
Versionsstände der gleichen Daten.
Large Pool:
Durch den Large-Pool kann der Administrator für bestimmte Operationen Speicher
reservieren.
83
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
IT-Fachbegriff:
Erklärung:
Log-Writer-Prozess (LGWR):
Der LogWriter-Prozess schreibt den Inhalt
des RedoLog-Buffers in die entsprechende
Online-RedoLog-Datei auf der Festplatte.
Dieser Prozess tritt in folgenden Situationen
auf:
- Nach einem „Commit“
- Alle drei Sekunden
- RedoLog-Buffer ist zu einem Drittel gefüllt
- DBW schreibt „Dirty Blocks“ vom Databa
se-Buffer-Cache auf die Festplatte.
Netzwerk-DBS:
Gleichartige Daten werden in Recordsets
abgelegt, die Beziehung zwischen den Daten in Sets. In der grafischen Darstellung
entsteht ein gerichteter Graph, der als
Netzwerk bezeichnet wird.
Normalform (1.-5.):
Zustand, in dem sich ein Datenbankschema
bezüglich der Normalisierung befindet.
Normalisierung:
Methode zur Erreichung einer redundanzfreien Datenspeicherung in den Relationen
eines Datenbankschemas.
Paralleles DBS:
Läuft auf Parallelrechnern oder Multiprozessorsystemen. Die (eigentliche) Parallelarbeit wird durch den von Parallelrechnern
oder die Anwendung paralleler Algorithmen,
die gleichzeitig auf mehreren Prozessoren
ausgeführt werden (z.B. Multiprozessorsystemen), erreicht. Dadurch werden die Antwortzeiten bei Datenbank-Anfragen und
Transaktionen verkürzt.
Prozess-Monitor (PMON):
Der Prozess Monitor gibt alle Ressourcen,
Sperren und Transaktionen von abgebrochenen oder fehlgeschlagenen Benutzprozessen wieder frei.
Redundanz:
Mehrfache Speicherung von gleichen Daten
an verschiedenen Orten. Dadurch erhöht
sich das Risiko inkonsistenter Daten.
84
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
IT-Fachbegriff:
Erklärung:
Redo-Log-Buffer:
RedoLog-Verkehr wird primär beim Einfügen, Ändern oder Löschen von Daten erzeugt. RedoLog-Einträge werden durch
einen Prozess namens LGWR vom Cache
in die RedoLog-Dateien geschrieben.
Relation:
Geordnete Menge von Attributen, deren
Ausprägung die Tupel sind; eine Relation
kann in Form einer Tabelle notiert werden.
Die Begriffe Relation und Tabelle werden
häufig synonym gebraucht.
Relationale Datenbank:
Daten werden in Relationen organisiert, die
mit Daten anderer Relationen in Beziehung
stehen können. Eine Relation kann sowohl
Objekte (Entitäten) als auch Beziehungen
beinhalten.
Relationales DBS:
Die Daten werden in Tabellenform gespeichert. Zwischen den Tabellen können Beziehungen (Relationen) definiert werden.
Die meist verwendete Abfragesprache ist
SQL.
Relationship:
Beziehung zwischen zwei Entities.
Repository:
Wie ein Data Dictionary aufgebaut, speichert aber noch zusätzliche Informationen,
z.B. über Benutzer und Anwenderprogramme.
Schlüsselkandidat:
Zweidimensionale Konstruktion aus Spalten
und Zeilen zum Speichern der Datensätze
(Tupel).
Sekundärschlüssel:
Zusätzlicher Schlüssel, um Redundanzen in
den Datensätzen einer Tabelle zu vermeiden.
Shared Pool (Library-Cache):
Im Shared Pool (Library-Cache) befinden
sich
kürzlich
ausgeführte
SQLAnweisungen, Prozeduren und Ausführungspäne. Diese werden zum schnelleren
Zugriff dort zwischengespeichert.
85
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
IT-Fachbegriff:
Erklärung:
Sicht:
Ausschnitt (Teilmenge) einer Datenbank,
der die für eine Anwendung relevante Daten enthält.
Strings:
Zeichenketten: In einer Datenbank werden
Textinformationen als Zeichenketten gespeichert, Z.B. in Datenfeldern vom Datentyp VARCHAR.
System-Monitor (SMON):
Der SystemMonitor-Prozess ist primär verantwortlich für Instance-Recovery und
Crash-Recovery. Er stellt sicher, dass nach
einem Absturz einer Datenbank diese Datenbank beim nächsten Hochfahren wieder
in einen konsistenten Zustand überführt
wird.
Tabelle:
Stellt eine definierte Struktur für das Speichern von Daten zur Verfügung.
Transaktion:
Als Transaktion werden mehrere aufeinanderfolgende Lese- und Schreibzugriffe auf
eine Datenbank bezeichnet, die in einem
logischen Zusammenhang stehen. Diese
werden entweder vollständig oder gar nicht
ausgeführt.
Verteiltes DBS:
Die Datenbanken sind auf geografisch getrennt stehende Rechner verteilt. Auf jedem
dieser Rechner läuft das DBMS, welches
über Mechanismen zur Zusammenführung
der verteilten Datenbank verfügt. Es gibt
homogen und heterogen verteilte DBS.
Zentralisiertes DBS:
Die Datenbankanwendungen laufen auf
einem zentralen Rechner. Die Terminals
arbeiten über ein Netzwerk mit den Anwendungsprogrammen.
86
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
15.2
Wichtige SQL – Befehle
Aktion:
Syntax:
Bedingungen logisch verknüpfen
SELECT … WHERE bedingung1
AND / OR bedingung2 …;
Bedingungen negieren
SELECT … WHERE NOT bedingung;
Benutzer bestimmte Rechte gewähren
(ANY)
GRANT rechteliste ANY TABLE TO user;
Benutzer Administratorrechte zuweisen
(WITH ADMIN OPTION)
GRANT rechteliste ANY TABLE TO user
WITH ADMIN OPTION;
Benutzerrechte entziehen (REVOKE)
REVOKE rechteliste ANY TABLE TO user;
Benutzerrechte weitergeben
(WITH GRANT OPTION)
GRANT rechteliste
ON schema_user1.tabelle
TO user2 WITH GRANT OPTION;
Benutzern Objekt-Privilegien entziehen
REVOKE rechteliste ON schema.user1
FROM user2;
Daten einer Abfrage gruppieren
SELECT … ORDER BY datenfeldname;
Daten einer Abfrage mit einer
Bedingung definieren
SELECT … GROUP BY datenfeldname
HAVING bedingung;
Daten einer Abfrage sortieren
SELECT … ORDER BY datenfeldname;
Datenabfrage ausführen,
die alle Datensätze einer Tabelle liefert
SELECT * FROM tabellenname;
Datenabfrage mit Suchbedingung
definieren
SELECT * FROM tabellenname
WHERE bedingung;
Datenabfrage mit Mustervergleich
SELECT …
WHERE datenfeld BETWEEN
untergrenze AND obergrenze;
Datenfelder in einer Sicht benennen
CREATE VIEW viewname
(feldname1, …) AS SELECT …;
87
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Aktion:
Syntax:
Datenbank NOMOUNT-Status
STARTUP NOMOUNT;
(Start-Datei gelesen, Control-Datei identifiziert, SGA initialisiert und Hintergrundprozesse gestartet)
Datenbank angehängt – MOUNT-Status
STARTUP MOUNT;
(Control-Dateien geöffnet und die Speicherorte der Datendateien sowie OnlineRedo-Log-Dateien gelesen)
STARTUP OPEN;
Datenbank geöffnet – OPEN-Status
(Datendateien und Redo-Log-Dateien
geöffnet und Sperren gesetzt)
Datenbank herunterfahren:
SHUTDOWN NORMAL;
SHUTDOWN NORMAL – Oracle wartet
bis sich alle Benutzer abmelden.
Datenbank herunterfahren:
SHUTDOWN IMMEDIATE – Oracle
beendet alle Benutzersitzungen
(empfohlen !!!)
SHUTDOWN IMMEDIATE;
Datenbank herunterfahren:
SHUTDOWN TRANSACTIONAL;
SHUTDOWN TRANSACTIONAL –
Oracle wartet bei Transaktionen auf
„commit“ bzw. „rollback“.
SHUTDOWN ABORT;
Datenbank herunterfahren:
SUTDOWN ABORT – Oracle beendet
alles sofort ohne Speicherung. Nach
dem Neustart ist eine InstanceRecovery notwendig (nur Notfall !!!)
Datensatz einfügen
INSERT INTO tabellenname
(datenfeldliste) VALUES (wertliste);
Datensätze aktualisieren
UPDATE tabellenname
SET feld=wert, WHERE bedingung;
Datensätze mit einer Unterfrage einfügen
INSERT INTO tabellenname1
(datenfelder)
SELECT datenfelder
FROM tabellenname2
WHERE bedingung;
88
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Aktion:
Syntax:
Datensätze löschen
DELETE FROM tabellenname
WHERE bedingung;
Datensätze (referenzierte) beim
Löschen einschließen
FOREIGN KEY (datenfeldname)
REFERENCES tabellenname
(datenfeldname) ON DELETE CASCASE;
Datensätze über eine Sicht ändern
UPDATE viewname SET …;
Datensätze über eine Sicht erstellen
INSERT INTO viewname;
Datensätze über eine Sicht löschen
DELETE FROM viewname …;
Eingabe eines Wortes für ein Datenfeld
Erzwingen
NOT NULL
Ersatzname für eine Tabelle definieren
tabellenname AS ersatzname
Fremdschlüssel erstellen
CREATE TABLE tabellenname
ADD CONSTRAINT constraintname
FOREIGN KEY (datenfeldname)
REFERENCES tabellenname
(datenfeldname));
Fremdschlüssel nachträglich hinzufügen
ALTER TABLE tabellenname
ADD CONSTRAINT constraintname
FOREIGN KEY …;
Fremdschlüssel löschen
ALTER TABLE tabellenname
DROP CONSTRAINT constraintname;
Full-Join
SELECT datenfelder
FROM tabelle1, tabelle2;
Gültigkeitsbedingung definieren
CONSTRAINT name CHECK (bedingung);
Index erstellen
CREATE INDEX indexname
ON tabellenname (datenfeldname);
Index löschen
DROP INDEX indexname
ON tabellenname;
Inner-Join (Equi-Join)
SELECT datenfelder
FROM tabelle1, INNER JOIN tabelle2
ON tabelle1.datenfeld = tabelle2.datenfeld
[WHERE …];
89
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Aktion:
Syntax:
Natural-Join
SELECT DISTINCT datenfelder
FROM tabelle1 INNER JOIN tabelle2
ON bedingung;
Outer-Join von links erstellen
SELECT datenfelder FROM tabelle1
LEFT OUTER JOIN tabelle2
ON bedingung;
Outer-Join von rechts erstellen
SELECT datenfelder FROM tabelle1
RIGHT OUTER JOIN tabelle2
ON bedingung;
Primärschlüssel definieren
PRIMARY KEY (datenfeld);
Primärschlüssel erstellen
CREATE TABLE (…
PRIMARY KEY (datenfeldname));
Primärschlüssel nachträglich hinzufügen
ALTER TABLE tabellenname
ADD CONSTRAINT constraintname
PRIMARY KEY (datenfeldname);
Primärschlüssel löschen
ALTER TABLE tabellenname
DROP CONSTRAINT constraintname;
Self-Join (eine Tabelle mit sich selbst
verknüpfen)
SELECT
datenfelder_alt AS datenfelder_neu
FROM tabelle.datenfeld
WHERE bedingung;
Sicht erstellen
CREATE VIEW viewname AS SELECT …;
Sicht mit Datenüberprüfung erstellen
CREATE VIEW viewname
AS SELECT … WITH CHECK OPTION;
Sicht löschen
DROP VIEW viewname;
Standardwert festlegen
DEFAULT wert;
Tablespace erstellen
CREATE TABLESPACE tablespacename
DATAFILE ’c:\pfadangabe\tablespace.dbf’
SIZE größe_in_mb ONLINE;
Tablespace löschen
DROP TABLESPACE tablespacename;
Tablespace OFFLINE setzen
ALTER TABLESPACE tablespacename
OFFLINE;
90
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Tablespace ONLINE setzen
ALTER TABLESPACE tablespacename
ONLINE;
Tabelle ändern
ALTER TABLE tabellenname;
Tabelle erstellen
CREATE TABLE tabellenname
(datenfelddefinition);
Tabelle löschen
DROP TABLE tabellenname;
Wertebereich prüfen
SELECT …
WHERE datenfeld BETWEEN
untergrenze AND obergrenze;
Werteliste prüfen
SELECT …
WHERE datenfeld IN (werteliste):
15.3
Literaturhinweise
FRÖHLICH, L., Oracle9i. Administration. Data Warehouse. Hochverfügbarkeit, München:
Markt + Technik 2003.
HELD, A., Oracle 10g Hochverfügbarkeit. Die ausfallsichere Datenbank mit RAC, Data
Guard und Flashback, München: Addison-Wesley 2005.
HERDT-VERLAG (Hrsg.), SQL – Grundlagen und Datenbankdesign, Nackenheim 2003.
KERSKEN, S., Kompendium der Informatik, Bonn: Galileo Press GmbH 2003.
MICROSOFT PRESS DEUTSCHLAND (Hrsg.), Microsoft SQL Server 2000 Systemverwaltung
(MCSE 70-228), Unterschleißheim 2002.
NIEMIEC, R., Oracle9i Performance Tuning Tips & Techniques, California: McGraw-Hill
2003.
RAYMANS, H.-G., SQL, München: Addison-Wesley 2002.
91
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
16
Lösungen zu den Übungen
Kapitel 2:
1)
Einführung Datenbanken
(Übung)
Welche Datenbanktypen kennen Sie?
Wodurch sind sie gekennzeichnet?
•
Hierarchische Datenbanken:
„ Zwischen den Datensätzen besteht eine untergeordnete Rangfolge.
„ Dieses System dient der Verarbeitung von Datensätzen,
die eine unterschiedliche Länge besitzen können.
•
Netzwerkdatenbanken:
„ Zwischen den Tabellen besteht eine 1:n-Beziehung.
„ Relationen werden als SETS bezeichnet.
•
Relationale Datenbanken:
„ Daten werden in Tabellen (Relationen) gespeichert, zwischen denen Beziehungen (Integritäten) bestehen.
„ SQL stellt das Hauptbindeglied zwischen den Daten und den Resultaten dar.
2)
Nennen Sie die Namen der 3 Ebenen des 3-Ebenen-Modells, und geben Sie an,
was in jeder Ebene dargestellt wird.
•
3-Ebenen-Modell
„ Externe Ebene (Data Manipulation Language, Veränderung der Daten
Ö schreiben, löschen, hinzufügen).
„ Konzeptionelle Ebene (Data Definition Language, Beschreibung der Daten
und ihrer logischen Zusammenhänge: „Ausschnitt aus der realen Welt“).
„ Interne Ebene (Data Administration Language, beschreibt Organisation der
Daten auf Speichermedium und Zugriffsmöglichkeiten auf die Daten;
Zugriffsrechte werden vergeben).
3)
Was ist ein Datenbankmanagementsystem (DBMS)?
„ Ein DBMS ist ein Softwarepaket, welches die Verwaltung der Datenbank
übernimmt und alle Zugriffe darauf regelt.
92
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4)
Was ist ein Data Dictionary, und wozu wird es benötigt?
„ Data Dictionary (speichert Informationen über die Daten der DB und deren
Verwaltung).
5)
Welche physischen Datenbankarchitekturen kennen Sie?
„
„
„
„
„
•
Zentralisiertes Datenbanksystem (DBS)
Verteiltes DBS
Client-Server DBS
Objektorientiertes DBS
Paralleles DBS
Erläutern Sie eine Datenbankarchitektur!
„ Objektorientiertes DBS
† Es werden keine Daten, sondern Objekte gespeichert,
die wiederum Eigenschaften und Methoden besitzen.
Kapitel 3:
1)
Datenbankentwurf
Welche Phasen werden beim Entwurf von Datenbanken durchlaufen?
„
„
„
„
„
„
2)
(Übung)
Anforderungsanalyse
Konzeptioneller Entwurf
Logischer Entwurf
Physischer Entwurf / Implementierung
Test und Validation
Anwendung und Wartung
Wozu dient das Entity-Relationship-Modell?
„ Das Entity-Relationship-Modell (kurz ER-Modell oder ERM) ist das bekannteste und meistverwendete grafische Hilfs- mittel für den Datenbankentwurf.
„ Es ermöglicht, konzeptionelle Entwürfe einer Datenbank auf leicht verständliche Art grafisch darzustellen.
93
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Kapitel 4:
1)
Relationales Datenbankmodell
(Übung)
Erstellen Sie für eine CD-Sammlung eine Datenbank. Gehen Sie folgendermaßen
vor:
„ Erstellen Sie ein ER-Diagramm
„ Wenden Sie die Normalformen an (bis zur 3. Normalform)
•
Um die Datenbank aufzubauen, steht Ihnen die folgende Feldliste zur Verfügung
(Sänger bedeutet der Interpret oder der Gruppenname; bei einer Gruppe entfällt die
Eingabe des Vornamens):
„ Sängernummer, Name des Sängers, Vorname des Sängers, Bild des Sängers, Bemerkungen zum Sänger, CD-Nummer, Titel der CD, Laufzeit der
CD, Bild des Covers, Liednummer, Name des Liedes, Laufzeit des Lieds,
Managernummer, Name des Managers, Geschlecht des Managers und Adresse des Managers.
94
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
•
Als Regel gilt: Ein Sänger kann einen oder keinen Manager haben. Ein Manager
kann jedoch mehrere Sänger beraten.
•
Ziel:
„ Anomalien beheben.
„ Redundanzen vermeiden.
„ Eine einfache Datenpflege ermöglichen.
•
1. Normalform:
„ Wenn die Relation zweidimensional ist.
„ Wenn sich in jedem Datensatz nur Daten befinden, die zu einem Objekt der
realen Welt gehören.
•
2. Normalform:
„ Eine Relation befindet sich in der zweiten Normalform, wenn jeder NichtSchlüsselfeld vom ganzen Primär-Schlüssel abhängig ist.
„ Die Grundtabelle wird in mehrere Tabellen aufgeteilt, in denen wieder Primärschlüssel vergeben werden.
•
3. Normalform:
„ Eine Tabelle befindet sich in der 3. Normalform, wenn alle Daten- felder nur
von einem Schlüssel abhängig sind und untereinander keine Abhängigkeiten
auftreten.
95
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2)
3)
Welche Aussagen treffen für eine m:n-Beziehung zu?
;
Eine m:n-Beziehung kann in der Praxis nicht erstellt
werden.
…
In einer m:n-Beziehung kann die referentielle Integrität
nicht eingeschaltet werden.
;
Eine m:n-Beziehung liegt vor, wenn zwischen zwei
Entitäten die Primärschlüssel der beiden Tabellen
Mehrfach in der Relation vorkommen.
…
Eine m:n-Beziehung wird in Access oder Oracle
benutzt, um zwei Tabellen zu verknüpfen.
Kennzeichnen Sie die falschen Aussagen!
…
In einer Datenbank werden die Daten in Feldern
Strukturiert abgespeichert.
;
Die referentielle Integrität bedeutet, dass in einem
Feld ein Wert eingegeben werden muss.
…
Ein Standardwert soll die Arbeit erleichtern. Der
Standardwert wird in jeden neuen Datensatz für
ein Feld eingegeben.
96
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
Kapitel 7:
1)
Tabellen einfügen und verwalten
(Übung)
Erstellen Sie eine neue Tabelle „t_artikel“ mit den Datenfeldern „artid“, „artname“
und „artpreis“. Verwenden Sie geeignete Datentypen?
CREATE TABLE t_artikel
(
artid
INT NOT NULL,
artname
VARCHAR (20),
artpreis
VARCHAR (10)
);
2)
Löschen Sie das Datenfeld „artpreis“.
„ Variante 1:
ALTER TABLE t_artikel
DROP COLUMN artpreis ;
„ Variante 2:
ALTER TABLE t_artikel
DROP artpreis ;
3)
Erstellen Sie eine Tabelle „t_personen“ mit den Daten- feldern „persid“, „vorname“
und „nachname“. Verwenden Sie geeignete Datentypen. Für alle Datenfelder soll
dabei eine Eingabe erforderlich sein.
CREATE TABLE t_personen
(
persid
INT NOT NULL,
vorname
VARCHAR NOT NULL,
nachname VARCHAR NOT NULL
);
97
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
4)
Ändern Sie die Tabelle „t_personen“ und fügen Sie ein zusätzliches Datenfeld
„lebenslauf“ hinzu. In dem Feld soll eine größere Datenmenge – z.B. ein Word- /
Bild-Dokument – gespeichert werden können.
ALTER TABLE t_personen
ADD lebenslauf BLOB ;
5)
Fügen Sie ein neues Datenfeld „angestellt_seit“ in die Tabelle „t_personen“ ein. Es
soll ein Datumswert gespeichert werden können.
ALTER TABLE t_personen
ADD angestellt_seit DATE ;
6)
Löschen Sie die Tabellen „t_artikel“ und „t_personen“.
„ Tabelle „t_artikel“:
DROP TABLE t_artikel ;
„ Tabelle „t_personen“:
DROP TABLE t_personen ;
Kapitel 8:
1)
Datenpflege
(Übung)
Erstellen Sie eine neue Tabelle „wappenrolle“ mit den Datenfeldern „name“, „vorname“, „titel“, „wappen“ und „geboren“. Verwenden Sie geeignete Datentypen. Für
alle Datenfelder soll dabei eine Eingabe erforderlich sein.
CREATE TABLE wappenrolle
(
name
VARCHAR2
vorname
VARCHAR2
titel
VARCHAR2
wappen
VARCHAR2
geboren
VARCHAR2
);
98
(20),
(15),
(8),
(30),
(4)
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2)
Fügen Sie folgende Informationen in die Tabelle „wappenrolle“ [Skript, S. 43] ein.
„ Erste Zeile der Tabelle „wappenrolle“, welche man als Datensatz eingeben
muss:
INSERT INTO wappenrolle
(name, vorname, titel, wappen, geboren)
VALUES
(‘v. Furtenbach‘, ‘Franz‘, ‘Freiherr‘,
‘Wellenbalken‘, ‘1880‘) ;
3)
Führen Sie eine einfache Datenabfrage durch, um das erfolgreiche Einfügen der
neuen Datensätze zu überprüfen.
SELECT * FROM wappenrolle ;
4)
Ändern Sie den Titel „Zar“ in „Kaiser“ und das Geburtsdatum „1597“ in „1587“.
„ 1. Schritt:
UPDATE wappenrolle SET titel = ‘Kaiser‘
WHERE name = ‘Romanow‘ ;
„ 2. Schritt:
UPDATE wappenrolle SET geboren = ‘1587‘
WHERE name = ‘Romanow‘ ;17
5)
Löschen Sie den Datensatz des Freiherrn Franz v. Furtenbach.
„ Variante 1:
DELETE FROM wappenrolle
WHERE name = ‘v. Furtenbach‘ ;
„ Variante 2:
DELETE FROM wappenrolle
WHERE wappen = ‘Wellenbalken‘ ;
17
Elegante Lösung: UPDATE wappenrolle SET titel=’Kaiser’, geboren=’1587’ WHERE name=’Romanow’.
99
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
6)
Was bewirkt folgende Anweisung:
INSERT INTO wappenrolle
SELECT * FROM wappenrolle ;
•
7)
Es werden alle vorhandenen Datensätze der Tabelle Wappenrolle noch einmal in
die Tabelle Wappenrolle eingefügt.
Wieviele Datensätze befinden sich jetzt in der Tabelle Wappenrolle?
Löschen Sie anschließend die Tabelle.
„
„
Es befinden sich nun 14 Datensätze in der Tabelle.
Löschen der Tabelle „wappenrolle“:
DROP TABLE wappenrolle ;
Kapitel 9:
1)
Einfache Datenabfragen
(Übung)
Schreiben Sie eine Abfrage, die alle Datensätze der Produkttabelle der „NordwindDatenbank“ mit der Lieferantennummer = 2 und Kategorienummer = 1 liefert.
SELECT * FROM products
WHERE supplierid = 2
AND categoryid = 1 ;
•
Lösung:
„ Es wurden keine Datensätze ausgewählt.
100
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2)
Schreiben Sie eine Abfrage, die alle Produktnamen mit deren Kategorie-ID auflistet.
Dabei soll jeweils folgender Satz ausgegeben werden. Das Produkt X gehört zur
Kategorie Y.
SELECT
' Das Produkt ' || PRODUCTNAME ||
' gehört zur Kategorie ' || CATEGORYID || '. '
FROM products ;
3)
Erstellen Sie eine Abfrage, die Ihnen alle Produkte ausgibt, die mit „C“, „D“ oder „E“
anfangen.
SELECT productname FROM products
WHERE productname
LIKE ‘C%’
OR productname
LIKE ‘D%’
OR productname LIKE ‘E%‘ ;
4)
Erstellen Sie eine Abfrage, die Ihnen alle Produkte ausgibt, bei denen der 5. Buchstabe kein „c“ ist.
SELECT productname
FROM products
WHERE productname
NOT LIKE ‘____c%’ ;
101
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
5)
Erstellen Sie eine Abfrage, die Ihnen alle Produkte ausgibt, bei denen der 5. Buchstabe ein „c“ oder „d“ ist. Diese sollen aufsteigend nach dem Produktnamen sortiert
sein.
SELECT productname FROM products
WHERE productname
LIKE ‘____c%‘
OR productname
LIKE ‘____d%’
ORDER BY productname ASC ;
6)
Schreiben Sie eine Abfrage, die Ihnen die Spalte Ort der Kundentabelle ausgibt. Es
soll keine Stadt mehrmals auftauchen.
SELECT DISTINCT city FROM customers ;
Kapitel 11: Abfragen in Funktionen
1)
(Übung)
Listen Sie alle Lieferanten auf, die mehr als 5 Produkte liefern!
SELECT supplierid
FROM products
GROUP BY supplierid
HAVING count(productname) > 5 ;
•
Lösung:
„ Es wurden keine Zeilen ausgewählt.
102
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2)
Listen Sie die Lieferanten mit Ihren jeweils teuersten Produkt auf. Es sollen nur die
Lieferanten angezeigt werden, bei denen das teuerste Produkt billiger als
10,- EURO ist!
SELECT supplierid, max(unitprice) 18
FROM products
GROUP BY supplierid
HAVING max(unitprice) < 10 ;
3)
Listen Sie den Nachnamen des Mitarbeiters auf, der am längsten angestellt war!
SELECT lastname, hiredate
FROM employees
WHERE hiredate IN
(
SELECT min(hiredate)
FROM employees
);
Kapitel 12: Datenabfragen über mehrere Tabellen (Joins)
1)
(Übung)
Welche Kunden „contactname“ haben Produkte der Kategorie „beverages“ bestellt?
Benutzen Sie NATURAL JOINs, soweit es möglich ist.
SELECT DISTINCT contactname
FROM customers
NATURAL JOIN
NATURAL JOIN
NATURAL JOIN
NATURAL JOIN
orders
order_details
products
categories
WHERE lower(categoryname) = 'beverages‘ ;
18
Oracle akzeptiert nur zwei Abfragefelder bei dieser Art von Syntax mit GROUP BY und HAVING.
103
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
2)
Welcher Kunde hat noch nie etwas bestellt? Benutzen Sie einen OUTER JOIN.
SELECT DISTINCT contactname, orderid
FROM customers c
LEFT JOIN orders o
ON c.customerid = o.customerid
WHERE orderid IS NULL ;
3)
Welche Kunden wurden bisher von Speedy Express beliefert? Benutzen Sie INNER
JOIN und NATURAL JOIN, soweit es möglich ist. Erstellen Sie eine Hilfs-View!
„ Erstellung einer Sicht (VIEW) – nach der Abfrage wieder löschen:
CREATE VIEW shippers_view
AS SELECT shipperid AS shipvia, companyname
AS sh_companyname
FROM shippers;
„ Abfrage über die erstellte Sicht (VIEW):
SELECT DISTINCT companyname, sh_companyname
FROM customers
NATURAL JOIN orders
NATURAL JOIN shippers_view
WHERE lower(sh_companyname) = 'speedy express‘ ;
4)
Erstellen Sie eine Abfrage, die Ihnen rechts den Angestellten und links den Vorgesetzten ausgibt. Der Chef sollte in der Spalte Vorgesetzter „BOSS“ zu stehen haben.
SELECT e.lastname Mitarbeiter,
nvl(boss.lastname,'Boss') AS Chef
FROM employees e
LEFT JOIN employees boss
ON e.reportsto = boss.employeeid ;
104
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
5)
Schreiben Sie eine Abfrage, die Ihnen die Kontaktnamen der Lieferanten ausgibt,
die Produkte der Kategorie 1 oder 2 oder 3 liefern!
SELECT s.contactname, p.productname
FROM products p, suppliers s
WHERE p.categoryid IN ('1','2','3') ;
6)
Schreiben Sie eine Abfrage, die Ihnen die Kundennamen, die entsprechenden
Bestellungnummern „orderid“ und den Namen des Angestellten „employees“, der
die Bestellung bearbeitet hat, ausgibt. Es soll nach dem „contactname“ geordnet
sein!
SELECT s.contactname, o.orderid, e.lastname
FROM customers c, oders o, employees e
WHERE e.employeeid = o.employeeid
AND o.customerid = c.customerid ;
7)
Was machen folgende Abfragen?
„ SELECT lastname
FROM employees GROUP BY city ;
† Fehler: Falsche Gruppierung, da die Spalte „lastname“ kein Primarykey ist und somit etwas nicht eindeutig identifiziert wird.
„ SELECT discontinued, sum(unitprice*unitsinstock)
FROM products GROUP BY discontinued ;
† Lösung: Anzeige des Warenbestandes im Warenlager!
„ SELECT sum(unitprice*unitsinstock)
FROM products GROUP BY supplierid ;
† Lösung: Syntaktisch richtig aber unlogisch (sum)!
105
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
17
Stichwortverzeichnis
1
DBMS ..................................................... 7, 12
DCL ........................................................ 31
DDL ........................................................ 31
DML ........................................................ 31
DQL ........................................................ 31
1:n-Beziehung ..................................... 9, 19, 24
3
3-Ebenen-Modell ................................ 11, 89
E
Ebene ..................................................... 11
- externe ........................................... 11
- konzeptionelle ................................ 11
- interne ............................................ 11
Eingabeaufforderung .............................. 33
Elementprüfung ...................................... 49
ENTITÄT ................................................. 16
Entitätsmengen ...................................... 24
ENTITY-RELATIONSHIP-MODELL ....... 16
Entwurfsphasen ...................................... 14
A
Abfragesprachen ................................ 8, 31
Aggregatfunktion ................................. 59, 80
Attribute .............................................. 23
B
Bedingungen .......................................
Berechnungen ....................................
BEZIEHUNGEN ..................................
- 1:n-Beziehung ............................
- 1:1-Beziehung ............................
- n:m-Beziehung ...........................
- is-a-Beziehung ...........................
48
46
10, 18
9, 19, 24
19, 24
19, 26
26
G
Generalisierung ...................................... 26
Gültigkeitsprüfung .................................. 38
Gruppieren .............................................. 51
C
H
CONSTRAINTS .................................. 54
Herunterfahren von Oracle ..................... 34
D
I
DATA DICTIONARY ...........................
Datenabfragen ....................................
Datenbankarchitektur ..........................
- zentralisiert .................................
- verteilte .......................................
- Client-Server ..............................
- parallele ......................................
Datenbank-Management-System .......
Datenbanken ......................................
- Hierarchische .............................
- Netzwerk ....................................
- Relationale .................................
Datenbankentwurf ...............................
Datenbestand .....................................
Datenintegrität ....................................
Datenschutz ........................................
Datensicherheit ...................................
Datentypen .........................................
Datumswerte .......................................
7, 12
45
12
12
13
13
14
7, 12
7
9
9
10
20
7, 62
8
7, 8, 12
8
38
38
Index ....................................................... 23
Indizes .................................................... 54, 57
Informationsfluss .................................... 8
Installation .............................................. 31
Integritäten .............................................. 10
J
JOINS ..................................................... 63
- Cross-Join ...................................... 64
- Equi-Join ........................................ 65, 68
- Full-Join ......................................... 64, 67
- Inner-Join ....................................... 65, 68
- Natural-Join ................................... 63, 69
- Outer-Join ...................................... 70
- Self-Join ......................................... 64, 71
- Semi-Join ....................................... 64
- Theta-Join ...................................... 65, 70
106
o9i_einf.pdf
Oracle 9i – Einführung
Timo Meyer © RRZN 2004-2005
K
S
KARDINALITÄTEN ............................. 18
KONSISTENZ ..................................... 8
Schema .................................................. 10
SCHLÜSSEL .......................................... 23
- Fremdschlüssel .............................. 23, 56
- Primärschlüssel ............................. 17, 54
- Sekundärschlüssel ......................... 23
- SETS ............................................. 9
SHUTDOWN .......................................... 35
- ABORT .......................................... 35
- IMMEDIATE ................................... 35
- NORMAL ....................................... 35
- TRANSACTIONAL ......................... 35
Sicht ........................................................ 11
Sichten .................................................... 74
Sortieren ................................................. 52
Spezialisierung ....................................... 26
SQL*Plus Worksheet .............................. 32
Startvorgang von Oracle ........................ 34
STARTUP ............................................... 34
- MOUNT .......................................... 34
- NOMOUNT .................................... 34
- OPEN ............................................. 34
Structured Query Language (SQL) ........ 8, 31
L
Lebenszyklus ......................................
- Konzeptioneller Entwurf .............
- Logischer Entwurf ......................
- Physischer Entwurf ....................
- Test ............................................
- Anwendung und Wartung ..........
Logbuch ..............................................
Logische Operatoren ..........................
15
15
15
15
15
15
12
50
M
Mengenoperatoren .............................
- Vereinigungsmenge ...................
- Schnittmenge .............................
- Differenzmenge ..........................
Mustervergleich ..................................
62
62
62
62
50
T
N
NORMALFORM ..................................
- 1. Normalform ............................
- 2. Normalform ............................
- 3. Normalform ............................
- 4. Normalform ............................
- 5. Normalform ............................
Normalisierungsprozess .....................
Tabellen .................................................. 37, 71
TABLESPACES ..................................... 36
Typen ...................................................... 21, 86
28
28
28
29
29
29
27
V
Validation ................................................ 15
Vergleichsoperatoren ............................. 48
Verknüpfungen ....................................... 62, 65
VIEWS .................................................... 74
P
Profile .................................................. 76
Projektverwaltung ............................... 20
Protokollierung .................................... 8
Z
Zugriffsrechte .......................................... 76, 78
R
Rechtevergabe ................................... 8
REDUNDANZ ..................................... 8
RELATIONEN ..................................... 10, 23
107
o9i_einf.pdf