Database Systems Engineer Examination

過去問に飽きたころ↓に挑戦してみよう！
iTAC内テクニカルエンジニア（データベース）掲示板（2002.05.01-2003.12.31までのログ）より

設問１

健康保険被保険者証の様式をヒントにボトムアップアプローチの問題　投稿者：シカ狂う　投稿日：3/10 14:08:34

システムの帳票、画面からのデータ項目の拾い出し→正規化→E-Rモデルの作成
をボトムアップアプローチといいます。
午後Ⅱの定番ですが、以下のような健康保険被保険者証の様式をヒントに、そのような問題を作問してみようと思います。
（あくまで作問に徹しているため、下の様式は現実とかけ離れている箇所もあるかと思いますがご容赦ください）

　　　　　　　　　　　　健康保険被保険者証
交付年月日　　　：2000年01月01日
被保険者番号　　：111111
被保険者氏名　　：川崎次郎　フリガナ：カワサキジロウ
被保険者住所　　：神奈川県川崎市高津区高津1-2-3
被保険者生年月日：1945年01月01日
資格取得年月　　：1970年08月
事業所所在地　　：神奈川県横浜市中央区中央1-2-3
事業所名称　　　：あいうえお株式会社
保険者所在地　　：神奈川県横浜市○○区○○1-2-3
保険者番号　　　：1111
保険者名称　　　：○○社会保険事務所

被扶養者氏名　　　フリガナ　　続柄コード　続柄　　生年月日　　　性別
川崎花子　　　カワサキハナコ　　　02　　　妻　　1950年02月02日　女
川崎智子　　　カワサキトモコ　　　21　　　長女　1975年03月03日　女

（念のため、住所はH13春DB午後Ⅰ問2を引用したものであり、実在するものではありません。）

まずこれを関係スキーマとして表してみます。

被保険者（交付年月日、被保険者番号、被保険者氏名、被保険者住所、被保険者生年月日、
資格取得年月、事業所所在地、事業所名称、保険者所在地、保険者番号、保険者名称、
被扶養者氏名1、被扶養者フリガナ1、続柄コード1、生年月日1、性別1、
被扶養者氏名2、被扶養者フリガナ2、続柄コード2、生年月日2、性別2、
被扶養者氏名3、被扶養者フリガナ3、続柄コード3、生年月日3、性別3、
被扶養者氏名4、被扶養者フリガナ4、続柄コード4、生年月日4、性別4）

続柄（続柄コード、続柄名）

#「性別」はさすがに2値なのでコード化しないほうがよいでしょうか。

問題点1：主キー、外部キーが明示されていない。

設問1
関係スキーマ“被保険者”の主キー、外部キーを示せ。

この題材のおもしろさは、“被保険者”をうまく工夫して分割して正規化すると、
家族が被扶養者から抜け出した国民健康保険被保険者証のようなスキーマが得られることです。
（ってかなりかなり無理があるでしょうか^^；）

Re:問題　投稿者：シカ狂う　投稿日：3/10 21:10:28

あ、そういえばこれってけっこう業務知識がいるので、属性の意味を書き忘れてました。
とりあえず必要最低限なものだけ。

被保険者番号：健康保険組合の中で一意になるように振られた、被保険者を識別する番号
　健康保険組合に入った順番に新しい番号が付与される。
保険者番号：健康保険組合を一意に識別する番号
　2002年10月1日現在の全国の健康保険組合数は1,687である。

もしかして現実とはちょっと違うでしょうか？あまり詳しくないのでどんどん突っ込みお願いします。

問題（条件追加）　投稿者：シカ狂う　投稿日：3/10 22:50:24

健康保険被保険者証は定期的に再交付せねばなりません。
健康保険被保険者証は再交付ごとに色が変わり、その一つ一つを識別したい。

Re:問題　投稿者：シカ狂う　投稿日：3/11 10:03:46

> ただ、紛失などによる再交付でも色が変わるのか？
> また、被保険者証の色が保持されていなくてもいいのか？という点を確認したいところです。

なるほど、では紛失などによる再交付の場合は色は変わらないものとします。
以下のように色テーブルを新たに追加し、色コードという属性を“被保険者”に追加します。
同じ色の再利用はしないものとします。

また、少し勘違いしていたようなのですが、
> 被保険者番号：健康保険組合の中で一意になるように振られた、被保険者を識別する番号
> 健康保険組合に入った順番に新しい番号が付与される。
ではなく、
被保険者番号：健康保険組合に加入している会社の中で一意になるように振られた、被保険者を識別する番号
　会社に入った順番に新しい番号が付与される。
であり、新たに
被保険者記号：会社を一意に識別する記号
というのを設けます。
#場合によっては、この記号と番号を一緒にして記号番号としているところもあるようですが、ここでは分けます。

被保険者（交付年月日、色コード、被保険者記号、被保険者番号、
被保険者氏名、フリガナ、被保険者住所、被保険者生年月日、資格取得年月、
事業所所在地、事業所名称、
保険者所在地、保険者番号、保険者名称、
被扶養者氏名1、被扶養者フリガナ1、続柄コード1、生年月日1、性別1、
被扶養者氏名2、被扶養者フリガナ2、続柄コード2、生年月日2、性別2、
被扶養者氏名3、被扶養者フリガナ3、続柄コード3、生年月日3、性別3、
被扶養者氏名4、被扶養者フリガナ4、続柄コード4、生年月日4、性別4）

続柄（続柄コード、続柄名）

色（色コード、色名称）