トップページ -> 研究道具箱と入門演習 -> データベース特論・支援 Web ページ -> テーブルの分解とデータベース設計
[サイトマップへ]   [全文検索へ]   [統計情報へ]   [掲示板へ]  

テーブルの分解とデータベース設計

サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

URL: http://www.db.is.kyushu-u.ac.jp/rinkou/addb/norm.html











  • GROUP BY 句

  • 属性間の従属性

    テーブル R の属性 a1, a2, ..., am, b1, b2, ..., bn について,

    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 演習に関連する部分

    SQLite の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.


    SQLite Database Browser の起動と終了 (Start and end SQLite Database Browser)

    ※ SQLite Database Browser は Windows でのみ動く.

    (Please note that the SQLite command line tool works in many OSs. The 'SQLite Database Browser' works only on the Windows OSs).

    1. SQLite Database Browser の起動 (Start SQLite Database Browser)

      「SQLite Database Browser.exe」 をダブルクリック (double click "SQLite Database Browser.exe")

      新しいウインドウが開く.(A new window appears)


    SQLite Database Browser で既存のデータベースを開く (Open an existing database using SQLite Database Browser)

    以下の手順で,既存のデータベース「C:\SQLite\mydb」を開く. (Open an existing database 'C:\SQlite\mydb')

    File」→ 「Open Database

    データベースファイル C:\SQLite\mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

    テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears)


    事前準備

    「テーブルの結合と分解」の回の Web ページの記述に従って, 次の操作を終えていること
    CREATE TABLE results (
        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 results VALUES( 'Database', 'K', 'KK', 85 );
    INSERT INTO results VALUES( 'Database', 'K', 'AA', 75 );
    INSERT INTO results VALUES( 'Database', 'K', 'LL', 90 );
    INSERT INTO results VALUES( 'Programming', 'A', 'KK', 85 );
    INSERT INTO results VALUES( 'Programming', 'A', 'LL', 75 );
    

    CREATE TABLE S AS
    SELECT DISTINCT name, teacher_name
    FROM results;
    

    SELECT COUNT(*), name
    FROM S
    GROUP BY name; 
    


    SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)

    SQL を用いて,results テーブルを定義し,一貫性制約を記述する. (Define 'products' table and specify integrity constrants of the table using SQL)

    リレーショナル・スキーマ (relational schema): results( name, teacher_name, student_name, score )
    
    1. Execute SQL」をクリック (Click "Execute SQL")

      ※ 「SQL string」のウインドウには,SQL プログラムを1つ書くことができる(2つ以上書いた場合には,最初の SQL プログラムだけが評価される). In the 'SQL string' window, you can write down one SQL program. If you write more-than-one SQL programs, only the first one will be evaluated.

    2. results テーブルの定義

      次の SQL を入力し,「Execute Query」をクリック (Write the following SQL, and click 'Execute Query').

      CREATE TABLE results (
          name    TEXT  NOT NULL, 
          teacher_name TEXT  NOT NULL,
          student_name TEXT  NOT NULL,
          score   INTEGER NOT NULL CHECK ( score >= 0 AND score <= 100 ) ); 
      


    SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

    次のような records テーブルを作る. (Construct table 'records')

    name        | teacher_name |  student_name | score
    ---------------------------------------------------
    Database    |   K          |  KK           | 85
    Database    |   K          |  AA           | 75
    Database    |   K          |  LL           | 90
    Programming |   A          |  KK           | 85
    Programming |   A          |  LL           | 75
    

    以下の手順で,SQL を用いて records テーブルへの行の挿入を行う (Insert rows into table 'records' using SQL)

    1. INSERT INTO results VALUES( 'Database', 'K', 'KK', 85 );

    2. INSERT INTO results VALUES( 'Database', 'K', 'AA', 75 );

    3. INSERT INTO results VALUES( 'Database', 'K', 'LL', 90 );

    4. INSERT INTO results VALUES( 'Programming', 'A', 'KK', 85 );

    5. INSERT INTO results VALUES( 'Programming', 'A', 'LL', 75 );


    SQLite Database Browser を用いたデータのブラウズ (Browse Data using SQLite Database Browser)

    まず,「Browse Data」をクリック (Click 'Browse Data')

    次に,テーブル 「results」を選ぶ (Select table 'results)

    ※ もし,データに間違いがあれば,このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).


    DISTINCT を含む SQL の例 (SQL with DISTINCT)

    DISTINCT を付ける (with DISTINCT)

    SELECT DISTINCT name, teacher_name
    FROM results;
    

    DISTINCT を付けない (without DISTINCT)

    SELECT name, teacher_name  
    FROM results;
    

    DISTINCT を付ける (with DISTINCT)

    SELECT DISTINCT score
    FROM results;
    

    DISTINCT を付けない (without DISTINCT)

    SELECT score
    FROM results;
    


    SQL を用いたテーブルの分解 (table decomposition using SQL)

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

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

    'results' table is decomposed into two tables A and B. Duplicate records in A and B are eliminated when decomposition (because of the definition of "table decomposition"). Use 'DISTINCT'.

    SQL の実行 (Execute SQL)

    CREATE TABLE A AS
    SELECT DISTINCT name, teacher_name  
    FROM results;
    

    データのブラウズ (Browse Data)

    SQL の実行 (Execute SQL)

    CREATE TABLE B AS
    SELECT DISTINCT name, student_name, score
    FROM results;
    

    データのブラウズ (Browse Data)


    結合問い合わせ (join query)

    元のテーブル results が,分解後の2つのテーブル A, B から復元できることを確認しておこう. テーブル results は,テーブル A と B から復元できるから,データベース設計として.

    という 2つの案がありえることになる.(では,どちらが良いのか? 今度の授業で明らかにしていく)

    Examine that the original table 'results' can be constructed from the two tables A and B. It means that there are two alternatives in database design.

    1. Store 'results' into database, or
    2. Store 'A' and 'B' instead of 'results' in database.
    SELECT A.name, A.teacher_name, B.student_name, B.score
    FROM A, B
    WHERE A.name = B.name
    

    情報無損失分解

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

    Table decompositions are not always LOSSLESS.

    SQL の実行 (Execute SQL)

    CREATE TABLE C AS 
    SELECT DISTINCT name, student_name
    FROM results;
    

    データのブラウズ (Browse Data)

    SQL の実行 (Execute SQL)

    CREATE TABLE D AS 
    SELECT DISTINCT teacher_name, student_name, score
    FROM results;
    

    データのブラウズ (Browse Data)

    テーブル C, D からは,テーブル results を構築できない ('results' table can not be constructed from C and D).

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

    演習問題と解答例

    サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

    次の問いに答えよ.その後,下記の解答例を確認せよ. Answer the following questions. Then, inspect answers described below.

    問い (Questions)

    次の PTABLE テーブルに関する問題 (About the following 'PTABLE' table)

    name    |  type   |  color
    ------------------------------
    apple   |  fruit  |  red
    apple   |  fruit  |  blue
    rose    |  flower |  white
    rose    |  flower |  red
    rose    |  flower |  yellow
    
    1. 次の SQL の評価結果は何か? What is the result of the following SQL ?
      SELECT DISTINCT name, type FROM PTABLE;
      

    2. 次の SQL の評価結果は何か? What is the result of the following SQL ?
      SELECT DISTINCT name FROM PTABLE;
      

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

      The PTABLE table can be decomposed into the two tables E(name, type) and F(name, color). Write the decomposition result.

    4. テーブル E, F に対する,次の SQL の評価結果は何か ?
      SELECT E.name, E.type, F.color
      FROM E, F
      where E.name = F.name;
      

    解答例 (Answers)

    ※ 問い合わせ結果は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