2

  Abgabesystem Informatik <https://auas.cs.uni-duesseldorf.de/>

  * Startseite <https://auas.cs.uni-duesseldorf.de/student/index>
  * Vorlesungsverzeichnis <https://auas.cs.uni-duesseldorf.de/course/list>
  * Profil <https://auas.cs.uni-duesseldorf.de/profile/index>
  * Logout <https://auas.cs.uni-duesseldorf.de/student/logout>


  Abgabe ansehen


      Korrektor: Bastian Berndt

2. Aufgabenteil

ilovepdf_merged.pdf - Größe:: 251.9 KB
<https://auas.cs.uni-duesseldorf.de/file/de/post/download/a73bf9b568667587b5a334058cca8dee>

seite_hat_eintrag:
    *Seite[1,N]
    *Eintrag[1,1]

Ich glaube es wurde nie besprochen wie dies gehandhabt werden sollte. Verschmelzen würde die [1,N] constraint vergessen. Allerdings sind at-least-one constraints  sowiso nicht mit standard SQL darstellbar, daher habe ich jetzt erstmal verschmolzen.

Ich habe bild_hat_gps_koordinate verschmolzen obwohl das einen optionalen foreign key benötigt.

GpsKoordinate hat eine ID column but länge+breite sollten trotzdem eine unique constraint haben.


Korrektur:

*Punkte 1 von 1*
Schwere Fehler:


Mittlere Fehler:


Leichte Fehler:
* Bild und bild_hat_gps_koordinate werden nicht verschmolzen, da [0,1] und nicht [1,1].

Anmerkungen:

Bestanden!

------------------------------------------------------------------------
Datenschutz <http://www.uni-duesseldorf.de/home/footer/datenschutz.html>
Impressum <http://www.uni-duesseldorf.de/home/footer/impressum.html>

3

  Abgabesystem Informatik <https://auas.cs.uni-duesseldorf.de/>

  * Startseite <https://auas.cs.uni-duesseldorf.de/student/index>
  * Vorlesungsverzeichnis <https://auas.cs.uni-duesseldorf.de/course/list>
  * Profil <https://auas.cs.uni-duesseldorf.de/profile/index>
  * Logout <https://auas.cs.uni-duesseldorf.de/student/logout>


  Abgabe ansehen


      Korrektor: Bastian Berndt

3. Aufgabenteil

temp.zip - Größe:: 6.96 KB
<https://auas.cs.uni-duesseldorf.de/file/de/post/download/adbf8ce45f9ace14c611de32a87f56f0>

Geld ist als Integer gespeichert, niedrigsten zwei Ziffern sind Nachkommastellen. Ich war mir nicht sicher ob REGEXP am Testrechner installiert ist also ist der email_addresse CHECK etwas unlesbar geworden.

Ich überprüfe nicht ob der Preis 0 ist wenn es keine Tagebuch Seite gibt da das sqlite meines wissens keine deferred triggers hat.

Ich bin mir nicht ganz sicher wie das korrigierte Relationsmodel abgeben soll da PDF nicht erlaubt ist. Die Änderung ist

+    bild_hat_gps_koordinate:
+        *Bild![0,1]
+        *GpsKoordinate[0,N]

wobei * Foreign und ! Primary key ist. Hier ist ein tool das ich geschrieben habe um die plaintext syntax zu pandoc markdown and yed zu übersetzen: https://github.com/Tarmean/YedWriter

Die Schema creation ist in der zip datei unter temp.txt, die queries unter requests.sql. Hier noch mal inline:

--------------------------------------------------

PRAGMA auto_vacuum = 1;
PRAGMA automatic_index = 1;
PRAGMA case_sensitive_like = 0;
PRAGMA defer_foreign_keys = 0;
PRAGMA encoding = "UTF-8";
PRAGMA foreign_keys = 1;
PRAGMA ignore_check_constraints = 0;
PRAGMA journal_mode = WAL;
PRAGMA query_only = 0;
PRAGMA recursive_triggers = 1;
PRAGMA reverse_unordered_selects = 0;
PRAGMA secure_delete = 0;
PRAGMA synchronous = NORMAL;

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS  Benutzer (
    email_addresse TEXT PRIMARY KEY CHECK (LENGTH(substr(email_addresse, 1, instr(email_addresse, '@')-1)) > 0 AND LENGTH(substr(email_addresse, instr(email_addresse, '@')+1, instr(email_addresse, '.') - instr(email_addresse, '@')-1)) > 0 AND LENGTH(substr(email_addresse, instr(email_addresse, '.')+1)) > 0 AND substr(email_addresse, 1, instr(email_addresse, '@')-1) NOT GLOB '*[^a-zA-Z0-9]*' AND substr(email_addresse, instr(email_addresse, '@')+1, instr(email_addresse, '.') - instr(email_addresse, '@') - 1) NOT GLOB '*[^a-zA-Z0-9]*' AND substr(email_addresse, instr(email_addresse, '.')+1) NOT GLOB '*[^a-zA-Z]*'),

    vorname TEXT NOT NULL COLLATE NOCASE CHECK (LENGTH(vorname) > 0 AND vorname NOT GLOB '*[^a-zA-Z]*'),
    nachname TEXT NOT NULL COLLATE NOCASE CHECK (LENGTH(nachname) > 0 AND nachname NOT GLOB '*[^a-zA-Z]*'),
    passwort TEXT NOT NULL CHECK (LENGTH(passwort) >= 6 AND passwort GLOB '*[0-9]*' AND passwort GLOB '*[A-Z]*')
);

CREATE TABLE IF NOT EXISTS Autor (
    email_addresse TEXT PRIMARY KEY,
    pseudonym TEXT NOT NULL CHECK (LENGTH(pseudonym) > 0),
    tage_buch_preis INTEGER NOT NULL,
    avatar BLOB,
    CONSTRAINT fk_autor_benutzer
        FOREIGN KEY (email_addresse)
        REFERENCES Benutzer (email_addresse)
);

CREATE TABLE IF NOT EXISTS Bild (
    bild_id INTEGER PRIMARY KEY AUTOINCREMENT,
    blob BLOB NOT NULL,
    eintrag INTEGER NOT NULL,
    CONSTRAINT fk_bild_eintrag
        FOREIGN KEY (eintrag)
        REFERENCES Eintrag (eintrag_id)

);
CREATE TABLE IF NOT EXISTS BildHatGps (
    bild  INTEGER PRIMARY KEY,
    gps  INTEGER NOT NULL,
    CONSTRAINT fk_bildhatgps_gps
        FOREIGN KEY (gps)
        REFERENCES GpsKoordinaten (gps_id),
    CONSTRAINT fk_bildhatgps_bild
        FOREIGN KEY (bild)
        REFERENCES Bild (bild_id)
);
CREATE TABLE IF NOT EXISTS GpsKoordinaten (
    gps_id INTEGER PRIMARY KEY AUTOINCREMENT,
    laenge FLOAT NOT NULL,
    breite FLOAT NOT NULL,
    CONSTRAINT uniq_gps UNIQUE (laenge, breite)
);

CREATE TABLE IF NOT EXISTS Eintrag (
    eintrag_id INTEGER PRIMARY KEY AUTOINCREMENT,
    titel TEXT NOT NULL CHECK(LENGTH(titel) > 0),
    uhrzeit TEXT NOT NULL CHECK (strftime('%H-%M-%S', uhrzeit) IS NOT NULL),
    text TEXT NOT NULL CHECK (length(text) > 0),
    seite INTEGER NOT NULL,
    CONSTRAINT fk_eintrag_seite
        FOREIGN KEY (seite)
        REFERENCES Seite (seite_id)

);

CREATE TABLE IF NOT EXISTS Seite (
    seite_id INTEGER PRIMARY KEY AUTOINCREMENT,
    seiten_nummer INTEGER NOT NULL CHECK (seiten_nummer >= 0),
    typ TEXT NOT NULL CHECK (lower(typ) IN ('private', 'public')),
    datum TEXT NOT NULL CHECK (strftime('%Y-%m-%d', datum) IS NOT NULL),
    autor TEXT NOT NULL,
    CONSTRAINT fk_seite_autor
        FOREIGN KEY (autor)
        REFERENCES Autor (email_addresse)

);
CREATE TRIGGER chk_private_seite_benoetigt_preis_insrt
    BEFORE INSERT ON Seite
    FOR EACH ROW BEGIN
        SELECT RAISE(ABORT, 'Autor requires tagebuch preis') WHERE lower(NEW.typ) = 'private' AND EXISTS (SELECT 1 FROM Autor AS A WHERE A.email_addresse == NEW.autor AND A.tage_buch_preis = 0);
    END;
CREATE TRIGGER chk_private_seite_benoetigt_preis_updt
    BEFORE UPDATE ON Seite
    FOR EACH ROW BEGIN
        SELECT RAISE(ABORT, 'Autor requires tagebuch preis') WHERE lower(NEW.typ) = 'private' AND EXISTS (SELECT 1 FROM Autor AS A WHERE A.email_addresse == NEW.autor AND A.tage_buch_preis = 0);
    END;
CREATE TRIGGER chk_private_seite_pro_tag_insrt
    BEFORE INSERT ON Seite
    FOR EACH ROW BEGIN
        SELECT RAISE(ABORT, 'Maximal eine seite pro tag') WHERE EXISTS (SELECT * FROM Seite AS S WHERE S.autor = NEW.autor AND date(S.datum) = date(NEW.datum)AND S.seite_id != NEW.seite_id);
    END;
CREATE TRIGGER chk_private_seite_pro_tag_updt
    BEFORE UPDATE ON Seite
    FOR EACH ROW BEGIN
        SELECT RAISE(ABORT, 'Maximal eine seite pro tag') WHERE EXISTS (SELECT * FROM Seite AS S WHERE S.autor = NEW.autor AND date(S.datum) = date(NEW.datum) AND S.seite_id != NEW.seite_id);
    END;

CREATE TABLE IF NOT EXISTS TAG (
    attribut TEXT COLLATE NOCASE PRIMARY KEY CHECK(length(attribut) > 0 AND attribut NOT GLOB '*[^a-zA-Z]*')
);

CREATE TABLE IF NOT EXISTS Transaktion(
    transaktion_id INTEGER PRIMARY KEY AUTOINCREMENT,
    gutscheincode TEXT CHECK (gutscheincode IS NULL OR length(gutscheincode > 0)),
    betrag INTEGER NOT NULL,
    datum TEXT NOT NULL CHECK (strftime('%Y-%m-%d', datum) IS NOT NULL),
    zahlungsmittel TEXT NOT NULL CHECK (lower(zahlungsmittel) IN ('paypal', 'ueberweisung')),
    sender TEXT NOT NULL,
    zweck TEXT NOT NULL CHECK (length(zweck) > 0),
    empfaenger TEXT NOT NULL,
    CONSTRAINT fk_transaktion_autor
        FOREIGN KEY (empfaenger)
        REFERENCES Autor (email_addresse),
    CONSTRAINT fk_transaktion_benutzer
        FOREIGN KEY (sender)
        REFERENCES Benutzer (email_addresse)
);
CREATE TABLE IF NOT EXISTS AutorEmpfieltAutor (
    autor_von TEXT NOT NULL,
    autor_zu TEXT NOT NULL,
    CONSTRAINT fk_autorempfieltautor_autor_von
        FOREIGN KEY (autor_von)
        REFERENCES Autor (email_addresse),
    CONSTRAINT fk_autorempfieltautor_autor_zu
        FOREIGN KEY (autor_zu)
        REFERENCES Autor (email_addresse)
);
CREATE TABLE IF NOT EXISTS BenutzerAbonniertAutor (
    nutzer TEXT NOT NULL,
    autor TEXT NOT NULL,
    CONSTRAINT fk_benutzerabonniertautor_nutzer
        FOREIGN KEY (nutzer)
        REFERENCES Benutzer (email_addresse),
    CONSTRAINT fk_benutzerabonniertautor_autor
        FOREIGN KEY (autor)
        REFERENCES Autor (email_addresse)
);
CREATE TABLE IF NOT EXISTS BenutzerBewertetAutor (
    autor TEXT NOT NULL,
    nutzer TEXT NOT NULL,
    benotung INTEGER NOT NULL CHECK (benotung IN (1,2,3,4,5)),
    text TEXT NOT NULL CHECK (LENGTH(text) > 0),
    CONSTRAINT fk_benutzerbewertetautor_nutzer
        FOREIGN KEY (nutzer)
        REFERENCES Benutzer (email_addresse),
    CONSTRAINT fk_benutzerbewertetautor_autor
        FOREIGN KEY (autor)
        REFERENCES Autor (email_addresse)

);

CREATE TABLE IF NOT EXISTS BildHatTag (
    bild INTEGER NOT NULL,
    tag TEXT NOT NULL,
    CONSTRAINT fk_bildhattag_bild
        FOREIGN KEY (bild)
        REFERENCES Bild (bild_id),
    CONSTRAINT fk_bildhattag_tag
        FOREIGN KEY (tag)
        REFERENCES Tag (attribut)
);

INSERT INTO Benutzer VALUES
    ('foo@bar.co', 'Max', 'Musterman', 'Paass3'),
    ('sbb@onm.pbpntr', 'Zhk', 'Znfgrezna', 'Cnnff3');
INSERT INTO Autor VALUES
    ('foo@bar.co', 'Pseuydo', 1, NULL),
    ('sbb@onm.pbpntr', 'Cfrhlqb', 0, NULL);
INSERT INTO Seite (seite_id, seiten_nummer, typ, datum, autor) VALUES
    (0, 0, 'private', '1970-01-10', 'foo@bar.co'),
    (1, 0, 'public', '1970-01-10', 'sbb@onm.pbpntr'),
    (2, 0, 'public', '1970-01-11', 'sbb@onm.pbpntr');
INSERT INTO Eintrag (eintrag_id, titel, uhrzeit, text, seite) VALUES
    (0, 'Silvester in London', '00:11:22', 'Heyo', 0),
    (1, 'Zrva Rvagent', '00:11:22', 'Urlb', 1),
    (2, 'Mein Rvagent', '00:11:23', 'Urlb', 1),
    (3, 'Zrva Eintrag', '00:11:24', 'Heyo', 2);
INSERT INTO Bild VALUES
    (0, zeroblob(0), 0),
    (1, zeroblob(0), 1);
INSERT INTO GpsKoordinaten VALUES
    (0, 0, 1),
    (1, 1, 0);
INSERT INTO BildHatGps VALUES
    (0, 1),
    (1, 0);
INSERT INTO Tag (attribut) VALUES
    ('Dog'), ('Cat');
INSERT INTO Transaktion (gutscheincode, betrag, datum, zahlungsmittel, sender, zweck, empfaenger) VALUES
    (NULL, 1234, '1970-01-10', 'paypaL', 'foo@bar.co', 'Tax evasion', 'foo@bar.co'),
    (NULL, 1234, '1970-01-10', 'paypal', 'foo@bar.co', 'Bought stuff', 'sbb@onm.pbpntr');
INSERT INTO AutorEmpfieltAutor VALUES
    ('foo@bar.co', 'foo@bar.co'),
    ('foo@bar.co', 'sbb@onm.pbpntr');
INSERT INTO BenutzerAbonniertAutor VALUES
    ('sbb@onm.pbpntr', 'foo@bar.co'),
    ('foo@bar.co', 'sbb@onm.pbpntr');
INSERT INTO BenutzerBewertetAutor VALUES
    ('foo@bar.co', 'foo@bar.co', 5, 'Literally the best'),
    ('foo@bar.co', 'sbb@onm.pbpntr', 3, 'Ok I guess');
INSERT INTO BildHatTag VALUES
    (0, 'Cat'),
    (1, 'Dog');



COMMIT;




------------------------------------------------



SELECT tag FROM BildHatTag GROUP BY tag ORDER BY COUNT(*) DESC LIMIT 2;

SELECT B.autor
FROM BenutzerAbonniertAutor AS B
    JOIN Autor AS A ON B.autor = A.email_addresse
GROUP BY B.autor
HAVING COUNT(B.nutzer) * tage_buch_preis = (SELECT COUNT(B.nutzer) * A.tage_buch_preis from BenutzerAbonniertAutor as B JOIN Autor as A ON B.autor = A.email_addresse GROUP BY B.autor ORDER BY COUNT(B.nutzer) * A.tage_buch_preis DESC LIMIT 1);


SELECT E.eintrag_id
FROM Eintrag AS E
    JOIN Seite AS S ON E.seite = S.seite_id
    JOIN Autor AS A ON A.email_addresse = S.autor
WHERE E.titel = 'Silvester in London' AND (SELECT AVG(B.benotung) FROM BenutzerBewertetAutor as B WHERE B.autor = S.autor) IN (4,5);

/* 'Tagebücher' ist keine Entity also gebe ich den autor zurück */
SELECT S.autor
FROM Eintrag as E
    JOIN Seite as S ON E.seite = S.seite_id
WHERE
  EXISTS (
    SELECT 1
    FROM GpsKoordinaten as G
        JOIN BildHatGps as H ON G.gps_id = H.gps
        JOIN Bild as B ON B.bild_id = H.bild
    WHERE ABS(G.breite) < 0.001
      AND E.eintrag_id = B.eintrag
  );

SELECT A.email_addresse
FROM Autor as A
WHERE
    (SELECT COUNT(E.eintrag_id)
     FROM Seite AS S
       JOIN Eintrag AS E ON E.seite = S.seite_id
     WHERE S.typ = 'public'
       AND S.autor = A.email_addresse) >= 3
  AND NOT EXISTS
    (SELECT S.autor
     FROM Eintrag AS E
       JOIN Seite AS S ON E.seite = S.seite_id
       WHERE S.typ = 'private'
         AND S.autor = A.email_addresse);


Korrektur:

*Punkte 1 von 1*
Schwere Fehler:


Mittlere Fehler:


Leichte Fehler:
* Bei Anfrage 2 soll der Verdienst mit ausgegeben werden.

Anmerkungen:
- ON DELETE CASCADE und ON UPDATE CASCADE sind wichtig für Fremdschlüssel und sparen Aufwand im letzten Teil.

Bestanden.

------------------------------------------------------------------------
Datenschutz <http://www.uni-duesseldorf.de/home/footer/datenschutz.html>
Impressum <http://www.uni-duesseldorf.de/home/footer/impressum.html>