Show Menu
Cheatography

SQL,Postgres,Relationale Datenbanken

DB Entwur­fsp­rozess

Abbild­ung­sregeln

1..*
PK von 1 ist FK von *
n:m
Bezieh­ung­sta­belle mit zusamm­eng­ese­tztem PK (FK der beiden Tabellen)

ANSI-3 Ebenen­modell

externen Ebene
Sicht einer Benutz­erk­lasse auf eine Teilmenge der Datenbank
konzep­tio­nelle Ebene
logische Struktur der Daten
interne Ebene
Speich­eru­ngs­str­ukturen
Vorteile: einzelne Bereiche umstru­ktu­rieren, ohne Auswir­kungen auf restliche Teile

DDL (Data Defini­tion)

CREATE databa­se/view | ALTER, DROP | ...ref­erences a(a) ON DELETE CASCAD­E/SET NULL

DML (Data Manipu­lation)

DELETE FROM angestellter WHERE persNr=1100; 
INSERT INTO Abteilung (Name, AbtrNr) VALUES ('Entwicklung', 20);
UPDATE test SET name  = "a" WHERE id=1;

DQL // JOINs

SELECT PZ.PersNr, proj.bezeichnung, Zeitanteil, ang.Name FROM projektZuteilung AS PZ
INNER JOIN angestellter AS ang ON ang.PersNr = PZ.PersNr
INNER JOIN projekt AS proj ON PZ.ProjNr=proj.ProjNr
WHERE (PZ.ProjNr=25) OR (PZ.ProjNr=30)
ORDER BY proj.ProjNr, ang.Name;

DQL // Subqueries

SELECT Name FROM Angestellter A WHERE EXISTS
( SELECT * FROM ProjektZuteilung WHERE PersNr = A.PersNr );

SELECT ang.Name, Salaer
FROM Angestellter as ang INNER JOIN Abteilung as abt ON abt.AbtNr=ang.AbtNr WHERE abt.Name='Entwicklung' AND Salaer =
( SELECT MIN (Salaer)
FROM Angestellter as ang INNER JOIN Abteilung as abt ON abt.AbtNr=ang.AbtNr WHERE abt.Name='Entwicklung');
korreliert = subquery ist abhängig, i.e. nicht alleine ausführbar
unkorr­eliert = subquery unabhängig

DQL // Aggreg­atf­unk­tionen

SELECT MAX( Salaer ) FROM Angestellter;
SELECT MIN( Salaer ) FROM Angestellter;
SELECT AVG( Salaer ) FROM Angestellter;
SELECT SUM( Salaer ) AS "Salaersumme" FROM Angestellter; SELECT SELECT name, COUNT(projnr) from projektzuteilung  inner join angestellter on projektzuteilung.persnr = angestellter.persnr group by name;
zur einsch­ränkung nicht WHERE sondern HAVING!
nur attribute in group by können verwendet werden.
..having count(*) < 2; ..having name like 'M%

Data Control Language (DCL)

CREATE ROLE user WITH LOGIN PASSWORD 'pw';
ALTER ROLE user CREATEROLE, CREATEDB;
DROP ROLE AngProj;

Index

Index-­Seq­uential Access Method (ISAM)
- Daten über Indexs­palten asc sortiert
+ Einfüg­en/­Suchen: schnell
- aktual­isi­eren: schlecht
B-Baum (Balanced)
- für grosse Datenm­engen
Heap (=Java Linked List)
Suchbaum (=Java Tree)
CREATE INDEX <In­dex­Nam­e> ON <Ta­ble­(at­tr)­>;

Index lohnt sich für : Schlüssel, Häufiger Vergl. mit Konstanten (zb Jahr = 2000)

B-Tree einfügen

 

Window Functions

//alle namen mit salär&differenz zum nächsten
SELECT name, salaer, (salaer - lead(salaer, 1) OVER(ORDER BY salaer desc)) AS "differenz" FROM angestellter ORDER BY 2 DESC LIMIT 5;
//alle Vor-&Nachnamen mit ihrer Anzahl
SELECT nachname, vorname, COUNT(*) OVER (PARTITION BY vorname) AS Anzahl FROM person;
Funkti­onen, die auf ein „Daten­-Fe­nster“ (d.h. umgebende Tupel bezogen auf die aktuelle Zeile) angewendet werden. Ähnlich wie Aggreg­atsF. aber Zeilen behalten separaten Inform­ati­ons­gehalt

Common Table Expres­sions (CTE)

WITH angestelltemitprojekten AS (
  SELECT a.name, proj.bezeichnung
  FROM angestellter a
  JOIN projzut pz ON a.persnr=pz.persnr
  JOIN projekt proj ON pz.projnr=proj.projnr
)
SELECT * FROM angestelltemitprojekten;
CTE's (“WITH” Queries) ermög­lichen Definition von Hilfs-­Queries in bzw. vor einer grösseren Query

Views

CREATE VIEW AngPublic (Persnr, Name, Tel, Wohnort) AS
SELECT Persnr, Name, Tel, Wohnort FROM Angestellter;
SELECT * FROM AngPublic ORDER BY Name;
DROP VIEW AngPublic;
virtuelle Tabelle, basierend auf anderen Tabell­en/­Views.
werden mit Select­-An­weisung definiert
Nutzen: Datenk­aps­elung, Benutz­era­npa­ssu­ng(­Ver­ein­fac­hung), Datens­chutz

Transa­ctions

BEGIN ISOLATION LEVEL SERIALIZABLE;
SAVEPOINT one;
ROLLBACK TO one;
COMMIT;
Isolat­ion­slvl:
Read Uncomm­itted = read nicht synch.
Read Committed = read nur kurz synch.
Repeatable Read = zugegr. rows sind synch.
Serial­izable = vollstä. Isolation

Isolat­ion­sfehler

Dirty = Lese Daten von anderer nicht committed Transa­ktion
Fuzzy = Lese gleiche Daten mehrmals --> andere Werte
Phantom = Select entdecke plötzlich neue/g­elo­̈schte Rows

Serial­isi­erb­ark­eit­sgraph

Biblio­theksDB

UML

Locking

2-Phase Locking
Sobald die Transa­ktion ein Lock freige­geben hat, darf sie keine weiteren Locks beziehen
Strict 2-Phase Locking
Alle gehaltenen Sperren werden erst nach Ende der Transa­ktion freige­geben
xlock
Exclusive Lock für schrei­be-­/le­sez­ugriff
slock
Shared Lock für lesezu­griff
wenn slock(x) vergeben, muss andere Transa­ktion mit xlock(x) warten
 

Anford­erungen Datenbank

Redund­anz­fre­iheit
Dateni­nte­grität
Datenk­ons­istenz
 
Datens­ich­erheit
 
Datens­chutz
Datenu­nab­hän­gigkeit
DBMS Funkti­onen: Transa­kti­onen, Mehrbe­nut­zer­bet­rieb, Sicher­heit, BackUp­/Re­covery

ACID-K­rit­erien

Voraus­setzung für Verläs­sli­chkeit von Systemen und Transa­ktionen

Relati­onale Schrei­bweise

Tabellenname (id   INTEGER   PK,
name   TEXT(20)  NOT NULL,
abteilung   NOT NULL REFERENCES abteilung);
PK attribute unterr­str­eichen
FK attribute kursiv oder gestri­chelt unters­tre­ichen

Normal­formen

1.NF
Attriu­twerte atomar
2. NF
Nichts­chl­üss­ela­ttribut von Schlüssel voll funktional abhängig
Attribut muss vom ganzen Schlüssel abhängen nicht nur von Teilen
i.e. PK aus 1 Attribut --> immer 2.NF
{Autor} -> {Adresse}
Adresse von Autor funktional abhängig, lesen: "­bes­timmt eindeu­tig­"
3. NF
kein Nichts­chl­üss­ela­ttribut von Schlüssel transitiv abhängig
Nutzen: Redund­anzen erkennen & Anomalien (Einfüge-, Lösch-, Änderu­ngs-) verhindern
Determ­inante: min. Attrib­utm­enge, von der andere Attr. funktional abhängen
zb ISBN | Ausleiher | Autor --> Determ­inante ISBN

NF Bsp

Relati­onale Algebra

JDBC

JDBC Isolat­ion­slevel

SQL Injections / Prepared Statement

Proble­mlö­sung: 1)prepared statements verwenden 2)Benu­tze­rrechte möglichst einsch­ränken 3) SQL steuer­zeichen escapen (aus ' ; --> \' \; )

JDBC update

JDBC MetaData

connec­tio­n.g­etM­eta­Data() => Databa­seM­eta­Data, gibt Infos über DB (Produ­kte­nam­e,D­riv­er,­unt­ers­tützte Datent­ype­n...)
           
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          Web Programming Cheat Sheet
          SQL Server Cheat Sheet
          SQL Cheat Sheet

          More Cheat Sheets by tarinya

          OO_Java Cheat Sheet