SQL DDL & DML: Aufgaben (Lösungen)

SQL DDL & DML: Aufgaben (Lösungen)#

In diesem Tutorium üben wir die Funktionalitäten der SQL Data Description Language (DDL) und SQL Data Manipulation Language (DML). Die DDL beschreibt, welche Daten in einer Datenbank gespeichert werden. Die DML wird hingegen genutzt, um Instanzen in unserer Datenbank zu erstellen, zu verändern oder auch wieder zu löschen.

Wir benutzen hierfür die eingebettete Datenbank DuckDB, welche auch über das Python-Paket duckdb unterstützt wird. DuckDB wurde ursprünglich als Datei- oder In-Memory-basierte OLAP-Datenbank entwickelt (was OLAP heißt, sehen wir im Data Warehouse-Tutorium).

Hinweis: Aufgaben, welche durch einen Asterisk (*) markiert sind, sind Bonusaufgaben. Diese Aufgaben können im Tutorium behandelt werden, dies ist jedoch von den Übungsleitern nicht geplant.

Aufgabe 1: Geeignete Wahl von Datentypen#

Datenbanken unterstützen in der Realität nur eine limitierte Anzahl an unterschiedlichen Datentypen. Daher muss nach dem Abschluss der Modellierungsphase und der Wahl einer Datenbank-Software zunächst entschieden werden, welche Datentypen die Modellierungsergebnisse am besten abbilden.

In den folgenden drei Teilaufgaben ist jeweils eine Relation gegeben. Überlegen Sie sich zu den Attributen passende Datentypen und begründen Sie Ihre Entscheidung. Nutzen Sie dazu auch die DuckDB-Dokumentation.

Aufgabe 1.1: Kunde#

Kunde_ID

Name

Straße

Hausnummer

PLZ

Premium Kunde

Telefonnumer

Musterlösung

Musterlösung

  • Kunde_ID

    • Datentyp: UINTEGER

    • Reicht für 2³² Kunden

    • Alternativ: UBIGINT

    • Alternativ: UUID, aber schlechter lesbar

  • Name

    • Datentyp: VARCHAR(100)

    • Hinweis: Der Parameter n hat in DuckDB keinen Effekt und ist nur implementiert, um mit anderen SQL-Dialekten kompatibel zu sein.

    • Alternativ: TEXT

    • TEXT ist in DuckDB ein Alias zu VARCHAR, andere Systeme verwenden TEXT für Strings beliebiger Länge.

  • Straße

    • Datentyp: VARCHAR(80)

  • Hausnummer

    • Datentyp: VARCHAR(10)

    • Grund: Alphanumerische Hausnummern wie 25b

  • PLZ

    • Datentyp: VARCHAR(30)

    • Grund 1: z. B. Amerikanische Postleitzahlen wie NY 10019

    • Grund 2: Führende Nullen in deutschen PLZ

  • Adresse (Straße, Hausnummer, PLZ)

    • Datentyp: STRUCT(VARCHAR, VARCHAR, VARCHAR)

    • Grund: Komplexes Attribut (Zusammenfassung von Straße, Hausnummer, PLZ)

  • Premium Kunde

    • Datentyp: BOOLEAN

    • Grund: Kann auch in Prädikaten effizient genutzt werden. Die Auswertung von Textdatentypen ist alternativ relativ rechenlastig.

  • Telefonnumer

    • Datentyp: VARCHAR(30)

    • Grund 1: Führende Nullen (0176…)

    • Grund 2: Vorwahl (+49176…)

    • Alternative: Vorwahl Abspalten

Aufgabe 1.2: Buch#

ISBN

Titel

Autor

Preis

Veröffentlicht

Buchcover

Abstract

Musterlösung

Musterlösung

  • ISBN

    • Datentyp: UBIGINT/VARCHAR(17)

    • ISBN hat 13 Zeichen, kann also nicht in INT gespeichert werden (9999999999999 > 2³² -1).

    • VARCHAR wird benötigt, wenn man die Bindestriche der ISBN mitspeichern will

  • Titel

    • Datentyp: VARCHAR(100)

    • Durchschnittliche Buchtitel sind 3-7 Wörter lang, allerdings gibt es auch deutlich längere Titel

    • Lieber ein zu großes n wählen, als zu klein

  • Autor

    • Datentyp: VARCHAR(100)

  • Preis

    • Datentyp: DECIMAL(19,2)

    • Grund: Standard für Geldeinträge

    • Alternativ: Auch möglich aber u. U. unpraktisch: FLOAT, REAL, VARCHAR(n).

  • Veröffentlicht

    • Datentyp: DATE oder TIMESTAMP

    • Kann im Gegensatz zu VARCHAR einfach für Operationen verwendet werden (z.B. Alter des Buches berechnen).

  • Buchcover

    • Datentyp: BLOB (Binary Large Object)

    • Grund: wird benutzt, um große Daten wie z.B. Bilder abzuspeichern.

    • Alternativ: VARCHAR mit Referenz auf Object Storage URL, weil DBs oft nicht gut geeignet sind, um Binärdateien abzuspeichern.

  • Abstract

    • Datentyp: BLOB

    • Grund: Abstracts können sehr lang sein, deshalb ist VARCHAR ungeeignet.

Aufgabe 1.3: Shop*#

Kunde_ID

ISBN

Kaufzeitpunkt

Musterlösung

Musterlösung

  • Kunde_ID

    • Datentyp: UINTEGER

    • Reicht für 2³² Kunden

    • Alternativ: LONG, VARCHAR

  • ISBN

    • Datentyp: UBIGINT/VARCHAR(17)

    • ISBN hat 13 Zeichen, kann also nicht in INT gespeichert werden (9999999999999 > 2³¹ -1).

    • VARCHAR wird benötigt, wenn man die Bindestriche der ISBN mitspeichern will

  • Kaufzeitpunkt

    • Datentyp: TIMESTAMP

Aufgabe 2: Eigenschaften von Datentypen#

Gegeben ist eine Tabelle mit vielen Attributen. Testen Sie das Verhalten von diversen Datentypen in DuckDB, welches Verhalten ist nachvollziehbar und welches Verhalten ist überraschend?

import duckdb

con = duckdb.connect()

con.sql(
    """
CREATE TABLE Getraenk (GetraenkeId BIGINT PRIMARY KEY, 
                      Preis DECIMAL(8,2), 
                      Pfand float, 
                      Marke VARCHAR(20), 
                      BarcodeNummer INTEGER); 
"""
)

con.sql("INSERT INTO Getraenk VALUES (1, 1.29, 0.15, 'Club-Mate', NULL)")

con.sql("")

Hide code cell content

 
#### Musterlösung

# Ein Standardbeispiel für Studierende, dass Float-Formate nur begrenzt viele Bits haben und
# dass es hierbei entsprechend zu Fehlern kommen kann.

con.sql("SELECT 1000 + 1/3 - 1000 == 1/3")
# con.sql("SELECT 1000 + 1/3 - 1000") zeigt das Ergebnis an

┌───────────────────────────────────────┐
│ (((1000 + (1 / 3)) - 1000) = (1 / 3)) │
│                boolean                │
├───────────────────────────────────────┤
│ false                                 │
└───────────────────────────────────────┘

Hide code cell content

 
#### Musterlösung

# Ein Standardbeispiel, wie man mit NULL-Werten umgehen sollte/muss.

con.sql(
    """
SELECT *
FROM Getraenk
WHERE BarcodeNummer == NULL 
"""
)

# vs

con.sql(
    """
SELECT *
FROM Getraenk
WHERE BarcodeNummer IS NULL 
"""
)

┌─────────────┬──────────────┬───────┬───────────┬───────────────┐
│ GetraenkeId │    Preis     │ Pfand │   Marke   │ BarcodeNummer │
│    int64    │ decimal(8,2) │ float │  varchar  │     int32     │
├─────────────┼──────────────┼───────┼───────────┼───────────────┤
│           1 │         1.29 │  0.15 │ Club-Mate │          NULL │
└─────────────┴──────────────┴───────┴───────────┴───────────────┘

Hide code cell content

 
#### Musterlösung

# Der Decimal Datentyp wandelt Daten nur um in die gegebene Genauigkeit, aber kontrolliert nicht, ob ein
# Genauigkeitsverlust beim Einfügen stattfindet.

con.sql("INSERT INTO Getraenk VALUES (2, 1.2912, 0.15, 'Fritz-Kola', 123)")

con.sql("SELECT * FROM Getraenk")

┌─────────────┬──────────────┬───────┬────────────┬───────────────┐
│ GetraenkeId │    Preis     │ Pfand │   Marke    │ BarcodeNummer │
│    int64    │ decimal(8,2) │ float │  varchar   │     int32     │
├─────────────┼──────────────┼───────┼────────────┼───────────────┤
│           1 │         1.29 │  0.15 │ Club-Mate  │          NULL │
│           2 │         1.29 │  0.15 │ Fritz-Kola │           123 │
└─────────────┴──────────────┴───────┴────────────┴───────────────┘

Hide code cell content

 
#### Musterlösung

# Varchar(n) ist nur aus Kompatibilitätsgründen vorhanden, aber das n begrenzt nicht tatsächlich die
# Länge des Strings.

con.sql(
    "INSERT INTO Getraenk VALUES (3, 1.2912, 0.15, 'Club-Mate Zero: Der Prickelnde Mate-Eistee', NULL)"
)

con.sql("SELECT * FROM Getraenk")

┌─────────────┬──────────────┬───────┬────────────────────────────────────────────┬───────────────┐
│ GetraenkeId │    Preis     │ Pfand │                   Marke                    │ BarcodeNummer │
│    int64    │ decimal(8,2) │ float │                  varchar                   │     int32     │
├─────────────┼──────────────┼───────┼────────────────────────────────────────────┼───────────────┤
│           1 │         1.29 │  0.15 │ Club-Mate                                  │          NULL │
│           2 │         1.29 │  0.15 │ Fritz-Kola                                 │           123 │
│           3 │         1.29 │  0.15 │ Club-Mate Zero: Der Prickelnde Mate-Eistee │          NULL │
└─────────────┴──────────────┴───────┴────────────────────────────────────────────┴───────────────┘

con.close()

Aufgabe 3: Tabellen#

Im nächsten Schritt implementieren Sie nun ein vollständiges Datenbankschema. Beachten Sie auch hierzu im Zweifel wieder die DuckDB Dokumentation.

Als Erstes erstellen wir eine Datenbank im Arbeitsspeicher:

con = duckdb.connect()

Gegeben sei das folgende ER-Diagramm. In den folgenden Teilaufgaben schreiben Sie für jede Relation eine DDL-Query, welche die entsprechende Tabelle erstellt. Überlegen Sie dabei jeweils vorher für die Attribute geeignete Datentypen und ggf. weitere Integritätsbedingungen.

Aufgabe 3.1#

Wie viele Tabellen müssen Sie minimal für das obenstehende Schema erstellen? Erstellen Sie den relationalen Entwurf zu diesen Tabellen.

Musterlösung

Musterlösung

Vier, da gehoert_zu und ist_an nicht implementiert werden müssen.

  • Student(MatNr, Name, Vorname, GebDatum, FakNr→Fakultaet)

  • Pruefungsfach(PNr, Fach, FakNr→Fakultaet)

  • Fakultaet(FakNr, FakName, Dekan)

  • belegt(MatNr→Student, PNr→Pruefungsfach, Note, Pruefer, Termin)

Aufgabe 3.2#

Erstellen Sie die Tabelle Fakultaet. Integrieren Sie dabei die Anforderung, dass diese Tabelle nur die Fakultäten der TU Berlin enthalten darf (d. h. es gibt sieben Fakultäten, welche mit römischen Zahlen bezeichnet werden).

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

# Hinweis: In DuckDB, im Gegensatz zu Sqlite zuvor, forciert der PRIMARY KEY Constraint bereits NOT NULL

query = """
CREATE TABLE Fakultaet(FakNr UTINYINT PRIMARY KEY CHECK (FakNr > 0 AND FakNr <= 7),
                       FakName VARCHAR(10) NOT NULL CHECK (FakName ~ '^Fakultät (I{1,3}|IV|VI{0,2})$'),
                       Dekan VARCHAR(30));
"""

con.sql(query)

Aufgabe 3.3#

Erstellen Sie nun die Tabelle Pruefungsfach.

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

# Hinweis: Die Studierenden haben bis hierhin noch keine Fremdschlüsseldefinitionen im Tutorium gesehen.
# Außerdem gibt es in DuckDB kein ON DELETE DELETE/SET NULL.

query = """
CREATE TABLE Pruefungsfach(PNr UINTEGER PRIMARY KEY,
                           Fach VARCHAR(10),
                           FakNr UTINYINT NOT NULL REFERENCES Fakultaet(FakNr));
"""

con.sql(query)

Aufgabe 3.4#

Erstellen Sie nun die Tabelle Student. Es gibt die zusätzliche Anforderung, dass Studierende in der Vergangenheit geboren sein müssen.

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

query = """
CREATE TABLE Student(MatNr UINTEGER,
                     Name VARCHAR(30) NOT NULL,
                     Vorname VARCHAR(20) NOT NULL,
                     GebDatum DATE NOT NULL CHECK (GebDatum < current_date()),
                     FakNr UTINYINT,
                     PRIMARY KEY (MatNr),
                     FOREIGN KEY (FakNr) REFERENCES Fakultaet(FakNr));
"""

con.sql(query)

Aufgabe 3.5#

Erstellen Sie nun zuletzt ein paar beispielhafte Instanzen und fügen Sie diese in die Tabellen ein. Um das Ergebnis zu prüfen, haben wir für Sie bereits drei SQL-Statements vorbereitet, welche den Inhalt der Tabellen ausgeben.

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

# Hinweis: Instanzen müssen in der Reihenfolge der Constraints eingefügt werden. Ein Student muss eine
# Fakultät referenzieren, sodass diese existieren muss und ein Prüfungsfach hat die gleiche
# Einschränkung
# Idee fürs Tutorium: Geburtstags-Constraint und Fakultäts-Constraint testen

query = """

INSERT INTO Fakultaet VALUES (4, 'Fakultät IV', 'Marc Alexa'), 
                             (1, 'Fakultät I', 'Beate Krickel');
INSERT INTO Student VALUES ('1000000', 'Maier', 'Max', '2000-02-29', 1), 
                           ('0405054', 'Friedrich', 'Luisa', '1999-12-30',4);
INSERT INTO Pruefungsfach VALUES (1, 'ISDA', 4), 
                                 (2, 'AlgoDat', 4), 
                                 (3, 'Systemprogrammierung', 4)

"""

con.sql(query)

con.sql("SELECT * FROM Fakultaet")

┌───────┬─────────────┬───────────────┐
│ FakNr │   FakName   │     Dekan     │
│ uint8 │   varchar   │    varchar    │
├───────┼─────────────┼───────────────┤
│     4 │ Fakultät IV │ Marc Alexa    │
│     1 │ Fakultät I  │ Beate Krickel │
└───────┴─────────────┴───────────────┘

con.sql("SELECT * FROM Student")

┌─────────┬───────────┬─────────┬────────────┬───────┐
│  MatNr  │   Name    │ Vorname │  GebDatum  │ FakNr │
│ uint32  │  varchar  │ varchar │    date    │ uint8 │
├─────────┼───────────┼─────────┼────────────┼───────┤
│ 1000000 │ Maier     │ Max     │ 2000-02-29 │     1 │
│  405054 │ Friedrich │ Luisa   │ 1999-12-30 │     4 │
└─────────┴───────────┴─────────┴────────────┴───────┘

con.sql("SELECT * FROM Pruefungsfach")

┌────────┬──────────────────────┬───────┐
│  PNr   │         Fach         │ FakNr │
│ uint32 │       varchar        │ uint8 │
├────────┼──────────────────────┼───────┤
│      1 │ ISDA                 │     4 │
│      2 │ AlgoDat              │     4 │
│      3 │ Systemprogrammierung │     4 │
└────────┴──────────────────────┴───────┘

Aufgabe 3.6#

Löschen Sie nun eine Instanz der Relation Fakultaet, welche von Instanzen der Relationen Pruefungsfach oder Student referenziert wird. Um diese löschen zu können, muss mehr gelöscht werden als nur diese eine Instanz. Warum? Zum Überprüfen des Ergebnisses haben wir erneut drei SQL-Statements zum Ausgeben der Tabellen vorbereitet.

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

# Um die Fakultät löschen zu dürfen, müssen zuvor alle Instanzen gelöscht werden, welche diese
# referenzieren, im Falle der Musterlösung also auch die Studentin Luisa Friedrich.

query = """
DELETE FROM Student WHERE FakNr = 1;
DELETE FROM Fakultaet WHERE FakNr = 1;
"""

con.sql(query)

con.sql("SELECT * FROM Fakultaet")

┌───────┬─────────────┬────────────┐
│ FakNr │   FakName   │   Dekan    │
│ uint8 │   varchar   │  varchar   │
├───────┼─────────────┼────────────┤
│     4 │ Fakultät IV │ Marc Alexa │
└───────┴─────────────┴────────────┘

con.sql("SELECT * FROM Student")

┌────────┬───────────┬─────────┬────────────┬───────┐
│ MatNr  │   Name    │ Vorname │  GebDatum  │ FakNr │
│ uint32 │  varchar  │ varchar │    date    │ uint8 │
├────────┼───────────┼─────────┼────────────┼───────┤
│ 405054 │ Friedrich │ Luisa   │ 1999-12-30 │     4 │
└────────┴───────────┴─────────┴────────────┴───────┘

con.sql("SELECT * FROM Pruefungsfach")

┌────────┬──────────────────────┬───────┐
│  PNr   │         Fach         │ FakNr │
│ uint32 │       varchar        │ uint8 │
├────────┼──────────────────────┼───────┤
│      1 │ ISDA                 │     4 │
│      2 │ AlgoDat              │     4 │
│      3 │ Systemprogrammierung │     4 │
└────────┴──────────────────────┴───────┘

Aufgabe 3.7#

Erstellen Sie nun die Tabelle belegt. Es soll sichergestellt sein, dass nur Hochschulnoten eingetragen werden können. Eine Note mit dem Wert 1.5 sollte man also z.B. nicht eintragen können, die Note 1.7 aber schon (vgl. § 68 (1) AllgStuPO).

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

# Hinweis: Dies ist die erste Aufgabe, in der die Studierenden einen kombinierten Schlüssel erstellen müssen.

query = """
CREATE TABLE belegt(MatNr UINTEGER,
                    PNr UINTEGER,
                    Pruefer VARCHAR(30),
                    Termin DATE,
                    Note DECIMAL(2,1) CHECK (Note IN (1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0)),
                    PRIMARY KEY (MatNr, PNr),
                    FOREIGN KEY (MatNr) REFERENCES Student(MatNr),
                    FOREIGN KEY (PNr) REFERENCES Pruefungsfach(PNr));
"""

con.sql(query)

Aufgabe 3.8#

Erstellen Sie nun zuletzt ein paar beispielhafte Instanzen in der Relation belegt und testen Sie die Integritätsbedingung für Noten. Zum Überprüfen des Ergebnisses haben wir erneut ein SQL-Statement zum Ausgeben der Tabelle vorbereitet.

query = """

"""

con.sql(query)

Hide code cell content

 
#### Musterlösung

query = """
INSERT INTO belegt VALUES ('0405054', 1, 'Markl', '2024-08-02', 1.0);
"""

# query = """
# INSERT INTO belegt VALUES ('0405054', 2, 'Brock', '2024-07-16', 1.2);
# """

# query = """
# INSERT INTO belegt VALUES ('0405054', 2, 'Brock', '2023-07-16', 1.0);
# """

con.sql(query)

con.sql("SELECT * FROM belegt")

┌────────┬────────┬─────────┬────────────┬──────────────┐
│ MatNr  │  PNr   │ Pruefer │   Termin   │     Note     │
│ uint32 │ uint32 │ varchar │    date    │ decimal(2,1) │
├────────┼────────┼─────────┼────────────┼──────────────┤
│ 405054 │      1 │ Markl   │ 2024-08-02 │          1.0 │
└────────┴────────┴─────────┴────────────┴──────────────┘

con.close()

Aufgabe 4: Schemata*#

In der folgenden Aufgabe setzten wir uns mit Datenbankschemata auseinander. Ein Datenbankschema besteht aus mehreren Tabellen und beschreibt damit ein zusammenhängendes Informations-Konstrukt. In Produktiv-Datenbankmanagementsystemen werden Datenbankschemata im Mehrbenutzer:innen-Betrieb genutzt, um den Zugriff auf Daten zu beschränken. In der Datenbank eines Online-Shops sollen bspw. Kund:innen zwar Produkte einsehen können, aber nicht, wie viele Produkte noch auf Lager sind, oder wie die anderen Kund:innen des Shops heißen und wo sie wohnen. Die Angestellten-Datenbank, welche vom selben Datenbankmanagementsystem verwaltet wird, ist dann ein weiteres Datenbankschema und komplett getrennt vom Shopsystem.

Im Folgenden definieren wir auf einem einfachen Niveau derartige Schemata. Wir nutzen für diese Aufgaben wieder die DuckDB Dokumentation.

Zuerst erstellen wir eine Datenbank im Arbeitsspeicher für die Bearbeitung der Aufgabe.

con = duckdb.connect()

Aufgabe 4.1: Schemata erstellen*#

Erstellen Sie zuerst ein Schema mit dem Namen internesSchema.

Hinweis: Das aktive Schema können Sie mittels SET search_path TO <schemaName> wechseln. Alternativ können Sie mithilfe der Punktnotation das Schema in Kombination mit der Tabelle angeben, um zu spezifizieren, welche Relation oder Instanzen Sie referenzieren möchten.

con.sql("")

Hide code cell content

 
#### Musterlösung

con.sql("CREATE SCHEMA internesSchema")

con.sql("SET search_path TO internesSchema")

4.2: Schema füllen*#

Erstellen Sie nun die folgende Tabelle für internesSchema.

Produkt(ProduktId, Produktname, Produktbeschreibung, Preis, Haeufigkeit)

con.sql("")

Hide code cell content

 
#### Musterlösung

con.sql(
    """CREATE TABLE Produkt (ProduktID BIGINT PRIMARY KEY, Produktname VARCHAR, 
                             Produktbeschreibung VARCHAR,
                             Preis DECIMAL(8,2),
                             Haeufigkeit BIGINT CHECK(Haeufigkeit >=0))"""
)

4.3: Tabelle Füllen*#

Füllen Sie nun die Tabelle mit ein paar beispielhaften Instanzen. Ein Befehl zum Ausgeben der Tabelle, damit Sie das Ergebnis überprüfen können, ist bereits vorhanden.

con.sql(
    """

"""
)

con.sql("SELECT * FROM Produkt")

┌───────────┬─────────────┬─────────────────────┬──────────────┬─────────────┐
│ ProduktID │ Produktname │ Produktbeschreibung │    Preis     │ Haeufigkeit │
│   int64   │   varchar   │       varchar       │ decimal(8,2) │    int64    │
├───────────┴─────────────┴─────────────────────┴──────────────┴─────────────┤
│                                   0 rows                                   │
└────────────────────────────────────────────────────────────────────────────┘

Hide code cell content

 
#### Musterlösung

con.sql(
    """
INSERT INTO internesSchema.Produkt VALUES (1, 'MacBook Pro', 'MacBook Pro in Space Grey', 1499.99, 5), 
                                          (2, 'Lenovo Think Pad', '1TB SSD and 16GB RAM', 1299.99, 10);
"""
)

con.sql("SELECT * FROM Produkt")

┌───────────┬──────────────────┬───────────────────────────┬──────────────┬─────────────┐
│ ProduktID │   Produktname    │    Produktbeschreibung    │    Preis     │ Haeufigkeit │
│   int64   │     varchar      │          varchar          │ decimal(8,2) │    int64    │
├───────────┼──────────────────┼───────────────────────────┼──────────────┼─────────────┤
│         1 │ MacBook Pro      │ MacBook Pro in Space Grey │      1499.99 │           5 │
│         2 │ Lenovo Think Pad │ 1TB SSD and 16GB RAM      │      1299.99 │          10 │
└───────────┴──────────────────┴───────────────────────────┴──────────────┴─────────────┘

4.4: Kundenschema*#

Erstellen Sie nun ein zweites Schema für die Kunden in Ihrem Online-Shop.

con.sql("")

Hide code cell content

 
#### Musterlösung

con.sql("CREATE SCHEMA kundenSchema")

con.sql("SET search_path TO kundenSchema")

4.5: Produkttabelle*#

Erstellen Sie nun die Produkttabelle auch für die Kunden, aber ohne die Häufigkeit, da die Lagerbestände intern bleiben sollen.

Produkt(ProduktId, Produktname, Produktbeschreibung, Preis)

Hinweis: Alternativ könnte ein solches Verhalten auch mit Views realisiert werden. Diese werden jedoch nicht in der Vorlesung behandelt, weshalb wir das Problem mit zwei sehr ähnlichen Tabellen in zwei Schemata lösen.

con.sql(
    """

"""
)

con.sql("SELECT * FROM kundenSchema.Produkt")

---------------------------------------------------------------------------
CatalogException                          Traceback (most recent call last)
Cell In[40], line 7
      1 con.sql(
      2     """
      3 
      4 """
      5 )
----> 7 con.sql("SELECT * FROM kundenSchema.Produkt")

CatalogException: Catalog Error: Table with name Produkt does not exist!
Did you mean "internesSchema.Produkt"?

Hide code cell content

 
#### Musterlösung

con.sql(
    """CREATE TABLE Produkt (ProduktID BIGINT PRIMARY KEY, Produktname VARCHAR, 
                                 Produktbeschreibung VARCHAR,
                                 Preis DECIMAL(8,2))"""
)

con.sql("SELECT * FROM kundenSchema.Produkt")

4.6: Datenübertragen*#

Übertragen Sie nun zuletzt die Daten von internesSchema.Produkt nach kundenSchema.Produkt, aber nur die Daten, welche die Kunden auch einsehen sollen.

con.sql(
    """

"""
)

con.sql("SELECT * FROM kundenSchema.Produkt")

Hide code cell content

 
#### Musterlösung

con.sql(
    """
INSERT INTO Produkt 
SELECT ProduktID, Produktname, Produktbeschreibung, Preis FROM internesSchema.Produkt
"""
)

con.sql("SELECT * FROM kundenSchema.Produkt")

con.close()
Contents