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のSQL文法の詳細については,http://www.hwaci.com/sw/sqlite/lang.html(英語Webページ)を参照してください.

前準備

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)分解した場合の結果を示せ.
  4. テーブルE,Fに対する,次のSQLの実行結果を考えよ.
    SELECT E.name, E.type, F.color
    FROM E, F
    where E.name = F.name;
    

解答例

* 問合せ結果は1つのテーブルとなり,属性名には元のテーブル名と属性名をドットで連結したドット記法を使用する.

  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