2021年(R3)秋 データベーススペシャリスト 感想戦① (午後Ⅱ 問1)
こんにちは。
2021年10月に受けたデータベーススペシャリスト試験に合格して資格手当でメシウマ状態なのですが、物理設計系の問題を選択した人の話って意外とないので、暇つぶしに解き直ししながら感想戦してみようと思いました。
データベーススペシャリストを受験予定の人の何かの参考になれば幸いです。
もばたさんはTwitterを使っています 「データベーススペシャリスト試験さん、対戦ありがとうございました！！！！ https://t.co/wJyaVKQ8AC」 / Twitter
DB触った経験・学習期間など
文系未経験・新卒2年目・SIer・主にアプリの運用保守のシステムエンジニアを名乗るのも憚られるよわよわ会社員です。
得意な言語はロシア語です。(そういう話ではない)
SQLはSELECT文はそこそこ書ける。インデックスとかDBエクスポートとかバックアップがどうだの所は実はよくわかっていない。
FEを2021年1月・APを2021年4月に受験、合格。なので午後Ⅰ免除。
DBの学習は2021年7月ごろ午前Ⅱ対策を開始。通勤時間中心で、総計で10時間くらい。
午後問題は2021年8月から休日に開始。
2016~2020年の午後問題のうち、物理設計系(午後Ⅰの問2・問3と午後Ⅱの問Ⅰ)に絞って2回ほど繰りかえし。総計50時間くらい。
それではさっそく本年度を振り返っていきます。設問・小問ごとに。
0.本文
午後Ⅰは問3のSQLが簡単だったのでワンチャンありますねえ！などと調子に乗りつつ迎えた午後Ⅱ。
6ページ。「問1」開く。不動産。就職上京の時に池袋西口の不動産屋さんで賃貸アパート契約した記憶は役に立たなさそうだし、「３．販売業務」の項目あたりからしてむずそう。どうでもいいけど「追客(ついきゃく)」を追い諭吉・オイキムチのノリで心の中で120回くらい「おいきゃく」と呼んだ気がする。まあでも例年と構成が変わらずひと安心。
ちなみにわたしは始めに本文一通りざっと見て、問題文の内容から本文のどこを参照すればいいかすぐ考えられるようにする派です。
1.1 設問1(1)
(1)ア～ウ
スーパタイプ・サブタイプの関係をどう実装するかの問題。そんなに難しくなかったと思う(正答できてたとは言ってない)
わたしは論理設計をろくに勉強してこなかったので一瞬固まる。
長考の末、
方式①は列たくさんのクソデカTBLひとつ、
方式②は4つのテーブル
方式③は3つのテーブルと解釈。
アは間違いなく間違えてた。「スーパタイプの属性が冗長」などと寝言を書いていた気がする。
本文(P12の4行目)に「不要な選択・結合・和集合が必要…」と書いてあるので、ここを踏まえる必要がありましたね、と。性能の話で、格納効率の話してもちょっと違うでしょう。
ここでは、ひとつのTBLに受付・実施・応対すべての追客情報が格納されているので、どれかひとつのサブタイプ(すなわち、受付・実施・応対のどれか一つ)に絞らなきゃいけないね、と。はい。
イ・ウはあってた気がする。
イは「結合・和集合が必要」と。勉強不足であんまりイメージがわかないけど、例えばひとりの顧客のとある物件に対しての追客すべてを出すなら、サブタイプ(すなわち受付・実施・応対)ごとに別のテーブルから抽出するだけにUNION (ALL)だよね。スーパタイプの追客区分とかほしいなら、PK(主キー)でがっちゃんこしないとだよね。
ウは「和集合」。スーパタイプの列は各サブタイプのTBLで持ってるので、UNIONで3つのサブタイプ分くっつければいいよねと。
(1)エ・オ
エは余裕の善子ちゃん。「すべての追客(つまり3つのサブタイプすべて必要。)と接触日時・追客種別(つまりスーパタイプ)」ということで、G。あと、本文P13最上部の「方法②では、処理5の応答時間に不安..」がヒント。たくさん処理するんだね、と。ミック先生の「SQL実践入門」にもある通り、結合や和集合にはコストがかかるし(勉強してるアピール)。
オは個人的には苦戦。スーパタイプが必要なのはいいとして、「資料請求数・来場予約数・来場応接数・購入申込受領数」ってどこから持ってくるの…?わからん。(CV.多井隆晴)
冷静に考えてみると、ひとりの顧客がひとつの物件に対して行う資料請求なり、来場予約なり、購入申し込みの行動は各1回のみか。件数だけなら追客種別を集計関数かければいいからサブタイプ参照しなくていいよねってことでたぶんC。P14の制約②にあたる所ですね。結局本文なんて読み切れないんだよなあ。
そんなこんなを当時はパニックでよくわかってなかった気がする。業務要件をイメージするんだけど、そこで変な主観が入ってはいけないのでむずかしい。論理設計の学習もしとけば盤石なのかな…?
1.2 設問1(2)
これはさすがに楽勝だった気がする。外部制約のしがらみ順にTBLの順番つけるだけ
1.3 設問1(3)
ケは、UNIQUE制約について書いたのは間違いないけど、あってたか自信ない。解き直しの時は、「顧客コード・接触日時と顧客コード・追客種別の2つに一意性制約…」などと書いてしまってた。まあでも本文のとおり素直にやればいいか。
コはP13末尾でいうOROW(更新前の行の値)とNROW(更新後の値)を比較しなきゃだよねの話。仮に誤った値が入ったとして、その誤った値でUPDATEして、COMMITする前にエラー通知しなきゃという話?まあまあ簡単かしら。
1.4 設問1(4)
不正解。契約TBLのロールバックのことは書けてたんだけど。AFTERトリガで契約TBLが更新済で、契約TBL.商談ステータス≠商談TBL.商談ステータスになるし。追客TBLのロールバックなどと寝言を言っていた。できるだけ最新の状態でなきゃいけなくて、ロック待ちタイムアウトということでジョブのリトライくらいスケジュール設定しとけよということですか先生。それはそうっちゃあそうだけど思いつきませんでした。まいった。
2.設問2
(1)サのCOALESCEは書けなかった。つづりの話ではなく、思いつかなかったです。それでよくSELECT文書けますとかぬかしたなおい。
間違えた原因としては結合後のR1TBLをイメージできていなかった。INNER JOIN(内部結合)で結合してしまったせいで結合キー値がNULLの行が抽出できなくて怒られるのは非常によくある話。
脳内だけで処理できなかったらサブクエリのTBLもある程度丁寧に書き出してみろとあれほど言われたのに。
(2)は簡単な感じがする。
でも、ソで≧を≻とか書いてしまってたかもしれない。
3.1 設問3(1)
出ましたメインディッシュ。みんな大好きシステム障害。きらい。
(1)(a)
計算問題。午後Ⅰの問2より簡単な気がした。
aは404000回のストレージI/Oに0.01秒/回をかけておしまい。やさしい
b・c・dは丁寧に。
b：追加では１ページの平均行数まではバッファ上で処理ということで、なんで割り算なのかはいまだに納得してないけど、50000×8÷25=16000。
50000(行)÷25(1ページ当たり行数) = 2000(ページ)×8(時間)で16000ページ(分のストレージI/O)ってことか。
c:更新は、5000×8=40000。更新対象行の物理データファイルはばらばらになってて、1行ごとにストレージI/Oが発生するので、40000回のストレージI/Oというイメージ(?)わからん。全然わからん。
d:てことで56000÷0.01=560
e:16:00~18:00の2時間分のlog。
f:(5000×2÷10+50000×2)×0.01
(1)(b)
解けてたか自信がない。本文にもある通り、増分バックアップは最後の増分バックアップとの差分を保存するので、追加が多くて既存行の更新が少ない追客TBLはログからの復旧対象行の数が差分バックアップの時とそんなに変わらない。でも既存行の更新が多い商談TBLは、同じ行に複数更新がかかっているときに増分バックアップだと何度も同じ行を「復旧」する羽目になって無駄だよね、ということで解答例になるのかしら。
たとえば商談TBLのある行(顧客コード:test00、物件コード:test000の行)の商談ステータスが
障害発生日の
全体バックアップで'1'(接触開始)
その日の11:00に'2'(商談中)になって、
その日の15:00に'3'(要望書取得)になって、…の場合、
差分バックアップだと16:00にとったやつを使って
'1'→'3'にできるけど
増分バックアップだと
12時の増分バックアップで'1'→'2'にして、16:00の増分バックアップで'3'にして…と2回もログから更新しないといけなくてI/Oいっぱい発生しちゃうね。
【今更聞けない】差分バックアップと増分バックアップの違いとメリット | BackStore Blog
3.2 設問3(2)
実際にこの商談管理システムの担当者だったとしたら「なんで(送信ジョブ)停止しねえのよ～気い狂うわ～～」とか言ってそう。絶望した!
(2)(a)
障害ケース②の設問がくっそうざかった記憶。あんまりうまくかけた記憶がない。「誤登録発生後も行の更新・追加が発生しているから」的に書いた記憶。
まず、(a)はご登録の後も普通に追客したり商談したりしてることを見落としてはいけない。もすこしはっきり書いて♡
そこが読み取れてたら、後藤ロク前まで戻したとして、その後の(正常な、あるべき)更新をどうやって戻すねんということすか。
(2)(b)
これもきちい。CRUD表が欲しい。本文9Pにあるように、処理②では来場予約データには開始予定日時・顧客コード・物件コードなどが入ってる。追客TBLは追客(の種別)ごとにあたらしい行を作成してるので、処理②でも「追加」が行われる。商談TBLは顧客・物件で一意なので、処理②で追加のみならず更新もありうる、と。で、本文P18のような処理をする。
誤ってINSERTした行の削除フラグをONにした後で、問題になっているSQLを実行する。追客TBLには顧客・物件ごとにたくさん行があるので、そこの最新の行の接触日時が本来の商談TBL.最新接触日時である。で、削除フラグが立っていないものから選ぶ必要がある。障害復旧の手順をよく読んでなくて、削除フラグ=0を書けなかった気がする。
個人的に、障害ケース②でだいぶんパニックになって時間をくって、最後の障害ケース③に時間をさけなかった気がする。
3.3 設問3(3)
試験のときは障害ケース③のあたりで時間が残ってなさ過ぎて焦りに焦って頭の中で「あぽぽーw」とか叫んでた気がする
名寄せって過去問にも出てた気がする。
障害を復旧するイメージが大事。想像するだけでも、大丈夫。なんだけど、実際の試験の時はm・p・ｓにろくなことが書けてなかった記憶がある。
振り返ってみたら単純で、本文P10・P11を見ながら
・作業用DBをバックアップに戻して、
・作業用DBをロールフォワードで誤更新直前に更新ログで復旧して、
・作業用DBから障害対象行だけエクスポートして
・本番DBに障害対象行をMerge(上書き)でインポートする、と。
で名寄せジョブをリラン。まあ、名寄せジョブの処理対象が名寄せしないといけない顧客の行なので、誤更新後に追加された行とかにも影響ないか。
4.午後Ⅱ 問1を選ぶ、という選択について
わたしはクソザコなので物理設計とか全然わかんない人なのですが、SELECT文をそこそこ使えるのが物理設計を選ぶ障壁を下げた原因なのでしょうか。私はパッと見楽そうだったので物理設計に決め打ちしました。
実際の所、各個人の積んできた知見によって選ぶべき問題は違うという金太郎飴みたいな結論になりました。
最初は論理・物理両方を一通り目を通しておいた方が理想だとは思います。けっきょく、物理設計の問題でも1対多とかのリレーションわかってないと話にならないところあるし。
5.おわりに
てことで手ごたえはなかったけど午後Ⅱは77点でした。部分点いっぱいくれたね絶対。
ひま人なので、「点数ひくいねざぁ～こ♡」とか「結局DBとっても仕事の役にたたないよね(※)」などの反応、お待ちしております。なお、上記のコメントはご指摘の通りでございます。
(※)DBの勉強の過程でインデックスさん(テーブルスキャンとインデックススキャン)とお近づきになれたのは役に立ちました。
気が向いたら、午後Ⅰに続きます。
読んでくださった方、ありがとうございました。
おわり