Abbildungsregeln1..* | PK von 1 ist FK von * | n:m | Beziehungstabelle mit zusammengesetztem PK (FK der beiden Tabellen) |
ANSI-3 Ebenenmodellexternen Ebene | Sicht einer Benutzerklasse auf eine Teilmenge der Datenbank | konzeptionelle Ebene | logische Struktur der Daten | interne Ebene | Speicherungsstrukturen |
Vorteile: einzelne Bereiche umstrukturieren, ohne Auswirkungen auf restliche Teile
DDL (Data Definition)CREATE database/view | ALTER, DROP | ...references a(a) ON DELETE CASCADE/SET NULL
DML (Data Manipulation)DELETE FROM angestellter WHERE persNr=1100;
INSERT INTO Abteilung (Name, AbtrNr) VALUES ('Entwicklung', 20);
UPDATE test SET name = "a" WHERE id=1;
|
DQL // JOINsSELECT 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 // SubqueriesSELECT 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
unkorreliert = subquery unabhängig
DQL // AggregatfunktionenSELECT 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 einschrä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;
|
IndexIndex-Sequential Access Method (ISAM)
- Daten über Indexspalten asc sortiert
+ Einfügen/Suchen: schnell
- aktualisieren: schlecht
B-Baum (Balanced)
- für grosse Datenmengen |
Heap (=Java Linked List)
Suchbaum (=Java Tree)
CREATE INDEX <IndexName> ON <Table(attr)>;
Index lohnt sich für : Schlüssel, Häufiger Vergl. mit Konstanten (zb Jahr = 2000)
| | 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;
|
Funktionen, die auf ein „Daten-Fenster“ (d.h. umgebende Tupel bezogen auf die aktuelle Zeile) angewendet werden. Ähnlich wie AggregatsF. aber Zeilen behalten separaten Informationsgehalt
Common Table Expressions (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öglichen Definition von Hilfs-Queries in bzw. vor einer grösseren Query
ViewsCREATE 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 Tabellen/Views.
werden mit Select-Anweisung definiert
Nutzen: Datenkapselung, Benutzeranpassung(Vereinfachung), Datenschutz
TransactionsBEGIN ISOLATION LEVEL SERIALIZABLE;
SAVEPOINT one;
ROLLBACK TO one;
COMMIT;
|
Isolationslvl:
Read Uncommitted = read nicht synch.
Read Committed = read nur kurz synch.
Repeatable Read = zugegr. rows sind synch.
Serializable = vollstä. Isolation
IsolationsfehlerDirty = Lese Daten von anderer nicht committed Transaktion
Fuzzy = Lese gleiche Daten mehrmals --> andere Werte
Phantom = Select entdecke plötzlich neue/gelöschte Rows
Serialisierbarkeitsgraph
Locking2-Phase Locking | Sobald die Transaktion ein Lock freigegeben hat, darf sie keine weiteren Locks beziehen | Strict 2-Phase Locking | Alle gehaltenen Sperren werden erst nach Ende der Transaktion freigegeben | xlock | Exclusive Lock für schreibe-/lesezugriff | slock | Shared Lock für lesezugriff | wenn slock(x) vergeben, muss andere Transaktion mit xlock(x) warten |
| | Anforderungen DatenbankRedundanzfreiheit | Datenintegrität | Datenkonsistenz | | Datensicherheit | | Datenschutz | Datenunabhängigkeit |
DBMS Funktionen: Transaktionen, Mehrbenutzerbetrieb, Sicherheit, BackUp/Recovery
ACID-KriterienVoraussetzung für Verlässlichkeit von Systemen und Transaktionen
Relationale SchreibweiseTabellenname (id INTEGER PK,
name TEXT(20) NOT NULL,
abteilung NOT NULL REFERENCES abteilung);
|
PK attribute unterrstreichen
FK attribute kursiv oder gestrichelt unterstreichen
Normalformen1.NF | Attriutwerte atomar | 2. NF | Nichtschlüsselattribut 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: "bestimmt eindeutig" | 3. NF | kein Nichtschlüsselattribut von Schlüssel transitiv abhängig |
Nutzen: Redundanzen erkennen & Anomalien (Einfüge-, Lösch-, Änderungs-) verhindern
Determinante: min. Attributmenge, von der andere Attr. funktional abhängen
zb ISBN | Ausleiher | Autor --> Determinante ISBN
SQL Injections / Prepared StatementProblemlösung: 1)prepared statements verwenden 2)Benutzerrechte möglichst einschränken 3) SQL steuerzeichen escapen (aus ' ; --> \' \; )
JDBC MetaDataconnection.getMetaData() => DatabaseMetaData, gibt Infos über DB (Produktename,Driver,unterstützte Datentypen...)
|
Created By
Metadata
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets
More Cheat Sheets by tarinya