前回
前回はUMLの資格試験『UMLモデリング技能認定試験』について、サンプル問題の解説を１つ紹介しました。
【UML/UMTP】試験でも実務でも役に立つ、データモデリングの考え方
今回
イメージトレーニング
今回は、その解説したサンプル問題をSQLで実装してみます。
概要を見ただけで、設計から実装までをイメージするトレーニングです。
出来るようになると
筆者の経験に基づいての話ですが、要件定義書を読んですぐにER図やDDL／DMLが頭に浮かぶようになると、開発の現場ではかなり頼りにされます。
補足
当然ながら、要件の変更や設計以降の段階での調整があると思われるので、最初にイメージしたものがそのままリリースされることはあまり考えられませんが、土台として用意しておくことは開発業務の効率化に大きく貢献できると考えられます。
本題（サンプルをSQLに）
サンプル
まずは使用するサンプルの確認です。
※出典：UMLモデリング技能認定試験サンプル問題 L1
前回に解説の通り、正解は『３』です。これを実装していきます。
必要な情報は何か？
問題文やクラス図を見て、どんな情報が必要かイメージできるでしょうか？
テーマは『会員制ホテルの予約』です。
- 部屋：ホテル内の施設で、お客様ごとに確保するもの
- 会員：入会するお客様について
- 予約：お客様が部屋を事前に確保するための情報
では、クラス図を参考に『システム化に必須』と思われる情報を考えます。
- 部屋：部屋を一意に識別できる情報 → 部屋番号
- 会員：お客様を一意に識別できる情報 → 会員番号
- 予約：お客様がどの部屋を確保したかを一意に識別できる情報 → 部屋番号＋会員番号
データのイメージ
テーブルを作成するためにテーブル定義書とER図を書くべきですが、今回は大まかにデータのイメージを作ります。
この方法を先に実施することで、テーブル定義書とER図を作成する際の手戻りを少なくすることが可能です。
【部屋】
識別には部屋番号、表示には部屋名称を設定するのが適切と考えられます。
| 部屋番号 | 部屋名称 |
|---|---|
| 1 | 海側の客室 |
| 2 | 山側の客室 |
| 3 | 大人数向けの客室 |
| 4 | 喫煙者向けの客室 |
【会員】
識別には会員番号、表示には会員氏名を設定するのが適切と考えられます。
| 会員番号 | 会員氏名 |
|---|---|
| 1111 | なんの だれそれ |
| 2222 | どこの どなたか |
| 3333 | すごい かねもち |
【予約】
会員番号と部屋番号を紐づけ、名称は設定しないのが適切と考えられます。
理由は、名称は会員と部屋のテーブルから取得可能なためです（正規化の考え方）
| 会員番号 | 部屋番号 |
|---|---|
| 2222 | 1 |
| 3333 | 4 |
ドキュメント
今回、テーブル定義書やER図の作成は省略しますが、実際の現場では基本的に作成します。
- テーブル定義書：項目の名称、型、桁数、キー（主キー／ユニークキー／外部キー）などの表
- ER図：テーブル同士の関連図（※サンプルのクラス図を更に詳細にするイメージ）
フォーマットは現場ごとに異なると思われるので、現場のルールに沿って作成しましょう。
DDLを書いてみる
ここからは、SQLで実装します。
PostgreSQLで書きますので、よろしければ試してみてください。
※簡単に試す方法はこちらの記事を参考にして下さい。
まずはテーブル定義を作ります。
-- テーブル『部屋』を作成
CREATE TABLE 部屋 (
部屋番号 INTEGER PRIMARY KEY -- 主キー
,部屋名称 VARCHAR
);
-- テーブル『会員』を作成
CREATE TABLE 会員 (
会員番号 INTEGER PRIMARY KEY -- 主キー
,会員氏名 VARCHAR
);
-- テーブル『予約』を作成
CREATE TABLE 予約 (
会員番号 INTEGER UNIQUE REFERENCES 会員(会員番号) -- 一意且つ外部キー（会員）
,部屋番号 INTEGER REFERENCES 部屋(部屋番号) -- 外部キー（部屋）
,PRIMARY KEY (会員番号,部屋番号) -- 主キー
);
『予約』だけ、やや複雑です。
このサンプル問題では、一人の会員は複数回の予約はできないようです。
- 会員番号と部屋番号が主キーの理由：会員が、どの部屋を予約するか？ という『組合せ』であるため
- 会員番号がユニークの理由：クラス図にて予約への多重度が最大１であるため
- 会員番号が外部キーの理由：会員テーブルに登録されたお客様に限定するため
- 部屋番号が外部キーの理由：部屋テーブルに登録された部屋に限定するため
DMLを書いてみる
続いて、データを投入します。
さきほど記載したデータイメージの通りにSQLを書きます。
-- 『部屋』のデータを作成
INSERT INTO 部屋 VALUES
(1,'海側の客室')
,(2,'山側の客室')
,(3,'大人数向けの客室')
,(4,'喫煙者向けの客室');
-- 『会員』のデータを作成
INSERT INTO 会員 VALUES
(1111,'なんの だれそれ')
,(2222,'どこの どなたか')
,(3333,'すごい かねもち');
-- 『予約』のデータを作成
INSERT INTO 予約 VALUES
(2222,1)
,(3333,4);
登録したデータを確認してみます。
SELECT * FROM 部屋;
SELECT * FROM 会員;
SELECT * FROM 予約;
仕上げ（業務に合わせる）
データとしては完成ですが、予約テーブルは正規化しているので名称（部屋名称と会員氏名）がありません。
このままでは「この番号って、何を指しているの？」という状況になります。
では、仕上げに名称を取得します。
今回は敢えて、あまり現場ではお目にかからない（？）自然結合を使ってみます。
SELECT
予約.会員番号
, 会員.会員氏名
, 予約.部屋番号
, 部屋.部屋名称
FROM 予約
NATURAL LEFT OUTER JOIN 部屋 -- 自然結合
NATURAL LEFT OUTER JOIN 会員 -- 自然結合
ORDER BY 会員番号;
想定通り、名称を含めた予約に関する情報を取得できました。
なぜ予約テーブルに名称を持たせないのか？
それは『正規化』を勉強していただくことで理解できると思います。
ただ、例外として正規化せずに予約テーブルに名称を持たせることもあります（非機能要件）
正規化については、過去に記事を書いています。
少し複雑な事例かもしれませんが、ご興味があれば参考にしてみてください。
【開発SE】上流工程の初心者に伝えたい基本設計のポイント（データ中心アプローチ編）
さいごに
今回はサンプル問題を使ってSQLを使った物理的な実装に挑戦してみました。
このように身近なものを試しに実装してみることで、実際に現場で役に立つスキルが着実に身に着くと考えられます。
今後も引き続きデータベースやSQLを中心に、ちょっとしたお役立ち情報を発信していきます。
ここまでお付き合い頂き、ありがとうございましたm(_ _)m
Discussion