【デスペ】令和5年秋午後1問1の解説（データベーススペシャリスト）
このNoteでは「デスペR6春午後1問1」の解説をします。
私はデータベース設計に絞って、SQLは絶対にしません。
データベース設計は問題文（仕様）をER図とスキーマ表に矛盾なく実装する「作業」です。
問題文で見つけたスキーマ名（表名）と属性名（項目名）が、ER図とスキーマにあるかを確認し、なければ追加。次に、スキーマ間の関連（ER図のリレーションとスキーマの外部キー）が適正か。業務処理に伴うデータベースの更新が正常に行われるか。この3点を段階的に組み立てていきます。
データベース設計は地道な作業。言葉と表を大まかにも細かくも手厚くチェックする忍耐と速さが必要です。正直、真面目でない・一発逆転を狙う・コツコツできない方には向かないです。私もお力にはなれません。
しかし、他のSQLや運用の問題と違って、データベース設計は構成が決まっています。問題文・ER図・スキーマ表は必ず。あとは、処理の追加による設計変更、各スキーマへの権限がプラスされて出題される形。
しっかり学習をすれば得点再現性がとても高いのです。
このNoteには、過去問解説だけでなく、別の角度での解法・設計の基礎も添えました。一度は解いてがっつり復習して損はありません。
あなたの「データベーススペシャリスト合格の戦略立て」の役に立ったら嬉しいです。是非読んでいって欲しいです。
このNoteは、私が独学合格した経験と、IT専門学校での授業のノウハウで書いています。合格のお手伝いに少しでもなったら嬉しいです。
それでは始めましょう！
設問1(1) | 主キーであるキーワード「コード」「番号」「識別」
候補キーとは「主キーの候補となるキー」。
図2のスキーマの各項目の記述を、問題文から見つけて判断します。
項目名に「コード」「番号」、役割に「識別」という言葉があれば、主キーになる可能性が高い、と思って大丈夫です。また主キーの特性は主キー制約（一意制約、非NULL制約）なので「重複」が許されるかどうかも焦点です。
社員コード：3-(1)「社員コードで識別」の「識別」からキーになり得る。
社員氏名：3-(1)「同姓同名の社員は存在し得る」から、一意性がないのでキーになり得ない
社員所属組織コード：3-(2)「複数の組織に所属し得る」より、社員コード（など）と複合主キーにしそう
社員所属組織名：1-(1)「組織名は重複しない」より、組織コードと同じくキーになり得る
社員所属上位組織コード：1-(2)「一つの上位組織に属する」より、所属組織から一意に決まるので、キーにしなくて良い
社員所属上位組織名：1-(2)「一つの上位組織に属する」より、所属組織から一意に決まるので、キーにしなくて良い
社員役職コード：
2「役職コードで識別」よりキーになり得るかもだが…
3-(3)「同一組織で複数の役職には就かない」より、複合主キーにする必要はない。現状、(社員コード, 社員所属組織コードor社員所属組織名)で一意に決まるので、キーにしなくて良い
社員役職名：2「役職名は重複しない」より、キーになり得るが、社員役職コードと同じで、社員所属スキーマのキーにならなくて良い
報告先社員コード：3-(4)「所属組織ごとに、報告先となる社員が高々1名決まっている」より、各社員の所属組織ごとに一意なので、キーにしなくてよい
報告先社員氏名：3-(1)「同姓同名の社員は存在し得る」ので、キーになり得ない
以上より、社員所属スキーマ（社員所属表と云ってもOK）の一行を特定する（一意に決める）には、（社員コード, 社員所属組織コード）か（社員コード, 社員所属組織名）を複合主キーに設定します。
設問1(1)の但し書きの「候補キーを全て挙げよ」から「複数パターンあるかな？」、「候補キーが複数の属性から構成される場合は」から「複合主キーかな？」と、疑うようにしましょう。
ただ最近は「四捨五入する時は～」と書いてあっても、四捨五入を使わない問題も出ています。「必ずある」と思わず「高い確率であるんだろうなぁ」と思う程度が良いですね。
設問1(2) | 3種類の関数従属性「完全/部分/推移的」
正規形の判定なので、各項目の関数従属を見れば良いですね。
関数従属性を図示する
関数従属は、ある項目で別の項目が決まること。
3種類あります。
主キーから決まる（完全関数従属）
（複合）主キーの一部から決まる（部分関数従属）
主キー以外から決まる（推移的関数従属）
上図のように部分関数従属が残っているので、第一正規形です。
根拠は模範解答の書き方を覚えておいた方が不安になりませんよ。地味に「全ての属性が単一値をとり」は、「繰り返し項目がない」ことを意味していて、非正規形ではないと記述しています。
とはいえ私の解答は「主キーを社員コードと社員所属組織コード（または社員所属組織名）としたとき、社員所属組織名が社員所属組織コードに部分関数従属しているから」。「単一値」がないので×の可能性が高いです。
単一値でない時点で、非正規形が確定します。部分関数従属や推移的関数従属がなくても、単一値でない時点で非正規形です。
また、主キーが1つの時点で、第1正規形ではありません。主キーが1つなので、主の一部に部分関数従属するわけないので。非正規形でなければ、自動的に第一正規形を満たし、第二正規形以降になります。
主キーだけに従属するように表を分ける
何となくで良いので、以下のように表を分けられたら「センスあり」です。
正直「部分的関数従属が～」とか考えてチクチクやっていると時間がありません。最初にズバーンと「こんな形かな」とできた方が良いです。
データベース設計・正規化は、まずはセンスを磨いて、その後、確認や説明をするために理論を覚えたのが、私の勉強法でした。
データベースは第三正規形を目指します。
第三正規形は、主キーから全ての項目が決まる状態です。
非正規形：繰り返し項目が残る
第一正規形：部分が残る（繰り返しを除いた）
第ニ正規形：推移的が残る（部分を取り除いた）
第三正規形：完全だけが残る（推移を取り除いた）
正規形で残っている従属性と、正規「化」で取り除く従属性がずれてるように見えるので注意を。例えば、部分関数を取り除くのが「第二正規化」、取り除かれた後が「第二正規形」で、推移的関数従属が残っています。残っていなければ自動的に第三正規形に移行します。
あと地味に「計算できる値」を消す、のも含まれます。例えば、商品の「単価」と「個数」が分かれば「支払金額」が分かりますよね。よってデータベースに「支払金額」の項目を設ける必要は「本来は」ありません。
しかし、毎回計算するのが負担である場合、「価格」が変動する場合などがあるので、「支払金額」をあえて残す/設けることはよくあります。第三正規形を「敢えて」崩すことはよくあります。このあたり、過去問を解いていると分かるようになります。
階層構造エンティティの王道
組織所属表の正規化を仕上げていきます。
2番目の社員所属組織、3番目の上位組織は、1つの組織表で表現できます。
ER図のリレーションを自分にループさせるのは「階層構造」の王道です。
問題文の「1-(2)組織は、階層構造であり、いずれか一つの上位組織に属する」の時点で、スキーマの組織（組織コード, 組織名, 上位組織コード）を、パッと思いつくようにはってください。
データベースには「型」があります。
例えばER図で、発注→発注明細と「1対多」になってますよね。1回の発注で複数の商品を発注しますから。同様に、入荷→入荷明細、発送→発送明細。主キーは発注（発注番号）、発注明細（発注番号, 明細番号）ですね。
発注・入荷・発送との関係は、状況によって少し変わります。
問題演習をしたらER図の形、スキーマの表名・項目名をよく見てください。よくある形、よくある名前が分かります。その後、よくまとめられたテキストを読むと理解が進みます。＞DBの学習法Note
私は、問題文を読んで最初から、組織・役職・社員・組織所属の4つだと思っていたので、図2の社員所属スキーマを見て「おいおい」、設問1の正規化を見て「はいはい。ですよね」と思ってぐらいです。
名前を勝手につける勇気（慣れれば簡単）
正規化する際に、スキーマ名（表名）や属性名（項目名）を、自分で決める場面はよくあります。
最初は少しハードルを感じるかもですが、よくある名前をつければ良いので、過去問演習で分かるようになりますよ。
私としては、「まだ長いなぁ」という印象。
混同しないように、という配慮とは思いますが。
「社員所属組織コード」→「組織コード」
「社員役職コード」→「役職コード」
「報告先社員コード」→「社員コード」
でも問題ありません。むしろ、外部参照している表の項目名と同じ方が分かりやすいと思っています。「組織コード。ああ、組織表の主キーの組織コードを参照してるのね」と。
設問2(2) | スキーマ完成を先にしてみる
ER図のリレーションは、各エンティティ（表, スキーマ）の主キーと外部キーを見てつなぎ、個数対応（1対1や1対多）を考えて完成させます。
私は、先にスキーマの完成させます。問題文（仕様）からスキーマを完成させ、ER図を書いて、不足があればスキーマを変更する解法手順。
文章を読んでスキーマを確認する
私は、問題文を読むとき、表名・項目名を四角囲み、個数対応を丸囲み、他の考慮点・未解決点を▼印でマーキングしています。
この問題の【現行業務】7～10頁には、1～8項目にデータベース仕様が書かれています。表名や項目名が現れたら図2のスキーマにあるか確認する作業をします。
下図は図2（スキーマ）の完成形。問題文の項目との関連を図示しておきました。問題を読んだら、スキーマに書き込むイメージ。
※実際は時間がないので、問題文とスキーマに「レ点☑」する場合が多いです。ただ、見直す時にもう一度解くハメにはなります。お好みで。
設問1でも問われますが、社員所属は第三正規形ではありません。
また4-4の「調達条件（単価, ロットサイズ, 納入可能年月日）」は、未解決なので▼マーキングをしておいてください。7の見積回答で解決します。
赤文字の属性名が穴埋め問題の答えです。
上図。調達条件の項目がないので、遡って探すと4-4にあります。▼マーキングをちゃんとしてたら見つけるのが楽です。
また「製品用途」は6-1に既に書いてあります。基本的に問題文を読む順番にスキーマが完成していきますが、一部前後することはあります。
上図の緑文字は、問題文を読むだけ見落として間違うかもしれません。
後々ER図や出力例などで確認して解答することになります。
理解して欲しいのは、「問題文からスキーマ完成で80～90%は完成」する有効さ、そしてER図や出力表などで矛盾が出ないか最終確認で20～10％を詰めて得点を獲得していくということ。
部分解法について
時間短縮を狙うなら、空欄部分に集中して解くのテです。
ただし、問題に依る印象でした。
問題文が各スキーマ毎に塊になっている状態でなければ、部分解きは有効になりません。
今回だと見積回答明細は8に記載がありましたが、調達条件は4まで遡る必要がありました。部分解きだと、調達条件項目を追加して失点するか、「調達条件ってなんだ？書いてあるのか？」とファインプレイで気づいて探すかの2択になります。
他は全てまとまっていました。
部分解きは、今回は比較的上手くいったのですが、次回（問3）では上手く噛み合いませんでした。急がば回れ、かもしれません。
確実に解いて高得点を狙いたいなら、問題文→スキーマ→ER図→スキーマ修正が安定です。ただし、時間がかかるので、速さと正確さでバンバン処理していく訓練が必要です。
私は、しっかり過去問演習で最適化しました。午後1が70点、午後2は84点です。点数は余裕はあります。しかし時間的にはいつもギリギリでした。
設問2(1) | 完成したスキーマからER図をチェックする
問題文からスキーマを完成させたらER図に矛盾がないか確認します。
スキーマの外部キーに注目して、ER図にあるべきリレーションがあるかチェック。なかったらリレーションを追加。個数対応は問題文から、問題文になければ一般常識で考えてください。例えば、発注と発注明細は「1対多」だと、問題文に明記がなくても常識で分かります。
一方、ER図でリレーションでつながっているのに、スキーマで外部キーがなかったら、スキーマを修正します。
取引先の主キーが、得意先・仕入先から参照されています。
目安ですが、「区分」の時はサブタイプ（枝分かれ, 排他的サブタイプ）、「フラグ」の時は包含（枝分かれしない）になると思ってよいです。またフラグが複数ある場合「共存的サブタイプ」になると思ってください。
次々に主キーや外部キーなど、名前が同じ/似ている属性名をチェックしていきます。個数対応は問題文から読み取ったり常識の範囲で判断していきます。
取り扱いブランドEは、典型的な「連関エンティティ」です。
仕入れ先EとブランドEを直接つなぐと「多対多」になるので、間に連関エンティティを設けて、「1対多」「多対1」に解消しています。連関エンティティは複合主キーになります。
取り扱いブランドEの主キーが「取引先コード」「ブランドコード」の複合になってますね。
連関エンティティは、電子部品をキット（まとめ売り）にする時にもよく使われます。
見積依頼Eの取引先コードを、つなげられそうなエンティティが3つあります。
問題文を読み返して特定してください。7-1に「複数の仕入先に見積依頼を行う」とあります。
今回の依頼と回答、他には受注と発送のように、それぞれが違う業務だけど連携しているエンティティ間の個数対応は慎重にやってください。
例えば、受注と発送が「1対1」の場合もあれば、商品の発送準備が終わった都度発送する場合は「1対多」になるのはよくあります。更に、受注と受注明細・発送と発送明細が絡んでくると、複数の形があり得るので、注意深く取り組みましょう。
「○○」Ｅと「○○明細」Ｅは、ほぼほぼ「1対多」確定です。
通常は、発送Eの主キーが発送番号なら、発送明細Eの主キーは発送番号と明細番号、のように、主キーを引き継ぎます。
今回も見積回答の主キー見積依頼番号を引き継いでますが、見積回答番号を新設していないので、特殊だなぁと思いました。
回答が複数回に分かれるだけでなく、見積も複数に分かれるのを、明細番号だけで吸収しています。私は、調達条件番号を新設しそうになりました。
入荷と入荷明細は「1対多」でOKです。
もし「1対1」なら、入荷明細の明細番号が要りません。入荷番号だけで充分ですし、そもそも入荷Eと入荷明細Eに分ける必要がありません。
設問3(1)-(a,b) | 階層化の王道は自己参照
階層構造をもつエンティティの設計は、すでに設問1で見ています。
組織（組織コード, 組織名, 上位組織コード）。7頁1-(2)にも「組織は、階層構造であり、いずれか一つの上位組織に属する」
品目分類エンティティ（品目分類表）を改訂します。
今までが、品目分類（品目分類コード, 品目分類名, 上位品目分類コード）。上位品目分類コードは外部キーで、自分自身を参照します。
また10頁「品目分類を大分類、中分類、小分類のような階層的な構造」なので、1つの大分類の下に複数の中分類、1つの中分類の下に複数の小分類があるはずです。よって「1対多」のリレーションになります。
以上から解答を作ります。
a：品目分類エンティティから品目分類エンティティへ1対多のリレーションを追加する
b：
関係名：品目分類
属性名：上位品目分類コード
※外部キーなので下線点線
設問3(2)(a,b)
10頁より「1件の発注明細に対する納品を分けたい」ので、発注明細Eから入荷に絡むEへのリレーションを「1対多」に変更します。図1を見ると、発注明細Eから入荷明細Eに「1対1」でつながっています。
よって解答は、「発注明細エンティティから入荷明細エンティティへのリレーションを1対多に変更する」。
発注明細と入荷明細のスキーマを確認します。
発注明細（発注番号, 発注明細番号, 指定納入年月日, 見積依頼番号, 見積り解答明細番号, 発注ロット数）
入荷明細（入荷番号, 入荷明細番号, 発注番号, 発注明細番号）
今まで「1対1」対応だったので、「発注番号, 発注明細番号」の複合外部キーで充分でした。よって入荷明細エンティティに項目を追加して「1対多」対応に変更します。
ただし「入荷明細」に「分納番号」の追加は不要です。なぜなら既に入荷明細番号があるため。明細番号で小分けにすれば良いのです。私は追加しそうになって考え改めました。
また10頁に「未だ納入されていない数量である発注残ロット数も記録するようにしたい」ので、発注明細に発注残ロット数を追加します。
さらに、いくつ入荷したかも記録した方が良いので、入荷明細に入荷ロット数も追加します。
以上より解答は、
①関係名：発注明細エンティティ
①属性名：発注残ロット数
②関係名：入荷明細エンティティ
②属性名：入荷ロット数
2つのファインプレイが、必要だったので難しい問題でした。
入荷明細に分納番号を追加しなくて良い（明細番号で吸収できる）
入荷明細に入荷した個数（入荷ロット数）を追加する（問題文に書いていない）
発注明細の方は確実に正解できるので、半分ゲットで良しとしましょう。私は「入荷明細に分納番号を追加」で不正解でした。
まとめ
お疲れ様でした！
Noteでは初めてDBを解説しましたが、なかなか説明や図示が難しい印象でした。授業だと広々と板書できますし、声で伝えられますからね。
とはいえ、正答に至る考え方・解く時のコツなども含めたので、少しでも役に立てたら嬉しいです。
＼私の4ヶ月の学習履歴／
p.s. 普段は >> 専門学校とIT就職のブログ << をやってます。
でわでわ（・ω・▼）ノシ
いいなと思ったら応援しよう！
