7 Datenbanken

Der Umgang mit Datenbanken scheint vielleicht ein ungewöhnliches Thema für ein Buch zu sein, das sich mit Coding beschäftigt. Aber zum einen ist SQL ja auch Code; KI-Tools sind ausgezeichnet in der Lage, SQL-Kommandos zusammenzustellen. Und zum anderen ist die künstliche Intelligenz auch im weiteren Umfeld eine große Hilfe, etwa wenn es um das Design von Datenbanken oder um die Administration von Datenbankmanagementsystemen (DBMS) geht.

In diesem Kapitel versuchen wir, Ihnen anhand einer breit gestreuten Palette von Beispielen die Anwendung von KI-Tools beim Entwurf oder der Veränderung von Datenbanken, beim Zusammensetzen von SQL-Kommandos, bei der Administration und schließlich bei der Entwicklung von Client-Code schmackhaft zu machen.

DBMS-spezifische Antworten

Relationale Datenbankmanagementsysteme (DBMS) wie Microsoft SQL Server, MySQL, Oracle oder PostgreSQL haben viele Gemeinsamkeiten, unterscheiden sich aber nennenswert in Details. Soweit es nicht um ganz grundsätzliche Fragen geht, sollten Sie im Prompt immer angeben, auf welches DBMS sich Ihre Frage bezieht! Andernfalls erhalten Sie mitunter Antworten, die auf Ihr DBMS gar nicht zutreffen.

Die besten Erfahrungen werden Sie mit ChatGPT & Co. machen, wenn es zu Ihrem DBMS eine Menge Trainingsmaterial im Internet gibt. Bei relationalen Systemen trifft das insbesondere auf MySQL/MariaDB, PostgreSQL, SQLite und SQL Server zu, bei NoSQL-Produkten sind die sehr populären Programme MongoDB und Redis im Vorteil.

7.1 Datenbankdesign

Wie wir Ihnen in den folgenden Beispielen zeigen möchten, können Ihnen KI-Tools eine Menge Arbeit beim Entwerfen von Datenbanken und bei der Formulierung der erforderlichen CREATE-TABLE-Kommandos abnehmen. Wie so oft funktioniert die KI-Unterstützung umso besser, je genauer Sie Ihre Wünsche formulieren und je klarer Sie einschätzen können, welche Teile der KI-Vorschläge tatsächlich Ihren Wunschvorstellungen entsprechen. Da, wo Sie nicht zufrieden sind, können Sie durch gezielte Prompts eine Nachbesserung erreichen.

Beispiel: Datenbank für einen Online-Shop

Nehmen wir an, Sie wollen einen Online-Shop realisieren. Sie verfolgen eine dynamische Preispolitik, wollen also die Produktpreise jederzeit anpassen. Also bitten Sie ChatGPT um einen ersten Datenbankentwurf.

Prompt: I want to create a database for an online store. The prices of my products will change frequently (sometimes daily). Can you help me with logical modeling?

Absolutely, I can help you design a logical model for your online store database. Given that the prices of your products change frequently, we’ll need to ensure that the database structure supports efficient updates and historical tracking of price changes.

Here’s a basic logical model for your online store database.

Entities and attributes:

Users
- UserID (Primary Key)
- Username
- PasswordHash
- Email
- DateRegistered
Categories
- CategoryID (Primary Key)
- CategoryName
- Description
…

Relationships:

Users can place multiple Orders.
Each Order can have multiple OrderDetails.
…

(many more details …)

An dem von ChatGPT vorgeschlagenen Datenbankdesign gibt es wenig auszusetzen. Die Organisation der Daten ist sinnvoll, auch die Anzahl der Entitäten nimmt ein für diese Entwurfsphase vernünftiges Maß an. Zur weiteren Diskussion des Datenbankdesigns wäre jetzt ein Entity-Relationship-Diagramm eine feine Sache. Selbstbewusst wie immer leitet ChatGPT die entsprechende Bitte mit Sure ein, muss dann aber zugeben, dass es keine Diagramme zeichnen kann. (Wir sind sehr gespannt, wann KI-Tools dazu in der Lage sein werden.)

Prompt: Can you draw an ER diagram for the database?

Sure, I can describe how the ER diagram for this database would look. Given that I’m unable to directly draw images, I’ll provide a detailed description of how you can create the ER diagram. You can then use tools like draw.io, Lucidchart, or any ER diagram tool of your choice to visualize it.

(many more details …)

ChatGPT wiederholt im Wesentlichen die Ausgabe zum vorigen Prompt und gibt (wenige) Tipps, wie diese Daten visualisiert werden könnten.

Mit einem Trick können Sie ChatGPT aber überlisten! Es gibt im Internet diverse Tools zum Zeichnen von Diagrammen, die Anweisungen ähnlich einer Programmiersprache erwarten. Damit führen Sie ChatGPT zurück in seine Textmodell-Domäne. (Man kann sich fragen, warum ChatGPT nicht von sich aus einen derartigen Vorschlag macht.)

Bitten Sie also ChatGPT, Code zum Zeichnen des ER-Diagramm mit Mermaid zu erstellen. Mermaid ist eine großartige JavaScript-Bibliothek zur Generierung von Charts und Diagrammen:

https://mermaid.js.org

Prompt: Can you provide code to draw an ER diagram with Mermaid?

Sure! Mermaid is a great tool for creating diagrams, including ER diagrams, with code. Below is the Mermaid code to generate the ER diagram for your online store database:

erDiagram
    USERS {
        int UserID PK
        string Username
        string PasswordHash
        string Email
        date DateRegistered
    }
    ...
    USERS      ||--o{  ORDERS : "places"
    CATEGORIES ||--o{  PRODUCTS : "contains"
    PRODUCTS   ||--o{  PRODUCT_PRICES : "has"
    ORDERS     ||--o{  ORDER_DETAILS : "includes"
    PRODUCTS   ||--o{  ORDER_DETAILS : "part of"

To use this code:

Copy the Mermaid code above.
Paste it into a Mermaid live editor.
The diagram will be rendered automatically.

This Mermaid code defines the entities (tables), their attributes, primary keys (PK), and foreign keys (FK), along with the relationships between them. The relationships are depicted with the cardinalities: ||--o{ represents a one-to-many relationship.

Abbildung 7.1 Das Datenbankschema als Mermaid-Diagramm

Grundsätzlich funktioniert dieser Ansatz ausgezeichnet. Über die optische Gestaltung kann man sicher diskutieren. Bei Bedarf können Sie über Optionen Farben, Schriften und Gestaltungs-Themes an Ihre Wünsche anpassen. Sollten Sie mit Mermaid gar nicht glücklich werden, können Sie ChatGPT alternativ auch bitten, den Code des Diagramms in der Syntax von PlantUML, Graphviz oder TikZ zu formulieren.

Aus datenbanktheoretischer Sicht kann man hier kritisieren, dass ChatGPT gar kein ER-Diagramm geliefert hat, sondern vielmehr eine visuelle Repräsentierung des Datenbankschemas. (Ein ER-Diagramm ist dazu nur eine Vorstufe. Es enthält weniger Implementierungsdetails, z. B. keine Datentypen wie int oder date. Dafür sind »echte« ER-Diagramme viel übersichtlicher.)

Nehmen wir an, Sie sind mit dem Design grundsätzlich zufrieden. Der nächste Schritt wäre dann die Erzeugung der Datenbank mit CREATE-TABLE-Kommandos. Spätestens an dieser Stelle müssen Sie sich auf ein konkretes DBMS festlegen (hier: den Microsoft SQL Server). An dieser Stelle können Sie das Schema auch um weitere Entitäten bzw. Spalten ergänzen, Wünsche zur Benennung der Tabellen und Spalten formulieren etc.

Prompt: I would like to implement the database using Microsoft SQL Server. Can you help me with CREATE TABLE commands? Also suggest indexes.

Please add some additional columns:

Customers: first name, last name, address (street, city, etc.)
Products: URL for product description
Product prices: VAT rate

Use lower case names, plural for tables, singular for columns.

Certainly! Below are the CREATE TABLE commands for your database in Microsoft SQL Server, including the additional columns you requested. I’ve also included suggestions for indexes to optimize query performance.

CREATE TABLE users (
    userid INT PRIMARY KEY IDENTITY(1,1),
    username NVARCHAR(50) NOT NULL,
    passwordhash NVARCHAR(255) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    dateregistered DATE NOT NULL,
    firstname NVARCHAR(50),
    lastname NVARCHAR(50),
    street NVARCHAR(100),
    city NVARCHAR(50),
    state NVARCHAR(50),
    zip NVARCHAR(20),
    country NVARCHAR(50)
);
...

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_categories_categoryname 
          ON categories(categoryname);
CREATE INDEX idx_products_categoryid 
          ON products(categoryid);
...

Explanation: More details …

Warnung

Es sollte klar sein, dass Sie auch mit KI-Unterstützung nicht rasch in einer halben Stunde eine neue Datenbank entwerfen werden. Ein unzureichend durchdachtes Schema verursacht in der Folge große Probleme. Schuld daran ist nicht das von Ihnen eingesetzte KI-Werkzeug, sondern Ihre unzureichend genaue Problembeschreibung.

Das gilt natürlich auch für das hier präsentierte Beispiel. Ein paar Anmerkungen: Der Tabelle customers fehlen Spalten zur Authentifizierung im Webshop (Login). Der Tabelle orders fehlen Informationen darüber, wie die Bezahlung durchgeführt wird (Rechnung, PayPal etc.). Ebenso wenig ist der Versand der Produkte (inklusive der Kalkulation der Versandkosten) modelliert. Üblicherweise wollen Sie zusammen mit jedem Produkt auch Bilder anzeigen. Das erfordert ein oder zwei weitere Tabellen – und Überlegungen dazu, ob die Bilder als Dateien oder direkt in der Datenbank gespeichert werden sollen. ChatGPT & Co. erfüllen all diese Zusatzwünsche gerne – aber Sie müssen sie formulieren!

Kurz und gut: KI-Tools vermeiden lästige Tipparbeit. Sie müssen aber weiterhin selbst darüber nachdenken, welche Aufgaben Ihre Datenbank erfüllen soll und wie Sie diese Funktionen am besten realisieren.

DBMS-Auswahl

Im vorigen Beispiel sind wir davon ausgegangen, dass der SQL Server von Microsoft zum Einsatz kommen soll. Oft ist die Auswahl des optimalen Datenbankmanagementsystems (DBMS) aber ein Thema für sich. KI-Tools können – mit Einschränkungen – auch dabei helfen. Bei unseren Tests haben wir die Tendenz festgestellt, dass KI-Tools nur ungern eine eindeutige Empfehlung geben. Stattdessen bekommen Sie eine lange Liste von Argumenten, die für oder gegen einzelne Produkte sprechen. ChatGPT kann und will Ihnen also nicht die Entscheidung abnehmen, liefert Ihnen aber immerhin sachliche Argumente.

Der folgende Prompt bezieht sich auf die vorhin diskutierte Online-Shop-Datenbank.

Prompt: A friend suggested that I use a NoSQL database instead of SQL Server. He mentioned MongoDB. Would this make sense?

Using a NoSQL database like MongoDB can make sense depending on your specific use case and requirements. Here are some considerations to help you decide whether MongoDB or another NoSQL database is suitable for your online store:

Advantages of Using MongoDB (NoSQL): Schema flexibility, horizontal scalability, document-oriented storage, performance (more details …)

Potential Drawbacks of Using MongoDB: Lack of ACID transactions, complex queries, data duplication (more details …)

Example MongoDB Schema for Your Online Store

Here is an example of how you might structure your data in MongoDB.

More details …

Summary

Use MongoDB if you need flexible schemas, horizontal scalability, and efficient handling of large volumes of semi-structured or unstructured data.
Use SQL Server if your application relies on complex transactions, relationships, and requires strong ACID compliance.

Ultimately, the choice depends on your specific needs, including data structure complexity, scalability requirements, and the nature of your queries and transactions.

ChatGPT führt auf ca. zwei Seiten Text (hier stark gekürzt wiedergegeben) diverse Vor- und Nachteile von MongoDB im Vergleich zum SQL Server auf. ChatGPT gibt auch gleich einen Vorschlag zur Implementierung von vier MongoDB-Collections zur Speicherung der Daten.

Nach einer Beratung im Team entscheiden Sie sich gegen MongoDB. Sie sind sich aber unsicher, ob der SQL Server die beste Wahl ist.

Prompt: After consulting with my team, we decided to use a relational DBMS. However, we are considering an open source product (MariaDB, MySQL, PostgreSQL) instead. Can you recommend one of these programs?

All three of the open-source RDBMS options you mentioned (MariaDB, MySQL, PostgreSQL) are robust and widely used, but they have some differences that may make one more suitable for your specific needs. Here’s a brief comparison to help you decide (more details …).

Summary

PostgreSQL is recommended for its advanced features and performance.
MariaDB is a strong alternative, especially if you prefer MySQL compatibility and a fully open-source solution.
MySQL remains a popular choice for its ease of use and extensive support.

Choose the one that best fits your team’s familiarity and the specific needs of your application. If you need further assistance with setting up or configuring your chosen RDBMS, feel free to ask!

Beispiel: Cloud-Einkaufszettel

Sie kennen vielleicht Apps, mit denen Familienmitglieder ihre Einkäufe organisieren: Wem auffällt, dass Milch, Mehl oder Butter fehlen, schreibt den Artikel auf eine Einkaufs-To-do-Liste. Und wer als Nächstes einkaufen geht, sieht die gemeinschaftlich erstellte Liste und kann diese während des Einkaufs wieder abhaken.

Prompt: We want to build a smartphone app with a cloud backend. In the app, all members of a group (typically a family) can add shopping items. Whoever does the next shopping will see all the items that are currently missing at home. While shopping, he/she can uncheck all items in the shopping cart.

I am responsible for the database backend, which will be implemented using MongoDB. Can you suggest a database schema to handle the shopping list and connect the members of a group?

Certainly! Below is a suggested schema for a MongoDB database to handle a shopping list app where group members can add and manage items.

Users collection: Each user has an account and belongs to one or more groups.

{
  "_id": "ObjectId",         // Unique identifier for the user
  "username": "string",      // Username of the user
  "email": "string",         // Email address
  "password": "string",      // Hashed password
  "groups": ["ObjectId"]     // Array of group IDs the user 
}                            // belongs to

Groups collection: Each group contains multiple users and a list of shopping items.

{
  "_id": "ObjectId",         // Unique identifier for the group
  "name": "string",          // Name of the group (e.g., 
                             // Family, Friends)
  "members": ["ObjectId"],   // Array of user IDs who are 
                             // members of this group
  "shoppingList": [
    {
      "itemId": "ObjectId",  // Unique identifier for the item
      "name": "string",      // Name of the item
      "quantity": "string",  // Quantity (2 liters, 1 pack ...)
      "addedBy": "ObjectId", // User ID who added the item
      "status": "string"     // Status of the item (pending, 
    }                        // purchased ...)
  ]
}

Example Documents: More details …

Example Queries: More details …

Key Points:

User Authentication: Store user credentials and provide authentication mechanisms.
Group Management: Each user can belong to multiple groups, and each group can have multiple members.
Shopping List Items: Items in the shopping list have statuses to track whether they are pending or purchased.

Die Antwort von ChatGPT geht über die eigentliche Frage hinaus. Das KI-Tool schlägt nicht nur ein sinnvolles Datenbankschema vor; es gibt auch konkrete Beispiele zur Anwendung des Schemas sowie zur Durchführung elementarer Datenbankoperationen (Element zur Einkaufsliste hinzufügen etc.).

Unsere vorhin schon einmal formulierte Warnung, dass Sie den Lösungsvorschlag von ChatGPT kritisch hinterfragen müssen, gilt natürlich auch hier. Vermutlich haben Sie Detailwünsche für die optische und funktionelle Ausgestaltung der App, die entsprechende Änderungen am Datenbankschema erfordern. Aber als Ausgangspunkt für einen ersten Prototypen ist die von ChatGPT gelieferte Antwort vollkommen ausreichend.

Datenbankschema ändern

In der Praxis haben Sie nur selten die Chance, dass Sie ein ganz neues Datenbankschema von Grund auf entwickeln dürfen bzw. müssen. Viel öfter kommt es vor, dass Sie das Schema nur in Details erweitern sollen. Auch dabei können KI-Werkzeuge helfen. Typischerweise übergeben Sie im ersten Schritt das aktuelle Schema der Datenbank (also alle zugrunde liegenden CREATE-TABLE-Kommandos) bzw. stellen dieses in einem Editor-Tab bereit. (Bei Bedarf fragen Sie einfach ChatGPT, wie Sie eine Schemadatei erzeugen können. Ein entsprechendes Beispiel finden Sie im Abschnitt 7.3, »Administration«.)

Bei manchen Fragen reicht es aus, nur die Definition einer einzigen Tabelle anzugeben. In der folgenden Frage geht es darum, wie groß eine Textspalte sein muss, damit die Speicherung von IPv6-Adressen auch bei allen Sonderfällen fehlerfrei gelingt.

Prompt: MySQL: We use the logins table to store IPv4 addresses.

CREATE TABLE logins ( ...
  ip VARCHAR(15) DEFAULT NULL, 
  ...)

Now we have to deal with IPv6 addresses as well. How do we change the ip column? Could you provide an ALTER TABLE command?

ALTER TABLE logins MODIFY COLUMN ip VARCHAR(45) DEFAULT NULL;

This change sets the length of the ip column to 45 characters, which is sufficient to store both IPv4 and IPv6 addresses, including the full expanded form of an IPv6 address.

Als Ausgangspunkt für das zweite Beispiel dient eine mäßig komplexe Datenbank zur Speicherung von Kundendaten, Bestellungen und Rechnungen. Die Schemadatei umfasst ca. 300 Zeilen und enthält zehn miteinander verknüpfte Dateien.

Sie arbeiten in VS Code, die Schemadatei befindet sich in Ihrem Projektverzeichnis. Sie wollen die Struktur der Datenbank ändern. Vorher vergewissern Sie sich, dass Ihr KI-Tool die Struktur der Datenbank überhaupt versteht.

Prompt: @workspace Can you explain the content of accounting-schema.sql?

The accounting-schema.sql file appears to be a MySQL database schema dump for a database named accounting. It contains the SQL commands to recreate the structure of the database, including the creation of tables and their relationships. Here are some of the tables defined in the schema (more details …)

Nachdem dieser Test zufriedenstellend verlaufen ist, formulieren Sie Ihren eigentlichen Wunsch. In der Tabelle customers können aktuell nur die Kontaktdaten einer Person gespeichert werden. Für manche Ihrer Kunden wollen Sie aber mehrere Personen speichern.

Prompt: OK. The customers table currently contains contact information for only one person. For some customers, we need to store data for several contact persons (first name, last name, email, phone number). Can you suggest a schema change? Existing data must be preserved.

To accommodate multiple contact persons for a single customer, you can create a new table called contactPersons. This table will have a foreign key linking it to the customers table. Here’s the SQL to create this new table:

CREATE TABLE contactPersons (
  id         INT(11) NOT NULL AUTO_INCREMENT,
  customerId INT(11) NOT NULL,
  firstName  VARCHAR(100) DEFAULT NULL,
  lastName   VARCHAR(100) DEFAULT NULL,
  email      VARCHAR(100) NOT NULL,
  telNo      VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (customerId) REFERENCES customers(id)
);

In this new schema, each customer can have multiple contact persons. The customerId field in the contactPersons table links each contact person to a customer in the customers table.

To preserve existing data, you can migrate the contact person data from the customers table to the contactPersons table with a SQL command like this:

INSERT INTO contactPersons (customerId, firstName, 
                            lastName, email, telNo)
SELECT id, firstName, lastName, email, telNo 
FROM customers;

After the data migration, you may want to keep the contact person fields in the customers table for a while for backward compatibility. However, you should plan to eventually remove these fields and update any code that references them to use the new contactPersons table instead.

Wir haben das Beispiel mit GitHub Copilot durchgeführt – und in diesem Fall brilliert das Tool: Es schlägt nicht nur eine vernünftige Schemaänderung vor (Auslagerung der Kontaktdaten in eine eigene Tabelle), es übernimmt auch die Spaltennamen und Datentypen von der ursprünglichen Tabelle customers, benennt die neue Tabelle in der gleichen Camel-Case-Notation, die auch bei den bisherigen Tabellen zum Einsatz kam, und denkt an die neue Foreign-Key-Regel für die Verknüpfung der neuen Tabelle mit der vorhandenen customers-Tabelle. Auch die Vorschläge zur Datenmigration sind vernünftig und funktionieren.

Natürlich liegt es nun immer noch an Ihnen, ob Sie den Datenbankumbau wie vorgeschlagen durchführen wollen oder ob noch Änderungen notwendig sind. Vielleicht wollen Sie bei mehreren Kontakten einen davon als Hauptkontakt kennzeichnen – dann benötigen Sie dafür eine weitere Spalte.