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

テーブルの分解 (table decomposition)

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

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













演習

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

演習で行うこと


SQLite の SQL 演習に関連する部分

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


Sqliteman の起動と終了 (Start and end Sqliteman)

  1. Sqliteman の起動 (Start Sqliteman)

    「Sqliteman」 のアイコンをダブルクリック (double click "Sqliteman.exe")

    Sqliteman のウインドウが開く.(A New window appears)

  2. 終了 (End Sqliteman)

    File」→ 「Exit」で終了.


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

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

  1. File」→ 「Open

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


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

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

リレーショナル・スキーマ (relational schema): results( name, teacher_name, student_name, score )
  1. results テーブルの定義 (Define a table)

    次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

    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 ),
        UNIQUE (name, student_name),
        UNIQUE (teacher_name, student_name) );
    

    ※ 「SQL Editor」のウインドウには,SQL プログラムを書くことができる. In the 'SQL string' window, you can write down SQL program(s).

  2. コンソールの確認 (Inspect console)

    エラーメッセージが出ていないことを確認


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

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

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 を用いて results テーブルへの行の挿入を行う (Insert rows into table 'results' using SQL)

  1. SQL プログラムの記述

    「INSERT INTO ...」は行の挿入.ここには 5つの SQL 文を書き, 「BEGIN TRANSACTION」と「COMMIT」で囲む. ("INSERT INTO ..." means inserting a row into a table. Four SQL statements are wrote).

    BEGIN TRANSACTION;
    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 );
    COMMIT;
    

  2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に, 「Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認


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


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 rows 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;

データのブラウズ機能を使いテーブル A を確認 (Browse Table A)

SQL の実行 (Execute SQL)

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

データのブラウズ機能を使いテーブル B を確認 (Browse Table B)


結合問い合わせ (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;

情報無損失分解にならない分解 (Inspect lossless decomposition)

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

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