M165 NoSQL Datenbanken | Block 02¶

Inhaltsverzeichnis¶

Modellierungsübung Online-Bestellsystem¶

erDiagram

    %% ============================
    %% ENTITAETEN
    %% ============================

    KUNDE {
        int Kunden_ID PK
        string Vorname
        string Nachname
        string Email "UNIQUE"
        string Passwort
        string Telefonnummer
        string Adresse
    }

    PRODUKT {
        int Produkt_ID PK
        string Produktname
        string Beschreibung
        double Preis
        int Lagerbestand
    }

    BESTELLUNG {
        int Bestell_ID PK
        int Kunden_ID FK
        date Bestelldatum
        string Status
    }

    BESTELLPOSITION {
        int Bestellpositions_ID PK
        int Bestell_ID FK
        int Produkt_ID FK
        int Menge
    }

    %% ============================
    %% BEZIEHUNGEN
    %% ============================

    %% KUNDE (1) --- (n) BESTELLUNG
    KUNDE ||--o{ BESTELLUNG : "gibt auf"

    %% BESTELLUNG (1) --- (n) BESTELLPOSITION
    BESTELLUNG ||--o{ BESTELLPOSITION : "enthält"

    %% PRODUKT (1) --- (n) BESTELLPOSITION
    %% bildet gemeinsam eine n:m Beziehung
    PRODUKT ||--o{ BESTELLPOSITION : "kommt vor in"

Normalisierung¶

Ausgangstabelle (unnormalisiert)¶

Film_ID	Titel	Jahr	Regisseur	Regisseur_Telefon	Hauptdarsteller	Genre
1	Der Pate	1972	Francis Ford Coppola	+430123-456789	Marlon Brando, Al Pacino	Drama, Krimi
2	Inception	zweitausendzehn	Christopher Nolan	0041 0987-654321	Leonardo DiCaprio	Sci-Fi, Thriller
3	Pulp Fiction	1994	Quentin Tarantino	0456789012	John Travolta, Uma Thurman	Drama, Krimi
4	Titanic	1997	James Cameron	0123-456789	Leonardo DiCaprio, Kate Winslet	Drama, Romantik
5	The Dark Knight	zweitausendacht	Christopher Nolan	0987-654321	Christian Bale, Heath Ledger	Action, Drama

1. Normalform (1NF)

Ziele¶

Keine mehrfachen Werte
Jedes Feld atomar
Einheitliche Formate

Probleme¶

Hauptdarsteller enthält Listen
Genre enthält Listen
Jahr ist uneinheitlich (Zahl / Wort)

Lösung¶

Schauspieler → eigene Tabelle
Genres → eigene Tabelle
Jahr → überall Integer

Normalform (2NF)

Ziele¶

Alle Nicht-Schlüsselattribute hängen vom ganzen Primärschlüssel ab

Erkenntnisse¶

Ein Regisseur kann mehrere Filme machen → Auslagern
Regisseur_Telefon gehört funktional zum Regisseur → Auslagern

Neue Tabellen¶

REGISSEUR
FILM_SCHAUSPIELER (m:n)
FILM_GENRE (m:n)

3. Normalform (3NF)

Ziel¶

Keine transitiven Abhängigkeiten

Korrektes Modell¶

Telefon hängt vom Regisseur ab → separate Tabelle REGISSEUR
Keine Nicht-Schlüsselattribute hängen voneinander ab

Finale normalisierte Tabellenstruktur

Tabelle: FILM¶

Attribut	Typ	Beschreibung
Film_ID (PK)	INT	eindeutige ID
Titel	VARCHAR	Filmtitel
Jahr	INT	Erscheinungsjahr
Regisseur_ID (FK)	INT	Verweis auf Regisseur

Tabelle: REGISSEUR¶

Attribut	Typ
Regisseur_ID (PK)	INT
Name	VARCHAR
Telefon	VARCHAR

Tabelle: SCHAUSPIELER¶

Attribut	Typ
Schauspieler_ID (PK)	INT
Name	VARCHAR

Tabelle: FILM_SCHAUSPIELER (m:n Beziehung)¶

Attribut	Typ
Film_ID (FK)	INT
Schauspieler_ID (FK)	INT

Tabelle: GENRE¶

Attribut	Typ
Genre_ID (PK)	INT
Genre_Name	VARCHAR

Tabelle: FILM_GENRE (m:n Beziehung)¶

Attribut	Typ
Film_ID (FK)	INT
Genre_ID (FK)	INT

Mermaid ER-Diagramm (konzeptionelles Modell)¶

erDiagram

    FILM {
        int Film_ID PK
        string Titel
        int Jahr
        int Regisseur_ID FK
    }

    REGISSEUR {
        int Regisseur_ID PK
        string Name
        string Telefon
    }

    SCHAUSPIELER {
        int Schauspieler_ID PK
        string Name
    }

    GENRE {
        int Genre_ID PK
        string Genre_Name
    }

    FILM_SCHAUSPIELER {
        int Film_ID FK
        int Schauspieler_ID FK
    }

    FILM_GENRE {
        int Film_ID FK
        int Genre_ID FK
    }

    REGISSEUR ||--o{ FILM : "führt"
    FILM ||--o{ FILM_SCHAUSPIELER : ""
    SCHAUSPIELER ||--o{ FILM_SCHAUSPIELER : ""
    FILM ||--o{ FILM_GENRE : ""
    GENRE ||--o{ FILM_GENRE : ""

Erklärung Beziehungen

Pfeil / Symbol	Bedeutung	Erklärung
`\|\|`	genau 1	Ein Objekt muss genau einmal vorkommen
`o\|`	0 oder 1	Optional: kann vorkommen, muss aber nicht
`\|{`	1 zu viele	Eine Entität ist mit vielen anderen verbunden
`o{`	0 bis viele	Optional viele: kann mit mehreren verbunden sein
`--`	einfache Beziehung	Verbindung ohne definierte Kardinalität
`\|\|--o{`	1 zu 0..n	Ein Objekt hat viele, aber die Gegenseite ist optional
`o{--o{`	n zu n	Viele-zu-Viele-Beziehung
`\|\|--\|\|`	1 zu 1	Beide Seiten kommen genau einmal vor

SQL-Abfragen¶

Alle Abfragen

Hier die Abfragen:

Microsoft Windows [Version 10.0.22631.6060]
(c) Microsoft Corporation. Alle Rechte vorbehalten.

C:\windows\System32>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.40 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE FilmDB;
Query OK, 1 row affected (0.04 sec)

mysql> USE FilmDB
Database changed
mysql> CREATE TABLE Regisseur (
    -> Regisseur_ID INT PRIMARY KEY,
    -> Name VARCHAR(100),
    -> Telefon VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_filmdb |
+------------------+
| regisseur        |
+------------------+
1 row in set (0.04 sec)

mysql> SHOW regisseur
    -> ^Z^C
mysql> CREATE TABLE Film (
    ->     Film_ID INT PRIMARY KEY,
    ->     Titel VARCHAR(100),
    ->     Jahr INT,
    ->     Regisseur_ID INT,
    ->     FOREIGN KEY (Regisseur_ID) REFERENCES Regisseur(Regisseur_ID)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE Hauptdarsteller (
    ->     Hauptdarsteller_ID INT PRIMARY KEY,
    ->     Film_ID INT,
    ->     Hauptdarsteller VARCHAR(100),
    ->     FOREIGN KEY (Film_ID) REFERENCES Film(Film_ID)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE Genre (
    ->     Genre_ID INT PRIMARY KEY,
    ->     Film_ID INT,
    ->     Genre VARCHAR(50),
    ->     FOREIGN KEY (Film_ID) REFERENCES Film(Film_ID)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_filmdb |
+------------------+
| film             |
| genre            |
| hauptdarsteller  |
| regisseur        |
+------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO Regisseur VALUES
    -> (1, 'Francis Ford Coppola', '0123-456789'),
    -> (2, 'Christopher Nolan', '0987-654321'),
    -> (3, 'Quentin Tarantino', '0456-789012'),
    -> (4, 'James Cameron', '0123-456789');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Film VALUES
    -> (1, 'Der Pate', 1972, 1),
    -> (2, 'Inception', 2010, 2),
    -> (3, 'Pulp Fiction', 1994, 3),
    -> (4, 'Titanic', 1997, 4),
    -> (5, 'The Dark Knight', 2008, 2);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Hauptdarsteller VALUES
    -> (1, 1, 'Marlon Brando'),
    -> (2, 1, 'Al Pacino'),
    -> (3, 2, 'Leonardo DiCaprio'),
    -> (4, 3, 'John Travolta'),
    -> (5, 3, 'Uma Thurman'),
    -> (6, 4, 'Leonardo DiCaprio'),
    -> (7, 4, 'Kate Winslet'),
    -> (8, 5, 'Christian Bale'),
    -> (9, 5, 'Heath Ledger');
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Genre VALUES
    -> (1, 1, 'Drama'),
    -> (2, 1, 'Krimi'),
    -> (3, 2, 'Sci-Fi'),
    -> (4, 2, 'Thriller'),
    -> (5, 3, 'Drama'),
    -> (6, 3, 'Krimi'),
    -> (7, 4, 'Drama'),
    -> (8, 4, 'Romantik'),
    -> (9, 5, 'Action'),
    -> (10, 5, 'Drama');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT Film.Titel
    -> FROM Film
    -> JOIN Regisseur ON Film.Regisseur_ID = Regisseur.Regisseur_ID
    -> WHERE Regisseur.Name = 'Christopher Nolan';
+-----------------+
| Titel           |
+-----------------+
| Inception       |
| The Dark Knight |
+-----------------+
2 rows in set (0.01 sec)

mysql> SELECT Regisseur.Name, COUNT(Film.Film_ID) AS AnzahlFilme
    -> FROM Regisseur
    -> LEFT JOIN Film ON Film.Regisseur_ID = Regisseur.Regisseur_ID
    -> GROUP BY Regisseur.Name;
+----------------------+-------------+
| Name                 | AnzahlFilme |
+----------------------+-------------+
| Francis Ford Coppola |           1 |
| Christopher Nolan    |           2 |
| Quentin Tarantino    |           1 |
| James Cameron        |           1 |
+----------------------+-------------+
4 rows in set (0.01 sec)

mysql> SELECT Film.Titel, COUNT(Genre.Genre_ID) AS AnzahlGenres
    -> FROM Film
    -> JOIN Genre ON Film.Film_ID = Genre.Film_ID
    -> GROUP BY Film.Film_ID
    -> HAVING COUNT(Genre.Genre_ID) > 1;
+-----------------+--------------+
| Titel           | AnzahlGenres |
+-----------------+--------------+
| Der Pate        |            2 |
| Inception       |            2 |
| Pulp Fiction    |            2 |
| Titanic         |            2 |
| The Dark Knight |            2 |
+-----------------+--------------+
5 rows in set (0.01 sec)

mysql> SELECT Film.Titel, Hauptdarsteller.Hauptdarsteller
    -> FROM Hauptdarsteller
    -> JOIN Film ON Film.Film_ID = Hauptdarsteller.Film_ID
    -> ORDER BY Film.Titel;
+-----------------+-------------------+
| Titel           | Hauptdarsteller   |
+-----------------+-------------------+
| Der Pate        | Marlon Brando     |
| Der Pate        | Al Pacino         |
| Inception       | Leonardo DiCaprio |
| Pulp Fiction    | John Travolta     |
| Pulp Fiction    | Uma Thurman       |
| The Dark Knight | Christian Bale    |
| The Dark Knight | Heath Ledger      |
| Titanic         | Leonardo DiCaprio |
| Titanic         | Kate Winslet      |
+-----------------+-------------------+
9 rows in set (0.01 sec)

mysql> SELECT Film.Titel, Regisseur.Name
    -> FROM Film
    -> JOIN Regisseur ON Film.Regisseur_ID = Regisseur.Regisseur_ID
    -> WHERE Film.Jahr = 1994;
+--------------+-------------------+
| Titel        | Name              |
+--------------+-------------------+
| Pulp Fiction | Quentin Tarantino |
+--------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Regisseur;
+--------------+----------------------+-------------+
| Regisseur_ID | Name                 | Telefon     |
+--------------+----------------------+-------------+
|            1 | Francis Ford Coppola | 0123-456789 |
|            2 | Christopher Nolan    | 0987-654321 |
|            3 | Quentin Tarantino    | 0456-789012 |
|            4 | James Cameron        | 0123-456789 |
+--------------+----------------------+-------------+
4 rows in set (0.01 sec)

mysql> SELECT Name, Telefon
    -> FROM Regisseur
    -> WHERE Telefon LIKE '0123%';
+----------------------+-------------+
| Name                 | Telefon     |
+----------------------+-------------+
| Francis Ford Coppola | 0123-456789 |
| James Cameron        | 0123-456789 |
+----------------------+-------------+
2 rows in set (0.01 sec)

mysql> SELECT Film.Titel, Genre.Genre
    -> FROM Film
    -> JOIN Genre ON Film.Film_ID = Genre.Film_ID
    -> ORDER BY Film.Titel;
+-----------------+----------+
| Titel           | Genre    |
+-----------------+----------+
| Der Pate        | Drama    |
| Der Pate        | Krimi    |
| Inception       | Sci-Fi   |
| Inception       | Thriller |
| Pulp Fiction    | Drama    |
| Pulp Fiction    | Krimi    |
| The Dark Knight | Action   |
| The Dark Knight | Drama    |
| Titanic         | Drama    |
| Titanic         | Romantik |
+-----------------+----------+
10 rows in set (0.00 sec)

mysql> SELECT Film.Titel, Genre.Genre
    -> FROM Film
    -> JOIN Genre ON Film.Film_ID = Genre.Film_ID
    -> ORDER BY Film.Genre;
ERROR 1054 (42S22): Unknown column 'Film.Genre' in 'order clause'
mysql> SELECT * FROM Film;
+---------+-----------------+------+--------------+
| Film_ID | Titel           | Jahr | Regisseur_ID |
+---------+-----------------+------+--------------+
|       1 | Der Pate        | 1972 |            1 |
|       2 | Inception       | 2010 |            2 |
|       3 | Pulp Fiction    | 1994 |            3 |
|       4 | Titanic         | 1997 |            4 |
|       5 | The Dark Knight | 2008 |            2 |
+---------+-----------------+------+--------------+
5 rows in set (0.00 sec)

mysql>

Rollenkonzept¶

Das Rollenkonzept (Role-Based Access Control, RBAC) dient dazu, Berechtigungen effizient und sicher zu verwalten:

Funktionsweise: Berechtigungen (z. B. Lesen, Schreiben, Löschen) werden nicht direkt einzelnen Benutzern zugewiesen, sondern an Rollen (z. B. „Buchhaltung“, „Administrator“, „Praktikant“) gebunden. Benutzer werden dann diesen Rollen zugeordnet.
Vorteile:
Übersichtlichkeit: Einfachere Verwaltung bei vielen Nutzern.
Sicherheit (Least Privilege): Nutzer erhalten nur die Rechte, die sie für ihre spezifische Rolle wirklich benötigen.
Flexibilität: Verlässt ein Mitarbeiter das Unternehmen, muss nur sein Account gelöscht werden; die Rolle bleibt für den Nachfolger bestehen.

Datensicherheit¶

Ein Datensicherheitskonzept beschreibt technische und organisatorische Massnahmen zum Schutz von Daten vor Verlust, Manipulation oder unberechtigtem Zugriff.

Mögliche Konsequenzen bei Nichteinhaltung:

Rechtlich: Hohe Bussgelder (z. B. durch die DSGVO bis zu 4 % des weltweiten Jahresumsatzes).
Finanziell: Kosten für Schadensbegrenzung, Systemwiederherstellung oder Lösegelder bei Ransomware-Angriffen.
Reputation: Vertrauensverlust bei Kunden und Partnern, was zu Geschäftsaufgaben führen kann.
Betrieblich: Stillstand der Produktion oder des Dienstleistungsbetriebs durch Datenverlust.

Aufgabe 1: Alle Filme eines bestimmten Regisseurs abrufen¶

Frage: Zeigen Sie alle Filme, die von "Christopher Nolan" inszeniert wurden.

SELECT Film.Titel
FROM Film
JOIN Regisseur ON Film.Regisseur_ID = Regisseur.Regisseur_ID
WHERE Regisseur.Name = 'Christopher Nolan';

Aufgabe 2: Anzahl der Filme pro Regisseur¶

Frage: Ermitteln Sie die Anzahl der Filme, die jeder Regisseur in der Datenbank inszeniert hat.

SELECT Regisseur.Name, COUNT(Film.Film_ID) AS AnzahlFilme
FROM Regisseur
LEFT JOIN Film ON Film.Regisseur_ID = Regisseur.Regisseur_ID
GROUP BY Regisseur.Name;

Aufgabe 3: Filme mit mehreren Genres¶

Frage: Listen Sie alle Filme auf, die mehr als ein Genre haben. Geben Sie den Titel des Films und die Anzahl der Genres an.

SELECT Film.Titel, COUNT(Genre.Genre_ID) AS AnzahlGenres
FROM Film
JOIN Genre ON Film.Film_ID = Genre.Film_ID
GROUP BY Film.Film_ID
HAVING COUNT(Genre.Genre_ID) > 1;

Aufgabe 4: Hauptdarsteller nach Film sortiert¶

Frage: Zeigen Sie alle Hauptdarsteller zusammen mit dem Titel des Films, in dem sie mitspielen, und sortieren Sie das Ergebnis nach Film.

SELECT Film.Titel, Hauptdarsteller.Hauptdarsteller
FROM Hauptdarsteller
JOIN Film ON Film.Film_ID = Hauptdarsteller.Film_ID
ORDER BY Film.Titel;

Aufgabe 5: Filme aus einem bestimmten Jahr¶

Frage: Finden Sie alle Filme, die im Jahr 1994 veröffentlicht wurden, und geben Sie den Titel und den Regisseur an.

SELECT Film.Titel, Regisseur.Name
FROM Film
JOIN Regisseur ON Film.Regisseur_ID = Regisseur.Regisseur_ID
WHERE Film.Jahr = 1994;

Aufgabe 6: Regisseure mit Kontaktinformationen¶

Frage: Listen Sie alle Regisseure auf, deren Telefonnummer mit "0123" beginnt, und geben Sie deren Namen und Telefonnummer an.

SELECT Name, Telefon
FROM Regisseur
WHERE Telefon LIKE '0123%';

Aufgabe 7: Filme und deren Genres¶

Frage: Zeigen Sie alle Filme zusammen mit ihren Genres an. Geben Sie den Titel des Films und das Genre aus.

SELECT Film.Titel, Genre.Genre
FROM Film
JOIN Genre ON Film.Film_ID = Genre.Film_ID
ORDER BY Film.Titel;

NewSQL vs NoSQL¶

NewSQL ist ein moderner Ansatz, der versucht, die Vorteile beider Welten zu vereinen:

Herkömmliche Relationale Datenbanken (SQL): Fokus auf ACID-Konformität (Datenkonsistenz) und komplexe Abfragen, stoßen aber bei der Skalierbarkeit an Grenzen (meist nur vertikal).
NoSQL: Fokus auf horizontale Skalierbarkeit und Flexibilität für unstrukturierte Daten, opfert dafür oft die sofortige Konsistenz (BASE-Prinzip).
NewSQL: Kombiniert die ACID-Garantien und die SQL-Abfragesprache der relationalen Systeme mit der horizontalen Skalierbarkeit von NoSQL. Sie sind ideal für Anwendungen, die sowohl hohe Transaktionsraten als auch absolute Datengenauigkeit erfordern.