データベース設計

1つの改名されたコースに2つのタイトルがある

• 学校はコースタイトルを登録行のすべてにコピーします。一部のコピーを改名すると、同じコースに対して2つの矛盾するタイトルが存在することになります。
• リレーショナルデータベースは、テーブルにエンティティ（学生やコースなど）およびそれらの間の関係性を特定してから、行を格納します。

識別子を可変的なラベルから分離する

• プライマリキーは行を一意に識別し、nullにすることはできません。安定した学生IDを使用することで、名前が変わっても学生の識別自体は変わりません。
• 一意性と安定性が保証されている場合、ネイチャーキーも機能します。ただし、名前は異なる学生が共有できるため、ここでは適切なキーではありません。また、メールアドレスも変更される可能性があります。

マニ・トゥ・マニの関係を表す

• 一人の学生が複数の科目を受講できますし、一つの科目には多くの学生がいることがあります。各学生-科目ペアに対して1行ずつ格納する enrolments テーブルを使用します。
• 外部キーは、各登録を有効な学生または科目のキーに結びつけます。複合主キー (student_id, course_id) は、同じペアが重複して表示されるのを防ぎます。

科目に関する事実を科目と共に格納する

• 正規化は、依存関係を用いてテーブルを整理し、不整合な反復を減らすものです。この設計では、科目IDが科目名を決定するため、科目名は courses に格納します。
• 外部キー値の反復は予想されます：多くの登録が一つの科目を参照する可能性があります。「いかなる値も絶対に反復させない」というのは、有益なデータベース設計のルールではありません。

制約を有効にしてテストする

• SQLite では、作業を開始する前に各接続に対して PRAGMA foreign_keys = ON を用いて外部キーの強制を有効にします。関係性を宣言するだけでは不十分です。
• 有効な登録、重複したペア、存在しない科目を参照する登録をテストします。制約は後二者を拒否すべきですが、どの呼び出し元が権限を持っているかを判定するものではありません。

PRAGMA foreign_keys = ON;
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id),
  CHECK (student_id IS NOT NULL AND course_id IS NOT NULL)
);

新しいケースに対する設計を確認する

• 図書館の貸出において、書籍タイトルは必ずしも唯一の物理的なコピーを表すものではありません。キーを選択する前に、借り手、コピー、および貸出イベントを特定してください。
• 反復イベントが許可されるかどうかを判断します。ここでは学生-科目ペアで一つの登録を識別できますが、借り手-コピーペアでは異なる日付での反復する貸出を区別できません。