テーブルの分解 (table decomposition)

大学で使用した自作の資料等を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.

リレーショナルデータベースの基礎であるテーブル定義,一貫性制約,SQL,結合と分解,トランザクション,埋め込みSQL,実行計画,二次索引を学ぶ.SQLite 3 を用いて,SQL についての演習も行う.

【サイト内の関連ページ】

  • リレーショナルデータベースの基本まとめ:別ページ »で説明
  • SQL 入門演習(SQLite 3 を使用)(全3回):別ページ »にまとめ

    入門者向け.リレーショナルデータベースを知る.

  • リレーショナルデータベースシステム(スライド資料と SQL 演習)(全15回):別ページ »にまとめ

    入門者向け.設計と利用と運用.MySQL, Access を利用.基本を学ぶ.演習付き.

  • リレーショナルデータベース演習(全15回):別ページ »にまとめ

    入門者向け.設計と利用に関する演習.

  • リレーショナルデータベースの基礎(まとめ) [PDF], [ワードファイル]

    基礎を1つにまとめた資料.














演習

演習で行うこと

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

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

Sqliteman の起動と終了 (Start Sqliteman)

  1. Sqliteman の起動 (Start Sqliteman)

    * Ubuntu での SQLiteman の起動例

    プログラミング」→「Sqliteman」と操作する.

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

    * Lubuntu での SQLiteman の起動例

    プログラミング」→「Sqliteman」と操作する

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

    * Windows での SQLiteman の起動例

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

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

SQLiteman で新しいデータベースを作成する (Create a new database)

前回の授業で作成したデータベースを使うときは、ここの手順は行わなくて良い

以下の手順で,新しいデータベースを作成する.その結果,データベースファイルができる. (Create a new database)

  1. File」→ 「New
  2. データベースの新規作成を開始する (Start a new databae creation)

    データベースファイル名 を指定

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

すでに作成済みのデータベースを,下記の手順で開くことができる.

以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)

  1. File」→ 「Open
  2. データベースファイルを開く (Open Database File)

    * Ubuntu での実行例(「mydb」を開く場合)

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

    * Windows での実行例(「C:\SQLite\mydb」を開く場合)

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

    要するに,/home/<ユーザ名>/SQLite 3の mydb を選ぶ. 

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

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

リレーショナル・スキーマ (relational schema): scores(id, name, teacher_name, student_name, score, created_at, updated_at)

  1. scores テーブルの定義 (Define a table)

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

    create table scores (
        id            integer  primary key autoincrement not null,
        name          text     not null,
        teacher_name  text     not null,
        student_name  text     not null,
        score         integer  not null check ( score >= 0 AND score <=100 ),
        created_at    datetime not null,
        updated_at    datetime,
        UNIQUE (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)

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

以下の手順で,SQL を用いて scores テーブルへの行の挿入を行う (Insert rows into table 'scores' 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 scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
    insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
    insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
    insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
    insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );
    commit;
    
  2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする. 「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 scores;

DISTINCT を付けない (without DISTINCT)

SELECT name, teacher_name
FROM scores;

DISTINCT を付ける (with DISTINCT)

SELECT DISTINCT score
FROM scores;

DISTINCT を付けない (without DISTINCT)

SELECT score
FROM scores;

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

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

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

'scores' 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 scores;

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

SQL の実行 (Execute SQL)

create table B AS
select distinct id, name, student_name, score
FROM scores;

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

結合問い合わせ (join query)

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

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

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

  1. Store 'scores' into database, or
  2. Store 'A' and 'B' instead of 'scores' in database.
SELECT B.id, 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 scores;

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

SQL の実行 (Execute SQL)

create table D AS
select distinct id, teacher_name, student_name, score
FROM scores;

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

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

SELECT D.id, 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)

  1. 次の 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;
      
  2. 下記に関する問題

    次のようなテーブル R(id, price, type) を作りたい.

    id|price|type
    ------------------------------
    1 |10   |author
    2 |21   |name
    3 |30   |author
    4 |45   |name
    5 |50   |author
    

    そこで,SQLite 3 で 次の SQL を実行した.(To create table R, the following SQL commands are evaluated on SQLite 3)

    create table R (
     id   integer primary key autoincrement not null,
     price  integer not null,
     type  text );
    insert into R(price, type) values(10, 'author');
    insert into R(price, type) values(21, 'name');
    insert into R(price, type) values(30, 'author');
    insert into R(price, type) values(45, 'name');
    insert into R(price, type) values(50, 'author');
    
    1. 次の SQL の評価結果は何か? What is the result of the following SQL ?
      SELECT DISTINCT type FROM R; 
      
    2. 次のようなテーブル S(typeid, type) を作りたい.
      tid |  type  
      -----------
      1   |author
      2   |name
      

      そのために、次のSQLを評価させる.

      create table S (
       tid   integer primary key autoincrement not null,
       type  text); 
      insert into S(tid, type) values(1, 'author');
      insert into S(tid, type) values(2, 'name');
      
    3. 次の SQL の評価結果は何か? What is the result of the following SQL ?
      SELECT R.id, R.price, R.type, S.tid FROM R, S where R.type = S.type; 
      
  3. 次のSQLを評価させる。「SELECT * FROM T2; 」の評価結果は何か?
    CREATE TEMPORARY TABLE T1 AS select distinct type FROM R where type IS not null;
    create table T2 AS SELECT OID AS typeid, * FROM T1;
    SELECT * FROM T2; 
    
  4. RとSを使って,テーブル RR(id, price, typeid) を作りたい

    そこで、次のSQLを評価させる。「SELECT * FROM RR;」の評価結果は何か?

    create table RR AS SELECT R.id AS id, R.price AS price, S.tid AS tid
    FROM R NATURAL JOIN S;
    SELECT * FROM RR;
    

解答例 (Answers)

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

  • 問1
    1. select distinct name, type FROM PTABLE;
      
      name    |  type  
      ------------------
      apple   |  fruit 
      rose    |  flower
      
    2. select distinct name FROM PTABLE;
      
      name   
      --------
      apple  
      rose   
      
  • 問2
    1. select distinct type FROM R; 
      
      type
      --------
      author
      name
      
    2. SELECT R.id, R.price, R.type, S.tid FROM R, S where R.type = S.type; 
      
      id |  price  |  type  |  tid
      -----------------------------
      1  | 10      | author | 1
      2  | 21      | name   | 2
      3  | 30      | author | 1
      4  | 45      | name   | 2
      5  | 50      | author | 1
      
    3.  SELECT * FROM T2; 
      
      1|author
      2|name
      
    4. SELECT * FROM RR;
      id|price|tid 
      1|10|1
      2|21|2
      3|30|1
      4|45|2
      5|50|1