DBS Flashcards
1
Q
IS
A
Informationssstem: DBS + Anwendungssysteme + Benutzerschnittstellen
2
Q
DBS
A
Datenbanksystem: DB + Datenbankverwaltungssystem (DBVS, DBMS)
3
Q
DBVS/DBMS
A
Datenbankverwaltungssystem: generisches Software-System zur Definition, Verwaltung, Verarbeitung und Auswertung der DB-Daten Einsatz für unterschiedlichste Anwendungen
4
Q
ACID
A
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)
5
Q
Datenunabhängigkeit Definition + Typen
A
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
6
Q
DBA + Aufgaben
A
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
7
Q
Data Warehouse
A
DB, in der Datenbestände aus unterschiedlichen Quellen für Analysen konsolidiert und integriert werden -> OLAP
8
Q
OLAP
A
Online Analytical Processing - umfassende Auswertung/Analyse großer Datenbestände - meist über Data Warehouse - zur Unterstützung von Geschäftsentscheidungen (Vertriebskontrolle, Preisoptimierung, …)
9
Q
OLTP
A
Online Transaction Processing - dominierende Einsatzform von DBS - vorgeplante Anwendungsprogramme (Produktbestellungen, Überweisungen, …) greifen auf DBs zu - TAs: kurz + häufig + wenige Daten
10
Q
3-Ebenen-Architektur
A
- 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
11
Q
ER
Entity-Menge
Definition
A
Zusammenfassung gleichartiger Entities (Entitäten mit den gleichen Attributen) - homogene Menge
Definition:
- eindeutiger Name
- Festlegung aller Attribute
- Angabe des Primätschlüssels
12
Q
ER
schwache Entity-Menge
A
- 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
13
Q
ER
Schlüsselkandidat
A
- 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)
14
Q
ER
Primärschlüssel
A
- ausgewählter Schlüsselkandidat einer Entity-Menge
- ggf. mit lfd. Nummer erzeugt
15
Q
ER
Relationship-Menge
A
- 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
16
Q
ER
Diagrammsymbole
A
17
Q
Modellierung
3 Abstraktionskonzepte
A
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
18
Q
UML
Assoziationen
A
- Repräsentation von Beziehungen
- optional: Assoziationsnamen + Leserichtung + Rollennamen + Sichtbarkeit von Rollen (+, -, #) + Kardinalitätsrestriktionen
- gerichtete Assoziationen: mit Pfeil
- Assoziationsklassen mit Strichellinie an Assoziationslinie
19
Q
UML
zulässige Multiplizitätsfestlegungen
A
x .. y
0 .. *
1 .. *
0 .. 1
1
*
20
Q
Relationsschema
Definition
A
R(A1,…,An)
- R = Name
- A1, …, An = Liste von paarweise entschiedenen Attributnamen bzw. ensprechenden Attributen
21
Q
Attribut
Definition
A
- Name: A
- Wertebereich: W(A)
- Kontext: zugehöriges Relationsschema
- Semantik: oft implizit durch Name
22
Q
Relation
Definition
A
- 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)
23
Q
Grad
Kardinalität
A
Grad = Anzahl der Attribute
Kardinalität = Anzahl der Tupel/Sätze/Zeilen
24
Q
Äquivalenz von Relationen R und S
A
- alle Attribute stimmen überein
- Anordnung der Attribute von S ist durch Permutation der Attribute von R entstanden
25
**Relationenmodell**
Darstellung Primärschlüssel/Fremdschlüssel
_Primärschlüssel_ o. *(APK):PK*
Fremdschlüssel: gepunktet unterstrichen + mit Pfeil o. *(AFK):FK auf S*
26
7 Aufgaben/Eigenschaften von DBS
1. Zentrale Kontrolle über die operationalen Daten
2. Hohe Datenunabhängigkeit
3. Hohe Leistung und Skalierbarkeit
4. Mächtige Datenmodelle und Abfragesprachen
5. Transaktionskonzept (ACID)
6. Automatisierte Zugriffskontrolle/Datenschutz
7. Ständige Verfügbarkeit
27
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).
28
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!
29
**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!
2. **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
30
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
31
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
32
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)
33
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
34
Relationenalgebra
Klassische Mengenoperationen u. Relationenoperationen
**Klassische Mengenoperationen**
* Vereinigung
* Differenz
* kartesisches Produkt
* Durchschnitt (ableitbar)
**Relationenoperationen**
* Selektion
* Projektion
* Verbund (Join) (ableitbar)
* Division (ableitbar)
35
Relationenalgebra
**Selektion**
* Def.
* Schreibweise
* Grad/Kardinalität
Auswahl von Zeilen einer Relation über Prädikate
36
Relationenalgebra
**Projektion**
* Def.
* Schreibweise
* Grad/Kardinalität
Auswahl der Spalten (Attribute) A1, A2, . . . , Ak aus einer Relation R
37
Relationenalgebra
## Footnote
**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
38
Relationenalgebra
## Footnote
**Semi-Join**
Ergebnisbeschränkung des Gleichverbundes auf eine der beiden Eingaberelationen
39
Relationenalgebra
## Footnote
**(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
40
Relationenalgebra
## Footnote
**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
41
Relationenalgebra
## Footnote
**Überblick Operationen**
42
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)
43
SQL
DML-Befehle
SELECT
INSERT
UPDATE
DELETE
Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN
44
SQL
DDL-Befehle
CREATE SCHEMA
CREATE DOMAIN
CREATE TABLE
CREATE VIEW
ALTER TABLE
DROP SCHEMA
DROP DOMAIN
DROP TABLE
DROP VIEW
45
SQL
Datenkontrolle
Constraints-Definitionen bei CREATE TABLE CREATE ASSERTION
DROP ASSERTION
GRANT
REVOKE
COMMIT
ROLLBACK
46
SQL
SELECT Statement
* SELECT DISTINCT erzwingt Duplikateliminierung
* ORDER BY: asc/desc
* Umbenennung: buch AS B -\> B.titel, ...
47
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)
48
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) ...
49
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
50
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
51
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
52
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
53
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“
54
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
55
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
56
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
57
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]
58
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
59
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))
60
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
61
Normalisierung
voll funktional vs. partiell abhängig
62
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
63
Normalisierung
Merkmale eines schlechten Datenbank-Entwurfs
* implizite Darstellung von Informationen
* Redundanzen
* potenzielle Inkonsistenz (**Änderungsanomalien**)
* **Einfügeanomalien**
* **Löschanomalien**
64
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.
65
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
66
Normalisierung
Überführung in 1NF
**Ziel:** nur einfache Attribute
1. Starte mit der übergeordneten Relation (Vaterrelation).
2. Nimm ihren Primärschlüssel und erweitere jede unmittelbar untergeordnete Relation damit zu einer selbständigen Relation.
3. Streiche alle nicht-einfachen Attribute (untergeordnete Relationen) aus der Vaterrelation.
4. 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')
67
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**
1. Bestimme funktionale Abhängigkeiten zwischen Nicht-Primärattributen und Schlüsselkandidaten
2. Eliminiere partiell abhängige Attribute und fasse sie in eigener Relation zusammen (unter Hinzunahme der zugehörigen Primärattribute)
68
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.
69
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
70
Normalisierung
Def. transitiv abhängig
71
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
72
Aufbau SQL-Umgebung
SQL-Umgebung (Environment) besteht aus
– **Katalogen:** pro Datenbank ein Schema
– **Benutzern**
– **INFORMATION\_SCHEMA** (Metadaten über alle Schemata)
=\> dreiteilige Objektnamen: ..
73
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
74
DDL SQL
SQL92 Datums-/Zeitangaben (Datetimes)
DATE, TIME, TIMESTAMP
TIME WITH TIME ZONE
TIMESTAMP WITH TIME ZONE
INTERVAL (\* Datums- und Zeitintervalle \*)
75
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
76
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)
77
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
78
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)
79
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]
80
Vorteile Sichtkonzept
* Erhöhung der Benutzerfreundlichkeit
* erhöhte Datenunabhängigkeit / verbesserte Schema-Evolution
* Datenschutz / Zugriffskontrolle
81
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
82
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 ...
83
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
84
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
85
_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 }
86
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**
87
_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
88
_Datenkontrolle_
Klassifikation von Integritätsbedingungen
89
_Datenkontrolle_
dynamische Integritätsbedingungen
2 Varianten
Bezug im Gegensatz zu statischen IB auf Änderungen selbst und damit auf mehrere Datenbankzustände
1. **Übergangsbedingungen:** Änderung von altem zu neuem DB-Zustand wird eingeschränkt (z.B. FAM-STAND von 'ledig' nach 'geschieden', Gehalt darf nicht kleiner werden)
2. **temporale Bedingungen:** Änderungen in bestimmtem zeitlichen Fenster werden eingeschränkt (z.B. Gehalt darf innerhalb von 3 Jahren nicht um mehr als 25% wachsen)
90
_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**
91
_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)
92
_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]
93
_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, ...)
94
_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)
95
_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**
96
_Datenkontrolle_
Fehlerarten + Maßnahmen
1. **Transaktionsfehler:** vollständiges Zurücksetzen auf Transaktionsbeginn (**Undo**)
2. **Systemfehler** (Rechnerausfall, DBVS-Absturz): **REDO** für erfolgreiche Transaktionen / **UNDO** aller durch Ausfall unterbrochenen Transaktionen
3. **Gerätefehler** (Plattenausfall):: **REDO** aller Änderungen auf einer Archivkopie o. Spiegelplatten bzw. RAID-Disk-Arrays
4. **Katastrophen** (Komplettausfall Rechenzentrum durch Naturkatastrophe): verteilte Datensicherung auf geographisch separierten Systemen
97
_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**
98
_Datenkontrolle_
TA als Schnittstelle zwischen Anwendungsprogramm und DBS
99
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
100
Transaktionskonzept
- Programmierschnittstellen
- Ausgänge
* begin of transaction (BOT)
* commit transaction („commit work“ in SQL)
* rollback transaction („rollback work“ in SQL)
101
Kartesisches Produkt
alles von R mit allen von S in Verbindung setzen
102
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)
