金子邦彦研究室 ▶ 情報工学全般 ▶ リレーショナルデータベース（全１１回） ▶ テーブルの分解 (table decomposition)

サイトマップ サイト内検索 まとめ

サイトの全体構成

テーブルの分解 (table decomposition)

全体構成 人工知能 データベース ３次元，地図 プログラミング 情報工学全般 インストール 支援 連絡先，業績

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

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

【サイト内のリレーショナルデータベース関連の資料】


[image]
[image]
[image]
[image]
[image]
[image]

サイトの全体構成

演習

全体構成 人工知能 データベース ３次元，地図 プログラミング 情報工学全般 インストール 支援 連絡先，業績

演習で行うこと

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」と操作する．

    [image]

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

    [image]

    ■ Lubuntu での SQLiteman の起動例

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

    [image]

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

    [image]

    ■ Windows での SQLiteman の起動例

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

    [image]

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

    [image]

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」

    [image]
  2. データベースファイルを開く (Open Database File)

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

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

    [image]

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

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

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

    [image]

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).

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

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

    [image]

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

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

[image]

以下の手順で，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;

    [image]
  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)

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

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

    [image]

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

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

DISTINCT を付ける (with DISTINCT) 

SELECT DISTINCT name, teacher_name
FROM scores;

[image]

DISTINCT を付けない (without DISTINCT) 

SELECT name, teacher_name
FROM scores;

[image]

DISTINCT を付ける (with DISTINCT) 

SELECT DISTINCT score
FROM scores;

[image]

DISTINCT を付けない (without DISTINCT) 

SELECT score
FROM scores;

[image]

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;

[image]

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

[image]

SQL の実行 (Execute SQL) 

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

[image]

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

[image]

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

[image]

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

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

Table decompositions are not always LOSSLESS.

SQL の実行 (Execute SQL) 

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

[image]

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

[image]

SQL の実行 (Execute SQL) 

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

[image]

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

[image]

テーブル 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;

[image]

サイトの全体構成

演習問題と解答例

全体構成 人工知能 データベース ３次元，地図 プログラミング 情報工学全般 インストール 支援 連絡先，業績

次の問いに答えよ．その後，下記の解答例を確認せよ． 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)

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

本サイトは金子邦彦研究室のWebページである．

資料等の公開では，原則，「クリエイティブコモンズ BY NC SA」として公開するようにしている． PDFファイル，パワーポイントファイルなどには， 「クリエイティブコモンズ BY NC SA」を明記するとともに，ロゴを記載するようにしている（作業が間に合っていない分もあるのでご容赦ください）．

公開している資料をご利用になる場合の，再配布の条件，剽窃の防止などについて，別ページ »で説明 再配布や資料改変の際には，そのページをご確認ください．

サイトマップは，サイトマップのページをご覧下さい． 本サイト内の検索は，サイト内検索のページをご利用下さい．

問い合わせ先： 金子邦彦（かねこ くにひこ） [image]