リレーショナルデータベース(関係データベース)はIBMのエドガー・F・コッド氏が発明した関係モデルを元にしたデータベースで、商用のリレーショナルデータベースが最初に登場したのは1979年と比較的古いですが、現在でも主要なデータベースの一つです。
リレーショナルデータベースにはデータベースの表を設計するための正規化理論やER図、データを取得するためのSQL(Structured Query Language) という強力な問い合わせ言語が備わっています。
リレーショナルデータベースは様々な企業や団体から製品がリリースされており、商用製品としてはIBMのDb2やOracle社のOracle Databaseなどが有名です。また、オープンソースのデータベースとしてはMySQLやPostgreSQLなどが存在します。
サンプルプロジェクト
表と表の関係でシステムのデータを表現する
リレーショナルデータベースではシステムのデータを表と表の関係で表します。文科省の教員研修資料では蔵書目録データベースを題材にしておりますが、このデータベースも書籍情報と作者情報を2つの表の組み合わせ表現されています。表現したいデータを複数の表に分割して表すことを正規化と呼びます。正規化を行うことで、データが冗長になることを避けたり、更新漏れを防ぐことができます。
文科省の教員研修資料の例では次のような蔵書目録データベースの例が示されています。
◆蔵書テーブル
| 書籍ID | 書籍名 | 書籍作者ID | 作者名 |
|---|---|---|---|
| 1 | 羅生門 | 1001 | 芥川龍之介 |
| 2 | こころ | 1002 | 夏目漱石 |
| 3 | 坊っちゃん | 1002 | 夏目漱石 |
| 4 | 鼻 | 1001 | 芥川龍之介 |
| 5 | 舞姫 | 1003 | 森鴎外 |
| 6 | 吾輩は猫である | 1002 | 夏目漱石 |
上記の表は書籍情報と作者情報が「結合」された結果の表です。結合前の分割された状態の表は以下になります。
◆書籍テーブル
| 書籍ID | 書籍名 | 書籍作者ID |
|---|---|---|
| 1 | 羅生門 | 1001 |
| 2 | こころ | 1002 |
| 3 | 坊っちゃん | 1002 |
| 4 | 鼻 | 1001 |
| 5 | 舞姫 | 1003 |
| 6 | 吾輩は猫である | 1002 |
◆作者テーブル
| 書籍作者ID | 作者名 |
|---|---|
| 1001 | 芥川龍之介 |
| 1002 | 夏目漱石 |
| 1003 | 森鴎外 |
上記の表をER図で表すと次のようになります。
※ ER図作成ソフトのwwwsqldesignerにて作成
補足
列が少ないとテーブルを分割するメリットが感じにくいかもしれませんが、仮に作者の作者の生年や没年などを記録しようと思った場合には分割しないと冗長になり破綻します。また、図書館の蔵書システムならば貸し出し情報なども管理する必要があるため、更にテーブルが増えていきます。それと、作者が複数人いるケースを想定する場合は書籍と作者の関係がN:1ではなくN:Mの関係になるため「連関エンティティ」が必要になります。
蔵書目録データベースを作成してSQL文でデータを操作してみよう
リレーショナルデータベース上に表を作成するときには「CREATE文」を使用します、また、データの挿入は「INSERT文」、参照は「SELECT文」を使います。サンプルプロジェクトをMonacaに取り込んで、それぞれのSQL文の記述例や動作を確認してみて下さい。
※ AlaSQLではテーブル名や列名に日本語が使えなかったため、一部、英語表記にしております。
let result;
// テーブル定義とデータのINSERT
alasql("CREATE DATABASE zosho");
alasql("USE zosho");
alasql("CREATE TABLE author (id INT, name TEXT)");
alasql("INSERT INTO author VALUES (1001, '芥川龍之介')");
alasql("INSERT INTO author VALUES (1002, '夏目漱石')");
alasql("INSERT INTO author VALUES (1003, '森鴎外')");
alasql("CREATE TABLE book (id INT, name TEXT,author_id INT)");
alasql("INSERT INTO book VALUES (1, '羅生門', 1001)");
alasql("INSERT INTO book VALUES (2, 'こころ', 1002)");
alasql("INSERT INTO book VALUES (3, '坊っちゃん', 1002)");
alasql("INSERT INTO book VALUES (4, '鼻', 1001)");
alasql("INSERT INTO book VALUES (5, '舞姫', 1003)");
alasql("INSERT INTO book VALUES (6, '吾輩は猫である', 1002)");
// データのSELECT
result = alasql("SELECT * FROM author");
console.log(result);
result = alasql("SELECT * FROM book");
console.log(result);
result = alasql("SELECT * FROM book INNER JOIN author ON book.author_id = author.id");
console.log(result);
出力結果例
上記のサンプルでは３回、SELECT文を実行しています。以下は最後に実行したJOIN(結合)を伴うSELECTの出力例です。
(6) [{…}, {…}, {…}, {…}, {…}, {…}]
0: {id: 1001, name: "芥川龍之介", author_id: 1001}
1: {id: 1002, name: "夏目漱石", author_id: 1002}
2: {id: 1002, name: "夏目漱石", author_id: 1002}
3: {id: 1001, name: "芥川龍之介", author_id: 1001}
4: {id: 1003, name: "森鴎外", author_id: 1003}
5: {id: 1002, name: "夏目漱石", author_id: 1002}
追加演習：レコードの追加と削除を行う
テーブルに新しいレコードを追加したいときには「INSERT文」を使用します。
alasql("INSERT INTO book VALUES (7, '高瀬舟', 1003)");
また、削除したいときには「DELETE文」を使用します。
alasql("DELETE FROM book WHERE ID = 7");
DELETEを行う際にはWHERE句を使って削除対象を絞り込む必要があります、WHEREを付け忘れた場合には無慈悲にテーブルのデータが全て削除されます。
なお、データを修正したいときには「UPDATE文」を使用します。
選択・射影・結合について
リレーショナルデータベースからデータを選択して取得することを「選択」と呼びます、具体的にはWHERE句でレコード（行）の絞り込みを行うことを指します。また、射影はフィールド（列）の絞り込みを行うことを指します。「SELECT * …」と記述した場合には全てのフィールドを取得しますが例えば「SELECT id …」と記述した場合にはIDのみを取得します。「結合」は複数のテーブルを「JOIN句」で結合することを指します。
おわりに
あんこエデュケーション内にAlaSQLを使ったSQLの入門リファレンス記事をご用意しております、こちらも合わせてご活用下さい。