【失点小鉢】デスペ令和3年秋午後1問1（データベーススペシャリスト）
このNoteでは、DB令和3年秋PMI問1の解説をします。
＞前回＜ は、1文1文がっつり解きました。今回は、私が本試験でテキパキと解くときの速度で解説しています。
データベース設計は、問題文からスキーマとER図を完成させる作業がメイン。問題文を読み解いて、矛盾ない設計をするには時間がかかります。
データベース設計では、速く・正確に解くことが重要です。
すでに確定しているスキーマの記述は軽くチェックで流し、設計に重要な記述を見落とさない注意力も必要。実際に運用した時の不具合と改修にも、考え及ばねばなりません。
このNoteの解説には、問題文の何に注目して設計しているか、ER図を矛盾なく設計する解法テクニックを添えました。
前回よりは、さくっと読めますので、通常の復習に使って頂けたら嬉しいです。
それでは始めましょう！
私の4ヶ月の勉強法に興味が出たらどうぞ。私が独学合格した経験と、IT専門学校での授業内容を基に作っています。
設問1(1) | 問題文→スキーマ
会員表は問題文通り。
特段注意すべき情報も疑問点もないので進めますね。
企業と店舗についてはよくあるパターンですね。
店舗の主キーが、企業コードと店コードなのもオーソドックス。
ただ、図2で全スキーマを見ても企業スキーマがないので「企業名を記録しないって珍しいな」とは思いました。「店舗名」列に「A
社B支店」と記録するつもりなんでしょうか。
データベース設計で「違和感」「感覚」は結構役立ちます。後に「企業」「商品」絡みのスキーマを新しく作る設問が出てきます。
商品のスキーマ。
問題文で、主キーが企業コード＋商品コードと明記されていますが、図2には下線が引いてません。「下線を引かせる設問でもあったかな？」と少し疑問になりました。
忘れないうちに、スキーマの2項目に主キーの下実線をつけておきましょう。
また、企業コードと商品コードなので、店コードを使っていません。店舗スキーマで話題にしましたが、「なおのこと、企業スキーマが必要だったんじゃないかなぁ」と思い返します。
あと「JANコード（いわゆるバーコード）も主キーになりそうだなぁ」「横断分析用商品コードってなんだ？」とも思っておきます。
疑問だった横断分析用商品コードについてすぐに記述があるのでラッキー。同じ商品なんですね。販売元は違うけど、製造元は同じなケースなどでしょう。
また、コードは主キーになるけど、商品名は「一意ではない」ので「主キーにはなれない」ってメッセージも受け取ります。
更に横断分析用商品コードの説明です。
念入りに記述してきたので設問になってくるのでしょう。
上図のように、
同じ商品があれば、それと同じ横断分析用商品コードを付与
同じ商品がなければ、新たに横断分析用商品コードを採番
とのこと。読んだだけでは分からないので、具体的な値を考えました。
さらに横断分析用商品コード絡み。
「ここまでシツコイと何かあるんだな」と確信します。かなり疑っています（図でも橙や赤になりました）。
「数日を要する」。これは課題点。「絶対障害になるか改善を問うてくるでしょ」と▼マーク。
私たちの感覚だと「ほーん」「数日ぐらいかかっても問題なさそう」ぐらいに思っちゃうんですが、試験的・IT的には大問題。必ず▼マークしてください。＞長文問題の解法Note（その4：怪しいポイント）
次はポイントの話になるので、頭を切り替えます。スキーマの穴埋めもできるようになりますね。
如何にも項目になりそうな記述（緑枠）、主キーになる記述（赤枠）が「支払いスキーマ」にあるのをチェック。
穴埋めa, bはちょっと難しいかもですが、「ポイントの利用場所の記録」と思い至って解きます。（私は、イマイチ特定できなかったのでc, d, eの後に埋めました）
「a：加盟企業コード」、「b：店舗コード」に外部キーの下破線を忘れずに。
今回は優しいです。設問文に「主キーは実線、外部キーは破線」の旨が明記されているので。大抵は「図2の書き方に従って～」などアバウト目な書き方がされます。キーの下線は忘れずに。
次はポイントの付与。
支払いでポイント付くのは分かりますが「商品によっても付くのは珍しいなぁ」と思いました。
dは自信ないかもですが、「購入数」をとりあえず入れます。何でもかんでも保留にすると覚えきれないし進みませんからね。後になって見返しても問題ないので確定させました。
「購入数×商品単価×ポイント付与率=付与ポイント だけど、付与率が載ってないなぁ」と疑問に思いつつ進めます。「なんかクーポンもあるから、付与率を管理するスキーマがあるんだろうな」と。
支払方法のスキーマ。現金だけでなく、クレジットカードや電子決済もありますからね。
cに「支払金額」を入れます。「支払方法コードから付与率ひっぱってきて、付与率×支払金額=付与ポイント」なんでしょう。
なお、レシート番号+支払方法コードで複合主キーなので、「1つの支払いで複数の支払い方法が選べるってめっちゃ珍しいな」と驚きました。
いよいよポイント付与率の管理スキーマ。
fが「ポイント付与率」。特段問題はありませんね。
支払方法スキーマが、ポイント設定からポイント付与率を引っ張ってこれるように外部キーを作ります。下破線を忘れずに。
これで「付与率が、ポイント設定→支払方法→支払い方法明細・購入商品明細まで伝わって、付与ポイントの計算ができるな」と思って次へ進みます。
次はクーポンの話。再度、頭を切り替えます。「ポイント付与率が変わる話があったなぁ」と。
まずはクーポンを定義するスキーマ。問題文から穴埋めgは特定できませんでした。まだまだ記述があるので保留にして、読み進みましょう。
クーポンの種類の話。2種類のクーポンの詳細スキーマがあるのを確認しましたが、穴埋めgはまだ特定できません。
クーポンの利用記録について。
「どのクーポンをどの支払いで利用したか」なので、
どのクーポンを→クーポンコード：j
どの支払いで→レシート番号：k
あとはキー設定。
「1回の支払いで複数のクーポン」の記述から「1対多」。あと想像で逆視点「1つのクーポンは色んな人の支払いで使われるよな」から「1対多」。つまり「多対多」なので複合主キーにして連関エンティティにします。＞ER図の代表パターンNote
クーポンの配布の話。
「同じ会員に同じクーポンを配布しない」旨から、「配布したら記録しないとね」と考えます。
配布された会員を特定するのは「会員コード」、配布したクーポンを特定するのは「クーポンコード」。
キー設定は「クーポン利用」と同じ考え方。
1人の会員に複数のクーポンは配布するだろう：「1対多」
1種のクーポンは複数の会員に配布するだろう：「1対多」
つまり会員とクーポンは「多対多」
両項目を複合主キーにした連関エンティティを作る
以上より「h：会員コード」「i：クーポンコード」。模範解答と逆ですが順不同なのでOK。
最後の最後に穴埋めgの記述がありました。
「なんか見落としたかなぁ」と不安になりつつ、「結構解けたからまぁいいか」と思ってもいました。
設問1(2) | 問題文&スキーマ→ER図
スキーマが完成したので、各スキーマを見て、外部キーがあればリレーションを繋ぐことを考えます。外部キーながなければスルーします。
また「区分」や「フラグ」があれば「股あり△」や「股なし△」も書きますが、今回のスキーマにはなかったですね。＞ER図の代表パターンNote
※以後、スキーマとエンティティを同じ意味で使います。文字数が少ない方が読みやすいかなとスキーマを使いますね。
まずは「店舗スキーマ」と「会員スキーマ」。外部キーがないのでスルー。
ER図のリレーションは、エンティティを1つずつ見て「リレーションがありそうか」を考えていきます。「AとBのリレーションは」のように組み合わせでは考えません。なぜならどの組み合わせを考えたか忘れてしまうから。
ここでは「店舗からつながるかな」「会員からつながるかな」と考えましたよね。「店舗と会員はつながるかな」「店舗と支払はつながるかな」と組み合わせでは考えません。
「支払スキーマ」は外部キーが3つ。加盟企業コード+店舗コードで「店舗スキーマ」から引っ張ってきます。よって関係するスキーマは「店舗スキーマ」と「会員スキーマ」。
個数対応を考えます。
店舗と支払
1つの店で多くの支払いがある。
1つの支払いは1つの店舗
「1対多」
会員と支払
1人の会員は多くの買い物をする
1つの支払いは1人の会員がする
「1対多」
「店舗スキーマ」と繋がってなかったので「1対多」で繋げます。
最近は「0～」「1～」の記号を書かす問題はないから、助かりますね。
「支払方法明細スキーマ」。レシート番号で「支払」と、支払い方法コードで「支払方法」と繋がってます。他に外部キーになるのはないので進みます。
「購入商品明細スキーマ」。レシート番号で「支払い」と繋がっています。1回の支払いで複数の商品を購入するので、明細を作っています。商品は加盟企業コード＋加盟商品コードで特定しています。3つの複合主キーはなかなかヘビィですね。
「店舗スキーマ」と繋げたくなりますが、支払いの明細なのでと思い留まります。
「支払い方法スキーマ」はポイント設定コードが外部キーなので。「ポイント設定スキーマ」と繋げます。
個数対応ですが、9頁最初に「同じポイント設定を、複数の支払い方法に対応付けることがある」より「1対多」。見逃さないようにしたいですね。
「クーポン設定スキーマ」に外部キーはないです。
「クーポン設定対象店舗スキーマ」では、クーポンコードで「クーポン設定スキーマ」と、加盟企業コード＋店舗コードで「店舗スキーマ」と繋がります。
個数対応は「クーポン設定」とは「1対多」。1つのクーポンが色んな店舗を対象に配布できますから「クーポン設定→クーポン設定対象店舗」。
「店舗」とも「1対多」。1つの店舗は、複数のクーポンで適用対象になるでしょうから「店舗→クーポン設定対象店舗」。
「クーポン配布スキーマ」。
主キーのクーポンコードの大元は「クーポン設定スキーマ」なので繋ぎます。1つのクーポンは複数の会員さんに配布するので「クーポン設定→クーポン配布」。
会員コードも外部キー。1人の会員さんに複数のクーポンを配布するので「会員→クーポン配布」。
「クーポン利用スキーマ」。
主キーのクーポンコードがるので、「クーポン設定→クーポン利用」。個数対応は1種のクーポンは色んな人が使うので「1対多」。
レシート番号は外部キーとして、「支払スキーマ」の主キーと繋がります。10頁最初に「クーポンコードが異なる複数のクーポンを1回の支払いで利用できる」より「1対多」。
設問2(1) | 候補キー
主キーと外部キーはFEやAPで既に知っているとして、DBでは候補キーまで具体的に選べるように覚えてください。午後問題で狩られますよ。＞デスペ3問：キーのAMII問題解説Note
代理キーや代替キーあたりは用語として薄っすらで構いません。
今回の「候補キーを答えよ」→「関数従属性を答えよ」「正規形を答えよ」→「第三正規形にせよ」は、王道問題です。
作問者視点で見ると「スキーマが各個穴埋めだったので簡単だったから出してきたんだな」と思います。穴埋めに1個ずつ項目が入りましたよね。難しい問題だと、穴埋めに何個入るか分からないものもあります。
候補キーとは「主キーになり得る候補のキー」。一意制約とNULL制約を持ち、必要最小限のキー数です。
例えば、学生（学生番号, 氏名, 住所）の時、候補キーは｛学生番号｝と｛氏名, 住所｝。複数名の同姓同名の人が同じ住所には住むことはないでしょうからね（狙わないと無理）。
候補キーは主キーの候補。
「～コード」「～番号」って大抵主キーになるので、そのあたりから考え始めても良いです。逆に「～名」は重複があるので、単独では無理で、別項目と組み合わせ（複合）ればできるかもな印象。
あとは問題文の「一意」「同じ～はない」などの記述にも注目です。
以上のように洗い出すと3項目が怪しい。加盟企業コード、加盟企業商品コード、横断分析用商品コード。
横断分析用商品コードの発見はちょっと難しかったかもですね。
とはいえ、「候補キーを全て答えよ」なので、｛加盟企業コード、加盟企業商品コード｝の1個はあり得ないとメタ読みしてOK。何とかもう1個2個見つけましょう。
確認で具体的データを考えてみます。横断分析用商品コードの採番手順を理解してないとキビシイですね。
候補キーは2つでした。
｛加盟企業コード、加盟企業商品コード｝
｛加盟企業コード、横断分析用商品コード｝
設問2(1) | 関数従属性
正規形の判断。関数従属性は大丈夫ですね。
繰り返し項目がない（単一値を取っている）ので、非正規形ではありません。部分関数従属（青）が残っているので、第一正規形。
設問2(2) | 主キーの選別
｛加盟企業コード、横断分析用商品コード｝が主キーとして不適格である理由を何とか捻出します。
｛加盟企業コード、加盟企業商品コード｝の方が良いと直感的に思うけど、弾く理由が見当たらなくて困りますね。とはいえ主キー制約（一意・非NULL）は満たしているので…。
横断分析用商品コードにケチをつけるために、横断分析用商品コードだけにある事情を探し出すと「同一商品があるかないかの確認に数日かかる」旨がやはり引っ掛かります。
模範解答は「加盟企業商品コードは加盟企業商品が登録された後に設定される場合があるから」。ちょっとイマイチ分からないかも。時間差に言及しているようでもあり、外部キー制約に言及しているような印象。
私の解答は「横断分析用商品コードの採番に数日要するため」。21文字。短いと感じたら「横断分析用商品コードの採番に数日要するが、加盟企業商品コードは即時採番できるため」40文字。「運用上適している」までは無理ですね。
設問2(3) | 第三正規化
第三正規化については、一気にやってもらって大丈夫です。私は一気にやります。
手順通りに、部分関数従属を排して第二正規化して、推移的関数従属を排して第三正規化でも良いですよ。
なお、ボイス・コッド正規形、第四や第五正規形は、早々出ないです。作問者視点では、知識としては知っておいて欲しいですが、それよりも第二or第三正規形での設計・回収・問題点発見の理解度を試したいです。
とはいえ、ネットワーク問題のIPv6と同じで、もし出たら狩られてしまうので、学習ノートの裏表紙にまとめておいて、本試験前に付け焼刃が良いかなと思います。
設問3(1) | 日次パッチの不具合
日次パッチで処理できない理由を考えます。問題文にまだ使っていない情報が丸々残っているので怪しいです。＞長文問題の解法Note（その3：まだ使っていない情報を把握する）
設問3(1)：日次パッチ処理でポイント加算されない場合があるケース。模範解答は「購入の翌日以降にポイントの後付けをしたとき」。
他にも「会員が後日レシートを持っていきポイントの後付け申請をした場合」30文字。などでも良いですね。
設問3(2) | 日次パッチの不具合改修
設問3(2)：改修のために、支払スキーマに追加した属性（項目）の役割は？
模範解答は、3案公開されていました。
ポイント残高に加算済みかどうかを判別する
ポイント残高への加算処理日が分かるようにする
付与ポイントの記録を作成した日で抽出できるようにする
要はポイントを、当日に付与したかどうかの目印になる項目を追加すること。「ポイント加算済みフラグ」に「未/済」を入力するようにしたり、「ポイント付与日」に「NULL/日付」を入力するように改修すれば良いですね。
まとめ
お疲れ様でした！
小難しいテーマが4つあって、全部落とすと大失点になる問題でした。お弁当の小鉢のような印象です。
今回は、私が本試験で早めにテキパキ解くやり方で書きました。
他のアプローチでの学習も必要に応じてやっていってくださいね。
データベース設計をがっちり固めたいなら、1文1文がっつり見ながらの演習を一度やってみてください。＞DBR04年秋PM1問1の解説Note
今回のER図はシンプルで、連関エンティティ程度でした。サブクラスや自己回帰型などの色んなパターンもでます。＞ER図のパターン解説Note
今回「ポイント後付け」に正解できたでしょうか？長文問題からヒントを探すコツがあります。＞長文問題の解法テクニック６のNote
以上ぐらいをやっておけば、データベース設計の基盤は結構できたと思います。
後は問題演習と復習を繰り返して、問題文→スキーマ→ER図の作成手順の効率化を極めていってください。データベース設計は早さと正確さが高得点に重要です。
ぜひデータベース設計で得点を稼いで、他の実装系やSQLのフォローをしつつ、データベーススペシャリスト試験に合格していってくださいね！
私が合格してきた勉強法についてもNoteにしました。まずは書籍の読み倒し方、AMIIの勉強だけでも参考にして頂ければ、嬉しいです。
p.s. 普段は >> 専門学校とIT就職のブログ << をやってます。
でわでわ（・ω・▼）ノシ
この記事が参加している募集
学習方法・問題特集のNoteは全て無料提供を続けます▼ もしご覧になったNoteが有益だったり、私の志に共感されたりしましたら、サポート頂けますと励みになります▼ もちろんコメントでも結構です(・ω・▼)ノシ