課題一覧

以下を実行せよ。

  1. 最初に製品表、生産表、在庫表のデータのバックアップの存在を確認して(第3 回でバックアップを作成してあるはずである) テーブルとビューを(データではない) 削除しておく。また、サーバーPC(ZPC) のバックアップデータを使用し、BULK INSERT コマンドにより、リストアできるよう、データの存在するディレクトリを確認しておくこと。
    ※昔は以下のコマンドを利用することで、リストアできていたのだが、現在はセキュリティの関係によりできない。

    BULK INSERT <テーブル名> FROM '¥<サーバ名>¥<共有ディレクトリ名>¥<ファイル名>'
    WITH (CHECK CONSTRAINTS)

    ここで、WITH (CHECK CONSTRAINTS) は制約のチェックを行うためのオプションである。

  2. 以下の制約を付け、削除したテーブルを再定義し、BULK INSERT コマンドによって作り直したテーブルにデータを挿入せよ。
    (a) 各テーブルに主キーをつける(どれが主キーになるか自分で判断すること)。
    (b) 製品表condition は0 以上2 以下の数値であるという検査制約を付ける(数値を強調する必要はない)
    (c) 製品表prod_id はS で始まる二文字の文字列であるという検査制約を付ける(2 文字目は数字でなくても良い)。
    (d) 在庫表のstor_id は6 文字以内であるという検査制約を付ける(型から6 文字以内であることは確かだが検査制約でチェックがかかるように)
    (e) 生産表のprod_id は製品表のprod_id を参照しているという参照整合性制約をつける。
    (f) 在庫表のprod_id は製品表のprod_id を参照しているという参照整合性制約をつける。
    (g) 生産表のfact_address、在庫表のstor_address はNULL を許さない制約をつける。
  3. 製品表のprod_id がS7 のデータについて、price は未定だったためにNULL となっていたが、これを15 に確定した。データを変更せよ。
  4. 製品表の製品番号S1 のデータについて、製品番号をT1 に更新せよ。どういうメッセージが返るか?
  5. 製品表の製品番号S2 のデータについて状況を3 に更新せよ。どういうメッセージが返るか?
  6. 以下の2 つの場合それぞれについて、サーバが返すメッセージがどうか調べよ。
    (a) 製品表の製品番号S1 のデータを削除しようとした場合
    (b) 生産表、在庫表の製品番号S1 のデータ削除をした後に、製品表の製品番号S1 のデータを削除しようとした場合
  7. 在庫表に以下のデータを挿入せよ。どういうメッセージが返るか。

    解答一覧

    1.最初に製品表、生産表、在庫表のバックアップを確認しておいて(第三回でバックアップしたはず。)
    テーブルとビューを(データではない)削除しておく。
    また、サーバーPC(ZPC)のバックアップデータを使用し、BULK INSERTコマンドにより、リストアできるよう、
    データの存在するディレクトリを確認しておくこと。

    構文例:USE nagano_DB
    
    DROP TABLE seihin
    DROP TABLE seisan
    DROP TABLE zaiko

    2.以下の制約を付け、削除したテーブルを再定義し、BULK INSERT コマンドによっ

    作り直したテーブルにデータを挿入せよ。(各5点)

    (a)各テーブルに主キーをつける(どれが主キーになるか自分が判断する)

    構文例:USE nagano_DB
    
    CREATE TABLE seihin
    	(prod_id char(2) CONSTRAINT CEK_seihin CHECK(prod_id LIKE 'S_'),
    	name varchar(14),
    	type char(1),
    	price int,
    	condition smallint ,
    	CONSTRAINT PK_seihin PRIMARY KEY(prod_id),
    	CONSTRAINT CK_seihin CHECK(condition<=2 and condition>=0))
    
    
    
    CREATE TABLE seisan
    	(fact_id char(2),
    	prod_id char(2),
    	prod_qty int,
    	fact_address varchar(12) NOT NULL,
    	CONSTRAINT PK_seisan PRIMARY KEY(fact_id,prod_id),
    	CONSTRAINT FK_seisan FOREIGN KEY(prod_id) REFERENCES seihin)
    
    
    CREATE TABLE zaiko
    	(stor_id char(6),
    	prod_id char(2) CONSTRAINT FK_zaiko FOREIGN KEY(prod_id) REFERENCES seihin,
    	stoc_qty int,
    	safty_qty int,
    	stor_address varchar(12) NOT NULL,
    	CONSTRAINT PK_zaiko PRIMARY KEY(stor_id,prod_id),
    	CONSTRAINT CK_zaiko CHECK (LEN(stor_id)<=6))
    

    3.製品表のprod_idがS7のデータについて、priceは未定だったためNULLとなっていた
    が、
    これを15に確定した。データを変更せよ。

    構文例:USE nagano_DB
    
    UPDATE seihin SET price = 15 WHERE prod_id='S7'
    

    4.製品表の製品番号S1のデータについて、製品番号をT1に変更せよ。どういうメッセージが返ってくるか。

    構文例:USE nagano_DB
    
    UPDATE seihin SET prod_id = 'T1' WHERE prod_id='S7'
    

    5.製品表の製品番号S2のデータについて状況を3に変更せよ。どういうメッセージが返ってくるか。

    構文例:USE nagano_DB
    
    UPDATE seihin SET condition = 3 WHERE prod_id='S2'

    6.以下の2つの場合それぞれについて、サーバが返すメッセージがどうか調べよ。
    (a)製品表の製品番号S1のデータを削除しようとした場合

    構文例:USE nagano_DB
    DELETE FROM seihin WHERE prod_id='S1'

    (b)生産表、在庫表の製品番号S1のデータ削除をした後に、製品表の製品番号S1の削
    除をしようとした場合。

    構文例:USE nagano_DB
    SELECT prod_id,price FROM seihin WHERE name != 'Stereo'

    7.在庫表に以下のデータを挿入せよ。どういうメッセージが返ってくるか。

    構文例:USE nagano_DB
    
    DELETE FROM seisan WHERE prod_id='S1'
    DELETE FROM zaiko WHERE prod_id='S1'
    
    --その後、製品のS1削除--
    USE nagano_DB
    
    DELETE FROM seihin WHERE prod_id='S1'
    

    7.在庫表に以下のデータを挿入せよ。どういうメッセージが返ってくるか。

    構文例:USE nagano_DB
    
    INSERT INTO zaiko VALUES('W6EHM','S7',2,1,'Ehime')
    

    8.「3つのテーブルを削除し、主キー制約と参照整合性制約のみつけたテーブルを
    再作成した後にデータを挿入する」ようなスクリプトファイルreset.sqlをEmEditor

    エディタで作成し、sqlcmdによって実行せよ

    構文例: nagano_DB
    
    DROP TABLE seisan
    DROP TABLE zaiko
    DROP TABLE seihin
    
    CREATE TABLE seihin
    	(prod_id char(2),
    	name varchar(14),
    	type char(1),
    	price int,
    	condition smallint ,
    	CONSTRAINT PK_seihin PRIMARY KEY(prod_id))
    
    
    
    CREATE TABLE seisan
    	(fact_id char(2),
    	prod_id char(2),
    	prod_qty int,
    	fact_address varchar(12),
    	CONSTRAINT PK_seisan PRIMARY KEY(fact_id,prod_id),
    	CONSTRAINT FK_seisan FOREIGN KEY(prod_id) REFERENCES seihin)
    
    
    CREATE TABLE zaiko
    	(stor_id char(6),
    	prod_id char(2) CONSTRAINT FK_zaiko FOREIGN KEY(prod_id) REFERENCES seihin,
    	stoc_qty int,
    	safty_qty int,
    	stor_address varchar(12),
    	CONSTRAINT PK_zaiko PRIMARY KEY(stor_id,prod_id))
    
    BULK INSERT seihin FROM 'D:\SQL\seihin.txt' WITH (CHECK_CONSTRAINTS) BULK INSERT seisan FROM 'D:\SQL\seisan.txt' WITH (CHECK_CONSTRAINTS) BULK INSERT zaiko FROM 'D:\SQL\zaiko.txt' WITH (CHECK_CONSTRAINTS)

    9.以下の(b),(c)の場合それぞれについて、サーバーが返すメッセージがどうか調べ

    (a).生産表、製品表、在庫表の製品番号S1のデータを削除する
    (b).生産表、在庫表に対して、製品番号S1のデータを挿入する

    構文例:INSERT INTO seisan values('F1','S1',20,'TOKYO') INSERT INTO zaiko
    values('W6EHM','S1',2,1,'Ehime')

    (c).製品表に製品番号S1のデータを挿入し、その後に生産表、在庫表に製品番号S1の
    データを挿入する。

    構文例:INSERT INTO seihin values('S1','TV','A',200,0) INSERT INTO seisan
    values('F1','S1',20,'TOKYO') INSERT INTO zaiko
    values('W6EHM','S1',2,1,'Ehime')

    (b) 生産表、在庫表に対して、製品番号S1 のデータ(最初とおなじもの) を挿入する
    (各テーブルに
    ついてデータ1つでも良い。次も同様)。

    構文例:USE nagano_DB
    SELECT prod_id,price FROM seihin WHERE name != 'Stereo'

    10.前問で挿入したのと同じS1のデータを再度挿入せよ。
    どういうメッセージが返ってくるか。

    構文例:INSERT INTO seihin values('S1','TV','A',200,0)
    構文例:結果
    メッセージ 2627、レベル 14、状態 1、行 573
    制約 'PKey_sehin' の PRIMARY KEY 違反。オブジェクト 'dbo.seihin' には重複す
    るキーを挿入できません。重複するキーの値は (S1) です。
    ステートメントは終了されました。