トップページ -> インストール,設定,利用 -> SQLite 3 とSQL 演習 -> SQL によるテーブルの関数従属性の確認,分解と結合(SQLite 3 を使用)
[サイトマップへ], [サイト内検索へ]

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 の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.


前準備

SQL 演習のために,リレーショナルデータベース管理システムのインストールを行っておくこと.

ここでは,SQLite 3 で説明する.

Windows での SQLite 3 のインストールは, 別ページで説明している.


関数従属性の確認

  1. SQLite の起動

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

    sqlite3
    

    [image]
  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 );
    

    [image]
  3. 確認表示
    select * from SCORE; 
    

    [image]
  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;
    

    [image]
  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;
    

    [image]

    SQLite 3 の終了

    .exit
    

    [image]

テーブルの分解と結合

  1. SQLite の起動

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

    sqlite3
    

    [image]
  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 );
    

    [image]
  3. 確認表示
    select * from SCORE; 
    

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

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

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

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

    [image]
  5. 確認表示
    select * from A;
    

    [image]
    select * from B;
    

    [image]
  6. 元のテーブル SCORE が,分解後の2つのテーブル A, B から復元できることを確認
    select A.name, A.teacher_name, B.student_name, B.score
    from A, B
    where A.name = B.name;
    

    [image]

    テーブル 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;
    

    [image]
    select * from C;
    select * from D;
    

    [image]

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

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

    [image]

演習問題と解答例

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

問い

次の 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つのテーブルになる.その属性名には,元のテーブル名と属性名をドットでつなげたドット記法を用いている.

  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
    

本サイトは金子邦彦研究室のWebページです.サイトマップは,サイトマップのページをご覧下さい. 本サイト内の検索は,サイト内検索のページをご利用下さい.

問い合わせ先: 金子邦彦(かねこ くにひこ) [image]