ChatGPTを使ってDDLからER図をすばやく作成する

最近、酢を飲むと健康に良いという話を聞き、頑張って毎日飲んでいるkonnoです。
何となくですが、朝の目覚めは良くなっている気がしますよ！

今回はデータベース設計に欠かせないER図を、ChatGPTを使ってDDL（Data Definition Language：SQLのデータ定義言語）から簡単に作成できるのか試してみたいと思います。

サンプルとなるDDLを用意

DDLのサンプルとして、以下のMySQL公式サイトで公開されている従業員データベースを利用します。
dev.mysql.com

従業員データベースに必要なファイル類一式は、上記サイトで紹介されている以下のGithub リポジトリから、
"test_db-master.zip"としてダウンロードできます。
github.com

ダウンロードしたtest_db-master.zipには、従業員データベースを設定するために必要なDDLとして"employees.sql"が含まれており、これを使ってER図を作成してみます。

ChatGPTの Show Me Diagrams プラグインを使ってDDLからER図を生成する

今回はChatGPTのShow Me Diagramsプラグインを使用しました。
先ほど取得したDDLをMermaid記法に変換し、ER図を作成するように指示します。
Mermaid記法は、Markdown形式で書くことで、UMLなどの図を描画することできる記法です。

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;

SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';

DROP TABLE IF EXISTS dept_emp,
                     dept_manager,
                     titles,
                     salaries, 
                     employees, 
                     departments;

/*!50503 set default_storage_engine = InnoDB */;
/*!50503 select CONCAT('storage engine: ', @@default_storage_engine) as INFO */;

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

CREATE TABLE dept_manager (
   emp_no       INT             NOT NULL,
   dept_no      CHAR(4)         NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
); 

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
) 
; 

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
) 
; 

CREATE OR REPLACE VIEW dept_emp_latest_date AS
    SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
    FROM dept_emp
    GROUP BY emp_no;

# shows only the current department for each employee
CREATE OR REPLACE VIEW current_dept_emp AS
    SELECT l.emp_no, dept_no, l.from_date, l.to_date
    FROM dept_emp d
        INNER JOIN dept_emp_latest_date l
        ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;ここにSQLを記述する。

このDDLを基に、ER図を作成するよう指示します。

すると、Mermaid記法で変換した後、次のようなER図が作成されました。

以下のMySQL公式サイトで公開されている従業員データベースのER図と比較すると、デザイン的な要素以外は一致していることが分かります。
dev.mysql.com

Mermaid記法への変換を省略すると

ちなみに、Mermaid記法への変換をせずに、直接DDLからER図を作成させることも試してみましたが、こちらは期待する形に近い図が作成はされるものの、少しデザインの異なる図が作成されました。

DBMLに変換して dbdiagram.io でER図を作成する

DDL情報からER図を作成するための方法として、「dbdiagram.io」というものがあります。そこで、ChatGPTでER図を直接書かず、dbdiagram.io に作成してもらいましょう。
dbdiagram.io

dbdiagram.ioは以下のサイトに説明されているように、DBML (database markup language) というデータベース構造を定義するために設計されたDSL 言語にDDLを変換する必要があります。
dbml.dbdiagram.io

ここで、DBMLの文法に従ってDDLを変換するのに、ChatGPTを利用します。次のようにChatGPTに指示することで、DDLをDBML形式に変換することができます。

すると次のように変換されます。

// Define the database
Project employees {

  // Define the employees table
  Table employees {
    emp_no INT [pk]
    birth_date DATE
    first_name VARCHAR(14)
    last_name VARCHAR(16)
    gender ENUM('M', 'F')
    hire_date DATE
  }

  // Define the departments table
  Table departments {
    dept_no CHAR(4) [pk]
    dept_name VARCHAR(40) [unique]
  }

  // Define the dept_manager table
  Table dept_manager {
    emp_no INT [ref: > employees.emp_no, pk]
    dept_no CHAR(4) [ref: > departments.dept_no, pk]
    from_date DATE
    to_date DATE
  }

  // Define the dept_emp table
  Table dept_emp {
    emp_no INT [ref: > employees.emp_no, pk]
    dept_no CHAR(4) [ref: > departments.dept_no, pk]
    from_date DATE
    to_date DATE
  }

  // Define the titles table
  Table titles {
    emp_no INT [ref: > employees.emp_no, pk]
    title VARCHAR(50) [pk]
    from_date DATE [pk]
    to_date DATE
  }

  // Define the salaries table
  Table salaries {
    emp_no INT [ref: > employees.emp_no, pk]
    salary INT
    from_date DATE [pk]
    to_date DATE
  }

  // Define views (Note: DBML doesn't have native support for views, but you can document them as notes)
  Note: 'CREATE OR REPLACE VIEW dept_emp_latest_date AS
    SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
    FROM dept_emp
    GROUP BY emp_no;'

  Note: 'CREATE OR REPLACE VIEW current_dept_emp AS
    SELECT l.emp_no, dept_no, l.from_date, l.to_date
    FROM dept_emp d
        INNER JOIN dept_emp_latest_date l
        ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;'
}