ボイス・コッド正規形への分解は、結局どうすればいいのか(2)

T字形 モデリング

2ヶ月前に書いた1回目の続き。
http://d.hatena.ne.jp/tgk/20060507
「第3正規形だが非ボイスコッド正規形」の表の正規化のしかた。


前回書いたことは

  • 「第3正規形だが非ボイスコッド正規形」の表を教科書的に正規化すると、現実世界に存在する制約をデータベースに反映できなくなる。
  • そのような表は、分解して正規化すると制約が消滅してしまうから、構造を追加して正規化すればいいのではないか。

ということ。
「正規化とはデータの重複を排除することだ」という立場の人には、そんなの正規化じゃない、と言われるかもしれない。
まあいいじゃないですかということにして、２つのやり方をメモしておく。

渡辺式

渡辺幸三 生産管理・原価管理システムのためのデータモデリング P-46に、非ボイスコッド正規形の表の正規化方法が載っている。
前回の例に当てはめると、こういう形になる。

科目 = { (科目コード), 科目名 }
教官 = { (教官コード), 教官名, 科目コード }
学生 = { (学生コード), 学生名 }
受講 = { (学生コード, 教官コード), (学生コード, 科目コード) }

カッコで括ってあるのはユニークキー。
受講テーブルの二次識別子 (学生コード, 科目コード) がポイント。
これにより { 学生, 科目 } -> { 教官 } という関数従属性を保持している。
確かに問題が解決されているが、無理やり「受講」と命名した最後のテーブルが何を表しているのか、ぱっと見分からない感じがする。
慣れの問題だと思いますが。

T字形ER手法

T字形ER手法の場合は、科目・教官・学生をresourceと見るので、渡辺式のように教官が科目コードを外部キー参照することはない。
代わりに、教官・科目の対照表（＝「講座」）を追加する。これはvalidation-ruleの対照表。「受講」に挿入するデータの検証に使える。

科目(R) = { 科目コード, 科目名 }
教官(R) = { 教官コード, 教官名 }
学生(R) = { 学生コード, 学生名 }
講座 = { 教官コード, 科目コード }
受講(E) = { 教官コード, 科目コード, 学生コード }

作図しないと { 教官 } -> { 科目 } の関数従属性がどこで担保されているのか分からないですね。
いいツールが手元にないので作図は省略して、これをPostgreSQLの実装に落とすとこうなる*1。

-- 教官(Resource)
create table teacher (
    teacher_id integer default nextval('seq_teacher_id') not null primary key,
    teacher_name text not null
);

-- 科目(Resource)
create table course (
    course_id integer default nextval('seq_course_id') not null primary key,
    course_name text not null
);

-- 学生(Resource)
create table student (
    student_id integer default nextval('seq_student_id') not null primary key,
    student_name text not null
);

-- 講座(validation-rule)
create table class (
    -- 教官にはUNIQUEを付ける
    teacher_id integer not null unique references teacher(teacher_id),
    course_id integer not null references course(course_id)
);

-- 受講(Event)
create table class_attending (
    teacher_id integer not null references teacher(teacher_id),
    course_id integer not null references course(course_id),
    student_id integer not null references student(student_id),
    -- "{ 教官 } -> { 科目 }" の実装
    constraint class_key foreign key (teacher_id, course_id) references class(teacher_id, course_id), 
    -- "{ 学生, 科目 } -> { 教官 }" の実装
    constraint class_attending_unique_key unique (course_id, student_id)
);

{ 学生, 科目 } -> { 教官 } に違反するデータを挿入すると、こんなエラーになる：

ERROR: Cannot insert a duplicate key into unique index lecture_attending_unique_key

{ 教官 } -> { 科目 } に違反するに違反するデータを挿入すると、こんなエラーになる：

ERROR: lecture_key referential integrity violation - key referenced from lecture_attending not found in lecture

ということで、関数従属性は維持される。

*1:ほんものののT字形ER手法は外部キー制約を使わないらしいので、これはオレ流T字形ER手法ということになります