CREATE TABLE Partei (
    ParteiID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL
);

CREATE TABLE Landesliste (
    ListeID INT PRIMARY KEY,
    ParteiID INT NOT NULL,
    Land VARCHAR(255) NOT NULL,
    CONSTRAINT fk_Partei_Liste
        FOREIGN KEY (ParteiID)
        REFERENCES Partei(ParteiID)
);

CREATE TABLE Wahlkreis (
    WahlkreisID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL
);
### Anzahl Wahlberechtigte?

CREATE TABLE Direktkandidat (
    ID INTEGER PRIMARY KEY,
    Vorname VARCHAR(255),
    Nachname VARCHAR(255),
    Partei VARCHAR(255),
    Wahlkreis_ID INTEGER REFERENCES Wahlkreis(ID)
);

CREATE TABLE Wahlbezirk (
    WahlbezirkID INT PRIMARY KEY,
    WahlkreisID INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Wahlberechtigte NUMBER NOT NULL,   ## kam nach Hinweis dazu  
    CONSTRAINT fk_Wahlkreis_Bezirk
        FOREIGN KEY (WahlkreisID)
        REFERENCES Wahlkreis(WahlkreisID)
);

CREATE TABLE Kandidat (
    KandidatID INT PRIMARY KEY,
    Vorname VARCHAR(255) NOT NULL,
    Nachname VARCHAR(255) NOT NULL,
    Geburtsdatum DATE NOT NULL,
  partei_id INT NOT NULL,
  bundesland_id INT NOT NULL,
raus    ListeID INT NOT NULL,
raus    Platzierung INT NOT NULL,
    CONSTRAINT fk_Liste_Kandidat
        FOREIGN KEY (ListeID)
        REFERENCES Landesliste(ListeID)
);

CREATE TABLE Landeslisten (
  id INT PRIMARY KEY,
  partei_id INT NOT NULL,
  bundesland_id INT NOT NULL,
  FOREIGN KEY (partei_id) REFERENCES Parteien(id),
  FOREIGN KEY (bundesland_id) REFERENCES Bundeslaender(id)
);

CREATE TABLE Listenplaetze (
  id INT PRIMARY KEY,
  kandidat_id INT NOT NULL,
  landesliste_id INT NOT NULL,
  position INT NOT NULL,
  FOREIGN KEY (kandidat_id) REFERENCES Kandidaten(id),
  FOREIGN KEY (landesliste_id) REFERENCES Landeslisten(id)
);

CREATE TABLE Wahlberechtigter (
    WahlberechtigterID INT PRIMARY KEY,
    Vorname VARCHAR(255) NOT NULL,
    Nachname VARCHAR(255) NOT NULL,
    Geburtsdatum DATE NOT NULL,
    WahlbezirkID INT NOT NULL,
    CONSTRAINT fk_Wahlbezirk_Waehler
        FOREIGN KEY (WahlbezirkID)
        REFERENCES Wahlbezirk(WahlbezirkID)
);

CREATE TABLE Stimme (
    StimmeID INT PRIMARY KEY,
    WahlberechtigterID INT NOT NULL,
    KandidatID INT NOT NULL,
    CONSTRAINT fk_Waehler_Stimme
        FOREIGN KEY (WahlberechtigterID)
        REFERENCES Wahlberechtigter(WahlberechtigterID),
    CONSTRAINT fk_Kandidat_Stimme
        FOREIGN KEY (KandidatID)
        REFERENCES Kandidat(KandidatID)
);

* Wahlkreiskandidaten?
* Ergebnisse der Auszählung

#################################

CREATE TABLE Bundesland (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE Wahlkreis (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  bundesland_id INT NOT NULL,
  anzahl_wahlberechtigte INT NOT NULL,
  FOREIGN KEY (bundesland_id) REFERENCES Bundesland(id)
);

CREATE TABLE Wahlbezirk (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  wahlkreis_id INT NOT NULL,
  anzahl_wahlberechtigte INT NOT NULL,
  FOREIGN KEY (wahlkreis_id) REFERENCES Wahlkreis(id)
);

CREATE TABLE Partei (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  kurzname VARCHAR(10) NOT NULL
);

CREATE TABLE Landesliste (
  id INT PRIMARY KEY,
  partei_id INT NOT NULL,
  bundesland_id INT NOT NULL,
  FOREIGN KEY (partei_id) REFERENCES Partei(id),
  FOREIGN KEY (bundesland_id) REFERENCES Bundesland(id)
);

CREATE TABLE Kandidat (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  partei_id INT NOT NULL,
  wahlkreis_id INT NOT NULL,
  position_auf_liste INT NOT NULL,
  FOREIGN KEY (partei_id) REFERENCES Partei(id),
  FOREIGN KEY (wahlkreis_id) REFERENCES Wahlkreis(id)
);

CREATE TABLE Ergebnis (
  id INT PRIMARY KEY,
  wahlbezirk_id INT NOT NULL,
  partei_id INT NOT NULL,  <<< so ist es richtig. Später macht er anderes
  anzahl_erststimmen INT NOT NULL,
  anzahl_zweitstimmen INT NOT NULL,
  FOREIGN KEY (wahlbezirk_id) REFERENCES Wahlbezirk(id),
  FOREIGN KEY (partei_id) REFERENCES Partei(id)
);


######################################
CREATE TABLE Direktkandidaten (
  ID INT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  ParteiID INT NOT NULL,
  WahlkreisID INT NOT NULL,
  CONSTRAINT FK_Direktkandidaten_Partei FOREIGN KEY (ParteiID)
    REFERENCES Parteien(ID),
  CONSTRAINT FK_Direktkandidaten_Wahlkreis FOREIGN KEY (WahlkreisID)
    REFERENCES Wahlkreise(ID)
);

CREATE TABLE Listenkandidaten (
  ID INT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  ParteiID INT NOT NULL,
  LandeslisteID INT NOT NULL,
  Listenplatz INT NOT NULL,
  CONSTRAINT FK_Listenkandidaten_Partei FOREIGN KEY (ParteiID)
    REFERENCES Parteien(ID),
  CONSTRAINT FK_Listenkandidaten_Landesliste FOREIGN KEY (LandeslisteID)
    REFERENCES Landeslisten(ID)
    )



SELECT k.Name, k.Wohnort
FROM Kandidatur k
JOIN Wahlkreis w ON k.Wahlkreis_ID = w.ID
WHERE k.Listenplatz IS NULL AND k.Wohnort IN (
    SELECT w.Wohnort   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<???
    FROM Wahlkreis w
    JOIN Kandidatur k ON w.ID = k.Wahlkreis_ID
    WHERE k.Listenplatz IS NULL
)
ORDER BY k.Wohnort, k.Name;

### die Gemeinden fehlen im ganzen Schema ... also Hinweis
### korrigiert:
CREATE TABLE Wahlkreis (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    bundesland TEXT NOT NULL,
    anzahl_wahlberechtigte INTEGER NOT NULL,
    gemeinde TEXT NOT NULL,
    FOREIGN KEY (bundesland) REFERENCES Bundesland(name),
    CHECK (anzahl_wahlberechtigte >= 0)
);

Jeder Wahlkreis hat mehrere Gemeinden ... 
"Ja, das macht Sinn. " ...sagt ChatGPT:

CREATE TABLE Wahlbezirk (
    name varchar(255) NOT NULL,
    wahlberechtigte integer NOT NULL,
    gemeinde_id integer NOT NULL,
    wahlkreis_id integer NOT NULL,
    PRIMARY KEY (name, wahlkreis_id),
    FOREIGN KEY (gemeinde_id) REFERENCES Gemeinde(id),
    FOREIGN KEY (wahlkreis_id) REFERENCES Wahlkreis(id)
);


SELECT k.name, k.wohnort
FROM Wahlkreiskandidatur wk
JOIN Kandidat k ON wk.kandidat_id = k.id
JOIN Wahlkreis w ON wk.wahlkreis_id = w.id
WHERE k.wohnort IN (SELECT g.gemeindename FROM Gemeinde g WHERE g.id = w.gemeinde_id);

SELECT k.name, k.wohnort
FROM Kandidatur k
JOIN Wahlkreis w ON k.wahlkreis_id = w.id
JOIN Wahlbezirk b ON b.wahlkreis_id = w.id
WHERE k.wahlkreis_id = b.wahlkreis_id AND k.wohnort = b.gemeinde)


π k.name, k.wohnort, w.name, w.wahlberechtigte (σ k.wahlkreis =
w.nummer ∧ k.wohnort = w.gemeinde (Kandidat k ⨝ Wahlbezirk w))
### sehr seltsame Syntax, Aliasing gibts in der relationalen Algebra nicht!

π kandidat.name, kandidat.wohnort
σ kandidat.wahlkreis = w.name ∧ kandidat.wohnort = w.ort ∧ w.ort = wahlbezirk.gemeinde
(kandidat ⨝ w) ⨝ wahlbezirk

### Algebra kanner auch als IMG zurückgeben (wird aber nicht dargestellt), später versucht
er es dann auch mal als embedded TeX

<img
src="https://latex.codecogs.com/svg.image?\pi_{K.name,K.wahlkreis}&space;\sigma_{K.wohnort=W.wohnort}&space;(K&space;\Join_{K.wahlkreis=W.wahlkreis}&space;W)"
title="\pi_{K.name,K.wahlkreis} \sigma_{K.wohnort=W.wohnort} (K
\Join_{K.wahlkreis=W.wahlkreis} W)" />

### und was soll die Tabelle K sein?   ... wohl Kandidat ... 

### b) GROUP BY

SELECT wb.name, p.name
FROM (
    SELECT z.wb_id, z.partei_id, ROW_NUMBER() OVER (
        PARTITION BY z.wb_id ORDER BY z.zweitstimmen DESC
    ) AS rn
    FROM Zweitstimmen z
) AS x
JOIN Wahlbezirk wb ON wb.id = x.wb_id
JOIN Partei p ON p.id = x.partei_id
WHERE x.rn = 1;

SELECT b.name, z.partei
FROM (
    SELECT wb.name AS bezirk, z.partei, COUNT(*) AS stimmen
    FROM ErgebnisZweitstimmen z
    JOIN Wahlbezirk wb ON z.wahlkreis = wb.wahlkreis AND z.wahlbezirk = wb.name
    GROUP BY wb.name, z.partei
) AS bz_stimmen
JOIN (
    SELECT wb.name AS bezirk, MAX(stimmen) AS max_stimmen
    FROM (
        SELECT wb.name AS bezirk, z.partei, COUNT(*) AS stimmen
        FROM ErgebnisZweitstimmen z
        JOIN Wahlbezirk wb ON z.wahlkreis = wb.wahlkreis AND z.wahlbezirk = wb.name
        GROUP BY wb.name, z.partei
    ) AS wb_stimmen
    GROUP BY wb.name
) AS bz_max ON bz_stimmen.bezirk = bz_max.bezirk AND bz_stimmen.stimmen = bz_max.max_stimmen
JOIN Wahlbezirk b ON bz_stimmen.bezirk = b.name
JOIN ErgebnisZweitstimmen z ON bz_stimmen.bezirk = z.wahlbezirk AND bz_stimmen.partei = z.partei AND b.wahlkreis = z.wahlkreis

Die Anfrage gruppiert die Stimmen nach Wahlbezirk und Partei und
verwendet eine Subquery, um den Wahlbezirk mit den meisten Stimmen für
jede Partei zu finden. Dann wird eine weitere JOIN-Abfrage verwendet,
um den Namen des Wahlbezirks zu erhalten.


c)  

Angenommen, die Tabelle "Wahlbezirk" hat eine Spalte "gemeinde" für
den Namen der Gemeinde und die Tabelle "Ergebnis" hat Spalten
"wahlbezirk", "partei" und "erststimmen" für das Wahlergebnis in jedem
Wahlbezirk. Dann lautet die SQL-Anfrage:

SELECT DISTINCT w.gemeinde
FROM Wahlbezirk w
WHERE NOT EXISTS (
  SELECT *
  FROM Ergebnis e1
  WHERE e1.wahlbezirk = w.name AND e1.partei = 'SPD' AND e1.erststimmen > (
    SELECT COALESCE(MAX(e2.erststimmen), 0)
    FROM Ergebnis e2
    WHERE e2.wahlbezirk = w.name AND e2.partei = 'CDU'
  )
);

SELECT DISTINCT g.name
FROM Gemeinde g
LEFT JOIN Wahlbezirk w ON g.name = w.gemeinde
LEFT JOIN Kandidat k ON w.name = k.wahlbezirk
WHERE k.erststimmen_cdu > k.erststimmen_spd OR k.erststimmen_cdu IS NULL

SELECT g.name 
FROM Gemeinde g
WHERE NOT EXISTS (
    SELECT * 
    FROM Wahlbezirk wb 
    WHERE wb.gemeinde_id = g.id 
    AND wb.erststimmen_spd > wb.erststimmen_cdu
)
### im Prinzip jetzt richtig (NOT EXIST). Aber aus welcher Tabelle, und
wie sind die Erststimmen der einzelnen Parteien dort abgelegt?
Nie Daten (Parteinamen) in Spaltennamen ... aber die Tabelle da eh anders aus ...
es verstrickt sich.

SELECT DISTINCT g.name
FROM Gemeinde g
LEFT JOIN Wahlbezirk wb ON g.name = wb.gemeinde
LEFT JOIN Erststimmen es ON wb.id = es.wahlbezirk_id
LEFT JOIN Kandidat k ON es.kandidat_id = k.id
WHERE wb.id IS NULL OR 
      (k.partei = 'SPD' AND es.anzahl > (SELECT MAX(es2.anzahl) FROM Erststimmen es2 WHERE es2.partei = 'CDU' AND es2.wahlbezirk_id = es.wahlbezirk_id))


SELECT DISTINCT G.gemeinde_name
FROM Gemeinde G
LEFT JOIN Wahlbezirk W ON G.gemeinde_id = W.gemeinde_id
LEFT JOIN Erststimmen E ON W.wahlkreis_id = E.wahlkreis_id AND W.wahlbezirk_id = E.wahlbezirk_id
WHERE W.wahlbezirk_id IS NULL 
AND NOT EXISTS (
    SELECT *
    FROM Erststimmen E2
    WHERE E2.wahlkreis_id = E.wahlkreis_id 
    AND E2.wahlbezirk_id = E.wahlbezirk_id 
    AND E2.erststimmen_spd > E.erststimmen_cdu
);


SELECT g.name 
FROM Gemeinde g 
LEFT JOIN Wahlbezirk wb ON g.gemeinde_id = wb.gemeinde_id 
LEFT JOIN (
    SELECT w.wahlbezirk_id, e1.erststimmen, e1.partei 
    FROM Erststimmen e1 
    JOIN (
        SELECT wahlkreis_id, max(erststimmen) as max_erststimmen 
        FROM Erststimmen 
        GROUP BY wahlkreis_id
    ) e2 ON e1.wahlkreis_id = e2.wahlkreis_id AND e1.erststimmen = e2.max_erststimmen 
) es ON wb.wahlbezirk_id = es.wahlbezirk_id 
WHERE es.partei <> 'CDU' OR es.partei IS NULL;


π Gemeinde_name (Gemeinde - σ ¬∃(σ Partei = 'SPD' (Erststimmen)) (σ Partei = 'CDU' (Erststimmen) ⨝ Wahlbezirk))


π G.gemeindeName ((σ C.erststimmen < S.erststimmen) ⨯ ρ(G)(Gemeinde) - π G.gemeindeName(σ G.gemeindeName = B.gemeindeName ⨯ Kandidat))
### keine zulässige Syntax, kaputte Klammerung, rho geht so garnicht ... 


SELECT g.gemeindename
FROM Gemeinde g
WHERE NOT EXISTS (
    SELECT *
    FROM Wahlbezirk w
    JOIN Erststimmen e ON w.wahlbezirk_id = e.wahlbezirk_id
    JOIN Partei p ON e.erststimmen_partei_id = p.partei_id
    WHERE w.gemeinde_id = g.gemeinde_id AND p.partei_name = 'CDU'
)
AND EXISTS (
    SELECT *
    FROM Wahlbezirk w
    JOIN Erststimmen e ON w.wahlbezirk_id = e.wahlbezirk_id
    JOIN Partei p ON e.erststimmen_partei_id = p.partei_id
    WHERE w.gemeinde_id = g.gemeinde_id AND p.partei_name = 'SPD'
)

π Gemeinde.name ((π E1.gemeinde_id, E1.partei, E1.stimmen) ⋈ π E2.gemeinde_id ((σ E1.partei = 'SPD') ⋈ E2 (σ E2.partei = 'CDU'))) - (π Gemeinde.name (Wahlbezirk ⋈ E1 ⋈ Gemeinde)) 

### er kanns nicht.

### d) Wahlkreissummen der Parteien

SELECT w.wahlkreis_id, w.name, k.partei, SUM(k.erststimmen)
FROM Wahlkreis w
JOIN Wahlbezirk b ON b.wahlkreis_id = w.wahlkreis_id
JOIN Kandidat k ON k.wahlbezirk_id = b.wahlbezirk_id
GROUP BY w.wahlkreis_id, w.name, k.partei


CREATE VIEW erststimmen_wahlkreis AS
SELECT w.wahlkreis_id, k.partei, SUM(k.erststimmen) AS sum_erststimmen
FROM kandidat k
JOIN wahlbezirk w ON k.wahlbezirk_id = w.wahlbezirk_id
GROUP BY w.wahlkreis_id, k.partei

f)  gewählte Erststimmen-Kandidaten:

SELECT w.Name AS WahlkreisName, k.Partei AS GewähltePartei
FROM Kandidat k
JOIN (
  SELECT WahlkreisID, MAX(Erststimmen) AS MaxErststimmen
  FROM Ergebnis_Erststimme
  GROUP BY WahlkreisID
) e ON k.WahlkreisID = e.WahlkreisID AND k.Erststimmen = e.MaxErststimmen
JOIN Wahlkreis w ON k.WahlkreisID = w.ID;

SELECT w.name AS wahlkreis_name, 
       k.partei AS gewaehlte_partei
FROM Kandidat k
JOIN Wahlbezirk wb ON k.wahlbezirk_id = wb.wahlbezirk_id
JOIN Wahlkreis w ON wb.wahlkreis_id = w.wahlkreis_id
WHERE k.erststimmen = (SELECT MAX(erststimmen) 
                       FROM Kandidat k2 
                       WHERE k2.wahlbezirk_id = wb.wahlbezirk_id)
GROUP BY w.wahlkreis_id

SELECT k.wahlkreis_id, k.partei, MAX(e.erststimmen) AS max_erststimmen
FROM kandidaten k 
JOIN erststimmen e ON k.kandidaten_id = e.kandidaten_id
GROUP BY k.wahlkreis_id, k.partei

g)

SELECT p.Abkürzung
FROM Partei p
WHERE NOT EXISTS (
  SELECT *
  FROM Wahlkreis w
  WHERE w.Wahlberechtigte >= 100000 AND NOT EXISTS (
    SELECT *
    FROM Kandidat k
    WHERE k.Partei = p.Abkürzung AND k.Wahlkreis = w.ID
  )
);

π(partei) (
    σ(w_id IN (
        SELECT w_id
        FROM Wahlkreis
        WHERE wahlberechtigte >= 100000
    )) (
        Kandidat
    ) 
    ÷ 
    π(w_id) (
        Wahlkreis
    )
)


### jetzt wirds vogelwild: 

ΠPartei​(Kandidat)−ΠPartei​(Kandidat⋈Wahlkreis.wahlkreisi​d=Kandidat.wahlkreisi​d​(SELECTw.wahlkreisi​dFROMWahlkreiswGROUPBYw.wahlkreisi​dHAVINGCOUNT(DISTINCTw.wahlberechtigte)>=100000))


π Partei (σ Wahlkreisname ϵ (π Wahlkreisname (σ Wahlberechtigte >=
100000 (ρ Wahlkreisname ← w) ρ)))
÷ π Partei (σ Wahlberechtigte >= 100000 (ρ Partei ← p))


h) SELECT DISTINCT p.abkuerzung
FROM Partei p
WHERE p.abkuerzung NOT IN (
  SELECT k.partei
  FROM Kandidat k
  JOIN Wahlkreis w ON k.wahlkreis_id = w.wahlkreis_id
  GROUP BY w.wahlkreis_id
  HAVING COUNT(DISTINCT w.gemeinde_id) >= 1 AND COUNT(DISTINCT w.gemeinde_id) < 100000
)

### auch wild ...


Theorie: labert.

INSERT: ok,
UPDATE: ok