Einführung in Datenbanken und SQL

• Modellbegriff

• Exkurs Pointer

• Hierarchisch

• Netzwerk

Peter Chen; 1970

Es gibt keine ER-Datenbank

ER-Diagramm

• Entities

  • Attribute

    • Identifizierend

    • Beschreibend

    • Mehrwertig

    • Komplex

• Relationships

  • 1:n

  • 1:1

  • m:n

• 1976: Teorey

  • m:n-Relationships können selbst Attribute tragen

  • ›EER‹ (landläufig ER)

• Edgar Codd erkannte, dass die „Relationale Algebra“, richtig zum Einsatz gebracht, hervorragend zum systematischen Speichern und Wiederauffinden von Information geeignet ist.

• Relational: tabellenbasiert

• 12 ›Goldene Regeln‹ (eigene Lektion)

• Relationendiagramm

  • Relation

  • Relationship

• Umwandlung ERM->RM

• Objektrelational

  • Vererbung

  • Polymorphie

• ›Relationsdiagramme‹ im Management

Errata:

7:00 Wir haben im Diagramm ›Entitäten‹, keine ›Relationen‹. Die erzeugen wir erst.

Im Laufe der Zeit hat sich heraus gestellt, dass auch die Relationalen Modelle nicht uneingeschränkt geeignet sind, jede Art von Speicherungsaufgabe effizient zu bewältigen. So sind sie beispielsweise mit der schieren Menge an Daten in großen sozialen Netzwerken überfordert. (Und das ist nur eines von unzähligen Beispielen aus dem Sektor ›Big Data‹.) Andererseits ist es dort wenig relevant, ob eine von einer Benutzerin heute abgerufene Timeline bis ins letzte Detail mit jener übereinstimmt, die sie vorige Woche aufgerufen hat. Oder: es macht einen Unterschied, ob bei einer Abfrage viele bis alle Spalten einer Tabelle verarbeitet werden müssen wie es für "normale" Transaktionen typisch ist, wie wir sie hier im Kurs im ›OLTP‹ (›Online Transactional Processing‹) kennenlernen, oder nur wenige, wie es bspw. bei multidimensionalen Würfeln (›OLAP‹ – ›Online Analytical Processing‹) oft der Fall ist.

Daher haben sich auf die jeweilige Aufgabe zugeschnittene Systeme entwickelt, die unter dem Stichwort NoSQL (sprich: ›no-s-q-l‹) zusammengefasst werden, zumal SQL als standardisierte Anfragesprache dort nicht unterstützt wurde.

In vielen Zweigen stellte sich allerdings heraus, dass die Eigenschaften von SQL sehr praktisch und universell sind, sodass SQL auch in diesen Systemen, zusätzlich zum ›eigenen‹ Funktionsumfang, eingeführt wurde. Die Abkürzung änderte man kurzerhand auf NOSQL (sprich: ›n-o-s-q-l‹), das ab dann für ›not only SQL‹ stand.

Derartige andere Systeme sind nicht Kern unseres Kursthemas, weswegen sie hier lediglich (ohne Anspruch auf Vollständigkeit) aufgezählt werden sollen. Interessierte finden reichlich Details dazu in der Wikipedia.

In die Gruppe der NOSQL-Datenbanken fallen unter anderem folgende Modelle:

• Spaltenorientiert (z.B. HBase oder die Engine ›ColumnStore‹ von MariaDB)

• Dokumentenorientiert (z.B. MongoDB)

• Graphenorientiert (z.B. SAP HANA)

• Key-/Value-Store (z.B. Redis)

• Objektorientiert (z.B. db4o)

• Andere tabellenbasierte Verfahren, wie Google BigTable oder Apache HBase

• Kein ›Modell‹ im obigen Sinn!

• Extern

• Konzept

• Intern

• ›Logische Datenunabhängigkeit‹: entsteht zwischen Externer und Konzeptschicht

• ›Physische Datenunabhängigkeit‹: entsteht zwischen Konzept- und Interner Schicht

• Zugreifende Applikation über der Externen Schicht

• ODBC

• JDBC

• Integrierte Systeme (zB MS Access oder Delphi)

• Dateibasierte Datenbank

• Server-Datenbank

• Embedded Datenbank

• Anforderungsanalyse: Diese wird vom Datenbankentwickler gemeinsam mit den Benutzern (sowohl mit den Erzeugern als auch mit deren Kunden) erstellt. Analyse der Daten und deren natürliche Beziehungen sowie der zu verwendenden Hard- und Software. Eine bewährte Vorgangsweise ist, dass der Kunde ein ›Lastenheft‹ aus Anwendersicht und in Anwendersprache verfasst, auf das der Softwareersteller mit einem ›Pflichtenheft‹ antwortet, das detaillierte Angaben enthält, wie die im Lastenheft aufgeführten Anforderungen erfüllt werden. Mengengerüste (Daten- und Änderungsvolumen) sind dabei ein wichtiger, aber nicht selten übersehener Punkt! Das Pflichtenheft wird vom Kunden abgenommen und wird dann zur Vertragsgrundlage.

• Konzeptioneller Entwurf: Ausgehend vom Anforderungsprofil werden die Objekte des Problembereichs mit ihren Eigenschaften und Beziehungen zu anderen Objekten erfasst.

  Das heute in der Literatur am häufigsten genannte konzeptionelle Datenmodell ist das Entity-Relationship-Modell (ER-Modell) von Chen [Chen1976].

• Logischer Entwurf: Beim logischen Entwurf wird das konzeptionelle Schema in ein logisches Schema transformiert, das in dem Implementierungsmodell des ausgewählten DBMS beschrieben ist. Das heute am meisten verwendete logische Modell ist das Relationale Datenmodell von Codd. Es beschreibt die Daten einer Datenbank in Form von Tabellen.

  Tipp: In der Praxis ist das Relationale Modell ebenso gut auch zum konzeptionellen Entwurf geeignet, sodass auf einen eigenen Entwurfsschritt im ER fast immer verzichtet wird, sondern im Konzeptentwurf (gemeinsam mit dem Kunden) eine ›grobe‹ Tabellenstruktur erarbeitet wird, die dann im logischen Entwurf zum engültigen Schema weiterentwickelt wird.

• Physischer Entwurf: Dabei wird ein dem logischen Schema entsprechendes physisches Schema erstellt. Das Ziel dabei ist das Laufzeitverhalten zu optimieren. Die Umsetzung in ein physisches Schema wird vom DBMS übernommen und reduziert sich in der Praxis also auf die Auswahl eines besonders geeigneten DBMS.

• Verteilter Entwurf: Ist zusätzlich notwendig, wenn die Daten aufgeteilt werden (Datenverteilung). Besteht aus Datenfragmentierung (Unterteilung der Daten) und Datenzuordnung (an welcher Stelle werden die einzelnen Fragmente physisch gespeichert).

• Datenbankimplementierung, Überwachung und Wartung: Nach der Entwurfsphase müssen wir nun den Entwurf unter Verwendung einer Datendefinitionssprache (DDL) eines beliebigen DBMS umsetzen und mit einer Datenkontrollsprache (DCL) eine Berechtigungsstruktur definieren.. Mittels einer DML (Datenmanipulationssprache) können wir nun die DB mit Daten füllen, sie abfragen, Indizes setzen etc.

Kochrezept für einen sauberen Datenbankentwurf

1. Entitäten (Tabellen) identifizieren,

2. Attribute (Tabellenspalten) und ihre Datentypen identifzieren

3. Beziehungen identifizieren und für m:n-Beziehungen wo nötig Zwischentabellen erzeugen

4. Schlüsselkandidaten identifizieren

5. Häufige Suchbegriffe (Sekundärschlüssel) identifzieren und Indizes erzeugen

6. Normalisierung überprüfen und gegebenenfalls nachschärfen

7. Spezielle Anforderungen identifizieren und ggf. denormalisieren

• Namenskonvention!

Übung: Design einer Datenbank

Ein Kino will seinen Kartenverkauf mit einer DB unterstützen:

Es gibt 1 Saal mit einer definierten Anzahl von Sitzen. Jeder Sitz kann einer willkürlichen Preiskategorie zugeordnet werden.

Das Kino spielt im Laufe der Zeit Filme, die der Betreiber ebenfalls einer Preiskategorie zuordnet. Aus den Preiskategorien des Sitzes und des Filmes wird somit der endgültige Kartenpreis für die jeweilige Vorstellung berechnet.

Das System soll Reservierungen verwalten können. Eine Reservierung kann dabei 1 oder mehrere Sitze in einer Vorstellung umfassen (d.h. für Reservierungen in mehreren Vorstellungen muss der Kunde einfach mehrere Reservierungen vornehmen). Es muss verwaltet werden, ob die Karten für eine bestimmte Vorstellung bereits verkauft sind oder nicht, aber jedenfalls muss zu gewährleisten sein, dass lediglich reservierte (aber noch nicht verkaufte) Sitze nicht in derselben Vorstellung nochmals reserviert werden können.

Selbstverständlich dürfen an der Kassa "frei" verkaufte Karten ebenfalls nicht mehr reserviert oder verkauft werden können.

13:03 Filme

• ›Normalbetrieb‹: OLTP – Online Transactional Processing

  • Datensätze erfassen/ändern/löschen

• Analysieren, Auszählen, Auswerten: OLAP – Online Analytical Processing

  • Business Intelligence

  • Multidimensionale Würfel

Inhalt des Abschnitts

Bedeutung der Theorie für die Praxis

• Mathematische Grundlagen in der Algebra: "Relationale Algebra"

• Cantorscher Mengenbegriff

• Tabelle <=> Relation

• Relation = Menge von Tupeln

• Tupel <=> Tabellenzeile

• Tupel: Satz von Attributen; definiert & geordnet

• Attribut <=> Spalte, Feld

• Domäne = für Feld mögliche Inhalte

• 232 Features

• keines erfüllt alle (›table‹)

• RDBMS = Im Grund Mengenlehre!

Errata:

Die natürlichen Zahlen bilden mit Addition und Multiplikation nicht eine Gruppe, sondern je eine Gruppe. Die Struktur, die sich mit beiden Operationen ergibt, heißt ›Ring‹.

• Relation: Menge von Tupeln => Keine zwei gleichen Tupel! (Menge!)

• Reihenfolge der Zeilen egal (Explizite Felder)

• Reihenfolge der Spalten egal (Metadaten; Spaltennamen)

• Die 12 goldenen Regeln

  • 0. Ein RDBMS muss in der Lage sein, DBs vollständig über seine relationalen Fähigkeiten zu verwalten.

  • 1. Darstellung von Information: Alle Informationen in einer relationalen DB (einschl. Namen von Tabellen & Spalten etc.) sind explizit als Werte in Tabellen darzustellen.

  • 2. Zugriff auf Daten: Jede Information in einer RDB muss durch eine Kombination von Tabellenname, Spaltenname und Primärschlüsselwert auffindbar sein.

  • 3. Systematische Behandlung von ›Nullwerten‹: Das DBMS behandelt Nullwerte durchgängig gleich als unbekannte oder fehlende Daten und unterscheidet diese von Standardwerten, unabhängig vom jeweiligen Datentyp.

  • 4. Struktur einer DB: Die DB und ihre Inhalte werden in einem so genannten Systemkatalog auf derselben logischen Ebene wie die Daten selbst - also in Tabellen - beschrieben. Demzufolge lässt sich der Katalog mit Hilfe der Datenbanksprache abfragen.

  • 5. Anfragesprache: Zu einem relationalen System gehört mindestens eine Anfragesprache mit einem vollständigen Befehlssatz für Datendefinition & Integritätsregeln, Manipulation & Transaktionen sowie Autorisierung.

  • 6. Aktualisierung von Sichten: Alle Sichten, die theoretisch aktualisiert werden können, lassen sich auch vom System aktualisieren.

  • 7. Abfragen & Bearbeiten ganzer Tabellen(teile): Das DBMS unterstützt nicht nur Abfragen, sondern auch die Operationen für Einfügen, Aktualisieren und Löschen in Form ganzer Tabellen (mehrerer Datensätze). Das umfasst auch die Mengenoperationen (Union, Intersect, Except).

  • 8. Physische Datenunabhängigkeit: Der logische Zugriff auf die Daten durch Anwendungen und Ad-Hoc-Programme muss unabhängig von den physikalischen Zugriffsmethoden oder den Speicherstrukturen der Daten sein.

  • 9. Logische Datenunabhängigkeit: Änderungen an den Tabellenstrukturen dürfen keinen Einfluss auf die Logik der Anwendungen und Ad-Hoc-Programme haben, soweit das möglich ist.

  • 10. Unabhängigkeit der Integrität: Integritätsregeln müssen sich in der Datenbanksprache definieren lassen. Die Regeln müssen im Systemkatalog gespeichert werden. Es darf nicht möglich sein, diese Regeln zu umgehen.

  • 11. Verteilungsunabhängigkeit: Der logische Zugriff auf die Daten durch Anwendungen und ad-hoc-Programme darf sich bei   einer auf mehrere Standorte verteilten von einer nicht-verteilten Datenbank nicht unterscheiden.

  • 12. Kein Unterlaufen der Abfragesprache: Integritätsregeln, die über die Datenbanksprache definiert sind, dürfen sich nicht mit Hilfe von Low-Level-Sprachen umgehen lassen.

• Definition

• Daraus resultierende Probleme:

  • Änderungsaufwand

  • Inkonsistente Attributwerte

  • Löschen mit Informationsverlust

  • Unvollständiges Einfügen

• Eindeutige:

  • Schlüsselkandidat

  • Primärschlüssel

  • Alternativschlüssel

  • Surrogatschlüssel

  • Superschlüssel

• Nicht eindeutige:

  • Fremdschlüssel

  • Sekundärschlüssel

• Jargon für ›Determinante‹ (siehe ›Funktionale Abhängigkeiten‹)

Tipp: Sagen Sie beim Datenbankdesign Ihrer Datenbank, was Sie über die Schlüsseleigenschaften Ihrer Spalten wissen! (Indem Sie die betreffenden Spalten(gruppen) zu Primärschlüsseln oder eindeutig machen bzw. Indizes definieren.)

• Definition

• »Determinante«

• Beispiele

• Arten

  • trivial

  • voll

  • transitiv

  • reflexiv

  • mehrwertig

• Definition

• Normalformen:

  • 1NF: Alle Feldinhalte atomar, keine Wiederholungsgruppen (+ es gibt überall Primärschlüssel)

  • 2NF: 1NF + alle Nichtschlüssel von den Schlüsselkandidaten voll funktional abhänig

  • 3NF: 2NF + keine transitiven Abhänigkeiten

  • BCNF (Boyce-Codd): 1NF + Alle Determinanten sind Schlüsselkandidat

  • 4NF: 3NF + keine paarweisen mehrwertigen Abhängigkeiten

  • 5NF: 4NF + keine Rekonstruktion aus einfacheren Relationen möglich

• Anmerkungen

• Beispiel 1: Eine Tabelle in die 3NF bringen

• Beispiel 2: Eine Datenstruktur für ein Spreadsheet durch Normalisierung erzeugen

  Auch für (Papier-)Formulare mit Feldern kann man so eine Spreadsheet-Tabelle erzeugen

  und so eine Datenbankstruktur für Formulare erstellen.

• Beispiel 3: Vorverarbeitung eines Spreadsheets, um Normalisierung zu ermöglichen

• Definition

• Arten

  • 1:n

  • 1:1

  • m:n

  • Subkategorien

    • vollständig

    • unvollständig

  • Exkurs objektrelationale DB (PostgreSQL/Oracle)

• Implementierung

  • Programmierung

  • Deklarativ

    • Referenzielle Aktionen

      • restrict

      • no action

      • cascade

      • set null

      • set default

    • Match-Regeln

      • match simple

      • match partial

      • match full

      • Nicht von allen DBS unterstützt (PostreSQL, Oracle: ja, MS SQL Server, Sybase: nein)

1. Entwurf der Basis-Struktur in normalisierter Form

2. Identifikation problematischer Teile

3. Anlegen von zusätzlichen Strukturen, die NFen verletzen

Wichtig: so entstandene Datenstrukturen read-only behandeln und nur automatisiert aktualisieren!

• Definition

• ACID-Eigenschaften

  • Atomic

  • Consistent

  • Isolated

  • Durable

• Codeskizze

• Impliziter Transaktionsmodus

• Verschachtelte Transaktionen

  • Commit beendet innerste Transaktion

  • Rollback rollt ÄUßERSTE Transaktion zurück!

• Tipps

  • Kein Userinterface

  • So kompakt wiemöglich

  • Achtung vor Pseudo-Transaktionen!

3.11 – Parallelität: Problemaspekte der Mehrbenutzerfähigkeit

• Anomalien durch Parallelität

  • Dirty Read

  • Nonrepeatable Read

  • Phantom Read

  • (Lost Update)

(Das ›Lost Update‹ ist ein eigenes Thema: Oft liest man, dass ›Repeatable Read‹ genügen würde, um es zu verhindern. Aber das ist nur technisch richtig, praktisch jedoch wertlos: Man müsste eine Bildschirmmaske innerhalb einer Transaktion öffnen und das ist – aus Gründen! – ein völliges No-Go!)

• Isolationsstufen gemäß ISO

  • Read Uncommitted (alle 3 Anomalien werden zugelassen)

  • Read Committed (Dirty Read wird verhindert)

  • Repeatable Read (Nur noch Phantom Read wird zugelassen)

  • Serializable (keine der 3 Anomalien wird zugelassen)

                               Dirty Read     Nonrepeatable     Phantom
                                                             Read                  Read

Serializable                   ✓                      ✓                       ✓

Repeatable Read          ✓                      ✓                       !

Read Committed          ✓                       !                         !

Read Uncommitted       !                        !                         !

Jede Verbindung entscheidet für sich, welche Effekte sie akzeptieren kann und wählt die entsprechende Isolationsstufe; die Koordination aller Verbindungen erledigt der Server.

set transaction isolation level <stufe>;

• Technischer Ablauf einer Transaktion am Beispiel ›Buchung‹.

• Write-Ahead-Log (WAL)

• Regel- und Fehlerszenarien

• Ablauf der Verarbeitung von Pages bei Änderungen

• Symmetrie von Commit und Rollback

• Sperren

  • Modi

    • eXclusive

    • Shared

    • Update

    • Intent (IX/IS/IU)

    • Schema

    • (Bulk Update)

  • Granularität

    • Datensatz

    • Page

    • Tabelle

    • Schema

    • Datenbank

• Sperrsituationen

• Deadlocks

• Eigentlich auf Objekten der externen Schicht

• Sehr oft (leider): Konzeptschicht

• Die Chinook-DB

• Auch gemeinsam mit Abschnitt 3 bearbeitbar

• Herunterladbare Übungen beachten!

• Select … from … where

• Klauseln

• Groß-/Klein-Sensitivität überprüfen

• Ergebnisse mit Bedingungen einschränken

• Vergleichsoperatoren =, !=, <>, <, <=, >=, >

• Bedingungen verknüpfen

  • and/or

• Operatorpräzedenz

• between … and

• in()

• NULLs

• Unterschied zwischen NULL und leerem String

• Operator: is [not] null

• Stringdelimiter: '

• Wildcards

  • %

  • _

  • […]

• like

• Projektion von Spalten

• Aliases

  • Spalten-

  • Tabellen-

• Order By

  • asc

  • desc

• Limit / Offset

• T-SQL:

  • TOP

  • TOP WITH TIES

  • OFFSET / FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY)

    • Nur mit ORDER BY

• select distinct

• Abarbeitung eines SQL-Skripts

• Execution Tree

• Umbau

• Optimiser

Errata:

›Untergeben‹ ist kein Fachbegriff, sondern einfach nur ein Versprecher. Gemeint war ›übergeben‹.

• Namenskonflikte (zB SAP: bis zu 36000 Tabellen!) - Datenbank unterteilen

• Wie Ordner in Dateisystem: Namensraum

• Nur 1 Ebene (keine „Unterschemata“ im Sinne von Unterordnern)

• MS SQL Server: Default-Schema: dbo (bzw. userspezifisch), kann weggelassen werden

• Objekte aus anderen Schemata müssen „qualifiziert“ werden (schema.objekt)

• Abfragen mehrerer Tabellen auf einmal

• Datensätze „zusammenhängen“: Join

• Mehrere Arten

  • Cross: Alle Kombinationen (kaum Bedeutung)

  • Inner: Nur „passende“ Kombinationen (große Bedeutung!)

• „alte“ und „neue“ Syntax (ISO)

• Abfragen mehrerer Tabellen auf einmal

• Neben Cross und Inner Joins gibt es mehrere Outer Joins

  • Left

  • Right

  • Full

• Bei Inner und Outer Joins Achtung auf Vergleichsoperatoren!

  • Equi-Join (=) und

  • Non-equi Join (alle anderen: <, <=, <>, >= , >)

• Weiters (weniger wichtig) zu erwähnen:

  • Natural Join

  • Semi Join

• Berechnungen: Rechenregeln werden befolgt

• Diagramm lesen

• Datentypen ›money‹ und ›smallmoney‹ sind ›deprecated‹ – nicht mehr verwenden!

• case

  • simple

  • boolean

• Variablen

• Unicode-Strings: N'…

• Character stuffing für '

• char(), ascii(), nchar(), unicode()

• charindex()

• len()

• lower(), upper()

• ltrim(), rtrim()

• left(), right(), substring()

• replace(), stuff()

• string_split()

Errata:

17:30 Gemeint waren ›trailing blanks‹ (statt ›trailing strings‹)

• Datums-

• User-

• Konvertierungen

• count

  • *

  • <feld>

  • DISTINCT <feld>

• min()

• max()

• avg()

• sum()

• var()

• stdev()

• group by

• having

• OLAP-Erweiterung: with rollup

• union [all]

• intersect

• except

• in() (bereits bekannt),

• exists(),

• all(),

• any(),

• some()

• Definition

• Beispiel

• "Standardsituationen"

• Beispiel 2

• Unterabfrage im from

• Unterabfrage im where

• Geschachtelte Unterabfragen

• Definition

• Unterabfrage in der select-Liste

• select into

• Temporäre Tabellen

• insert

  • values

  • select

  • exec

Kein Log-Eintrag

Zum Einlesen größerer Datenmengen vom Datenträger

Mind. Differenzbackup unmittelbar nachher!

• update

• from (ABweichung vom ISO-Standard)

• Besser: subselect

• Trick: Transaktion und select-Kommentar

• Achtung auf Beziehungen und referenzielle Aktionen!

• delete

• from (Abweichung vom ISO-Standard)

• Besser: subselect

• Trick: Transaktion und select-Kommentar

• Achtung auf Beziehungen und referenzielle Aktionen!

• truncate table

• merge

• upsert (Oracle/PostgreSQL/MySQL/MariaDB)

CTE

• Empfehlung: Nach Kapitel 3

• Kann gemeinsam mit Kapitel 4 bearbeitet werden

• Weitere Objektarten mit create/alter/drop in Abschnitt 7

• DDL

  • create

  • alter

  • drop

• create database

• alter database

• drop database

• create schema

• drop schema

• create table

• Datentypen

• Constraints: Spalten- und Tabellenconstraints

• Constraints (Spalten- und Tabellen-)

  • primary key (ct)

  • unique (ct)

  • foreign key (ct)

    • on update, on delete – ref. Aktionen

  • check (ct)

  • [not] null (c)

  • default (c)

  • identity (c)

• alter table

• Spalte hinzufügen

• Spalte löschen

• Constraint hinzufügen

• Constraint löschen

Nicht allgemeingültig

• Tabellen: select, insert, update, delete

• Sichten: select, insert, update, delete

• Prodzeduren: execute

• Funktionen?

• Trigger?

• Ownership chains

grant

revoke

deny

connect

Welche noch?

Nach Kapitel 4

Nicht allgemeingültig

• Die Sicht als "gespeicherte Abfrage"

• Performanceüberlegungen

• Persisted Views

• View Updatability

• Übergabeparameter

• output-Parameter

• select in der Prodzedur

• Control-of-flow

• Cursor

• Error-Handling

• Temporäre Tabellenobjekte

• Geschäfts-, Applikations- & Datenbanklogik

• Datenbank- / Tabellentrigger

• after

• before (instead of)

• Trigger für einen/mehrere Datensätze

• Skalar

• Tabellenwertig

  • inline

  • multiline

• Einschränkungen

• Aggregatfunktionen

Nicht allgemeingültig

• Table Scan

• Zweck

• Funktionsweise: Index Scan

• Page Splitting

• Statistics Page & Optimiser

• Mehrspaltige Indizes

• Usabitily

• covering

  
  

• Einfügungen im Indexbereich

• Page Split

• Neue Ebene im Indexbaum durch neuen Wurzelknoten

• Clusterung

Indizieren von Sichten

1. Warum
2. Wie

1. Der Optimizer des MS SQL Server kann (genau gesagt: pro Thread in der Parallelverarbeitung) nur einen Index zur Beschleinigung der Abfrage wählen! Wenn man also aus einem Join selektieren möchte mit je einem Kriterium in * beiden Tabellen, so kann nur ein Index beschleunigen!

Lösung: Aus dem Join eine Sicht machen, diese indizieren.

Voraussetzungen

Die Sicht…

• …muss ›with schemabinding‹ deklariert werden

• …kann keine Views referenzieren

• …kann nur Tabellen innerhalb der eigenen Datenbank referenzieren

• …muss eindeutige Records als Ergebnis liefern

• …sofern eine Gruppierung vorhanden ist,

  • muss diese count_big(*) enthalten

  • darf kein having enthalten

• …darf keine doppelten Spaltennamen enthalten

Schemabinding: Sichten sind normalerweise verträglich gegenüber Struturänderungen in den zugrunde liegenden Strukturen – sie funktionieren einfach weiter. Mit der Option ›with schemabinding‹ können Basisstrukturen nicht mehr abgeändert werden.

2. Sichten indizieren – Wie: Siehe SQL-Code in der Ressource.

• Zweck

• Machart

• Arten

  • Complete

  • Differential

  • Increment

• Ganzer Kurs für Windows, Linux, MacOS

• MS SQL Server

  • 2019 (Alle Versionen ab 2008 R2)

  • Developer Edition

  • Jede andere für diesen Kurs geeignet: Express, Standard, Enterprise, Datacenter

• Datenbank AdventureWorks

  • 2019 (=2017, seit 2014 keine Änderungen mehr)

  • Diagramme hier und in der Lektion über AdventureWorks

  • Kann Server im Netz sein oder lokal

• Client-Programm: DBeaver

  • Datenbank Chinook (lokal)

  • Arbeitet mit Vielzahl von Servern!

• Englische Versionen

  • Deutsch möglich

  • Usergroups & dadurch Support besser

• Optional:

  • MSSQL Management Studio (nur Windows)

  • Azure Data Studio

  • DBSchema

  • UMLet

  • WinSQL (nur Windows – ODBC-Treiber)

  • JaySQL

  • Squirrel-SQL

  • IDEs

    • NetBeans

    • Eclipse

    • IntelliJ IDEA

• Übungen

  • einfache hier in Udemy

  • fortgeschrittene in der eigenen Umgebung

1. Überprüfen, ob Java installiert ist. Es muss keine aktuelle Version sein, wie man hier sieht. Falls eine Fehlermeldung kommt, das ›Java Development Kit‹ von https://jdk.java.net/14/ herunterladen und installieren.

2. DBeaver im MS Store suchen und installieren.

3. DBeaver starten.

4. Die Beispieldatenbank ›Chinook‹ erzeugen lassen.

5. Die Verbindung zur Datenbank herstellen und die nötigen Treiber herunterladen lassen (einmalig).

6. Den Editor öffnen und eine Abfrage eingeben.

7. Die Abfrage mit Strg+Enter starten.

8. Das Programm beenden.

9. DBeaver aus dem Windows-Menü heraus starten (›dbe‹ tippen und dann auswählen).

1. Überprüfen, ob der ›snap-Daemon‹ (snapd) installiert ist. Dieses Paket sollte es in jeder Distribution im Standardumfang geben.

2. snap install dbeaver-ce

3. DBeaver starten.

4. Die Beispieldatenbank ›Chinook‹ erzeugen lassen.

5. Die Verbindung zur Datenbank herstellen und die nötigen Treiber herunterladen lassen (einmalig).

6. Den Editor öffnen und eine Abfrage eingeben.

7. Die Abfrage mit Strg+Enter starten.

9.4 – Installation DBeaver auf MacOS

1. Download

https://dbeaver.io
(Downloads von dbeaver.io ggf. erlauben)

2. Installation
Heruntergeladenes dbeaver-ce-x.y.z.macos.dmg öffnen
Dbeaver-Symbol auf Applications ziehen

3. Erster Aufruf und Einrichten der Chinook-Datenbank

apt-Paket

Download-Site

Nur DB-Engine

9.8 – Installation SQL Server auf MacOS

1. Virtualisierungsumgebung “Docker”

a. Vorhanden?
In Finder Application Docker suchen

b. Herunterladen
https://docker.com
(Downloads von docker.com ggf. erlauben)

c. Installieren
Heruntergeladenes Docker.dmg öffnen
Docker-Symbol auf Applications ziehen

d. Starten
Im Finder unter Applications

e. Einstellen
Nicht mehr als die Hälfte der CPUs
4GB RAM Minimum, nicht mehr als halben RAM

2. MS SQL Server

a. Docker-Container herunterladen
sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

b. Initialisieren
sudo docker run -e ACCEPT_EULA=Y
    -e SA_PASSWORD=R2D2.c3p0 -p 1433:1433 --name mssql
    -d mcr.microsoft.com/mssql/server:2019-latest

SA_PASSWORD muss komplex sein (klein, groß, Ziffer, Sonderzeichen)! Notieren!!

c. Überprüfen:
docker ps -a
1. Anmeldung in DBeaver
(User name: sa, Kennwort aus 2b; einmalig: automatischer Treiber-Download)

Daten- bzw. Backup-Ordner finden

Download AdventureWorks-Backup

Restore

• MSSQL Management Studio (nur Windows, nur MS SQL Server)

• Azure Data Studio (MS SQL Server)

• DBSchema

• UMLet

• WinSQL (nur Windows)

• JaySQL

• Squirrel-SQL

• IDEs

  • NetBeans

  • Eclipse

  • IntelliJ IDEA

DBSchema

Von Zeit zu Zeit schlägt DBeaver vor, ein Update zu machen.

Da bin ich generell immer ein bisschen skeptisch, weil ich nicht weiß, ob das jeweilige Software-Repository davon erfährt!

Daher klicke ich Update-Vorschläge von Programmen eigentlich immer weg und warte, bis die entsprechende Version ins jeweilige Repository aufgenommen wurde.

Das ist bei Windows – jedenfalls, wenn Sie so installiert haben, wie im Video gezeigt – der ›Store‹, über den ich das Update machen lassen würde.

Bei den Debain-Linux-Varianten (die .deb-Installationspakete verwenden, wie Debian, Ubuntu, Xubuntu etc.) haben wir apt verwendet und ich würde mit apt-get update und apt-get install dbeaver-ce das Update durchführen lassen, wenn es die neue Version im apt-Repository gibt.

Innerhalb der Arch-Familie (Arch, Manjaro usw.) haben wir snap benutzt, wo man einfach den Befehl snap install dbeaver-ce wiederholt. Wenn es ein Update gibt, wird es gemacht und wenn nicht, darauf hingewiesen.

Unter MacOS haben wir das Installationsprogramm heruntergeladen und ausgeführt. Dort können Sie ein vorgeschlagenes Update einfach ausführen.

Eine der Stärken von DBeaver ist die vollautomatische Treiberinstallation. Sowohl beim Erstaufruf, als auch bei Updates der jeweiligen Treiber werden die benötigten Versionen beim Verbindungsaufbau ganz von selbst heruntergeladen, installiert und ab dann benutzt.

Für uns kann das leider mit einer Unannehmlichkeit verbunden sein: Im Zuge des Treiberupdates wandert die Datei Chinook.db u.U. in einen anderen Ordner und die im DBeaver gespeicherte Verbindung geht ins Leere. Das merken Sie, wenn beim Öffnen der Datenbank eine Fehlermeldung erscheint.

Das ist aber leicht zu korrigieren, wenn man weiß, wo die Datei zu finden ist, z.B. aktuell:

(~ steht für den Pfad zum eigenen Home-Ordner und muss in Windows durch diesen ersetzt werden!)

Windows: ~\AppData\Roaming\DBeaverData\workspace6\.metadata\sample-database-sqlite-1\Chinook.db

Linux: ~/snap/dbeaver-ce/78/.local/share/DBeaverData/workspace6/.metadata/sample-database-sqlite-1/Chinook.db

MacOS: ~/Library/DBeaverData/workspace6/.metadata/sample-database-sqlite-1/Chinook.db

Man muss in den Verbindungseinstellungen den neuen Pfad angeben und abspeichern und die Verbidnung zur Chinook-Datenbank ist wieder intakt.

Beachten Sie bei den Unix-Betriebssystemen, dass Namen, die mit einem Punkt beginnen, im Ordnersystem ›versteckt‹ sind, sodass Sie sie (inklusive Punkt) tippen müssen, um in den jeweiligen Ordner zu gelangen. Das ist beim Pfad zur Datenbankdatei von Chinook an zwei Stellen der Fall: .local und .metadata

Zu den "Programmierübungen" hier im Udemy-Kurs ist leider eine Anmerkung nötig:

Udemy bietet die Möglichkeit automatisch bewerteter Übungen in einer Reihe von Programmiersprachen. Jüngst kam SQL dazu, aber Udemy bezeichnet den ensprechenden Automaten selbst noch als "beta".

Und das merkt man leider gleich an mehreren Stellen.

• So ist es beispielsweise nicht möglich, bei einer Übung mit mehreren Statements, diese einzeln bewerten zu lassen! Solange der Kursteilnehmer nicht alle Statements richtig hat, erscheint die lakonische Meldung "Deine Lösung hat leider nicht funktioniert" - was natürlich nicht sehr aussagekräftig ist, da man nicht weiß, welches Statement zu ändern ist.

• Im Falle eines unrichtigen Lösungsversuchs erhält man auch keine Erklärung, was eigentlich falsch wäre oder wenigstens, wo der Fehler liegt! Ebenfalls nicht sehr hilfreich.

• Der Automat versucht zwar, das richtige Abfrageergebnis anzuzeigen, aber in vielen Fällen besteht diese Anzeige lediglich aus dem immer wieder wiederholten Wort "[object]", was natürlich ebenfalls völlig nichtssagend ist und TeilnehmerInnen nicht hilft - selbst, wenn ihre Lösung richtig ist! Und auch, wenn es gelingt, sieht man nur eine endlose Datenwurst, aber keinerlei Tabellenstruktur.

• Und last, but not least, gelingt es dem Autor selbst nicht, bspw. bei der 2. Programmierübung eine "richtige" Lösung abzugeben und er erhält immer wieder nur "funktioniert leider nicht". Und das, obwohl er das Beispiel selbst erstellt hat und relativ sicher ist, dass seine Lösung stimmt.

Aus all diesen Gründen möchte ich anregen, die Programmierübungen in der eigenen Umgebung durchzuführen und die eigenen Statements mit den jeweils vorhandenen Musterlösungen abzugleichen. Dazu ein Tipp: Öffnen Sie die Musterlösung nur, wenn Sie wirklich nicht weiterkommen.

Ich bedaure diese Umstände, die nicht in meinem Bereich liegen und hoffe auf eine baldige Besserung!

P.S. Diese Situation ist bereits eingemeldet und die Udemy-Technik meinte dazu, dass die Plattform von den von mir vorgeschlagenen Änderungen profitieren würde. Ob oder wie schnell das allerdings zu einer Verbesserung der Situation führt, kann ich leider nicht sagen.

Hier finden Sie das Begleitbuch aus meiner Feder, das ich textaffinen Teilnehmern empfehlen kann.

Gerne können Sie sich auch auf meiner Webseite mit weiteren Angeboten von Wolf Online Training umschauen. Insbesondere finden Sie dort

weitere Kurse des Autors:

• Einführung in Business Intelligence & Big Data

• Programmieren, Java & Netbeans

Außerdem finden Sie dort auch eine Kurzbeschreibung des Buch über die Programmiersprache Processing, ein Java-Spin-Off – meines Wissens das kompletteste in deutscher Sprache.

Bei den Resourcen zu dieser Bonuslektion außerdem:

• DB/2, die ›Mutter aller Relationalen Datenbanken‹ in der gratis Community License Edition.
  Läuft auf Windows, Linux & MacOS, aber auch auf z/OS (dem IBM Betriebssystem für Großrechenanlagen)

• Für technisch Interessierte ein Blick hinter die Kulissen:
  Quellcodes von open source Datenbanksystemen

  • Ocelot (SQL-99; für kommerzielle Anwendungen weniger interessant; speichert in XML-Dateien)

  • SmallSQL (SQL-99; sehr klarer, verständlicher Quellcode)

  • HSQLDB (SQL:2016; näher am aktuellen Standard, dadurch aber auch schwieriger zu lesen)