「sqlのスキーマについて簡潔に答えよ」と聞かれたら皆さんは的確に答えることが出来ますか？
スキーマという言葉は聞いたことがあるけど、実際のところはあまり理解できていないという方は非常に多いのではないでしょうか？
そこで今回はこのスキーマについて、基本的な部分から解説してみたいと思います。
初心者の方にも理解しやすいように別のものに例えながら解説していきますので、多少の解釈は違うかもしれませんがこの記事で少しでも理解を深めていただけると幸いです。
スキーマとは
まず大前提として、これからする解説はSQL Serverでの用語と言うことを念頭に置いておいてください。
(というのも、OracleとPostgreSQL、SQL Serverではスキーマに対する解釈が少しずつ違うため、Oracleのスキーマと思って読んでいると「ん？なんか違うぞ…」ということになります。)
SQL Serverでのスキーマとは簡単に言い換えるとデータベースにおける名前付きコンテナだと思ってください。
もう少しわかりやすく説明すると、ユーザーAを作成した際には必ずスキーマAも同時に作成されます。
しかしユーザーA＝スキーマAではありません。
あくまでもユーザーAが持つ全ての権限を有したAという名前のコンテナを指します。
このコンテナにはデータベースのあらゆるオブジェクト(データベースの構造の全て)が格納されています。
既にここまでで訳がわからなくなった方は簡単に「スキーマ＝データベースの設計図 」と考えていただいて大丈夫です。
3つのスキーマ
先ほど「スキーマ＝データベースの設計図」と説明しましたが、この設計図であるスキーマにもいくつかあります。
「外部スキーマ」「概念スキーマ」「内部スキーマ」の3つです。
データベースでスキーマを定義するということは、これら3つの構造を定義するということです。
それでは早速これらのスキーマの役割について見ていきましょう。
外部スキーマ
外部スキーマとは概念スキーマで定義されたデータから、必要なデータを取り出したビューのような部分を言います。
より分かりやすく言うなら「目に見えるもの構造」と言えるかもしれません。
概念スキーマ
名前の通り、データベースの概念を決定づけるスキーマで論理データにあたる部分を指します。
もう少し噛み砕くと、データの要素やデータ同士の関係性などを定義している部分とも言えます。
内部スキーマ
概念スキーマで定義付けられた論理データが、具体的にどういう形で格納されるかを定義する部分です。
これら３つのスキーマを意識しながら設計することで、どの箇所を変更すれば良いか明確に見えてくるので最小限の変更で済むようになります。
スキーマについて学習前の方は逆に吸収する事しかないはずですので、早い段階で3つのスキーマの階層を意識するようにしましょう。
スキーマの作成
これまではスキーマそのものについて解説してきましたので、ここからは実際にスキーマを作ってみたいと思います。
基本的な記述ルールは次のようになっています。
-- Syntax for SQL Server and Azure SQL Database
CREATE SCHEMA schema_name_clause [ [ ...n ] ]
<schema_name_clause> ::=
{
schema_name
| AUTHORIZATION owner_name
| schema_name AUTHORIZATION owner_name
}
<schema_element> ::=
{
table_definition | view_definition | grant_statement |
revoke_statement | deny_statement
}
- schema_name = 該当データベースで使用するスキーマの名前を指定します。
- AUTHORIZATION owner_name = スキーマを所有するユーザー名を指定します。ここで設定したユーザーも他のスキーマを所有することは出来ますが、既定のスキーマとして使用することは出来ません。
- table_definition =スキーマ内のテーブルを作成するステートメントを指定します。CREATE系は全てに対し同様のことが言えますが、権限を持っている場合のみ作成可能となります。
- view_definition = こちらはスキーマ内のビューを作成するCREATE VIEWステートメントを指定します。ステートメントの実行にはtableと同じく、CREATE VIEW権限が必要です。
- grant_statement = 権限を付与するユーザーを指定します。
- revoke_statement = 権限を取り消す際にユーザー名を指定します。
- deny_statement = 権限を拒否する場合にユーザー名を指定します。
それでは早速次のコードを見てみましょう。
CREATE SCHEMA sample_schema AUTHORIZATION sample_user CREATE TABLE sample_table ( sample_id INT IDENTITY(1,1) PRIMARY KEY, sample_name VARCHAR(50) NULL, sample_group VARCHAR(50) NULL ) GRANT SELECT ON SCHEMA::sample_schema TO sample_user_A DENY SELECT ON SCHEMA::sample_schema TO sample_user_B; GO
このコードは「sample_schema」というスキーマを作成しています。
スキーマを作成してすぐ「sample_table」という名前のテーブルを作成し、このテーブルを操作できる権限を与えています。
“GRANT”より後の記述は全て、各ユーザーに対しての権限の設定です。
許可の場合にはGRANT、未許可の場合にはDENYを使用します。
このコードでは「sample_user_A」にはSELECTを許可していますが、「sample_user_B」に対してはSELECTの許可は付与していません。
スキーマを確認する
データベーススキーマは、次のステートメントで確認できます。
SELECT * FROM sys.schemas;
まとめ
いかがでしたか？
今回はスキーマについて出来るだけ噛み砕いて解説してみました。
データベースに関する全ての知識を丸々理解しようとすると非常に膨大な量で嫌気が差すと思いますが、こうやって必要な部分だけかいつまんでいけば以外にもすぐ使えるようになります。
スキーマについても、なんとなく理解できている程度で学習をしていけば、ある程度した後に完全に理解できるようになるかもしれません。
この記事で何となくでも形がつかめたと言っていただけると非常に嬉しく思います。
