1、數據庫綜合練習三習題一1 .現有一個局部應用，包括兩個實體：“出版社”和“作者”，這兩個實體是多對 多的聯系，請設計適當的屬性，畫出 E-R圖，再將其轉換為關系模型（包括關系名、 屬性名、碼和完整性約束條件）。2 .請設計一個圖書館數據庫，此數據庫中對每個借閱者保存的記錄包括：讀者號， 姓名，地址，性別，年齡，單位。對每本書保存有：書號，書名，作者，出版社。 對每本被借出的書保存有讀者號、借出日期和應還日期。要求：給出該圖書館數據 庫的E-R圖，再將其轉換為關系模型。3 .圖4-1是某個教務管理數據庫的 E-R圖，請把它們轉換為關系模型（圖中關系、 屬性和聯系的含義，已在它旁邊用漢字標出）。圖

2、4-1教學管理數據庫4 .圖4-2是一個銷售業務管理的 E-R圖，請把它轉換成關系模型。5 .設有一家百貨商店，已知信息有：l ）每個職工的數據是職工號、姓名、地址和他所在的商品部。2 ）每一商品部的數據有：它的職工，經理和它經銷的商品。3 ）每種經銷的商品數有：商品名、生產廠家、價格、型號（廠家定的）和內部商品代號（商店規定的）4）關于每個生產廠家的數據有：廠名、地址、向商店提供的商品價格。請設計該百貨商店的概念模型，再將概念模型轉換為關系模型。注意某些信息 可用屬性表示，其他信息可用聯系表示。6.下列有關E-R模型向關系模型轉換的敘述中。不正確的是 。A. 一個實體類型轉換為一個關系模式B

3、. 一個1:1聯系可以轉換為一個獨立的關系模式，也可以與聯系的任意一端實體 所對應的關系模式合并C. 一個1:n聯系可以轉換為一個獨立的關系模式，也可以與聯系的任意一端實體 所對應的關系模式合并D. 一個m n聯系轉換為一個關系模式習題一解答1 .答：轉換后的關系模型如圖 6所示。出版社（出版社名,地址，郵政編碼）；作者（姓生性別，年齡，證件號碼，單位）出版（出版社名，作者姓名，出書數量，聯系方式）圖62 .答：該圖書館數據庫的E-R圖如圖7圖7其中：讀者：讀者號，姓名,地址，性別，年齡，單位；圖書.書呈書名，作者，出版社。轉換后的關系模型為：借閱者（讀者號，姓名,地址，性別，年齡，單位）書籍

4、（一書名，作者，出版社）；借閱（讀者號，書號，借出日期,應還日期）。3答：轉換后的關系模型如下：Student （SNQ SN SR SA ;Teacher(TNO, TN, TD, TG ;Course (CN。CN PCNQ;ST （SN。TN。;SC（SNQ CN。G）;TC （TN。CN。4 .答：轉換后的關系模型如下工廠（匚紜廠長，地址）；產品（編出型號，單價）；用戶（心地址，電話）；工廠-產品-用戶（廠名，編號，姓名）,5 .答：概念模型如圖8所示。關系模型為：職工（職工號、姓名、住址，工作商品部）商品部（商品部號、名稱、經理職工號，經理名）商品（商品代號，價格,型號，出廠價格）生

5、產廠家（廠名，地址）銷售（商品代號，商品部號）生產（匚區商品代號）。圖8百貨商店E-R圖模型圖中：職工：職工號,姓名，住址；商品部：商品部號，名稱;商品：商品代號，價格,型號，出廠價格;生產廠家：廠名，地址。6 .答：D習題二1.設職工社團數據庫有三個基本表：職工（職工號，姓名，年齡，性別）；社會團體（編號，名稱，負責人，活動地點）參加（職工號，編號，參加日期）。其中:1 ）職工表的主碼為職工號2 ）社會團體表的主碼為編號；外碼為負責人，被參照表為職工表，對應屬性為 職工號。3 ）參加表的職工號和編號為主碼；職工號為外碼，其被參照表為職工表，對應 屬性為職工號；編號為外碼，其被參照表為社會團體

6、表，對應屬性為編號。試用SQL語句表達下列操作：l ）定義職工表、社會團體表和參加表，并說明其主碼和參照關系。4 ）建立下列兩個視圖。社團負責人（編號，名稱，負責人職工號，負責人姓名，負責人性別）；參加人情況（職工號，姓名，社團編號，社團名稱，參加日期）5 ）查找參加唱歌隊或籃球隊的職工號和姓名。6 ）查找沒有參加任何社會團體的職工情況。7 ）查找參加了全部社會團體的職工情況。8 ）查找參加了職工號為“ 1001”的職工所參加的全部社會團體的職工號。9 ）求每個社會團體的參加人數。10 ）求參加人數最多的社會團體的名稱和參加人數。11 ）求參加人數超過100人的社會團體的名稱和負責人。10）把

7、對社會團體和參加兩個表的數據查看、插入和刪除數據的權力賦給用戶李 平，并允許他再將此權力授予其他用戶。2. SQL語言集數據查詢、數據操作、數據定義和數據控制功能于一體，語句INSERTDELETE UPDAT凌現下歹U哪類功能。A.數據查詢B.數據操縱C.數據定義D.數據控制3.下面列出的關于視圖（View）的條目中，不正確的是 oA.視圖是外模式B.視圖是虛表C. 使用視圖可以加快查詢語句的執行速度D. 使用視圖可以簡化查詢語句的編寫4.在SQL語言的SELECTS句中，能實現投影操作的是。A. SELECTB. FROMC. WHERED. GROUP BYALTER5. SQL語言集數

8、據查詢、數據操縱、數據定義和數據控制功能于一體，語句TABLE實現哪類功能A.數據查詢B.數據操縱C.數據定義D.數據控制6 .在關系數據庫系統中，為了簡化用戶的查詢操作，而又不增加數據的存儲空間，常用的方法是創建 0A.另一個表B.游標C. 視圖D.索引7 .設要建立學生選課數據庫，庫中包括學生、課程和選課3個表，其表結構為:學生（學號，姓名，性別，年齡，所在系）；課程（課程號，課程名，先行課），選課（學號，課程號，成績）。用Transact-SQL完成下列操作。l ）建立學生選課庫。2 ）建立學生、課程和選課表。3 ）建立各表以主碼為索引項的索引。4 ）建立學生性別只能為“男”或“女”的規

9、則，性別為“男”的缺省，并將它 們綁定在學生表上。5 ）建立在對選課表輸入或更改數據時，必須服從參照完整性約束的INSERT和UPDAT觸發器。6 ）建立在刪除學生記錄時，同時也要把相應的選課記錄刪除的觸發器。7 ）查詢各系及學生數，最后來出共有多少系和多少學生。8 ）將學生表和選課表進行內連接、左外連接和右外連接。9 ）列出學生學號、姓名及學習情況。學習情況用好、較好、一般或較差表示。當平均成績大于85分時，學習情況為好；當平均成績在7085分之間，學習情況為較好；當平均成績在 6070分之間，學習成績為一般；平均成績在60分以下的為學習情況較差。習題二解答1） 答：2） CREAT TAB

10、LED工（職工號 CHAR（10 ） NOT NULL UNIQUE姓名 CHAR（8） NOT NULL年齡 SMALLINT性另U CHAR(2)CONSTRAINT C1 CHECK捌 IN （男，女）；CREAT TABLED會團體（編號 CHAR（8） NOT NULL UNIQUE名稱 CHAR（12） NOT NULL負責人CHAR （8）,活動地點VARCHAR50）,CONSTRAINC2FOREIGNKEY（負責人）REFERENCES工（職工號）;CREAT TABLED （職工號 CHAR（8）,編號 CHAR（8）,參加日期DATECONSTRAINT C3 PRIM

11、ARY KEY；HX<,編號），CONSTRAIC3FOREIGNKEY（職工號）REFERENCES工（職工號）;3） CREAT VIEW社團負責人（編號，名稱，負責人職工號，負責人姓名，負責 人性別）AS SELECTS號，名稱，負責人，姓名，性別FROMi會團體，職工WHERE會團體，負責人=職工.職工號；CREAT VIEW1加人情況（職工號，姓名，社團編號，社團名稱，參加日期）AS SELEC慘力口.職工號，姓名，社會團體編號，名稱，參加日期FROM只工，社會團體，參加WHERBRX.耳R工號=參加.職工號 AND參力口.編號二社會團體.編 號；4） ) SELEC屈工號，姓

12、名FROM職工，社會團體，參加WHERE工.職工號=參加.職工號AND#力口.編號二社會團體.編號ANDtt會團體.名稱IN ('唱歌隊，籃球隊)5） SELECT*FROMHRXWHERE NOT EXIST SSELECT*FRO修力口WHEREAT 職工號=職工.職工號)；6） SELECT*FROMR 工WHERE NOT EXIST SSELECT*FRO修力口WHERE NOT EXISTS(SELECT*FROMt會團體WHERE加.職工號=職工.職工號 AND參加.編號二社會團體.編號）；7） SELEC屈工號FROMR工WHERE NOT EXIST SSELECT*

13、FRO修加參加1WHERE力口 1.職工號=1001' AND NOT EXISTS（SELECT*FRO修加參力口 2WHER參加2.編號二參加1 .編號AND參加2.職工號一職工.職工號）;8） SELECT TCOUN碎工號）FRO唯力口GROUP B編號；2 .答：B3 .答：C4 .答：A5 .答：C6 .答：C7 .答：l ) CREATE DATABASE生選課庫ON PRIMARY NANE學生選課庫，FILENAME= ' C:學生選課.mdfSIZE=4MBMAXSIZ丘 6MBFILEROWHT=2M BGO2) CREAT TABLED (學號 CHAR

14、1Q PRIMARY KEY CLUSTERED姓名 CHAR(8),性另U CHAR ( 2),年齡 SMALLINT所在系 VARCHAR50)GOCREAT TABLE?® (課程號 CHAR(10) PRIMARY KEY CLUSTERED課程名 VARCHAR50),先行課 VARCHAR50）GOCREATE TABLE課（學號 CHAR （10）,課程號 VARCHAR50）,成績 SMALLINTCONSTRAINT C1 PRIMARY KE（號，課程號）,CONSTRAINT C2 FOREIGN KE（Y號）REFRENCES生（學號）,CONSTRAINT

15、FOREIGN KEt程號）REFERENCES程（課程號）GO3) CREATE INDEX student_ind ON 學生（學號）GOREATE* 巨 class_ind ON 課程（課程號）GOCREATE INDEX select_ind ON 選課（學號，課程號）GO4) CREAT RULE value_rule AS VALUE IN ('男'，'女')GOCREAT DEFAULT另瞅省 ASM'GOEXEC sp_bindrule 'value_rule'學生.性別GOEXEC Sp_binddefault '

16、;性別缺省，學生.性另GO5) CREATE TRIGGER sc_insert ON 選課FOR INSERTAS IF ( SELECT COLJNT*)FRO游生，inserted ,課程WHERE生.學號=inserted .學號 AN醴程.課程號=inserted .課程號)=0ROLLBACK TRANSACTIONGOCREATE TRIGGER sc_updat6 O隨課FOR UPDATEAS IF ( SELECT COUINT*)FRO游生，updated ,課程WHERE生.學號二updated .學號AND課程.課程號=updated .課程號)=0ROLLBACK

17、TRANSACTIONGO6) CREATE TRIGGER delete_all ON 學生FOR DELETEAS DELETEFRO隨課WHER選課.學號=deleted .學號GO7) SELECTS在系，COUN悻號)FRO陣生GROUP B浮生.所在系COMPUTE COUNDISTINCT （所在系）COMPUTE COUNTS BY 所在系GO8) SELECT*FRO陣生INNER JOIN課程GOSELECT*FRO蚱生 LEFT OUTER JOINfK程GOSELECT*FRO蚱生 RIGHT OUTER JOINWGO9) SELECTS課.學號，學生.姓名,學習情況

18、=CASEWHEN AV破績）=85 THEN 好'WHEN AV破績）=75 AND AVG假績） 85 THEN 較好WHEN AVG（績）v 60 THEN 較差ENDFRO蚱生選課WHERE生.學號=選課.學號GROUP B選課.學號GO習題三1. 設關系模式 RA B, C, D函數依賴集 F= AfC, C- A, B” AC £ ACBD A o1 ）求出R的候選碼。2 ）求出F的最小函數依賴集。3 ）將R分解為3NF,使其既具有無損連接性又具有函數依賴保持性2. 設關系模式 R<A B, C, D, E, F>,函數依賴集 F= ABE, ACHF

19、, ADHB, Bf C, 5 D。1 ）證明AB AC AD均是候選關鍵宇。2 ）證明主屬性C部分依賴于關鍵字 AB,傳遞依賴于AD同時證明主屬性 D部 分依賴于關鍵字AC,傳遞依賴于關鍵字ABo3 .設關系模式 R<A B, C, D, E, F> 函數依賴集 F= ABfE, BJD, BDC,CAB, C+AF, CFBD CA, D-EF,求F的最小函數依賴集。4 .判斷下面的關系模式是不是 BCNF為什么1 ）任何一個二元關系。2 ）關系模式選課（學號，課程號，成績），函數依賴集F= （學號，課程號） f成績。3 ）關系模式 R （A, B, C, D, E, F）,函

20、數依賴集 F=A"BC BJA, BCI>EF,C o5. 設關系模式 R（A, B, C, D, E, F）,函數依賴集 F=AfB, C-F, A, C+A,將R分解為P= ABE CDEFo判斷p是否是無損連接。6. 設關系模式 RB,O,I , S,Q.D,函數依賴集 F=SfD,I “S,IS"QB”Q。l ）找出R的主碼2 )把R分解為BCNF且具有無損連接性7. 在關系模式選課(學號，課程號，成績)中， “學號”課程號”正確嗎為什么8. 設有關系模式 R (A, B, C),數據依賴集F=ABfC, C"”A, R屬于第幾范式為什么9. 設有關

21、系模式 R (A, B, C, D),數據依賴集 F=AfB, B"A, ACD, BCD,A£C, B£C, 2f CD Bf f CD。1 )求R的主碼。2 ) R是否為第4范式為什么3 ) R是否是BCN叨什么4 ) R是否是3NF為什么習題三解答1 .答：1) R的候選碼為BQ2)將F中的函數依賴都分解為右部為單屬性的函數依賴。F= QC, rA, BfA, Bf C, >A, >C, B£A去掉F中冗余的函數依賴判斷A-C是否冗余。設：G1= 5A, BfA, EHC, >A, >C, BCHA,得：（a）gi=AV C

22、 （ a）gi，Af C不冗余判斷C-A是不冗余。設：G2= QC, BfA, BC, >A, >C, BCHA,得：（C）G2=CA （C）gi，CfA不冗余判斷B-A是否冗余。設：G3= QC, CfA, BC, >A, >C, BCHA,得：（b）g3=bCAA （ B J. BA 冗余判斷B-C是否冗余。設：G4 QC, CfA, DfA, rC, BCHA, 得：（b）g，=bV C（ B ）g 4.Bf C不冗余判斷>A是否冗余設：G5= QC, CfA, BfC, rC, BDHA, 得：(D)g5=DCA. A( D )g5r A不冗余判斷C是否冗

23、余。設：G6= Q C, r A,C ( D ) G 6判斷BA A是否冗余。設：G7= Q C, r A,. A( BD )g7BC, BDA,得:C不冗余BC, rC,得: BD A冗余(BD ) G 7 =BDCAF= Af C, 5A, Bf C, r C由于各函數依賴在部都為單屬性.故:Fm=A C, C” A, BfC, D- C。3) T= AC, BC, DC, BD2 .答：AB為碼1); (ab)f= ABECDF ABCDEF (AB)f( AC )F = ABECDF ABCDEE (AC )f .AC為碼(AD )F = ABECDF ABCDEE (AD )f .A

24、D為碼部分2) v BCAB C傳拂V ADB, B"C .'.AD C部分- CD.AC CV BC, SD /.AB 傳遞 C3 .答：將F中的函數依賴都分解為右部為單屬性的函數依賴。F= ABE, B8D, BEC, CAB, CEA, CDF, CFB, CFD, CA,D E, rF去掉F中冗余的函數依賴。判斷A4E是否冗余。設：G1= BCf D, BXC, CAB, CD A, CDF, CAB, CDD, SA,E, >F得：（AB）G1=AB E （A）G1，AEH E 不冗余判斷BC- D是否冗余。設：G2= ABf E, BEC, CDHB , C

25、EA, CD F, CF B, CD D, 5A,Df E, >F得：（BC K=BCAEFDDC （BC）G2.-.BCD冗余判斷BEC是否冗余。設：G3= ABf E, CDB,CEf A , CEF, CDB, CDD, CA, £E,F得：（BE）g3=BEV C （BE）g3/.BEC不冗余判斷CD- B是否冗余。設：G4=AB7E,BEf C,C;A ,CEF, CDB, CDD, 8A, >E, 毆F得：（CD ）G4=CDAEFBBG (CD)。,判斷CEE-A是否冗余。設：G5=AB7E, BbC, CbF, CFB, CDD, CA, >E,F得

26、：(CE )g5=CEFBDAAG (CE)G5，C+ A冗余判斷CDF是否冗余。設：G6=ABfE, BEC, CFB, CFD, CA, >E, A F得：(CE)G6=CEA F (CE)G6.cd F 不冗余判斷CF- B是否冗余。設：G7=ABfE, BEC, CEF, CFD, CA, >E, AF得：(CF)G7=CFDEF B (CF)G7，C曰B不冗余判斷CD D是否冗余設：G8=AB7E, BbC, CbF, CFB,。A, >E, AF得：(CF )G8=CFABEd (CF )g8CD D不冗余判斷C-A是否冗余。設：G9= ABf E, BE C,C

27、EF, CFB, CDD, >E, >F得：（C）g9=C A (C )G9Cf A不冗余判斷D- E是否冗余。設：G10= AB7E,BE C, C+ F, CD B, CD D, 8A, > F得：(D )G10 =DFE (D )gi0判斷D- F是否冗余。設：G11= AB7E,BE C, C+ F, CD B, CD D, 8A, > 日得：(D ) g 11 =DE，F= AB7E, BDC, CEF, CDB, CDD, CA, AE, >F求得.Ff=FD Ff不能以F"D代替CDD在決定因素中去掉Fo求得：G=CAD Cp不能以r D代

28、替CF D不能以CDD不冗余F= AB7E, BDC, CDF, CDB, CFD, 5A, >E, AF4答：l）是BCNF二元關系中或為全碼，或為一個單屬性碼候選碼。2）是BCNF關系模式中只有一個候選碼。3）不是BCNF因為模式中存在候選碼為 AD BCM BE顯然C對AD是部分依 賴。5答:i-U2=ABU U=EU 1 Uf U U=Ef AB=Ef A,卜 BU i U2Ui-Ub F該分解具備無損連接。6答：1) R的主碼為舊O。2) F= Sf D, IS, 1fQ Bf Q令 P= BOISQD由于R的碼為舊O.選擇S”D分解。得出： =Si, S2其中 Si=SD,

29、Fi=SD;S2=BOISQ F2= IfS, I-Q, B Q0顯然S2不服從BCNF需要繼續分解：對Si分解，S的碼為 舊O,選擇I"S分解 得出： =Si, S2, S3其中：$=IS. F3= ISS 4=BOIQ F4= I f Q, Bf Q顯然.0不服從BCNF還需要繼續分解。對S4分解。S4的碼為 舊O,選擇I" Q分解。得出：=Sl, S3, S5,S6o其中 S5=IQ, F5= I"Q；S 6 = BIO, F6 = o最后的分解為：= SD, IS. IQ. BIO o7答：正確。因為學號能夠多值決定課程號。8答：BCNF由于A多值依賴于動

30、 而C不是碼.故不服從 4NF。但在函數依賴式中 C 依賴于碼AB.故該模式服從 BCNF9答：l ）候選碼為 AC BC. AD, BD可選其中之一為主碼。2 ）不服從4NF。在多值依賴中 泱定因素中不包含碼。3 ）不服從BCNF在函數依賴中決定因素中不包含碼。4 ）服從3NF。該模式中不存在非主屬性。習題四1 .今有兩個關系模式：職工（職工號，姓名，年齡，職務，工資，部門號） ；部門（部門號，名稱，經理名，地址，電話） 。請田SQL的GRAN下口 REVOK踣句（加上視圖機制）,完成以下授權定義或存取 控制功能。1 ）用戶王明對兩個表有 SELECTOR2 ）用戶李勇對兩個表有 INSER