2021-11-30

第2章 IoTシステムのコンピューティング技術

IoT技術試験

2-1 クラウド/エッジコンピューティング

p20 IoTシステム構成

フィールド領域：IoTデバイス，IoTゲートウェイ

インフラストラクチャ領域：IoTサーバ，クラウドなど

フィールド領域とインフラストラクチャ領域はWANで連携される．

IoTデバイスやIoTゲートウェイ間の通信には，無線PAN近距離無線(無線LAN，ZigBee，Bluetoothなど)，有線LAN，PLCなどが使われる．

WANには，3G，LTE，WiMAXなどの移動体通信ネットワークや，固定系ネットワークが使われる．

p21 IoTゲートウェイの機能

中継処理

・データの集約

・送信タイミングの調整

・フィルタリング処理(ノイズ除去など)

・エッジコンピューティングによるデータ処理

負荷分散

処理内容によって，処理をクラウド側，エッジ側に分散させる．

リアルタイム処理

自動走行車の制御などのリアルタイム処理

p22 クラウド/エッジコンピューティング

クラウドコンピューティング

クラウドネットワーク上のコンピュータリソースを必要に応じて利用し，生産性向上，コスト削減，データ管理の効率化を図る．

エッジコンピューティング

伝送遅延を抑えるため，ネットワーク上のユーザの近いところで処理を行う．

2-2 IoTゲートウェイ

p24 接続形態

直接接続

IoTデバイスをIoTサーバに直接接続する時，IoTデバイスは複数のIoTサーバと通信をするのがスペック的に難しいときもある．さらに，IoTサーバはつながったIoTデバイスを全て管理する必要があるので大変．

IoTゲートウェイ経由接続

直接接続の課題を解決する．

IoTデバイスが収集したデータを集約することで，通信上の負荷を軽減できる．

通信を終端することで，不正アクセス防止にもつながる．

IoTエリアネットワーク内の通信プロトコルとWANの通信プロトコルの変換を行う．

p26 OSGi

OSGiはAllianceによって標準化されたJavaベースのソフトウェアコンポーネントらしいがよくわからんのでパス．

p27 プロトコル変換

IoTネットワークとWANでは使う通信プロトコルが異なるため，変換が必要である．

IoTエリアネットワークの通信では低コスト，低消費電力を求めるため，6LoWPAN，CoAPを使うと嬉しい．TCP/IPとの互換性も高いのでさらに嬉しい．

WANではTCP/IPを使うことが多い．

2-3 クラウドコンピューティング

p29 クラウドサービスの形態

IaaS

コンピュータシステムの環境を提供．

PaaS

IaaSをベースに，データ収集，分散処理，保存などのサービスを提供．

自分で用意できなさそうな部分をサービスとして利用する．

SaaS

データ処理はPaaSに任せて，データ利用のためのアプリケーションは自分で作成したり，提供されるソフトウェアを使う．

BaaS

IoTアプリケーションのバックエンドに必要なサービス(データ保管，プッシュ通信，ユーザ管理，SNS連携など)を提供し，IoTアプリケーションからAPIで呼び出す．

p30 パブリック/プライベートクラウド

パブリッククラウド

プロバイダが提供するクラウドコンピューティング環境であり，不特定多数のユーザがインターネットを通じてサービスを利用する．

プライベートクラウド

ある企業にだけパブリッククラウドを切り出し，高度なセキュリティポリシーを適用するなどの自由度の高いシステム構築に向いた形態．

中でも，企業が独自にサーバを所有し，企業内に構築するプライベートクラウドをオンプレミスという．

2-4 エッジコンピューティング

クラウドコンピューティングは伝送遅延がネックとなるため，エッジコンピューティングの考え方が考案された．

エッジコンピューティングは伝送遅延を抑えるため，ネットワーク上のユーザの近いところで処理を行う．

メリット

近くのサーバなどに処理の一部を肩代わりさせることで，その分高度なアプリケーションを動作させるリソースが生まれる．

クラウドへのデータ伝送を省略することで，ネットワークトラフィックを削減可能．

p33 エッジコンピューティングの活用

oneM2MのVehicle Data Collection Service(エッジコンピューティングを車両に導入)

Edgecrossコンソーシアムは2017年に設立が発表された団体で，ITシステムとFA(Factory Automation)の連携を図ることを目的とする．

ファナック株式会社は，2017年にFIELD systemと呼ばれるIoTとFAの連携をうたうプラットフォームをリリースした．

p36 エッジAI

大規模データ処理などの大量のコンピュータパワーを必要とする環境はクラウド上でしか構築できなかったが，自動走行車などの分野ではリアルタイムなAI分析が必要であり，エッジ側でAIを実行する考え方が生まれた．

要件として，インターネットに接続できない環境や，サーバ接続ができない環境下でのエッジ側のみでの稼働がある．

上記の実現のためには，以下があげられる．

(1)AI分析モデルを実行可能なハードウェア拡張

GPUを組み込む，分析処理に特化したアクセラレータを組み込む，エッジAI向けSoCを組み込む，などがある．

(2)学習済みモデルのエッジ側への組み込み

クラウドで作成した学習モデルをエッジで使用する．

(3)FPGA(Field Programmable Gate Array)の活用

単純な処理の繰り返しをFGPAに専用の論理回路(VHDLやVerilogなどで記述)に組み込む．

論理回路設計を間違えても現場で修正可能なのが特徴であり，開発コストを抑えたり，開発リスクを低減できるメリットもある．

2-5 データ駆動型システム

p39 CPS(Cyber Physical System)

現実世界のセンサデバイスなどが生み出すデータを，仮想世界で処理することで価値を創出する考え方．

Physical空間のセンサなどで集めたデータをゲートウェイなどを経由してCyber空間のサーバで処理をし，Physical空間にフィードバックする．このような動きは，「データ駆動」に基づくシステムの機能といえる．

p41 Industrie4.0

ドイツ政府が推進しており，ドイツ製造業の高度化を目指す戦略的プロジェクトである．ICT，IoT技術を駆使した製造業の革新を目指している．

基本はCPSをベースとして製造業を強化することで，工場の稼働状況をリアルタイムに把握し，他の工場との連携などを含めたバリューチェーン全体にわたって効率化を図る．

p41 デジタルツイン

Physical空間が生成するデータをもとに，Cyber空間上に仮想的な製品製造を行うシミュレーション環境を構築する．デジタルツインのシミュレーションにより，Physical空間の各種データはどのような動きをするのか，どのような影響を周辺機器に与えているかなどを予測できる．

p42 IoTサービスプラットフォーム

IoTサービスは，サービス分野ごとに特殊な固有サービス機能(IoTアプリケーション)と，サービス分野に依存しない共通機能に分けられる．

IoTアプリケーション

(1)フィールド領域

センサ，アクチュエータを含むIoTデバイスや，複数のIoTデバイスを集約するIoTゲートウェイが含まれる．

(2)インフラストラクチャ領域

IoTデバイスからのデータの集約，分析，あるいはIoTデバイスを制御するサーバやクラウド部分を指す．

(3)下位層ネットワーク

フィールド領域とインフラストラクチャ領域を連携させる．

p43 垂直統合型と水平連携型

(1)垂直統合型

分野ごとに単一のIoTアプリケーションとそれにかかわるIoTゲートウェイ，IoTデバイスが接続され，エンドツーエンドでのサービス提供が行われる．IoTシステムにおいて共通する機能を毎回実装する必要があり，コストがかかるという欠点がある．

(2)水平連携型

IoTシステムにおける共通機能はプラットフォームとして提供し，IoTアプリケーションはこのプラットフォーム上で個別に構築する．共通機能の構築コストを抑えられるだけでなく，異なるIoTアプリケーションが収集したデータを共有できるメリットもある．

共通機能

(1)データ収集

IoTデバイスやIoTゲートウェイからデータを収集する．

(2)データ蓄積

収集したデータを蓄積する．

(3)データ可視化・分析

収集，蓄積したデータを可視化したり，データの相関分析や特異点検出を行う．

(4)遠隔制御

IoTデバイスからの指示やデータ分析の結果に基づいて，IoTデバイスに接続されたアクチュエータを駆動させるための制御コマンドを送信する．

(5)イベント通知

IoTデバイスが検知した状態変化や取得したIoTデータをIoTアプリケーションに通知する．

(6)デバイス管理

IoTデバイスの位置や接続方法，状態を管理したり，IoTデバイスのファームウェアをアップデートする．

(7)アプリケーションインターフェース

様々なIoTアプリケーションからIoTサービスを利用できるように，インターフェースを提供する．

p45 データ収集の方法

(1) アップロード方式

IoTデバイスまたはIoTゲートウェイが主導となり，IoTサービスにデータをアップロードする．アップロード方式はさらに以下の3つに分類される．

(a)逐次収集方式

IoTデバイスでデータが発生した都度，または定期的にIoTサービスにデータをアップロードする．

リアルタイムにデータを収集することが可能である反面，データサイズに比べて通信ヘッダが大きくなり，ネットワーク負荷が増大する．また，計測したデータを全てサーバに蓄積するため，ストレージコストもかかる．

(b)一時蓄積方式

IoTゲートウェイにデータを一時的に蓄積しておき，一定時間ごとにまとめてIoTサービスにアップロードする．

ネットワーク負荷を軽減できるが，リアルタイムなデータ収集には向いていない．また，ストレージコストはかかる．

(c)区間集約方式

IoTゲートウェイで蓄積したデータの集約のみをIoTサーバにアップロードする．

ネットワーク負荷とストレージコストの両方を解決するが，リアルタイムなデータ収集には向いていない．

(2) ポーリング方式

IoTプラットフォームが主導となり，IoTデバイス/ゲートウェイからデータを取得する．

IoTアプリケーションが必要とするタイミングでデータを収集できる．また，IoTデバイスが非常に多くても，IoTアプリケーションが順番にデータ収集をするので，ネットワーク負荷やサーバ負荷をおさえられる．

しかし，リアルタイムなデータ収集には向いていない．

(3) パブリッシュ・サブスクライブ方式

あらかじめIoTアプリケーションが必要なデータのトピックをsubscribeすることをIoTゲートウェイに伝えておく．IoTゲートウェイは，IoTデバイスからpublishされたデータを，そのデータをsubscribeしているIoTアプリケーションに送信する．

p47 遠隔制御

(1)直接制御方式

IoTサービスプラットフォームが必要とするタイミングで遠隔制御要求をIoTゲートウェイに送信し，応答を受け取る．

IoTデバイスを即座に制御できるが，不正操作防止のアクセス制御などが必要．

(2)ポーリング方式

IoTゲートウェイは定期的にIoTプラットフォームに遠隔制御要求の有無を照会する．要求があれば，それを取得し応答する．要求がなければ，一定時間後(ポーリング間隔)に再度照会する．

安全性は高いが，要求の応答性はポーリング間隔に依存する．

(3)ロングポーリング方式

IoTゲートウェイはIoTプラットフォームに遠隔制御要求の有無を照会する．要求がなければ，IoTプラットフォームで要求があるまで待機する．

安全性と即時性をともに満たすが，IoTゲートウェイが多く接続されるシステムではサーバ負荷が問題となることもある．

(4)双方向通信方式

双方向のプロトコルを使用することで，IoTサービスプラットフォームとIoTゲートウェイのどちらからでも要求を出せる．

XMMPで遠隔制御を実現することも可能である．

(5)ウェイクアップ方式

通常はスリープ状態にしておき，信号のやり取りで機器を起動させる方式．遠隔制御の際にはSMSなどで信号をやり取りする．

ネットワーク負荷の軽減につながる．

参考

IoT技術テキスト第3版

【MCPC】第６回IoTシステム技術検定のうろ覚え過去問

https://www.gg-sikau.com/?p=325

IoTシステム技術検定中級テキスト第２版抜粋音声読み上げ用

https://qiita.com/sxnxhxrxkx/items/bda596a4a6abc2504385#%E7%AC%AC1%E7%AB%A0-iot%E6%A6%82%E8%A6%81

難なく MCPC IoTシステム技術検定試験(中級) に合格したい

https://cutnpaste.hatenablog.com/entry/2017/12/03/193809

2021-11-25

第1章 IoT概要

IoT技術試験

p4 IoTを取り巻く世界の動き

Industrie4.0(ドイツ)

第4次産業革命．

IIC(Industry Internet Consortium)

Industry Internetの普及が目的である．

AllSeen Alliance

各社デバイスやサービスの相互運用のためのフレームワークを開発．

p4 標準化動向

oneM2M

共通サービスプラットフォームの普及を図る．プロダクトの相互接続性や規格適合性を定めた技術書を出している．

3GPP

NB-IoT(Narrow Band IoT)の標準化を進めていて，LPWA(Low Power Wide Area)の構築を進めている．

p4 オープンイノベーション

企業間の技術やアイデアを組み合わせたり，自社の特許などを公開し，新たな価値を創出する考え方．

ArduinoやRaspberry Piなどの普及により，メイカームーブメント(無数の個人がコミュニティなどで協力し合い，モノの製造などに取り組むこと)が進んでいる．

p7 IoTシステム構成

IoTサーバ

収集したデータを加工，分析し，実世界に活かす．

IoTゲートウェイ

・受信したデータを集約してIoTサーバへ送信する

・フィルタリング

・通信トラフィック削減のため，前処理など

・エッジコンピューティング

p12 アジャイル開発

システム開発におけるプロジェクト管理方法の一つで，スクラムやXPなどの流派がある．

イテレーションという短期間(１～２週間が多い)での開発を繰り返すことで，開発リスクを低減している．

p16 DX

Digital Transformationのことで，定義は場合によって異なる．

ストルターマン教授によると，「ITの浸透が，人々の生活をあらゆる面でより良い方向に変化させる」こと．

企業がDXに取り組む目的は，急激な市場の変化から取り残されないこと，人口減少による人材不足への対応，など．

参考

IoT技術テキスト第3版

【MCPC】第６回IoTシステム技術検定のうろ覚え過去問

https://www.gg-sikau.com/?p=325

IoTシステム技術検定中級テキスト第２版抜粋音声読み上げ用

https://qiita.com/sxnxhxrxkx/items/bda596a4a6abc2504385#%E7%AC%AC1%E7%AB%A0-iot%E6%A6%82%E8%A6%81

難なく MCPC IoTシステム技術検定試験(中級) に合格したい

https://cutnpaste.hatenablog.com/entry/2017/12/03/193809

2021-01-31

SQL<後編>

実は，SQLはデータ定義言語(DDL：Data Definition Language)とデータ操作言語(DML：Data Manipulation Language)に分類されます．

前回見たSELECTやらWHEREやらはDMLに分類されます．

後半の記事では，DDLや表の結合，副問合せを扱います．

1.データ定義言語 DDL

前回はDMLを見ましたが，今回はDDLについて見ていきます．

1.1.CREATE TABLE

CREATE TABLE文は，テーブルを新規作成するコマンドです．

基本的な構文が3つあるので順に見ていきます．

1.1.1.構文1

CREATE TABLE(

"列名1" "データ型" "列制約"
"列名2" "データ型" "列制約"
$\vdots$
)

主なデータ型は下の通りです．

f:id:chankilu23:20210109194124j:plain

また，主な列制約は下の通りです．

f:id:chankilu23:20210109194150j:plain

構文1に従って表を作成する例を挙げます．

例1

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4) PRIMARY KEY,
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3) CHECK(成績<=100),
学部コード $\hspace{0.1cm}$ DEC(3) REFERENCES 学部
)

1.1.2.構文2

CREATE TABLE(

"列名1" "データ型" "列制約"
"列名2" "データ型" "列制約"
$\vdots$
表制約
)

主な表制約を挙げます．

PRIMARY KEY("列名1", "列名2", ...)

"列名1", "列名2", ...を主キーに設定します．

なお，同時に非NULL制約と一意性制約が設定されます．

FOREIGN KEY("列名1", "列名2", ...) REFERENCES "表名" ("列名A", "列名B", ...) [+]

"列名1", "列名2", ...を，"表名"を参照先とする外部キーに設定します．

"列名1", "列名2", ...に対応する主キーの名称を指定する場合は"列名A", "列名B", ...とします．

"列名1", "列名2", ...と"列名A", "列名B", ...が同じ名称の場合は省略可能です．

また，[+]で指定した規則に従います．

[+]はを参照してください．

CHECK("条件")

検査制約を設定します．

以上を踏まえて，構文2に従って表を作成する例を挙げます．

例2

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4) PRIMARY KEY,
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3),
学部コード $\hspace{0.1cm}$ DEC(3),
PRIMARY KEY(学籍番号),
FOREIGN KEY(学部コード) REFERENCES (学部),
CHECK(成績　<= 100)
)

1.1.3.構文3

CONSTRAINTを用いますが，これは制約に名前をつけるだけで，表制約の書き方は上の構文2と同じです．

CONSTRAINT "制約名" "表制約" の形で記述し，"表制約"に"制約名"という名前をつけられます．

CREATE TABLE(

"列名1" "データ型" "列制約"
"列名2" "データ型" "列制約"
$\vdots$
CONSTRAINT "制約名1" "表制約", $\dots$
CONSTRAINT "制約名1" "表制約", $\dots$
)

構文3に従って表を作成する例を挙げます．

例3

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4) PRIMARY KEY,
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3),
学部コード $\hspace{0.1cm}$ DEC(3),
CONSTRAINT 学生制約1 PRIMARY KEY(学籍番号),
CONSTRAINT 学生制約2 FOREIGN KEY(学部コード) REFERENCES (学部),
CONSTRAINT 学生制約3 CHECK(成績　<= 100)
)

1.2. 外部キーの更新規則と削除規則

以前話扱った参照制約の話に関係します．

外部キーの参照先の表の行が更新・削除されたときに，外部キー側(参照元)ではどうするかという話をここでは扱います．

以下の4つのパターンがあり，ON UPDATE(更新時)と，ON DELETE(削除時)に続けて指定します．

・NO ACTION

参照先のある行を更新・削除したとき，参照元側に参照先の対象の行と同じ値を持つ行があったとき，更新・削除は拒否されます．

例4

CREATE TABLE 学生(

学部表(参照先)の学部コード02の行を削除しようとすると，学生表(参照元)にも学部コード02の行があるため，学部表の学部コード02の行の削除は拒否されます．

なお，以下青字は主キーを表します．

f:id:chankilu23:20210114110152j:plain

・CASCADE

参照先のある行を更新・削除したとき，参照元側に参照先の対象の行と同じ値を持つ行があったとき，更新・削除を許可し，参照元側も同様に更新・削除をします．

例5

CREATE TABLE 学生(

①学部表(参照先)で学部コード02の行を削除しようとします．

②学生表(参照元)にも学部コード02の行があるので，こちらも削除します．

f:id:chankilu23:20210114110239j:plain

・SET DEFAULT

参照先のある行を削除しようとしたとき，参照先の削除を許可し，参照元側に参照先の主キーの値と同じ行があれば，デフォルト値で更新します．

例6

CREATE TABLE 学生(

①学部表(参照先)で学部コード02の行を削除しようとします．

②学生表(参照元)にも学部コード02の行があるので，その行の学部コードをデフォルト値に設定します．

(ここでは，00をデフォルトにしました)

f:id:chankilu23:20210114110335j:plain

・SET NULL

参照先のある行を削除しようとしたとき，参照先の削除を許可し，参照元側に参照先の主キーの値と同じ行があれば，NULL値で更新します．

例7

CREATE TABLE 学生(

①学部表(参照先)で学部コード02の行を削除しようとします．

②学生表(参照元)にも学部コード02の行があるので，その行の学部コードをNULL値に設定します．

f:id:chankilu23:20210114110354j:plain

1.3. ビュー

ビューは簡単に言うとSELECT文に別の名前をつけたもので，一時的に作成される仮想的な表です．

主なメリットは以下の2つです．

・複雑なクエリ(コマンドのようなもの)をあらかじめビューとして作成することで，何度も実行するときに大幅に効率が良くなる．

・アクセス権を設定できるため，セキュリティ向上につながる．

ビューを作るコマンドは以下のCREATE VIEWの構文に沿って行われます．

CREATE VIEW "ビュー名" ("列名1", "列名2", ...)

AS SELECT "列名A", "列名B", ...

FROM "表名1", "表名2", ...
WHERE "条件"

SELECTで指定した"列名A", "列名B", ...の値がそれぞれ"列名1", "列名2", ...に順次格納されます．

以下に例と対応する表(表5)を示します．

例8

CREATE VIEW 学部別学生一覧(学部コード，学部，学籍番号，氏名，)

AS SELECT 学部コード，学部，学籍番号，氏名

FROM 社員，部門
WHERE 学生.学部コード=学部.学部コード
ORDER BY 学部.学部コード，学生番号

f:id:chankilu23:20210114163557j:plain

上で作成したビューを用いて，学部コードが03の行を抽出する例と対応する表(表6)を示します．

例9

SELECT *

FROM 学部別学生一覧
WHERE 学部コード=03

f:id:chankilu23:20210114163616j:plain

このSELECT文を実行するには，学部別学生一覧に対するSELECT権限を持っている必要があります．

なお，学生表および学部表に対するSELECT権限は無くてもOKです．

(権限の話は後述します)

1.3.2. ビューの更新

1.3.1.で触れたように，ビューは複雑なクエリなどをまとめるようにして作成した仮想的な表ですが，実は条件を満たせばビューを用いて元の表の更新ができます．

条件とは例えば以下のものです．

SELECTの後に集合関数を含まない．

DISTINCTを含まない．

HAVINGやGROUP BYを含まない．

ただし，上記意外にも例外はあります．

1.4. アクセス権付与

ここでは表やビューへのアクセス権を扱います．

表，ビューの作成者(オーナという)は，作成した表やビューに関する参照，挿入，削除，更新などの権限を全て持っています．

オーナは，表やビューに関する諸権限を他のユーザに付与したり取り消したりできます．

そこで使うコマンドが，GRANTとREVOKEです．

1.4.1. GRANT

GRANTは以下のような構文で，他のユーザにアクセス権を付与できます．

GRANT "権限範囲" ON "表名" TO "ユーザ1", "ユーザ2", ... $\hspace{0.5cm}$ (WITH GRANT OPTION)

WITH GRANT OPTIONをつけると，指定したユーザが，指定した"権限範囲"に関する権限を他のユーザに付与できるようになります．

権限範囲は主に以下のようなものがあります．

上記の構文に沿って具体的に示していきます．

SELECT

行の参照権限を指定したユーザに付与します．

以下の例では，木村に学生表に関する参照権限を付与します．

例10

GRANT SELECT ON 学生 TO 木村

INSERT

行の挿入権限を指定したユーザに付与します．

なお， INSERTの後に("列名1", "列名2", ...)をつけると，"列名1", "列名2", ...のみの挿入権限を付与できます．

以下の例では，任意のユーザに学生表に関する挿入権限を付与します．

(PUBLICを指定すると，任意のユーザを指定できます)

例11

GRANT INSERT ON 学生 TO PUBLIC

DELETE

行の削除権限を指定したユーザに付与します．

例は省略します．上と同じです．

UPDATE

行の更新権限を指定したユーザに付与します．

なお， UPDATEの後に("列名1", "列名2", ...)をつけると，"列名1", "列名2", ...のみの更新権限を付与できます．

以下の例では，木村に学生表に関する更新権限を付与します．

また，木村に「学生表に関する更新権限を付与する」権限を付与します．

(つまり，木村は学生表に関するUPDATEのみ，他のユーザにGRANTできます)

例12

GRANT UPDATE ON 学生 TO 木村

REFERENCES ("列名1", "列名2", ...)

CREATE TABLE文において，主キーの"列名1", "列名2", ...に対する外部キーを設定する権限を付与します．

以下の例では，佐藤に学部表の主キーである学部コードの外部キー設定の権限を付与します．

例13

GRANT REFERENCES(学部コード) ON 学部 TO 佐藤

つまり，佐藤は例えば次のようなCREATE TABLE文を実行できます．

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4),
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3),
学部コード $\hspace{0.1cm}$ DEC(3),
PRIMARY KEY(学籍番号),
FOREIGN KEY(学部コード) REFERENCES (学部)
)

ALL PRIVILEGES

上で紹介した全ての権限を付与します．

1.4.2. REVOKE

REVOKEは以下のような構文で，他のユーザのアクセス権を取り消せます．

REVOKE "権限範囲" ON "表名" FROM "ユーザ1", "ユーザ2", ...

権限範囲は1.4.1.のGRANTと同様なので省略します．

以下の例では，木村に付与した学生表に関するINSERTとDELETEの権限を取り消します．

例14

REVOKE INSERT, DELETE ON 学生 FROM 木村

1.4.3. CREATE ROLE

ロール(role:役割)が割り当てられたユーザにまとめて権限を付与します．

ロールに関する構文は以下です．

・ロールの作成
CREATE ROLE "ロール名"
・ロールへの権限付与
GRANT "権限範囲" ON "表名" TO "ロール名"
・ロールをユーザに割り当て
GRANT "ロール名" TO "ユーザ1", "ユーザ2", ...

トップへ

2. 結合

ここでは，複数の表を1つの表にまとめる結合という操作を扱います．

結合には，内部結合，外部結合，自己結合の3種類があります．

2.1. 内部結合 INNER JOIN

FROM句でまずは結合する2つの表を指定します．

さらに，それぞれの表の列を指定し，列の値が一致する行を抽出し，1つの表に結合します．

基本的な構文は以下です．

SELECT "列名1", "列名2", 集合関数, ...
$\hspace{1cm}$ FROM "表名1" INNER JOIN "表名2"
$\hspace{1cm}$ ON "列名1" = "列名2"
$\hspace{1cm}$ WHERE "抽出条件

以下は，学生表と学部表を，学部コードで内部結合する例です．

例15(表7参照)

SELECT *

FROM 学生 A INNER JOIN 学部 B
ON A.学部コード=B.学部コード

f:id:chankilu23:20210124112037j:plain

実は，内部結合は以下のようにWHEREを使ったSQL文に書き換えることが可能です．

SELECT *
$\hspace{0.5cm}$ FROM 学生 A，学部 B
$\hspace{0.5cm}$ WHERE A.学部コード=B.学部コード

また，以下のように，INNER JOINの前にNATURALをつけると，学部コードは一度しか現れません．

例16(表8参照)

SELECT *

FROM 学生 NATURAL INNER JOIN 学部

f:id:chankilu23:20210124112055j:plain

2.2. 外部結合

外部結合は，基準となる表側の値は必ず結果表に含む結合です．

結合条件に一致しない場合でも基準の表の値は結果表に含まれます．

外部結合には，左外部結合，右外部結合，全外部結合があります．

外部結合の構文は以下のとおりです．

SELECT "列名1", "列名2", ... $\hspace{0.5cm}$ FROM "表名1" "外部結合指定" "表名2" $\hspace{0.5cm}$ ON "列名1" = "列名2" $\hspace{0.5cm}$ WHERE "抽出条件"

外部結合指定というのが上で示した左外部結合，右外部結合，全外部結合です．

それぞれ記法と共に見ていきます．

2.2.1. 左外部結合

以下は，学生表を基準に，学部表と左外部結合する例です．

例17(表9参照)

SELECT *

FROM 学生 A LEFT OUTER JOIN 学部 B
ON A.学部コード=B.学部コード

f:id:chankilu23:20210128111416j:plain

2.2.2. 右外部結合

以下は，学部表を基準に，学生表と右外部結合する例です．

例18(表10参照)

SELECT *

FROM 学生 A RIGHT OUTER JOIN 学部 B

f:id:chankilu23:20210128111433j:plain

2.2.3. 全外部結合

以下の例は，学生表と学部表を基準に，全外部結合する例です．

例19(表11参照)

SELECT *

FROM 学生 A FULL OUTER JOIN 学部 B

f:id:chankilu23:20210128111449j:plain

2.3. 自己結合

自己結合は，そこまで深く触れません．

自己結合とは，1つの表に異なる相関名を2つつけることで別の表として扱い，その2つの表を1つに結合することです．

例えば，以下のように，大学ー学部ー学科ー学生，という関係を考えます．

f:id:chankilu23:20210128132030j:plain

これを1つの表に，例えば次のように"大学内階層"として表すことができます．

f:id:chankilu23:20210128132346j:plain

大学内階層表に相関名X, Yをつけると，X.上位コードとY.コードが等しい行の一部は以下の色付き線のようになります．

これが自己結合です．

f:id:chankilu23:20210128132708j:plain

2.4. その他の結合

UNION JOIN

UNION JOINでは，それぞれの表の全ての行を新しい表に追加し，相手の列は問答無用でNULLにします．

以下は，学生表と学部表をUNION JOINする例です．

例20(表15参照)

SELECT *

FROM 学生 A UNION JOIN 学部 B

f:id:chankilu23:20210128193057j:plain

CROSS JOIN

CROSS JOINでは，2つの表の全ての行と列の組み合わせを作ります．

いわゆる直積演算をすることと同じになります．

以下は，学生表と学部表をCROSS JOINする例です．

例21(表16参照)

SELECT *

FROM 学生 A CROSS JOIN 学部 B

f:id:chankilu23:20210128193112j:plain

トップへ

3.副問合せ

実は，WHERE句の中にSELECT文を書くことができます．

このとき，SELECT文が二重になることがわかると思いますが，内側のSELECT文以降を副問合せといいます．

外側の問合わせを主問合せといいます．

下の図(?)において，赤字が主問合せ，青字が副問合せです．

SELECT "列名1", "列名2", ...

FROM "表名1", "表名2", ...
WHERE "抽出条件" (SELECT "列名A"

FROM "表名a", "表名b", ...
WHERE "抽出条件", "結合条件")

副問合せのSELECT文が1つの値を返すか，複数の値を返すかで少し主問い合わせの書き方が違います．

具体例と一緒に確認してみます．

3.1. 副問合せが1つの値を返すとき

以下は，副問合せが1つの値を返し，それを主問合せに渡す例です．

副問合せで学生の成績の平均を返し，主問合せで成績が平均以上の学生の表を抽出します．

例22(表17参照)

SELECT *

FROM 学生 WHERE 成績 >= (SELECT AVG(成績)

FROM 学生)

これは，副問合せでの実行結果が平均の63.83であることより，主問合せが以下のようになると解釈すると見通しが良くなります．

SELECT *

FROM 学生 WHERE 成績 >= 63.83

f:id:chankilu23:20210130141202j:plain

3.2.副問合せが複数の値を返すとき

以下は，副問合せが複数の値を返し，それを主問合せに渡す例です．

副問合せで成績が60以上の学生の学籍番号を返し，主問合せで成績が60以上の学生かつ学部コードが工学部である学生の行を抽出します．

(もっとも，これは副問合わせをせずとも，より簡単なSQL文で書けますが...)

例23(表18参照)

SELECT *

FROM 学生 WHERE 学部コード = 03 AND
学籍番号 IN (SELECT 学籍番号

FROM 学生
WHERE 成績 >= 60)

これは，副問合せでの実行結果が(0002, 0003, 0005)であることより，主問合せが以下のようになると解釈すると見通しが良くなります．

SELECT *

FROM 学生
WHERE 学部コード = 03 AND
学籍番号　IN (0002, 0003, 0005)

f:id:chankilu23:20210130141216j:plain

3.3.相関副問合せ

副問合せのうち，主問合せのFROM句で指定した表を副問合せで使っているとき，この問合わせを相関副問合せといいます．(下図の赤字部分が該当)

相関副問合せのうち，主問合せのWHERE句でEXITS またはNOT EXISTが指定されているときはいわゆる二重ループの処理を行うことになります．

SELECT "列名1", "列名2", ...

FROM "表名1"
WHERE EXIST ( SELECT "列名A", "列名B", ...

FROM "表名2"
WHERE "表名1"を含む条件式

C言語っぽい擬似コードで一般的に相関問合せの処理手順を書いてみます．

for(i=0; i<"表名1"の行数; i++){

for(j=0; j<"表名2"の行数; j++){

if(副問合せのWHERE句が真){

if(主問合わせのWHERE句が真){
"表名1"のi行目を抽出
}

}

3行目のif文の中身，副問合せのWHERE句は，"表名1"のi行目と"表名2"のj行目に関する条件式です．

簡単に言うと，"表名1"の全ての行と"表名2"の全ての行を総当たり的にWHERE句を満たすかを確かめます．

以下は，少なくとも1つ評定が6未満の講義がある学生を抽出する例です．

例24(表19参照)

SELECT *

FROM 学生 X
WHERE EXISTS (SELECT 学籍番号

FROM 評定 Y
WHERE Y.評定 < 06 AND X.学籍番号 = Y.学籍番号

f:id:chankilu23:20210131194343j:plain

学生表が3行，評定表が8行あるので，3 $\times$ 8=24回の操作が行われることになります．

まずは学生表の1行目，0001の木村について見るので，SQL文最終行について，X.学籍番号=0001になります．

つまり，WHERE句内は

Y.評定 < 06 AND 0001 = Y.学籍番号

になりますが，これを満たす行は評定表にはありません．

よって，EXISTSには引っかからず，0001の木村は抽出されません．

同様に考えると，0002の佐藤については評定表の6, 8行目が該当します．

0003の田中については評定表の2行目が該当します．

以上より，出力結果は表19のようになります．

トップへ

これでSQLの説明が一通り終わりました．表を作るのがとても時間がかかった...

次はトランザクションの話です．

参考：金子則彦「データベーススペシャリスト合格教本」

2021-01-05

SQL <前編>

データベース

SQL

前回はデータベースの正規化の話をしましたが，次はSQLの話を2つの記事に分けてしていきます．

前半の記事では，SQLの基本的な構文を紹介します．

SQL(Structured Query Language)は，データベースの定義や操作を実行するために用いられます．

1. SELECT

SELECTの後に列名を指定することでその列のデータのみがその順番通りに抽出されます．

ちなみに，このように指定した列名のデータのみを抽出することを射影といいます．

1.1. SELECT "列名1"，"列名2"，...

"列名1", "列名2", ...と複数の列を抽出できます．

例1(表1参照)

SELECT 学籍番号，氏名

FROM 学生

f:id:chankilu23:20201231141452j:plain

1.2. SELECT "列名" AS "新列名"

"列名"のデータを，"新列名"を新たな列名として抽出します．

例2(表2参照)

SELECT 学籍番号，氏名 AS 名前

FROM 学生

f:id:chankilu23:20201231141750j:plain

1.3. SELECT *

FROM句で指定したテーブルの全ての列が抽出されます．

例3(表3参照)

SELECT *

FROM 学生

f:id:chankilu23:20201231141805j:plain

1.4. SELECT * FROM "表名1", "表名2", ...

SELECTで*と指定し，FROM句で表を指定すると，指定した表の値の全ての組み合わせが抽出されます．

これを直積といいます．

例4(表4参照)

SELECT *

FROM 学生，学部

f:id:chankilu23:20210102121318j:plain

目次へ

2. DISTINCT

distinctとは，ここでは「区別ができる」というような意味で，DISTINCTは射影したときに行を重複させずに出力します．

DISTINCTがないとどうなるのか見てみます．

例5(表5参照)

SELECT 所属サークル

FROM 学生

f:id:chankilu23:20210101174725j:plain

赤字で示したように，軽音とダンスが重複して表示されています．

一方で，SELECTの直後でDISTINCTを指定してみます．

例6(表6参照)

SELECT DISTINCT 所属サークル

FROM 学生

f:id:chankilu23:20210101174741j:plain

重複していた軽音とダンスは $1$ 行しか表示されていません．

トップへ

3. 集合関数

3.1. MAX, MIN

それぞれ最大値(maximum)と最小値(minimum)を求めます．

SELECTの後にMAX（"列名"）を指定すると，"列名"の値における最大値が得られます．

MIN（"列名"）を指定すると，"列名"の値における最小値が得られます．

先ほどのSELECT ASと合わせることで以下のようになります．

例7(表7参照)

SELECT MAX(成績) AS 成績の最大，MIN(成績) AS 成績の最小

FROM 学生

f:id:chankilu23:20210102123043j:plain

3.2. AVG

平均(average)を求めます．

NULLがあった(値が登録されていない)ときはその行は無いものとして計算します．

下の例では，

$\dfrac{48+65+73+99+54+44}{6}$

を計算します．

例8(表8参照)

SELECT AVG(成績) AS 成績の平均

FROM 学生

f:id:chankilu23:20210102123127j:plain

3.3. SUM

和(summation)を求めます．

NULLがあったときはAVGと同様にその行は無いものとして計算します．

例9(表9参照)

SELECT SUM(成績) AS 成績の和

FROM 学生

f:id:chankilu23:20210102123139j:plain

3.4. COUNT

行の数を求めます(count)．

COUNT(*)と指定すると，全ての行数がカウントされます．

COUNT("列名")と指定すると，"列名"のNULL以外がカウントされます．

例えば，体調不良などで試験を欠席した人以外を数えるときは以下のようにするといいでしょう．

例10(表10参照)

SELECT COUNT(*) AS 合計人数，COUNT(成績) AS 受験人数

FROM 学生

f:id:chankilu23:20210102123150j:plain

トップへ

4. FROM

FROM句では，データを抽出するテーブル名であったりビュー名を指定します．

例4で見たようにFROM句では複数の表を指定できます．

また，相関名という概念があります．

相関名とは，テーブル名につけた別の名前のことです．

下の例では，ASで相関名をXとしています．

ただし，相関名を指定したときは，列名は"相関名.列名"で指定します．

例11(表11参照)

SELECT X.学籍番号，X.氏名

FROM 学生 AS X

f:id:chankilu23:20210103152906j:plain

トップへ

5. WHERE

WHERE句の後に条件を指定することによって，その条件を満たす行のみを抽出します．

条件に合う行を取り出すことを選択といいます．

条件には抽出条件と結合条件があります．

5.1. 抽出条件

抽出条件に用いる演算子として，比較演算子や論理演算子などがあります．

5.1.1. 比較演算子

比較演算子には6種類あり，それぞれ次のような意味を持っています．

f:id:chankilu23:20210103160800j:plain:w1500

ここでは，>=の例だけ挙げておきます．

例12(表12参照)

SELECT *

FROM 学生
WHERE 成績 >= 65

f:id:chankilu23:20210103203449j:plain

5.1.2. 論理演算子

AND演算子

WHEREの後に，"条件1" AND "条件2" と指定すると，"条件1"と"条件2"が共に真であるような行が抽出されます．

ただし，NULLのある行は無条件に抽出しません．

例13(表13参照)

SELECT *

FROM 学生
WHERE 学籍番号 < 0005 AND 成績 >= 65

f:id:chankilu23:20210104153117j:plain

OR演算子

WHEREの後に，"条件1" OR "条件2" と指定すると，"条件1"と"条件2"のうち少なくとも一方が真であるような行が抽出されます．

ただし，NULLのある行は無条件に抽出しません．

例14(表14参照)

SELECT *

FROM 学生
WHERE 学籍番号 < 0005 OR 成績 >= 65

f:id:chankilu23:20210104153134j:plain

NOT演算子

WHEREの後に，NOT "条件1" と指定すると，"条件1"が偽であるような行が抽出されます．

ただし，NULLのある行は無条件に抽出しません．

例15(表15参照)

SELECT *

FROM 学生
WHERE NOT 成績 = 65

f:id:chankilu23:20210104180132j:plain

5.1.3. その他

IN()演算子

WHEREの後に，"列名" IN ("値1", "値2", ....)と指定すると，"列名"が"値1", "値2", ...に一致するような行が抽出されます．

ただし，NULLのある行は無条件に抽出しません．

例16(表16参照)

SELECT *

FROM 学生
WHERE 成績 IN (44, 46, 48)

f:id:chankilu23:20210104162744j:plain

BETWEEN AND演算子

WHEREの後に，"列名" BETWEEN "値1" AND "値2" と指定すると，"列名"の値が"値1"以上"値2"以下であるような行が抽出されます．

例17(表17参照)

SELECT *

FROM 学生
WHERE 成績 BETWEEN 48 AND 72

f:id:chankilu23:20210104175435j:plain

IS NULL演算子

WHEREの後に，"列名" IS NULLと指定すると，"列名"の値がNULLであるような行が抽出されます．

例18(表18参照)

SELECT *

FROM 学生
WHERE 成績 IS NULL

f:id:chankilu23:20210104175448j:plain

IS NOT NULL演算子

WHEREの後に，"列名" IS NOT NULLと指定すると"列名"の値がNULLでないような行が抽出されます．

例19(表19参照)

SELECT *

FROM 学生
WHERE 成績 IS NOT NULL

f:id:chankilu23:20210104175500j:plain

LIKE演算子

文字列の検索に使います．

WHEREの後に，"列名" '文字列検索条件'と指定すると，"列名"の値がその文字列検索条件を満たす行を抽出します．

文字列検索条件に用いるのは

%：0文字以上の任意の文字列

_：1文字以上の任意の文字列

があります．

以下に4つ例を出します．

例20 前方一致検索 (表20参照)

SELECT *

FROM 学生
WHERE 氏名 LIKE '中%'

f:id:chankilu23:20210105102649j:plain

例21 後方一致検索 (表21参照)

SELECT *

FROM 学生
WHERE 氏名 LIKE '%花'

f:id:chankilu23:20210105104353j:plain

例22 中間一致検索1 (表22参照)

SELECT *

FROM 学生
WHERE 氏名 LIKE '%中%'

%は任意の0文字分なので，この例22では先頭が"中"でもOKです．

f:id:chankilu23:20210105104414j:plain

例23 中間一致検索2 (表23参照)

SELECT *

FROM 学生
WHERE 氏名 LIKE '_中%%'

アンダースコア"_"は任意の1文字分なので，この例23では2文字目が"中"である，という条件であることに注意します．

f:id:chankilu23:20210107165029j:plain

5.2 結合条件

結合条件とは，指定された2つの列の値が同じ行を抽出する条件です．

例24(表24参照)

SELECT *

FROM 学生，学部
WHERE 所属学部コード = 学部コード

f:id:chankilu23:20210105153216j:plain

下の例25のように，違う表で列名が同じ場合は，"表名.列名"のように指定します．

例25(表25参照)

SELECT *

FROM 学生，学部
WHERE 学生.学部コード = 学部.学部コード

f:id:chankilu23:20210105153236j:plain

下の例26のように，相関名を用いることもできます．

例26(表26参照)

SELECT *

FROM 学生 A，学部 B
WHERE A.学部コード = B.学部コード

f:id:chankilu23:20210105153256j:plain

トップへ

6. GROUP BY

GROUP BY句は，指定した列の値が同じ行をグループ化します．

普通は集合関数と一緒に使います．

また制約があり，SELECTの後に指定する項目は

・GROUP BY句の後にした列名

・集合関数

のどちらかでないといけません．

下の例27は，学部ごとの成績の平均を抽出したものです．

例27(表27参照)

SELECT 学部名 AVG(成績)

FROM 学生，学部
WHERE 学生.学部コード = 学部.学部コード
GROUP BY 学部名

f:id:chankilu23:20210105153732j:plain

トップへ

7. HAVING

HAVING句では，GROUP BY句でグループ化した後の行に対する条件を指定します．

また制約があり，HAVINGの後に指定する抽出条件は

・GROUP BY句で指定した列名

・集合関数

のどちらかでないといけません．

例28(表28参照)

SELECT 学部名 AVG(成績)

FROM 学生，学部
WHERE 学生.学部コード = 学部.学部コード
GROUP BY 学部名
HAVING AVG(成績) > 80

f:id:chankilu23:20210105161038j:plain

トップへ

8. ORDER BY

ORDER BY句は，指定した列に関して，抽出した行を昇順または降順にソート(並び替え)します．

昇順ではASC(ascending)を指定し，降順ではDESC(descending)を指定します．

何も指定しない場合は，ASCがデフォルトで指定されます．

複数の列に関してソートすることも可能で，優先順位の高い順に

ORDER BY "列名1", "列名2", ...

とすればよいです．

例29(表29参照)

SELECT *

FROM 学生
ORDER BY 学部コード，成績

f:id:chankilu23:20210107145755j:plain

ORDER BY の後に整数 $n$ を指定すると，SELECTで指定した列のうち $n$ 番目の列を指定することができます．

例30(表30参照)

SELECT 学部コード，AVG(成績)

FROM 学生
GROUP BY 学部コード
ORDER BY 2 DESC

f:id:chankilu23:20210107165715j:plain

トップへ

9．UNION，INTERSECT，EXCEPT

数学でいう和集合，共通部分，差集合に対応する演算子です．

これらの演算子を使うときには，SELECTで指定した表の列名や列の構成が同じである必要があります．

そこまで頻出ではないようなのでサラッと紹介だけします．

9.1. UNION

2つの表のうち，少なくとも一方に属する行を抽出します．

ALLを付けると，重複行は取り除かれません．

ALLを付けなければ，重複行は取り除かれます．

例31(表31参照)

SELECT * FROM 工学部生

UNION ALL

SELECT * FROM ダンス部

f:id:chankilu23:20210107154357j:plain

9.2 INTERSECT

2つの表に共に属する行を抽出します．

例32(表32参照)

SELECT * FROM 工学部生

INTERSECT

SELECT * FROM ダンス部

f:id:chankilu23:20210107154412j:plain

9.3 EXCEPT

先に指定した表に属する行のうち，後に指定した表に属する行を取り除きます．

例33(表33参照)

SELECT * FROM 工学部生

EXCEPT

SELECT * FROM ダンス部

f:id:chankilu23:20210107170025j:plain

基本的なSQLの構文を解説しました．

後半の記事ではデータ定義言語や副問合せなどの話をします．

~~ふぅ，疲れた．~~

参考：金子則彦「データベーススペシャリスト合格教本」，アイテックIT人材教育研究部(2020)「2021応用情報・高度共通　午前試験対策書」

2020-12-17

正規化

データベース

正規化

まずは用語をいくつか定義します．

表中のタプルを一意に決定することのできる属性を主キーといいます．

つまり，表中に主キーの値が同じ行が複数存在することはありません．複数存在すればタプルを一意に決定できないためです．

下の表1では，学籍番号が主キーになります．その他の属性の値を1つ決めてもタプルは一意に決まるとは限りません．

例えば，学部が農学部という指定をしたところで0004の田中さんと0007の宮地さんの2人がいて一意にタプルは定まっていません．

f:id:chankilu23:20201217130107j:plain

表中に主キーの役割を果たす属性が複数存在することもあって，これを主キーの候補という意味で候補キーといいます．

候補キーの中から主キーを1つ選びますが，選ばなかった候補キーのことを主キーの代わりができるぞという意味で代替キーといいます．

上の表1において，住所が同じ人がいないと仮定するなら住所も主キーになりえます．

つまり，学籍番号と住所は候補キーですが，主キーとして学籍番号を選ぶなら住所は代替キーです．

候補キーに求められる性質で，表中に同じ値を持つ行が複数存在しないというものをユニーク制約といいます．

また，主キーに求められる性質で，主キーの値にNULL値を持つことができないというものを非NULL制約といいます．

また，単独の属性でタプルを一意に決定できないときは複数の属性を組み合わせたものを候補キーとしますが，これを複合キーといいます．

ここで，候補キーには極小という性質が求められます．極小とは簡単に言えば必要最低限という意味です．

上の例で学籍番号と住所は候補キーでしたので，当然学籍番号と住所を組み合わせたものでもタプルを一意に決定できます．

しかしこれは候補キーの極小という性質を満たしていないので候補キーにはなりえません．

日本語が難しいですが，{学籍番号}や{住所}は候補キー，{学籍番号，住所}は候補キーでない，と書くと少しスッキリしそうです．

属性Aの値が決まると属性Bの値も1つに決まるとき，属性Bは属性Aに関数従属するといいます．

主キー以外の属性は必ず主キーに関数従属することがわかると思います．

上の表1では，例えば氏名は学籍番号に関数従属します．

外部キーとは，その値によって他の表のタプルを一意に決定できる属性のことです．

外部キーにおいては，参照先との間に参照制約という制約が発生します．

大まかには以下の2つの利点があります．

・参照先に存在しないような値を持つものは参照元の表に登録できない

・参照している行を参照先の行から削除できない

外部キーについては記事の最後で具体例を使って確認します．

第1正規化

第1正規化では繰り返し属性というのがキーワードになります．

繰り返し属性を持つとは，1つの属性が複数の値を持った状態を指し，下の表2のような状態です．

f:id:chankilu23:20201217130239j:plain

0004の田中さんと0006の橋本さんを見ると，所属サークルが繰り返し属性になっているのがわかります．

このような状態から，値ごとに行を分けて下表のような状態にする操作を第1正規化といいます．

f:id:chankilu23:20201217130324j:plain

そして，第1正規化を行い繰り返し属性を排除した表の形式を第1正規形といいます．

それに対し，繰り返し属性を持つような表の形式を非正規形といいます．

当然ですが，全ての表が非正規形であるとは限らず，初期状態が既に第1正規形である表もあります．

第2正規化

第2正規化では部分関数従属というのがキーワードです．

部分関数従属とは，主キーのうち一部分のみに関数従属することです．

表のうち，部分関数従属している属性のみを別の表に分割する操作を第2正規化といいます．

そして，第2正規化を行い主キーに部分関数従属する属性を排除した表の形式を第2正規形といいます．

上の表3では，主キーは{学籍番号，所属サークル}です．

学籍番号だけではタプルを一意に決定できないことに注意してください．

このとき，氏名，学部，学部コードは主キー{学籍番号，所属サークル}のうち学籍番号のみに関数従属しています．

これは第2正規形に反しているので，そこだけ別表に切り取り，下の表4のようになります．

f:id:chankilu23:20201217130413j:plain

第3正規化

第3正規化では推移的関数従属というのがキーワードです．

推移的関数従属とは，その名の通り関数従属の関係が推移的に辿れるものです．

例えば，上の表4では学籍番号が決まると学部が一意に決まります．そして学部が決まると学部コードも一意に決まります．

つまり，学籍番号 $\to$ 学部 $\to$ 学部コードの順に推移的な関数従属があることがわかります．

このような関係を別表に切り出して下の表5のようにすればOKです．

f:id:chankilu23:20201217130441j:plain

ここで，初めに紹介した外部キーを思い出してみましょう．

以下の表6のようにそれぞれエンティティ名をつけておきます．

f:id:chankilu23:20201217192503j:plain

学生エンティティでは学部が外部キーになります．

実際，学生エンティティの学部が1つ決まれば学部エンティティのタプルが一意に定まります．

さらに外部キーの参照制約を確認してみます．

学部エンティティにない法学部に所属する学生を学生エンティティに追加することはできません．

また，学部エンティティの工学部は学生エンティティで使用されているので，学部エンティティから工学部という値は削除できません．

正規化の話はこんなかんじです．

次はSQLの話です．

SQLはほとんど知識がないので更新にさらに時間がかかるかもしれないです．

参考：アイテックIT人材教育研究部(2020)「2021応用情報・高度共通　午前試験対策書」

2020-12-06

データモデル

データベース

これからはしばらくデータベースの話をまとめていきます．

データモデル

データベースには色んなデータが格納され，データ間で依存関係を持つものも存在します．

どんなデータが格納されているとか，このデータ間にはこんな関係がある，というのを図式化して整理したものをデータモデルといいます．

データモデルには，概念データモデルと論理データモデルがあります．

DBMS(DataBase Management System)の型を無視してデータ関係を表現するのが概念データモデル，DBMSの型を無視してデータ関係を表現するのが論理データモデルらしいです．(何言ってるかわからない)

概念データモデル

エンティティ(entity)と，エンティティ同士のリレーションシップ(relationship)によってデータやデータ集合の構造を表すモデルがE-Rモデルで，概念データモデルの一つです．

・エンティティ

データの項目を属性と呼び，属性の集合をエンティティと呼びます．

例えば，学籍番号，学生氏名，講義コード，講義名，講義区分，という5つの項目があるとすると，それぞれを属性といいます．

そして，{学籍番号，学生氏名}をまとめて「学生」，{講義コード，講義名，講義区分}をまとめて「講義」という情報として扱うとします．

新しくまとまりとして名前をつけたこの「学生」や「講義」がエンティティです．

・リレーションシップ

エンティティとして上に書いたような学生，講義があるとします．

そして学生と講義の間に，学生が講義を「履修する」という関係があったとき，この「履修する」というのがリレーションシップです．

リレーションシップは一つとは限らず，例えば学生と講義の間の「単位を取得した」とかもリレーションシップになりそうです．

さらに，各属性に具体的な値を持たせたものをインスタンスといいます．

例えば上の学生と講義のエンティティにおいて，(0001, アリス)や(1000, ボブ)は学生エンティティのインスタンスです．

同様に，(07, 英語基礎, 一般教養)や(85, データベース論, 専門科目)は講義エンティティのインスタンスです．

E-R図

E-Rモデルを図として表したのがE-R図です．

最近ではバックマン線図やUMLクラス図が使われているみたいです．

(UML：Unified Modeling Language)

この2つは後述します．

さらにエンティティ間のリレーションシップに関してカーディナリティという概念があります．

カーディナリティは多重度とも言われ，その名の通りリレーションシップにおける対応が多重になっているかどうかです．

先ほどの学生と講義のエンティティにおいて，1人の学生は普通は多数の講義を履修しますし，1つの講義は普通多数の学生から履修されます．

よって「履修する」というリレーションシップの多重度は多対多です．

次に，教員エンティティ{教員番号，教員氏名}と講義エンティティのリレーションシップ「担当する」を考えます．

1つの講義は1人の教員によって担当されるとします．

このとき，1人の教員は複数の講義を担当することはあるかもしれませんが，1つの講義は1人の教員に担当されます．

よって「担当する」の多重度は1対多です．

・バックマン線図

1対多という多重度において，「1」を直線，「多」を矢印で表します．(下図参照)

シンプルで図はごちゃごちゃしませんが，ぱっと見で意味がわかりにくいという特徴があります．

学生，講義，教員のエンティティ間のリレーションシップと多重度をバックマン線図で整理してみます．

f:id:chankilu23:20201205130835j:plain

・UMLクラス図

バックマン線図に比べて，「多」をより厳密に表現していて，多重度の最小値と最大値を明確に図中に示します．

例えば，最小で3，最大で5のときは「3...5」と書きます．

最小が1で，上限はないような場合は「1...*」と書きます．

学生，講義，教員のエンティティ間のリレーションシップと多重度をUMLクラス図で整理してみます．

f:id:chankilu23:20201205170844j:plain

この例では以下のことが読み取れます．

・1人の学生は5個以上20個以下の講義を履修している

・1つの講義は1人以上の学生が履修している

（1人の教員は0個以上の講義を担当している）

・1つの講義につき担当教員は1人である

3つ目はそれはそうってかんじなのでカッコにしておきました．

論理データモデル

論理データモデルには大きく階層型，ネットワーク型，関係型の3種類があります．

E-R図ではエンティティというものが出てきましたが，論理データモデルにおいてエンティティに対応するのはレコードとよびます．

階層型データモデル

レコード間のリレーションシップを親子関係で表すのが階層型の特徴です．

子レコードは親レコードを1つしか持てず，複数の親を持たせたいときはそれぞれの親に別の子としてレコードを追加する必要があります．

下の例では，子Bを親1と親3に2回登録しています．

子Eを飛ばしたのはミスではなくわざとです．(ほんまか??)

f:id:chankilu23:20201205202754j:plain

ネットワーク型データモデル

階層型の拡張で，子レコードは複数の親レコードを持つことができます．

上の階層型のモデルをネットワーク型で表すと下図のようになります．

赤線で示したように，1つの子レコードは複数の親レコードを持つことができます．

f:id:chankilu23:20201205203214j:plain

関係型データモデル

属性名とそれぞれの属性の値の組であるタプルの組み合わせとして表形式で関係を表したのが関係型データモデルで，最も広く利用されています．

下図のように表されます．

f:id:chankilu23:20201206112709j:plain

3層スキーマ

まずはスキーマの説明から．

データベースというからには，属性やそれらをまとめるレコードを定義する必要がありますし，データ達をどのようにディスク上に格納するかも決めなければいけません．

このようなデータベースの設計の仕方を記述したものをスキーマといいます．

データベースの規模が大きくなるにつれスキーマも複雑になるので，一部に変更があったときに他にも影響がどんどん出て修正する作業がとても大変になってしまいます．

そこでスキーマを独立性の高い3種類に分割することで，データベースを変更しても他への影響が最小限になるように工夫をしました．

これをデータ独立性などどいい，スキーマに上のようなデータ独立性を持たせたものが3層スキーマです．

3層スキーマでは，データベースを扱う立場によってスキーマを分割しています．

その立場とは，データ管理者，データベース管理者，利用者，の3種類です．

それぞれの立場におけるスキーマを，概念スキーマ，内部スキーマ，外部スキーマといいます．

順に説明していきます．

概念スキーマ

データ管理者(DA：Data Administrator)によって管理されます．

エンティティやリレーションなどの情報を持ち，データベースの全体像を定義するスキーマです．

内部スキーマ

データベース管理者(DBA： DataBase Administrator)によって管理されます．

効率よくデータを検索するためにデータベース中のデータをどのように格納するかなどを定義しています．

内部スキーマでのデータの独立性を物理的データ独立性といいます．

外部スキーマ

データベースにおいて，プログラムが必要とする情報のみを定義したものです．

例えば，元のデータベースに属性などの追加があった場合でも，その外部スキーマに影響がなければ外部スキーマは影響されません．

それぞれのユーザはデータベース自体から独立しており，これを論理的データ独立性といいます．

データモデルのまとめはこんなかんじです．

思ったよりボリュームが大きくなってしまった...

3層スキーマはわりと理解が浅いので日を改めて記事を更新するかもしれません．

次はデータベースの正規化の話です．

参考：アイテックIT人材教育研究部(2020)「2021応用情報・高度共通　午前試験対策書」

2020-12-04

システム構成<後編>

前回に引き続き，システム設計のお話です．

今回も文字ばかりになりますが...

3.信頼性設計

信頼性のあるシステムを設計するための考え方で，以下のようなものがあります．

(a)フェールセーフ (fail safe)

システムの一部に故障が生じたときには，できるだけ安全な状態を確保してシステムを停止させることです．

(b)フェールソフト (fail soft)

システムの一部に障害が生じたとき，その問題のあるシステムを切り離すなどして，全体の稼働は止めることなく残りの部分的なシステムで運転を続けることです．

なので故障が起きることを前提にシステムを組む必要があって，ある部分システムが故障したらそれを停止させたり切り離すことができるように，他の箇所に被害が及ばないような設計をする必要があります．

(c)フォールトトレラント (fault tolerant)

fault tolerantとは「障害に耐性」があるという意味で，信頼性設計に用いられる手法全般のことを言いますが，一般的にはもう少し狭い意味をさすようです．

システムの一部が故障しても，予備系のシステムに切り替えるなどして運転を引き続き行うことをフォールトトレラントということが多いらしい．

(d)フォールトアボイダンス (fault avoidance)

avoidance：回避

できるだけ故障が起きにくいようにシステムを設計します．

信頼性の高い部品を使ったり，構成をシンプルにしたり，十分にテストを通したりするなどの実現方法があります．

(e)フールプルーフ (fool proof)

システムの利用に関して不慣れなユーザに配慮したシステム設計のことです．

具体的には誤った使い方をしたときに，周囲に危険が及ばない設計，機器が破損しない設計，取り返しのつかない故障に繋がらない設計などのように組むことがあります．

もっというと，そもそも誤った使い方ができなかったり誤った使い方をしようとするとシステムが停止するような設計もあるみたいです．

4.マルチプロセッサ

一つのコンピュータに複数のプロセッサを搭載させることです．

命令などを複数のプロセッサで並列して処理できるのでシステム全体の処理能力が上がります．

マルチプロセッサではプロセッサ間で処理結果などを共有する必要がありますが，その共有の仕方の違いで密結合と疎結合の二種類に分類されます．

(a)密結合プロセッサ

1つのOSが複数のプロセッサを制御し，プロセッサ間で主記憶を共有します．

主記憶を共有するので，排他制御(長くなるので別記事で)と呼ばれる技術を実装する必要があります．

排他制御ではプロセッサを増やしすぎると逆に性能が落ちるという性質があるので，とりあえず増やしとけばええやろというわけにもいかないです．

(b)疎結合プロセッサ

各プロセッサはそれぞれ主記憶とOSを持ちます．

プロセッサ間を通信インタフェースなどで繋ぎ，全体を一つのシステムとして動かすことを疎結合といいます．

以前紹介したクラスタリングはこの疎結合マルチプロセッサに分類されます．

システム構成は終わりです．

次からはデータベース！

参考：アイテックIT人材教育研究部(2020)「2021応用情報・高度共通　午前試験対策書」