====== Datenbanksysteme I ====== ===== Allgemein ===== ==== Probleme ==== * Fehlende Datenunabhängigkeit (änderung des Dateiformats) * Redundanz (u.a. Änderungsanomalien) * Schnittstellen (Jeder kocht sein eigenes Süppchen) * Mehrbenutzer System * Datenverlust bei Systemabsturz/deffekt * Zugriffkontrolle ==== Aufbau ==== * DB-Anwendung * DBS * DBMS * DB | Anwendungen (mehrere) || ^ Externe Ebene | Views (mehrere) | ^ | ↓↓ Logische Datenunabhängigkeit ↓↓ | ^ Konzeptionelle Ebene | | ^ | ↓↓ Physische Datenunabhängigkeit ↓↓ | ^ Interne Ebene | Speicherformat | ==== Anforderungen ==== * **Integration** //einheitlicher// Zugriff auf //alle// Daten einer Anwendung * **Operationen** auf den Daten (ändern, löschen, ...) * **Data Dictionary** Schema anschauen * **Benutzersicheten** views * **Konsistenzüberwachung** bei Änderung * **Zugriffskontrolle** * **Transaktionen** * **Synchronisation** (Mehrbenutzersystem) ==== Sprachen ==== * **D**ata **D**efinition **L**anguage * **D**ata **M**anipulation **L**anguage ==== Datenmodelleigenschaften ==== * //Objekte// der Datenbank * //Beziehungen// zwischen verschiedenen Objekten * //Integrität//sbedingugen * Angebotenen //Operationen// (zum Abfragen) ==== Datenmodelle ==== * **Hierarchisches ~** * **Netzwerk ~** (Art linked list, was den parent im link Ring beinhaltet) * **Relationales ~** * **Objektorientiertes ~** * **Objekt-Relationales ~** ==== Verbindung zur Datenbank ==== * API * DSL ===== Optimierung ===== | %%|%% \\ Anfrage (deklarativ) \\ ↓ | ^ Scanner/Parser/View-Erzeuger ^ | %%|%% \\ algebraischer Ausdruck \\ ↓ | ^ Anfrageoptimierung ^ | %%|%% \\ Auswertungsplan (prozedural) \\ ↓ | ^ Ausführung ^ ==== Kanonischer Auswertungsplan ==== Query in Funktionsbaum übersetzten * Kaskadierter Kartesische Produkte (//nur 2 Eingänge//) * Jede ''WHERE'' Bedingung //einzeln// Beispiel SELECT A1, A2 FROM R1, R2, R3 WHERE B1, B2 Ergibt $\pi_{A_1, A_2}(\sigma_{B_1}(\sigma_{B_2}(R_1 \times (R_2 \times R_3))))$ Optimierung: Filter so früh wie möglich ==== Wo kann optimiert werden ==== * **Logische Anfrageoptimierung** \\ Auswertungsplan umbauen * **Physische Anfrageoptimierung** * Join Strategie * **Nested Loop** \\ Kartesisches Produkt Filtern * **Sort Merge** \\ Vorher Sortieren, dann mergen * **Indexed Loop** \\ Tabelle 1 abarbeiten, Tabelle 2 über Index mergen * Index Verwenden? ==== Wie kann optimiert werden ==== * **Regelbasiert** \\ Heuristiken * **Kostenbasiert** \\ Iterativ Heuristiken ausprobieren und Kostenentwicklung beobachten ===== Transaktionen ===== * **Atomicity** * **Consistency** * **Isolation** * **Durability** ==== Datensicherheit ==== Technische Fehler ^ Wann ^ Was ^ Undo ^ Redo ^ | Transaktionsfehler | Rücksetzen | //Lokales Undo// \\ nicht abgeschlossene Transaktion rückgängig | | | Systemfehler | Warmstart | //Globales Undo// \\ nicht abgeschlossene Transaktionen rückgängig | //Globales Redo// \\ Alle abgeschlossenen Transaktionen nachholen | | Medienfehler | Kaltstart | \\ Backup einspielen | //Globales Redo// \\ Alle abgeschlossenen Transaktionen nachholen | ==== Integrität ==== * **Statische Integrität** \\ Einschränkung der Datenbank//zustände// * **Dynamische Integrität** \\ Einschränkung der //Zustandsübergänge// * **Modellinhärente Integrität** * //Typ//integrität * //Schlüssel//integrität * //Referentielle// Integrität ==== Synchronisation ==== === Anomalien === == Lost update == ^ t1 | r(x) | | w(x) | ^ t2 | | w(x) | | == Dirty read/write == ^ t1 | w(x) | | w(x) | ^ t2 | | r(x) | | == Non-repeatable read == ^ t1 | r(x) | | r(x) | ^ t2 | | w(x) | | == Phantom-Problem == //Non-repeatable read// mit Aggregiertem read === Serialisierung === ^ allgemeiner Schedule | Durcheinander | \[\frac{(m_1 + m_2 + \dots){\color{red}!}}{m_1{\color{red}!} \cdot m_2{\color{red}!} \cdot \dots}\] | ^ serialisierbarer (allgemeiner) Schedule | Durcheinander kann in Blockform gebracht werden | ^ serieller Schedule | Blockform | \[n{\color{red}!}\] | Graph Zeichen * Knoten: Transaktionen * Kanten: Abhängigkeiten ^ Übergang ^ Markierung ^ | $w_i(x) \rightarrow r_j(x)$ | wr(x) | | $r_i(x) \rightarrow w_j(x)$ | rw(x) | | $w_i(x) \rightarrow w_j(x)$ | ww(x) | //Kein// rr(x) Zyklenfrei? => Serialisierbar durch topologisches sorieren === Technicken === * Pessimistische Ablaufsteuerung (Locking) * Optimistische Ablaufsteuerung (Zeitstempelverfahren) \\ Notfalls rollback ===== Anwendung ===== ==== "MVC" Aufteilungen ==== ^ ^ Thin client ^ Fat client ^ Stored procedure ^ Web Application ^ Application Server ^ ^ Client | GUI | GUI \\ Logik | GUI \\ Hauptprogramm | Browser | GUI | ^ Application \\ Server | | | | | Logik | ^ DB-Server | \\ Logik \\ DB-Schnittstelle \\ DB | \\ \\ DB-Schnittstelle \\ DB | \\ Prozeduren \\ DB-Schnittstelle \\ DB | Webserver \\ Logik \\ DB-Schnittstelle \\ DB | \\ \\ DB-Schnittstelle \\ DB | ==== Interaktion ==== * Call-Level-Schnittstelle (library) * Embedded SQL ==== Cursor ==== - **Declaration** \\ SQL eingeben - **Open** \\ Ausführen - **Fetch** loop - **Close** Vorübersetzes SQL mit Platzhaltern ===== Mathe ===== ==== Funktionale Abhängigkeiten ==== === Arten === * **Partielle funktionale Abhängigkeit**: $X \rightarrow Y$ * **Volle funktionale Abhängigkeit**: $X \dot{\rightarrow} Y$ (Minimalität) === Axiome === ^ Reflexivität | ${\color{ForestGreen}Y} \subseteq {\color{red}X}$ | $\Longrightarrow$ | ${\color{red}X} \rightarrow {\color{ForestGreen}Y}$ | ^ Verstärkung | ${\color{red}X} \rightarrow {\color{ForestGreen}Y}$ | $\Longrightarrow$ | ${\color{red}X}{\color{RoyalBlue}Z} \rightarrow {\color{ForestGreen}Y}{\color{RoyalBlue}Z}$ | ^ Transistivität | ${\color{red}X} \rightarrow {\color{ForestGreen}Y} \wedge {\color{ForestGreen}Y} \rightarrow {\color{RoyalBlue}Z}$ | $\Longrightarrow$ | ${\color{red}X} \rightarrow {\color{RoyalBlue}Z}$ | ^ Vereinigung | ${\color{red}X} \rightarrow {\color{ForestGreen}Y} \wedge {\color{red}X} \rightarrow {\color{RoyalBlue}Z}$ | $\Longrightarrow$ | ${\color{red}X} \rightarrow {\color{ForestGreen}Y}{\color{RoyalBlue}Z}$ | ^ Dekomposition | ${\color{red}X} \rightarrow {\color{ForestGreen}Y}{\color{RoyalBlue}Z}$ | $\Longrightarrow$ | ${\color{red}X} \rightarrow {\color{ForestGreen}Y} \wedge {\color{red}X} \rightarrow {\color{RoyalBlue}Z}$ | ^ Pseudotransitivität | ${\color{red}X} \rightarrow {\color{ForestGreen}Y} \wedge {\color{RoyalBlue}Z}{\color{ForestGreen}Y} \rightarrow V$ | $\Longrightarrow$ | ${\color{red}X}{\color{RoyalBlue}Z} \rightarrow V$ | === Attributhülle === //AttrHülle(F,X)// mit \\ //F// = FDs \\ //X// = Start Attributmenge Starte mit //X// und wende so lange F ab, bis nichts mehr geht. ("Schau wie weit du kommst") ==== Normalisierung ==== === Anomalien === * **Update~** \\ Nicht alles wird geupdatet * **Insert~** \\ Inkonsistentes insert * **Delete~** \\ Löschen der letzten Verwendung, löscht auch Objekt === 1. NF === Bedingung > Alle Attribute //atomar// Attributwerte dürfen nicht ... * Listen sein: {Telefon1, Telefon2, Telefon3, ...} * Zusammengesetzt sein: (Hausnummer, PLZ, ...) === 2. NF === Bedingung > //Jedes// Attribut entweder > voll funktional Abhängig von //jedem// Schlüsselkandidaten > -- oder -- > prim (teil eines Schlüsselkandidaten) Trivial: nur //ein einelementiger// Schlüsselkandidat => 2. NF Sonst: Vom Schlüssel nicht voll funktional Abhängige Attribute werden herausgelöst, in eigene Tabelle === 3. NF === Bedingung //Jede// FD $X \rightarrow Y$ ist mindestens eins: * tivial * X enthält Schlüsselkandidat * $\forall a \in (Y-X)$ ist prim Keine (nicht trivialen) FDs unter Nicht-Schlüssel-Attributen - **Kanonische Überdeckung** - Linksreduktion \\ Komme ich von //X-x// und //identischem FD// dennoch auf Y, dann streiche x aus X - Rechtsreduktion \\ Komme ich von //X// und //FD ohne y// dennoch auf y in Y, dann streiche y aus Y - $X \rightarrow \emptyset$ entfernen - Gleiche X zusammenfassen - **Relationsschema erzeugen und FDs zuweisen** - Aus jedem FD erzeuge eine Relation mit X als Primärschlüssel und Y als Attribute - Ordne jedes FD den Relationen zu, die alle seine "Buchstaben" enthält - **Schlüsselkandidat rekonstruieren** \\ Stelle sicher, dass eine Relation einen ehemaligen Schlüsselkandidaten enthält, notfalls erzeuge eine neue mit leerem ohne zugeordnete FDs - **Überflüssige Relationen eliminieren** \\ Lösche Relationen, die Teilmenge einer anderen sind. === Boyce-Codee-Normalform === Bedingung //Jede// FD $X \rightarrow Y$ ist mindestens eins: * tivial * X enthält Schlüsselkandidat Keine (nicht trivialen) FDs unter Schlüssel-Attributen === 4. NF === Wirkung > Nicht mehrere Tabellen in einer. Bedingung //Jede// MVD $X \twoheadrightarrow Y$ ist mindestens eins: * tivial * X enthält Schlüsselkandidat MVD (Multi Valued Dependency): //Mehre// Attribut//werte//, eines Attributs, sind von der linken Seite abhängig. ==== Begriffe ==== * Domain * = Typ * Kann auch Liste Sein * endlich * unendlich (nicht in DB Darstellbar) * z.B. Int, Sting, Date, {rot, gel, käsekuchen} * Relation = Ausprägung eines Relationsschemata = Menge = {} * Tupel = Zeile = Ausprägung einer Relation = () * Grad = Stelligkeit = Elemente im Tupel * Relationsschema: Über //eine// Tabelle * DB-Schema: Über //alle// Tabellen ==== Schlüssel ==== === Eigenschaften === - Eindeutig \\ $t_1 \ne t_2 \Rightarrow \pi_S(t_1) \ne \pi_S(t_1)$ \\ Unterschiedliche Tupel ⇒ Unterschiedliche Schlüssel - Minimal \\ $\text{Eindeutig}(T) \wedge T \subseteq S \Rightarrow T = S$ \\ Keine Teilmenge des Schlüssels erfüllt die 1. Eigenschaft (Minimal bedeutet //nicht//: Die wenigsten Attribute) Schlüssel und FD sind //semantische Eigenschaften// (nicht anhand von aktueller Ausprägung erkennbar) === Arten === * Superschlüssel: Nur 1. Eigenschaft erfüllt ($S \rightarrow R$) * Schlüsselkandidat: 1. und 2. Eigenschaft erfüllt ($S \dot{\rightarrow} R$) * Primärschlüssel: Ein beliebiger aber fester Schlüsselkandidat Primes Attribut: Teil eines Schlüsselkandidaten === Beweis === Beispiel: Beweise das //S = (A, B)// Schlüsselkandidat ist. - **Eindeutigkeit** \\ $S \rightarrow R$ herleiten - **Minimalität** \\ Zeigen das weder $A \rightarrow R$ noch $B \rightarrow R$ gild ==== Relationsschemata ==== === geordnet === ^ Schema | R = (id:Integer, Name:String) | ^ Ausprägung | r = {(1, Alice), (2, Bob), (42, Eve)} | === ungeordnet (mit Domänenabbildung) === ^ Schema | R = {id, Name} mit \\ dom(id) = Integer, dom(Name) = String | ^ Ausprägung | r = {t1, t2, t3} mit \\ t1(id) = 1, t1(Name) = Alice, \\ t2(id) = 2, t2(Name) = Bob, \\ t3(id) = 42, t3(Name) = Eve | ===== Relationale Algebra ===== ^ Formel ^ Name ^ Iden- \\ tisch ^ Duplikat- \\ elimi- \\ nation ^ SQL ^ Kommentar ^ ^ $A \cup B$ | Vereinigung | X | X | ''UNION'' \\ ''UNION ALL'' | {{:uni:5:union.png?35|}} Duplikatelimination in SQL nur bei ''UNION'' | ^ $A - B$ | Differenz | X | | ''EXCEPT'' \\ ''MINUS'' | {{:uni:5:except.png?35|}} | ^ $A \times B$ | Kreuzprodukt \\ Kartesisches Produkt | | | | | ^ $\sigma_F(A)$ | Selektion | | | ''WHERE'' | | ^ $\pi_{a, b, \dots}(A)$ | Projektion | | X | ''SELECT'' | | ^ $A \cap B$ | Durchschnitt | X | | ''INTERSECT'' | {{:uni:5:intersect.png?35|}} | ^ $A \div B$ | Quotient | | | | Allquantor. Die Tupel aus A, die alle Attribute aus jedem B Tupel gleich haben, ohne die B Attribute. | ^ $A \bowtie B$ | Natural Join | | | ''**NATURAL JOIN** t'' | Inner-Join (Tupel ohne Partner gehen verloren) \\ Vergleich //aller// gleichbenannten | ^ $A \underset{a = b}{\bowtie} B$ | Equi Join | | X | ''JOIN t **USING(**id**)**'' | Inner-Join (Tupel ohne Partner gehen verloren) | ^ $A \underset{a \Theta b}{\bowtie} B$ | Theta Join | | | ''JOIN t **ON** f'' | Inner-Join (Tupel ohne Partner gehen verloren) \\ Θ = Vergleichsoperator | Identisch * Relationale Algebra: //Typ// und //Name// jedes Attributs muss übereinstimmen (Schemata identisch) * SQL: //Typ// jedes Attributs muss kompatibel sein (nur die Position ist maßgebend) ==== Relationale Algebra und SQL ==== * Für jeden relationalen Ausdruck existiert ein SQL statement (SQL ist relational vollständig) * SQL Projektion entfernt nicht immer Duplikate implizit (Explizites ''DISTINCT'' bei umsetzung!!) * SQL kann mehr: Sortieren, Aggregation ===== Relationen-Kalkül ===== t ∈ Schema ⇔ Schema(t) t[A] ⇔ t.A Ψ(r | t): Ersetze //t// in Ψ(r | t) mit dem konkreten Tupel //r// * Relationale algebra \\ prozedual (wie) * Relationen-Kalkül \\ deklarativ (was) * //Tupel//kalkül: Variable = ein Tupel * //Bereichs//kalkül: Variable = einfacher Typ Quantoren sind nur im Relationenkalkül möglich. Variable ist ... * frei: keinem ∃ oder ∀ zugeordnet * gebunden: einem ∃ oder ∀ zugeordnet * Zuordnungszustand kann sich ändern Es ist möglich unendlich (nicht speicherbare) Relationen zu beschreiben. \\ Eine Relation ist //sicher// wenn alle Vaiablen //x// einer (gespeicherten) Relation angehören //Schema(x)//. ^ Tupelkalkül ^ Bereichskalkül ^ ^ Alle Großstädte in Bayern ^^ | Schema(t) = Schema(Städte) \\ {t %%|%% Städte(t) ∧ f[Land] = Bayern ∧ t[SEinw] ≥ 500000} | | ^ In welchem Land liegt Passau ^^ | Schema(t) = (Land:String) \\ {t %%|%% (∃ u ∈ Städte)(u.Sname = Passau ∧ u.Land = t.Land} | $\{ x_3 \mid \exists x_1, x_2 : (\text{Städte}(x_1, x_2, x_3) \wedge x_1 = \text{Passau}) \}$ | | | $\{ x_3 \mid \exists x_2 : (\text{Städte}(\text{Passau}, x_2, x_3)) \}$ | ^ CDU-regierte Städte ^^ | Schema(t) = Schema(Städte) \\ {t %%|%% Städte(t) ∧ (∃ u ∈ Länder)(u.Lname = t.Land ∧ u.Partei = CDU) } | $\{x_1 \mid \exists x_2, x_3, y_2 : (\text{Städte}(x_1, x_2, x_3) \wedge \text{Länder}(x_3, y_2, \text{CDU}))\}$ | ^ SPD alleinregierte Länder ^^ | Schema(t) = SChema(Länder) \\ {t %%|%% Länder(t) ∧ (∀ u ∈ Länder)(u.LName = t.LName ⇒ u.Partei = SPD} | $\{x_1 \mid \exists x_2 : (\text{Länder}(x_1, x_2, \text{SPD}) \wedge \neg \exists y_3 : \text{Länder}(x_1, x_2, y_3) \wedge y_3 \ne \text{SPD} \}$ | | Schema(t) = SChema(Länder) \\ {t %%|%% Länder(t) ∧ (∀ u ∈ Länder)(u.LName = t.LName ⇒ u.Partei = SPD} | | ===== Speicher Stukturen ===== Jeder zusätzlich Schlüssel wirkt sich negativ auf updates aus. * **//Daten//organisierende Strukturen** \\ //Suchbaum//verfahren (B+-Baum) * **//Raum//organisierende Strukturen** \\ dynamische //Hash//-Verfahren Seitenarten (für Daten- und Raumorganisierende Strukturen) * **//Directory//seiten** \\ Verweis auf Datenseiten * **//Daten//seiten** \\ Die eigentlichen Daten Schlüsselarten * Primärschlüsselsuche * B+-Baum * Lineares Hashing * Sekundärschlüsselsuche * Invertierte Listen ==== B+ Baum ==== {{http://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Bplustree.png/800px-Bplustree.png?400}} * //Alle Daten// ausschließlich in den Blättern (Datenseiten) * Schlüssel (Seperatoren) kommen mehrfach in Directoryseiten vor. * Mit //rechtem// Bruder ausgleichen * Ordnung //m// * Wurzel: //1// ... //2m// Kinder * Knoten: //m// ... //2m// Kinder ==== Erweiterbares Hashing (Directory) ==== {{http://pluto.ksi.edu/~cyh/cis501/f5-14.GIF?400}} * Die Zahlen geben an, wie viele Bits "beachtet" werden müssen. * Die Zahl des Diresctorys ist immer größer-gleich der Zahl der Datenseiten, ansonsten Directory Zahl inkrementieren & Directory verdoppeln * Läuft eine Datenseite über => Split + Zahl inkrementieren ==== Lineares Hashing (Ohne directory) ==== $\text{Belegungsfaktor} = \frac{\text{Alle Schlüssel}}{\text{Kapazität in \textit{Primär}seite}} > 0.8 \Rightarrow \text{Expansion}$ ==== Invertierte Listen ==== * Alle Attribute einzeln indizieren * einzeln suchen * Schnittmenge der Ergebnislisten bilden ==== Reverse pattern matching ==== * Für ''LIKE "Horst%"'' ==== Composite Indizes ==== mehrere Attribute * Attributwerte werden concatiniert sortiert gespeichert * Reihenfolge wichtig ===== E/R-Modell ===== Elemente: * Entitie (eckig) * Relationship (raute) * Können Attribute haben * Attribute (oval) * Primärschlüssel unterstreichen * (strich) * Können Rollen haben * Pfeil = 1-KArdinalität * Voller Kringel: 1.. / NOT NULL * Hohler Kringel: 0.. / NULL * Vererbung (Pyramide) * Text: "isa" Umsetzen: * Vererbung * Eine gemeinsame Tabelle \\ -- ODER -- * "isa" in einzelne Relationen auflösen * 1:1 Relation - Beide Relationen zusamenfassen - Nur //einer// der alten Primärschlüssel wird der neue Primärschlüssel ===== SQL ===== ==== Typen ==== | ''integer'' | ''integer4'' | ''int'' | | | ''smallint'' | ''integer2'' | | | | ''float(''//p//'')'' | ''float'' | | Bits der mantisse (nicht Exponent) | | ''decimal(''//p//'', ''//q//'')'' | ''numeric(''//p//'', ''//q//'')'' | | Exakte Komma Zahlen. p Stellen gesamt, davon q Nachkommastellen. | | ''character(''//n//'')'' | ''char(''//n//'')'' | | | | ''character varying(''//n//'')'' | ''varchar(''//n//'')'' | | | | ''date'' | | | | | ''time'' | | | | | ''timestamp'' | | | | http://www.postgresql.org/docs/9.2/static/datatype-numeric.html ==== Constrains ==== * ''NOT NULL'' * ''PRIMARY KEY'' * ''UNIQUE'' * ''REFERENCES ''//t//''(''//a//'')'' * ''DEFAULT ''//w// * ''CHECK ''//f// ==== Integritätsbedingungen ==== * ''PRIMARY KEY (''//a1//'', ''//a2//'', ...)'' * ''UNIQUE (''//a1//'', ''//a2//'', ...)'' * ''FOREIGN KEY (''//a1//'', ''//a2//'', ...) REFERENCES ''//t//''(''//b1//'', ''//b2//'', ...)'' * ''CHECK ''//f// ==== Schlüssel ==== * Schlüsselkandidat * ''UNIQUE'' * ''UNIQUE (''//a1//'', ''//a2//'', ...)'' * Primärschlüssel * ''PRIMARY KEY'' * ''PRIMARY KEY (''//a1//'', ''//a2//'', ...)'' * Fremdschlüssel * ''REFERENCES ''//t//''(''//a//'')'' * ''FOREIGN KEY (''//a1//'', ''//a2//'', ...) REFERENCES ''//t//''(''//b1//'', ''//b2//'', ...)'' ==== Fremdschlüssel Zusätze ==== Folgende sachen können am Ende einer ''REFERENCES'' Angabe stehen * ''ON DELETE SET NULL'' \\ Wenn das Referenzierte gelöscht wird, wird dieser auf ''NULL'' gesetzt * ''ON DELETE CASCADE'' \\ Wenn das Referenzierte gelöscht wird, wird auch dieser gelöscht * ''ON UPDATE CASCADE'' \\ Wenn das Referenzierte geändert wird, wird auch dieser geändert ==== Bla ==== | ''//a// + //b//'' | ''//a// - //b//'' | ''//a// %%*%% //b//'' | ''//a// %%/%% //b//'' | | ''//a// **=** //b//'' | ''//a// **<>** //b//'' | | | | ''//a// < //b//'' | ''//a// > //b//'' | ''//a// %%<=%% //b//'' | ''//a// >= //b//'' | | ''//x// BETWEEN //a// AND //b//'' | | | | | ''//a// IS NULL'' | ''//a// IS NOT NULL'' | | ''//a// IN (...)'' | | | | | ''//a// AND //b//'' | ''//a// OR //b//'' | ''//a// NOT //b//'' | | | ''//s1// %%||%% //s2//'' | ''CHAR_LENGTH(//s//)'' | ''SUBSTRING(s FROM pos [FOR len])'' | ''//a// LIKE %%'...'%%'' | * ''%'' 0...n beliebige Zeichen * ''_'' 1 beliebiges Zeichen CREATE TABLE name ( name typ constraints, name typ constraints, integritätsbedingungen ) DROP TABLE name ALTER TABLE name ADD ( name typ constraints, name typ constraints ) ALTER TABLE name DROP ( name, name ) ALTER TABLE name MODIFY ( name typ constraints, name typ constraints ) SELECT [DISTINCT] *, t.*, t.a, 42, 32 AS Foo -- Hier AS FROM t, Unisinn u, -- KEIN AS (SELECT ...) WHERE INSERT INTO name [(a1, a2)] VALUES (v11, v21), (v12, v22) INSERT INTO name (a1, a2) (SELECT ...) UPDATE name SET a1=v1, a2=v2 [WHERE ...] DElETE FROM name [WHERE ...] GRANT ALL ALL PRIVILEGES SELECT INSERT DELETE UPDATE UPDATE(a, b) ON t TO TU PUBLIC, userName [WIDTH GRANT OPTION] REVOKE ALL ALL PRIVILEGES SELECT INSERT DELETE UPDATE UPDATE(a, b) ON t FROM TU PUBLIC, userName [RESTRICT | CASCADE] -- RESTRICT: Nur den user löschen -- CASCADE: nehme die rechte auch denjenigen, die sie vom user bekommen haben CREATE INDEX name ON t (a1, a2, ...); DROP INDEX name; -- Laut script kein START TRANSACTION SET TRANSACTION READ-ONLY -- kein INSERT, UPDATE und DELETE. Dafür bessere Transaktionsplanung SET TRANSACTION READ-WRITE -- default SAVEPOINT name COMMIT ROLLBACK ROLLBACK TO name ==== Join ==== | ''**NATURAL JOIN** t'' | Natural Join | $A \bowtie B$ | Vergleich //aller// gleichbenannten | | ''JOIN t **USING(**id**)**'' | Equi Join | $A \underset{a = b}{\bowtie} B$ | | | ''JOIN t **ON** f'' | Theta Join | $A \underset{a \Theta b}{\bowtie} B$ | Θ = Vergleichsoperator | * inner * outer * left * right * full ^ Join Art ^ Verlust//freie// \\ Seite ^^ NULL ^^ ^ ::: ^ L ^ R ^ L ^ R ^ | ''[INNER] JOIN'' | | | | | | ''LEFT [OUTER] JOIN'' | ✓ | | | ✓ | | ''RIGHT [OUTER] JOIN'' | | ✓ | ✓ | | | ''FULL [OUTER] JOIN'' | ✓ | ✓ | ✓ | ✓ | ==== Quantoren ==== WHERE EXISTS (SELECT ...) -- Ergebnis Form egal WHERE NOT EXISTS (SELECT ...) -- Ergebnis Form egal WHERE ... a Θ ALL (SELECT ...) -- Ergebnis genau ein Attribut, aber mehre Tupel WHERE ... a Θ SOME/MANY (SELECT ...) -- Ergebnis genau ein Attribut, aber mehre Tupel WHERE ... a IN (SELECT ...) -- Ergebnis genau ein Attribut, aber mehre Tupel ==== Grouping ==== GROUP BY x HAVING SUM(y) > 42 keine Aggregartfunktionen in ''WHERE'' In ''SELECT'' nur erlaubt * Groupierte Attribute * Aggregatfunktionen * ''COUNT'' * ''SUM'' * ''AVG'' * ''MAX'' * ''MIN'' In ''SELECT'' //nicht// erlaubt * %%*%% * An groupierte Elemente gejointe Elemente, die selbst nicht groupiert sind. \\ Abhilfe: * Auch nach denen groupieren * ''MAX(x)'' ^ Befehl ^ Zähle ^^ ^ ::: ^ NULL ^ doppelte ^ | ''COUNT(**DISTINCT** a)'' | | | | ''COUNT([ALL] a)'' | | ✓ | | ''COUNT(**%%*%%**)'' | ✓ | ✓ | ==== Order ==== ORDER BY a [ASC/DESC], b [ASC/DESC], ... * '' ASC'': 0, 1, 2, ... (default) * ''DESC'': 9, 8, 7, ... ==== Mengenoperatoren ==== //Typ// jedes Attributs muss kompatibel sein * Nur die Position ist maßgebend * Länge bei Strings egal * Genauigkeit bei Zahlen egal [Im Gegensatz zur relationalen Algebra, wo //Typ// und //Name// jedes Attributs übereinstimmen muss (Schemata identisch)] | $A \cup B$ | Vereinigung | ''UNION [CORRESPONDING]'' | {{:uni:5:union.png?35|}} **mit** Duplikatseliminierung | | | Vereinigung | ''UNION ALL [CORRESPONDING]'' | {{:uni:5:union.png?35|}} **ohne** Duplikatseliminierung | | $A - B$ | Differenz | ''EXCEPT [CORRESPONDING]'' \\ ''MINUS [CORRESPONDING]'' | {{:uni:5:except.png?35|}} | | $A \cap B$ | Durchschnitt | ''INTERSECT [CORRESPONDING]'' | {{:uni:5:intersect.png?35|}} | SELECT ... UNION/UNION ALL/EXCEPT/MINUS/INTERSECT [CORRESPONDING] SELECT ... * ''CORRESPONDING'' beschränkt Ergebniss auf gleiche Namen. Die Position ist dann egal. * Besser: Explizite Auswahl/Sortierung mit ''SELECT'' * Tip: Explizites ''NULL'' in ''SELECT'' bei fehlenden Werten. ==== View ==== Nutzen * Übersicht * Datenschutz === Normaler View === CREATE [OR REPLACE] VIEW v AS SELECT ... DROP VIEW v Nicht erlaubt in //Basisrelation//: * ''ORDER BY'' === Materialisierter View === INSERT INTO mv (SELECT ...) === Effekt-Konformität === > Kein merkbarer unterschied zwischen View und Relation ^ Teil im \\ View ^ Angewendete \\ Operation ^ Problembeschreibung ^ | ''SELECT'' | ''INSERT'' | Default (''NULL'') für wegprojezierte Attribute | | ::: | ''INSER''T \\ ''UPDATE'' \\ ''DELETE'' | Nicht möglich, wenn //''DISTINCT''// oder //arithmetische Ausdrücke// | | ''WHERE'' | ''INSERT'' \\ ''UPDATE'' | //Tupel-Migration//: Tupel verschwindet, da nicht von ''WHERE'' erfasst \\ Abhilfe: ''WHERE ... WITH CHECK OPTION'' | | ''JOIN'' \\ ''GROUP BY'' | ''INSERT'' \\ ''UPDATE'' \\ ''DELETE'' | Nicht möglich | | Subquerry | ''INSERT'' \\ ''UPDATE'' \\ ''DELETE'' | Nicht möglich, wenn //Selbstbezug// | ==== Beispiele ==== Finde Paare mit gleicher Eigenschaft SELECT DISTINCT t1.name, t2.name FROM Foo t1, Foo t2 WHERE t1.attrib = t2.attrib AND t1.name < t2.name -- Macht aus (AA, AB, BA, BB) : (AB)