【情報I】データベースの基本を図解で総まとめ—表・主キー・関係データベース・検索と結合｜武蔵野個別指導塾

作品台帳の例で、主キーと外部キーの役割、条件による検索、並べ替え、表の結合を図とPythonで確かめます。同じ名前や繰り返すIDをどう扱うかまで、手順を追って学べます。

よくあるのは、美術室の床のタイルに沿って展示作品の置き場所を考えながら、作品一覧と作者一覧を見比べ、「同じ作者の名前が何度も出るのだから、全部を1枚の表にまとめればよいのでは」と手が止まる場面です。作品名を探すだけなら、それでも困らないかもしれません。しかし、作者の表示名を直したいとき、同じ人の名前をすべて直せるでしょうか。別の人が同じ名前だったとき、作品の持ち主を正しく区別できるでしょうか。

データベースは、情報を大量に保存するためだけのものではありません。何を1件として記録し、何を手がかりに区別し、必要な情報をどう取り出すかを決める仕組みです。表の見た目だけで覚えると、行を絞る操作と列を取り出す操作、表を結びつける操作が混ざります。作品台帳を実際にたどりながら、操作の前後で何が変わり、何が保たれるのかを確かめていきましょう。

この単元で身につけること

• データベースが必要な理由を説明できます。情報の重複や修正漏れを減らし、複数の利用者が同じ記録を適切に使うための仕組みとして捉えます。
• 表・行・列を区別できます。行は1件分の記録、列は作品名や作者IDなどの項目であり、まず「1行が何を表すか」を決めます。
• 主キーと外部キーの役割を説明できます。その表の行を一意に識別する手がかりと、別の表の記録を参照する手がかりを分けて考えます。
• 検索と並べ替えを使い分けられます。条件に合う行を取り出すことと、行の順序を変えることは異なる操作です。
• 共通する項目を使って表を結合できます。表が上下の同じ位置に並んでいるかではなく、対応させる値が一致するかを確かめます。

前の単元で学ぶネットワークはデータを届ける仕組みであり、データベースは届いたデータを整理して保存し、必要に応じて取り出す仕組みにつながります。プログラミングで扱う条件分岐やリストも、検索の手順を具体的に確かめる道具になります。

基本の考え方

データをためることと、管理すること

データベースとは、ある目的に沿って整理され、検索や更新などを行えるように蓄積されたデータの集まりです。作品台帳なら、作品の一覧を保存するだけでなく、「展示する作品だけ調べる」「作者を変更する」「作品の点数を数える」といった利用を想定します。写真のフォルダもデータの集まりですが、何をどの項目で記録し、どの条件で取り出せるかまで考えることが、この単元の出発点です。

データベースの検索・登録・更新・削除などを管理するソフトウェアを、データベース管理システム、略してDBMSと呼びます。データそのものと、それを管理するソフトウェアは区別しましょう。図書の記録がデータベースなら、貸出の登録や蔵書の検索を受け付ける仕組みの中心にDBMSがあります。利用者は画面のボタンを押していても、その裏側でデータの取り出しや更新が行われています。

表計算でも小さな台帳は作れます。ただし、人数が増え、同じ記録を複数の人が操作するようになると、保存のタイミングや入力の規則が重要になります。たとえば、同じ作品IDを別々の作品に割り当てたり、登録されていない作者IDを入力したりしないための制約が必要です。DBMSは、このような制約や利用権限、同時に行われる処理の調整などを扱います。データベースを導入すれば自動的に正しいデータになるのではなく、何を禁止し、どこまで入力を確認するかを設計します。

関係データベースの表・行・列

関係データベースは、データを複数の表として扱い、項目の値を使って関連づける方式です。「関係」は単に仲のよさを表す言葉ではなく、表の形でデータを表現する考え方に由来します。ここでは、各表が何を記録しているか、どの値で表どうしを対応させるかに注目すれば十分です。

次の作品表では、1行が1つの作品を表します。行はレコード、列はフィールドや属性とも呼ばれます。作品名の「空」と作者IDの「A」は同じ行にあるため、同じ作品の情報です。列には項目ごとの意味があり、価格の列には数値、作品名の列には文字列、といったデータの種類をそろえて扱います。

図1のように、横方向のまとまりは1件、縦方向のまとまりは1項目です。図では作品表の最初の3列・3件を抜き出し、作品IDの列を淡い赤、作品IDが102の行を淡い緑で示しています。交わるマスには「102」という1つの値が入り、行と列の両方の意味を持ちます。

表の先頭の見出しは、作品そのものの記録には数えません。この作品表は4件のレコードと4つの項目を持ちます。また、関係データベースの表には、取り出すときの順序が初めから保証されているとは考えません。「3行目だから海」と位置で覚えるのではなく、「作品IDが103だから海」と値で特定します。順序が必要なときは、並べ替えの条件を明示します。

1つのマスに複数の作者IDを区切り文字で詰め込むと、作者ごとの検索や結合が難しくなります。この例では、1作品に作者は1人という条件にしています。共同制作を扱うなら、作品と作者の組を1行ずつ記録する別の表を用意する方法が考えられます。現実の決まりが変わると、適した表の形も変わる点を覚えておきましょう。

主キーと外部キーでつながりを保つ

主キーは、表の各行を一意に識別するために選ぶ項目、または項目の組です。一意とは、値を指定すれば該当する行が1つに決まることです。主キーには重複した値やNULLを認めません。NULLは、値が不明であったり未登録であったりする状態を表す特別な扱いで、数値の0や文字の「なし」と同じではありません。

作品表の主キーは作品IDです。作品名は別の作者が同じ名前をつける可能性があるので、主キーにするには不安があります。いま見えている4件が重複していないだけでは、将来も重複しない根拠にはなりません。主キーを判断するときは、現在の値だけでなく、表を運用するときの規則も必要です。番号なら何でもよいわけではなく、その表の中で重複させないという約束が大切です。

一方、作者の情報は次の作者表にまとめます。作者表は1行が1人を表し、その主キーは作者IDです。作品表の作者IDは、この作者表の作者IDを参照する外部キーとして使います。外部キーは、自分の表の行を区別するためではなく、参照先の行との対応を保つための項目です。

作品表ではAが2回現れますが、これは誤りではありません。同じ作者が2作品を作ったことを表しており、外部キーの値は重複していてもかまいません。対して作者表のAが2行に増えると、参照先を1人に決められなくなります。外部キーの制約を設定すると、参照先にない値の登録などを防げます。この例では作者IDの空欄も認めないと決めますが、一般に外部キーを必須入力にするかどうかは別途定める条件です。

作者の表示名を作品表の全行に書く方式では、青木という名前を変更するとき、複数行の修正が必要になります。作者表にまとめれば、作者の表示名は対応する1行で管理でき、作品との対応はIDで保てます。同じ内容の不必要な重複を減らすように表を整理する考え方は、正規化につながります。ただし、表を細かく分けること自体が目的ではありません。何がどの記録に属する情報なのかを明確にして、修正時の食い違いを防ぐことが目的です。

例題で解き方を確かめる

例題1　主キーにできる項目を選ぶ

先ほどの作品表を使います。「作品IDは重複させず必ず入力する」「作品名は同じでもよい」「1人の作者が複数作品を登録できる」という規則のもとで、作品ID・作品名・作者IDのうち、単独で主キーにするのに適した項目を選びましょう。いまの表だけでなく、追加登録された後も行を区別できるかを考えます。

1. まず、1行は1作品であると確かめます。何を区別する番号なのかが決まらないと、主キーも決められないためです。
2. 作品名と作者IDを候補から外します。作品名は同名を認め、作者IDはAがすでに2行に現れているので、これらだけでは作品を1つに決められません。
3. 作品IDの規則を確認します。必須入力で重複を禁止しているため、どの作品の行かを一意に識別できます。

答え：単独で主キーにするのに適した項目は作品IDです。

主キーは1つの項目だけで作るとは限りません。たとえば展示への出品記録を「展示IDと作品IDの組は重複しない」という規則で管理するなら、この組を主キーにする設計も考えられます。個々の列に重複があっても、組として一意であることが判断の基準です。ただし、今回の作品表では作品IDだけで十分なので、余分な列を組み合わせる必要はありません。

例題2　条件に合う行を検索する

作品表から「作者IDがA、かつ価格が700円以上」の作品を取り出し、作品IDと作品名を答えましょう。「かつ」という言葉は、両方の条件が同時に成り立つことを表します。価格がちょうど700円なら条件を満たす点にも注意します。

1. 作者IDがAの行を確認します。作品IDが101と103の2行が残り、作者の条件で候補を限定できます。
2. その2行の価格を調べます。101は800円なので条件を満たしますが、103は600円なので除きます。
3. 残った行から作品IDと作品名を取り出します。最後に欲しい項目だけを見ることで、答える内容をはっきりさせます。

答え：作品IDは101、作品名は空です。

Pythonで同じ判断をすると、次のようになります。各辞書が1行、その辞書のキーが列の見出しに対応します。これは検索の考え方を確かめるためのリストであり、DBMSそのものを作っているわけではありません。

works = [
    {"id": 101, "name": "空", "author": "A", "price": 800},
    {"id": 102, "name": "森", "author": "B", "price": 1200},
    {"id": 103, "name": "海", "author": "A", "price": 600},
    {"id": 104, "name": "街", "author": "C", "price": 1200},
]
for w in works:
    if w["author"] == "A" and w["price"] >= 700:
        print(w["id"], w["name"])

出力は次のとおりです。

101 空

条件を「作者IDがA、または価格が700円以上」に変えると結果は4件になります。101と103は作者の条件を満たし、102と104は価格の条件を満たすからです。「または」は、片方だけでなく両方を満たす行も含みます。同じ行が両方の条件を満たしても、条件で取り出しただけで2行に増えるわけではありません。

例題3　同じ価格の作品を含めて並べ替える

作品表を価格の降順、同じ価格なら作品IDの昇順に並べ替えたとき、作品IDの順を答えましょう。昇順は小さい値から大きい値へ、降順は大きい値から小さい値への順です。今回の主な基準は価格であり、作品IDは同じ価格のときだけ使います。

1. 価格を大きい順にまとめます。1200円の2作品、800円の作品、600円の作品というまとまりができ、価格の基準が優先されます。
2. 1200円のまとまりの中で作品IDを比べます。102を104より先に置くことで、同じ価格の行の順序も決められます。
3. すべてのまとまりを順につなぎます。並べ替えは行の位置を変える操作なので、作品数は4件のままです。

答え：作品IDの順は102、104、101、103です。

次のコードは、例題2で定義したworksを続けて使います。比較用の組の先頭を価格の負の値にすると、通常の昇順の比較で高い価格が先に来ます。2番目の作品IDは正のままなので、価格が同じときは小さいIDが先になります。

ordered = sorted(works, key=lambda w: (-w["price"], w["id"]))
print([w["id"] for w in ordered])

出力は次のとおりです。

[102, 104, 101, 103]

価格の列だけを動かし、作品名の列を元の位置に残す操作は、行の並べ替えではありません。作品と価格の対応が壊れてしまいます。並べ替えでは、1行の項目をひとまとまりとして移動させることが必要です。また、価格だけを指定した場合、同価格の順序まで意図どおりになるとは限らないため、必要なら次の基準を明示します。

例題4　2つの表を結合する

作品表に作者の表示名を加え、作品IDの昇順で「作品名と表示名」を答えましょう。結合とは、指定した条件に従って表どうしの行を組み合わせる操作です。今回は作品表の作者IDと作者表の作者IDが等しい行を組み合わせます。

1. 作品表の101の行を選び、作者IDのAを読みます。作品IDの101を作者表で探すのではなく、対応を表す列を使う必要があります。
2. 作者表で作者IDがAの行を探し、表示名の青木を取り出します。主キーで参照先が1行に決まるため、対応する表示名も決まります。
3. 同じ手順を残りの作品にも適用します。作品表のAは2回現れるので、青木は空と海の両方に対応します。
4. 指定された作品ID順で作品名と表示名を並べます。結合条件と表示順の条件は役割が違うので、両方を確かめます。

答え：空―青木、森―上田、海―青木、街―加藤です。

図2のように、2つの表は作者IDの値で結びます。図では対応を見やすくするため、作者Aに関係する作品101と103だけを示しています。作者表の1行を2つの作品が参照してもよく、これを1対多の関係として捉えられます。

次のコードもworksを続けて使います。作者IDを辞書で調べる形にして、各作品に対応する表示名を出力します。この短い例では、作品表の作者IDがすべて作者表に存在することを前提にしています。

authors = {"A": "青木", "B": "上田", "C": "加藤"}
for w in works:
    print(w["name"], authors[w["author"]])

出力は次のとおりです。

空 青木
森 上田
海 青木
街 加藤

一般の結合では、条件に一致する相手の行が複数あれば、その組み合わせの数だけ結果の行ができます。反対に、一致する相手がない行を結果に含めない結合もあります。今回の4件が4件のままなのは、必須の外部キーが作者表の主キーを参照し、それぞれの作品に対して作者がちょうど1人決まるからです。「結合すれば必ず行数が変わらない」とは覚えないようにしましょう。

つまずきやすいところと直し方

誤り1　主キーと外部キーの重複を同じに扱う

誤った書き方は「作品表の作者IDはAが重複しているので、表として不正」です。どこが違うかというと、作者IDは作品表の主キーではなく、作者表を参照する外部キーである点です。同じ作者が複数の作品を持つという意味なので、この重複には理由があります。

正しい書き方は「作品表では作品IDを重複させず、作者IDの重複は認める」です。列名だけに赤線を引いて覚えるより、作品表と作者表それぞれに「1行は何か」を書き添えましょう。作者IDは作者表では主キー、作品表では外部キーというように、どの表にあるかによって役割が変わります。

誤り2　行を選ぶことと列を選ぶことを混同する

誤った書き方は「価格が700円以上の作品を選ぶには、価格の列だけを残す」です。どこが違うかというと、条件に合う作品を選ぶことと、見せる項目を選ぶことが混ざっています。価格の列だけを残しても、600円の作品の記録はそのまま残ります。

正しい書き方は「価格が700円以上の行を選び、必要なら作品名などの列を取り出す」です。条件に合う行を取り出す操作を選択、必要な列を取り出す操作を射影と呼びます。図3のように、選択は横のまとまり、射影は縦のまとまりに注目すると区別できます。図の橙は取り出す対象を示しており、色の付いていない部分は結果に含めません。

作品表から作者IDの列だけを取り出したとき、Aが2回現れることにも注意が必要です。数学的な関係の演算としての射影では、同じ結果の行は重複を除いて扱います。一方、実際のデータベースで単に列を指定して検索した結果は、重複が残る場合があります。「異なる作者を何人か数える」という問題なら、作者IDを取り出すだけでなく、重複を除いてA・B・Cの3人と数える必要があります。作品の件数4件と作者の人数3人を取り違えないようにしましょう。

誤り3　表の上から同じ位置の行を結ぶ

誤った書き方は「作品表の1行目に作者表の1行目、2行目に2行目をつなぐ」です。どこが違うかというと、行の位置には作者を示す意味がない点です。この方法では3作品目の海を加藤に結びつけてしまい、作者表を並べ替えるだけでも対応が変わってしまいます。

正しい書き方は「作品表の作者IDと作者表の作者IDが一致する行をつなぐ」です。作者表の順をC・A・Bに変えても、Aをたどれば青木に到着します。結合を点検するときは、2つの列を指でたどり、比較している値を声に出すと、作品IDと作者IDを取り違える誤りも見つけやすくなります。

練習問題

ここまでと同じ作品表・作者表を使います。答えだけを先に探さず、残す行の作品IDを紙に書いてから、必要な列や順序を考えてみましょう。練習4では、作者IDの参照先と必須入力の規則を守るものとします。

練習1

作品表に作品IDが105、作品名が空、作者IDがB、価格が900円の作品を追加したいとします。作品IDを主キーとする規則に違反しますか。また、作品IDが101、作品名が山、作者IDがC、価格が900円の作品を追加する場合はどうですか。この2つは別々に、元の4件の表への追加として判断してください。

練習2

作品表から、価格が800円以上、かつ作者IDがAではない行を選びます。作品IDを昇順に答えてください。また、求める行を選ぶ前に作者IDの列を捨ててしまうと、何が困るでしょうか。

練習3

作品表と作者表を作者IDの一致で結合し、作者の学年が1の作品だけを残します。価格の昇順、同じ価格なら作品IDの昇順に並べたとき、作品名の順と作品数を答えてください。作者の人数を尋ねているのではないことに注意しましょう。

練習4

作者表から作者IDがAの行だけを削除し、作品表はそのまま残そうとしています。どの作品の参照先がなくなりますか。参照関係を壊す削除を拒否する設定なら、この操作は受け付けられるでしょうか。

答えと解説

練習1の答えは、105の追加は違反せず、101の追加は違反する、です。作品名の重複は認めているので空という名前は追加できますが、作品IDの101はすでに存在します。主キーの条件は、名前が同じかどうかとは別に判断します。

練習2の答えは、102、104です。800円以上なのは101・102・104であり、その中から作者IDがAの101を除きます。先に作者IDの列を捨てると、残った情報だけでは作者についての条件を判定できません。操作の順序を考えるときは、後の操作に必要な列を残しておきます。

練習3の答えは、海、空、街の順で3作品です。学年が1の作者はAとCなので、対象の作品IDは101・103・104です。価格はそれぞれ800円・600円・1200円ですから、この順序になります。作者は2人でも、その作者が作った作品は3件あります。

練習4の答えは、作品IDが101と103の参照先がなくなり、指定の設定では削除は拒否される、です。外部キーの参照関係を保つには、参照されている側を消すときの扱いも必要になります。関連する記録も削除する設定などもありますが、どの処理を採るかは目的に応じて決めるものであり、勝手に作品まで消えると考えてはいけません。

次の単元へのつながりと家庭での確かめ方

データ分析では、平均やグラフを作る前に、何を1件として集計するかをそろえます。作品を数えたいのか作者を数えたいのかが曖昧なら、同じ表から計算しても違う答えになります。結合の条件が不適切で行が増えれば、合計にも影響します。データベースの学習は、計算の前提となるデータの形を点検する練習でもあります。

家庭で5分あれば、次の2つを確かめられます。1つ目は、紙に作品3件と作者2人の小さな表を別々に書き、同じ作者の作品を2件作ることです。主キーには重複がなく、外部キーには重複がある状態を自分で作り、役割を説明します。2つ目は、作者表の行の順を入れ替え、それでも同じ作品に同じ作者が結びつくかを確かめることです。位置ではなく値で結んでいれば対応は変わりません。

個別指導では、結合で止まったとき、まず「この1行は作品か作者か」という読み取りまで戻って確かめます。お子さまが用語を言えていても、具体的な行を指して説明できるかを見ると、理解の確かめどころが絞れます。

まとめ

作品台帳を正しく扱えるかは、表が整って見えるかだけでは決まりません。同名の作品が増えても区別できるか、作者名が変わっても修正漏れを起こさないか、並べ替えても作品と作者の対応が崩れないかで確かめられます。検索の答えを説明するときにも、残した行、使った列、結びつけた値を示せば、別の人が手順をたどれます。

表の行を入れ替えても失われないIDによる対応こそ、作品と作者を正しく結ぶ根拠です。