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

  • Data Definition Language
  • Data Manipulation Language

Datenmodelleigenschaften

  • Objekte der Datenbank
  • Beziehungen zwischen verschiedenen Objekten
  • Integritätsbedingugen
  • 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

  1. Eindeutig
    $t_1 \ne t_2 \Rightarrow \pi_S(t_1) \ne \pi_S(t_1)$
    Unterschiedliche Tupel ⇒ Unterschiedliche Schlüssel
  2. 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
Duplikatelimination in SQL nur bei UNION
$A - B$ Differenz X EXCEPT
MINUS
$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
$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)
    • Tupelkalkül: Variable = ein Tupel
    • Bereichskalkü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
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
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 ...]

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 Verlustfreie
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, …
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] mit Duplikatseliminierung
Vereinigung UNION ALL [CORRESPONDING] ohne Duplikatseliminierung
$A - B$ Differenz EXCEPT [CORRESPONDING]
MINUS [CORRESPONDING]
$A \cap B$ Durchschnitt INTERSECT [CORRESPONDING]
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.

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)
users/skruppy/ext/uni/5/dbs/start.txt · Zuletzt geändert: 2014/01/25 02:06 von skruppy