エクセルがともだち

会社で一番のともだちがエクセル・・・のアラフィフパート事務員のつれづれ帳。備忘録もかねてエクセルのことやVBAのことを書いていくつもりです。

【基本情報処理技術者】OSI参照モデルはアプセトデネブ

基本情報処理技術者の試験が迫っています。勉強するのがイヤで・・・申し込みしないといつまでたっても勉強しないな、と思ったのでえいやっと申し込んでしまった。今回は受かるつもりはなく(配点の高いアルゴリズムに手が及んでいない)肩慣らしとして・・・(有料のかたならし)。カタカナ語や英単語系の言葉も覚えられないけどそれ以前に国語と算数が・・・。午後問題の長い文章を読む読解力(および忍耐力)がない。小数点の割り算とかやり方を忘れている。いやそれ以前に九九があやしい。計算は電卓、エクセルがあるから普段自力でやらないんだよなぁ。

それはさておき、OSI参照モデルというものがなんのことやらさっぱり理解できなかったので、理解するようによちよち調べてみたので自分の覚書です。

自分が理解できるように低次元の言語に置き換えているので、これを読んでもなかみは信用しないでください。。。

 

OSI参照モデルとは

ネットワークを通じてコンピューター同士がやり取りするための約束事を「(通信)プロトコル」という。様々さ種類のプロトコルがあるのでそれを7階層に整理したものが「OSI参照モデル

 

プロトコル」ってなんなん?というところでなじめず混乱。

プロトコル(protocol) とはお約束事のこと。

IT関係で「プロトコル」と出てきたら、「通信するうえでのお約束事」つまり「通信プロトコル」ということを指すと思えばいいようです。

「メールを送ったりするときはこうゆうふうにすることにしようね」とか

「LANケーブルをさすところはこうゆう形にして、こうゆう仕組みで電気信号を送ることにしようね」とか決めておくと、パソコン屋さんとかケーブルを作る屋さんとかネットワーク事業者さんとかシステムを作る屋さんとかが、かってに独自のものをつくったりせず、迷わずいろいろできて便利、みたいな感じでしょうか。

階層毎に独自性を高めておくと、たとえば3階層のネットワーク層の部分をごちょごちょししたくなったばあい、1階層の部分とかあんまり影響ないので、ほかの階層への影響をあれこれ心配せず、おもいっきりごちょごちょできる・・・みたいな感じでしょうか。

 

むかし、コンピュータ間通信はメーカー独自仕様が乱立していて互いに通信することが大変だったので、1977年にISO(国際標準化機構)が標準的なネットワークとしてOSI(Open Systems Interconnection)を作成することとなったそうです。で、開発に先立って必要な機能を整理するためOSI参照モデルが策定されたそうです。

 

ああ。なんとなくなっとく。

 

OSI参照モデルは7階層あり

上位層から

7階層 アプリケーション層

6階層 プレゼンテーション層

5階層 セッション層

4階層 トランスポート層

3階層 ネットワーク層

2階層 データリンク層

1階層 物理層

階層はレイヤーともいうそうで、レイヤー1とかいうと1階層のこと、レイヤー2というと2階層のことらしいです。レイヤーって髪型のレイヤーカット(段カット・・・古いか)段々のことらしいです。

上から頭の文字を一文字ずつ読むと「アプセトデネブ」。これで覚えるとよいみたいです。うん、覚えたかも。

 

こんどは下位層から

1階層 物理層

物理的にどうつなぐか。LANケーブルとか、光ケーブルとか、Wi-Fiとかそのあたりのこと。LANケーブルやWi-Fiなどに関するお約束事。LANケーブルやリピータ、NIC(Network Interface Card)がこの層らしい。

NICって懐かしい。むかーし、LANカードをビックカメラで買ってきてノートパソコンにさした記憶がある。これがないとLANケーブルがさせなかった。もう、内蔵していないパソコンなんてないよねー。

 

2階層 データリンク層

同一ネットワーク内でどう通信するか。直接的に接続されたネットワーク機器間の信号の処理についてのお約束事。

MACアドレスを使うらしい。MACアドレスは機械固有の認識番号。メーカーで作られるときに割り振られる製造メーカ番号と製造番号で世界中で重複することのない番号だそうです。

機器はスイッチやブリッジ、ハブがこの層を担当しているらしい。

 

3階層 ネットワーク層

ネットワークとネットワークをどう中継するか。お隣の機器ではなく離れたところにあるLAN(ローカルエリアネットワーク)同士をつなぐ感じの部分?

IPアドレスを使うらしい。機器はルーターが担当するところらしい。

 

4階層 トランスポート層

通信の信頼性はどう確保するか。聞いたことのあるような、TCPUDPというものがトランスポート層のお約束事(プロトコル)になるらしい。

TCPは信頼性が高い方のプロトコル。データを送った後「ちゃんと受け取った?」と確認しながら通信する。受け取ったか確認が取れなかったらまた送る。

UDPは相手にデータが届いたかどうか確認せずに通信するプロトコル。確認しないので高速。映像配信サービスとかは多少コマがかけても速度重視でこっちらしい。

機器はゲートウェイというものが担当しているらしい。ネットワーク双方で使っているプロトコルの差異をこの機器が変換、吸収してくれるみたい。ゲートウェイは4階層だけでなく、4階層以上にかかわるみたい。

携帯メールとパソコンメールがお互いにやり取りできるもの、この機械が頑張ってくれているおかげらしい。

 

5階層 セッション層

通信の開始から終了までをどう管理するか。

パソコンとサーバーの会話(通信)の始めと終わりの管理とか?に関するお約束事?

 

6階層 プレゼンテーション層

データはどんな形式にするか。文字コード等のデータの表現形式のあたりのお約束事?文字コードの違いなどをなんとかしてくれているみたいな感じ?

 

7階層 アプリケーション層

どんなサービスを提供するのか。アプリケーション毎の固有の規定。

 

5階層以上はよくわからんでした。

実際のところはOIS参照モデルではなく「TCP/IPモデル」がスタンダートとなっていて、TCP/IPモデルでは「5~7階層はいっしょでよくね?」とその部分の階層は一緒になってるようです。

 

せいぜい私には、LANケーブルくらいしか理解できない、ということがわかりました。。。

 

参考とさせていただいた書籍(階層の解説を引用させていただいています)

きたみりゅうじさんの書籍「基本情報技術者

 

 

 

 

 

 

 

 

 

 

 

 

 

【VBA】Val関数の落とし穴にはまった~Val関数はカンマ付テキストはカンマの前までしか数値を返さない

VBAをやっていて、Val関数の落とし穴にはまったので備忘録として記載。

 

今回の事象

①テキストファイルからデータを読み込んでエクセルに出力していく

②テキストファイルの内容は

 3月  1,234円

 4月  5,879円

 とかそんな感じ

③文字位置からエクセルに

 f:id:mwke:20210221142908p:plain

 みたいな感じで出力しようとしていた

④ところがこうなった

 f:id:mwke:20210221143015p:plain

 

「VAL関数は数値として認識できる部分までを数値として返す」とのことでした。なのでカンマの部分で「これ知らん。あっしの仕事はここまで!」と引き上げてしまい、返ってきた数値が千の位より左側だけとなった。

文字列情報に「¥」が入っていても「これ知らん」となるようで「¥300」は頭の部分で「わしゃ知らん」となるので「0」と返ってくるそうです。

 

対処法としては

①Replace関数で「,」や「¥」を取り除いてからVal関数をかける

②VALの代わりにCLNGやCINT関数を使う

等があるようです。

 

サンプルコード

Sub val関数()

Range("B2").Value = Val(Range("A2").Value)
Range("B3").Value = Val(Range("A3").Value)

Range("C2").Value = CLng(Range("A2").Value)
Range("C3").Value = CLng(Range("A3").Value)

Range("D2").Value = Val(Replace(Range("A2").Value, ",", ""))
Range("D3").Value = Val(Replace(Range("A3").Value, "\", ""))

End Sub

 

結果

f:id:mwke:20210221143744p:plain

 

たしか半年前くらいにaccessVBAでテキストインポートの時も同じことにはまった記憶がよみがえってきた。二回目だし、ブログにも書いたから忘れない。。。といいな。

 

 

【Excel/関数】小技~負の時刻を計算・表示する方法

エクセルの時刻形式では負の時刻は「そんなもんねんよ」とエラーになってしまう。だがしかし、勤務時間の前月との差を出してみたり、マイナスも表現したいときがある。

f:id:mwke:20210214104141p:plain

 

◆関数で負の時間を表示する方法

IF関数と、ABS関数と、TEXT関数を使うと時間表記でマイナスを表示することができます。

ABS関数は数値の絶対値を返す関数。(例えば ▲0.5→0.5、0.5→0.5)

TEXT関数はこの値をこの書式で表示してねと、指定できる関数。セルの書式設定と違って数値は「文字列」になるのでその後計算などには使用できない。

詳しくはこちらが分かりやすかったです。

TEXT関数の使い方(表示形式に応じた文字列を返す):Excel関数 (dti.ne.jp)

 

E列に式を入れてみました。

f:id:mwke:20210214111857p:plain
=IF(C3<B3,TEXT(ABS(C3-B3),”▲h:mm:ss",TEXT(C3-B3,"h:mm:ss"))

まず、差はC列からB列を引いて出しています。

C3<B3 つまりC3の方が小さい場合は負の値になります。

その場合はABSでC3-B3からマイナスを取り去った絶対値を出して書式をh:mm:ssにマイナスをつけたものにしています。なお、ここではマイナスを「▲」で表していますがもちろん「-」でも大丈夫です。

C3<B3 が偽の場合(つまりC3の方が大きい)はC3-B3がマイナスにならないのでそのまま"h:mm:ss"とします。

 

◆負の時間を計算・表示する方法

これで見た目は大丈夫になりました。

ただ、このE列は文字列なのでE列の値を使って計算をすることはできません。

「差」の平均を出したくなったとします。

もともとのシリアル値のD列で平均を出しました。しかし平均がマイナスだったため#####になってしまいました。

なので先ほどの式をちょっと修正して、E列にマイナスの時間が表示されるようにしました。

f:id:mwke:20210214113652p:plain

D7の値が0より小さいい場合は、D7の絶対値を”▲h:mm:ss"で表示、0以上の場合は"h:mm:ss"で表示します。

 

◆秒換算で計算して時間表記にする方法

####でもちゃんと時間としてのシリアル値の数値を保っているので、これで大丈夫なのですが、なんか見えないと不安なので、私は秒換算(分が最小単位の時は分換算)して計算し、それの結果を時間表示にしてあらわしたりしています。

f:id:mwke:20210214114158p:plain

D列はB列1月の時間を秒換算しています。

ちょっと面倒なのですが、

=HOUR(B3)*3600+MINUTE(B3)*60+SECOND(B3)

とします。

B列の値からHOUR関数で時刻を取り出し、秒換算のため3600をかける(1時間は3600秒なので)。

同様にB列の値からMINUTE関数で分を取り出し、秒換算のため60をかける(1分は60秒)。

同様にB列の値からSECOND関数で秒を取り出し、すべてを足すとB列の値が秒換算されます。

 

E列はD列と同様にC列の値を秒換算したものです。

 

で、秒換算した1月と2月を比較して差を出しています。列の秒を使って差の平均も出しています。

「B君、2月の方が1820秒遅くなってるね」とか言われても、「平均は305秒早くなってるね」とか言われてもピンとこないだろうと思い、G列で時間表記にしています。

 

例えばG7の平均の箇所の式は

=IF(F7<0,TEXT(TIME(0,0,ABS(F7)),"▲h:mm:ss"),TEXT(TIME(0,0,F7),"h:mm:ss"))

です。

F7の値が0より小さい場合はF7の値からマイナスをとった絶対値をTIME関数の秒のところにつっこみ、TEXT関数で時間表記にしています。

TIME関数は時、分、秒のところに数値を入れると時間のシリアル値に変換してくれます。「秒」のところに〇秒を突っ込むと、60秒を超えるところは勝手に計算して時・分にしてくれます。

そしてTIME関数でシリアル値にしたものをTEXT関数で時間表記にしています。

 

◆関数は使わずエクセルのオプションで負の時間を表示する方法

なお、エクセルのオプションで「1904年から計算する」にチェックを入れると、時間がマイナスでもエラーにならなくなります。

f:id:mwke:20210214120021p:plain

ただし、日付の計算方式が変わってしまうので、既に日付がどこかに入力されているエクセルの場合、注意が必要です。どこでなにがおこるか予想がつかない部分があるのであまりお勧めではないです。

f:id:mwke:20210214120732p:plain


以上です。

最後までお読みいただきありがとうございました。

 

時間についてはこちらもどうぞ!

 

mwkexcel.hatenablog.com

 

 

【Excel/関数】TIME関数は時・分・秒を指定せずとも秒に突っ込むだけで時分も計算してくれる

エクセルのTIME関数は TIME(時,分,秒)と入力すると時間のシリアル値にしてくれる関数。時間形式ではない数値を時間形式に変換してくれる関数です。

ちなみにシリアル値とは。。。

シリアル値についてはこちらのサイトが分かりやすかったです

日付と時刻のシリアル値とは - 日付関数 - Excel関数入門 (officepro.jp)

 

たとえば =TIME(2,30,40) と入れると 2:30:40 と時間に変換してくれる。

セルの値を利用して計算したりもします。

f:id:mwke:20210207105809p:plain

 

勤務時間や処理時間など前月と比較したりする場合に計算するとマイナスになることもあり、時間形式でマイナスだとエラーになってしまうので、時間の間隔の計算は秒単位、または分単位にしてから計算することがある。

なお、

「今回の処理時間は前回に比べて5489秒早かったです」

と、言われてもピンとこないだろうと思うので、秒で計算したものをまた時間形式に変換して返します。

f:id:mwke:20210207110938p:plain

 

で、例えば5489秒という値を時間に変換したいとき、わざわざ時間、分、秒に分けてTIME関数に入れていた。

 

「時」のところに5489を3600(1時間は3600秒)で除算して、切り捨てした値 1 を入れる。

「分」のところに5489から1 × 3600(さっき出した〇時間×3600)を引いた値を60(1分は60秒)で除算して切り捨てした値 31 を入れる。

「秒」のところに5489から1 × 3600(さっき出した〇時間×3600)を引いて、さらに 31 × 60(さっき出した〇分×60)を引いた値 29 を入れる。

とめんどくさいことをやっていた。

 

式にすると(上で書いたのとちょっと違うけど)

=TIME(ROUNDDOWN(5489/3600,0),
ROUNDDOWN*1*3600)/60,0),
5489-(ROUNDDOWN(5489/60,0))*60)

→1:31:29

 

それが

=time(0,0,5489)

→1:31:29

これだけでよかった!

 

なんとー

 

時、分には0を入れても省略してもどちらでも大丈夫。

f:id:mwke:20210207111812p:plain

ちなみに「分」に65を入れてみた。1時間5分と計算してくれた。

f:id:mwke:20210207112428p:plain

ちなみに「時間」に24を入れたら0:00:00となり、25を入れたら1:00:00となった。プラス1日とかはならないみたい。

「分」に30.5とか入れたら

30分30秒とか計算してくれたりして、とやってみたけど小数点以下は無視されるみたい。

f:id:mwke:20210207112614p:plain

以上です。

最後までお読みいただきありがとうございました。

 

TIME関数についてはこちらのサイトを参考とさせていただきました。

わかりやすくTIME関数が理解できました。ありがとうございました。

エクセル TIME 関数:時間を計算した結果を取得する (tipsfound.com)

 

時間、日付に関する記事はこちらもどうぞ

 

mwkexcel.hatenablog.com

 

 

 

*1:5489-(ROUNDDOWN(5489/3600,0

【基本情報処理技術者】そういえばcookieってなんだろうって調べてみた

いつ会社をクビになるかもわからないので、なんかもっておいたほうがいいよなあと基本情報処理技術者を受けようと思っている。。。が、到底受かる気がしない。問題文が読めない。日本語として頭に入ってこない。でも、いろいろと知らなかった世界を覗くことができて、なんだかおもしろい。イーサネットとかMIMEとか聞いたとこはあるけど、へーそうゆうものなんだーと。学習しても一週間たつと「で、イーサネットってなんだっけ?」って忘れてしまうんだけど。。。

 

今回はcookieについて。問題に出てきて、そういえばネットを見てると「このサイトではcookieを使用しています。cookieの使用に同意してください」みたいにでてくることがあり、cookieってなんだかわからないけどなんかこわいものって聞いたことがあるからみるのやめとこう、とかしてたんだけど、そういや一体何だろう調べてみようと思ったのでその覚書。ぴよぴよ頭で理解できるようむりやり翻訳したものなので、正確ではありません。正しいところは正しいサイトでご確認ください。

 

cookie(クッキー)とは

・WebサーバーからユーザーのWebブラウザに送られる、ユーザーのデータを保存しておくためのファイルのこと。

・クッキーの語源は「マジッククッキー」であるといわれている。マジッククッキーとはフォーチュンクッキーとも呼ばれる、中に秘密のメッセージが入ったクッキーのこと。サーバーがメッセージが入ったクッキーをWebブラウザに「はいっ。これあげる」って渡すイメージ?

 

→ 次に来た時にはこのメモ持ってきてね、とサーバーがブラウザにメモを渡しておく。次にそのメモをもって同じサイトを訪れると「あ、このひとね。このユーザーで、この商品をカートに入れていたわね」と前と同じ状態を表示してくれたりする。

AKBの歌っていたフォーチュンクッキーってメモ入りクッキーだったのか。いままで知らずにいた。。。

 

 

cookieの使用目的

・ユーザー情報を保存し、利便性を向上

→ サイトを一度離れても、次にサイトにアクセスしたとき、ログインID・パスワードをいちいち入れなくていい。ショッピングカードの中身がそのままになっていてくれる。等、たしかに便利だ。

ただし、cookieはブラウザごとに保存されるので違う端末では有効ではない。違う端末では、また、ログインID・パスワードを入れなければならない。(そういやそうだな)

 

・ユーザーの嗜好や趣味、何を検索したか等の情報を取得し、ユーザーごとに適した広告配信を行う。

→ ああ、あの「犬」を検索したら「犬のエサ」とか犬関連の広告がじゃんじゃん出てくるな、と思ったやつね。

ショッピングサイトなどではユーザーが閲覧した情報をcookieに保存して、別のサイトを見た時も関連商品の広告を出す。「リターゲティング」という方法らしい。

 

cookieの種類

・ファーストパーティークッキーとサードパーティークッキーがある

→ ユーザーがアクセスして、ユーザーIDとかショッピングカードの中身の保存と化してくれる役割の方がファーストパーティークッキー。そのサイト内でのみ有効。

サードパーティークッキーは見に行ったサイトではなくその中で表示されている「広告」とかから発行されるクッキー。ファーストパーティークッキーとは違い、別のドメインでも共有できるので、広告を表示したりするのはこちらのクッキー。複数サイトにまたがってユーザーの興味を収集する。サードは第三者の意味。セカンドパーティークッキーはない。

 

cookieのこわいところ

・共用パソコンでたとえばAさんが楽天にログイン、次にBさんが同じパソコンで楽天を見に行くと、Aさんのログイン状態で表示される。

共用パソコンではcookieを無効にするか、削除しないと危険。

・あとは、勝手に趣味とか行動とか個人の情報を集められてていやだなあ、という感じ?

 

cookieは知らないうちに便利に利用していた。むやみに怖がるものではなく、共用パソコンでの利用など、気を付けるべきところは気を付ければよい。ということを勉強しました。

 

「知る」ってすばらしい。で、cookieってなんだっけって一週間後に忘れてなければいいなあ。

 

ほとんどこのサイトから引用しています。すみません。よく理解できました。ありがとうございます。

cookie(クッキー)の意味と使い方をわかりやすく解説!企業側もマーケティングに大活躍!? (digitalidentity.co.jp)

あとは毎度お世話になっているピヨ太さん

クッキー (cookie)とは|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 (i-3-i.info)

まずはピヨ太さんで理解する素地を作り、足りなければそのあと詳しく調べるのが常。ピヨ太さんでわかりやすくだいたいのところをつかんでおくと、そのあと難しい解説でも若干理解しやすい。

 

 

 

 

【基本情報処理技術者】公開鍵、秘密鍵、暗号化などについて

基本情報処理技術者試験を受けようと思っている。だがしかしやはり、カタカナ用語が覚えられない、横文字用語が覚えられない、問題文を最後まで読む気力がない、解説を読んでもさっぱり意味が分からない、そんなこと知ってどうすると逆切れしそうになる、等、到底受かる気がしないし、勉強するのもつらい。

このままではいかんとえいやッと試験の申し込みをしてしまった。今回は落ちるでしょう。二回目落ちたらあきらめよう。

でも、回答は選択式だし、もしかしたらもしかしないかしら、という若干の淡い期待も捨てきれない。

 

それはさておき、セキュリティの公開鍵とか秘密鍵とかこんがらがってよくわからなかったので、ネットでいろいろ見てみて学習したことの覚書。

きちんとした知識に基づいたものではなく、ネットを流し読みした程度のものなので、事実と異なることを書いているかもしれません。

 

そもそも暗号化とは

ネットワーク上でデータの安全性を高めるための方法の一つ。

主に以下の二種類に分類される。

秘密鍵暗号方式(共通鍵方式)

公開鍵暗号方式

 

そのまんま送ると、盗み見られちゃったときやばい。なので、内容を何らかの方法でぐちゃぐちゃにして送って、受け取った側は何らかの方法で元に戻して内容を知る、という感じ。

ちなみにそのまんまの文書のことを「平文」というみたいで、ひらぶん?へんな日本語、と思っていた。英語では「plaintext」というらしい。なるほど、略すと平文ね、と納得。暗号化した文書は「暗号文」「ciphertext」というらしい。

 

秘密鍵暗号方式(共通鍵方式)

暗号化と復号に同じ鍵を使用する。暗号化に使用する鍵をあらかじめ安全な方法で相手に届けておく必要がある。

公開鍵暗号方式

暗号化と復号に別のカギを用いる暗号方式。「公開鍵」と「秘密鍵」をペアで使用。鍵の片方は他人にも広く公開する「公開鍵」、もう片方は本人のみが持っている「秘密鍵」。

公開鍵暗号方式のメリットは暗号を解くことを非常に困難でセキュリティが高いこと。公開鍵は一般に公開しているものなので、秘密鍵方式のように安全に注意して鍵を送る、という手間が省ける。

「公開鍵」で暗号化し文書などを本人に送付、復号できるのは本人のみが持っている「秘密鍵」のみ。「公開鍵」は暗号化するのみで複合はできない。なので「公開鍵」は悪い人に盗まれても大丈夫。「秘密鍵」は厳重に保管し盗まれないようにしなければいけない。

公開鍵暗号方式のデメリットは、複雑な計算処理が必要なのでデータの暗号化、複合化に処理の負荷がかかる。

だそうです。

 

実際にどんな時に使われているのか?ということを調べてみました。

 

SSL通信

ネットで買い物するときとかにでてくるやつ。クレジットカード番号を入力したりするときに情報を盗み見られないように安全になっている。

ホームページのアドレスがHTTPSになっているやつはSSL通信を使用していて安全!

仕組みは、公開鍵方式と共通鍵方式を合わせたものだそうです。

なんとなくこんな感じかなの例。

太郎がネットショッピング、XYZマートのお洋服を購入する。

①太郎は(知らないうちに)公開鍵を取得し、共通鍵を作る

②太郎は公開鍵を使用し、共通鍵をがっちりガードしてサーバー(XYZ商店側)に送る(開封できるのは「秘密鍵」を持ってる人のみ)

③サーバー側(XYZ商店側)は秘密鍵を使用し、がっちりガードされた共通鍵を開封してゲット

④以降、同じ共通鍵を使用して通信内容を暗号化、複合し通信する

 

電子証明書

マイナンバーカードでも使われているらしい。マイナンバーカード取得時に「電子証明書を利用する場合は・・・」とかなんとか言われ訳が分からないまま暗証番号などを設定したような記憶が。。。で、訳が分からないまま「有効期限が切れます」という通知を受け取り役所に申請に行ったような記憶が。。。

 

電子署名とは。

これまた公開鍵方式を使っているらしい。

情報を送信する際に本人しか持ちえない秘密鍵で暗号化

→受信者がその人の公開鍵で複合できれば間違いなく本人からの通信だと証明出来て安心。

→文書が改ざんされていないことを調べることができて安心。(送られてきたハッシュ値?と送られてきた文書から公開鍵を使って生成されるハッシュ値が同じであることで文書が改ざんされていないことを確認できるらしい)

 

公開鍵はだれでも持てる「公開鍵」だから、この場合「秘密鍵」では「文書の中身が見られちゃいけない暗号化」が目的なわけではなく、「秘密鍵」でしかできない計算をしてハッシュ値?を作成するためのもの。

 

マイナンバーカードは持ってるけど、秘密鍵とかどこに入ってるんだ?というと、ICチップの中に入っているらしい(知らなかったわ。いつのまにか鍵を持っていた!)

公開鍵もICチップの中に入っているらしい。公開鍵と電子証明書はセットみたい。

 

どんな用途に使うのか?

①署名用電子証明書

②利用者証明電子証明書

 

①署名用電子証明書 は 本人ですよ!と証明できる

e-taxとか利用するときに、本人が申請しましたよ!と証明できる。

また、文書が改ざんされてないかどうかも確認できる。

署名用秘密鍵と「公開鍵+電子証明書」を利用。

 

②利用者証明電子証明書 は 本人がログインしましたよ!と証明できる。コンビニで住民票を取りたいなとか、サービスを利用するときに使える

本人であることの認証手段。

利用者証明用秘密鍵と「公開鍵+電子証明書」を利用。

 

どうやって「本人ですよ!」と確認できるのか。(ざっくりこのように理解しました)

①認証してください、とリクエスト(本人)

②乱数を送付(認証側)

③乱数をマイナンバーカードの秘密鍵で変換しハッシュ値(?)を作成(本人)

④乱数を変換したハッシュ値と公開鍵・電子証明書を送付(本人)

⑤送られてきたハッシュ値を、同時に送られてきた公開鍵(電子証明書)で復号し、元の乱数と比べて一致を確認(認証側)

⑥送られてきた電子証明書(公開鍵)を認証局に確認。有効ですよと返事をもらい、本人であると認証

 

本人しか持っていない「秘密鍵」で作成したハッシュ値はその人の「公開鍵」でしか復号できない。

「公開鍵」で復号したハッシュ値の結果が一致することを確認できた。

⑥で「公開鍵」が正しいことを確認しているから「本人と認証成立!」

 

 

こちらを参考とさせていただきました↓

マイナンバーカードの安全性 (cao.go.jp)

マイナンバーカードはどうやって認証してる? 意外と知らない「所有物認証」のハナシ (4/4) - ITmedia NEWS

そしていつもお世話になっているピヨ太さん

公開鍵暗号方式 (public-key cryptography)とは|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 (i-3-i.info)

 

電子署名」がむかしながらので例えると「押印」

電子証明書が昔ながらで例えると「印鑑証明書」→電子証明書は国が指定した認証局が「これは正しいもので信用できるものです!」と証明してくれる。

 

わかったようなわからないような。。。

とにかくマイナンバーカード申請時に電子証明書申請しておいてよかった。コンビニで住民票とか取りたいときに使えるんだろうな。

 

最後まで読んでいただいた方、、、なんだかごめんなさい。

 

2021.1.31加筆しました 

 

【データベース】正規化について理解するように努める

データベースの正規化。らしきものはできる。

でも、「データベースの正規化とは」となると・・・

・データ等を一定のルールに基づき利用しやすいように整理すること

・第一正規形:データの冗長性や不整合を排除する。繰り返しの項目を持たない

・第二正規形:すべての非キー属性が候補キーに完全関数従属する

・第三正規形:すべての非キー属性が候補キーに推移的関数従属しない。

・・・と書いてあるのを見かけるが、意味が分からない。

 

なので、この機会に正規化についてぴよぴよ頭で理解するように努めてみました。

 

参考になる記事を読んで、ざっくり以下のように飲み込みました。

・第一正規形:とりあえず最低限データベースに突っ込めそうな形にする

・第二正規形:主キーをみつけて「主キーが決まればこの項目は決まるよね」という項目を表から切り離す

・第三正規形:主キー以外の項目でも「この項目が決まればこの項目は決まるよね」という項目を表から切り離す

 

やってみます。

 

◆例1:請求書をデータベース化する。

1.第一正規形

何枚か紙の請求書があります。これを正規化しながらデータベースに入れてみようと思います。

f:id:mwke:20210117124934p:plain

こうしました。

f:id:mwke:20210117125306p:plain

でも、これではデータベースにつっこめません。

エクセルの表としては見やすいですが、データベースに突っ込んだ場合、明細の2行目がNULLになってしまいます。

なので、こうしました。色付きの部分(「請求ナンバー」と「請求先」「請求先住所」)を2行目にもコピーしました。

f:id:mwke:20210117125428p:plain

これで第一正規形ができました。

 

2.第二正規形

次に第二正規形です。

①主キーの特定

まず、各項目(候補キーというらしい)から主キーになるものを探します。

f:id:mwke:20210117134004p:plain

主キーの制約は

1)重複がないこと

2)NULLがないこと

だそうです。

それと、

・主キーとは。テーブルの行を一つに特定するためのキー

つまり、行が変われば必ず違う項目のこと。テーブルの中でその行だけに唯一無二の値!そして、複数のキーの組み合わせでも可、です。

さて、主キーを探します。

請求№ → 重複があるからだめ
請求先 → 重複があるからだめ
請求先住所 → 重複があるからだめ
明細№ → 重複があるからだめ
商品名 → 重複があるからだめ
数量 → どう考えてもちがうだろう
単価 → どう考えてもちがうだろう

ない!

まてよ。「請求№」と「明細№」の組み合わせではどうだろう。

これで行ける!

よって、この表で主キーは「請求№」と「明細№」としました。

f:id:mwke:20210117140130p:plain

②「主キーが決まればこの項目は決まるよね」という項目を表から切り離す

この値が決まればこの値は決まるよね、ということを「関数従属」というそうです。

たとえば、社員番号が特定されれば社員名も特定される=社員名は社員番号に関数従属しているといえる、といったような感じ。

 

「主キーが決まればこの項目は決まるよね」ですが、主キーに関係していれば「主キーの一部が決まればこの項目は決まるよね」も同様とします。

表を眺める… 「請求№」がきまれば「請求先」「請求先住所」が決まるっぽい。

なので、切り離します。

f:id:mwke:20210117140524p:plain

表か2つになりました。

「請求一覧」と「請求明細一覧」とテーブル名を付けました。

黄色の項目が各テーブルの主キーです。

これで第二正規形ができました。

評価分離する際に気を付けること!元の表に戻るようにしなければなりません。

下のようにしちゃうと元に戻らない・・・。

f:id:mwke:20210117141037p:plain

3.第三正規形

主キー以外の項目で関数従属を探します。

主キー以外の項目で「この項目が決まればこの項目は決まるよね」です。

請求一覧を眺める…「請求先」がきまれば「請求先住所」が決まります!→取引先マスターとして分離

請求明細を眺める…「商品コード」がきまれば「商品名」「単価」が決まります!→商品マスターとして分離

f:id:mwke:20210117142143p:plain
表か4つに分かれました。なんだかリレーショナルデータベースって感じになりましたね!

これで第三正規形ができました。完成!

 

理解を深めるため、もう一つやってみます。

◆例2:料理レシピをデータベース化する

何枚かの料理メモがあります。これをデータベース化します。

f:id:mwke:20210117142850p:plain

1.第一正規形

表にしました!

f:id:mwke:20210117143256p:plain

1メモ1行に整理しましたが、なんかだめっぽい表ですね。

どこがだめかというと「材料」と「分量」が横に繰り返しになってます。いわゆる「冗長な表」という感じです。

「材料7」「分量7」まで表を作ってますが7個を超える材料のレシピがでてきたら表を「材料8」「分量8」・・・と項目を追加する必要が出てきてしまいます。

また、カレーの「材料7」「分量7」はNULLです。NULLがあるのはあんまり好ましくなさそうですよね。

一つの料理に材料は何個必要かしら・・・?とか集計するのも大変そうですね。

 

なので、こうしました。

f:id:mwke:20210117143919p:plain

これでさっきよりはましになりました。

一応データベースに突っ込める形にはなったので、第1正規形完了とします。

 

2.第二正規規形

①主キーをさがせ!

さて、主キーをさがせ!です

№ → 重複がある

料理名 → 重複がある

材料 → 重複がある。っていうかいろいろある。キーっぽくない

分量 → 違うだろ~

作り方 → 重複がある。っていうか違うだろ~

 

組み合わせると唯一無二のもの(行ごとに違うもの)を探します。

「料理№」と「材料」

「料理名」と「材料」

 

どちらも行ごとに違うものですが、「料理№」と「材料」のほうがキーっぽいかな。ということで「料理№」と「材料」の組み合わせで主キーとします。

f:id:mwke:20210117144857p:plain

 

②「主キーが決まればこの項目は決まるよね」という項目を表から切り離す

および「主キーの一部が決まればこの項目は決まるよね」という項目を表から切り離す。

主キーの一部「№」→「料理名」「作り方」がきまる

主キーの「№」「材料」→「分量」がきまる

f:id:mwke:20210117145421p:plain

分離します。

f:id:mwke:20210117145821p:plain

レシピテーブルと材料テーブルに分離しました。

これで第二正規形となりました。

主キーと関係ない項目で、もう分離できる項目はないので、これは第二正規形でおしまいです。

 

やれやれ。疲れますね。

要は、

・管理がしやすい形(データの追加はよくても項目の追加・削除はない方がよい)

・集計がしやすい形

・重複するものはまとめる(例えば上の例でレシピテーブルに料理名、作り方をまとめたので、カレーの作り方が変わった場合一つの表の一つのデータだけ直せばよい。正規化前だと何行も直さなければならず、大変~ということになる)

・各表は主キーにつながっているデータで構成されるのがよい

といったものを目指す、というところでしょうか。

 

データベースの正規形について、理解が違っていたらすみません。

accessにさわりはじめて十何年・・・正規化をちゃんと理解せずになんとなくもやもや正規化をやってきたが、ここでだいたいこんなものかな~と理解できたと思い、自分的には満足。

 

参考にさせていただいたホームページ(わかりやすかったです!)

データベースの正規化の手順をわかりやすく解説 (katalog.tokyo)

【データベース】正規形をなんとなくでいいから理解したいのに理解が難しい人のためになるべくわかりやすく書いた記事 │ コジマノテック (kojimanotech.com)

データベースの正規化(正規形)とはなんぞや – gomokulog (gomocool.net)

【初級編⑧】テーブル正規化の概要とその手順 | SQL Server 虎の巻 (kaya-soft.com)

ありがとうございました!