Datenbanken

Relationale Algebra

Operatoren

Difference
Gibt die Unterschiede zweier Tabellen aus. Es werden nur Datensätze berücksichtig welche in beiden Tabellen vorkommen.
Intersect

Es wird die Schnittmenge zweier Tabellen augegeben.

Product

Multipliziert alle Zeilen einer Tabelle mit allen Zeilen einer anderen Tabelle. Kann auch mit mehreren Tabellen gemacht werden. Generiert sehr viele Daten.

Project

Gibt alle Zeilen einer definierten Spalte aus.

Select

Zeigt nur diese Zeilen an welche einem vorgegebenen Wert entsprechen. Etwa alle Stundensätze welche grösser 40 sind.

Joins

Verbindet zwei Tabellen zu einer. Diese werden über die Fremdschlüssel verknüpft. "Natural Join" gibt eine normalisierte Form aus.

Inner Join Equi
Hier werden nur Werte übernommen welche einen passenden Fremdschlüssel haben.
Inner Join Theta
Hier werden der Primärschlüssel und der Fremdschlüssel mit dem Operatoren grösser und kleiner als verglichen. Wird in der Praxis selten verwendet.
Outer Join
Es werden immer ein kompletter Satz auf einen anderen abgeglichen. Wobei nur die jeweilige Seite komplett übernommen wird. "Left Outer Join" zeigt die linke Tabelle komplett und bei "Right Outer Join" wird die rechte Tabelle komplett angezeigt.

Formelzeichen

Restriction
REST(r1, A2=A)
Projection
PROJ(r1,A2)
Product
PRODUCT(r1,r2)
Union
UNION(r1,r2)
Intersection
INTERSECTION(r1,r2)
Difference
DIFFERENCE(r1,r2)
Division
DIVISION(r1,A2,r2)
Join
JOIN(r1,A2=A4,r2)
Outer Join
left JOIN(r1,A1*=A4,r2), right JOIN(r1,A1=*A4,r2)

Erweiterte Bakus Nauer Form

BEFEHL [optional] (argument)

  • [] optional Parameter
  • () zwingender Parameter
  • entweder oder
  • {} zusätzlich möglich

Befehlsformen

DDL
Data Definition Language create table, create view, etc
DML
Data Manipulation Language select, insert, update
DCL
Data Control Language grant, revoke, commit

DDL

TABLE

CREATE TABLE [Benutzer] (
    [Benutzernummer] INTEGER NOT NULL,
    [Nachname] VARCHAR(40),
    [Vorname] VARCHAR(40),
    [Position] VARCHAR(40),
    CONSTRAINT [PK_Benutzer] PRIMARY KEY ([Benutzernummer])
)
DROP TABLE tabelname;

Constraint

  1. Foreign Key

    CONSTRAINT fk_bestellung FOREIGN KEY (kun_nr)
        REFERENCES tab_kunde (kun_nr)
    
  2. Unique

    CONSTRAINT uq_name UNIQUE kunde_name
    

VIEW

Ein View ist eine gedankliche, virtuelle Tabelle, die in Wirklichkeit nicht vorhanden ist. Sie beansprucht selbst keinen Platz.

CREATE kunden_zrh VIEW AS
    SELECT name, strasse, ort FROM kunden WHERE kanton = ‚Zuerich‘

DML

INSERT

INSERT INTO abteilung VALUES ( ‘A003‘ ‚‘BRUECKENBAU‘)

UPDATE

UPDATE abteilung
    SET abt_name = ‘STRASSENBAU‘
    WHERE abt_id = ‘A003‘

SELECT

SELECT [ALL | DISTINCT] { * | table.* [, table.* ...}
FROM [user.table [alias] [,[user.]table [alias]] ...
    [WHERE Klausel]
    [GROUP BY Klausel]
    [HAVING Klausel]
    [ORDER BY Klausel]
SELECT au_id, au_fname, au_lname
FROM autor

WHERE

Vergleichsoperatoren : =, <>, <, >, <=, >=
Like : name [NOT] LIKE ‚M%‘
IN abtnr [NOT] IN ( ‚A5‘, ‘A6‘)
BETWEEN : gnetto [NOT] BETWEEN 5000 AND 10000
ISNULL : name IS [NOT] NULL
% : steht für beliebige Zeichenkette.
_ : steht als Platzhalter für ein beliebiges

ORDER BY

Syntax: ORDER BY {folgenummer | spaltenname} { ASC | DESC }

Beispiele:
ORDER BY name ASC, vorname DESC
ORDER BY 1, 2

Aggregate

MIN: MIN(gehalt)
MAX: MAX(gehalt)
AVG: AVG(gehalt)
SUM: SUM(gehalt)
COUNT: COUNT(*), COUNT( DISTINCT plz)

GROUPY BY

SELECT NAME, COUNT(*) FROM KUNDE GROUP BY NAME

DELETE

DELETE FROM abteilung
    WHERE abt_id = ‘A003‘

DCL

GRANT

GRANT berechtigung [,berechtigung …]
    ON objekt [,objekt …]
    TO benutzer [,benutzer …]

Mögliche Berechtigungen:

  • Tabelle ALL PRIVILEGES
  • ALTER
  • DELETE
  • INSERT
  • SELECT
  • UPDATE
  • UPDATE(spaltenname)

REVOKE

REVOKE berechtigung [,brechtigung …]
    ON objekt [,objekt …]
    FROM benutzer [,benutzer …]

ROLES

CREATE ROLE rolenname;
GRANT SELECT, INSERT ON tabelle_xy to rolenname;
GRANT rolenname TO user;

COMMIT

BEGIN TRANSACTION
Update tblTest Set userid = ‚King‘
...
COMMIT TRANSACTION

ROLLBACK

BEGIN Transaktion
Update tblTest Set userid = ‚King‘
...
ROLLBACK TRANSACTION

Stored Procedures

Vorgefertigte SQL Anweisungen welche der User immer wieder verwenden kann. Dies ist insbesondere bei komplexen Anweisungen hilfreich oder wenn man eine Schnittstelle für einen Interface Programmierer zur Verfügung stellen möchte.

Vorteile:

  • Wiederverwendbarkeit
  • Wartbarkeit
  • Performance
  • Sicherheit

Nachteile:

  • Wiederverwendbarkeit wenn die DB getauscht wird
  • Muss zusätzlich erlernt werden
  • Testbarkeit, mehr Schichten zum testen
  • Aufteilung der Verantwortlichkeit, wer macht die Logik
  • Debugging wird durch die Abstraktion schwieriger
  • Aufwändiger zum erstellen als übliche Programmiersprachen
  • Die Datenbank übernimmt Logik
  • Refactoring

Befehle

Bei den folgenden Befehlen wird nur der grossgeschriebene Teil benötigt damit sie ausgeführt werden können. Ähnlich wie etwa bi IOS von Cisco.

Erstellen

CREATE PROCedure  HumanResources.GetMitarbeiter
    (@LastName NVARCHAR(50),
    @FirstName NVARCHAR(50))
AS select * from mitarbeit;

Ausführen

EXEcute HumanResources.GetMitarbeiter 'max', 'muster'

Löschen

DROP PROCedures proc_name
GO

User Defined Functions

Können einen Wert zurückgegeben. Dieser ist entweder Scalar oder Tabular. Scalar bedeutet das es nur ein einzelner Wert ist und Tabluar bedeutet das eine ganze Tabelle ausgegeben wird.

Befehle

Erstellen

create FUNCTION functionname()
RETURNS varchar(100)
AS BEGIN
END
go

Ausführen

EXEcute functionname
go

Löschen

DROP FUNCTION functionname
GO

Triggers

Sind eine spezielle Art Stored Procedures. Diese werden dabei immer automatisch ausgeführt wenn ihr jeweiliges Event eintritt. Ein Trigger ist dabei immer einer Tabelle zugeordnet. Wird die Tabelle gelöscht wird auch der Trigger gelöscht. Pro Tabelle kann es jeweils nur max. 3 Triggers geben. Insert, Update und Delete.

Trigger haben keine Parameter sondern beziehen sich jeweils auf die automatisch generierten "inserterted" und "deleted" Tabellen.

Trigger werden immer dann eingesetzt wenn sichergestellt werden muss das eine nachfolgende Aktion ausgeführt werden muss. Da Trigger immer automatisch ausgeführt werden wenn das jeweilige Event ausgelöst wird.

Einer der Nachteile von Triggers ist das sie direkt der Tabelle angehängt werden. Dies kann dazu führen das man eine unerwartete Reaktion erhält und nicht genau weiss woher sie kommt.

Falls möglich sollten zuerst immer Check Constraints verwendet werden bevor man Trigger einsetzt.

Befehle

CREATE TRIGGER trigger_name
    ON table_name
FOR {INSERT|UPDATE|DELETE}
AS
    sql_code

INSTEAD-OF Trigger

INSTEAD-OF Trigger sind Trigger welche anstelle der eigentlichen Aktion ausgeführt werden. Diese können etwa verwendet werden um das Löschen auf einer Tabelle um jeden Fall zu verhindern.

Constraints

Primary Key

Definiert den Primary Key in einer Tabelle.

CONSTRAINT pk_kunde PRIMARY KEY (kun_nr)

Foreign Key

Definiert das Feld als einen Foreign Key einer anderen Tabelle.

CONSTRAINT fk_bestellung FOREIGN KEY (kun_nr) REFERENCES tab_kunde (kun_nr)

Unique

Stellt sicher das der Wert nur einmal in einer Spalte existiert.

CREATE TABLE Angebot ( ..., CONSTRAINT U_code UNIQUE (lfr_code, art_code))

Default

Wird das Feld bei einem INSERT Befehl leergelassen, wird dieser Wert eingesetzt.

CREATE TABLE Adult (  ..., CONSTRAINT D_state, DEFAULT ‘CA‘ FOR state)

Check

Check constraints erlauben es einem auf einer Tabelle festzulegen welche Werte gespeichert werden dürfen. Die Check Constraints werden auch bei updates ausgelöst.

create table Patrons (
    ID int primary key,
    FirstName varchar(50),
    LastName varchar(50),
    StartDate datetime,
    EndDate datetime,
    constraint EndDateMustComeAfterStartDate check(EndDate > StartDate)

Oracle DB

Allgemeines

Oracle DB ist eine sogenannte Objekt Relationale Datenbank. Oracle versucht dabei die Vorteile von objektorientierte Datenbanken und relationalen Datenbanken zu verbinden.

Stored Procedures

Bei Oracle muss man in Stored Procedures den Variabeln den Typ nicht mitgeben. Dieser kann automatisch entsprechend dem defnierten Wert in der Tabelle zugewiesen werden.

Packages

Sind ähnlich wie Module von Python. Zum Aufrufen muss man allerdings immer den Kompletten Namen angeben.

User Define Type

create or replace type Adresse_t as object
    (strasse varchar2(20),
    hausnummer number(5),
    ortsname varchar2(40));

create or replace type Personal_t  as object
    (nachname varchar2(20),
    vorname varchar(20),
    geburtsdatum date,
    gehalt number(7,2),
    kinder number(5),
    adresse adresse_t);

CREATE TABLE personal OF Personal_t OBJECT IDENTIFIER IS SYSTEM GENERATED;

insert into personal_o values (
    personal_t('Hörler', 'Ivan', to_date('1999-12-01', 'YYYY-MM-DD'),
        1000.00, 4,
        adresse_t('musterstrasse', 2, 4900, 'Langenthal')));

Datawarehouse

Was ist ein Data Warehouse

Daten aus den operativen Systemen und externen Quellen werden in einem Data Warehouse gespeichert. Mittels Analyseprogrammen wird versucht Informationen aus den Daten zu generieren. Teilweise auch um vorherzusehen wie sich etwas in der Zukunft verhalten könnte basierend auf den bestehenden Daten. Die Daten können einem auch eine historische Sicht auf die Daten liefern. Also wie sich die Daten dahin entwickelt haben.

Mögliche Synome sind etwa:

  • Management-Informationssystem (MIS)
  • Executive Information System (EIS)
  • Decision Support System (DSS)
  • Data Mart ein dediziertes Datawarehouse. Ein gewachsenes Data Mart ist ein Data Warehouse.

Zusammenzug

Die Daten sind in der Regel auf verschieden operative Systeme verteilt. Die Daten werden dann im Data-Warehouse zusammengezogen und asynchron aktualisieren.

OLAP

OLAP Applikation
ermöglicht das Betrachten der Daten aus dem Warehouse aus verschiedenen multidimensionalen Perspektiven.
Data Mining
Analyse und Reporting Tools, Hilfsmittel und Informationen aus den Daten generieren. Entscheidende Faktoren heraussuchen. Beziehungen und Zusammenhänge in den Daten aufdecken.

Dimensionen

Mit einem Datawarehouse können dann mehrdimensionale Informationen dargestellt werden. Im Falle der Verkaufszahlen eines Produkts könnte man etwa die folgenden drei Dimensionen auswerten. Es kann beliebig viele Dimensionen geben. Würfel welche mehr als 3 Dimensionen abbilden werden Hyper-Cubes genannt.

  • Raum (Verkaufsregion)
  • Zeit
  • Produkt

Unterschiede zu operationale Systemen

Operationale Systeme Data Warehouse System
Historische Daten gehen teilweise verloren Analytik im Vordergrund
Read & Write Idealerweise Read Only
Performance ist wichtig  
Sicherheit ist wichtig  
   

ETL

Bezeichnet den Export der operativen Daten und die Umwandlung der Daten für das DSS. Sowie am Schluss das Loading, also den Import in das DSS.

Modellierungs Muster

Es gibt zwei bekannte Muster für die Datenbankmodellierung eines Datawarehouses. Star- und Snowflakeschema. Die Wahl hängt dabei stark von der Anwendung ab. Die Schema können aber auch gemischt eingesetzt werden.

Generell sollten nicht einfach alle Daten gespeichert werden sondern nur die, welche auch für die Analyse relevant sind. Redundanz ist an gewissen stellen akzeptabel.

Star Schema

Besteht aus einer Fakten Tabelle welche die eigentlichen Daten enthält. Die umliegenden Tabellen zeigen dann die Metadaten (Dimensionen) auf. Der Primary Key der Fakten Tabelle besteht aus den jeweilgen Primary Keys der Dimensionstabellen. Die Faktentabelle wird sehr gross und ist normalisiert. Die Dimensionstabellen müssen nicht zwingend normalisiert sein.

Beim erstelllen der Datenbank muss die feinste Granularität bereits feststehen da die Faktentabelle auf die kleinste Grannularität aggregiert wird.

Snowflake Schema

Dieses Schema ist ähnlich aufgebaut wie das Star Schema, allerdings wurden die Dimensionstabellen normalisiert. Ist etwas langsam allerdings wird die Datenbank dafür kleiner. Immer die "feinste" Tabelle wird mit der Faktentabelle verbunden ansonsten ist eine Drill-Down nicht möglich.

Vorgehen

  1. Geschäftsprozesse auswählen
  2. Granularität festlegen
  3. Dimensionen entwerfen
  4. Kennzahlen bestimmen

Verteilte Datenbanken

Eine verteilte Datenbank verhält sich für den User gleich wie eine lokale Datenbank. Das System tritt dabei als einzelnes System auf.

Vorteile

  • bessere Skalierbarkeit
  • globale Verfügbarkeit
  • hohe Datensicherheit
  • allenfalls niedrigere Hardwarekosten

*Nachteile*

  • höhere Komplexität

12 Date Regeln

  1. Der User sieht nur ein System
  2. Jeder Rechner hat die Kontolle über seine Daten
  3. Die Verarbeitung wird nicht zentral gesteuert (Flaschenhals)
  4. Es spielt keine Rolle wenn ein Server ausfällt
  5. Der User sieht nicht wo die Daten liegen
  6. Daten werden an mehreren Orten gespeichert
  7. Die Daten werden automatisch repliziert
  8. Die Daten einer Query können von mehrern System aus kommen
  9. ACID wird auch über mehrere Server eingehalten
  10. Beliebige Hardware kann benutzt werden
  11. Ein beliebiges OS kann eingesetzt werden
  12. Das Netzwerk pfuscht nichte rein
  13. Es können unterschiedliche SQL System gleicher Version benutzt werden

4 Ebenen Architektur

externes Schema
Views für Anwendungen
globales Konzeptuelles Schema
Beschreibt die Datenbank im Allgemeinen. Also wie die Datenbank für den User aussieht.
Allokationsschema
Definiert welche Daten wo/auf welchem Server gespeichert werden.
Partitionierung
Definiert wie die Daten aufgeteilt werden können.
Allokation
wo die Daten physikalisch abgelegt werden.
Replikation
das mehrfache Speichern der Daten auf verschiedenen Knoten.

Fragmentierungen

Horizontale Fragmentierung
Speichert Zeilen welche etwa einem Deutschen Benutzer gehören näher an Deutschland. Dies erhöt die Perfomance.
Vertikale Fragmentierung
Dabei werden etwa Spalten welche sensible Informationen enthalten in speziell gesicherten Datacenter gespeichert. Dadurch können nicht sensible und sensible Daten voneinander getrennt werden.
Gemischte Fragmentierung
Dabei werden die Daten zuerst horizontal fragmentiert um die Leistung zu optimieren und anschliessend vertikal um die Sicherheit zu gewährleisten.

NoSQL

Werden benötigt weil die Grenzen zwischen den Daten, Dateien nicht immer klar getrennt sind. Zudem braucht es für spezifische Anwendung je nach dem optimiertere Datenbanksysteme. Etwa Big Data.

CAP Theorem

  • C onsistency
  • A vailability
  • P artition tolerance

Eine Datenbank kann maximal 2 der Eigenschaften unterstützen.

BASE

  • B asically
  • A vailable
  • S oft State
  • E ventual Consistency

Bedeutet das sich die DB dann zu einem gewissen Zeitpunkt wieder synchronisiert hat aber halt nicht gerade sofort.

Glossary

<>
Bascically Available, Soft State, Eventual Consistency
<>
Consistency, Availablity, Partition tolerance
<>
Data Control Language grant, revoke, commit
<>
Data Definition Language create table, create view, etc
<>
Data Manipulation Language select, insert, update
<>
Decision Support System
<>
Extraction Transformation and Loading
<>
Not only SQL
<>
Online Analytical Processing