テーブルの分解 (table decomposition)

【概要】

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

【目次】

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

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







用語

演習で行うこと

SQLite Manager Tool の起動と終了

  1. SQLite Manager Tool の起動

    * Ubuntu での SQLite Manager Tool の起動例

    「プログラミング」→「SQLite Manager Tool」と操作する．

    SQLite Manager Tool の新しいウインドウが開く．

    * Lubuntu での SQLite Manager Tool の起動例

    「プログラミング」→「SQLite Manager Tool」と操作する．

    SQLite Manager Tool の新しいウインドウが開く．

    * Windows での SQLite Manager Tool の起動例

    「SQLite Manager Tool」 のアイコンをダブルクリックする．

    SQLite Manager Tool のウインドウが開く．

SQLite Manager Tool で新しいデータベースを作成する

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

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

  1. ツールバーの「新規DB作成」ボタンをクリックする．
  2. データベースの新規作成を開始する

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

SQLite Manager Tool で既存のデータベースを開く

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

  1. ツールバーの「DBオープン (Ctrl+O)」ボタンをクリックする．
  2. データベースファイルを開く

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

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

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

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

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

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

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

  1. scores テーブルの定義

    次の SQL を入力し，「▶ SQL実行 (F5)」を押下する 

    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エディタ」欄には，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エディタ」に記述した複数の SQL 文をまとめて実行するには，「▶ SQL実行 (F5)」を押下する．

  3. 「出力」欄の確認

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

SQLite Manager Tool を用いたデータのブラウズ

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 の評価結果は何か？ 
      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 の評価結果は何か？ 
      SELECT DISTINCT type FROM R;
    2. 次のようなテーブル S(tid, 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, tid) を作成したい．

    そこで，次の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;

解答例

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