高校情報I 関係データベースと主キー—行を識別する条件を調べて表の分け方を考える｜武蔵野個別指導塾

同姓同名の名簿や貸出記録を使い、重複と参照先を確かめて選ぶのが主キーです。

よくあるのは、同姓同名がある表で氏名を主キーに選び、別の人の記録まで同じ人のものとして扱ってしまう場面です。名簿に同じ名前が並んだとき、氏名だけで行を指せるでしょうか。自分の学校の範囲表と授業ノートを見ながら、行を区別する根拠をたどってみましょう。

ここでは表を分けて関連づけるための基本を扱います。表の名前や記号は練習のために作ったもので、実在の名簿ではありません。列の値が同じか、空欄がないか、別の表に対応先があるかを、行ごとに確かめます。

テストで問われる3つの判断

1つ目は、示された表のどの列を主キーにできるかという判断です。列の名前に「番号」が含まれるだけでは十分ではありません。値が重複せず、空欄もなく、各行を区別できるかを表の末尾まで見ます。さらに新しい行にも同じ規則を守らせられるかを考えます。

2つ目は、2列を組み合わせて行を識別できるかという判断です。片方の列だけに重複があっても、組にした値が重ならなければ、その組をキーにできます。反対に、どちらか一方だけを見て「両方に同じ値がある」と決めてはいけません。同じ組が別の行にもあるかが判定点です。

3つ目は、表を分けた後に、どの列で関係をつなぐかという判断です。記録側に置いた番号が、参照される表のどの行に対応するかを確かめます。名前を各記録に何度も書く設計と比べ、名前の変更をどこに反映するかも説明できると、表を分ける理由まで答えられます。

行を指す印と、別表を指す印

関係データベースでは、情報を表の形で扱います。横の1行は1件の記録、縦の1列はその記録の同じ種類の項目です。たとえば道具の一覧なら、1行に道具番号と道具名を置きます。列にはどのような値を入れるかを決めておき、行ごとに値を読んでいきます。

主キーは、表の各行を一意に識別するために選ぶ列、または列の組です。一意とは、その値で表の中の行を1つに定められることです。主キーの値は重複させず、空欄にもできません。表の一時点でたまたま名前が違っていても、将来同じ名前を登録しうるなら、名前を主キーにする設計は適切とは限りません。

図1のように、道具表の道具番号は道具を区別し、貸出表の道具番号はその道具を指します。貸出表では同じ道具が別の日に何回も貸し出されるため、道具番号の重複自体は問題ではありません。ここでは貸出番号で貸出の各行を区別します。

図1で矢印が向かう先は道具表の主キーです。別の表の行を参照するために置いた列を外部キーと呼びます。外部キーの値は参照先のキーの値と対応します。参照を必須にする設計なら、対応先のない番号や空欄を登録しないという規則を設けます。

表を分けると、道具名を道具表にまとめ、貸出表には番号を記録できます。名前を変更するときは道具表の該当行を直せば、番号を通して新しい名前にたどり着けます。ただし、昔の貸出時点の名前を保存する必要がある場合は、その目的に合わせた別の設計も必要です。表を分ければどの用途にも無条件に十分というわけではありません。

基本例題：貸出記録を番号でたどる

次の道具表と貸出表を使います。道具番号と貸出番号はそれぞれ空欄や重複を許さず、貸出表の道具番号は必ず道具表にある番号を指定します。各貸出は道具を1つだけ指すものとします。

問いは3つです。道具表と貸出表の主キーをそれぞれ答え、貸出表から道具表を参照する列を答えます。さらに各貸出番号に対応する道具名を貸出表の行順に書き、対応の組の件数を整数で答えます。

図2のように、まず道具表では D1、D2、D3 がそれぞれ異なると確かめます。貸出表では K1 から K4 までが異なります。一方、貸出表の D2 は2行に現れるので、その列だけでは貸出の行を特定できません。

解く順番を1行ずつ書くと、次のようになります。

1. 道具表の主キーは道具番号です。3行の値に重複も空欄もありません。
2. 貸出表の主キーは貸出番号です。4行の値に重複も空欄もありません。
3. 別表を参照する列は貸出表の道具番号です。道具表の道具番号と照合します。
4. K1 の D2 は道具表の定規を指します。
5. K2 の D1 は道具表のはさみを指します。
6. K3 の D2 は再び定規を指します。K1 と別の貸出なので、この組も残します。
7. K4 の D3 は道具表ののりを指します。
8. 行順の答えは K1・定規、K2・はさみ、K3・定規、K4・のりで、対応の組は4件です。

逆に、答えの道具名から元の道具番号を読み直すと D2、D1、D2、D3 となり、貸出表の4行に戻ります。道具の種類は3つですが、求めたのは貸出と道具の対応です。2つの貸出が同じ道具を指すとき、種類数と記録数は一致しません。

新しい貸出 K5 に D4 を書いたとします。道具表に D4 がないため、指定された参照必須の規則に合いません。先に実際の道具に対応する行を道具表に登録するか、入力した番号を確かめて正しい番号に直します。対応先が不明なまま、別の道具を勝手に選んではいけません。

少し難しい例題：組にしても重複するか

工作会の申込を、参加者番号と講座番号で記録します。参加者番号 P1、P2、P3 は参加者表にあり、講座番号 C1、C2、C3 は講座表にあります。各参加者は同じ講座に1回まで申し込めますが、異なる講座には申し込めます。各番号は空欄にせず、参照先にも存在するものとします。

参加者番号だけ、講座番号だけ、2列の組のうち、この申込表の主キーに適するものを答えます。また、新しい行 P2・C1 と P2・C3 をそれぞれ追加できるか、理由とともに判定します。

参加者番号だけでは P1 が2行に現れ、どちらの申込かわかりません。講座番号だけでも C1 と C2 がそれぞれ複数行に現れます。2列の組なら P1・C1、P1・C2、P2・C1、P3・C2 の4組が異なります。問題の規則では同じ人が同じ講座へ重ねて申し込めないので、参加者番号と講座番号の組を主キーにできます。

P2・C1 は3行目と組が一致するため追加できません。P2・C3 は既存の4組のいずれとも一致せず、両番号の参照先もあるので追加できます。追加後は P2 の申込が C1 と C3 の2件になり、それぞれ別の講座という元の条件に合います。両方とも P2 だから重複、と判定するのは組全体を見ていない誤りです。

もし同じ参加者が同じ講座を別の日に複数回予約できる条件なら、この2列の組だけでは各予約を識別できません。その場合は予約ごとに異なる番号を設けるなど、記録したい単位に合わせてキーを選び直します。表の現状だけでなく、今後認める行の条件が判断を左右します。

誤答から原因を探す

次の会員表を見て、「氏名は人を表すので主キーにできる」と答えた場合を考えます。会員番号は重複せず、空欄もないという規則です。

図3のように、同じ氏名が M1 と M2 にあります。氏名を手がかりにすると2行が候補になり、どちらの人か決まりません。誤答の原因は、氏名が人を呼ぶための情報であることと、表の中で行を一意に選べることを混同した点です。

修正すると、主キーは会員番号です。M1 と M2 は氏名が同じでも異なる行として指せます。係も「記録」が2行にあるので、この表の主キーには使えません。図3の各番号を元の表に戻して照合すると、それぞれ1行に対応しています。

番号を使っていても、M1 が2回登録されていたら主キーの規則に反します。また、番号欄が空欄なら、その行を番号で識別できません。重複や空欄を発見したら、どちらかの行を根拠なく削除するのではなく、入力元を確認し、別人なのか同じ記録の二重登録なのかを判断して修正します。

別の表から会員番号を参照する場合も、番号の形式が似ているだけでは足りません。たとえば参照先に M4 がなければ、その番号が誰を指すかは決められません。空欄を許すかどうかは問題で与えられた条件に従い、必須であれば未入力も規則違反です。

自分で解く4問

答えは4問の後にまとめています。問題ごとに与えられた表と規則だけを使い、行を省略せずに確かめてください。

問1　識別できる列

植物表の3行は、鉢番号 T1・種類 すみれ・置き場 窓辺、T2・種類 たんぽぽ・置き場 廊下、T3・種類 すみれ・置き場 廊下です。鉢番号は必ず異なり空欄にしない規則です。鉢番号、種類、置き場の中から主キーに適する列を答え、他の2列が適さない理由をそれぞれ書いてください。

問2　記録と参照先

部屋表には R1・音楽室、R2・美術室、R3・理科室があります。使用表は U1・R3、U2・R1、U3・R3、U4・R2、U5・R3 の5行です。各行の前の値は使用番号、後の値は部屋番号です。使用番号は重複せず、部屋番号は必ず部屋表に存在します。使用表の主キーと参照列を答え、行順に使用番号と部屋名の組を書き、組の件数を整数で答えてください。

問3　組の判定

展示への参加表に、出展者番号と展示番号の組 F1・E2、F2・E2、F2・E4、F3・E4 が登録されています。同じ出展者は同じ展示に1回まで参加でき、番号は空欄にせず両方とも参照先に存在します。2列の組を主キーにできるか答えてください。また F1・E4 と F2・E2 を別々に追加しようとするとき、それぞれ追加できるか理由を書いてください。

問4　登録を止める条件

利用者表の主キーは利用者番号で、値は A1、A2、A3 です。予約表の主キーは予約番号で、既存の行は B1・A1 と B2・A3 です。各行の前の値は予約番号、後の値は利用者番号です。予約表では両列を必須とし、利用者番号は利用者表を参照します。次の候補をそれぞれ既存の表に1行だけ追加する場合に、受け付けられるか答え、規則に反する場合はその理由をすべて挙げてください。空欄は値が未入力であることを表します。

• 候補ア: B3・A2
• 候補イ: B2・A4
• 候補ウ: B4・空欄
• 候補エ: 空欄・A1

答えと行に戻す確認

問1の答え

主キーに適するのは鉢番号です。T1、T2、T3 は異なり、今後も重複と空欄を許さないと決まっています。種類は「すみれ」が T1 と T3 に、置き場は「廊下」が T2 と T3 にあるため、それぞれ1行を指せません。鉢番号を元の3行に戻すと、各番号に対応する鉢は1つずつです。

問2の答え

使用表の主キーは使用番号、参照列は部屋番号です。答えは U1・理科室、U2・音楽室、U3・理科室、U4・美術室、U5・理科室で、5件です。R3 は3回現れますが、3つの使用記録を指すので3行とも残します。

部屋名を部屋表の番号に戻せば、順に R3、R1、R3、R2、R3 となり、使用表と一致します。部屋表には3行しかなくても、対応する使用記録の件数は5件です。数える対象を「部屋」から「使用」に変えていないか確認します。

問3の答え

2列の組を主キーにできます。既存の4組は互いに異なり、同じ出展者と展示の組を重ねないという規則もあるからです。F1・E4 は既存の4組にないため追加できます。F2・E2 は1列ずつでなく組全体が2行目と一致するため追加できません。

F1・E4 を加えた後の F1 は E2 と E4 に参加しており、異なる展示なら複数参加できる条件に戻ります。参照先の有無も問題で保証されています。もし展示番号が参照先に存在しなければ、組が新しくても参照の規則を別に確認する必要があります。

問4の答え

候補アは受け付けられます。B3 は既存の B1、B2 と異なり、A2 も利用者表に存在するためです。元の表に加えると B3 の予約は A2 の利用者を指します。

候補イは受け付けられません。B2 が既存の予約番号と重複し、A4 も利用者表にありません。主キーと参照の2つの規則に反しています。片方を直すだけでは登録できないことに注意します。

候補ウは利用者番号が必須なのに空欄なので受け付けられません。候補エは主キーの予約番号が空欄なので受け付けられません。A1 はすでに B1 にも使われていますが、同じ利用者が複数の予約を持つことは禁止されていないため、参照列の重複はこの問題では理由になりません。

要点を手元に残す

主キーは各行を一意に指し、重複と空欄を許しません。
複数列のキーは組全体を比べます。
参照列は対応先のキーと照合し、必須かどうかも読みます。
表を分けるときは、何を1行とするかを先に決めます。

答えが止まる場所を見分ける

列の値を丸で囲んでも答えが出ないときは、同じ値がある行を線で結んでみます。1列に重複があっても組では異なる場合と、組まで同じ場合を区別できれば、複数列の判断に進めます。空欄や参照先のない値は、重複の有無とは別に検査します。

個別指導で確かめるなら、どの行を比べたかを説明し、答えを出した後に追加候補を1行戻してもらうと、規則を使えているか見えます。氏名が同じ2人を別々に指せる番号を選び、その番号が別表からどう参照されるかまで自分の言葉でたどれれば、表のつながりを根拠を持って説明できます。