K5 Die Standardsprache SQL Flashcards
Sprachentwicklung von SQL
SQL wurde „de facto“-Standard in der relationalen Welt (1986 von ANSI, 1987 von ISO akzeptiert)
Wesentliche Stufen der Weiterentwicklung des Standards
- SQL2 (1992): rein relational
- (SQL3) SQL:1999: objekt-relational
Mächtigkeit von SQL
Auswahlvermögen umfasst das des Relationenkalküls und der Relationenalgebra: relational vollständig
Aussprache von SQL
Englische Aussprache von SQL
- SQL kann als Nachfolger von SEQUEL (Structured English Query Language) betrachtet werden und wird daher oft [ˈsiːkwəl] ausgesprochen
- Speziell im Umfeld von:
- Oracle
- Mircosoft
- Gemäß Standard wird es als [ɛskjuˈː ɛl] ausgesprochen
- So auch im Umfeld von:
- MySQL
- PostgreSQL
SQL: abbildungsorientierte Sprache
SQL92-Syntax:
- INSERT IN TO
- DETLETE FROM
- UPDATE
- CREATE TABLE
- CREATE VIEW
- DROP TABLE
- DROP VIEW
SQL92-Syntax
- SELECT
SQL92-Syntax
- condition
- expr
- predicate
- comparison
- arithm.op
SQL92-Syntax
- from
- function
- set
- attr-spec
- attr-defn
Anfragen: SELECT-Anweisung
Anfragen: Untermengenbildung und Benennung von Ergebins-Spalten
Welche Dramen von Goethe wurden nach 1800 uraufgeführt?
Anfragen: Test auf Mengenzugehörigkeit
Anfragen: Geschachtelte Abbildung
Welche Figuren kommen in Dramen von Schiller oder Goethe vor?
Anfragen: Symmetrische Abbildung und deren Vorteile
- Finde die Figuren und ihre Autoren, die in Dramen von Schiller oder Goethe vorkommen.
- Finde die Dichter (AUTOR, G-ORT), deren Dramen von Dichtern mit demselben Geburtsort (G-ORT) kritisiert wurden
- Finde die Schauspieler (NAME, W-ORT), die bei in Weimar urauf geführten Dramen an ihrem Wohnort als ’Held’ mitgespielt haben.
Anfragen: Auswertungs-/Erklärungsmodell
Anfragen: Benutzer-spezifizierte Reihenfolge der Ausgabe
Finde die Schauspieler, die an einem Ort wohnen, an dem sie gespielt haben, sortiert nach Name (aufsteigend), W - Ort (absteigend).
Anfragen: Aggregatfunktionen
- Bestimme das Durchschnittsgehalt der Schauspieler, die älter als 50 Jahre sind (GEHALT und ALTER seien Attribute von SCHAUSPIELER).
- An wievielen (unterschiedlichen) Orten wurden Dramen uraufgeführt (U - Ort)?
- Welches Drama wurde zuerst aufgeführt ?
Anfragen: Partitionierung
- Liste alle Abteilungen und das Durchschnittsgehalt ihrer Angestellten auf (Monatsgehalt).
- Liste die Abteilungen zwischen K50 und K60 auf, bei denen das Durchschnittsalter ihrer Angestellten kleiner als 30 ist.
Anfragen: Hierarchische Beziehungen auf einer Relation
Finde die Angestellten, die mehr als ihre (direkten) Manager verdienen (Ausgabe: NAME, GEHALT, NAME des Managers).
Anfragen: Auswertung von SELECT-Anweisungen – Erklärungsmodell
- Die auszuwertenden Relationen werden durch die FROM-Klausel bestimmt. Alias-Namen erlauben die mehrfache Verwendung derselben Relation.
- Das Kartesische Produkt aller Relationen der FROM-Klausel wird gebildet.
- Tupeln werden ausgewählt durch die WHERE-Klausel.
- Qualifizierte Tupeln werden gemäß der GROUP-BY-Klausel in Gruppen eingeteilt.
- Gruppen werden ausgewählt, wenn sie die HAVING-Klausel erfüllen. Prädikat in der HAVING-Klausel darf sich nur auf Gruppeneigenschaften beziehen (Attribute der GROUP-BY-Klausel oder Anwendung von Aggregat-Funktionen).
- Die Ausgabe wird durch die Auswertung der SELECT-Klausel abgeleitet. Wurde eine GROUP-BY-Klausel spezifiziert, dürfen als SELECT-Elemente nur Ausdrücke aufgeführt werden, die für die gesamte Gruppe genau einen Wert ergeben (Attribute der GROUP-BY-Klausel oder Anwendung von AggregatFunktionen).
- Die Ausgabereihenfolge wird gemäß der ORDER-BY-Klausel hergestellt. Wurde keine ORDER-BY-Klausel angegeben, ist die Ausgabereihenfolge systembestimmt (indeterministisch).
Anfragen: Erklärungsmodell – Die Summe der Gehälter pro Abteilung, in der mindestens ein Mitarbeiter 40 Jahre oder älter ist, soll berechnet werden:
Anfragen: Suchbedingungen, (Nicht-)quantifizierte Prädikate
Anfragen: IN-Prädikate
Anfragen: Ähnlichkeitssuche
Unterstützung der Suche nach Objekten, von denen nur Teile des Inhalts bekannt sind oder die einem vorgegebenen Suchkriterium möglichst nahe kommen.
Anfragen: Quantifizierung
- Finde die Manager, die mehr verdienen als alle ihre direkten Untergebenen
- Finde die Namen der Schauspieler, die mindestens einmal gespielt haben (… nie gespielt haben)
- Finde die Namen aller Schauspieler, die alle Rollen gespielt haben
Anfragen: Prädikate über Nullwerten
Attributspezifikation: Es kann für jedes Attribut festgelegt werden, ob NULL-Werte zugelassen sind oder nicht
Verschiedene Bedeutungen von Nullwerten:
- Datenwert ist momentan nicht bekannt
- Attributwert existiert nicht für ein Tupel
Anfragen: Häufige genutzte Alternativen zu Nullwerten
Häufige genutzte Alternativen zu Nullwerten
- Defaultwerte (Risiko: Eingabe eines falschen Wertes)
- Leere Zeichenkette, „None“, „Unbekannt“ oder spezielle Symbole wie „-“ oder „#“ bei Stringwerten
- „- 1“ bei Attributen mit positiven Zahlen wie z.B. Alter, Gehalt
- Untypische Werte wie „01.01.0000“ bei Datumsangaben
Generelle Aspekte:
- Welche Auswirkungen haben solche Alternativwerte auf Wertvergleiche, Aggregationen oder Statistikanalysen?
- Inkonsequente Nutzung führt zu Inkonsistenzen (Konsequente Nutzung kann aber nur begrenzt vom System kontrolliert werden)
- Bis auf Defaultwerte nicht auf Fremdschlüsselattribute anwendbar
Anfragen: Alternative Modellierung von optionalen Eigenschaften
Verwendung eines zusätzlichen Boolschen Attributes pro optionaler Eigenschaft
- Belegung mit 1 wenn Eigenschaft existiert
- Belegung mit 0 wenn Eigenschaft nicht existiert
- Belegung mit Nullwert falls Existenz der Eigenschaft unbekannt ist Anm.: In den beiden letzten Fällen beinhaltet das eigentl. Attribut auch einen Nullwert
- Vorteil: Löst Konflikt zwischen den Nullwertsemantiken „unbekannt“ und „nicht existent“
- Nachteil: Erhöht Komplexität von Schema und Anfragen sowie den Speicherbedarf
Anfragen: Weiteres zu Nullwerten
Anfragen: Vermeintliche Anfrageäquivalenzen
- Finde alle Abteilungen deren Leiter nicht mehr als 50k verdient
- Finde die Person mit dem maximalen Gehalt.
- Finde alle Personen aus Abteilung K56 die mehr als 40k verdienen.
Anfragen: Vermeintliche Tautologien
- Finde alle Personen die weniger, gleich oder mehr als 40k verdienen.
- Finde alle Personen die entweder in Abteilung K45 oder nicht in Abteilung K45 arbeiten
SQL Fiddle
Datenmanipulation: Einfügen von Tupeln
- Füge den Schauspieler Garfield mit der PNR 4711 ein
- Füge die Schauspieler aus HH in die Relation TEMP ein
Datenmanipulation: Löschen von Tupeln mit Hilfe von Suchklauseln
- Lösche den Schauspieler mit der PNR 4711.
- Lösche alle Schauspieler, die nie gespielt haben.
Datenmanipulation: Ändern von Tupeln mit Hilfe von Suchklauseln
Datenmanipulation: Um mengenbasierte Änderungen in MySQL auszuführen, muss ‚Safe Updates‘ deaktiviert werden
Datendefinition: Ziel der SQL-Normierung
- möglichst große Unabhängigkeit der DB-Anwendungen von speziellen DBS
- einheitliche Sprachschnittstelle genügt nicht!
- Beschreibung der gespeicherten Daten und ihrer Eigenschaften nach einheitlichen und verbindlichen Richtlinien ist genauso wichtig
Datendefinition: Definitionsschema
Datendefinition: Definition von Schemata
Datendefinition: Datentypen
Datendefinition: Definition von Domains
Datendefinition: Definition von Attributen
Datendefinition: Erzeugung von Basisrelationen
Beziehungen: (1:n)-Beziehung
Beziehungen: (1:1)-Beziehung
Beziehungen: Symmetrische (1:1)-Beziehung
Beziehungen: (n:m)-Beziehung
Beziehungen: Reflexive (1:n)-Beziehung
- Mit Hilfe dieser Lösung kann die Personal-Hierarchie eines Unternehmens dargestellt werden; die referentielle Beziehung stellt hier eine partielle Funktion dar, da die „obersten“ Manager einer Hierarchie keinen Manager haben
- MNR … NOT NULL lässt sich nur realisieren, wenn die „obersten“ Manager als ihre eigenen Manager interpretiert werden; dadurch treten jedoch Referenzzyklen auf, was die Frageauswertung und die Konsistenzprüfung erschwert
- Welche Beziehungsstruktur erzeugt MNR … UNIQUE NOT NULL?
Abbildung von Beziehungen - Zusammenfassung
- Relationenmodell ‚hat‘ wertbasierte Beziehungen (im Gegensatz hierzu haben objektorientierte Datenmodelle referenzbasierte Beziehungen)
- Fremdschlüssel (FS) und zugehöriger Primärschlüssel/Schlüsselkandidat (PS/SK) repräsentieren eine Beziehung (gleiche Wertebereiche!)
- Alle Beziehungen (FS ↔ PS/SK) sind binär und symmetrisch
- Auflösung einer Beziehung geschieht durch Suche
- Es sind i. allg. k (1:n)-Beziehungen zwischen zwei Relationen möglich
Datendefinition: Beispiel
Illustriere DB-Schema
Datendefinition: Beispiel
Definiere Wertebereiche
Datendefinition: Beispiel
Definiere Relationen
Wartung von Beziehungen: Relationale Invarianten / referentielle Integrität
- Primärschlüsselbedingung: Eindeutigkeit, keine Nullwerte!
- Fremdschlüsselbedingung: Zugehöriger PS (SK) muss existieren
Wartung von Beziehungen: Potentielle Gefährdung
Operationen in der Sohn-Relation
- Einfügen eines Sohn-Tupels
- Ändern des FS in einem Sohn-Tupel
- Löschen eines Sohn-Tupels
- Welche Maßnahmen sind erforderlich?
- Beim Einfügen erfolgt eine Prüfung, ob in einem Vater-Tupel ein PS/SK-Wert gleich dem FS-Wert des einzufügenden Tupels existiert
- Beim Ändern eines FS-Wertes erfolgt eine analoge Prüfung
- Löschen erfordert keine Maßnahme
Operationen in der Vater-Relation
- Löschen eines Vater-Tupels
- Ändern des PS/SK in einem Vater-Tupel
- Einfügen eines Vater-Tupels (unproblematisch)
- Welche Reaktion ist wann möglich/sinnvoll?
- Verbiete Operation
- Lösche/ändere rekursiv Tupel mit zugehörigen FS-Werten
- Falls Sohn-Tupel erhalten bleiben soll (nicht immer möglich, z.B. bei Existenzabhängigkeit), setze FS-Wert zu NULL oder Default
Wie geht man mit NULL-Werten um? (Wdh.)
- Spezielle Semantiken von NULL-Werten
- Dreiwertige Logik verwirrend: T, F, ?
- Setzung: NULL ≠ NULL (z. B. beim Verbund)
- bei Operationen: Ignorieren von NULL-Werten
Wartung von Beziehungen: Referentielle Aktionen
Wartung von Beziehungen: Diskussion der Auswirkungen referentieller Aktionen am Beispiel
FOLIEN 102-109
Wartung von Beziehungen: Maßnahmen zur Verhinderung von Reihenfolgeabhängigkeiten
Statische Schemaanalyse zur Feststellung sicherer DB-Schemata
- nur bei einfach strukturierten Schemata effektiv
- hohe Komplexität der Analysealgorithmen
- bei wertabhängigen Konflikten zu restriktiv (konfliktträchtige Schemata)
Dynamische Überwachung der Modifikationsoperationen
- durchspielen aller Ausführungsreihenfolgen (können weit mehr als 2 sein!)
- hoher Laufzeitaufwand
- häufig unnötig, da Reihenfolgeabhängigkeiten eher selten auftreten
Vorgehensweisen:
- Falls Sicherheit eines Schemas festgestellt werden kann, ist keine Laufzeitüberwachung erforderlich
- Alternative Möglichkeiten zur Behandlung konfliktträchtiger Schemata, nach dem die statische Schemaanalyse die Sicherheit des Schemas nicht feststellen konnte
- sie werden verboten, oder
- sie werden erlaubt und
- die referentiellen Aktionen werden bei jeder Operation dynamisch überwacht
- falls ein Konflikt erkannt wird, wird die Operation zurückgesetzt
Wartung von Beziehungen: Durchführung der referentiellen Aktionen
Wartung von Beziehungen: Zyklische Referenzpfade
Schemaevolution
Wachsender oder sich ändernder Informationsbedarf
- Erzeugen/Löschen von Tabellen (und Sichten)
- Hinzufügen, Ändern und Löschen von Spalten
- Anlegen/Ändern von referentiellen Beziehungen
- Hinzufügen, Modifikation, Wegfall von Integritätsbedingungen
Hoher Grad an logischer Datenunabhängigkeit ist sehr wichtig!
Zusätzliche Änderungen im DB-Schema durch veränderte Anforderungen bei der DB-Nutzung
- Dynamisches Anlegen von Zugriffspfaden
- Aktualisierung der Zugriffskontrollbedingungen
Sichten
Ziel: Festlegung
- welche Daten Benutzer wie sehen wollen (Vereinfachung, leichtere Benutzung)
- welche Daten sie nicht sehen dürfen (Datenschutz)
- einer zusätzlichen Abbildung (erhöhte Datenunabhängigkeit)
Sicht (View)
- mit Namen bezeichnete, aus Tabellen abgeleitete, virtuelle Tabelle (Anfrage)
Korrespondenz zum externen Schema bei ANSI/SPARC (Benutzer sieht jedoch i. allg. mehrere Sichten (Views) und Tabellen)
Sichten: Eigenschaften von Sichten
- Sicht kann wie eine Tabelle behandelt werden
- Sichtsemantik: „dynamisches Fenster“ auf zugrundeliegende Tabellen
- Sichten auf Sichten sind möglich
- eingeschränkte Änderungen: aktualisierbare und nicht-aktualisierbare Sichten
Sichten: Sichtbarkeit von Änderungen
Sichten: Abbildung von Sicht-Operationen auf Tabellen (Query Unfolding)
Sichten werden i. allg. nicht explizit und permanent gespeichert, sondern SichtOperationen werden in äquivalente Operationen auf Tabellen umgesetzt
Vorgehen: Sichtname wird durch seine Definition (d.h. eine Anfrage) ersetzt
Sichten: Löschen von Sichten
Sichten: Änderbarkeit von Sichten
Sichten: WITH CHECK OPTION
- Einfügungen und Änderungen müssen das die Sicht definierende Prädikat (WHERE-Klausel der zugehörigen CREATE-VIEW-Anweisung) erfüllen, sonst Zurückweisung
- nur auf änderbaren Sichten definierbar
Spezifikationsmöglichkeiten
- Weglassen der CHECK-Option
- WITH CASCADED CHECK OPTION oder äquivalent WITH CHECK OPTION
- WITH LOCAL CHECK OPTION
Annahmen
- Sicht SA mit dem die Sicht definierenden Prädikat VA wird aktualisiert
- SI ist die höchste Sicht im Abstammungspfad von SA, die die Option CASCADED besitzt
- Oberhalb von SI tritt keine LOCAL-Bedingung auf
Aktualisierung von SA
- als Prüfbedingung wird von SI aus an SA “vererbt”: V = VI ⋀ VI-1 ⋀ . . . ⋀ V1
- erscheint irgendeine aktualisierte Zeile von SA nicht in SI, so wird die Operation zurückgesetzt
- Es ist möglich, dass Zeilen aufgrund von gültigen Einfüge- oder Änderungsoperationen aus SA verschwinden
Aktualisierte Sicht besitzt WITH CHECK OPTION
- Default ist CASCADED
- Als Prüfbedingung bei Aktualisierungen ergibt sich V = VA ⋀ VN ⋀ . . . ⋀ VI ⋀ . . . ⋀ V1
- Zeilen können jetzt aufgrund von gültigen Einfüge- oder Änderungsoperationen nicht aus SA verschwinden
LOCAL hat eine undurchsichtige Semantik
- wird hier nicht diskutiert
- Empfehlung: nur Verwendung von CASCADED
Indexierung: Einsatz von Indexstrukturen
- Beschleunigung der Suche: Zugriff über Spalten (Schlüsselattribute)
- Kontrolle von Integritätsbedingungen (relationale Invarianten)
- Zeilenzugriff in der logischen Ordnung der Schlüsselwerte
- Gewährleistung der Clustereigenschaft für Tabellen
- Aber auch: erhöhter Aktualisierungsaufwand und Speicherplatzbedarf
Indexierung: Einrichtung von Indexstrukturen
- Phys. Datenunabhängigkeit des Relationenmodells erlaubt ein Hinzufügen und Löschen
- jederzeit möglich, um z. B. bei veränderten Benutzerprofilen das Leistungsverhalten zu optimieren
- “beliebig” viele Indexstrukturen pro Tabelle und mit unterschiedlichen Spaltenkombinationen als Schlüssel möglich
- Steuerung der Eindeutigkeit der Schlüsselwerte und der Clusterbildung
- Freiplatzanteil (PCTFREE) in jeder Indexseite beim Anlegen erleichtert das Wachstum
- Spezifikation: DBA oder Benutzer
Indexierung: Index mit und ohne Clusterbildung
Indexierung: Beispiele
- Erzeugung einer Indexstruktur mit Clusterbildung auf der Spalte Anr von Abt
- Erzeugung einer Indexstruktur auf den Spalten Anr (absteigend) und Gehalt (aufsteigend) von Pers.
Indexierung: Typische Implementierung eines Index: B*-Baum
(wird von meisten DBVS angeboten!)
- dynamische Reorganisation durch Aufteilen (Split) und Mischen von Seiten
- Wesentliche Funktionen
- direkter Schlüsselzugriff auf einen indexierten Satz
- sortiert sequentieller Zugriff auf alle Sätze (unterstützt Bereichsanfragen, Verbundoperation usw.)
- Balancierte Struktur
- unabhängig von Schlüsselmenge
- unabhängig von Einfügereihenfolge
Zusammenfassung: SQL-Anfragen, (Mengenorientierte Datenmanipulantion), Datendefinition
SQL-Anfragen
- Mengenorientierte Spezifikation, verschiedene Typen von Anfragen
- Vielfalt an Suchprädikaten
- Auswahlmächtigkeit von SQL ist höher als die der Relationenalgebra
- Erklärungsmodell für die Anfrageauswertung: Festlegung der Semantik von Anfragen mit Hilfe von Grundoperationen
- Optimierung der Anfrageauswertung durch das DBS
Mengenorientierte Datenmanipulation
Datendefinition
- CHECK-Bedingungen für Wertebereiche, Attribute und Relationen
- Spezifikation des Überprüfungszeitpunktes
Zusammenfassung: Kontrolle von Beziehungen, Wartung der referentiellen Integrität
Kontrolle von Beziehungen
- SQL erlaubt nur die Spezifikation von binären Beziehungen.
- Referentielle Integrität von FS –> PS/SK wird stets gewährleistet.
- Rolle von PRIMARY KEY, UNIQUE, NOT NULL
- Es ist nur eine eingeschränkte Nachbildung von Kardinalitätsrestriktionen möglich; insbesondere kann nicht spezifiziert werden, dass „ein Vater Söhne haben muss“.
Wartung der referentiellen Integrität
- SQL2/3 bietet reichhaltige Optionen für referentielle Aktionen
- Es sind stets sichere Schemata anzustreben
- Falls eine statische Schemaanalyse zu restriktiv für die Zulässigkeit eines Schemas ist, muss für das gewünschte Schema eine Laufzeitüberwachung der referentiellen Aktionen erfolgen.
Zusammenfassung: Schemaevolution, Sichtenkonzept, Indexstrukturen als B*-Bäume
Schemaevolution
- Änderung/Erweiterung von Spalten, Tabellen, Integritätsbedingungen, …
Sichtenkonzept
- Erhöhung der Benutzerfreundlichkeit
- Flexibler Datenschutz
- Erhöhte Datenunabhängigkeit
- Rekursive Anwendbarkeit
- Eingeschränkte Aktualisierungsmöglichkeiten
Indexstrukturen als B*-Bäume
- direkter Schlüsselzugriff auf einen indexierten Satz
- sortiert sequentieller Zugriff auf alle Sätze (unterstützt Bereichsanfragen, Verbundoperation usw.)
