高校情報I 関係データベースの主キー—一行を特定する条件から表の項目を設計する｜武蔵野個別指導塾

備品の点検記録を例に、日付と番号の組を全行で照合し、欠損や重複を防ぐ主キーを選びます。

よくあるのは、高校生が備品の点検日だけを主キーに選び、同じ日に複数の備品を点検したときに記録が重なる場面です。主キーには、表の各行を欠けのない値で区別する役割があります。日付に備品番号を組み合わせれば区別できる場合がありますが、同じ備品を同じ日に複数回点検するなら、それでも足りません。

出方には、既存の表で重複と欠損を調べる型、記録の単位に合わせて項目を組み合わせる型、追加される行を想定して設計を直す型があります。今ある行と、これから許される記録の両方を確かめます。

行の意味からキーを考える

関係データベースでは、データを行と列からなる表として扱います。列は備品番号や点検日などの項目、行はその項目の値を集めた1件の記録です。備品そのものを1行にする表と、点検を1回行うごとに1行を作る表では、同じ備品番号の繰り返しに異なる意味があります。まず「1行は何を表すか」を決めると、何を区別する必要があるかが見えてきます。

主キーは、その表で1行を特定するために選んだ項目、または項目の組です。主キーの値は各行で重複せず、欠損も許しません。複数項目を組にしたものを複合キーと呼びます。組として異なればよいので、組を構成する個々の項目が繰り返されること自体は問題ありません。図1のように、同じ点検日でも備品番号が違えば、番号と日付の組は異なります。

図1では、AとBという備品を同じ日に点検しています。日付の列だけでは2行を区別できません。一方、番号と日付を一緒に見れば、Aの記録とBの記録を区別できます。ただし、この図だけから将来も必ず区別できるとは言えません。同じ備品を同じ日に再点検できるかどうかが必要な条件です。

主キーに選べそうな項目は、値を全行で比較します。欠損は、まだ記録されていないなどの理由で値がない状態です。空欄を同じ特別な値として扱って主キーにするのではなく、何の記録かを調べて値を確定するか、別の項目を使う必要があります。記録がなくても備品が存在しないと決まるわけではありません。

また、現在の表に重複がないことは必要ですが、それだけでは設計の保証になりません。例えば点検結果がたまたま全部異なっていても、次の点検で同じ結果が現れる可能性があります。名前も変更や同名があり得ます。値が何を表し、どんな追加を許すかという運用の条件から判断します。行を区別できる複数の候補があれば、欠損せず安定して管理できるものを主キーに選びます。

複合キーの比較では、左の列をすべて比較した後に右の列だけを比べるのではなく、同じ行の値を組にしたまま比べます。異なる列を混ぜて別行の組を作ると、存在しない記録を検査してしまいます。日付の表記も統一し、同じ日を異なる書き方で別の値と誤認しないようにします。以下の日付は年、月、日の順で記し、同じ文字列は同じ日を表します。

基本例題：同じ日の記録を区別する

学習用の点検表を作ります。1行は備品1台の1回の点検です。各備品は同じ日に高々1回点検するものとし、備品番号と点検日は必ず記録します。次の表で、備品番号、点検日、それらの組のそれぞれが主キーとして使えるか、全行の値を根拠に答えてください。結果は点検内容であり、同じ結果が複数回出るものとします。

図2のように、まず候補ごとに値を抜き出します。元表の行番号は説明のための印で、点検の識別子として与えられた項目ではありません。

1. 備品番号は A、B、A、C です。行1と行3が A なので単独では使えません。
2. 点検日は 4月3日、4月3日、4月8日、4月8日です。行1と行2、行3と行4がそれぞれ重なります。
3. 番号と日付の組は (A, 4月3日)、(B, 4月3日)、(A, 4月8日)、(C, 4月8日) です。
4. 同じ番号の行1と行3でも日は異なります。同じ日で比較した行1と行2、行3と行4でも番号は異なります。
5. 4組に欠損も重複もありません。与えられた1日1回の条件の下では、番号と日付の組を主キーにできます。

答えを元表に戻すと、(A, 4月8日) は行3に対応します。(C, 4月8日) は行4であり、日付だけが共通です。結果の良好は3行に現れるため、結果だけでも区別できません。図2に示した4組は表の4行から1組ずつ作っており、行を落としていません。

この表では(A, 4月3日)の行を、結果が後で修正されても同じ点検として探せます。ただし番号や日付の入力自体を間違えた場合には、元の点検記録を確認して訂正する必要があります。キーがあることと入力内容が事実に合うことは別の条件です。誤った値が重複していなくても、正しい記録とは限りません。

標準例題：再点検を許した表に直す

今度は、同じ備品を同じ日に複数回点検することがあります。1行は1回の点検で、点検回はその備品のその日の点検を1から順に区別する番号です。番号、日付、回は必ず記録し、同じ備品の同じ日の点検回は重複しないものとします。次の表で、番号と日付の組が主キーに使えるか調べ、使えない場合は示された3項目から主キーを提案してください。各行の組も表の順に答えます。

1. 番号と日付は行1と行2がともに (D, 5月2日) です。2回の点検を区別できません。
2. 番号、日付、点検回の組を行順に作ると、(D, 5月2日, 1)、(D, 5月2日, 2)、(E, 5月2日, 1)、(D, 5月6日, 1) です。
3. 行1と行2は回、行1と行3は番号、行1と行4は日付が違います。残る組どうしも少なくとも1項目が異なります。
4. 欠損はなく、同じ備品の同じ日に回が重複しない条件があるので、この3項目の組を主キーにできます。

元表に戻すと、(D, 5月2日, 2) は調整後の行2です。回を省くと行1も当てはまり、日付を省くと行1と行4が同じ (D, 1) になります。番号を省くと行1と行3が同じ (5月2日, 1) です。この表では3項目のどれを省いても区別が崩れます。

点検回が「その日全体の通し番号」なのか「備品ごとに数える番号」なのかで、保証される組は変わります。表の値がたまたま同じでも、番号を付ける規則が異なれば将来の重複の可能性は異なります。点検回を新しく付ける際は、その備品と日付の既存記録を見て、同じ回を使わないようにします。空欄のままなら複合キーの一部が欠けるため、その行を主キー付きの完成した記録として扱えません。

発展例題：記録番号を使う設計と失敗の扱い

点検の受付時に、記録ごとに重複しない記録番号を必ず発行する運用を考えます。1行は1回の点検です。番号は変更せず、欠損を許しません。同じ備品の同じ日の再点検も許します。下の表について、記録番号を主キーにできるか、備品番号と点検日の組はどうかを答え、追加しようとする2件をそれぞれ受け入れられるか判定してください。受け入れ判定は、既存表に対して各件を独立に調べます。

追加候補は (R04, G, 6月4日, 要調整) と (R02, H, 6月5日, 良好) です。いずれも番号、備品、日付、結果の順に書いています。

1. 記録番号は R01、R02、R03 で、欠損も重複もありません。発行規則も重複を禁止しているので主キーに選べます。
2. 備品番号と点検日の組は先頭の2行でともに (F, 6月1日) です。再点検を区別できず、主キーにはできません。
3. R04 は既存の記録番号と重ならないため、指定されたキー条件では追加できます。(G, 6月4日) の再点検があること自体は違反ではありません。
4. R02 は既存行と重なります。備品や日付が違っていても主キーの重複なので、そのまま追加できません。
5. R04 を戻すと4つの記録番号は R01、R02、R03、R04 です。R02 の候補を戻すと R02 が2行に現れるため、1行を指定できません。

記録番号は行を区別しますが、入力の重複を何でも見つけるわけではありません。同じ点検を別番号で二重に登録した場合、番号は異なるためキーの検査を通ります。点検の原記録と照らして、同一の出来事を二度登録していないかを別に確かめます。逆に、同じ備品と日付が2行あることだけで二重登録と決めると、本当の再点検を消してしまいます。

番号発行に失敗して番号が空欄になった場合は、空欄を主キーにして登録完了とはしません。発行結果を確認し、記録がすでに保存されたかを調べてから処理を再開します。応答が時間切れになった場合も、保存されなかったと決めて別番号で繰り返すと二重登録の恐れがあります。保存の有無が確認できるまで結果を保留し、確認できた状態に応じて再開します。外部の番号発行サービスがエラーを返した場合も、エラーを成功として扱わず、記録内容と処理状態を残して原因を確認します。

誤答を行に戻して直す

基本例題の表で「4月3日と4月8日の2種類があるので、点検日は主キーにできる」と答えたとします。異なる日が複数あることと、各行で日付が異なることを取り違えています。図3のように、4月3日を手掛かりにすると A と B の2行が残り、目的の1行には決まりません。

修正するときは、重複した行を消すのではなく、何を区別したかったかを確認します。AとBは別々の備品なので、両方の点検記録が必要です。番号を加えた (A, 4月3日) と (B, 4月3日) は異なり、図3の右側ではそれぞれ1行を指します。ただし、再点検を許す表なら番号と日付も重複し得るため、標準例題のように回を加える条件が必要です。

別の誤りは、複合キーの各列がそれぞれ重複してはいけないと考えることです。行1と行3の番号はともに A ですが、日付が異なるので組は異なります。反対に、片方の列が違う行を1組見つけただけで全体を合格とすることもできません。全行の組を作り、同一の組がないかを調べます。空欄がある場合は、見かけ上重複がなくても欠損の条件で不合格です。

練習問題

次の5問はそれぞれ独立した学習用の表です。行は表の順に扱い、日付の同じ表記は同じ日を表します。主キーには欠損と重複を許しません。

問1

備品ごとに同じ日は1回だけ点検します。番号と日付は必ず記録します。表の番号、日付、それらの組の各候補について可否と重複する行を答え、組は表の順に列挙してください。

問2

1行は1回の点検です。同じ備品を同じ日に複数回点検でき、回は備品ごとに日付ごとに1から付け、同じ組の回は重複しません。番号、日付、回に欠損はありません。次の2行を区別する主キーを3項目の中から提案し、なぜ番号と日付だけでは足りないか答えてください。

問3

点検番号は各点検に固有で必ず記録する決まりです。次の表はその決まりに違反しています。違反箇所を行で示し、現状のまま点検番号を主キーにできるか答えてください。空欄は欠損です。

問4

記録番号は各点検に固有で、同じ備品の同じ日の再点検を認めます。保存済みの記録番号は Q1、Q2 です。追加候補 (Q3, T, 9月1日) と (Q1, U, 9月2日) は、番号、備品、日の順です。それぞれを保存済みの表に独立に追加するとき、キーの条件を満たすか理由とともに答えてください。保存済みの Q2 は T の9月1日の記録です。

問5

ある表は点検番号 V1、V2 を持ち、点検番号を主キーにします。新しい点検の登録を依頼したところ、応答が時間切れになりました。再送前に何を調べるか、保存済みだった場合と保存されていなかった場合に分けて説明してください。番号発行に失敗した場合の扱いも答えてください。

練習の答えと解き方

問1の答え

番号は行1と行3が J なので不可です。日付は行1と行2が 7月1日なので不可です。組は (J, 7月1日)、(K, 7月1日)、(J, 7月9日) で、欠損も重複もありません。1日1回の条件の下では組を主キーにできます。

各組を元表に戻すと順に行1、行2、行3です。行1と行3は番号が同じでも日付が違い、行1と行2は日付が同じでも番号が違います。3行の比較で判定を確認できます。

問2の答え

番号、日付、回の組を主キーにできます。2行は (L, 8月2日, 1) と (L, 8月2日, 2) です。前の2項目は同じで、回が異なるため区別できます。

点検回は備品と日付の範囲内で付けるので、別の備品や別の日にも回1は現れ得ます。現在の2行で足りるかだけでなく、与えられた採番規則を使って3項目を選びます。組を表に戻すと前者が1回目、後者が2回目であり、両方を残せます。

問3の答え

行2の点検番号が欠損し、行1と行3の P1 が重複しています。したがって現状では主キーにできません。行2に適当な値を推測して入れたり、行3を重複として消したりせず、元の記録と発行状況を調べます。

例えば確認の結果、行2が P2、行3が P3 と確定したなら、P1、P2、P3 は欠損も重複もなくなります。これは確認後の値を例示したもので、問題の情報からその値が実際に正しいとは決まりません。修正後も3つの備品の記録がそれぞれ残ることを確かめます。

問4の答え

Q3 は Q1、Q2 と重ならないので、キーの条件を満たします。T の9月1日という組は Q2 と共通ですが、再点検を許しているので、その一致だけで拒む理由にはなりません。追加後の番号は Q1、Q2、Q3 です。

Q1 は保存済みの Q1 と重なるため、備品や日付が異なっても条件を満たしません。追加すると Q1 が2行になり、番号を指定しても1行に決まりません。別の出来事か入力の誤りかを確認して、正しい番号を確定します。

問5の答え

まず登録依頼の処理状態と保存済みの記録を確認します。時間切れは応答が届かなかったことを示すだけで、保存が失敗した証拠とは限りません。保存済みならその記録と依頼内容を照合し、同じ点検を別番号で再登録しないようにします。

保存されていないと確認できたなら、番号の発行状態も調べ、運用の規則に従って登録をやり直します。状態を確認できない間は保留します。発行に失敗して番号が欠けた場合も、そのまま主キーとして受け入れず、発行と保存の状態を確認してから処理します。正常に保存された場合には、発行された番号が V1、V2 と異なり、目的の点検を1行で指せることを照合します。

覚えておくこと

• 主キーは表の1行を特定し、欠損と重複を許しません。
• 複合キーは同じ行の値を組にして比較します。
• 現在の全行に加え、将来の記録を許す条件も調べます。
• 登録失敗や時間切れでは、保存と番号発行の状態を確認します。

つまずきを見分ける

日付の種類を数えるだけで判定した場合は、同じ日付を持つ行を指でたどり、その値から何行が残るかを試します。複合キーの列ごとの重複を問題にした場合は、同じ番号で異なる日付の2行を組にして比べます。

個別指導では、1行の意味と再点検の規則を本人の言葉で説明し、欠損や重複を含む追加行でも判定が変わるか確認します。どの行を指すかまで戻して考えると、主キーの条件が表の中で具体的になります。