関数従属性と第二・第三正規形
受講表の一行を学生と講座の組とし、氏名・学科・講座名・成績を保存します。キーの一部分に依存する値を分け、さらに学科名の依存を分けて、再結合で情報を復元できるか確認します。最後に代理IDを加えても業務上の一意制約が必要なことを試します。
説明
関数従属性X→Yとは、Xの値が決まればYの値が一つに決まるという関係です。表を分割するときは、現在の数行が偶然一致しているだけなのか、業務上いつも成立する規則なのかを確認します。
受講表の一行を学生と講座の組とし、氏名・学科・講座名・成績を保存します。キーの一部分に依存する値を分け、さらに学科名の依存を分けて、再結合で情報を復元できるか確認します。最後に代理IDを加えても業務上の一意制約が必要なことを試します。
1受講の組に各種の情報を保存する
学生は一つの学科に所属し、講座コードごとの現在名は一つと定めます。
CREATE TABLE enrollment(student TEXT,course TEXT,student_name TEXT,dept TEXT,dept_name TEXT,course_name TEXT,grade INTEGER,PRIMARY KEY(student,course)受講の組を識別する) STRICT;
INSERT INTO enrollment VALUES('S1','C1','あおい','D1','情報','SQL',80),('S1','C2','あおい','D1','情報','Web',90),('S2','C1','はる','D1','情報','SQL',70);
SELECT * FROM enrollment分割前の情報 ORDER BY student,course;この例の候補キーは(student,course)です。学生→氏名・学科、学科→学科名、講座→講座名、学生と講座の組→成績という規則があります。成績は両方が決まって初めて特定できます。
2学生ごとの名前の数を調べる
現在のデータが学生→氏名を満たすか確認します。
SELECT student,COUNT(DISTINCT student_name)学生ごとの氏名の種類 AS names,COUNT(DISTINCT dept)所属学科の種類 AS departments FROM enrollment GROUP BY student ORDER BY student;両学生とも名前と学科は各一種類です。この結果は今のデータに反例がないことを示すだけです。関数従属性が今後も成立するかは、学生IDで同一人物を管理するなどの業務規則から決めます。
3主キーだけでは氏名の矛盾を防げない
一つの受講行の氏名だけを変更します。
BEGIN試行の変更をまとめる;
UPDATE enrollment SET student_name='別名'一受講行だけ氏名を変更 WHERE student='S1' AND course='C2';
SELECT student,COUNT(DISTINCT student_name) AS names FROM enrollment GROUP BY student HAVING COUNT(DISTINCT student_name)>1依存の反例を取得;S1に二つの名前が出ますが、受講の組は重複していないので主キー違反は起きません。学生→氏名という依存が、表の構造と制約で表現されていない状態です。次にこの変更を取り消して分割します。
4キーの一部分に依存する列を分ける
学生・講座・受講へ分けます。
ROLLBACK;
CREATE TABLE students2学生だけで決まる情報(student TEXT PRIMARY KEY,name TEXT,dept TEXT,dept_name TEXT) STRICT;
INSERT INTO students2 SELECT DISTINCT student,student_name,dept,dept_name FROM enrollment;
CREATE TABLE courses講座だけで決まる情報(course TEXT PRIMARY KEY,name TEXT) STRICT;
INSERT INTO courses SELECT DISTINCT course,course_name FROM enrollment;
CREATE TABLE grades組全体で決まる成績(student TEXT REFERENCES students2(student),course TEXT REFERENCES courses(course),grade INTEGER,PRIMARY KEY(student,course)) STRICT;
INSERT INTO grades SELECT student,course,grade FROM enrollment;
SELECT * FROM grades ORDER BY student,course;受講表gradesには組全体で決まる成績だけが残ります。候補キーの一部分への依存を非キー属性から取り除くのが第二正規形の要点です。ここでは学生情報と講座名をそれぞれの表へ移しました。
5学生表に残った学科名の重複
第二段階の学生表を表示します。
SELECT * FROM students2学科名が繰り返された学生表 ORDER BY student;S1とS2に学科D1の名称「情報」が繰り返されています。学生→学科、学科→学科名なので、学生→学科名には学科を経由する依存があります。氏名と違い、学科名は学生単位で管理する情報ではありません。
6学科を独立させて第三正規形へ進む
学科コードをキーとする表へ名称を移します。
CREATE TABLE departments学科→学科名を一表にする(dept TEXT PRIMARY KEY,name TEXT) STRICT;
INSERT INTO departments SELECT DISTINCT dept,dept_name FROM students2;
CREATE TABLE students学生には所属学科のコードを残す(student TEXT PRIMARY KEY,name TEXT,dept TEXT REFERENCES departments(dept)) STRICT;
INSERT INTO students SELECT student,name,dept FROM students2;
CREATE TABLE grades3受講の参照先も新しい学生表へ(student TEXT REFERENCES students(student),course TEXT REFERENCES courses(course),grade INTEGER,PRIMARY KEY(student,course)) STRICT;
INSERT INTO grades3 SELECT * FROM grades;
SELECT * FROM departments ORDER BY dept;
SELECT * FROM students ORDER BY student;この例では非キー属性の学科名が別の非キー属性に依存する状態を解消しました。第三正規形は、非自明なX→AについてXがスーパーキー（行を一意に識別できる属性の組）か、Aがいずれかの候補キーに含まれる属性であることを要求します。ここでは名称はいずれもキー以外の属性なので、対応する対象の表へ分けています。 受講表もgrades3へ移し、学生の参照先を新しいstudentsに合わせます。
7分割後の表から元の情報を取得する
学生・学科・講座・成績をキーで結びます。
SELECT g.student,g.course,s.name AS student_name,s.dept,d.name AS dept_name,c.name AS course_name,g.grade FROM grades3 AS g JOIN students AS s ON s.student=g.student学生のキーで結合 JOIN departments AS d ON d.dept=s.dept学科のキーで結合 JOIN courses AS c ON c.course=g.course講座のキーで結合 ORDER BY g.student,g.course;元の三行を復元できます。学生コード・講座コード・学科コードをキーとする表へ分け、対応を残したため、名前や成績を失わず結び直せます。単に列を別表へ移すだけでなく、再結合で余分な組合せが生じないことも確認します。
8元の表との集合差を両方向で検査する
再結合の結果をビューにし、EXCEPTで差を数えます。
CREATE VIEW restored再結合のSELECTを名前で参照 AS SELECT g.student,g.course,s.name,s.dept,d.name AS dept_name,c.name AS course_name,g.grade FROM grades3 AS g JOIN students AS s ON s.student=g.student JOIN departments AS d ON d.dept=s.dept JOIN courses AS c ON c.course=g.course;
SELECT COUNT(*) AS missing FROM (SELECT * FROM enrollment EXCEPT SELECT * FROM restored元にあって復元結果にない行);
SELECT COUNT(*) AS extra FROM (SELECT * FROM restored EXCEPT SELECT * FROM enrollment復元結果だけにある行);missingもextraも0です。この固定データでは情報の欠落も余分な行もありません。一般に損失のない分解であることは、結合に使う共通属性が分けた側のキーになるなど、依存とキーの条件からも判断します。
9代理IDに加えて受講の一意性を保つ
採番用のIDを加え、学生と講座の組にもUNIQUEを付けます。
CREATE TABLE registrations(id INTEGER PRIMARY KEY内部参照用の代理キー,student TEXT NOT NULL REFERENCES students(student),course TEXT NOT NULL REFERENCES courses(course),grade INTEGER,UNIQUE(student,course)業務上の受講の一意性) STRICT;
INSERT INTO registrations(student,course,grade) SELECT student,course,grade FROM grades3 ORDER BY student,course;
SELECT * FROM registrations ORDER BY id;id 1～3の代理キーが付きます。学生・講座の組という業務上の候補キーも残しています。代理IDを付けただけでは、同じ受講を異なるIDで二重登録することを防げません。
10別の代理IDでも同じ受講は追加できない
新しいID 99でS1とC1を登録します。
INSERT INTO registrations VALUES(99,'S1','C1'IDは新しく受講の組は重複,100);
SELECT COUNT(*)拒否後の件数 AS registrations FROM registrations;studentとcourseのUNIQUE違反で拒否され、件数は3のままです。代理キーと自然な業務キーの役割を分け、それぞれ必要な一意性を制約にします。
まとめ
関数従属性は現在の一致だけで推測せず、対象の業務規則から定めます。
キーの一部への依存と、非キー属性を経由する依存を分け、再結合を検査します。
代理キーを加えても、業務上の一意性や正規化の検討は必要です。
操作履歴と解説を振り返れます。
SQLite 3.54.0
1
CREATE TABLE enrollment(student TEXT,course TEXT,student_name TEXT,dept TEXT,dept_name TEXT,course_name TEXT,grade INTEGER,PRIMARY KEY(student,course)) STRICT;
INSERT INTO enrollment VALUES('S1','C1','あおい','D1','情報','SQL',80),('S1','C2','あおい','D1','情報','Web',90),('S2','C1','はる','D1','情報','SQL',70);
SELECT * FROM enrollment ORDER BY student,course;╭─────────┬────────┬──────────────┬──────┬───────────┬─────────────┬───────╮ │ student │ course │ student_name │ dept │ dept_name │ course_name │ grade │ ╞═════════╪════════╪══════════════╪══════╪═══════════╪═════════════╪═══════╡ │ S1 │ C1 │ あおい │ D1 │ 情報 │ SQL │ 80 │ │ S1 │ C2 │ あおい │ D1 │ 情報 │ Web │ 90 │ │ S2 │ C1 │ はる │ D1 │ 情報 │ SQL │ 70 │ ╰─────────┴────────┴──────────────┴──────┴───────────┴─────────────┴───────╯
2
SELECT student,COUNT(DISTINCT student_name) AS names,COUNT(DISTINCT dept) AS departments FROM enrollment GROUP BY student ORDER BY student;╭─────────┬───────┬─────────────╮ │ student │ names │ departments │ ╞═════════╪═══════╪═════════════╡ │ S1 │ 1 │ 1 │ │ S2 │ 1 │ 1 │ ╰─────────┴───────┴─────────────╯
3
BEGIN;
UPDATE enrollment SET student_name='別名' WHERE student='S1' AND course='C2';
SELECT student,COUNT(DISTINCT student_name) AS names FROM enrollment GROUP BY student HAVING COUNT(DISTINCT student_name)>1;╭─────────┬───────╮ │ student │ names │ ╞═════════╪═══════╡ │ S1 │ 2 │ ╰─────────┴───────╯
4
ROLLBACK;
CREATE TABLE students2(student TEXT PRIMARY KEY,name TEXT,dept TEXT,dept_name TEXT) STRICT;
INSERT INTO students2 SELECT DISTINCT student,student_name,dept,dept_name FROM enrollment;
CREATE TABLE courses(course TEXT PRIMARY KEY,name TEXT) STRICT;
INSERT INTO courses SELECT DISTINCT course,course_name FROM enrollment;
CREATE TABLE grades(student TEXT REFERENCES students2(student),course TEXT REFERENCES courses(course),grade INTEGER,PRIMARY KEY(student,course)) STRICT;
INSERT INTO grades SELECT student,course,grade FROM enrollment;
SELECT * FROM grades ORDER BY student,course;╭─────────┬────────┬───────╮ │ student │ course │ grade │ ╞═════════╪════════╪═══════╡ │ S1 │ C1 │ 80 │ │ S1 │ C2 │ 90 │ │ S2 │ C1 │ 70 │ ╰─────────┴────────┴───────╯
5
SELECT * FROM students2 ORDER BY student;╭─────────┬────────┬──────┬───────────╮ │ student │ name │ dept │ dept_name │ ╞═════════╪════════╪══════╪═══════════╡ │ S1 │ あおい │ D1 │ 情報 │ │ S2 │ はる │ D1 │ 情報 │ ╰─────────┴────────┴──────┴───────────╯
6
CREATE TABLE departments(dept TEXT PRIMARY KEY,name TEXT) STRICT;
INSERT INTO departments SELECT DISTINCT dept,dept_name FROM students2;
CREATE TABLE students(student TEXT PRIMARY KEY,name TEXT,dept TEXT REFERENCES departments(dept)) STRICT;
INSERT INTO students SELECT student,name,dept FROM students2;
CREATE TABLE grades3(student TEXT REFERENCES students(student),course TEXT REFERENCES courses(course),grade INTEGER,PRIMARY KEY(student,course)) STRICT;
INSERT INTO grades3 SELECT * FROM grades;
SELECT * FROM departments ORDER BY dept;
SELECT * FROM students ORDER BY student;╭──────┬──────╮ │ dept │ name │ ╞══════╪══════╡ │ D1 │ 情報 │ ╰──────┴──────╯ ╭─────────┬────────┬──────╮ │ student │ name │ dept │ ╞═════════╪════════╪══════╡ │ S1 │ あおい │ D1 │ │ S2 │ はる │ D1 │ ╰─────────┴────────┴──────╯
7
SELECT g.student,g.course,s.name AS student_name,s.dept,d.name AS dept_name,c.name AS course_name,g.grade FROM grades3 AS g JOIN students AS s ON s.student=g.student JOIN departments AS d ON d.dept=s.dept JOIN courses AS c ON c.course=g.course ORDER BY g.student,g.course;╭─────────┬────────┬──────────────┬──────┬───────────┬─────────────┬───────╮ │ student │ course │ student_name │ dept │ dept_name │ course_name │ grade │ ╞═════════╪════════╪══════════════╪══════╪═══════════╪═════════════╪═══════╡ │ S1 │ C1 │ あおい │ D1 │ 情報 │ SQL │ 80 │ │ S1 │ C2 │ あおい │ D1 │ 情報 │ Web │ 90 │ │ S2 │ C1 │ はる │ D1 │ 情報 │ SQL │ 70 │ ╰─────────┴────────┴──────────────┴──────┴───────────┴─────────────┴───────╯
8
CREATE VIEW restored AS SELECT g.student,g.course,s.name,s.dept,d.name AS dept_name,c.name AS course_name,g.grade FROM grades3 AS g JOIN students AS s ON s.student=g.student JOIN departments AS d ON d.dept=s.dept JOIN courses AS c ON c.course=g.course;
SELECT COUNT(*) AS missing FROM (SELECT * FROM enrollment EXCEPT SELECT * FROM restored);
SELECT COUNT(*) AS extra FROM (SELECT * FROM restored EXCEPT SELECT * FROM enrollment);╭─────────╮ │ missing │ ╞═════════╡ │ 0 │ ╰─────────╯ ╭───────╮ │ extra │ ╞═══════╡ │ 0 │ ╰───────╯
9
CREATE TABLE registrations(id INTEGER PRIMARY KEY,student TEXT NOT NULL REFERENCES students(student),course TEXT NOT NULL REFERENCES courses(course),grade INTEGER,UNIQUE(student,course)) STRICT;
INSERT INTO registrations(student,course,grade) SELECT student,course,grade FROM grades3 ORDER BY student,course;
SELECT * FROM registrations ORDER BY id;╭────┬─────────┬────────┬───────╮ │ id │ student │ course │ grade │ ╞════╪═════════╪════════╪═══════╡ │ 1 │ S1 │ C1 │ 80 │ │ 2 │ S1 │ C2 │ 90 │ │ 3 │ S2 │ C1 │ 70 │ ╰────┴─────────┴────────┴───────╯
10
INSERT INTO registrations VALUES(99,'S1','C1',100);
SELECT COUNT(*) AS registrations FROM registrations;Error: UNIQUE constraint failed: registrations.student, registrations.course ╭───────────────╮ │ registrations │ ╞═══════════════╡ │ 3 │ ╰───────────────╯
演習完了