Datenbanksysteme 1

Term	Definition
Assoziation	Eine Beziehung zwischen zwei oder mehr Klassen, die zeigt, wie Objekte dieser Klassen miteinander verbunden sind.
Aggregation	Eine spezielle Form der Assoziation, die eine “Teil-von” Beziehung darstellt, bei der die Lebensdauer des Teils unabhängig von dem Ganzen ist.
Komposition	Eine stärkere Form der Aggregation, bei der die Lebensdauer des Teils von der des Ganzen abhängt; wenn das Ganze gelöscht wird, wird auch das Teil gelöscht.
Vererbung	Ein Mechanismus, durch den eine Klasse Eigenschaften und Verhalten (Methoden) von einer anderen Klasse erben kann, was die Wiederverwendbarkeit fördert.
Multiplizität 1 | 0..1 | 1..*	Gibt an, wie viele Objekte einer Klasse mit einem Objekt einer anderen Klasse in Beziehung stehen können (z. B. 1:1, 1:n, n:m).
Notiz	Eine Anmerkung oder ein Kommentar innerhalb eines UML-Diagramms, die zusätzliche Informationen oder Erklärungen zu Elementen oder Beziehungen bereitstellt.

In dieser Phase wird eine abstrakte Sicht auf die Datenbank erstellt, die die Anforderungen der Benutzer erfasst. Es werden Entitäten, Attribute und deren Beziehungen definiert, ohne sich um technische Details zu kümmern.

Hier wird das konzeptionelle Modell in eine spezifische Datenbankstruktur übersetzt, ohne sich um physische Aspekte zu kümmern. Es wird festgelegt, wie Daten organisiert sind (z. B. in Tabellen) und welche Regeln für Integrität bestehen.

In dieser Phase werden die spezifischen Implementierungsdetails festgelegt, einschließlich der Speicherstruktur und der Indexierung. Das Modell wird optimiert, um Leistung, Effizienz und Datenzugriff zu verbessern.

Tabelle nicht in normalisierter Form
1. Normalform
Attributwerte sind atomar
2. Normalform
Nichtschlüsselattribute voll vom Schlüssel abhängig
3. Normalform
Keine transitiven Abhängigkeiten
Boyce-Codd-Normalform
Nur Abhängigkeiten vom Schlüssel
(4. Normalform)
(5. Normalform)

• Attributwerte sind atomar

• Nichtschlüsselatribute voll vom Schlüssel abhängig
• Besteht der PK nur aus einem einzigen Attribut (ist er also nicht zusammengesetzt), so bereits automatisch die 2. NF gegeben.
• Eine Tabelle ist NICHT in 2. NF, wenn sie einen zusammengesetzten Schlüssel hat und mind. ein Nichtschlüsselattribut von nur einem Teil des Schlüssels funktional abhängig ist.

• Keine transitiven Abhängigkeiten

• Keine Abhängigkeiten vom Schlüssel

https://users.informatik.uni-halle.de/~brass/db23/slides/dd_relal.pdf

Postgres Dokumentation

Definiert Befehle für das Kreieren, Löschen und Modifizieren von Tabellendefinitionen, von externen Sichten (Views), von Constraints und von einigen anderen Datenbankobjekten (Index, Cluster) für die Optimierung der Abbildung von der logischen auf die interne Ebene.

Zusätzlich kann IF (NOT) EXISTSIF (NOT) EXISTS hinzugefügt werden oder der CREATECREATE mit CREATE OR REPLACECREATE OR REPLACE ausgetauscht werden. Beim DROPDROP Befehl kann CASCADECASCADE hinzugefügt werden.

CREATE SCHEMA s;    DROP SCHEMA s;
CREATE DOMAIN d;    DROP DOMAIN d;
CREATE TABLE t;     DROP TABLE t;     ALTER TABLE t;  TRUNCATE TABLE t;
CREATE VIEW v;      DROP VIEW v;
CREATE INDEX i;     DROP INDEX i;
CREATE DATABASE db; DROP DATABASE db;

CREATE SCHEMA s;    DROP SCHEMA s;
CREATE DOMAIN d;    DROP DOMAIN d;
CREATE TABLE t;     DROP TABLE t;     ALTER TABLE t;  TRUNCATE TABLE t;
CREATE VIEW v;      DROP VIEW v;
CREATE INDEX i;     DROP INDEX i;
CREATE DATABASE db; DROP DATABASE db;

CREATE TABLE schema_name.table_name (
  counter SERIAL NOT NULL,
  important INT NOT NULL,
  field INT NOT NULL UNIQUE,
  very_long_name VARCHAR(666) DEFAULT "Based PostgreSQL user",
  age INT CHECK (age > 18),
  reference INT NOT NULL,
  someday DATE DEFAULT SYSDATE,
  another_ref INT FOREIGN KEY REFERENCES other_table.id ON UPDATE RESTRICT,
  PRIMARY KEY (counter, important),
  FOREIGN KEY (reference) REFERENCES third_table.id ON DELETE CASCADE,
  CHECK (field BETWEEN 69 AND 420)
)

CREATE TABLE schema_name.table_name (
  counter SERIAL NOT NULL,
  important INT NOT NULL,
  field INT NOT NULL UNIQUE,
  very_long_name VARCHAR(666) DEFAULT "Based PostgreSQL user",
  age INT CHECK (age > 18),
  reference INT NOT NULL,
  someday DATE DEFAULT SYSDATE,
  another_ref INT FOREIGN KEY REFERENCES other_table.id ON UPDATE RESTRICT,
  PRIMARY KEY (counter, important),
  FOREIGN KEY (reference) REFERENCES third_table.id ON DELETE CASCADE,
  CHECK (field BETWEEN 69 AND 420)
)

Postgres Dokumentation
Sinnvolle Konversionen und Rundungen werden implizit durchgeführt.

Explizit

CAST(5 AS float8) = 5::float8
SELECT 'ABCDEFG'::NUMERIC;                   -- error
SELECT SAFE_CAST('ABCDEFG' AS NUMERIC);      -- NULL

CAST(5 AS float8) = 5::float8
SELECT 'ABCDEFG'::NUMERIC;                   -- error
SELECT SAFE_CAST('ABCDEFG' AS NUMERIC);      -- NULL

Implizit

SELECT 5 + 3.2;                              -- cast to 5.0 (numeric)
SELECT 'Number ' || 42;                      -- cast to '42'
SELECT true AND 1;                           -- treated as true
SELECT CURRENT_TIMESTAMP + INTERVAL '1 day'; -- cast to DATE
SELECT '100'::text + 1;                      -- cast to 100

SELECT 5 + 3.2;                              -- cast to 5.0 (numeric)
SELECT 'Number ' || 42;                      -- cast to '42'
SELECT true AND 1;                           -- treated as true
SELECT CURRENT_TIMESTAMP + INTERVAL '1 day'; -- cast to DATE
SELECT '100'::text + 1;                      -- cast to 100

Postgres Dokumentation

Postgres Dokumentation

Sollte bei foreign Keys bevorzugt werden, da somit rekursive Referenzen einfacher umgesetzt werden können.

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  CHECK (counter < 1337);
ALTER TABLE other_table
  ADD CONSTRAINT fk_that
  FOREIGN KEY (that) REFERENCES table_name (counter);

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  CHECK (counter < 1337);
ALTER TABLE other_table
  ADD CONSTRAINT fk_that
  FOREIGN KEY (that) REFERENCES table_name (counter);

Der Fremdschlüssel in einer abhängigen Tabelle muss als Wert entweder einen aktuellen Wert des Schlüssels der referenzierten Tabelle (1:n) oder NULL (1c:n) aufweisen. Diese referentielle Integritätsbedingung kann bei Einfüge-, Lösch- und Änderungsoperationen verletzt werden und sollte daher vom DBMS überprüft werden.

Trigger:

ON UPDATE
ON DELETE

ON UPDATE
ON DELETE

Aktion:

CASCADE
RESTRICT
SET DEFAULT
SET NULL

CASCADE
RESTRICT
SET DEFAULT
SET NULL

Siehe “Beispiel CREATE TABLE” (Seite 1) .

Blogpost Stefan Keller

Use the index, luke

Ein Index ist eine Hilfsdatenstruktur, die zu einem gegebenen Attributwert die Adressen der Tupel mit diesem Attributwert liefert.

Zwei Datenstrukturen:

• Data Pages (Heaps)
• Suchbaum

Zugriff Baum:

• Durchwandern des Baumes

• Verfolgen der Blattknoten-Kette

• Tabellenzugriff (falls nötig)

• Primär-Index vs Sekundär-Index

• Sekundärer Index vs Integrierter (Clustered) Index

CREATE INDEX mytable_col_idx ON mytable (col);
SELECT id,nr,txt FROM test;
CREATE INDEX magic_idx ON test (nr,id) INCLUDE txt;
CREATE EXTENSION btree_gist;
CREATE INDEX mytable_col_idx2 ON mytable (col) USING gist (col);
DROP INDEX mytable_col_idx;
CREATE INDEX mytable_col_idx ON mytable (UPPER(col));

CREATE INDEX mytable_col_idx ON mytable (col);
SELECT id,nr,txt FROM test;
CREATE INDEX magic_idx ON test (nr,id) INCLUDE txt;
CREATE EXTENSION btree_gist;
CREATE INDEX mytable_col_idx2 ON mytable (col) USING gist (col);
DROP INDEX mytable_col_idx;
CREATE INDEX mytable_col_idx ON mytable (UPPER(col));

Postgres Dokumentation

Ein Schema ist ein Menge von DB-Objekten, welche zu einer logischen Datenbank gehören. Ein Schema kann folgende Objekte beinhalten: Tabellen, Sichten, Zusicherungen (Assertions), Berechtigungen etc.

Postgres Dokumentation

Postgres Dokumentation

<select> := [ 'WITH' [ 'RECURSIVE' ] <with_query> [, ...] ]
'SELECT' [ 'ALL' | 'DISTINCT' [ 'ON' ( <expression> [, ...] ) ] ]
    [ { * | <expression> [ [ 'AS' ] <output_name> ] } [, ...] ]
    [ 'FROM' <from_item> [, ...] ]
    [ 'WHERE' <condition> ]
    [ 'GROUP BY' [ 'ALL' | 'DISTINCT' ] <grouping_element> [, ...] ]
    [ 'HAVING' <condition> ]
    [ 'WINDOW' <window_name> 'AS' ( <window_definition> ) [, ...] ]
    [ { 'UNION' | 'INTERSECT' | 'EXCEPT' } [ 'ALL' | 'DISTINCT' ] <select> ]
    [ 'ORDER BY' <expression> [ 'ASC' | 'DESC' | 'USING' <operator> ] [ 'NULLS' { 'FIRST' | 'LAST' } ] [, ...] ]
    [ 'LIMIT' { <count> | 'ALL' } ]
    [ 'OFFSET' <start> [ 'ROW' | 'ROWS' ] ]
<from_item> := <table_name> [ * ] [ [ 'AS' ] <alias> [ ( <column_alias> [, ...] ) ] ]
    [ 'LATERAL' ] ( <select> ) [ [ 'AS' ] <alias> [ ( <column_alias> [, ...] ) ] ]
    <with_query_name> [ [ 'AS' ] <alias> [ ( <column_alias> [, ...] ) ] ]
    <from_item> <join_type> <from_item> { 'ON' <join_condition> | 'USING' ( <join_column> [, ...] ) [ 'AS' <join_using_alias> ] }
    <from_item> 'NATURAL' <join_type> <from_item>
    <from_item> 'CROSS JOIN' <from_item>
<with_query> := <with_query_name> [ ( <column_name> [, ...] ) ] 'AS' ( <select> | <values> | <insert> | <update> | <delete> | <merge> )
        [ 'USING' <cycle_path_col_name> ]

<select> := [ 'WITH' [ 'RECURSIVE' ] <with_query> [, ...] ]
'SELECT' [ 'ALL' | 'DISTINCT' [ 'ON' ( <expression> [, ...] ) ] ]
    [ { * | <expression> [ [ 'AS' ] <output_name> ] } [, ...] ]
    [ 'FROM' <from_item> [, ...] ]
    [ 'WHERE' <condition> ]
    [ 'GROUP BY' [ 'ALL' | 'DISTINCT' ] <grouping_element> [, ...] ]
    [ 'HAVING' <condition> ]
    [ 'WINDOW' <window_name> 'AS' ( <window_definition> ) [, ...] ]
    [ { 'UNION' | 'INTERSECT' | 'EXCEPT' } [ 'ALL' | 'DISTINCT' ] <select> ]
    [ 'ORDER BY' <expression> [ 'ASC' | 'DESC' | 'USING' <operator> ] [ 'NULLS' { 'FIRST' | 'LAST' } ] [, ...] ]
    [ 'LIMIT' { <count> | 'ALL' } ]
    [ 'OFFSET' <start> [ 'ROW' | 'ROWS' ] ]
<from_item> := <table_name> [ * ] [ [ 'AS' ] <alias> [ ( <column_alias> [, ...] ) ] ]
    [ 'LATERAL' ] ( <select> ) [ [ 'AS' ] <alias> [ ( <column_alias> [, ...] ) ] ]
    <with_query_name> [ [ 'AS' ] <alias> [ ( <column_alias> [, ...] ) ] ]
    <from_item> <join_type> <from_item> { 'ON' <join_condition> | 'USING' ( <join_column> [, ...] ) [ 'AS' <join_using_alias> ] }
    <from_item> 'NATURAL' <join_type> <from_item>
    <from_item> 'CROSS JOIN' <from_item>
<with_query> := <with_query_name> [ ( <column_name> [, ...] ) ] 'AS' ( <select> | <values> | <insert> | <update> | <delete> | <merge> )
        [ 'USING' <cycle_path_col_name> ]

Postgres Dokumentation

INSERT INTO table_name (important, field, reference) VALUES (3, 99, 7);
INSERT INTO other_table VALUES (20, "Goodbye world.") RETURNING counter;

INSERT INTO table_name (important, field, reference) VALUES (3, 99, 7);
INSERT INTO other_table VALUES (20, "Goodbye world.") RETURNING counter;

INSERT INTO combined_table
  SELECT t.age, t.very_long_name FROM table_name AS t
  INNER JOIN other_table AS o ON t.reference = o.id;

INSERT INTO combined_table
  SELECT t.age, t.very_long_name FROM table_name AS t
  INNER JOIN other_table AS o ON t.reference = o.id;

Postgres Dokumentation

Mit dem Update-Befehl können bestehende Tupel in der Datenbank modifiziert werden.

UPDATE table_name
  SET field = field * 2
  WHERE field IN (71,73,74);

UPDATE table_name
  SET field = field * 2
  WHERE field IN (71,73,74);

Postgres Dokumentation

DELETE FROM table_name
  WHERE field > 300;

DELETE FROM table_name
  WHERE field > 300;

Postgres Dokumentation

SELECT field FROM table_name;
SELECT important, CONCAT(very_long_name, age) AS personal_info
  FROM table_name
  WHERE counter > 21
  GROUP BY personal_info
  ORDER BY age, counter DESC
  LIMIT 77;

SELECT field FROM table_name;
SELECT important, CONCAT(very_long_name, age) AS personal_info
  FROM table_name
  WHERE counter > 21
  GROUP BY personal_info
  ORDER BY age, counter DESC
  LIMIT 77;

Postgres Dokumentation

BETWEEN ... AND ...
IN (..., ...)
LIKE '___%'         -- 3 chars or more
LIKE '%asd'         -- ending with "asd"
AND
OR
IS (NOT) NULL

BETWEEN ... AND ...
IN (..., ...)
LIKE '___%'         -- 3 chars or more
LIKE '%asd'         -- ending with "asd"
AND
OR
IS (NOT) NULL

Postgres Dokumentation

MAX()
MIN()
AVG()
SUM()
COUNT()

MAX()
MIN()
AVG()
SUM()
COUNT()

Postgres Dokumentation

GROUP BY teilt die Resultattabelle in Gruppen auf, die in der GROUP BY - Spalte gleiche Werte aufweisen. NULL-Werte einer GROUP-BY Spalte werden als separate Gruppe behandelt.

Die HAVING Klausel kann nur nach einer GROUP-BY Klausel stehen. Sie erlaubt die Auswahl von Zeilen, die durch die Anwendung der GROUP BY Bedingung entstehen (analog der WHERE-Klausel). Die Bedingung der HAVING-Klausel muss mit einer Funktion beginnen, welche in der SELECT-Klausel vorkommen muss.

SELECT very_long_name, age, COUNT(*)
  FROM table_name
  GROUP BY age, very_long_name
  HAVING COUNT(*) > 2;

SELECT very_long_name, age, COUNT(*)
  FROM table_name
  GROUP BY age, very_long_name
  HAVING COUNT(*) > 2;

Postgres Dokumentation , Visuelle Beispiele Atlassian , Visuelle Beispiele CodeCademy

Die Join-Operation verbindet Tabellen über Spalten mit dem gleichen Datentyp. Damit können Beziehungen zwischen den Tabellen (realisiert über Fremdschlüssel) aufgelöst werden.

Gibt das kartesische Produkt der beiden Tabellen zurück, d.h. jede Zeile aus der ersten Tabelle wird mit jeder Zeile aus der zweiten Tabelle kombiniert.

SELECT a.*, b.*
  FROM a
  CROSS JOIN b;

SELECT a.*, b.*
  FROM a
  CROSS JOIN b;

Gibt nur die Zeilen zurück, die in beiden Tabellen übereinstimmen.

SELECT a.*, b.*
  FROM a
  INNER JOIN b ON a.id = b.id;

SELECT a.*, b.*
  FROM a
  INNER JOIN b ON a.id = b.id;

Gibt alle Zeilen aus der linken Tabelle zurück und die übereinstimmenden Zeilen aus der rechten Tabelle. Wenn es keine Übereinstimmung gibt, werden NULL-Werte für die rechte Tabelle zurückgegeben.

Wie left join, nur umgekehrt.

Gibt alle Zeilen zurück, die in einer der beiden Tabellen vorhanden sind. Wenn es keine Übereinstimmung gibt, werden NULL-Werte für die Tabelle zurückgegeben, in der keine Übereinstimmung gefunden wurde.

Ein spezieller Typ von Join, der es ermöglicht, eine Unterabfrage zu verwenden, die auf Zeilen der äußeren Abfrage basiert. Dies ist nützlich für komplexe Abfragen, bei denen die Ergebnisse einer Unterabfrage von den Ergebnissen der äußeren Abfrage abhängen.

SELECT a.*, b.*
  FROM table_a AS a,
    JOIN LATERAL (SELECT * FROM table_b WHERE table_b.id = a.id) AS b;

SELECT a.*, b.*
  FROM table_a AS a,
    JOIN LATERAL (SELECT * FROM table_b WHERE table_b.id = a.id) AS b;

Postgres Dokumentation

Jede dieser Operationen kann mit ALLALL postfixed werden, um die Duplikate beizubehalten.

Kombiniert die Ergebnisse zweier oder mehrerer SELECT-Abfragen und gibt alle Zeilen zurück, ausschließlich Duplikate.

SELECT r1, r2
FROM A
UNION ALL
SELECT r1, r2
FROM B;

SELECT r1, r2
FROM A
UNION ALL
SELECT r1, r2
FROM B;

Gibt die gemeinsamen Zeilen aus zwei SELECT-Abfragen zurück. Das Ergebnis enthält nur die Zeilen, die in beiden Abfragen vorhanden sind.

Gibt die Zeilen aus der ersten SELECT-Abfrage zurück, die nicht in der zweiten SELECT-Abfrage vorhanden sind.

Postgres Dokumentation

Eine Unterabfrage darf nur einen Spaltennamen oder einen Ausdruck und keine ORDER BY - Klausel enthalten.

Verschachteltes Beispiel:

SELECT name
  FROM mitarbeiter
  WHERE id IN
  (SELECT mitarbeiterNr
    FROM projektZuteilung
    WHERE projNr IN
    (SELECT projNr
      FROM projekt INNER JOIN mitarbeiter
      ON projekt.projLeiter = mitarbeiter.id
      WHERE name = 'Kropotkin, Peter'
    )
  );

SELECT name
  FROM mitarbeiter
  WHERE id IN
  (SELECT mitarbeiterNr
    FROM projektZuteilung
    WHERE projNr IN
    (SELECT projNr
      FROM projekt INNER JOIN mitarbeiter
      ON projekt.projLeiter = mitarbeiter.id
      WHERE name = 'Kropotkin, Peter'
    )
  );

Vergleicht den Wert einer Spalte mit dem Ergebnis einer Unterabfrage. Unterabfrage darf nur einen Wert zurückliefern.

SELECT *
  FROM mitarbeiter
  WHERE gehalt = (SELECT MAX(gehalt) FROM mitarbeiter);

SELECT *
  FROM mitarbeiter
  WHERE gehalt = (SELECT MAX(gehalt) FROM mitarbeiter);

Überprüft, ob der Wert einer Spalte in der Menge der Ergebnisse einer Unterabfrage enthalten ist.

SELECT *
  FROM bestellungen
  WHERE kundenID IN (SELECT id FROM kunden WHERE land = 'CH');

SELECT *
  FROM bestellungen
  WHERE kundenID IN (SELECT id FROM kunden WHERE land = 'CH');

Überprüft, ob eine Unterabfrage mindestens eine Zeile zurückgibt.

SELECT name
  FROM mitarbeiter
  WHERE EXISTS
  (SELECT * FROM projektZuteilung WHERE mitarbeiterNr = mitarbeiter.id);

SELECT name
  FROM mitarbeiter
  WHERE EXISTS
  (SELECT * FROM projektZuteilung WHERE mitarbeiterNr = mitarbeiter.id);

Überprüft, ob der Wert einer Spalte irgendeinen Wert in der Menge der Ergebnisse einer Unterabfrage erfüllt.

SELECT *
  FROM mitarbeiter
  WHERE gehalt > ANY (SELECT gehalt FROM manager);

SELECT *
  FROM mitarbeiter
  WHERE gehalt > ANY (SELECT gehalt FROM manager);

Überprüft, ob der Wert einer Spalte alle Werte in der Menge der Ergebnisse einer Unterabfrage erfüllt.

Postgres Dokumentation

Window Functions sind spezielle SQL-Funktionen, die Berechnungen über eine Menge von Zeilen durchführen, die mit der aktuellen Zeile in Beziehung stehen.

SELECT
  mitarbeiter,
  gehalt,
  RANK() OVER (ORDER BY gehalt DESC) as gehaltsrang
FROM
  mitarbeitertabelle;
SELECT
  persnr,
  name,
  LAG(salaer, 1) OVER (
    PARTITION BY abtnr
  ORDER BY
    salaer DESC
  ) - salaer AS differenz
FROM
  angestellter;

SELECT
  mitarbeiter,
  gehalt,
  RANK() OVER (ORDER BY gehalt DESC) as gehaltsrang
FROM
  mitarbeitertabelle;
SELECT
  persnr,
  name,
  LAG(salaer, 1) OVER (
    PARTITION BY abtnr
  ORDER BY
    salaer DESC
  ) - salaer AS differenz
FROM
  angestellter;

Postgres Dokumentation

Eine View ist eine virtuelle Tabelle, welche auf eine oder mehrere Tabellen oder Views abgebildet wird. Die Abbildung wird mit einer Select-Anweisung definiert. Die Daten der View werden erst zur Ausführungszeit aus den darunter liegenden Tabellendaten hergeleitet.

Die Views erlauben es, dass verschiedene Benutzer die Daten unterschiedlich strukturiert sehen. Mit Views kann die Formulierung von Abfragen, die sich über mehrere Tabellen erstrecken, verein- facht werden. Views erlauben einen wirksamen Zugriffsschutz, da es möglich ist, Spalten der da- runterliegenden Tabellen auszublenden.

CREATE VIEW mitarbeiter_public (id, name, tel) AS
  SELECT id, name, tel
  FROM mitarbeiter;

CREATE VIEW mitarbeiter_public (id, name, tel) AS
  SELECT id, name, tel
  FROM mitarbeiter;

Hilfs-Query in einer WITH-Klausel (Temporäre Tabellen während des Statements). Query-Name immer im FROM.

WITH queryName AS ( SELECT * FROM myTable )
SELECT * FROM queryName;
WITH tmptable(name, bezeichnung, zeitanteil) AS (
  SELECT name, bezeichnung, zeitanteil
  FROM angestellter a
  JOIN projektzuteilung pz ON pz.persnr=a.persnr
  JOIN projekt p ON p.projnr=pz.projnr
)
SELECT name AS "Mitarb.", bezeichnung AS "Projekt", zeitanteil AS "Zeit" FROM tmptable;

WITH queryName AS ( SELECT * FROM myTable )
SELECT * FROM queryName;
WITH tmptable(name, bezeichnung, zeitanteil) AS (
  SELECT name, bezeichnung, zeitanteil
  FROM angestellter a
  JOIN projektzuteilung pz ON pz.persnr=a.persnr
  JOIN projekt p ON p.projnr=pz.projnr
)
SELECT name AS "Mitarb.", bezeichnung AS "Projekt", zeitanteil AS "Zeit" FROM tmptable;

Ein rekursives CTE referenziert sich selbst, um Teilmengen der Daten zurückzugeben, bis alle Ergebnisse erhalten sind.

WITH RECURSIVE untergebene(persnr, name, chef) AS (
  SELECT A.persnr, A.name, A.chef FROM angestellter A
  WHERE A.chef = 1010 UNION ALL -- recursive term
    SELECT A.persnr, A.name, A.chef FROM angestellter A
  INNER JOIN untergebene B ON B.persnr = A.chef
)
SELECT * FROM untergebene ORDER BY chef, persnr;

WITH RECURSIVE untergebene(persnr, name, chef) AS (
  SELECT A.persnr, A.name, A.chef FROM angestellter A
  WHERE A.chef = 1010 UNION ALL -- recursive term
    SELECT A.persnr, A.name, A.chef FROM angestellter A
  INNER JOIN untergebene B ON B.persnr = A.chef
)
SELECT * FROM untergebene ORDER BY chef, persnr;

CREATE PROCEDURE
EXEC

CREATE PROCEDURE
EXEC

Postgres Dokumentation Postgres Dokumentation

ROLE: Oberbegriff für Benutzer (USER) oder Gruppe (GROUP).

CREATE ROLE user_name
WITH LOGIN PASSWORD 'password';
-- set current user
SET ROLE user_name;
DROP ROLE user_name;

CREATE ROLE user_name
WITH LOGIN PASSWORD 'password';
-- set current user
SET ROLE user_name;
DROP ROLE user_name;

Postgres Dokumentation

GRANT INSERT ON TABLE table_name TO user_name;
REVOKE INSERT ON TABLE table_name TO user_name;

GRANT INSERT ON TABLE table_name TO user_name;
REVOKE INSERT ON TABLE table_name TO user_name;

ALTER ROLE user_name CREATEROLE, CREATEDB, INHERIT;

ALTER ROLE user_name CREATEROLE, CREATEDB, INHERIT;

CREATE ROLE manager;
GRANT SELECT ON table_name TO manager;
GRANT manager TO user_name;

CREATE ROLE manager;
GRANT SELECT ON table_name TO manager;
GRANT manager TO user_name;

-- creating
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'readonlyuser' NOINHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
-- assign permissions to read all newly tables created in the future (by others):
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonlyuser;
-- deleting
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM readonlyuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT ON TABLES FROM readonlyuser;
DROP USER readonlyuser;

-- creating
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'readonlyuser' NOINHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
-- assign permissions to read all newly tables created in the future (by others):
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonlyuser;
-- deleting
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM readonlyuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT ON TABLES FROM readonlyuser;
DROP USER readonlyuser;

Ist das, was oben erklärt wird.

• Check-Constraint auf Tabelle pro Rolle (sozusagen ein zusätzliches WHERE)
• (Tipp: Aufpassen bei TRUNCATE, REFERENCES und VIEWS)

CREATE TABLE exams (
  id SERIAL,
  student_name TEXT,
  grade DECIMAL(2,1),
  added TIMESTAMP DEFAULT current_timestamp,
  teacher_pguser VARCHAR(60) DEFAULT current_user
);
CREATE POLICY policy_teachers_see_own_exams ON exams
FOR ALL
TO PUBLIC
USING (teacher_pguser = current_user);
ALTER TABLE exams
ENABLE ROW LEVEL SECURITY;

CREATE TABLE exams (
  id SERIAL,
  student_name TEXT,
  grade DECIMAL(2,1),
  added TIMESTAMP DEFAULT current_timestamp,
  teacher_pguser VARCHAR(60) DEFAULT current_user
);
CREATE POLICY policy_teachers_see_own_exams ON exams
FOR ALL
TO PUBLIC
USING (teacher_pguser = current_user);
ALTER TABLE exams
ENABLE ROW LEVEL SECURITY;

Postgres Dokumentation

BEGIN;
COMMIT;

BEGIN;
COMMIT;

BEGIN;
ROLLBACK;

BEGIN;
ROLLBACK;

https://pgdash.io/blog/postgres-transactions.html

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Standart-Isolationsstufe in PostgreSQL.
Jede Abfrage sieht nur Daten, die vor Beginn der Abfrage committed wurden. Verhindert “Dirty Reads”

Verhindert “Non-Repeatable Reads”. Verwendet Snapshot-Isolation. Höherer Speicherbedarf durch Snapshots.

Falls die gleiche Spalte in mehreren Transaktionen UPDATEd wird, werden die Transaktionen abgebrochen (ausser die erste).

Postgres Dokumentation

BEGIN;
...
SAVEPOINT savepoint1;
...
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;

BEGIN;
...
SAVEPOINT savepoint1;
...
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;

https://www.youtube.com/watch?v=01MDhIXiXIY