Datenbanken mit einem DBMS erstellen

Info

Wir werden heute eine Datenbank für einen Coffee-Shop erstellen. Dieser soll das folgende ER-Modell besitzen:

info:sek-ii:q2:sql:lektion5gk-erd.png

Aufgabe 1: Relationenmodell erstellen

Erstellt das zugehörige Relationenmodell.

Lösung zu Aufgabe 1

Kunden(ID, Name, Adresse)
Produkte(ID, Name, Preis)
Bestellungen(ID, ↑KundenID, Datum, Rabatt)
BestellPosten(↑BestellungID, ↑ProduktID, Anzahl)

Aufgabe 2: Neue Datenbank erstellen

Startet SQLite Studio und erstellt mit dem -Knopf die neue Datenbank CoffeeShop.sqlite3:
Verbindet die Datenbank durch einen Doppelklick:

Aufgabe 3: Erste Tabelle erzeugen

Erstellt mit dem -Knopf eine neue Tabelle "Kunden".
Erstellt mit dem -Knopf eine neue Spalte "ID" mit dem Datentypen INTEGER und den Constraints PRIMARY KEY und NOT NULL:
Erstellt ebenso "Name" und "Adresse" jeweils als TEXT mit dem Constraint NOT NULL:
Speichert die neue Tabelle mit dem -Knopf. Euch wird der SQL-Code zum erstellen angezeigt. Versucht den Code zu verstehen:

CREATE TABLE Kunden (
    ID      INTEGER PRIMARY KEY NOT NULL,
    Name    TEXT    NOT NULL,
    Adresse TEXT    NOT NULL
);

Aufgabe 4: Restliche "normale" Tabellen erzeugen

Erstellt die Tabelle Produkte

ID INTEGER NOT NULL PRIMARY KEY
Name TEXT NOT NULL
Preis DECIMAL(10,2) NOT NULL

CREATE TABLE Produkte (
    ID    INTEGER         PRIMARY KEY NOT NULL,
    Name  TEXT            NOT NULL,
    Preis DECIMAL (10, 2) NOT NULL
);

Erstellt die Tabelle Bestellungen

ID INTEGER NOT NULL PRIMARY KEY
KundenID INTEGER NOT NULL FOREIGN KEY Kunden(ID)
Datum DATETIME NOT NULL
Rabatt INTEGER NOT NULL

CREATE TABLE Bestellungen (
    ID       INTEGER  PRIMARY KEY NOT NULL,
    KundenID INTEGER  REFERENCES Kunden (ID) NOT NULL,
    Datum    DATETIME NOT NULL,
    Rabatt   INTEGER  NOT NULL
);

Aufgabe 5: Kreuztabelle zwischen Bestellung und Produkt erstellen

Erstellt die Kreuztabelle BestellPosten
- BestellungID INTEGER NOT NULL FOREIGN KEY Bestellungen(ID) (ohne PRIMARY KEY!)
- ProduktID INTEGER NOT NULL FOREIGN KEY Produkte(ID) (ohne PRIMARY KEY!)
- Anzahl INTEGER NOT NULL
Erstellt mit dem -Knopf einen zusammengesetzten Primärschlüssel aus BestellungID und ProduktID:

CREATE TABLE BestellPosten (
    BestellungID INTEGER REFERENCES Bestellungen (ID) NOT NULL,
    ProduktID    INTEGER REFERENCES Produkte (ID) NOT NULL,
    Anzahl       INTEGER NOT NULL,
    PRIMARY KEY (BestellungID, ProduktID)
);

Aufgabe 6: Daten einfügen

Fügt die folgenden Daten in die Tabelle Kunden ein:
Fügt die folgenden Daten in die Tabelle Produkte ein:
Fügt die folgenden Daten in die Tabelle Bestellungen ein:
Fügt die folgenden Daten in die Tabelle BestellPosten ein:

Lösung zu Aufgabe 6

INSERT INTO Kunden (ID, Name, Adresse) VALUES
    (1, 'Hans Wurst', 'Baumstraße 1, 12345 Baumhausen'),
    (2, 'Max Mustermann', 'Musterweg 2, 54321 Musterdorf'),
    (3, 'Gerlinde Gutfrau', 'Dorfallee 3, 51423 Stadt am Dorf');
 
INSERT INTO Produkte (ID, Name, Preis) VALUES
    (1, 'Müsli', 9.99),
    (2, 'O-Saft', 3.5),
    (3, 'Rührei mit Speck', 6.75), 
    (4, 'Wurstbrot', 7),
    (5, 'Kaffee', 4);
 
INSERT INTO Bestellungen (ID, KundenID, Datum, Rabatt) VALUES
    (1, 1, '2021-06-13 09:00', 10),
    (2, 2, '2021-06-13 09:03', 10), 
    (3, 3, '2021-06-13 09:09', 0);
 
INSERT INTO BestellPosten (BestellungID, ProduktID, Anzahl) VALUES
    (1, 2, 1),
    (1, 1, 1),
    (1, 5, 2),
    (2, 3, 1),
    (2, 5, 4),
    (3, 5, 1),
    (3, 4, 1);

Aufgabe 7: Abfragen erstellen

Erstellt mit dem -Knopf eine neue Abfrage:
Gebt eine Kostenübersicht pro Bestellung aus: Der Name und die Adresse des Kunden sowie die Anzahl der Produkte und Summe der Rechnung sollen dargestellt werden:
Passe die Datenbank an.
- Gerlinde Gutfrau ist umgezogen. Sie wohnt jetzt in der Bahnhofsstraße 1, 32145 Gleisdorf.
- Max Mustermann bestellt noch einen Kaffee.
- Hans Wurst hatte keinen O-Saft bestellt. Die Kellnerin nimmt diesen Posten von der Rechnung runter.
- Fügt eigene Daten ein (eigene Kunden, Produkte und Bestellungen).
Erstellt weitere, sinnvolle Abfragen (zunächst als Text, dann mit SQL).
Exportiert eure Datenbanken als CSV-Dateien und importiert sie dann wieder.

Lösung zu Aufgabe 7

SELECT
    kunden.name AS Kunde,
    kunden.adresse AS Adresse,
    COUNT(*) AS AnzahlProdukte,
    round(
        SUM(bestellposten.anzahl * produkte.preis) * (100 - bestellungen.rabatt) / 100,
        2
    ) AS Kosten
FROM
    kunden,
    produkte,
    bestellungen,
    bestellposten
WHERE
    bestellungen.kundenid = kunden.id
    AND bestellposten.bestellungid = bestellungen.id
    AND bestellposten.produktid = produkte.id
GROUP BY
    bestellungen.id