====== 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

===== Mathe =====
==== 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

=== Arten ===
  * Superschlüssel: Nur 1. Eigenschaft erfüllt
  * Schlüsselkandidat: 1. und 2. Eigenschaft erfüllt
  * Primärschlüssel: Ein beliebiger aber fester Schlüsselkandidat

==== 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'' | {{:users:skruppy:ext:uni:5:dbs:union.png?35|}} Duplikatelimination in SQL nur bei ''UNION'' |
^  $A - B$                               | Differenz    |  X  |     | ''EXCEPT'' \\ ''MINUS'' | {{:users:skruppy:ext:uni:5:dbs: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'' | {{:users:skruppy:ext:uni:5:dbs: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 \not \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} | |
===== 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//'' |
| ''//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


<code sql>
CREATE TABLE name (
  name typ constraints,
  name typ constraints,
  integritätsbedingungen
)
</code>

<code sql>
DROP TABLE name
</code>

<code sql>
ALTER TABLE name ADD (
  name typ constraints,
  name typ constraints
)
</code>

<code sql>
ALTER TABLE name DROP (
  name,
  name
)
</code>

<code sql>
ALTER TABLE name MODIFY (
  name typ constraints,
  name typ constraints
)
</code>

<code sql>
SELECT [DISTINCT]
  *,
  t.*,
  t.a,
  42,
  32 AS Foo  -- Hier AS
FROM
  t,
  Unisinn u  -- KEIN AS
WHERE
</code>


<code sql>
INSERT INTO name [(a1, a2)]
VALUES
  (v11, v21),
  (v12, v22)
</code>

<code sql>
INSERT INTO name (a1, a2)
(SELECT ...)
</code>

<code sql>
UPDATE name
SET
  a1=v1, 
  a2=v2
[WHERE ...]
</code>

<code sql>
DElETE FROM name
[WHERE ...]
</code>
  
=== Mengen Operationen ===

== 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 ==
<code sql>
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
</code>

== Grouping ==
<code sql>
GROUP BY x
HAVING SUM(y) > 42
</code>

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 ==
<code sql>
ORDER BY a [ASC/DESC], b [ASC/DESC], ...
</code>

  * '' ASC'': 0, 1, 2, ... (default)
  * ''DESC'': 9, 8, 7, ...

== Weitere ==
//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]'' | {{:users:skruppy:ext:uni:5:dbs:union.png?35|}} **mit** Duplikatseliminierung |
|             | Vereinigung  | ''UNION ALL [CORRESPONDING]'' | {{:users:skruppy:ext:uni:5:dbs:union.png?35|}} **ohne** Duplikatseliminierung |
|  $A - B$    | Differenz    | ''EXCEPT [CORRESPONDING]'' \\ ''MINUS [CORRESPONDING]'' | {{:users:skruppy:ext:uni:5:dbs:except.png?35|}} |
|  $A \cap B$ | Durchschnitt | ''INTERSECT [CORRESPONDING]'' | {{:users:skruppy:ext:uni:5:dbs:intersect.png?35|}} |

<code sql>
SELECT ...
UNION/UNION ALL/EXCEPT/MINUS/INTERSECT [CORRESPONDING]
SELECT ...
</code>

  * ''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.


=== Beispiele ===
Finde Paare mit gleicher Eigenschaft
<code sql>
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)