Überführung des ERM in SQL - HackMD

<style> .reveal section img{ border: 0; box-shadow: none; } </style> ## Praxis ### Überführung eines ERM in eine MYSQL-Datenbanken --- ### Agenda * Normalformen 0NF - 3NF * Analyse des ERMs in 3NF * Überführung eines ERM in MySQL * Tabellen verknüpfen über Referenzen  * DB Import/Export über DUMP * Einfache Entwicklungsumgebung in Docker --- ### Normalformen 0NF - 3NF #### Wiederholung ---- #### Die "0. Normalform" ![](https://i.imgur.com/Y2LQHot.png) ##### Überführung in die 1. Normalform - Was ist zu tun? <p style="font-size: 20px;"> 👉 Jede der Spalten einer Tabelle enthält unteilbare (atomare) Informationen. Ein Attributwert ist atomar, wenn er nicht aus mehreren Werten zusammengesetzt ist.</p> <p style="font-size: 20px;"> 👉 Die Datensätze verwenden keine Informationen, die nicht auch separat dargestellt werden könnten (elementar).</p> ---- #### Die 1. Normalform ![](https://i.imgur.com/bMSGD9Y.png) :::info <p style="font-size: 16px;"> 👉 : Eine Relation ist in der ersten Normalform, wenn jeder Attributwert atomar und elementar ist.</p> ::: ---- #### Die 2. Normalform ![](https://i.imgur.com/4RC7Ihc.png =600x) :::info <p style="font-size: 16px;"> 👉 Eine Tabelle befindet sich in der zweiten Normalform, wenn sie schon in der 1. Normalform ist und jedes nicht zum ID-Schlüssel gehörende Attribut voll vom ID-Schlüssel abhängig ist. Es können sich also nur Tabellen mit zusammengesetzten ID-Schlüsseln in der 2. Normalform befinden.</p> ::: ---- #### Die 3. Normalform ![](https://i.imgur.com/vqPlNYe.png =600x) :::info <p style="font-size: 20px;"> 👉 : Eine Relation ist in der dritten Normalform, wenn Sie in der Zweiten Normalform ist und jedes Nicht-Schlüssel-Attribut von keinem Schlüsselkandidaten transitiv abhängig ist.</p> ::: ---- #### Abbildung der Tabellen in der 3NF in einem ERD ![](https://i.imgur.com/b2OUEVT.png) ---- ##### Die 3. Normalform im Detail (Ausgangssituation) <p style="font-size: 17px;"><b>Alternative Formulierung</b><br> Die <b>dritte Normalform</b> ist erreicht, wenn sich das Relationenschema in 2NF befindet, und kein Nichtschlüsselattribut (hellgraue Zellen in der Tabelle) Determinante ist.</p> <p style="font-size: 17px;"><b>Oder:</b><br> Die dritte Normalform ist erreicht, wenn sich das Relationenschema in 2NF befindet, und kein Nichtschlüsselattribut (hellgraue Zellen in der Tabelle) von einem anderen Nichtschlüsselattribut funktional abhängig ist.</p> <p style="font-size: 17px;"><b> Negativbeispiel: 3NF verletzt</b></p> ![](https://i.imgur.com/HNdhHsM.png) <p style="font-size: 17px;">Offensichtlich lässt sich der Albumtitel einer CD aus der CD_ID bestimmen, das Gründungsjahr der Band/Interpreten hängt wiederum vom Interpreten und damit transitiv von der CD_ID ab. Das Problem ist hierbei wieder Datenredundanz. Wird zum Beispiel eine neue CD mit einem existierenden Interpreten eingeführt, so wird das Gründungsjahr redundant gespeichert</p> ---- ##### Die 3. Normalform im Detail (Lösung) ![](https://i.imgur.com/XYJGexO.png) <p style="font-size: 16px;">Diese Lösung gilt nur, wenn man davon ausgeht, dass der Interpret weltweit eindeutig ist. Ansonsten müsste man eine synthetische ID in der Tabelle Künstler hinzufügen, die dann den Fremdschlüssel in der Tabelle CD stellt, wie folgt:</p> ![](https://i.imgur.com/zosjdE1.png) <p style="font-size: 16px;">Die Relation wird aufgeteilt, wobei die beiden voneinander abhängigen Daten in eine eigene Tabelle ausgelagert werden. Der Schlüssel der neuen Tabelle muss als Fremdschlüssel in der alten Tabelle erhalten bleiben. An der Tabelle „Lied“ wurden keine Änderungen bei der Übertragung in die 3. Normalform vorgenommen. Sie ist hier nur der Vollständigkeit halber gelistet.</p> --- ![SQL](https://media.giphy.com/media/vISmwpBJUNYzukTnVx/giphy.gif) #### **S**tructured **Q**uery **L**anguage --- ### SQL ist ein Standard - <p style="font-size: 26px;">SQL ist eine Datenbanksprache zur Definition von Datenstrukturen in relationalen Datenbanken sowie zum Bearbeiten (Einfügen, Verändern, Löschen) und Abfragen von darauf basierenden Datenbeständen. Die Sprache basiert auf der relationalen Algebra, ihre Syntax ist relativ einfach aufgebaut und semantisch an die englische Umgangssprache angelehnt.</p> ###### Quelle: [Wikipedia](https://de.wikipedia.org/wiki/SQL) ---- ### DDL / DML / DCL => SQL ![](https://i.imgur.com/n6wMCiG.png) ---- ### SQL und CRUD Die vier Standardfuntkionen/Befehle sind: | CRUD | SQL | | ---------- | ------ | | **C**reate | INSERT | | **R**ead | SELECT | | **U**pdate | UPDATE | | **D**elete | DELETE | --- ### Überführung eines ERMs in MySQL ![](https://i.imgur.com/b2OUEVT.png) ---- ##### 0. Allgemein: Show Befehle + USE Datebase <br> ```sql show databases; show tables; show CREATE DATABASE Name; show CREATE Table Name; ``` <br> <br> ```sql use Databasename; ``` <br> ---- ##### 1. Allgemein: Wie wird eine Datenbank in MySQL angelegt? <br> ```sql CREATE DATABASE IF NOT EXISTS `RECH_DB` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; ``` <br> ```sql drop database RECH_DB; ``` <br><br> :::info ###### DDL - Data Definition Language ::: <br> ---- #### 2. Analyse des ERM und daraus die benötigten Tabellen ableiten und anlegen ![](https://i.imgur.com/UqibWGf.png) ---- #### 2.1 Anlegen der Tabelle Artikel und Kunde: ```sql CREATE TABLE `Artikel` ( `ArtNr` int(11) NOT NULL, `ArtHersteller` varchar(120) COLLATE utf8_bin NOT NULL, `ArtGroesse` varchar(120) COLLATE utf8_bin NOT NULL, `ArtName` varchar(120) COLLATE utf8_bin NOT NULL, `ArtPreis` double NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ``` #### 2.2 Tabelle Kunden ```sql CREATE TABLE `Kunden` (`KdNr` INTEGER PRIMARY KEY,`Anrede` TEXT,`Titel` TEXT,`Vorname` TEXT,`Nachname` TEXT,`Geburtsdatum` TEXT,`Straße` TEXT,`Hausnummer` TEXT,`Postleitzahl` TEXT,`Stadt` TEXT,`Telefon` TEXT,`Mobil` TEXT,`Telefax` TEXT,`EMail` TEXT,`Newsletter` TEXT,`Eintragsdatum` TEXT,`Bundesland` TEXT,`Gender` TEXT)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ``` ---- #### 2.3 Anlegen der Tabelle Rechnung und Artikelbestellung: ```sql CREATE TABLE `Rechnung` ( `RechNr` int(11) NOT NULL, `RechDat` date NOT NULL, `KdNr` int(11) NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ``` ```sql INSERT INTO `Rechnung` (`RechNr`, `KndNr`, `RechDat`) VALUES ('999', '999', '2022-05-05'); ``` #### 2.4 Hilfstabelle Artikelbestellung: ```sql CREATE TABLE `Artikelbestellung` ( `RechNr` int(11) NOT NULL, `ArtikelNr` int(11) NOT NULL, `position` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ``` --- ### 3. Tabellen Verknüpfen über Referenzen ##### Anlegen von Keys und Abhängigkeiten * Primary Key * Auto Increment * Foreign Key * Zusammengesetzter Primary Key * Beziehungen ---- #### 3.1 Primary Key konfigurieren ```sql BSP: ALTER TABLE TableName ADD CONSTRAINT Attribut PRIMARY KEY (Attribut); ALTER TABLE Tabel Name DROP PRIMARY KEY; ``` ```sql ALTER TABLE Kunden ADD CONSTRAINT KundenNr PRIMARY KEY (KdNr); ``` ```sql ALTER TABLE Artikel ADD CONSTRAINT ArtikelNr PRIMARY KEY (ArtNr); ``` ```sql ALTER TABLE Rechnung ADD CONSTRAINT RechNr PRIMARY KEY (RechNr); ``` ---- #### 3.2 AUTO_INCREMENT ```sql BSP: ALTER TABLE Tabelname CHANGE Attribut Attribut INT(10)AUTO_INCREMENT; ``` ```sql ALTER TABLE Rechnung CHANGE RechNr RechNr INT(10)AUTO_INCREMENT; ``` ```sql ALTER TABLE Artikel CHANGE ArtNr ArtNr INT(10)AUTO_INCREMENT; ``` ```sql ALTER TABLE Kunden CHANGE KdNr KdNr INT(10)AUTO_INCREMENT; ``` ```sql ALTER TABLE Tablename DROP PRIMARY KEY; ``` ---- #### 3.3 Foreign Key setzen in der Tabelle Rechnung ```sql BSP: ALTER TABLE Tablename ADD FOREIGN KEY(Attribut) REFERENCES Tablename(PRIK); ``` ```sql ALTER TABLE Rechnung ADD FOREIGN KEY(KdNr) REFERENCES Kunden(KdNr); ``` ---- #### 3.4 Setzen der FOREIGN KEYs in der "Hilfstabelle" Artikelbestellung (n:m) ```sql ALTER TABLE Artikelbestellung ADD FOREIGN KEY(RechNr) REFERENCES Rechnung(RechNr); ``` ```sql ALTER TABLE Artikelbestellung ADD FOREIGN KEY(ArtikelNr) REFERENCES Artikel(ArtNr); ``` ```sql Alter Table Artikelbestellung DROP Foreign Key Artikelbestellung_ibfk_1; ``` ---- #### 3.5 Zusammengesetzter Primary Key (PRIK) <br> ```sql ALTER TABLE Artikelbestellung ADD CONSTRAINT PK_Artikelbestellung PRIMARY KEY (ArtikelNr, RechNr); ALTER TABLE Artikelbestellung ADD CONSTRAINT PK_Artikelbestellung PRIMARY KEY (ArtikelNr, RechNr, position); ``` <br> ```sql ALTER TABLE Artikelbeschreibung DROP PK_Artikelbestellung; ``` ---- #### Damit ist das ERM in MySQL abgebildet ![ERM_SQLRally](https://hackmd.io/_uploads/ByQaP-AXa.png) --- #### Datenbank DUMP erstellen und exportieren * Allgemeiner Befehl um eine Datenbank zu speichern ```sql mysqldump -uUSER -p – all-databases > my-mysql-dump.sql ``` * MySQL Dump einer speziellen Datenbank ```sql mysqldump -uUSER -p mydatabase1 > my-mysql-dump.sql ``` ---- #### Datenbank DUMP einlesen/importieren <p style="font-size: 18px;"> * Allgemeiner Befehl um eine Datenbank zu speichern </p> ```sql mysql -uUSER -p Datenbankname < my-mysql-dump.sql ``` <p style="font-size: 18px;"> Wenn noch keine Datenbank angelegt ist dann zuerst erstellen mit: </p> ```sql CREATE DATABASE IF NOT EXISTS `RECH_DBxyz_v1` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; ``` <p style="font-size: 18px;"> Mit folgendem Befehl kann der DUMP eingelesen werden </p> ```sql mysql -uroot -p test < 20211020_2326_RECH_DBxyz.sql ``` --- #### Weitere hilfreiche MySQL Befehle ```sql use database name; // Wechseln zu einer Datenbank show databases; // Datenbanken anzeigen show tables; // Tabellen einer Datenbank anzeigen describe Tabellenname; // Ausführliche Beschreibung/Anzeige einer Tabelle drop database Datenbankname; // Eine Datenbank löschen ALTER TABLE tablename ENGINE=INNODB"; // Änderungen an der Datenbank durchführen - ENGINE anpassen show create table Tabellenname; // Anzeige was mit create table erzeugt wurde ``` --- #### INSERT INTO Befehl ```sql INSERT INTO Kunde (Kunde.KdName, Kunde.KdStrasse, Kunde.PLZ, Kunde.Ort) VALUES('Peter Pan', 'Peterstrasse 5', '12345', 'Panama Stadt'); ``` ```sql select * from Kunde; ``` ```sql INSERT INTO Artikel (ArtName, ArtPreis) VALUES('Fussball', 39.90); ``` ```sql select * from Artikel; ``` ---- #### Rechnung anlegen in der Tabelle Rechnung und Artikel zuweisen ```sql insert into Rechnung (RechDat, KdNr) VALUES('2021-10-01',5); ``` ```sql insert into Artikelbestellung (RechNr, KdNr, Menge) VALUES(1000,25,1) ``` <p style="font-size: 18px;"> <b>Frage:</b> Warum kommt es bei diesem Insert möglicherweise zu einem Fehler? </p> --- #### Datenbankabfragen - SELECT Statement ```sql Select * from tablename; ``` ```sql Select * from tablename where bedingung; ``` <p style="font-size: 18px;"> <b>Beispiel:</b> Ein einfacher "Select-Befehl" mit einer Bedingung </p> ```sql Select * from Kunde where Kunde.Ort like "Muenchen"; ``` <p style="font-size: 18px;"> <b>Hinweis:</b> Bedingungen können über Vergleichsoperatoren realiseirt werden: </p> <ul> <li><code> = </code></li> <li><code> < </code></li> <li><code> > </code></li> <li><code> like </code></li> <li><code> IN </code></li> </ul> ---- #### Datenbankabfragen - SELECT Statement - Bedingungen und Verknüpfungen ```sql Select Rechnung.RechNr, Rechnung.KdNr, Rechnung.RechDat, Kunde.Vorname,Kunde.Name, Kunde.Strasse, Kunde.PLZ, Kunde.Ort from Rechnung, Kunde where Kunde.Ort like "Berlin" and Kunde.KdNr=Rechnung.KdNr; ``` <p style="font-size: 18px;"> Bedingen können Verknüpft werden mit AND oder OR </p> ---- #### Datenbankabfragen mit SELECT und Aggregatfunktionen ```sql Select * from tablename where bedingung; ``` <p style="font-size: 18px;"> Aggregatfunktionen <ul> <li>max()</li> <li>min()</li> <li>avg()</li> <li>count()</li> <li>sum()</li> </ul> </p> ```sql Select max(ArtPreis) from Artikel; ``` <p style="font-size: 18px;"> Ermittel den minimalen Preis </p> ```sql= ??? ``` ---- #### Datenbankabfragen Aggregatfunktion + Sub Select <p style="font-size: 18px;"> <b>Aufgabe:</b> Selektiere den minimalen Preis von der Tabelle Artikel </p> ```sql SELECT MIN(ArtPreis) FROM Artikel; ``` <p style="font-size: 18px;"> <b>Aufgabe:</b> Selektiere den minimalen Wert mit allen Attributen der Tabelle Artikel </p> ```sql Select * from Artikel WHERE ArtPreis = (SELECT MAX(ArtPreis) FROM Artikel); ``` ---- #### Datenbankabfrage mit Sub Select - weiteres Beispiel ```sql Select RechNr, CNT from (Select RechNr, Count(ArtikelNr)AS CNT from Artikelbestellung GROUP BY RechNr) AS R1 where R1.CNT=4; ``` ---- ### Transaction ```sql START TRANSACTION; INSERT INTO Kunde (Kunde.KdName, Kunde.KdStrasse, Kunde.PLZ, Kunde.Ort) VALUES('Petra Pan', 'Peterstrasse 5', '32413', 'Buxtehude'); SELECT LAST_INSERT_ID(); COMMIT; ``` ---- ### Joins ![](https://i.imgur.com/qgROq51.png) ```sql SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; ``` --- ### Lösungen Übungsklausur ```text Aufgabe 1 (Datenbankmodellierung): 30 Punkte Ihr Unternehmen hat den Auftrag erhalten, für kriminalistische Zwecke eine Datenbank zu erstellen, in der die Daten von Vorgängen gespeichert werden sollen. Bisher wurden diese Daten in einer Excel-Liste geführt (siehe folgende Tabelle, aus datenschutzrechtlichen Gründen, sind die personenbezogenen Daten wie der Name geschwärzt): ``` ``` 1. Welche Normalform ist in der Tabelle verletzt? Erläutern Sie die damit verbundenen Probleme (5 Punkte). ``` ``` zu 1. Die erste Normalform ist verletzt. Die Daten sind nicht atomar gespeichert. Dadurch ist der Aufwand bei Abfragen sehr komplex und würde lange dauern. Z.B. die suche nach Personen aus einem bestimmten Ort. Das wäre sehr kompliziert. ---- #### Aufgabe 1 fortsetzung ``` 2. Überführen Sie die Excel-Tabelle in die dritte Normalform. (insg. 20 Punkte) a. Erstellen Sie das relationale Datenbankmodell in der dritten Normalform. (12 Punkte) Hinweis: Es müssen keine Datensätze eingetragen werden. b. Kennzeichnen die Primärschlüssel (PK) und die Fremdschlüssel (FK). (8 Punkte ``` ``` 3. Bestimmen Sie geeignete Datentypen für folgende Attribute: (5 Punkte) a. Ort b. Postleitzahl c. Anrede d. VorgangstID e. Geburtsdatum ``` ``` varchar(120) - varchar(5) - varchar(10) - int - date ---- ### Aufgabe 2 ``` 1. ``` ---- ### Entwicklungsumgebung aufsetzen * XAMPP installieren Tutorial XAMPP unter Windows * [Link](https://praxistipps.chip.de/xampp-installieren-und-einrichten-ein-tutorial_32258) * Docker Umgebung bestehend aus einer MySQL und phpMyAdmin ---- :::info #### Auftrag: Entwicklungsumgebung mit MySQL und phpMyAdmin in Docker aufsetzen ::: #### Vorbereitung ##### [Install Docker BSP: Ubuntu 20.04](https://linuxize.com/post/how-to-install-and-use-docker-on-ubuntu-20-04/) ##### [Install Docker-Compose BSP: Ubuntu 20.04](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-docker-compose-on-ubuntu-20-04) :::warning ##### Die Vorbereitung ist nötig und muss auch in anderen Betriebssystemen z.B. Windows oder OSx erfolgen ::: ---- :::info #### Docker Container mit Docker-Compose aufsetzen ::: ##### **Step 1:** Download "docker_compse.yml" ###### Im Klassenkurs unter Lernfeld 5 findet ihr die Docker_compose.yml ![](https://i.imgur.com/ikbJIxj.png) ---- ---- :::info #### Docker Container mit Docker-Compose aufsetzen ::: ##### **Step 2:** Legt einen Ordner an zum Beispiel: <pre><code> mkdir docker_projects</pre></code> ##### **Step 3:** Erstellt die Datei docker_compose.yml in dem angelegten Ordner <pre><code> vim docker_compose.yml </pre></code> ---- :::info #### Docker Container mit Docker-Compose aufsetzen ::: ##### **Step 4:** Starten der Container <pre><code> docker-compose up -d </pre></code> ---- :::info #### Docker Container mit Docker-Compose aufsetzen ::: ##### **Step 5:** phpMyAdmin starten http://localhost:8081 ![](https://i.imgur.com/yUjR7pR.png =250x) ---- * Tabellen Verknüpfen über Referenzen  * DB Import/Export über DUMP * Einfache Entwicklungsumgebung in DockerINSERT INTO `Rechnung` (`RechNr`, `KndNr`, `RechDat`) VALUES ('999', '999', '2022-05-05'); 2.4 Hilfstabelle Artikelbestellung: CREATE TABLE `Artikelbestellung` ( `RechNr` int(11) NOT NULL, `ArtikelNr` int(11) NOT NULL, `position` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 3. Tabellen Verknüpfen über Referenzen Anlegen von Keys und Abhängigkeiten Primary Key Auto Increment Foreign Key Zusammengesetzter Primary Key Beziehungen 3.1 Primary Key konfigurieren BSP: ALTER TABLE TableName ADD CONSTRAINT Attribut PRIMARY KEY (Attribut); ALTER TABLE Tabel Name DROP PRIMARY KEY; ALTER TABLE Kunden ADD CONSTRAINT KundenNr PRIMARY KEY (KdNr); ALTER TABLE Artikel ADD CONSTRAINT ArtikelNr PRIMARY KEY (ArtNr); ALTER TABLE Rechnung ADD CONSTRAINT RechNr PRIMARY KEY (RechNr); Datenbank DUMP erstellen und exportieren Allgemeiner Befehl um eine Datenbank zu speichern mysqldump -uUSER -p – all-databases > my-mysql-dump.sql MySQL Dump einer speziellen Datenbank mysqldump -uUSER -p mydatabase1 > my-mysql-dump.sql 6 . 6 2.3 Anlegen der Tabelle Rechnung und Artikelbestellung: CREATE TABLE `Rechnung` ( `RechNr` int ( 11 ) NOT NULL , `RechDat` date NOT NULL , `KdNr` int ( 11 ) NOT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; INSERT INTO `Rechnung` (`RechNr`, `KndNr`, `RechDat`) VALUES ( '999' , '999' , '2022-05-05' ); 2.4 Hilfstabelle Artikelbestellung: CREATE TABLE `Artikelbestellung` ( `RechNr` int ( 11 ) NOT NULL , `ArtikelNr` int ( 11 ) NOT NULL , `position` int ( 11 ) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; mysqldump -u root -p --databases db_3nf > backup_db_3nf.sql use database; show databases; show tables; describe Artikel; drop database RECH_DAT; ALTER TABLE tablename ENGINE=INNODB"; show create table Rechnung; ----- ### Weitere Begriffe #### InnoDB-Engine Die **InnoDB-Engine** in MySQL ist eine Speicher-Engine, die in MySQL verwendet wird. Sie verhält sich wie ein Modul, das flexibel in die Software integriert werden kann . Die InnoDB-Engine bietet verschiedene Funktionen, darunter Transaktionen, Fremdschlüssel, zeilenbasiertes Sperren und Crash-Recovery-Mechanismen **Transaktionen:** InnoDB unterstützt Transaktionen, die es ermöglichen, mehrere Datenbankoperationen als eine Einheit zu gruppieren. Dadurch wird sichergestellt, dass entweder alle Operationen erfolgreich abgeschlossen werden oder keine von ihnen auf die Datenbank angewendet wird. **Fremdschlüssel:** InnoDB unterstützt Fremdschlüssel, die verwendet werden, um Beziehungen zwischen Tabellen in einer Datenbank herzustellen. Fremdschlüssel gewährleisten die referentielle Integrität und stellen sicher, dass die Daten in den verknüpften Tabellen konsistent bleiben. **Zeilenbasiertes Sperren:** InnoDB verwendet zeilenbasiertes Sperren, was bedeutet, dass nur die betroffenen Zeilen gesperrt werden, während andere Transaktionen gleichzeitig auf andere Zeilen zugreifen können. Dies verbessert die Parallelität und reduziert die Wahrscheinlichkeit von Konflikten und Deadlocks in einer Multi-User-Umgebung. **Crash-Recovery:** InnoDB bietet Mechanismen zur Crash-Wiederherstellung, um die Datenkonsistenz im Falle eines Systemausfalls oder eines unerwarteten Herunterfahrens sicherzustellen. Dabei wird ein Transaktionsprotokoll (auch als Redo-Log bezeichnet) verwendet, um die Änderungen an der Datenbank zu speichern. Während der Wiederherstellung werden die Änderungen aus dem Protokoll angewendet, um die Datenbank in einen konsistenten Zustand zu bringen . Die InnoDB-Engine ist standardmäßig in MySQL aktiviert und wird in vielen Anwendungen und Umgebungen aufgrund ihrer Zuverlässigkeit, Leistung und Unterstützung der ACID-Eigenschaften (Atomicity, Consistency, Isolation, Durability) eingesetzt #### Constraints **Constraints** in MySQL dienen dazu, Regeln für Daten in einer Tabelle festzulegen. Sie werden verwendet, um den Typ der Daten einzuschränken, die in eine Tabelle eingefügt werden können, um die Genauigkeit und Zuverlässigkeit der Daten sicherzustellen. Constraints können auf Spalten- oder Tabellenebene angewendet werden. Hier sind einige häufig verwendete Constraints in MySQL: NOT NULL: Stellt sicher, dass eine Spalte keinen NULL-Wert haben kann. UNIQUE: Stellt sicher, dass alle Werte in einer Spalte unterschiedlich sind. PRIMARY KEY: Erzwingt Eindeutigkeit und Nicht-NULL-Werte für eine Spalte oder eine Kombination von Spalten. Es bietet auch eine schnelle Suche nach Zeilen. FOREIGN KEY: Etabliert eine Beziehung zwischen zwei Tabellen, indem der Primärschlüssel einer Tabelle als Fremdschlüssel in einer anderen Tabelle referenziert wird. Es stellt die referentielle Integrität sicher. CHECK: Definiert eine Bedingung, die für die Daten in einer Spalte erfüllt sein muss. DEFAULT: Legt einen Standardwert für eine Spalte fest, wenn kein Wert während einer Einfügeoperation angegeben wird. Constraints können bei der Erstellung einer Tabelle mit dem CREATE TABLE-Statement angegeben oder später mit dem ALTER TABLE-Statement hinzugefügt werden. Es ist wichtig zu beachten, dass Constraints eine wichtige Rolle bei der Aufrechterhaltung der Datenintegrität und der Durchsetzung von Beziehungen zwischen Tabellen spielen. Sie helfen sicherzustellen, dass die in der Datenbank gespeicherten Daten korrekt und konsistent sind. #### Relationale Datenbanken Ja, relationale Datenbanken sind immer SQL-Datenbanken. Eine relationale Datenbank speichert Daten im tabellarischen Format mit Zeilen und Spalten und verwendet das relationale Modell zur Darstellung der Daten. Die Daten in einer relationalen Datenbank werden mithilfe der Datenbanksprache SQL (Structured Query Language) manipuliert und abgefragt SQL ist eine Programmiersprache, die speziell für relationale Datenbanken entwickelt wurde. Sie ermöglicht das Erstellen, Ändern und Abfragen von Daten in einer relationalen Datenbank [Quelle:](https://www.bigdata-insider.de/was-ist-eine-relationale-datenbank-a-643028/) --- # Datenbank anlegen --- ```sql CREATE TABLE `Artikel` ( `ArtNr` int(10) NOT NULL, `ArtHersteller` varchar(120) NOT NULL, `ArtGroesse` varchar(120) NOT NULL, `ArtName` varchar(120) NOT NULL, `ArtPreis` double NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; CREATE TABLE `Kunden` ( `KdNr` int(10) NOT NULL, `Anrede` text DEFAULT NULL, `Titel` text DEFAULT NULL, `Vorname` text DEFAULT NULL, `Nachname` text DEFAULT NULL, `Geburtsdatum` text DEFAULT NULL, `Straße` text DEFAULT NULL, `Hausnummer` text DEFAULT NULL, `Postleitzahl` text DEFAULT NULL, `Stadt` text DEFAULT NULL, `Telefon` text DEFAULT NULL, `Mobil` text DEFAULT NULL, `Telefax` text DEFAULT NULL, `EMail` text DEFAULT NULL, `Newsletter` text DEFAULT NULL, `Eintragsdatum` text DEFAULT NULL, `Bundesland` text DEFAULT NULL, `Gender` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; CREATE TABLE `Rechnung` ( `RechNr` int(10) NOT NULL, `RechDat` date NOT NULL, `KdNr` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; CREATE TABLE `Artikelbestellung` ( `RechNr` int(11) NOT NULL, `ArtikelNr` int(11) NOT NULL, `position` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; -- -- Indizes der exportierten Tabellen -- -- -- Indizes für die Tabelle `Artikel` -- ALTER TABLE `Artikel` ADD PRIMARY KEY (`ArtNr`); -- -- Indizes für die Tabelle `Artikelbestellung` -- ALTER TABLE `Artikelbestellung` ADD PRIMARY KEY (`ArtikelNr`,`RechNr`,`position`), ADD KEY `RechNr` (`RechNr`); -- -- Indizes für die Tabelle `Kunden` -- ALTER TABLE `Kunden` ADD PRIMARY KEY (`KdNr`); -- -- Indizes für die Tabelle `Rechnung` -- ALTER TABLE `Rechnung` ADD PRIMARY KEY (`RechNr`), ADD KEY `KdNr` (`KdNr`); -- -- AUTO_INCREMENT für exportierte Tabellen -- -- -- AUTO_INCREMENT für Tabelle `Artikel` -- ALTER TABLE `Artikel` MODIFY `ArtNr` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=500001; -- -- AUTO_INCREMENT für Tabelle `Kunden` -- ALTER TABLE `Kunden` MODIFY `KdNr` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1000000; -- -- AUTO_INCREMENT für Tabelle `Rechnung` -- ALTER TABLE `Rechnung` MODIFY `RechNr` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=90000000; -- -- Constraints der exportierten Tabellen -- -- -- Constraints der Tabelle `Artikelbestellung` -- ALTER TABLE `Artikelbestellung` ADD CONSTRAINT `Artikelbestellung_ibfk_1` FOREIGN KEY (`RechNr`) REFERENCES `Rechnung` (`RechNr`), ADD CONSTRAINT `Artikelbestellung_ibfk_2` FOREIGN KEY (`ArtikelNr`) REFERENCES `Artikel` (`ArtNr`); -- -- Constraints der Tabelle `Rechnung` -- ALTER TABLE `Rechnung` ADD CONSTRAINT `Rechnung_ibfk_1` FOREIGN KEY (`KdNr`) REFERENCES `Kunden` (`KdNr`); ``` ### File Importanweisungen: LOAD DATA INFILE 'home/Artikel.csv' into table Artikel fields terminated by ',' ENCLOSED BY '\'' ### Artikel LOAD DATA INFILE '/home/Artikel.csv' into table Artikel fields terminated by ',' ENCLOSED BY '\'' (ArtHersteller, ArtGroesse, ArtName, ArtPreis); ### Kunden LOAD DATA INFILE '/home/Kunden.csv' into table Kunden fields terminated by ',' ENCLOSED BY '\'' (Anrede, Titel,Vorname,Nachname,Geburtsdatum,Straße,Hausnummer,Postleitzahl,Stadt,Telefon,Mobil,Telefax,EMail,Newsletter,Eintragsdatum,Bundesland,Gender); ### Rechnung LOAD DATA INFILE '/home/Rechnung.csv' into table Rechnung fields terminated by ',' ENCLOSED BY '\'' ( RechDat, KdNr); ### Artikelbestellung LOAD DATA INFILE '/home/Artikelbestellung.csv' into table Artikelbestellung fields terminated by ',' ENCLOSED BY '\'' (RechNr, ArtikelNr, position); docker exec -it b1f30ee3e8c9 bin/bash ###Transactions -- 1. Trascation Rechnung erzeugen START TRANSACTION; -- 2. Varianlen Deklarieren SET @RECHDAT:=sysdate(); SET @KdNr:=1000000; SET @RECHNR := 0; SET @ArtikelNr := 1720; -- ----------------------- -- 3. Insert into Rechnung INSERT INTO Rechnung (RechDat,KdNr) VALUES(@RECHDAT,@KdNr); -- 4. Select Last RECHNr SELECT @RECHNR:=MAX(RECHNR)FROM Rechnung; SELECT @RECHNR; -- 5. Insert Artikelbestellung Insert into Artikelbestellung (RechNr, ArtikelNr, position) VALUES(@RECHNR, @ArtikelNr, 1); -- 5. commit changes COMMIT; SET autocommit = 0; INSERT INTO Rechnung (RechDat,KdNr) VALUES(sysdate(),1000000); By default, MySQL automatically commits the changes permanently to the database. To force MySQL not to commit changes automatically, you use the following statement: SET autocommit = 0; Code language: SQL (Structured Query Language) (sql) Or SET autocommit = OFF Code language: SQL (Structured Query Language) (sql) You use the following statement to enable the autocommit mode explicitly: SET autocommit = 1; Code language: SQL (Structured Query Language) (sql) Or SET autocommit = ON;