Folien ab 11.12.13

12. Views und Datenbankverwaltung
• Views
• Änderungen in Views
• Organisation der DB
• Zugriffsrechte
Datenbanken
Prof. Dr.
Stephan Kleuker
303
Sichtkonzept (Views 1/2)
• Sicht (View): mit eigenem Namen bezeichnete, aus
Basisrelation abgeleitete, virtuelle Relation (View-Name wie
Tabellen-Name verwendbar)
• Views sind das Ergebnis einer Anfrage, auf dem weitere
Operationen durchgeführt werden können
• Views können jedes mal neu erzeugt werden oder nur
einmal und dann gespeichert (materialized view)
• Gespeicherte Views müssen nach jedem Update der
Basisrelationen geändert werden
• Wir betrachten keine materialized Views
• Korrespondenz zum externen Schema bei ANSI SPARC
(Benutzer sieht jedoch mehrere Views und Basisrelationen)
Datenbanken
Prof. Dr.
Stephan Kleuker
304
Sichtkonzept (Views 2/2)
CREATE VIEW Germany AS
SELECT name, population
FROM City
WHERE Country='D'
• Vorteile
– Erhöhung der Benutzerfreundlichkeit (z.B. Verbergen
komplexer Joins in einer View)
– Datenschutz
• Löschen eines Views
DROP VIEW Germany
Datenbanken
Prof. Dr.
Stephan Kleuker
305
View-Update-Problem
• Änderungsoperationen auf Sichten erfordern, dass zu jedem
Tupel der Sicht zugrunde liegende Tupel der Basisrelationen
eindeutig identifizierbar sind
• Sichten auf einer Basisrelation sind nur änderbar, wenn der
Primärschlüssel in der Sicht enthalten ist
• Wenn Tupelanteile bei INSERT eindeutig auf die darunter
liegenden Basisrelationen abgebildet werden können,
könnten fehlende Werte durch NULL aufgefüllt werden
(Constraints sind zu beachten)
[Allerdings, in Oracle grundsätzlich keine Veränderung auf
zusammengesetzten View möglich]
Datenbanken
Prof. Dr.
Stephan Kleuker
306
Problemquellen bei Views
1. Zeilen löschen, wenn der View Gruppenfunktionen (z.B.
COUNT), GROUP BY oder DISTINCT enthält oder in der
View-Definition WITH READ ONLY steht
2. Zeilen ändern, wenn 1. oder es berechnete Spalten (z.B.
A+B) gibt
3. Zeilen hinzufügen, wenn 1. oder 2. oder es in den
Basistabellen eine Spalte mit NOT NULL gibt, die nicht im
View liegt
Hinweis: Infos, was man in Oracle darf, erhält man mit
SELECT * FROM USER_UPDATABLE_COLUMNS;
Datenbanken
Prof. Dr.
Stephan Kleuker
307
Beispiel: View-Probleme
R
A B
- a b
x b
S
B C
- b c
b z
RS
A B C
- - a b c
x b c
a b z
x b z
CREATE VIEW RS AS
SELECT A, R.B, C
FROM R, S
WHERE R.B=S.B;
• Löschen von (a,b,c) führte zum Verlust von (a,b,z)
• alleiniges Ändern von (a,b,c) nach (a,b,d) geht nicht
• Einfügen von (a,b,d) ebenfalls
Datenbanken
Prof. Dr.
Stephan Kleuker
308
Aktualisierung von Views
• Tupel können durch Update aus dem Wertebereich des
Views hinausfallen
• Views häufig verwendet, um den "Aktionsradius" eines
Benutzers einzuschränken
• Verlassen des Wertebereichs kann durch WITH CHECK
OPTION verhindert werden:
CREATE OR REPLACE VIEW UScities AS
SELECT *
FROM City
WHERE City.Country = 'USA'
WITH CHECK OPTION;
Datenbanken
Prof. Dr.
Stephan Kleuker
309
Beispiel: WITH CHECK OPTION
CREATE TABLE XX(
A
B
A NUMBER, B NUMBER, C NUMBER,
---------- ---------PRIMARY KEY(A));
1
2
INSERT INTO XX VALUES(1,2,3);
2
1
INSERT INTO XX VALUES(2,1,3);
A
B
INSERT INTO XX VALUES(3,4,3);
---------- ---------CREATE OR REPLACE VIEW V1X AS
2
1
SELECT A,B
1 Zeile wurde erstellt.
FROM XX
INSERT INTO V1X VALUES(5,5)
WHERE B<3
*
WITH CHECK OPTION;
FEHLER in Zeile 1:
SELECT * FROM V1X;
ORA-01402: Verletzung der WHERE-Klausel
UPDATE XX
einer View WITH CHECK OPTION
SET B=5 WHERE B=2;
A
B
C
SELECT * FROM V1X;
---------- ---------- -------1
5
3
INSERT INTO V1X VALUES(4,2);
2
1
3
INSERT INTO V1X VALUES(5,5);
3
4
3
SELECT * FROM XX;
4
2
Datenbanken
Prof. Dr.
Stephan Kleuker
310
INSTEAD OF –Trigger
• View Updates: müssen auf Basistabellen umgesetzt werden
• View-Update-Mechanismen eingeschränkt
• INSTEAD OF -Trigger: Änderung an einem View wird durch
andere SQL-Anweisungen ersetzt
CREATE [OR REPLACE] TRIGGER <trigger-name>
INSTEAD OF
{INSERT | DELETE | UPDATE} ON <view>
[FOR EACH ROW]
<p1/sql-block>;
• Keine Einschränkung auf bestimmte Spalten möglich
• Keine WHEN-Klausel
• Default: FOR EACH ROW
Datenbanken
Prof. Dr.
Stephan Kleuker
311
View-Update ohne Trigger
CREATE OR REPLACE VIEW AllCountry AS
SELECT Name, Code, Population, Area,
GDP, Population/Area AS Density,
Inflation, population_growth,
infant_mortality
FROM Country, Economy, Population
WHERE Country.Code = Economy.Country
AND Country.Code = Population.Country;
INSERT INTO AllCountry
(Name, Code, Population, Area, GDP, Inflation,
population_growth, infant_mortality) VALUES
('Lummerland','LU',4,1,0.5,0,25,0);
• Fehlermeldung: Über ein Join-View kann nur eine
Basistabelle modifiziert werden
Datenbanken
Prof. Dr.
Stephan Kleuker
312
View-Update mit Trigger
CREATE OR REPLACE TRIGGER InsAllCountry
INSTEAD OF INSERT ON AllCountry
FOR EACH ROW
BEGIN
INSERT INTO Country (Name,Code,Population,Area)
VALUES (:NEW.Name, :NEW.Code, :NEW.Population,
:NEW.Area);
INSERT INTO Economy (Country,Inflation)
VALUES (:NEW.Code, :NEW.Inflation);
INSERT INTO Population (Country,
Population_growth,infant_mortality)
VALUES (:NEW.Code, :NEW.Population_growth,
:NEW.infant_mortality);
END;
Datenbanken
Prof. Dr.
Stephan Kleuker
313
Organisation der DB (in Oracle)
Data Dictionary
• Besteht aus Tabellen und Views, die Metadaten über die
Datenbank enthalten
• Mit SELECT * FROM DICTIONARY (kurz SELECT * FROM
DICT) erklärt sich das Data Dictionary selber
• ALL_OBJECTS: Enthält alle Objekte, die einem Benutzer
zugänglich sind
• ALL_CATALOG: Enthält alle Tabellen, Views und Synonyme,
die einem Benutzer zugänglich sind
• ALL_TABLES: Enthält alle Tabellen, die einem Benutzer
zugänglich sind
• Analog für diverse andere Dinge
SELECT * FROM ALL_CATALOG
WHERE TABLE_NAME LIKE 'ALL%';
Datenbanken
Prof. Dr.
Stephan Kleuker
314
Informationen über/für User
• USER_OBJECTS: enthält alle Objekte, die einem Benutzer
gehören
• Analog für die anderen, meistens existieren für USER ...
auch Abkürzungen, etwa OBJ für USER_OBJECTS
• ALL_USERS: Enthält Informationen über alle Benutzer der
Datenbank
Datenbanken
Prof. Dr.
Stephan Kleuker
315
Einrichtung von Usern (Oracle)
CREATE USER <user>
IDENTIFIED BY <password>;
CREATE USER Egon
IDENTIFIED BY ottilie01
QUOTA 5M ON system;
• nächster Schritt: Einrichtung der Systemprivilegien des
Nutzers (viele Möglichkeiten), u.a.
GRANT create session TO Egon
Datenbanken
Prof. Dr.
Stephan Kleuker
316
Verwaltung einer Datenbank
• Für DB-Projekte gibt es meist zwei Administratoren
– DB-Systemadministratoren: Physikalische Einrichtung
von Datenbanken (z.B. Name, Speicherbereich),
Nutzerverwaltung
– Projekt-DB-Administratoren: Verantwortlich für die
Tabellen des Projekts, wer hat welche Rechte auf
welchen Tabellen
• Abhängig vom DB-System müssen beide eng
zusammenarbeiten
• Hinweis: Sie haben auf unserem System grob die Rechte
eines Projekt-Admin und können Zugriffsmöglichkeiten für
Andere einrichten
Datenbanken
Prof. Dr.
Stephan Kleuker
317
Systemprivilegien
GRANT <privilege-list>
TO <user-list> | PUBLIC
[WITH ADMIN OPTION];
• PUBLIC: jeder erhält das Recht
• ADMIN OPTION: Empfänger darf Recht weitergeben
Rechte entziehen:
REVOKE <privilege-list> | ALL
FROM <user-list> | PUBLIC;
• nur wenn man dieses Recht selbst vergeben hat (im Fall von
ADMIN OPTION kaskadierend)
Datenbanken
Prof. Dr.
Stephan Kleuker
318
Systemprivilegien
• berechtigen zu Schemaoperationen
• CREATE [ANY] TABLE / VIEW / TYPE / INDEX /
CLUSTER / TRIGGER/ PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte
erzeugen
• ALTER [ANY] TABLE / TYPE/ TRIGGER / PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte
verändern
Datenbanken
Prof. Dr.
Stephan Kleuker
319
Systemprivilegien
• DROP [ANY] TABLE / VIEW / TYPE / INDEX / CLUSTER
/ TRIGGER / PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte löschen
• SELECT / INSERT / UPDATE / DELETE [ANY] TABLE:
Benutzer darf in Tabellen Tupel lesen/ erzeugen/
verändern/ entfernen
• ANY: Operation in jedem Schema erlaubt,
• ohne ANY: Operation nur im eigenen Schema erlaubt
Datenbanken
Prof. Dr.
Stephan Kleuker
320
Rollen
• Privilegien können Rollen zugeordnet werden, die dann
wieder Nutzern zugeordnet werden können.
CREATE ROLE manager;
GRANT create table, create view
TO manager;
GRANT manager TO i03d09, i00d02;
• Der Entwurf einer sinnvollen Rollenmatrix ist nicht trivial!
Datenbanken
Prof. Dr.
Stephan Kleuker
321
Objektprivilegien (1/4) [entspricht Projektebene]
berechtigen dazu, Operationen auf existierenden Objekten
auszuführen:
• Niemand sonst darf mit einem Datenbankobjekt eines
Nutzers arbeiten, außer
• Eigentümer (oder DBA) erteilt explizit entsprechende
Rechte:
GRANT <privilege-list> | ALL
[(<column-list>)]
ON <object>
TO <user-list> | PUBLIC
[ WITH GRANT OPTION ];
Datenbanken
Prof. Dr.
Stephan Kleuker
322
Objektprivilegien (2/4)
• <object>: TABLE, VIEW, PROCEDURE/FUNCTION, TYPE
•
Tabellen und Views: Genauere Einschränkung für INSERT,
REFERENCES und UPDATE durch <column-list>
• <privilege-list>: DELETE, INSERT, SELECT, UPDATE
für Tabellen und Views,
INDEX, ALTER und REFERENCES für Tabellen
EXECUTE für Prozeduren, Funktionen und TYPEn
• ALL: alle Privilegien, die man an dem beschriebenen Objekt
hat
• WITH GRANT OPTION: Der Empfänger darf das Recht
weitergeben
Datenbanken
Prof. Dr.
Stephan Kleuker
323
Objektprivilegien (3/4)
• Rechte entziehen:
REVOKE <privilege-list> | ALL
ON <object>
FROM <user-list> | PUBLIC
[CASCADE CONSTRAINTS];
• CASCADE CONSTRAINTS (bei REFERENCES): alle
referenziellen Integritätsbedingungen, die auf einem
entzogenen REFERENCES-Privileg beruhen, fallen weg
• Berechtigung von mehreren Benutzern erhalten: Fällt mit
dem letzten REVOKE weg
• im Fall von GRANT OPTION kaskadierend
Datenbanken
Prof. Dr.
Stephan Kleuker
324
Objektprivilegien (4/4)
• Überblick über vergebene/erhaltene Rechte:
SELECT * FROM USER_TAB_PRIVS;
• Rechte, die man für eigene Tabellen vergeben hat,
Rechte, die man für fremde Tabellen bekommen hat
SELECT
SELECT
SELECT
SELECT
SELECT
*
*
*
*
*
FROM
FROM
FROM
FROM
FROM
USER_COL_PRIVS;
USER_TAB_PRIVS_MADE;
USER_COL_PRIVS_MADE;
USER_TAB_PRIVS_RECD;
USER_COL_PRIVS_RECD;
• Stichwort: Rollenkonzept
Datenbanken
Prof. Dr.
Stephan Kleuker
325
Beispiele
GRANT select,
update(name,code)
ON Country
TO egon, manager
GRANT select,insert
ON City
TO PUBLIC
REVOKE select,insert
ON Country
FROM manager
Datenbanken
Prof. Dr.
Stephan Kleuker
326
Zugriffsrechte innerhalb von Oracle
• Zugriffsrechte an ORACLE-Account gekoppelt
• Ausgangsrechte vom DBA vergeben
Schema-Konzept
• Jedem Benutzer ist sein Database Schema zugeordnet, in
dem "seine" Objekte liegen.
• Bezeichnung der Tabellen global durch
<username>.<table> (z.B. xmaier.City für die Tabelle
City des Nutzers xmaier),
• im eigenen Schema durch <table> oder <ich>.<table>
nutzbar
Datenbanken
Prof. Dr.
Stephan Kleuker
327
Synonyme
• Schemaobjekt unter einem anderen Namen als ursprünglich
ansprechbar:
CREATE [PUBLIC] SYNONYM <synonym>
FOR <schema>.<object>;
• Ohne PUBLIC: Synonym ist nur für den Benutzer definiert
• PUBLIC ist das Synonym systemweit verwendbar. Geht nur
mit CREATE ANY SYNONYM -Privileg
Beispiel:
CREATE SYNONYM City2
FOR db07ws65.City
(man muss Zugriffsrechte auf die Tabelle haben)
löschen:
DROP SYNONYM <synonym>
Datenbanken
Prof. Dr.
Stephan Kleuker
328
Zugriffseinschränkung über Views (1/2)
• GRANT SELECT kann nicht auf Spalten eingeschränkt werden
Stattdessen: Views verwenden
GRANT SELECT [<column-list>] -- nicht erlaubt
ON <table>
TO <user-list> | PUBLIC
[WITH GRANT OPTION];
• kann ersetzt werden durch
CREATE VIEW <view> AS
SELECT <column-list> FROM <table>;
GRANT SELECT
ON <view>
TO <user-list> | PUBLIC
[WITH GRANT OPTION];
Datenbanken
Prof. Dr.
Stephan Kleuker
329
Zugriffseinschränkung über Views (2/2)
• db07ws65 ist Besitzer der Tabelle Country, will Country
ohne Hauptstadt und deren Lage für db07ws00 Ies- und
schreibbar machen
• View mit Lese- und Schreibrecht für db07ws00 :
CREATE VIEW pubCountry AS
SELECT Name, Code, Population, Area
FROM Country;
GRANT SELECT, INSERT, DELETE, UPDATE
ON pubCountry
TO db76ws00;
Datenbanken
Prof. Dr.
Stephan Kleuker
330
13. Integrität innerhalb der DB
• Integrität in relationalen DB
• ACID-Transaktionen
• Probleme beim verteilten Arbeiten
• Transaktionen: Praxis (Oracle)
Datenbanken
Prof. Dr.
Stephan Kleuker
331
Wiederholung: Überblick über SQL
•
•
•
DDL Data Definition Language
– CREATE (Anlegen von Tabellen, Sichten, Indexen, ...)
– ALTER (Ändern)
– DROP (Löschen)
– RENAME (Umbenennen)
DML Data Manipulation Language
– SELECT (Anfragen)
– INSERT (Einfügen von Zeilen)
– UPDATE (Ändern)
– DELETE (Löschen)
DCL Data Control Language
– GRANT (Vergabe von Zugriffsrechten)
– REVOKE (Zurücknahme von Zugriffsrechten)
– COMMIT (Abschluss einer Transaktion)
– ROLLBACK (Abbruch einer Transaktion)
Datenbanken
Prof. Dr.
Stephan Kleuker
332
Erinnerung: COMMIT, Transaktion, Rechte
i00x
CREATE TABLE User
(NAME VARCHAR(9));
GRANT ALL ON User TO
i00y;
INSERT INTO User
VALUES(‘egon‘);
SELECT * FROM USER;
NAME
egon
SELECT * FROM USER;
NAME
egon
hugo
Datenbanken
i00y
INSERT INTO i00x.User
VALUES(‘hugo‘);
SELECT * FROM
i00x.User;
NAME
hugo
COMMIT;
Prof. Dr.
Stephan Kleuker
333
ACID-Transaktionen
Atomicity
(Atomarität)
Consistency
(Konsistenz)
Isolation
(Isolation)
Durability
(Dauerhaftigkeit)
Datenbanken
Transaktionen werden entweder ganz
oder gar nicht ausgeführt
Transaktionen überführen die Datenbank
von einem konsistenten Zustand in einen
anderen konsistenten Zustand
Nebenläufige (gleichzeitige) Transaktionen
laufen jede für sich so ab, als ob sie alleine
ablaufen würden.
Die Wirkung einer abgeschlossenen
Transaktion bleibt (auch nach einem
Systemausfall) erhalten.
Prof. Dr.
Stephan Kleuker
334
Zu ACID-Transaktionen
Tatsächlich muss ein DBMS nur garantieren:
• Das Ergebnis einer Transaktion ist gleichwertig zu einer
ACID-Transaktion
Die Transaktionsbehandlung betrifft:
• die Synchronisation von mehreren gleichzeitig ablaufenden
Transaktionen
• das Recovery, d.h. die Behebung von Fehlern
Transaktionen können erfolgreich (commit) oder erfolglos
(abort, für DB-Nutzer rollback) abgeschlossen werden
Datenbanken
Prof. Dr.
Stephan Kleuker
335
Transaktionen
• Zustände einer Transaktion
Datenbanken
Prof. Dr.
Stephan Kleuker
336
Transaktion in SQL
Erste SQL-Anfrage
Folge von SQL-Anfragen
commit
Beginn der ersten Transaktion
Nächste SQL-Anfrage
Folge von SQL-Anfragen
rollback
Beginn der nächsten Transaktion
Nächste SQL-Anfrage
Folge von SQL-Anfragen
commit
Beginn der nächsten Transaktion
Datenbanken
Festschreiben
Rücksetzen zum
vorhergehenden commit
Festschreiben
Prof. Dr.
Stephan Kleuker
337
Atomarität (1/2)
Eine Transaktion wird entweder ganz oder gar nicht ausgeführt
• Konto-Beispiel: Umbuchung von K1 auf K2
– Abbuchung von Konto K1
– Zubuchung auf Konto K2
entweder beide Operationen werden durchgeführt oder
keine
Abbruch kann stattfinden aufgrund:
• Selbstaufgabe (z.B. Benutzerabbruch)
• Systemabbruch durch das DBMS (z.B. wg. Deadlock)
• Crash (Hardware-/Softwarefehler)
Datenbanken
Prof. Dr.
Stephan Kleuker
338
Atomarität (2/2)
Wie stellt das Datenbanksystem die Atomarität sicher?
• bei einer lesenden Transaktion: kein Problem
• bei einer schreibenden Transaktion:
– bei Abbruch: Änderungen müssen rückgängig gemacht
werden (bzw. dürfen gar nicht erst sichtbar werden)
– im Erfolgsfall: alle Änderungen müssen sichtbar werden
Realisierungsmöglichkeit: das Schattenspeicherverfahren
Datenbanken
Prof. Dr.
Stephan Kleuker
339
Schattenspeicherkopie
Realisierungsmöglichkeit: das Schattenspeicherverfahren
• Kopie einer DB: Arbeitskopie
Durchführung einer Transaktion:
• Änderung: wird nur auf der Arbeitskopie gemacht
• Commit (erfolgreich): DB := Arbeitskopie
• Abort (oder erfolgloses Commit): Arbeitskopie wegwerfen
Datenbanken
Prof. Dr.
Stephan Kleuker
340
Konsistenz
Konsistenz betrifft alle vorgegebenen Regeln:
- Datentypen und Bereiche bei Attributen
- PRIMARY KEY
- FOREIGN KEY
- CONSTRAINTs
- TRIGGER
technische Realisierung z.B. wie bei der Atomarität
Datenbanken
Prof. Dr.
Stephan Kleuker
341
Isolation
Parallel ablaufende Transaktionen sollen sich gegenseitig nicht
beeinflussen, d.h. jede läuft für sich so ab, als sei sie die
einzige Transaktion im System
Dient zur Vermeidung div. Probleme:
• lost-update-Problem
• dirty read
• non-repeatable-read
• phantom read
Datenbanken
Prof. Dr.
Stephan Kleuker
342
Beispiel: Mehrnutzerbetrieb
• zwei parallel verlaufende Transaktionen:
T1:
T2:
BEGIN A=A+100, B=B-100 END
BEGIN A=1.06*A, B=1.06*B END
• erste Transaktion transferiert 100 € vom Konto B zum Konto A
• zweite Transaktion schreibt beiden Konten 6 % Zinsen gut
• gibt keine Garantie, dass T1 vor T2 ausgeführt wird (oder
umgekehrt), wenn beide zusammen gestartet werden. Jedoch
gilt: Der Nettoeffekt muss äquivalent zu beiden Transaktionen
sein, wenn sie seriell in irgendeiner Reihenfolge ablaufen
würden
Datenbanken
Prof. Dr.
Stephan Kleuker
343
Beispiele für Schedules
• Betrachte folgenden Ablauf mit ineinander geschachtelten
Transaktionen (Schedules) :
T1:
T2:
A=A+100,
B=B-100
A=1.06*A,
B=1.06*B
• Kein Problem, aber bei diesem Beispiel:
T1:
T2:
A=A+100,
B=B-100
A=1.06*A, B=1.06*B
• Zweiter Schedule aus Sicht des DBMS:
T1:
R(A), W(A),
R(B), W(B)
T2:
R(A), W(A), R(B), W(B)
Datenbanken
Prof. Dr.
Stephan Kleuker
344
Anomalien im Mehrbenutzerbetrieb (1/4)
• Verlorengegangene Änderungen (Lost Update)
– WW-Konflikt
– Gleichzeitige Änderung desselben Objekts durch zwei
Transaktionen
– Erste Änderung (aus nicht beendeter Transaktion) wird
durch die zweite überschrieben
T1:
T2:
W(B), C
W(A),
W(A), W(B), C
C für Commit
Datenbanken
Prof. Dr.
Stephan Kleuker
345
Anomalien im Mehrbenutzerbetrieb (2/4)
• Zugriff auf schmutzige Daten (Dirty Read)
– WR-Konflikt
– “schmutzige“ Daten = geänderte Objekte, deren
Änderungen von Transaktionen stammen, die noch nicht
beendet sind
– Dauerhaftigkeit der Änderungen nicht garantiert, da
Transaktionen noch zurückgesetzt werden
– Ungültige Daten werden somit durch andere Transaktion
gelesen und (schlimmer noch!) vielleicht noch
weiterpropagiert
T1:
T2:
R(A), W(A),
R(B), W(B), Rollback
R(A), W(A), C
Datenbanken
Prof. Dr.
Stephan Kleuker
346
Anomalien im Mehrbenutzerbetrieb (3/4)
• Nicht-wiederholbares Lesen (Unrepeatable Read)
– RW-Konflikt
– Eine Transaktion sieht (bedingt durch parallele
Änderungen) während ihrer Ausführung unterschiedliche
Zustände des Objekts. Erneutes Lesen in der Transaktion
liefert somit anderen Wert
T1:
T2:
R(A), W(A), C
R(A),
R(A), W(A), C
Datenbanken
Prof. Dr.
Stephan Kleuker
347
Anomalien im Mehrbenutzerbetrieb (4/4)
• Phantom-Problem
– Spezielle Form des Unrepeatable Read
– Lesetransaktion: Mengenorientiertes Lesen über ein
bestimmtes Suchprädikat P
– Parallel läuft Änderungstransaktion, die die Menge der
sich für das Prädikat qualifizierenden Objekte ändert
– Folge: Phantom-Objekte, die durch parallele Einfügeoder Löschvorgänge in Ergebnismenge auftauchen
und/oder daraus verschwinden
Datenbanken
Prof. Dr.
Stephan Kleuker
348
Lost Update Beispiel
Zeit
Gehaltsänderung T1
SELECT GEHALT
INTO :gehalt
FROM PERS
WHERE PNR=2345;
gehalt:=
gehalt+2000;
UPDATE PERS
SET GEHALT=:gehalt
WHERE PNR=2345;
Datenbanken
DB-Inhalt
(PNR, GEHALT)
2345 39.000
Gehaltsänderung T2
SELECT GEHALT
INTO :gehalt2
FROM PERS
WHERE PNR=2345;
gehalt2:=
gehalt2+1000;
UPDATE PERS
SET
GEHALT=:gehalt2
WHERE Prof.
PNR=2345;
Dr.
Stephan Kleuker
2345 41.000
2345 40.000
349
Dirty Read Beispiel
Zeit
Gehaltsänderung T1
UPDATE PERS
SET GEHALT=
GEHALT+1000
WHERE PNR=2345
. . .
Gehaltsänderung T2
DB-Inhalt
(PNR, GEHALT)
2345
39.000
2345
40.000
2345
42.000
2345
39.000
SELECT GEHALT
INTO :gehalt
FROM PERS
WHERE PNR=2345
gehalt:=gehalt*1.05;
ROLLBACK
Datenbanken
UPDATE PERS
SET GEHALT=:gehalt
WHERE PNR=2345
COMMIT
Prof. Dr.
Stephan Kleuker
350
Unrepeatable Read Beispiel
Gehaltsänderung T1
DB-Inhalt
(PNR, GEHALT)
Gehaltssumme T2
UPDATE PERS
SET GEHALT=
GEHALT+1000
WHERE PNR=2345
. . .
UPDATE PERS
SET GEHALT=
GEHALT+2000
WHERE PNR=3456
SELECT SUM(GEHALT)
INTO :summe
FROM PERS
WHERE PNR IN
(2345,3456)
39.000
45.000
2345
40.000
3456
47.000
Inkonsistente Analyse
summe=85.000
COMMIT
Zeit
Datenbanken
2345
3456
Prof. Dr.
Stephan Kleuker
351
Unrepeatable Read Beispiel
Gehaltssumme T2
Gehaltsänderung T1
UPDATE PERS
SET GEHALT=
GEHALT+1000
WHERE PNR=2345
SELECT GEHALT INTO :g1
FROM PERS
WHERE PNR=2345
UPDATE PERS
SET GEHALT=
GEHALT+2000
WHERE PNR=3456
COMMIT
Datenbanken
SELECT GEHALT INTO :g2
FROM PERS
WHERE PNR=3456
summe:=g1+g2
Inkonsistente Analyse ohne
schmutziges Lesen in T2
Prof. Dr.
Stephan Kleuker
DB-Inhalt
(PNR, GEHALT)
2345
3456
39.000
45.000
2345
40.000
3456
47.000
Zeit
352
Phantom-Problem Beispiel
Lesetransaktion (Gehaltssumme der
Abteilung 17 bestimmen)
Änderungstransaktion (Einfügen
eines neuen Angestellten in
Abteilung 17)
SELECT SUM(GEHALT) INTO :sum1
FROM PERS
WHERE ANR=17
INSERT INTO PERS
(PNR,ANR,GEHALT)
VALUES(4567,17,55.000)
. . .
SELECT SUM(GEHALT) INTO :sum2
FROM PERS
WHERE ANR=17
IF sum1<>sum2 THEN
<Fehlerbehandlung>
Zeit
Datenbanken
Prof. Dr.
Stephan Kleuker
353
Isolationsgrade
Die gezeigten Effekte treten je nach Isolationsgrad (isolation
level) auf:
Isolationsgrad
mögliche Effekte
0
Dirty Read, Non-Repeatable-Read, Phantom
1
Non-Repeatable-Read, Phantom
2
Phantom
3
Standard ist Isolationsgrad 3; geringere Grade können für
bestimmte Anwendungen Sinn machen (z.B. grobe Statistik)
• man erreicht so eine Leistungssteigerung
Auch hier gilt: man sollte wissen, was man tut...
Datenbanken
Prof. Dr.
Stephan Kleuker
354
Isolationsgrade in SQL (eine Version)
Setzen des Isolationsgrades in SQL (aber nicht Oracle):
SET TRANSACTION ISOLATION LEVEL <nr.>;
oder
SET TRANSACTION ISOLATION LEVEL
[ READ UNCOMMITED –- nicht Oracle
| READ COMMITTED -- Oracle (default)
| REPEATABLE READ –- nicht Oracle
| SERIALIZABLE
-- Oracle];
Zusätzlich kann eingestellt werden, ob die Transaktion nur
liest:
SET TRANSACTION [ READ ONLY | READ WRITE ];
Einstellung gilt für aktuelle Verbindung, nach COMMIT oder
ROLLBACK wieder zurück gesetzt
Datenbanken
Prof. Dr.
Stephan Kleuker
355
Isolation - Realisierung
Die Isolationsgrade werden erreicht durch Sperren (locks)
• Lesesperren (shared lock, s)
• Schreibsperren (exclusive lock, x)
Lesesperren sind mit anderen Lesesperren kompatibel,
aber nicht mit Schreibsperren;
Schreibsperren sind mit nichts kompatibel
Die Sperrgranularität ist von der Implementierung eines
DBMS abhängig (z.B. Tabellensperren vs. Tupelsperren)
Oracle:
LOCK TABLE T IN EXCLUSIVE MODE;
LOCK TABLE T IN EXCLUSIVE MODE NOWAIT;
Freigabe der Sperren nach Transaktionsende
Datenbanken
Prof. Dr.
Stephan Kleuker
356
2-Phasen-Sperrprotokoll
Relationale Datenbanken arbeiten üblicherweise pessimistisch,
und zwar nach dem 2-Phasen-Sperrprotokoll
• eine Transaktion muss jedes Objekt, das sie lesen will, mit
einer Lesesperre (s-lock) versehen
• eine Transaktion muss jedes Objekt, das sie ändern will, mit
einer Schreibsperre (x-lock) versehen
• eine Transaktion darf kein Objekt mehr neu sperren,
nachdem sie die erste Sperre freigegeben hat
Datenbanken
Prof. Dr.
Stephan Kleuker
357
Probleme mit ACID-Transaktionen
• Entwurfsanwendungen: Entwurf startet, Transaktion beginnt,
nach drei Monaten ist der Entwurf fertig, Transaktion wird aber
beim commit abgebrochen (… bitte von vorne anfangen...)
gefragt sind sog. lange Transaktionen: Syncpoints oder
Sagas
• Mobile Geräte: Daten werden auf Datenbank in (Notebook,
PDA) geladen, danach Trennung von Hauptdatenbank,
Benutzer macht Änderungen, verbindet sich wieder mit der DB,
was tun? alle Änderungen zurückweisen? Haupt-DB
sperren?
Synchronisation ist gefragt, evtl. mit vorheriger Anzeige
möglicher Arbeitsbereiche
Datenbanken
Prof. Dr.
Stephan Kleuker
358
14. Wiederholung: Anfragen in SQL
• Kompakte Zusammenfassung von SQL-Anfragen
• Feedback
• Ihre Fragen
Datenbanken
Prof. Dr.
Stephan Kleuker
359
Aufbau einer SQL-Anfrage [KL]
Schlüssel- Auswertungs- Inhalt
wort
reihenfolge
SELECT
6
Attribute, Aggregatsfunktionen
FROM
1
WHERE
2
GROUP BY
3
HAVING
4
ORDER BY
5
Datenbanken
Liste von Tabellen, deren kartesisches
Produkt betrachtet wird
Boolescher Ausdruck, zur Auswahl von
Zeilen des kartesischen Produkts
Liste von Attributen, nach denen
gruppiert wird
Attribute aus der GROUP-BY-Liste
oder Aggregatsfunktionen
Attribute (oder Aggregatsfunktionen
bei GROUP BY) mit Sortierreihenfolge
Prof. Dr.
Stephan Kleuker
360
Erinnerung: Mondial
City
Is_member
Name
Country
Province
Population
Longitude
Latitude
Country
Organization
Type
Aalborg
DK
Denmark
113865
10
57
D
UN
member
Aarau
CH
AG
?
?
?
D
UNESCO
member
Aarhus
DK
Denmark
194345
10.1
56.1
D
UNHCR
member
Abancay
PE
Apurimac
?
?
?
DK
ICRM
National Society
Abeokuta
WAN
Nigeria
377000
?
?
DK
IEA
member
Aberdeen
GB
Grampian
219100
?
?
DK
IFAD
Category I
Country
Encompasses
Name
Code
Capital
Province
Area
Population
Country
Continent
Percentage
Austria
A
Vienna
Vienna
83850
8023244
TR
Asia
68
Afghanistan
AFG
Kabul
Afghanistan
647500
22664136
TR
Europe
32
Albania
AL
Tirane
Albania
28750
3249136
TT
America
100
Angola
ANG
Luanda
Luanda
1246700
10342899
UAE
Asia
100
Azerbaijan
AZ
Baku
Azerbaijan
86600
7676953
USA
America
100
Belgium
B
Brussels
Brabant
30510
10170241
UZB
Asia
100
Datenbanken
Prof. Dr.
Stephan Kleuker
361
Anfragen an eine Tabelle
• Geben Sie alle Städte mit mehr als 9000000 Einwohnern aus
SELECT City.Name
FROM City
WHERE City.Population>9000000
•
•
•
•
•
NAME
-----------Mexico City
Karachi
Sao Paulo
Mumbai
Seoul
erst in FROM die betroffenen Tabellen,
dann Randbedingungen in WHERE
dann die gewünschte Ausgabe in SELECT,
warum City.Name, was wäre noch möglich
Erinnerung: In Oracle Semikolon hinter dem Befehl
Datenbanken
Prof. Dr.
Stephan Kleuker
362
Anfragen an mehrere Tabellen
• Geben Sie alle Städte mit mehr als 9000000 Einwohnern
und dem zugehörigen Land aus
SELECT City.Name, Country.Name
FROM City, Country
WHERE Country.Code= City.Country
AND City.Population>9000000
NAME
-----------Mexico City
Karachi
Sao Paulo
Mumbai
Seoul
NAME
----------Mexico
Pakistan
Brazil
India
South Korea
• Vorgriff: Warum könnte die Ergebnistabelle nicht in einer
FROM-Zeile genutzt werden?
Datenbanken
Prof. Dr.
Stephan Kleuker
363
Mehrere Informationen aus einer Tabelle
• Geben Sie die Kürzel aller Länder aus, in denen es mindestens zwei
Städte mit mehr als 5000000 Einwohner gibt
SELECT C1.Country
COUN
---FROM City C1, City C2
IND
WHERE C1.Country=C2.Country
CN
AND C1.Name<C2.Name
CN
AND C1.Population>5000000
CN
AND C2.Population>5000000
BR
• Ansatz: Mehrere Informationen aus einer Tabelle, d.h. diese Tabelle
kommt mehrmals in der FROM-Zeile vor
• Was würde durch SELECT DISTINCT C1.Country ... geändert
• Anmerkung: Gibt alternativen Ansatz
Datenbanken
Prof. Dr.
Stephan Kleuker
364
Schachtelung von Anfragen
• Geben Sie die Namen aller Länder aus, in denen es
mindestens zwei Städte mit mehr als 5000000 Einwohner gibt
SELECT Country.Name
FROM Country, (SELECT C1.Country Land
FROM City C1, City C2
WHERE C1.Country=C2.Country
AND C1.Name<C2.Name
AND C1.Population>5000000
AND C2.Population>5000000) Moloch
WHERE Country.Code = Moloch.Land
• Ergebnisse von SELECT-Anfragen sind wieder
Tabellen, diese können als normale Tabellen in
die FROM-Zeile gesetzt werden
• Man kann Ergebnisspalten und Ergebnistabellen
temporär umbenennen
Datenbanken
Prof. Dr.
Stephan Kleuker
NAME
-------India
China
China
China
Brazil
365
Alternative Lösung
• Geben Sie die Namen aller Länder aus, in denen es mindestens
zwei Städte mit mehr als 5000000 Einwohner gibt
SELECT Country.Name
FROM Country, City C1, City C2
WHERE Country.Code=C1.Country
AND C1.Country=C2.Country
AND C1.Name<C2.Name
AND C1.Population>5000000
AND C2.Population>5000000
NAME
-------India
China
China
China
Brazil
• Generell kann es verschiedene Lösungsansätze geben
• Man sucht zuerst nach einer laufenden Lösung und versucht,
diese zu optimieren
• Grundsätzlich sollen die verknüpften Tabellen möglichst klein
sein (evtl. ist deshalb sogar die erste Lösung zu bevorzugen),
hängt von DB-System ab
Datenbanken
Prof. Dr.
Stephan Kleuker
366
Ergebnisse ordnen
• Geben Sie die unterschiedlichen Namen aller Länder in
alphabetischer Reihenfolge aus, in denen es mindestens
zwei Städte mit mehr als 5000000 Einwohner gibt
SELECT DISTINCT Country.Name
FROM Country, City C1, City C2
WHERE Country.Code=C1.Country
AND C1.Country=C2.Country
AND C1.Name<C2.Name
AND C1.Population>5000000
AND C2.Population>5000000
ORDER BY Country.Name ASC
NAME
-------Brazil
China
India
• ASC (ascending), DESC (descending)
• Hinweis: ASC kann weggelassen werden
Datenbanken
Prof. Dr.
Stephan Kleuker
367
Aggregatsfunktionen
• Elemente zählen
SELECT COUNT(City.Name) FROM City
COUNT(CITY.NAME)
---------------3053
hätte auch COUNT(*) nutzen können
• Minimale Werte, maximale Werte, Anzahl, Summe bestimmen
SELECT MIN(City.Population), MAX(City.Population),
SUM(City.Population), AVG(City.Population)
FROM City
MIN(CITY.POPULAT MAX(CITY.POPULAT SUM(CITY.POPULAT AVG(CITY.POPULAT
---------------- ---------------- ---------------- ---------------392
10229262
1117388947
429434,645
• Achtung!! Folgende Anfrage funktioniert nicht
SELECT Country.Name, COUNT(City.Name)
FROM Country, City
WHERE Country.Code=City.Country
FEHLER in Zeile 1:
ORA-00937: keine Gruppenfunktion für Einzelgruppe
Datenbanken
Prof. Dr.
Stephan Kleuker
368
Rechnen in der SELECT-Zeile
•
Geben Sie für jedes Land in Europa in Prozent an, welchen
Anteil die Landesfläche an der Fläche von Gesamteuropa hat
SELECT Country.Name,
Country.Area*Encompasses.Percentage/Continent.Area
FROM Country, Encompasses, Continent
WHERE Country.Code=Encompasses.Country
AND Encompasses.Continent=Continent.Name
AND Continent.Name='Europe'
NAME
COUNTRY.AREA*ENCOMPASSES.PERCENTAGE/CONTINENT.AREA
------- -------------------------------------------------Albania
,300653972
Greece
1,37976644
Macedonia
,264920594
...
Datenbanken
Prof. Dr.
Stephan Kleuker
369
Gruppierungen (1/3)
• Geben Sie für jedes Land die
Anzahl der Städte mit mehr als
5000000 Einwohner aus, Ausgabe:
Ländername, Stadtanzahl
• Ansatz: Zunächst eine
Standardanfrage, in der im
Ergebnis alle wichtigen
Informationen im Einzelformat
auftreten
SELECT Country.Name,
City.Name
FROM Country,City
WHERE
Country.Code=City.Country
AND City.Population>5000000
Datenbanken
NAME
------------United States
Brazil
Russia
Iran
India
Thailand
Egypt
Japan
Hong Kong
Mexico
Colombia
Peru
Turkey
China
Pakistan
Brazil
Nigeria
United Kingdom
China
China
India
Indonesia
South Korea
Prof. Dr.
Stephan Kleuker
NAME
-------------New York
Rio de Janeiro
Moscow
Tehran
New Delhi
Bangkok
Cairo
Tokyo
Hong Kong
Mexico City
Bogota
Lima
Istanbul
Shanghai
Karachi
Sao Paulo
Lagos
London
Beijing
Tianjin
Mumbai
Jakarta
370
Seoul
Gruppierungen (2/3)
• Im nächsten Schritt stelle man
sich das Ergebnis sortiert
(angeordnet, gruppiert) nach
dem Attribut vor, das allen
Elementen gemeinsam ist
SELECT Country.Name,
City.Name
FROM Country,City
WHERE
Country.Code=City.Country
AND
City.Population>5000000
ORDER BY Country.Name
• Auf den entstehenden Gruppen
können Aggregatsfunktionen
genutzt werden (MIN, MAX,
AVG, SUM, COUNT)
)
Datenbanken
NAME
--------------Brazil
Brazil
China
China
China
Colombia
Egypt
Hong Kong
India
India
Indonesia
Iran
Japan
Mexico
Nigeria
Pakistan
Peru
Russia
South Korea
Thailand
Turkey
United Kingdom
United States
Prof. Dr.
Stephan Kleuker
NAME
-------------Rio de Janeiro
Sao Paulo
Shanghai
Tianjin
Beijing
Bogota
Cairo
Hong Kong
New Delhi
Mumbai
Jakarta
Tehran
Tokyo
Mexico City
Lagos
Karachi
Lima
Moscow
Seoul
Bangkok
Istanbul
London
New York
371
Gruppierungen (3/3)
• Es wird also nach dem gemeinsamen
Attribut gruppiert und auf jeder dieser
Gruppen die Aggregatsfunktion
durchgeführt
SELECT
Country.Name,COUNT(City.Name)
FROM Country, City
WHERE Country.Code=City.Country
AND City.Population>5000000
GROUP BY Country.Name
• Hinweis: Sortierung nach Ländernamen
in der Ausgabe ist zufällig
Datenbanken
Prof. Dr.
Stephan Kleuker
NAME
--------------Brazil
China
Colombia
Egypt
India
Hong Kong
Indonesia
Iran
Japan
Mexico
Nigeria
Pakistan
Peru
Russia
South Korea
Thailand
Turkey
United Kingdom
United States
COU
--2
3
1
1
2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
372
Alternative Suche nach mehrfachen Werten
• Geben Sie die Kürzel aller Länder aus, in denen es mindestens
zwei Städte mit mehr als 5000000 Einwohner gibt
COUN
---IND
CN
BR
SELECT City.Country
FROM City
WHERE City.Population>5000000
GROUP BY City.Country
HAVING COUNT(*)>=2
• Will man die Ländernamen dazu ausgeben, muss man entweder
Anfragen schachteln oder in der GROUP-BY-Zeile tricksen
SELECT Country.Name,City.Country
FROM Country, City
WHERE Country.Code=City.Country
AND City.Population>5000000
GROUP BY Country.Name,City.Country
HAVING COUNT(*)>=2
Datenbanken
Prof. Dr.
Stephan Kleuker
NAME
-------India
China
Brazil
COUN
---IND
CN
BR
373
Anfragen in Anfragen
• [Variante] Geben Sie die Namen aller Länder aus, in denen es
mindestens zwei Städte mit mehr als 5000000 Einwohner gibt
SELECT Country.Name
NAME
FROM Country
-------India
WHERE Country.Code IN
Brazil
(SELECT City.Country
China
FROM City
WHERE City.Population>5000000
GROUP BY City.Country
HAVING COUNT(*)>=2)
• Geben Sie die Namen aller Länder, für die mehr als
100 Städte eingetragen sind (eine Variante)
NAME
-------------Russia
United Kingdom
China
United States
Brazil
SELECT Country.Name
FROM Country
WHERE 100< (SELECT COUNT(*)
FROM City
WHERE Country.Code=City.Country)
Datenbanken
Prof. Dr.
Stephan Kleuker
374
EXISTS
• Geben Sie die Namen aller Länder aus, in denen
es mindestens eine Stadt mit mehr als 5000000
Einwohner gibt
NAME
-------------Russia
United Kingdom
China
United States
Brazil
...
SELECT Country.Name
FROM Country
WHERE EXISTS( SELECT *
FROM City
WHERE Country.Code=City.Country
AND City.Population>5000000)
• Geben Sie die Namen aller Länder aus, in denen alle
NAME
eingetragenen Städtenamen mit „A“ beginnen
------------SELECT Country.Name
Andorra
Jordan
FROM Country
Samoa
WHERE NOT EXISTS( SELECT *
Paraguay
...
FROM City
WHERE Country.Code=City.Country
AND NOT(City.Name LIKE 'A%'))
Datenbanken
Prof. Dr.
Stephan Kleuker
375
NULL-Werte (1/2)
• SQL nutzt eine dreiwertige Logik (Wahr, falsch, undefiniert),
WHERE wählt die Zeilen aus, für die die Bedingung, nach „Wahr“
ausgewertet wird
• Geben Sie die Kürzel aller Länder aus, für die eine Stadt ohne
Einwohnerzahl eingegeben ist
SELECT DISTINCT City.Country
FROM City
WHERE City.Population IS NULL
• Achtung! Nie, nie!
SELECT DISTINCT City.Country
FROM City
WHERE City.Population = NULL
Datenbanken
Prof. Dr.
Stephan Kleuker
376
NULL-Werte
• Geben Sie die Kürzel der Länder aus, für die für alle Städte keine
Einwohnerzahl eingetragen ist
SELECT DISTINCT C1.Country
FROM City C1
WHERE NOT EXISTS (SELECT *
FROM City C2
WHERE C1.Country=C2.Country
AND NOT (C2.Population IS NULL))
Datenbanken
Prof. Dr.
Stephan Kleuker
377
Mengenoperationen
• Geben Sie die Kürzel aller Länder aus, bei denen alle
eingetragenen Städtenamen nicht mit „B“, „M“ oder „S“
beginnen
SELECT City.Country
FROM City
MINUS
SELECT City.Country
FROM City
WHERE City.Name LIKE 'B%'
OR City.Name LIKE 'M%'
OR City.Name LIKE 'S%'
• Anmerkung: In Standard-SQL keine komplexere Unterstützung
von regulären Ausdrücken
Datenbanken
Prof. Dr.
Stephan Kleuker
378
Verwirrung?
• Folgendes ist verboten
SELECT Country.Name, COUNT(City.Name)
FROM Country, City
WHERE Country.Code=City.Country
• Folgendes klappt
SELECT Country.Name, 42 Zahl
FROM Country
NAME
ZAHL
--------- ---Albania
42
Greece
42
...
• Folgendes bringt eventuell vorher erwartetes Ergebnis
SELECT Country.Name, Alle.Zahl
FROM Country, (SELECT COUNT(City.Name) Zahl
FROM City) Alle
• oder auch
SELECT Country.Name,
(SELECT COUNT(City.Name)
FROM City) Zahl
FROM Country
Datenbanken
Prof. Dr.
Stephan Kleuker
NAME
--------Albania
Greece
Macedonia
...
ZAHL
---3053
3053
3053
379