Oracle University 講師が ORACLE MASTER 取得を目指している方に向けて試験トピックを解説するブログ連載講座。「ORACLE MASTER Silver SQL 2019」資格取得に向けた試験トピックについて解説します。
————————————————-
今回はORACLE MASTER Silver SQLの試験トピック「リレーショナル・データベースの概念 – ERDのコンポーネントへのSQL Select文内の句の関連付け -」に関連する問題をご紹介いたします。ORACLE MASTER Silver SQL(Exam Number: 1Z0-071-JPN)の詳細につきましては以下URLをご参考ください。
Oracle Database SQL Exam Number: 1Z0-071
URL: https://education.oracle.com/ja/oracle-database-sql/pexam_1Z0-071
————————————————-
今回は、リレーショナル・データベースの概念に関する内容になります。データベースの設計の際に用いられるERモデルに関する問題を解いていきましょう。
(解説)
それでは解説をしていきます。今回はERモデルということで、データベース設計などに携わる機会が少ない方にはあまり馴染みが無い内容だったかもしれません。イメージ図も使用しながら少しずつ説明していくので頑張って学習していきましょう。
お客様が必要とするデータに的確にアクセスし、かつ素早くデータ分析ができるようにするためはリレーショナル・データベースの設計は非常に重要な要素になります。そのため、業務データの種類やデータ同士の関連性などを把握してデータを適切な表定義内に格納していく必要がありますが、その際に使用される設計手法として用いられているのが「ERモデル」になります。
ERモデルには主に次の4つの要素から構成されています。
- エンティティ
- アトリビュート
- リレーションシップ
- カーディナリティ
それぞれについて解説していきましょう。
- エンティティ
エンティティとは人やモノ、具体的または抽象的なものも含め情報を蓄積する対象となるものを指します。
以下の図を確認してください。
従業員の情報を蓄積していきたいのであれば「従業員」エンティティ、部門の情報を蓄積していきたいのであれば「部門」エンティティという入れ物に格納していくという具合ですね。それぞれのエンティティを、従業員表(EMPS)、部門表(DEPTS)というように表に置き換えていただくと、理解しやすいかと思います。
- アトリビュート
アトリビュート(属性)とはエンティティ内にどのようなデータで構成するか説明する要素(情報)になります。
先ほどのエンティティを例えにしてアトリビュートを構成した図を以下に示しましょう。
従業員の情報を格納していきたいのであれば、従業員用のIDや従業員の名前を管理する必要があるでしょう。そのため従業員エンティティに「従業員ID」や「名前」、「部門ID」を定義しています。これがアトリビュートに該当します。
またアトリビュートにはそのデータの特性によって「主キー」や「外部キー」として表示していきます。
エンティティが表に該当すると説明しましたがアトリビュートについては表に定義されている列(カラム)と考えると分かりやすいですね。
- リレーションシップ、カーディナリティ
各エンティティの関連性を、線を用いて表現するのがリレーションシップです。また各エンティティのリレーション情報として「1対1」「1対多」「多対多」など詳細に記載するのがカーディナリティになります。
今回の問題文にありました「従業員(EMPS)」エンティティと「部門(DEPTS)」エンティティの関連性をERモデルで表現した図が以下になります。
※IE記法での表示になります
こちらの図を参考にリレーションシップ、カーディナリティの記述方法についてまとめておきましょう。
①関連性のあるエンティティを洗い出して線で結び、相互関係が分かりやすいようにリレーションシップ名を付けます。
②カーディナリティ(最大の個数について、1または多)を検証して記述します。記述の方法は以下です。
- 1個・・・「|」
- たくさん(多)・・・「3本爪」
③リレーションシップの任意性を検証します。
リレーションシップが必ず成立する(必須)・・・「|」
リレーションシップが成立しないことがある(任意)・・・「〇」
以上のことより「従業員」エンティティと「部門」エンティティの関連性を言葉で説明すると以下になります。
- 部門に対して従業員は、複数の人数が所属している。ただし0人の場合もある。
- 従業員は必ず1つの部門に所属している。
さて、これでERモデルの基本的な部分について確認ができましたので、問題文に記載があったEMPS(従業員表)とDEPTS(部門表)を表形式で作成してみます。
- 従業員は1つの部署に所属している必要があります。
- EMPS表とDEPTS表のリレーションを構成するためにEMPS表からDEPTS表のDEPT_IDを外部キーとして参照させます(EMPS表のDEPT_ID列に外部キー制約を設定)。
- 各部門には複数の従業員を所属させることが可能です(上記の例だと第1営業部に植木、中村の2人が所属)。
- DEPTS表のDEPT_IDが40である第2営業部には、従業員が一人もいませんがリレーションシップが成り立たなくてもOKなので問題ありません。
- 従業員用のIDは必ず存在するということでEMP_IDに主キー制約を定義しています。
これでERモデルを用いてEMPS表とDEPTS表のデータベース設計ができました。
ここまでくれば問題の選択肢の正解を選ぶことができるはずです。
さっそく確認していきましょう。
選択肢1は正しい内容になります。DEPTSエンティティ、EMPSエンティティをDEPTS表、EMPS表に置き換えて考えてみてください。1つの部門に対して複数の従業員が所属できるので「DEPTS：EMPS ＝ 1：多」
の図式が成り立ちます。
選択肢2は、選択肢1の逆となる内容になりますので誤りですね。
選択肢3は正しい内容です。先ほど作成したEMPS表とDEPTS表の関係性を確認するとよいでしょう。
選択肢4の内容は誤りです。後半部分の「・・・DEPTSエンティティ内の外部キーである必要がある。」が間違っています。
選択肢５は「交差テーブル」という新しい用語が出てきているのでこちらは後で説明しますが、DEPTSとEMPSは1対多の関係であるため、「多対多」と書いてあるこの選択肢は誤りになります。
以上の内容から、正解の選択肢は１と３になります。
さて、選択肢5に登場していた「交差テーブル(※)」について説明をしておきましょう。
※交差エンティティや交差表、中間テーブルと言ったりもします。
先の解説にて、リレーション情報のカーディナリティの種類には「1対1」「1対多」「多対多」があるとお伝えしました。まずはそれぞれの例を確認しておきましょう。
ここで着目してほしいのは一番下にある「多対多」になります。
この例では「図書館カード」と「蔵書」というエンティティが出てきていますが、皆さんが図書館に書籍を借りに行くのを想像してみてください。
通常、図書館カードを使って複数の書籍を借りることができますよね(図書館によっては1人一冊限定という所もあるかもですが・・・)。そして書籍は図書館カードを持参した不特定多数の人から借りられる状態になっているでしょう。このような場合「多対多」の関連性になるわけです。
この「多対多」の注意事項として、リレーショナルモデルではこの関連性をそのままモデル化することはできないという点です。厳密にいうと絶対に出来ないというわけではないですが、非常に効率の悪い設計になってしまいます。
実際にアトリビュートを定義してみると分かりやすいと思います。
「図書館カード」エンティティにカード番号や利用者名を定義しましたが、「〇〇さんが何の本を借りているか？」という情報を管理する必要があると考えてみてください。仮に□の部分に「蔵書コード」というアトリビュートを追加で作成したとしましょう。アトリビュートは表の列(カラム)に相当しますから、列の中には1件ずつのデータしか格納できません。つまりこの設計だと1人につき1冊の貸出し情報しか格納できません。そのため「〇〇さんが何の本を借りているか？」という書籍情報を管理するためには貸出しをする書籍の数だけ列定義が必要になってしまいます。
「蔵書」エンティティについても同じように考えてみてください。「〇〇書籍は誰に何回借りられているか？」という情報を管理するとなった場合、「蔵書」エンティティ内に図書館利用者の人数分の列定義が必要になってしまいます。このような非効率な設計にならないために構成するのが「交差テーブル」になります。
今回の図書館の例のようにリレーションシップのカーディナリティが両方「多」になった場合は交差テーブルを使用して「多対多」の関連性から「1対多」の関連性に分解していきます。
- 交差テーブルを追加してエンティティ名を決定します。今回は「貸出し」にします。
- 各エンティティ(図書館カード、蔵書)と「貸出し」エンティティとが1対多の関連性になるようにリレーションシップによる定義を構成します。「図書館カード」を参照する外部キー制約を「貸出し」エンティティ内のカード番号(F)に設定します。また「蔵書」を参照する外部キー制約を「貸出し」エンティティ内の所蔵コード(F)に作成します。
- 「貸出し」エンティティに対して追加で必要なアトリビュートを考えます。例えば、貸出し日や返却日などが想定できます。
- 「貸出し」エンティティ内の一意識別子を決定します。データを一意に識別するために「カード番号(F)」、「所蔵コード(F)」に加え「貸出し日」も含めた複合主キーを作成します。図書館では、同じ利用者が、 同じ蔵書を繰り返し借りることが想定されるので、「貸出し日」も含める必要があります。
「カード番号(F)」、「所蔵コード(F)」のみの複合主キーだと同じ人が同じ本を2度と借りられなくなります(この後の交差テーブルのイメージ図も参考にしてください)。
以上の内容を踏まえまして、実際に表定義にしてみると以下のような形になるかと思います。
実際の試験問題でERモデル関連の問題が出てきましたら、各エンティティがどのようなリレーションシップをもっているか、それを考慮したアトリビュートの設定、交差テーブルが必要になるか、などをしっかりと考えて選択肢を確認してみてください。きっと正解の選択肢を選ぶことができるはずです！
今回はERモデルに関する問題を扱ってきましたがシステム設計に携わる機会が少ない方は難しかったと思います。基本的な内容については今回の講義で身に着けることができると思いますが、なかなかイメージができない方は、簡単なモデルでよいので身近なものをイメージして実際に作成してみると理解が深まると思います。是非チャレンジしてみてください。
それでは今回の講義はこれで終了とします。
お疲れ様でした。
————————————————-
– ORACLE MASTER Silver SQL 2019 のご紹介
– ORACLE MASTER Silver SQL 2019 試験記事トピック一覧
————————————————-
その他の ORACLE MASTER 試験トピック解説講座シリーズ：
【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧
————————————————-