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