DBS Flashcards
IS
Informationssstem: DBS + Anwendungssysteme + Benutzerschnittstellen
DBS
Datenbanksystem: DB + Datenbankverwaltungssystem (DBVS, DBMS)
DBVS/DBMS
Datenbankverwaltungssystem: generisches Software-System zur Definition, Verwaltung, Verarbeitung und Auswertung der DB-Daten Einsatz für unterschiedlichste Anwendungen
ACID
Atomicity: “Alles oder nichts” - Fehlerisolierungn (UNDO) Consistency: erfolgreiche Transaktion erhält die DB-Konsistenz (Gewährleistung der definierten Integritätsbedingungen) - keine Anomalien und Redundanzen Integrity: alle Aktionen innerhalb einer Transaktion müssen vor parallel ablaufenden Transaktionen verborgen werden („logischer Einbenutzerbetrieb“) Durability: Überleben von Änderungen erfolgreich beendeter Transaktionen trotz beliebiger (erwarteter) Fehler (Persistenz, REDO)
Datenunabhängigkeit Definition + Typen
Maß für die Isolation zwischen Anwendungsprogrammen (AWP) und Daten (sonst: extremer Wartungsaufwand für die AWPs) - physisch: ggü. Art der physischen Datenspeicherung (Geräteeigenschaften, Indexstrukturen …) = Minimalziel - logisch: ggü. logischer Strukturierung der Daten (z.B. durch Sichten) – nur teilweise erreichbar
DBA + Aufgaben
Datenbankadministrator - Entwurf des externen/konzeptionellen/internen Schemas - Zugriffskontrolle auf externe Schemata - Definition und Implementierung von Integritätsbedingungen - Entwurf und Umsetzung einer Strategie für Datensicherung und Recovery
Data Warehouse
DB, in der Datenbestände aus unterschiedlichen Quellen für Analysen konsolidiert und integriert werden -> OLAP
OLAP
Online Analytical Processing - umfassende Auswertung/Analyse großer Datenbestände - meist über Data Warehouse - zur Unterstützung von Geschäftsentscheidungen (Vertriebskontrolle, Preisoptimierung, …)
OLTP
Online Transaction Processing - dominierende Einsatzform von DBS - vorgeplante Anwendungsprogramme (Produktbestellungen, Überweisungen, …) greifen auf DBs zu - TAs: kurz + häufig + wenige Daten
3-Ebenen-Architektur
- nach ANSI
- intern: physische Struktur der DB
-
konzeptuell:
- logische Sicht auf Struktur der Daten
- abstrahiert von interner Struktur -> physische Datenunabhängigkeit
-
extern: definiert Nutzersichten auf Struktur der Daten (für Nutzer/AWPs)
- Zugriffsschutz, Reduzierung der Komplexität
- abstrahiert vom konzeptuellen Schema -> teilweise logische Datenunabhängigkeit
ER
Entity-Menge
Definition
Zusammenfassung gleichartiger Entities (Entitäten mit den gleichen Attributen) - homogene Menge
Definition:
- eindeutiger Name
- Festlegung aller Attribute
- Angabe des Primätschlüssels
ER
schwache Entity-Menge
- Existenzabhängigkeit von anderer Entity-Menge
- kein eigener Schlüsselkanditat - Primärschlüssel teilweise von Vater-Entity
- 1:1 oder 1:n-Beziehung zu Vater-Entity-Menge
ER
Schlüsselkandidat
- Attribut oder Menge von Attributen einer Entity-Menge
- eindeutig für jede Entity der Menge
- kein Null-Wert
- minimal (Entfernen eines Attributes im Schlüsselkandidaten führt nicht zu einem neuen Schlüsselkanditaten)
ER
Primärschlüssel
- ausgewählter Schlüsselkandidat einer Entity-Menge
- ggf. mit lfd. Nummer erzeugt
ER
Relationship-Menge
- Zusammenfassung gleichartiger Beziehungen zwischen Entities, die jeweils gleichen Entity-Mengen angehören
- math. Relation zwischen n Entitymengen E1xE2x…xEn
- üblicherweise binär oder tertiär (n ε {2,3})
- kann selber Attribute haben
- keine Disjunktheit der beteiligten Entity-Mengen gefordert (rekursive Beziehungen) - mit Rollennamen möglich
ER
Diagrammsymbole
Modellierung
3 Abstraktionskonzepte
Klassifikation: fasst Objekte (Entities, Instanzen) mit gemeinsamen Eigenschaften zu einem neuen (Mengen-) Objekt (Entity-Menge, Klasse, Objekttyp) zusammen - Instanzen einer Klasse unterliegen gleicher Struktur (Attribute), gleichen Integritätsbedingungen, gleichen Operationen -> ER, UML
Aggregation: Zusammenfassung potentiell unterschiedlicher Teilobjekte (Komponenten) zu neuem Objekt – mathematisch: Bildung von kartesischen Produkten -> ER, UML
Generalisierung / Spezialisierung: Teilmengenbeziehungen zwischen Elementen verschiedener Klassen -> UML
- mathematisch: Bildung von Potenzmengen (bzw. Teilmengen)
- wesentlich: Vererbung von Eigenschaften an Teilmengen
UML
Assoziationen
- Repräsentation von Beziehungen
- optional: Assoziationsnamen + Leserichtung + Rollennamen + Sichtbarkeit von Rollen (+, -, #) + Kardinalitätsrestriktionen
- gerichtete Assoziationen: mit Pfeil
- Assoziationsklassen mit Strichellinie an Assoziationslinie
UML
zulässige Multiplizitätsfestlegungen
x .. y
0 .. *
1 .. *
0 .. 1
1
*
Relationsschema
Definition
R(A1,…,An)
- R = Name
- A1, …, An = Liste von paarweise entschiedenen Attributnamen bzw. ensprechenden Attributen
Attribut
Definition
- Name: A
- Wertebereich: W(A)
- Kontext: zugehöriges Relationsschema
- Semantik: oft implizit durch Name
Relation
Definition
- r(R) ist eine Relation r zu einem Relationsschema R
- Teilmenge des kartesischen Produkts der Attributwertebereiche
- Menge von Tupeln (Zeilen)
- RM -> nur atomare Werte (keine zusammengesetzten oder mehrwertigen Attribute)
Grad
Kardinalität
Grad = Anzahl der Attribute
Kardinalität = Anzahl der Tupel/Sätze/Zeilen
Äquivalenz von Relationen R und S
- alle Attribute stimmen überein
- Anordnung der Attribute von S ist durch Permutation der Attribute von R entstanden
Relationenmodell
Darstellung Primärschlüssel/Fremdschlüssel
Primärschlüssel o. (APK):PK
Fremdschlüssel: gepunktet unterstrichen + mit Pfeil o. (AFK):FK auf S
7 Aufgaben/Eigenschaften von DBS
- Zentrale Kontrolle über die operationalen Daten
- Hohe Datenunabhängigkeit
- Hohe Leistung und Skalierbarkeit
- Mächtige Datenmodelle und Abfragesprachen
- Transaktionskonzept (ACID)
- Automatisierte Zugriffskontrolle/Datenschutz
- Ständige Verfügbarkeit
UML
Spezialisierung/Generalisierung
- Def. + Eigenschaften
- Is-A-Beziehungen
- Vererbung von Eigenschaften der Oberklasse
- Subklasse: Klasse S, deren Entities eine Teilmenge einer Superklasse G sind, d. h. jedes Element (Ausprägung) von S ist auch Element von G.
- Z heisst vollständig (complete), falls gilt: G = Vereinigung Si (i = 1..n) andernfalls partiell (incomplete).
Z ist disjunkt (disjoint), falls Si Schnitt Sj = { } für i ungleich j andernfalls überlappend (overlapping).
UML
Aggregation
Referenzsemantik - Wertesemantik
- Zusammensetzung: part-of-Beziehung
- Elemente einer Subkomponente sind auch Elemente aller Superkomponenten dieser Subkomponente
- keine Vererbung
- Referenzsemantik: gleichzeitig Element verschiedener Komponenten -> Netzwerke, (n:m) !
- Wertesemantik (Komposition): genau zu einem Aggregat-Objekt; Existenzabhängigkeit!
Relationenmodell
Relationale Invarianten
- inhärente Integritätsbedingungen des Relationenmodells
1. Primärschlüsselbedingung (Entity-Integrität)
- Eindeutigkeit des Primärschlüssels / Minimalität
- keine Nullwerte!
- Fremdschlüsselbedingung (referentielle Integrität):
- zugehötiger Primärschlüssel muss existieren
- d.h. zu jedem Wert (ungleich Null) eines _Fremdschlüsselattribut_s einer Relation R2 muss gleicher Wert des _Primärschlüsse_ls in irgendeinem Tupel von Relation R1 vorhanden sein
- Nullwerte möglcih, wenn nicht Teil eines Primärschlüssels
- “zusammengesetzt“, wenn zugehöriger Primärschlüssel „zusammengesetzt“ ist
- Zyklen möglich (geschlossener referentieller Pfad)
- selbstreferenzierende Tabelle möglich
SQL
Wartung der referentiellen Integrität
- NOT NULL
- Löschregel für Zielrelation: ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT }
- Änderungsregel fÜr Ziel-Primärschlüssel: ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
NO ACTION (Voreinstellung): Operation nur zugelassen, wenn keine Abhängigkeiten bestehen
Abbildung ERM / UML -> RM
Regeln
- jede Entity-Menge muss als eigenständige Relation mit einem eindeutigen Primärschlüssel definiert werden
- Relationship-Mengen können als eigene Relationen definiert werden, wobei die Primärschlüssel der zugehörigen Entity-Mengen als Fremdschlüssel zu verwenden sind
- n:m-Beziehungen müssen durch eigene Relation dargestellt werden
- RM unterstützt nicht Aggregation/Generalisierung
Generalisierung im RM
4 Arten
- vertikale Partitionierung (ID dupliziert, sonst nur nicht vererbte Attribute) - jede Instanz in Subklasse gespeichert
- horizontale Partitionierung (jede Instanz ist genau einmal und vollständig in ihrer „Hausklasse“ mit allen (ererbten) Attributen gespeichert – keinerlei Redundanz)
- volle Redundanz (eine Instanz wird wiederholt in jeder Klasse, zu der sie gehoört, mit eigenen + ererbten Attributen gespeichert)
- Wide Table (eine einzige Tabelle mit allen Attributen, mit NOT NULL auffüllen)
Relationenalgebra
Definition
System, das aus einer nichtleeren Menge und einer Familie von Operationen besteht
- Relationen sind Mengen
- Operationen auf Relationen arbeiten auf einer oder mehreren Relationen als Eingabe und
erzeugen eine Relation als Ausgabe (Abgeschlossenheitseigenschaft) => mengenorientierte Operationen
Relationenalgebra
Klassische Mengenoperationen u. Relationenoperationen
Klassische Mengenoperationen
- Vereinigung
- Differenz
- kartesisches Produkt
- Durchschnitt (ableitbar)
Relationenoperationen
- Selektion
- Projektion
- Verbund (Join) (ableitbar)
- Division (ableitbar)
Relationenalgebra
Selektion
- Def.
- Schreibweise
- Grad/Kardinalität
Auswahl von Zeilen einer Relation über Prädikate
Relationenalgebra
Projektion
- Def.
- Schreibweise
- Grad/Kardinalität
Auswahl der Spalten (Attribute) A1, A2, . . . , Ak aus einer Relation R
Relationenalgebra
Theta-Join, Equijoin
Natural Join
- kartesisches Produkt zwischen zwei Relationen
- eingeschränkt durch θ -Bedingungen zwischen Attribut A von R und Attribut B von S.
- Equijoin: θ ist ‘=’
- Natural Join: Gleichverbund über alle gleichnamigen Attribute und Projektion über die verschiedenen Attribute
Relationenalgebra
Semi-Join
Ergebnisbeschränkung des Gleichverbundes auf eine der beiden Eingaberelationen
Relationenalgebra
(Left/Right) Outer Join
- Ziel: Verlustfreier Verbund soll erzwungen werden
- verlustfrei = alle Tupel von R und S nehmen am Verbund teil
- inverse Operation Projektion erzeugt dann wieder R und S (lossless join)
- durch Einfügen künstlischer Verbundpartner
Relationenalgebra
Division
- Simulation des Allquantors => ein Tupel aus R steht mit allen Tupeln aus S in einer bestimmten Beziehung
- Zusammenhang mit kartesischen Produkt: (RxS) / S = R
Relationenalgebra
Überblick Operationen
SQL
4 Elemente einer vollständigen DB-Sprache
Grundbaustein
- Datenmanipulation: Einfügen, Löschen und Ändern von Tupeln/Relationen
- Datendefinition: Wertebereiche, Attribute, Relationen, Sichten auf Relationen
- Datenkontrolle: Bedingungen zur Zugriffskontrolle, Zusicherungen (assertions) zur semantischen Integritätskontrolle
- Kopplung mit Wirtssprache
- Grundbaustein: Abbildung (SELECT)
SQL
DML-Befehle
SELECT
INSERT
UPDATE
DELETE
Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN
SQL
DDL-Befehle
CREATE SCHEMA
CREATE DOMAIN
CREATE TABLE
CREATE VIEW
ALTER TABLE
DROP SCHEMA
DROP DOMAIN
DROP TABLE
DROP VIEW
SQL
Datenkontrolle
Constraints-Definitionen bei CREATE TABLE CREATE ASSERTION
DROP ASSERTION
GRANT
REVOKE
COMMIT
ROLLBACK
SQL
SELECT Statement
- SELECT DISTINCT erzwingt Duplikateliminierung
- ORDER BY: asc/desc
- Umbenennung: buch AS B -> B.titel, …
SQL
String-Funktionen
- || (String-Konkatenation), CHAR_LENGTH, BIT_LENGTH
- SUBSTRING Bsp.: SUBSTRING (NAME FROM 1 FOR 20)
- POSITION, LOWER, UPPER
- TRIM Bsp.: TRIM (TRAILING ’ ’ FROM NAME)
SQL
Zeit/Datumsfunktionen
- CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
- EXTRACT (Herausziehen von YEAR, MONTH, … aus Datum)
- CAST (Typkonversionen) Bsp.: CAST (’2009-04-24’ AS DATE) …
SQL
Join-Anfragen
4 Varianten: Natural Join/Normaler Join
- SELECT * FROM buch NATURAL JOIN verlag
- SELECT * FROM buch JOIN verlag USING (verlagsid)
- SELECT * FROM buch B JOIN verlag V ON B.verlagsid = V.verlagsid
- SELECT * FROM buch B, verlag V WHERE B.verlagsid = V.verlagsid
SQL
- Outer Joins
- Kartesisches Produkt
-
Outer Joins: LEFT JOIN, RIGHT JOIN, FULL JOIN
- schlagwort LEFT OUTER JOIN buch_sw USING (swid)
-
Kartesisches Produkt:
A CROSS JOIN B <=> SELECT * FROM A, B
SQL
3 Arten von Sub-Queries
- Table Sub-Queries (mengenwertige Ergebnisse): auch in FROM möglich
- Row Sub-Queries (Tupel-Ergebnis)
- skalare Sub-Queries (atomarer Wert; Kardinalität 1, Grad 1): auch in SELECT möglich
SQL
Partitionierung in Gruppen
- Ausgabe, Aggregatfunktionen, Sonderbedingung
SELECT … FROM … [WHERE …]
[GROUP BY column-ref-commalist]
- ausgegeben werden können nur: Gruppierungsattribute, Konstante, Ergebnis von Aggregatfunktionen (-> 1 Satz pro Gruppe)
- Aggregatfunktion wird jeweils auf die Tupeln einer Gruppe angewendet
- HAVING: Bedingungen nur bezüglich Gruppierungsattribut(en) – meist Aggregatfunktionen
SQL
Suchprädikate
nicht quantifizierend, quantifizierend, LIKE
nicht quantifizierend
- Vergleichsprädikate (ungleich <>)
- LIKE, BETWEEN- (<=, >=) , IN (Menge(“…”,”…“,…) od. Subquery)
- Test auf Nullwert
quantifizierend: ALL, ANY/SOME, EXISTS: false bei leerer Tabelle
LIKE: Aufbau einer Maske mit Hilfe zweier spezieller Symbole
- % bedeutet „null oder mehr beliebige Zeichen“
- _ bedeutet „genau ein beliebiges Zeichen“
SQL
Problemfälle Null-Werte
- 3-wertige Logik führt zu unerwarteten Ergebnissen
- PERS (Alter <= 50) vereinigt mit PERS (Alter > 50) ergibt nicht notwendigerweise Gesamtrelation PERS
- Nullwerte werden bei SUM, AVG, MIN, MAX nicht berücksichtigt, während COUNT(*) alle Tupel (inkl. Null-Tupel, Duplikate) zählt.
- Test auf Null-Werte: IS [NOT] NULL
SQL
Mengenoperatoren
{UNION | EXCEPT | INTERSECT }
[ALL][CORRESPONDING [BY (column-commalist)]] table-exp
- vor Ausführung werden Duplikate entfernt (außer für ALL)
- Vereinigungsverträglichkeit und übereinstimmende Attributnamen gefordert (ggf. vorher umbenennen)
-
Abschwächung:
- CORRESPONDING BY (A1, A2, …An): Operation ist auf Attribute Ai beschränkt, die in beiden Relationen vorkommen müssen (-> n-stelliges Ergebnis)
- CORRESPONDING: Operation ist auf gemeinsame Attribute beschränkt
SQL
INSERT
INSERT INTO table [(column-commalist)] { VALUES row-constr-commalist | table-exp | DEFAULT VALUES }
- alle nicht angesprochenen Attribute erhalten Nullwerte
- falls alle Werte in der richtigen Reihenfolge versorgt werden, kann Attributliste entfallen (NICHT zu empfehlen)
- Integritätsbedingungen müssen erfüllt werden
SQL
UPDATE
DELETE
UPDATE table
SET update-assignment-commalist
[WHERE cond-exp]
update-assignment ::= column = {scalar-exp | DEFAULT | NULL }
DELETE FROM table [WHERE cond-exp]
Relationenalgebra -> SQL
- SELECT A1, A2,…,Ak FROM R
- SELECT * FROM R WHERE p
- SELECT * FROM R,S (CROSS JOIN)
- SELECT * FROM R,S WHERE R.A θ S.B
- R NATURAL JOIN S
- R LEFT JOIN S
Relationenalgebra -> SQL
- R FULL JOIN S
- SELECT R.* FROM R NATURAL JOIN S
- R UNION S
- R INTERSECT S
- R EXCEPT S
- für R: LNR, TNR, S: TNR: SELECT DISTINCT R1.LNR FROM R R1 WHERE NOT EXISTS (SELECT * FROM S WHERE TNR NOT IN (SELECT R2.TNR FROM R R2 WHERE R2.LNR = R1.LNR))
Normalisierung
Funktionale Abhängigkeit
Definition, triviale FA
- beschreiben semantische Integritätsbedingungen bezüglich der Attribute eines Relationenschemas, die jederzeit erfüllt sein müssen
-
trivial: X->Y und Y ist Teilmenge von X
- X -> X
- reflexiv, Komplementierung (X->Y auch ZX->ZY), transitiv
Normalisierung
voll funktional vs. partiell abhängig
Normalisierung
Schlüsselkandidat bei FA
- X ist Schlüsselkandidat von Relationenschema R, wenn
- für alle Y aus R: X -> Y
- keine echte Teilmenge von X bestimmt funktional alle anderen Attribute Y aus R (Minimalität)
- Kenntnis aller FA ermöglicht Bestimmung der Schlüsselkandidaten
Normalisierung
Merkmale eines schlechten Datenbank-Entwurfs
- implizite Darstellung von Informationen
- Redundanzen
- potenzielle Inkonsistenz (Änderungsanomalien)
- Einfügeanomalien
- Löschanomalien
Normalisierung
2 Korrektheitskriterien für die Zerlegung von Relationenschemata
Verlustlosigkeit: ursprünglichen enthaltenen Informationen müssen aus den Ausprägungen der neuen Relationenschemata R1, .., Rn rekonstruierbar sein
Abhängigkeitserhaltung: Die für R geltenden funktionalen Abhängigkeiten müssen auf die Schemata R1, …, Rn übertragbar sein.
Normalisierung
Stufen
- NF2: Non-First Normal-Form (unnormalisierte Relation)
- 1NF: normalisierte Relationen (einfache Attribute)
- 2NF: keine partiellen (funktionalen) Abhängigkeiten
- 3NF: keine transitiven Abhängigkeiten (jedes Nicht-Primärattribut ist direkt von jedem SK abhängig)
- BCNF: jeder Determinant ist Schlüsselkandidat
Normalisierung
Überführung in 1NF
Ziel: nur einfache Attribute
- Starte mit der übergeordneten Relation (Vaterrelation).
- Nimm ihren Primärschlüssel und erweitere jede unmittelbar untergeordnete Relation damit zu einer selbständigen Relation.
- Streiche alle nicht-einfachen Attribute (untergeordnete Relationen) aus der Vaterrelation.
- Wiederhole ggf.rekursiv.
Regeln:
- nicht-einfache Attribute bilden neue Relationen.
- Primärschlüssel der übergeordneten wird an untergeordnete Relation angehängt (‘copy down the key’)
Normalisierung
Überführung in 2NF
Ziel: Eliminierung partiell abhängiger Attribute
2NF, wenn es in 1NF ist und jedes Nicht-Primärattribut von R voll funktional von jedem Schlüsselkandidaten in R abhängt.
Überführung
- Bestimme funktionale Abhängigkeiten zwischen Nicht-Primärattributen und Schlüsselkandidaten
- Eliminiere partiell abhängige Attribute und fasse sie in eigener Relation zusammen (unter Hinzunahme der zugehörigen Primärattribute)
Normalisierung
Def. Primärattribut
Def. Determinant
Primärattribut (Schlüsselattribut):
Attribut, das zu mind. einem Schlüsselkandidaten eines Schemas gehört.
Determinant:
Ein Attribut (oder eine Gruppe von Attributen), von dem andere voll funktional abhängen.
Normalisierung
Überführung in 3NF
Ziel: Eliminierung der transitiven Abhängigkeiten
3NF, wenn es sich in 2NF befindet und jedes Nicht-Primärattribut von R von keinem Schlüsselkandidaten von R transitiv abhängig ist
Normalisierung
Def. transitiv abhängig
Normalisierung
BCNF
Boyce/Codd-Normalform
Ziel: Beseitigung der Anomalien für Primärattribute
BCNF, wenn es in 1NF ist und jeder Determinant ein Schlüsselkandidat von R ist.
Wenn Attribute Y (voll funktional) abhängen von einer disjunkten Attributen X, dann hängen andere Attribute Z auch von X (voll funktional) ab.
D.h. für alle X,Y,Z mit X und Y disjunkt gilt: X -> Yimpliziert X -> Z
Aufbau SQL-Umgebung
SQL-Umgebung (Environment) besteht aus
– Katalogen: pro Datenbank ein Schema
– Benutzern
– INFORMATION_SCHEMA (Metadaten über alle Schemata)
=> dreiteilige Objektnamen: ..
DDL SQL
Schema-Definition
CREATE SCHEMA [schema] AUTHORIZATION user [DEFAULT CHARACTER SET char-set] [schema-element-list]
- jedes Schema einem Benutzer zugeordnet (z.B. DBA)
- Definition aller
- Definitionsbereiche
- Basisrelationen
- Sichten(Views)
- Zugriffsrechte
- Integritätsbedingungen
DDL SQL
SQL92 Datums-/Zeitangaben (Datetimes)
DATE, TIME, TIMESTAMP
TIME WITH TIME ZONE
TIMESTAMP WITH TIME ZONE
INTERVAL (* Datums- und Zeitintervalle *)
DDL SQL
Datentypen nach SQL92
- Boolean (SQL:1999)
- Large Objects (für Texte, Fotos, etc. in der Datenbank)
- BLOB (Binary Large Object)
- CLOB (Character Large Object): Texte mit 1-Byte Character-Daten
- NCLOB (National Character Large Objects): 2-Byte Character-Daten für nationale Sonderzeichen (z. B. Unicode)
- komplexere Typen (-> Vorlesung DBS2)
- ROW: zusammengesetzte Attribute
- ARRAY
- MULTISET: mengenwertige Attribute (seit SQL:2003)
- user-defined types
DDL SQL
Definitionsbereiche
CREATE DOMAIN domain [AS] data-type
[DEFAULT { literal | niladic-function-ref | NULL}] [[CONSTRAINT constraint] CHECK (cond-exp) [deferrability]]
- Festlegung zulässiger Werte durch Domain-Konzept
- Wertebereichseingrenzung durch benamte CHECK-Constraint
- in SQL-92 nur bzgl. Standard-Datentypen (nicht über andere Domains)
DDL SQL
Relationen erzeugen
CREATE [[GLOBAL | LOCAL] TEMPORARY] TABLE base-table (base-table-element-commalist)
[ON COMMIT {DELETE | PRESERVE} ROWS]
base-table-element ::= column-def | base-table-constraint-def
- permanente und temporäre Relationen
- zwei Typen von temporären Relationen:
- LOCAL: Lebensdauer auf erzeugende TA begrenzt
- GLOBAL: Lebensdauer = „Session“ eines Benutzers; Inhalt kann beim Commit zurückgesetzt werden
DDL SQL
Large Objects
CREATE TABLE Pers (PNR INTEGER, Name VARCHAR (40), Vollzeit BOOLEAN, Lebenslauf CLOB (75K), Unterschrift BLOB (1M), Bild BLOB (12M))
- unterstützte Operationen
- Suchen+Ersetzen von Werten (bzw. partiellen Werten)
- LIKE-Prädikate, CONTAINS, POSITION, SIMILAR TO „SQL(1999|2003)“
- Konkatenation ||, SUBSTRING, LENGTH, IS [NOT] NULL…
- nicht möglich
- Schlüsselbedingung
- Kleiner/Größer-Vergleiche
- Sortierung (ORDER BY , GROUP BY)
DDL SQL
Sichtkonzept
- Sicht (View): mit Namen bezeichnete, aus Basisrelationen abgeleitete, virtuelle Relation (Anfrage)
- Korrespondenz zum externen Schema bei ANSI
- kann wie Relation behandelt werden: Anfragen/AWP/Sichten auf Sichten möglich
- als Anfrageergebnis interpretiert, das dynamisch beim Zugriff generiert wird
- Sicht-Operationen müssen durch (interne) Query-Umformulierung auf Basisrelationen abgebildet werden
- CREATE VIEW view [(column-commalist )] AS table-exp [WITH [CASCADED | LOCAL] CHECK OPTION]
- DROP VIEW view [RESTRICT | CASCADE]
Vorteile Sichtkonzept
- Erhöhung der Benutzerfreundlichkeit
- erhöhte Datenunabhängigkeit / verbesserte Schema-Evolution
- Datenschutz / Zugriffskontrolle
Probleme für Änderungsoperationen auf Sichten
aktualisierbare vs. nicht aktualisierbare Sichten
- erfordern, dass zu jedem Tupel der Sicht zugrundeliegende Tupel der Basisrelationen eindeutig identifizierbar sind
- Sichten auf einer Basisrelation sind nur aktualisierbar, wenn der Primärschlüssel in der Sicht enthalten ist.
- Sichten, die über Aggregatfunktionen oder Gruppenbildung definiert sind, sind nicht aktualisierbar
- Sichten über mehr als eine Relation sind im allgemeinen nicht aktualisierbar
Materialisierte Sichten
Sonderform von Sichten mit physischer Speicherung des Anfrageergebnisses (redundante Datenspeicherung)
- keine Query-Umformulierung + Ausführung auf Basisrelationen
- sehr schneller Lesezugriff
- Nutzung auch als Daten-Snapshot /Kopie
- Notwendigkeit der Aktualisierung/Refresh (automatisch durch das DBS)
- erhöhter Speicherbedarf
- nicht standardisiert, jedoch in vielen DBS verfügbar (Oracle, DB2, PostgresSQL…)
CREATE MATERIALIZED VIEW Monatsumsatz_mv REFRESH COMPLETE ON DEMAND AS SELECT Monat, SUM(Betrag) FROM Umsatz GROUP BY Monat;
- Refresh-Optionen: complete, fast (inkrementell) …
- Refresh-Zeitpunkte: on demand, on commit, never …
DDL SQL
Dynamische Änderung einer Relation
ALTER TABLE base-table
{ ADD [COLUMN] column-def
| ALTER [COLUMN] column {SET default-def | DROP DEFAULT}
| DROP [COLUMN] column {RESTRICT | CASCADE}
| ADD base-table-constraint-def
| DROP CONSTRAINT constraint {RESTRICT | CASCADE}}
-
Schema-Evolution: dynamische Schemaanpassungen während der Lebenszeit (Nutzung) der Relationen
- Hinzufügen, Ändern + Löschen von Attributen / Check-Constraints
DDL SQL
Löschen von Objekten
DROP { TABLE base-table | VIEW view | DOMAIN domain | SCHEMA schema } [RESTRICT | CASCADE]
- RESTRICT: Rückweisung von Drop, wenn Attribut in Sicht / Check referenziert
- CASCADE: Folgelöschung aller Sichten/Check-Klauseln, die von dem Attribut abhängen
Datenkontrolle
Zugriffskontrolle
-
Sichtkonzept: wertabhängiger Zugriffsschutz
- Untermengenbildung
- Verknüpfung von Relationen
- Verwendung von Aggregatfunktionen
-
GRANT-Operation: Vergabe von Rechten auf Relationen bzw. Sichten an Liste von Benutzern o. PUBLIC
- Zugriffsrechte: SELECT, INSERT, UPDATE, DELETE, REFERENCES, USAGE
- Erzeugung einer „abhängigen“ Relation erfordert REFERENCES-Recht auf von Fremdschlüsseln referenzierten Relationen
- USAGE: Nutzung spezieller Wertebereiche (character sets)
- Attributeinschränkung bei INSERT, UPDATE und REFERENCES möglich
- dynamische Weitergabe von Zugriffsrechten: WITH GRANT OPTION (dezentrale Autorisierung)
- bei REVOKE Probleme durch Rechteempfang aus verschiedenen Quellen + Zeitabhängigkeiten -> Autorisierungsgraph
- *GRANT** {privileges-commalist | ALL PRIVILEGES}
- *ON** accessible-object TO grantee-commalist [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] privileges-commalist ON accessible-object FROM grantee-commalist {RESTRICT | CASCADE }
ACID und Datenkontrolle
Integritätskontrolle
- semantische Integritätskontrolle: möglichst hohe Übereinstimmung von DB-Inhalt und Miniwelt (Datenqualität)
- bei COMMIT müssen alle semantischen Integritätsbedingungen erfüllt sein (Transaktionskonsistenz)
- Einhaltung von physischer Integrität und Ablaufintegrität
Datenkontrolle
semantische Integritätskontrolle
zentral vs. dezentral
DBS-basierte Integritätskontrolle:
- größere Sicherheit
- vereinfachte Anwendungserstellung
- Unterstützung von interaktiven sowie programmierten DB-Änderungen
- leichtere Änderbarkeit von Integritätsbedingungen
Datenkontrolle
Klassifikation von Integritätsbedingungen
Datenkontrolle
dynamische Integritätsbedingungen
2 Varianten
Bezug im Gegensatz zu statischen IB auf Änderungen selbst und damit auf mehrere Datenbankzustände
- Übergangsbedingungen: Änderung von altem zu neuem DB-Zustand wird eingeschränkt (z.B. FAM-STAND von ‘ledig’ nach ‘geschieden’, Gehalt darf nicht kleiner werden)
- temporale Bedingungen: Änderungen in bestimmtem zeitlichen Fenster werden eingeschränkt (z.B. Gehalt darf innerhalb von 3 Jahren nicht um mehr als 25% wachsen)
Datenkontrolle
Integritätsbedingungen in SQL
- Eindeutigkeit von Attributwerten: UNIQUE bzw. PRIMARY KEY bei CREATE TABLE, Satztypbedingungen
- Fremdschlüsselbedingungen: FOREIGN-KEY-Klausel / Satztyp- bzw. satztypübergreifende Bedingung
- Wertebereichsbeschränkungen von Attributen: CREATE DOMAIN / NOT NULL / DEFAULT / Attribut- und Satztyp-Bedingungen
- Allgemeine Integritätsbedingungen: CHECK-Constraints bei CREATE TABLE / allgemeine Assertions, z. B. für satztypübergreifende Bedingungen
-
Festlegung des Überprüfungszeitpunktes:
- IMMEDIATE: am Ende der Änderungsoperation (Default)
- DEFERRED: am Transaktionsende (COMMIT)
- dynamische Integritätsbedingungen durch Trigger
Datenkontrolle
Integritätsregeln / Trigger
- Standardreaktion auf verletzte Integritätsbedingung: ROLLBACK
- Integritätsregeln erlauben Spezifikation von Folgeaktionen, z. B. um Einhaltung von Integritätsbedingungen zu erreichen
- SQL92: deklarative Festlegung referentieller Folgeaktionen (CASCADE, SET NULL, …)
- SQL99: Trigger -> Festlegung von Folgeaktionen für Änderungsoperationen (INSERT/UPDATE/DELETE)
- Trigger wesentlicher Mechanismus von aktiven DBS
- Verallgemeinerung durch sogenannte ECA-Regeln (Event / Condition / Action)
Datenkontrolle
Trigger Definition
CREATE TRIGGER
{BEFORE|AFTER} {INSERT|DELETE|UPDATE [OF]}
ON
[ORDER]
[REFERENCING]
[FOR EACH {ROW | STATEMENT}] [WHEN ()]
::= OLD [AS]| NEW [AS]| OLD_TABLE [AS]| NEW_TABLE [AS]
Datenkontrolle
Probleme von Triggern
- teilweise prozedurale Semantik (Zeitpunkte, Verwendung alter/neuer Werte, Aktionsteil im Detail festzulegen)
- beschränkt auf Änderungsoperationen einer Tabelle (UPDATE, INSERT, DELETE)
- i. a. keine verzögerte Auswertung von Triggern
- Gefahr zyklischer, nicht-terminierender Aktivierungen
- Korrektheit des DB-/Trigger-Entwurfes (Regelabhängigkeiten, parallele Regelausführung, …)
Datenkontrolle
Anwendung von Triggern
- Überwachung nahezu aller Integritätsbedingungen, inkl. dynamischer Integritäsbedingungen
- Validierung von Eingabedaten
- automatische Erzeugung von Werten für neu eingefügten Satz
- Wartung replizierter Datenbestände
- Protokollieren von Änderungsbefehlen (Audit Trail)
Datenkontrolle
4 Konsistenzebenen (Isolation Level) bei Synchronisation
- Default ist Serialisierbarkeit (serializable)
- Lost-Update muss generell vermieden werden
- READ UNCOMMITTED für ÄnderungsTAs unzulässig
SET TRANSACTION [READ WRITE | READ ONLY] ISOLATION LEVEL
Datenkontrolle
Fehlerarten + Maßnahmen
- Transaktionsfehler: vollständiges Zurücksetzen auf Transaktionsbeginn (Undo)
- Systemfehler (Rechnerausfall, DBVS-Absturz): REDO für erfolgreiche Transaktionen / UNDO aller durch Ausfall unterbrochenen Transaktionen
- Gerätefehler (Plattenausfall):: REDO aller Änderungen auf einer Archivkopie o. Spiegelplatten bzw. RAID-Disk-Arrays
- Katastrophen (Komplettausfall Rechenzentrum durch Naturkatastrophe): verteilte Datensicherung auf geographisch separierten Systemen
Datenkontrolle
Systemkomponenten zur Recovery
- Pufferung von Log-Daten im Hauptspeicher (Log-Puffer) – Ausschreiben spätestens am Transaktionsende (“Commit”)
- Log-Datei zur Behandlung von Transaktions- und Systemfehler: DB + Log => DB
- Behandlung von Gerätefehlern: Archivkopie + Archiv-Log => DB
Datenkontrolle
TA als Schnittstelle zwischen Anwendungsprogramm und DBS
typische Probleme bei Informationsverarbeitung ohne DBVS
- Redundanz und Inkonsistenz
- beschränkte Zugriffsmöglichkeiten
- hohe Entwicklungskosten für Anwendungsprogramme
- enge Bindung von Dateistrukturen an Programmstrukturen (geringe „Datenunabhängigkeit“)
- Probleme beim Mehrbenutzerbetrieb
- Verlust von Daten
- Integritätsverletzung
- Sicherheitsprobleme
Transaktionskonzept
- Programmierschnittstellen
- Ausgänge
- begin of transaction (BOT)
- commit transaction („commit work“ in SQL)
- rollback transaction („rollback work“ in SQL)
Kartesisches Produkt
alles von R mit allen von S in Verbindung setzen
Vorteile
Zentrale Kontrolle der Daten
- alle (operationalen) Daten können gemeinsam benutzt werden
- keine verstreuten privaten Dateien
- ermöglicht inhaltliche Querauswertungen
- Eliminierung der Redundanz
- Vermeidung von Inkonsistenzen
- keine unterschiedlichen Änderungsstände
- einfache Erweiterung/Anpassung der DB (Änderung des Informationsbedarfs)
- Verwaltung durch Datenbankadministrator (DBA)
