Integrität_sicherstellen¶

Bedeutung der referenziellen Integrität¶

Datenkonsistenz: Sie stellt sicher, dass die Daten in der Datenbank konsistent und logisch sind. Es kann keine Ausleihe für ein Buch geben, das nicht existiert.
Datenqualität: Sie verbessert die Datenqualität, indem sie verhindert, dass ungültige oder inkonsistente Daten in die Datenbank gelangen.
Einfache Verwaltung: Sie erleichtert die Verwaltung von Datenbeziehungen, indem sie sicherstellt, dass alle Verweise zwischen Tabellen korrekt sind.

Dieses Konzept ist nicht nur in der Schulbibliothek, sondern in vielen anderen realen Anwendungen relevant, in denen Datenbeziehungen verwaltet werden müssen, wie z.B. in Bestell- und Kundendatenbanken, Personalmanagementsystemen und vielem mehr.

ON DELETE CASCADE¶

Beschreibung: Wenn ein Datensatz in der referenzierten Tabelle gelöscht wird, werden alle Datensätze in der referenzierenden Tabelle, die auf diesen Datensatz verweisen, ebenfalls automatisch gelöscht.
Anwendung: Verhindert das Entstehen von "verwaisten" Datensätzen in der referenzierenden Tabelle, die auf nicht existierende Datensätze verweisen würden.

ON UPDATE CASCADE¶

Beschreibung: Wenn der Primärschlüssel eines Datensatzes in der referenzierten Tabelle aktualisiert wird, werden alle entsprechenden Fremdschlüsselwerte in der referenzierenden Tabelle automatisch aktualisiert.
Anwendung: Sorgt dafür, dass die referenzielle Integrität gewahrt bleibt, auch wenn sich die Primärschlüsselwerte ändern, z.B. bei Datenmigrationen oder Zusammenführungen.

Die möglichen Aktionen für ON DELETE und ON UPDATE sind:

CASCADE: Automatisches Löschen oder Aktualisieren der referenzierenden Datensätze.
SET NULL: Setzt die Fremdschlüsselwerte in den referenzierenden Datensätzen auf NULL.
SET DEFAULT: Setzt die Fremdschlüsselwerte auf einen Standardwert (nicht in allen Datenbanksystemen unterstützt).
RESTRICT: Verhindert das Löschen oder Aktualisieren, wenn es referenzierende Datensätze gibt.
NO ACTION: Ähnlich wie RESTRICT, erlaubt keine Aktion, wenn es referenzierende Datensätze gibt.

CODE der eine Datenbank erstellt:¶

CREATE DATABASE IF NOT EXISTS lernplattform;
USE lernplattform;

-- Erstellen der Tabelle "Kurse", falls sie noch nicht existiert
CREATE TABLE IF NOT EXISTS Kurse (
    KursID INT PRIMARY KEY AUTO_INCREMENT, -- Automatische ID-Vergabe
    Kursname VARCHAR(100) NOT NULL UNIQUE, -- Kursname muss eindeutig sein
    Lehrer VARCHAR(100) NOT NULL           -- Lehrername darf nicht leer sein
);

-- Erstellen der Tabelle "Einschreibungen", falls sie noch nicht existiert
CREATE TABLE IF NOT EXISTS Einschreibungen (
    EinschreibungsID INT PRIMARY KEY AUTO_INCREMENT, -- Automatische ID-Vergabe
    KursID INT NOT NULL,                             -- Fremdschlüssel zu Kurse
    StudentID INT NOT NULL,                          -- ID des Studenten
    Einschreibungsdatum DATE NOT NULL,               -- Datum der Einschreibung
    FOREIGN KEY (KursID)
        REFERENCES Kurse(KursID)
        ON DELETE CASCADE                            -- Löscht Einschreibungen, wenn Kurs gelöscht wird
        ON UPDATE CASCADE                            -- Aktualisiert KursID, wenn sie sich in Kurse ändert
);

Constraint	Tabelle	Spalte(s)	Beschreibung
PRIMARY KEY	Kurse, Einschreibungen	KursID, EinschreibungsID	Stellt sicher, dass die KursID und EinschreibungsID eindeutig und nicht NULL sind.
UNIQUE	Kurse	Kursname	Stellt sicher, dass der Kursname eindeutig ist.
NOT NULL	Kurse, Einschreibungen	Kursname, Lehrer, KursID, StudentID, Einschreibungsdatum	Stellt sicher, dass diese Spalten nicht NULL sind.
CHECK (<= CURRENT_DATE)	Einschreibungen	Einschreibungsdatum	Stellt sicher, dass das Einschreibungsdatum nicht in der Zukunft liegt.
FOREIGN KEY	Einschreibungen	KursID	Stellt sicher, dass KursID in `Einschreibungen` auf eine existierende KursID in `Kurse` verweist.
ON DELETE CASCADE	Einschreibungen	KursID	Löscht alle Einschreibungen, wenn der zugehörige Kurs gelöscht wird.
ON UPDATE CASCADE	Einschreibungen	KursID	Aktualisiert die KursID in `Einschreibungen`, wenn sie in `Kurse` geändert wird.

Reverse Engineering:¶

bei KursID wird der PRIMARY KEY nicht aufgeführt und wir wissen nicht warum

ERM für des sozialen Netzwerks¶

Friendzone¶

400.

Entität	Attribute
users	id, username, email, password, name, bio, gender, birthday, city, country, centimeters, avatar, role, is_active, remember_token, created_at, updated_at
photos	id, user_id, description, url, created_at, updated_at
tags	id, photo_id, name, created_at, updated_at
likes	id, photo_id, user_id, created_at, updated_at
follows	following_id, follower_id, created_at, updated_at
comments	id, user_id, photo_id, body, created_at, updated_at
password_resets	email, token, created_at

-- Datenbank erstellen, falls sie nicht existiert
CREATE DATABASE IF NOT EXISTS friendzone;
USE friendzone;

-- Erstellen der Tabelle "users", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    bio TEXT,
    gender VARCHAR(20),
    birthday DATE,
    city VARCHAR(100),
    country VARCHAR(100),
    centimeters INT,
    avatar VARCHAR(255),
    role VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    remember_token VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Erstellen der Tabelle "photos", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    description TEXT,
    url VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Erstellen der Tabelle "tags", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    photo_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
);

-- Erstellen der Tabelle "likes", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    photo_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Erstellen der Tabelle "follows", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS follows (
    following_id INT NOT NULL,
    follower_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (following_id, follower_id),
    FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Erstellen der Tabelle "comments", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    photo_id INT NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
);

-- Erstellen der Tabelle "password_resets", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS password_resets (
    email VARCHAR(100) NOT NULL,
    token VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (email, token)
);

-- Erstellen der Tabelle "user_follows", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS user_follows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    follower_id INT NOT NULL,
    following_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Erstellen der Tabelle "owns", falls sie nicht existiert
CREATE TABLE IF NOT EXISTS owns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    photo_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
);

Schwere Begriffe erklärt:

Begriff	Erklärung
`AUTO_INCREMENT`	Wird verwendet, um eine automatische Zählung für eine Spalte zu erzeugen. Wird in der Regel für Primärschlüssel verwendet, damit der Wert bei jedem Einfügen automatisch erhöht wird.
`BOOLEAN DEFAULT TRUE`	Definiert eine Spalte mit dem Datentyp `BOOLEAN`, wobei der Standardwert `TRUE` ist. In MySQL wird `BOOLEAN` als `TINYINT(1)` behandelt, wobei `1` für `TRUE` und `0` für `FALSE` steht.
`DATETIME DEFAULT CURRENT_TIMESTAMP`	Eine Spalte, die den aktuellen Zeitstempel der Einfügung speichert. Wenn kein Wert für diese Spalte angegeben wird, wird der aktuelle Zeitpunkt verwendet.
`DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Eine Spalte, die den aktuellen Zeitstempel speichert, wenn ein Datensatz erstellt wird. Zudem wird der Zeitstempel automatisch aktualisiert, wenn der Datensatz geändert wird.