SQL 一意制約違反と列の追加による回避
ホーム > 発行済み
読者の情報処理試験合格(シスアドから高度まで)を目指しています。
作者は、アプリケーションの開発を行なっているエンジニアです。
情報処理試験の知識(理論)を、日々の業務(実践)にどう生かしていくか、その辺りの事を意識
して、発行していきます。

SQL 一意制約違反と列の追加による回避


                                               
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
        ★★ 『情報処理試験のお勉強(千里の道も一歩から)』 ★★
                           2007/03/25 第97号
        知識の習得は、本当に楽しいものです。
        それぞれの目標に向かって、一歩ずつ踏出して行きましょう。
     
     ----------------------------------------------------------------------
        まぐまぐ:2479 めろんぱん:129 メル天:98 カプライト:126
        E-Magazine:183 melma:86 YAHOO:29      総発部数:3130
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
     
     
       『テクニカルエンジニア(システム管理) 午前平成18年問39』
     
     
      二つの表 S と SP から部品番号 P2 の部品を納入している納入業者名を探す
     SQL 文はどれか。ここで,下線部は主キーを表す。
     
       S
       ┌──────┬─────┬───┐
       │納入業者番号│納入業者名│ 住所 │
       │──────│     │      │
       └──────┴─────┴───┘
     
     
       SP
       ┌──────┬────┬────┐
       │納入業者番号│部品番号│ 納入量 │
       │──────│────│    │
       └──────┴────┴────┘
     
     
       ア  SELECT 納入業者名 FROM S, SP
             WHERE S.納入業者番号 = SP.納入業者番号
     
     
     
       イ  SELECT 納入業者名 FROM S
             WHERE 納入業者番号
                 IN(SELECT 納入業者番号 FROM SP WHERE 部品番号 = 'P2')
     
     
     
       ウ  SELECT 納入業者名 FROM S
             WHERE NOT EXISTS
               (SELECT * FROM SP
                    WHERE 納入業者番号 = S.納入業者番号 AND 部品番号 = 'P2')
     
     
     
       工  SELECT 納入業者名 FROM S
             WHERE EXISTS
               (SELECT * FROM SP
                   WHERE 納入業者番号 = S.納入業者番号 AND 部品番号 > 'P2')
     
     
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
     メールマガジンがずれて見える方は、
      http://www.mag2.com/faq/mua.htm を参考にしてください。
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━



合格を勝取るにはコレ!! 絶対に読んでおくべきです。

▽ http://www.mag2.com/m/0000120201.htm


     
      『解説』
     
       今回の問題は、SQLについての問題ですね。
       
       まずは、問題文の2つのテーブルを確認しましょう。
       テーブルには、「s」と「sp」の2つがありますね。
       
       s(納入業者番号、納入業者名、住所) 主キー:納入業者番号
       
       このテーブルは、マスタテーブルですね。
     
       システムを使用する前に、前もって納入業者の情報を保持しておく
       テーブルです。
       
       自分の会社に納入している全ての業者は、あらかじめ登録して
       準備をしておくのでしょう。
       
       
       次に
       sp(納入業者番号、部品番号、納入量) 主キー:{納入業者番号、部品番号}
       
       このテーブルは、納入業者別部品別に、納入量を保持するためのものですね。
       
       一見すると、トランザクションテーブルのように考えられますが、トランザクション
       テーブルとして活用するには、問題がありますね。
       
       トランザクションテーブルとして活用するとすると、下記のような場合に
       問題が発生します。
       
       昨日、納入業者Aから、部品番号ZZ1の部品を100個納入されました。
       本日、納入業者Aから、部品番号ZZ1の部品を500個納入されました。
       
       このような2つのデータを、登録することが出来ません。
       なぜだか分かりますか?
       
       理由としては、主キーが{納入業者番号、部品番号}の為です。
     
       {納入業者A、部品番号ZZ1}(昨日の分)
       {納入業者A、部品番号ZZ1}(今日の分)という2件のデータは、同じテーブル内には
       存在する事が出来ません。
     
       いわゆる、一意制約違反ですね。
     
     
       では、これを解消する為に、テーブルspにひとつ列を追加すれば上記問題を
       解決する事が出来ます。 さてどのような列を追加すれば良いでしょうか?
       
       答えは、「納入日付」これを、主キーに加えれば、一意制約違反を回避できますね。
       
       sp(納入業者番号、部品番号、納入日付、納入量) 
       主キー:{納入業者番号、部品番号、納入日付}
       
       列をテーブルに追加させて、既存の問題を回避すると言った問題、基本情報技術者試験は
       勿論、初級シスアドの午後試験で出題されているのを見たことがあります。
       
       基本情報技術者試験、初級シスアドでは選択式で出題ですね。
       それ以外の試験では、列名と列の使用方法まで記述を求められます。
       
       分からなかった人は、ぜひ習得して下さいね。
       
       
       話が、脱線してしまったので、元に戻します。
       つまり、spテーブルはトランザクションテーブルとしては、使用できないので
       使用方法としては、マスタテーブルとして使用するのでしょう。
       
       つまり、納入量等を見積もる為に、あの納入業者からあの部品はこれだけの量を確保
       する事が出来るということを事前に保持しておく為のテーブルとして使用するのでしょう。
       
       または、納入される時に、部品の数を検品するのですが、毎回決まった量を納入されるので
       その値を保持しておく等が想像出来ます。
       
       
       では、ひとつずつ、選択肢を見ていきましょう。
       
       求めるべきSQLの内容としては
       「P2 の部品を納入している納入業者名を探す」ですね。
       
     
     >  ア  SELECT 納入業者名 FROM S, SP
     >        WHERE S.納入業者番号 = SP.納入業者番号
     
       これは、部品を納入している納入業者の名前が出力されますね。
     
       このSQL文から更に、「P2 の部品」という条件を付加すると
       「P2 の部品を納入している納入業者名を探す」SQL文が出来上がりますね。
     
        SELECT 納入業者名 FROM S, SP
         WHERE S.納入業者番号 = SP.納入業者番号
         AND   SP.部品番号 = 'P2'
     
     
     
     
     >  イ  SELECT 納入業者名 FROM S
     >        WHERE 納入業者番号
     >            IN(SELECT 納入業者番号 FROM SP WHERE 部品番号 = 'P2')
     
       これは、副問合せですね。
     
       副問合せの中で
       SELECT 納入業者番号 FROM SP WHERE 部品番号 = 'P2' と記述されています。
     
       つまり、P2という部品番号を納入している納入業者の納入業者番号が
       導き出されます。
       
       そして、副問合せの外で、
     
        SELECT 納入業者名 FROM S
         WHERE 納入業者番号 IN(副問合せ)
     
       となっている為、納入業者番号から納入業者名が、出力されるのですね。
       
       これが、正解です。
     
     
     
     
     >  ウ  SELECT 納入業者名 FROM S
     >        WHERE NOT EXISTS
     >          (SELECT * FROM SP
     >               WHERE 納入業者番号 = S.納入業者番号 AND 部品番号 = 'P2')
     
       これは、相関副問合せですね。
       
       相関副問合せの中で
       SELECT * FROM SP WHERE 納入業者番号 = S.納入業者番号 AND 部品番号 = 'P2'
       と記述されています。
     
       つまり、P2という部品番号を納入している納入業者の行が導き出されます。
     
       そして、相関副問合せの外で、「NOT EXISTS」ですね。
       つまり、P2という部品番号を納入していない納入業者名が、出力されますね。
       
       上記SQL文は、「NOT IN」で書き換えられますね。
     
        SELECT 納入業者名 FROM S
         WHERE 納入業者番号 NOT IN
          (SELECT 納入業者番号 FROM SP WHERE 部品番号 = 'P2')
     
       これらは、今までに何度もこのメルマガで扱ってきたのでもう完璧ですよね。(笑)
       分からない人は、私のサイト内で詳細に説明をしていますので、ぜひご覧下さい。
     
     
     
     
     >  工  SELECT 納入業者名 FROM S
     >        WHERE EXISTS
     >          (SELECT * FROM SP
     >              WHERE 納入業者番号 = S.納入業者番号 AND 部品番号 > 'P2')
     
       これも相関副問合せですね。
       
       相関副問合せの中で
       SELECT * FROM SP WHERE 納入業者番号 = S.納入業者番号 AND 部品番号 > 'P2'
       と記述されています。
     
       つまり、文字コードで「P2」よりも大きな部品番号を納入している納入業者の行が
       導き出されます。
     
       そして、相関副問合せの外で、「EXISTS」ですね。
       つまり、文字コードで「P2」よりも大きな部品番号を納入している納入業者名が
       出力されます。

     
     
      今日の解説、理解できて納得ならば、クリックをお願いします。
      ▽ http://ranking.with2.net/link.cgi?33705
     
     



合格を勝取るにはコレ!! 絶対に読んでおくべきです。

▽ http://www.mag2.com/m/0000120201.htm


     
    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    
    『 ER図を、正確に読めるように、書けるようになりたい 』
    
      メガフラが、一番最初に、ER図の勉強に使用した良書をご紹介します。
    
      著者である林衛さんが、今年新たに書き直されたようです。
      本屋で見つけて、懐かしくて、しばらく立ち読みをしてしまいました。(笑)
    
      ER図の読み方、書き方に絞ってあるので、正確に情報を読み取れるようになりたい、
      書き込めるようになりたいと考えている方にお薦めです。
    
    
      ERモデルによるデータベース設計技法―モデルベース開発のための必修技術
    
      ▽ http://tinyurl.com/8ba5e
    
    
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
       『 情報処理試験のお勉強(千里の道も一歩から) 』
     
       発行者 :メガフラ
       HP  :http://jstudy.fc2web.com/
       発行周期:隔週
     
      以下のスタンドより、発行させて頂いています。
      配信登録、配信中止も以下のアドレスよりお願いします。
     
      まぐまぐ  :http://www.mag2.com/m/0000120201.htm
      めろんぱん :http://www.melonpan.net/mag.php?005485
      メル天   :http://melten.com/m/16380.html
      カプライト :http://cgi.kapu.biglobe.ne.jp/m/8670.html
      E-MAGAZINE :http://www.emaga.com/info/megafri.html
      melma!   :http://www.melma.com/backnumber_102220/
      YAHOO    :http://merumaga.yahoo.co.jp/Detail/1793/p/1/
     
     ----------------------------------------------------------------------
     <免責事項>掲載されているいかなる情報により損害を被っても、
           当方では責任を負いませんことをご了承ください。
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
     『試験別の参考書・問題集』
     
      試験を勝取るための本当の勝負は、午後の試験です。
      このメルマガで、午前問題を、必要以上に詳しく理解して、午後試験にも通用
      する知識を、習得してしまいましょう。
     
      また、早いうちから下記アドレスの参考書などで、午後の問題にも慣れてお
      きましょう。 準備は、早ければ早いほど、良いです。
     
       システム監査技術者試験
       ▽ http://jstudy.fc2web.com/book/book_sysKansa.html
     
        テクニカルエンジニア(データベース)試験
       ▽ http://jstudy.fc2web.com/book/book_db.html
     
        テクニカルエンジニア(情報セキュリティ)試験
       ▽ http://jstudy.fc2web.com/book/book_spring_ts.html
     
        テクニカルエンジニア(システム管理)試験
       ▽ http://jstudy.fc2web.com/book/book_systemKanri.html
     
        テクニカルエンジニア(エンベデッドシステム)試験
       ▽ http://jstudy.fc2web.com/book/book_endeve.html
     
        ソフトウェア開発技術者試験
       ▽ http://jstudy.fc2web.com/book/book_sofukai.html
     
        基本情報技術者試験
       ▽ http://jstudy.fc2web.com/book/book_fall_fe.html
     
        初級システムアドミニストレータ試験
       ▽ http://jstudy.fc2web.com/book/book_fall_SyokyuSysAdo.html

     





情報処理試験のお勉強(千里の道も一歩から) (マガジンID:0000120201)

メールアドレス:
メールアドレス:

Powered by まぐまぐ


メインメニューに戻る

SEO [PR] 住宅ローン フラワーギフト 必勝祈願 冷え対策 動画 無料レンタルサーバーSEO