第２弾
前回より、UMLの資格試験『UMLモデリング技能認定試験』のサンプル問題をSQLで実装する企画を始めました。
この企画の趣旨やメリットについては、前回の記事に記載しております。
もしご興味がございましたら、読んでみてください。
【SQL】UMTP認定試験サンプル問題（L1：構造モデリング）を実装してみた！
前回は構造モデリングのクラス図を使って、実装に挑戦しました。
今回は要求モデリングのユースケース図を使い、挑戦します。
サンプル問題
実装の前に、まずは設計しなくてはいけません。
そのためにサンプル問題を確認します。
※出典：UMLモデリング技能認定試験サンプル問題 L1
この問題、正解は４です。
今回はユースケース図の解説ではないので、説明は割愛します。
問題文と選択肢を結び付けるだけでも、正解は導けると思います。
ユースケース図について気になる方は、下記のUMTP公式動画を参照してください。
（下記のリンクは、ちょうどユースケース図の説明から観られるようにしています）
実装前に決めること
スコープ
まずは、対象範囲（スコープ）の確認です。
ユーザが利用するのは注文管理システムです。
顧客管理システムは顧客の情報を取得するために連携しますが、既に存在します。
一応、システム間で紐づけするためのキー項目は必要と判断します。
テーブル定義
必要なデータを考慮し、テーブル定義を作成します。
- 受注係が、注文情報を管理する（登録や編集など）
- 配送係が、注文情報を参照する（お客様に配送するため）
- 注文管理システムが、顧客管理システムを参照する（お客様の届け先などを取得するため）
注文情報に関するテーブルを作成します。
今回はかなりシンプルですが、実務レベルではもっと項目が多かったり、関連するテーブルも多いと思います。
【注文ヘッダ】
- 注文番号（主キー）
- 注文日
- 顧客番号
- 注文ステータス（注文が確定したかどうか）
【注文明細】
- 注文番号（主キー１、外部キー）
- 注文明細番号（主キー２）
- 商品番号
- 数量
基本的に、業務系のシステムではトランザクションテーブルを作成する際に 『ヘッダ』と『明細』に分けて考えることが多いです。
たとえば、下記のようなイメージです。
- 受注：いつ・誰から受注したという『ヘッダ（手続き）』と、何が・何個必要なのか？ という『明細（内訳）』
- 販売：いつ・誰に・合計いくら販売したという『ヘッダ（手続き）』と、何を・何個販売したという『明細（内訳）』
実装してみよう（SQL）
では、簡易な設計ではありますが上記をSQLで実装してみます。
PostgreSQLで書きますので、よろしければ試してみてください。
※簡単に試す方法はこちらの記事を参考にして下さい。
DDL
まずはテーブル定義を作ります。
-- テーブル『注文ヘッダ』を作成
CREATE TABLE 注文ヘッダ (
注文番号 CHAR(3) PRIMARY KEY -- 主キー
,注文日 DATE
,顧客番号 CHAR(4)
,注文ステータス Boolean
);
-- テーブル『注文明細』を作成
CREATE TABLE 注文明細 (
注文番号 CHAR(3) REFERENCES 注文ヘッダ(注文番号) -- 外部キー（注文ヘッダに無い注文番号が発生しないようにするため）
,注文明細番号 INTEGER
,商品番号 CHAR(5)
,数量 INTEGER
,PRIMARY KEY (注文番号,注文明細番号) -- 主キー
);
DML（データ登録）
では、データのイメージを登録してみます。
-- 『注文ヘッダ』のデータを作成
INSERT INTO 注文ヘッダ VALUES
('C01','2025/03/31','K001',True)
,('C02','2025/04/02','K001',False);
-- 『注文明細』のデータを作成
INSERT INTO 注文明細 VALUES
('C01',1,'S1111',5)
,('C01',2,'S3333',24)
,('C01',3,'S5555',8)
,('C02',1,'S1111',30)
,('C02',2,'S7777',4)
,('C02',3,'S2222',19);
上記の内容を、SELECT文で確認してみます。
SELECT * FROM 注文ヘッダ
ORDER BY 注文番号;
SELECT * FROM 注文明細
ORDER BY 注文番号,注文明細番号;
結果は下記の通りです。
【注文ヘッダ】
| 注文番号 | 注文日 | 顧客番号 | 注文ステータス |
|---|---|---|---|
| C01 | 2025-03-31 | K001 | true |
| C02 | 2025-04-02 | K001 | false |
【注文明細】
| 注文番号 | 注文明細番号 | 商品番号 | 数量 |
|---|---|---|---|
| C01 | 1 | S1111 | 5 |
| C01 | 2 | S3333 | 24 |
| C01 | 3 | S5555 | 8 |
| C02 | 1 | S1111 | 30 |
| C02 | 2 | S7777 | 4 |
| C02 | 3 | S2222 | 19 |
データとしてはこれで良いと思いますが、ユーザが利用する場合を想定し結合します。
DML（結合）
今回は、注文番号で内部結合します。
つまり、注文ヘッダと注文明細のキーが一致するもののみ、対象とします。
業務要件にもよりますが「注文時、商品を必ず１つ以上する」場合は、内部結合で良いと考えられます。
SELECT * FROM 注文ヘッダ hd
INNER JOIN 注文明細 ms USING(注文番号)
ORDER BY hd.注文番号,ms.注文明細番号;
| 注文番号 | 注文日 | 顧客番号 | 注文ステータス | 注文明細番号 | 商品番号 | 数量 |
|---|---|---|---|---|---|---|
| C01 | 2025-03-31 | K001 | true | 1 | S1111 | 5 |
| C01 | 2025-03-31 | K001 | true | 2 | S3333 | 24 |
| C01 | 2025-03-31 | K001 | true | 3 | S5555 | 8 |
| C02 | 2025-04-02 | K001 | false | 1 | S1111 | 30 |
| C02 | 2025-04-02 | K001 | false | 2 | S7777 | 4 |
| C02 | 2025-04-02 | K001 | false | 3 | S2222 | 19 |
上記の表を見ることで、たとえば下記のような解釈ができます。
『3/31』の『K001さん』からの注文は『C01番』である。
その『C01番』には３つの商品が含まれ、注文が確定している。※注文ステータスの意味は下記の通りです。
- True（真）：確定済
- False（偽）：未確定
補足
また、ユースケース図では『配送係』や『顧客情報管理システム』が関係しています。
今回はスコープから外したので割愛しますが、その関係を考慮する場合は更にテーブルが増えるものと考えられます。
- 配送係の業務関連：配送情報を、注文と紐づけるテーブル
- 顧客情報管理システムとの連携：顧客番号に紐づく、顧客の情報を取得するためのテーブルまたはビュー（配送先住所など）
さいごに
今回は、ユースケース図に記載の内容から読み取れる範囲で、簡易的な実装に挑戦してみました。
SQLがメインの記事ではありますが、ユースケース図が何か気になるようであれば、それについて勉強するキッカケになる内容になったかと思います。
このシリーズでは、そのような『一石二鳥』を目指しております！
ここまでお付き合い頂き、ありがとうございましたm(_ _)m
Discussion