SQL によるテーブルの関数従属性の確認，分解と結合（SQLite 3 を使用）

はじめに

属性間の従属性を SQL で確認する

テーブル R が属性 a1, a2, ..., am, b1, b2, ..., bn およびその他の属性を持ち， 関数従属性 a1, a2, ..., am -> b1, b2, ..., bn があるとき， 次の SQL の結果では，1番目の属性値が「1」になる．

create table S AS
select distinct a1, a2, ..., am, b1, b2, ..., bn
from R;

select count(*), a1, a2, ..., am
from S
group by a1, a2, ..., am;

前準備

SQLite 3 について: 別ページ »にまとめ

関数従属性の確認

1. SQLite 3の起動

   ここでは， データベース名を指定せず，インメモリ・データベースを用いている．

   sqlite3
   

2. テーブルの作成

   create table SCORE (
       name         text  not null,
       teacher_name text  not null,
       student_name text  not null,
       score        INTEGER not null CHECK ( score >= 0 AND score <= 100 ) );
   
   insert into SCORE values( 'Database', 'K', 'KK', 85 );
   insert into SCORE values( 'Database', 'K', 'AA', 75 );
   insert into SCORE values( 'Database', 'K', 'LL', 90 );
   insert into SCORE values( 'Programming', 'A', 'KK', 85 );
   insert into SCORE values( 'Programming', 'A', 'LL', 75 );
   

   
3. 確認表示

   select * from SCORE; 
   

   
4. 関数従属性 name -> teacher_name の確認

   結果では，1番目の属性値が「1」になる．

   create table S AS
   select distinct name, teacher_name
   from SCORE;
   
   select count(*), name
   from S
   group by name;
   

   
5. 関数従属性 name, student_name -> score の確認

   結果では，1番目の属性値が「1」になる．

   drop table S;
   
   create table S AS
   select distinct name, student_name, score
   from SCORE;
   
   select count(*), name, student_name
   from S
   group by name, student_name;
   

   

   SQLite 3の終了

   .exit
   

   

テーブルの分解と結合

1. SQLite 3の起動

   ここでは， データベース名を指定せず，インメモリ・データベースを用いている．

   sqlite3
   

2. テーブルの作成

   create table SCORE (
       name         text  not null,
       teacher_name text  not null,
       student_name text  not null,
       score        INTEGER not null CHECK ( score >= 0 AND score <= 100 ) );
   
   insert into SCORE values( 'Database', 'K', 'KK', 85 );
   insert into SCORE values( 'Database', 'K', 'AA', 75 );
   insert into SCORE values( 'Database', 'K', 'LL', 90 );
   insert into SCORE values( 'Programming', 'A', 'KK', 85 );
   insert into SCORE values( 'Programming', 'A', 'LL', 75 );
   

   
3. 確認表示

   select * from SCORE; 
   

   
4. 2つのテーブルに分解

   SCORE( name, teacher_name, student_name, score )を 2つのテーブル

   • A(name, teacher_name)
   • B(name, student_score, score)

   に分解．テーブルを分解するときは重複行を除去するという規則があることに注意．DISTINCTを使う．

   create table A as 
   select distinct name, teacher_name
   from SCORE;
   create table B AS
   select distinct name, student_name, score
   from SCORE;
   

   
5. 確認表示

   select * from A;
   

   

   select * from B;
   

   
6. 元のテーブル SCORE が，分解後の2つのテーブル A, B から復元できることを確認．

   select A.name, A.teacher_name, B.student_name, B.score
   from A, B
   where A.name = B.name;
   

   

   テーブル SCORE は，テーブル A と B から復元できるから，データベース設計として．

   • テーブル SCORE をデータベースに格納するか，あるいは，
   • テーブル SCORE でなく，テーブル A と B をデータベースに格納する

   という 2つの案がありえることになる．

7. 情報無損失分解

   今度は，テーブルの分解の仕方を変える． 分解後のテーブルから，もとのテーブルに戻らない場合がある． このことを確認しておく．

   create table C AS
   select distinct name, student_name
   from SCORE;
   create table D AS
   select distinct teacher_name, student_name, score
   from SCORE;
   

   

   select * from C;
   select * from D;
   

   

   テーブル C, D からは，テーブル SCORE を構築できない

   SELECT C.name, D.teacher_name, C.student_name, D.score
   FROM C, D
   WHERE C.student_name = D.student_name;
   

   

演習問題と解答例

次の問いに答えよ．その後，下記の解答例を確認せよ．

問い

次の PTABLE テーブルに関する問題

name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  yellow

1. 次の SQL の評価結果は何か?

   SELECT DISTINCT name, type FROM PTABLE;
   

2. 次の SQL の評価結果は何か?

   SELECT DISTINCT name FROM PTABLE;
   

3. PTABLE テーブルは，2つのテーブル E(name, type) と F(name, color) に分解することができる． 分解結果 E(name, type) と F(name, color) は何になるか?
4. テーブル E, F に対する，次の SQL の評価結果は何か ?

   SELECT E.name, E.type, F.color
   FROM E, F
   where E.name = F.name;
   

解答例

※ 問い合わせ結果は１つのテーブルになる．その属性名には，元のテーブル名と属性名をドットでつなげたドット記法を用いている．

1. select distinct name, type FROM PTABLE;
   
   name    |  type  
   ------------------
   apple   |  fruit 
   rose    |  flower
   

2. select distinct name FROM PTABLE;
   
   name   
   --------
   apple  
   rose   
   

3. E
   name    |  type  
   ------------------
   apple   |  fruit 
   rose    |  flower
   
   F
   name    |  color
   ------------------
   apple   |  red
   apple   |  blue
   rose    |  white
   rose    |  red
   rose    |  yellow
   

4. name    |  type   |  color
   ------------------------------
   apple   |  fruit  |  red
   apple   |  fruit  |  blue
   rose    |  flower |  white
   rose    |  flower |  red
   rose    |  flower |  yellow