Informatik (Fach) / Datenbanksysteme1 (Lektion)

Diese Lektion wurde von daschinka erstellt.

Lektion lernen

zurück  |  weiter 1 / 1

• Sortieren • In SQL mit ORDER BY A1, A2, ... • Steht am Schluss der Anfrage  Nach Attribut kann man ASC für aufsteigend (Default) oder DESC für absteigend angeben • Nur Attribute der SELECT-Klausel verwendbar
• Aggregation • Berechnet Eigenschaften ganzer Tupel-Mengen • Arbeitet also Tupel-übergreifend • Aggregatfunktionen in SQL: – count Anzahl der Tupel bzw. Werte – sum Summe der Werte einer Spalte – avg Durchschnitt der Werte einer Spalte – max größter vorkommender Wert der Spalte – min kleinster vorkommender Wert • Aggregate können sich erstrecken: – auf das gesamte Anfrageergebnis – auf einzelne Teilgruppen von Tupeln (siehe später) • Aggregatfunktionen stehen in der Select-Klausel • Beispiel: Gesamtzahl und Durchschnitt der Einwohnerzahl aller Länder, die mit ‘B‘ beginnen: select sum (Einw), avg (Einw)  from länder  where LName like ‘B%‘ • Ergebnis ist immer ein einzelnes Tupel: Keine Mischung aggregierte/nicht aggregierte Attribute • Aggregate wie min oder max sind ein einfaches Mittel, um Eindeutigkeit bei Subqueries herzustellen  (vgl. Kapitel 4, Folie 43) • NULL-Werte werden ignoriert (auch bei count) • Eine Duplikatelimination kann erzwungen werden – count (distinct KName) zählt verschiedene Kunden – count (all KName) zählt alle Einträge (außer NULL) – count (KName) ist identisch mit count (all KName) – count (*) zählt die Tupel des Anfrageergebnisses (macht nur bei NULL-Werten einen Unterschied) • Beispiel: Produkt (PName, Preis, ...) Alle Produkte, mit unterdurchschnittlichem Preis: select * from Produkt where Preis < (select avg (Preis) from Produkt)
• Gruppierung • Aufteilung der Ergebnis-Tupel in Gruppen • Ziel: Aggregationen • Wegen Relationen-Eigenschaft des Ergebnisses Einschränkung der select-Klausel. Erlaubt sind: – Attribute aus der Gruppierungsklausel (incl. arithmetischer Ausdrücke etc.) – Aggregationsfunktionen auch über andere Attribute,  z.B. count (*) – in der Regel select * from...group by… nicht erlaubt
• Die Having-Klausel • Motivation: Ermittle das Gesamt-Einkommen in jeder Abteilung, die  mindestens 5 Mitarbeiter hat • In SQL nicht möglich: select ANr, sum (Gehalt)  from Mitarbeiter where count(*) >= 5 GEHT NICHT ! group by ANr STATT DESSEN: having count(*) >= 5 • Grund: Gruppierung wird erst nach SELECT-FROM-WHERE-Operationen ausgeführt
• Konsequenzen • Automatisch sind in dieser View alle Tupel der Basisrelation, die die Selektionsbedingung erfüllen • An diese können beliebige Anfragen gestellt werden, auch in Kombination mit anderen Tabellen (Join) etc: select * from Buchhalter where Name like ‘B%‘ • In Wirklichkeit wird lediglich die View-Definition in die Anfrage eingesetzt und dann ausgewertet: Buchhalter: select PNr,Name,Gehalt  from Mitarbeiter where ANr=01 → select * from Buchhalter where Name like ‘B%‘ → select * from ( select PNr, Name, Gehalt  from Mitarbeiter where ANr=01 ) where Name like ‘B%‘ • Bei Updates in der Basisrelation (Mitarbeiter) ändert sich auch die virtuelle Relation (Buchhalter) • Umgekehrt können (mit Einschränkungen) auch  Änderungen an der View durchgeführt werden, die sich  dann auf die Basisrelation auswirken • Eine View kann selbst wieder Basisrelation einer neuen  View sein (View-Hierarchie) • Views sind ein wichtiges Strukturierungsmittel für  Anfragen und die gesamte Datenbank Löschen einer View: drop view VName
• In Views erlaubte Konstrukte • Folgende Konstrukte sind in Views erlaubt: – Selektion und Projektion  (incl. Umbenennung von Attributen, Arithmetik) – Kreuzprodukt und Join – Vereinigung, Differenz, Schnitt – Gruppierung und Aggregation – Die verschiedenen Arten von Subqueries • Nicht erlaubt: – Sortieren
• Insert/Delete/Update auf Views • Logische Datenunabhängigkeit: – Die einzelnen Benutzer-/Anwendungsgruppen sollen ausschließlich über das externe Schema (d.h. Views) auf die Datenbank zugreifen (Übersicht, Datenschutz) – Insert, Delete und Update auf Views erforderlich • Effekt-Konformität – View soll sich verhalten wie gewöhnliche Relation – z.B. nach dem Einfügen eines Tupels muß das Tupel in der View auch wieder zu finden sein, usw. • Mächtigkeit des View-Mechanismus – Join, Aggregation, Gruppierung usw. – Bei komplexen Views Effekt-Konformität unmöglich • Wir untersuchen die wichtigsten Operationen in der View-Definition auf die Effekt-Konformität - Projektion -Selektion -Join -Aggregation und Gruppierung • Wir sprechen von Projektions-Sichten usw. – Änderung auf Projektionssicht muß in Änderung der Basisrelation(en) transformiert werden • Laufendes Beispiel: – MGA (Mitarbeiter, Gehalt, Abteilung) – AL (Abteilung, Leiter)    
• Projektionsichten • Beispiel: create view MA as select Mitarbeiter, Abteilung from MGA • Keine Probleme beim Löschen und Update: delete from MA where Mitarbeiter = ... → delete from MGA where Mitarbeiter = ... • Bei Insert müssen wegprojitierte Attribute durch NULL-Werte oder bei der Tabellendefinition festgelegte  Defaultwerte belerg werden: insertinto MA values(‘Weber‘, 001) → insertinto MGA values(‘Weber‘, NULL, 001) • Problem bei Duplikatelimination (select distinct):  Keine eindeutige Zuordnung zwischen Tupeln der View und der Basisrelation: • Bei Arithmetik in der Select-Klausel: Rückrechnung wäre erforderlich: create view P as select 3*x*x*x+2*x*x+x+1 as y from A • Der folgende Update wäre z.B. problematisch: update P set y=0 where... • womit müsste x besetzt werden? Mit der Nullstelle des Polynoms f(x)=3x3+2x2+x+1 Nullstellensuche kein triviales mathematisches Problem Kein insert/delete/update bei distinct/Arithmetik
• Selektionssichten • Beispiel: create view MG as select * from MGA where Gehalt >= 20 • Beim Ändern (und Einfügen) kann es passieren, dass ein Tupel aus der View verschwindet, weil es die Selektionsbedingung nicht mehr erfüllt: update MG set Gehalt = 19 where Mitarbeiter = ‘Huber‘ • Huber ist danach nicht mehr in MG • Dies bezeichnet man als Tupel-Migration:Tupel verschwindet, taucht aber vielleicht dafür in anderer View auf • Dies ist manchmal erwünscht – Mitarbeiter wechselt den zuständigen Sachbearbeiter, jeder Sachbearbeiter arbeitet mit „seiner“ View • Manchmal unerwünscht – Datenschutz • Deshalb in SQL folgende Möglichkeit: create view MG as select * from MGA where Gehalt >= 20 with check option • Die Tupel-Migration wird dann unterbunden Fehlermeldung bei: update MG set Gehalt = 19 where
• Join-Views • Beispiel: create view MGAL as select Mitarbeiter, Gehalt, MGA.Abteilung, Leiter  from MGA, AL where MGA.Abteilung = AL.Abteilung • Insert in diese View nicht eindeutig übersetzbar: insert into MGAL values(‘Schuster‘, 30, 001, ‘Boss‘) → inser tinto MGA values(‘Schuster‘, 30, 001) wenn kein Tupel (001, ‘Boss‘) in AL existiert: → insert into AL values(001, ‘Boss‘)  → update AL set Leiter=‘Boss‘ where Abteilung=001 oder Fehlermeldung ? • Daher: Join-View in SQL nicht updatable
• Anzeige
• Aggregation, group by, Subquery • Auch bei Aggregation und Gruppierung ist es nicht möglich, eindeutig auf die Änderung in der Basisrelation zu schließen • Subqueries sind unproblematisch, sofern sie keinen Selbstbezug aufweisen (Tabelle in from-Klausel der View wird nochmals in Subquery verwendet)   Eine View, die keiner der angesprochenenProblemklassen angehört, heisst Updatable View.Insert, delete und update sind möglich
• Materialisierte View Hier nur Begriffserklärung: • Eine sog. materialisierte View ist keine virtuelle Relation sondern eine real gespeicherte • Der Inhalt der Relation wurde aber durch eine Anfrage an andere Relationen und Views ermittelt • In SQL einfach erreichbar durch Anlage einer Tabelle MVName und Einfügen der Tupel mit: insert into MVName (select ... from ... where) • Bei Änderungen an den Basisrelationen keine automatische Änderung in MVName und umgekehrt • DBS bieten oft auch spezielle Konstrukte zur Aktualisierung (Snapshot, Trigger), kein Standard-SQL Bei dem Begriff View meinen wir nicht materialisierte Views
• Rechtevergabe • Basiert in SQL auf Relationen bzw. Views • Syntax: grant Rechteliste on Relation bzw. View to Benutzerliste [with grant option] • Rechteliste: – all [privileges] – select, insert, delete (mit Kommas sep.) – update (optional in Klammern: Attributnamen)  Benutzerliste: – Benutzernamen (mit Passwort identifiziert) – to public (an alle) • Grant Option: Recht, das entsprechende Privileg selbst weiterzugeben • Rücknahme von Rechten: revoke Rechteliste on Relation from Benutzerliste [restrict] Abbruch, falls Recht bereits weitergegeben [cascade] ggf. Propagierung der Revoke-Anweisung

zurück  |  weiter 1 / 1