テーブルの分解 (table decomposition)

【概要】 リレーショナルデータベースでのテーブルの分解は，あるテーブルを属性集合の部分集合による射影で置き換えるプロセスであり，X₁∪X₂∪…∪Xₘ = {A₁, A₂,…, Aₙ}を満たす．情報無損失分解は，分解されたテーブルから元のテーブルが復元可能な分解である．関数従属性はリレーションスキーマの属性間の依存関係を表すものであり，ある属性の値が決まれば別の属性の値が一意に決まる関係である．ボイス・コッド正規形は，任意の関数従属性X→Yについて，Xがスキーマのキーになっていると定義される．ボイス・コッド正規形は更新時異常を解消可能だが，正規化前のテーブルにおける関数従属性を維持できない場合がある．

大学授業用に作成した資料を更新・改良して公開しています．これらは クリエイティブコモンズ 表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0） で提供しており，事前の許可なく自由に利用できます．条件は著作者表示（BY），非営利目的のみ（NC），同一ライセンスでの再配布（SA）です．







演習で行うこと

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

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

Sqliteman の起動と終了

  1. Sqliteman の起動

    * Ubuntu での SQLiteman の起動例

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

    SQLiteman の新しいウインドウが開く．

    * Lubuntu での SQLiteman の起動例

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

    SQLiteman の新しいウインドウが開く．

    * Windows での SQLiteman の起動例

    「Sqliteman」 のアイコンをダブルクリック

    Sqliteman のウインドウが開く．

SQLiteman で新しいデータベースを作成する

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

以下の手順で，新しいデータベースを作成する．その結果，データベースファイルができる．

  1. 「File」→ 「New」
  2. データベースの新規作成を開始する

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

Sqliteman で既存のデータベースを開く

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

以下の手順で，既存のデータベースファイルを開く．

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

    * Ubuntu での実行例（「mydb」を開く場合）

    データベースファイル mydb を選び， 「開く」をクリック

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

    データベースファイル C:\SQLite\mydb を選び， 「開く」をクリック

    要するに，/home/＜ユーザ名＞/SQLite 3の下の mydb を選ぶ．　

SQL を用いたテーブル定義と一貫性制約の記述

SQL を用いて，scores テーブルを定義し，一貫性制約を記述する．

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

  1. scores テーブルの定義

    次の SQL を入力し，「Run SQL」のアイコンをクリック 

    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 プログラムを記述することができる．

  2. コンソールの確認

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

SQL を用いたテーブルへの行の挿入

次のような scores テーブルを作成する．

以下の手順で，SQL を用いて scores テーブルへの行の挿入を行う．

  1. SQL プログラムの記述

    「insert into ...」は行の挿入を意味する．ここには 5つの SQL 文を書き， 「begin transaction」と「commit」で囲む． 

    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 文の一括実行

    複数の 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」ウインドウの確認

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

Sqliteman を用いたデータのブラウズ

DISTINCT を含む SQL の例

DISTINCT を付ける場合 

SELECT DISTINCT name, teacher_name
FROM scores;

DISTINCT を付けない場合 

SELECT name, teacher_name
FROM scores;

DISTINCT を付ける場合 

SELECT DISTINCT score
FROM scores;

DISTINCT を付けない場合 

SELECT score
FROM scores;

SQL を用いたテーブルの分解

scores( name, teacher_name, student_name, score )を 2つのテーブルに分解する：

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

SQL の実行 

create table A AS
select distinct name, teacher_name
FROM scores;

データのブラウズ機能を使いテーブル A を確認する．

SQL の実行 

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

データのブラウズ機能を使いテーブル 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;

情報無損失分解にならない分解

今度は，テーブルの分解の仕方を変える． 分解後のテーブルから，もとのテーブルに復元できない場合があることを確認する．

Table decompositions are not always LOSSLESS.

SQL の実行 

create table C AS
select distinct name, student_name
FROM scores;

データのブラウズ

SQL の実行 

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

データのブラウズ

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

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

演習問題と解答例

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

問い

  1. 次の 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 の評価結果は何か？ 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 を実行した． 

    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 の評価結果は何か？ 
      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)

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