河北工業(yè)大學(xué)數(shù)據(jù)庫(kù)原理及應(yīng)用實(shí)驗(yàn)實(shí)驗(yàn)報(bào)告

1、數(shù)據(jù)庫(kù)原理及應(yīng)用實(shí)驗(yàn)實(shí)驗(yàn)報(bào)告班級(jí)：網(wǎng)絡(luò) 151姓名：徐毅民學(xué)號(hào)： 153299實(shí)驗(yàn) 1 數(shù)據(jù)庫(kù)定義與操作語(yǔ)言實(shí)驗(yàn)實(shí)驗(yàn) 1.4 數(shù)據(jù)更新實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康?熟悉數(shù)據(jù)庫(kù)的數(shù)據(jù)更新操作，能夠使用 SQL 語(yǔ)句對(duì)數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)的插入、刪除、修改操 作。2、實(shí)驗(yàn)內(nèi)容和要求針對(duì) TPC-H 數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)單元組插入、 批量數(shù)據(jù)插入、 修改數(shù)據(jù)和刪除數(shù)據(jù)等 SQL 語(yǔ)句。 理解和掌握 INSERTT 、UPDATE 、和 DELETE 語(yǔ)法結(jié)構(gòu)的各個(gè)組成成分，結(jié)合嵌套 SQL 子 查詢，分別設(shè)計(jì)幾種不同形式的插入、修改和刪除數(shù)據(jù)的語(yǔ)句，并調(diào)試成功。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn) 實(shí)驗(yàn)重點(diǎn)：插入、修改和刪除數(shù)據(jù)的 SQL

2、實(shí)驗(yàn)難點(diǎn)：與嵌套 SQL 子查詢相結(jié)合的插入、修改和刪除數(shù)據(jù)的 SQL 語(yǔ)句；利用一個(gè)表的 數(shù)據(jù)來(lái)插入、修改和刪除另一個(gè)表的數(shù)據(jù)。4、實(shí)驗(yàn)報(bào)告示例(1) INSERT 基本語(yǔ)句 (插入全部列的數(shù)據(jù) ) 插入一條顧客記錄，要求每列都給一個(gè)合理的值。INSERT INTO CustomerVALUES (30,' 張三 ','北京市 ',40,'#39;,0.00,'Northeast','VIP Customer');(2) INSERT 基本語(yǔ)句(插入部分列的數(shù)據(jù)) 插入一條訂單記錄，給出必要的幾

3、個(gè)字段值。INSERT INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate) VALUES(862,ROUND(RANDOM()*100,0,479,1,10,'2012-3-6');/*RANDOM() 函數(shù)為隨機(jī)小數(shù)生成函數(shù)， ROUND() 為四舍五入函數(shù) */(3) 批量數(shù)據(jù) INSERT 語(yǔ)句 創(chuàng)建一個(gè)新的顧客表，把所有中國(guó)籍顧客插入到新的顧客表中。INSERT INTO NewCustomer/* 批量插入 SELECT 語(yǔ)句查詢結(jié)果到 NewCustomer 表中 */ SELEC

4、T C.*FROM Costomer C,Nation NWHERE C.nationkey=N.nationkey AND N.name=' 中國(guó) ' 創(chuàng)建一個(gè)顧客購(gòu)物統(tǒng)計(jì)表，記錄每個(gè)顧客及其購(gòu)物總數(shù)和總價(jià)等信息。CREATE TABLE ShoppingStat(custkey INTEGER,quantity REAL,totalprice REAL);INSERT INTO ShoppingStatSELECT C.custkey,Sum(L.quantity),Sum(O.totalprice) /* 對(duì)分組后的數(shù)據(jù)求總和 */FROM Customer C,Orde

5、r O,Lineitem LWHERE C.custkey=O.custkey AND O.orderkey=L.orderkeyGROUP BY C.custkey 倍增零件表的數(shù)據(jù)，多次重復(fù)執(zhí)行，直到總記錄數(shù)達(dá)到50萬(wàn)為止。INSERT INTO PartSELECT partkey+(SELECT COUNT(*) FROM Part), name,mfgr,brand,type,size,container,retailprice,commentFROM Part;(4) UPDATE 語(yǔ)句(插入部分記錄的部分列值)“金倉(cāng)集團(tuán) ”供應(yīng)的所有零件的供應(yīng)成本價(jià)下降 10%。UPDATE P

6、artSuppSET supplycost=supplycost*0.9WHERE suppkey=(SELECT suppkey/*找出要修改的那些記錄 */FROM SupplierWHERE name=' 金倉(cāng)集團(tuán) ');(5) UPDATE 語(yǔ)句(利用一個(gè)表中的數(shù)據(jù)修改另外一個(gè)表中的數(shù)據(jù))利 用 Part 表 中 的 零 售 價(jià) 格 來(lái) 修 改 Lineitem 中 的 extendedprice, 其 中 extendedprice=Part.retailprice*quantity 。UPDATE Lineitem LSET L.extendedprice=P.re

7、tailprice*L.quantityFROM Part PWHERE L.partkey=P.partkey;/*Lineitem 表也可以直接與 Part 表相連接，而不需通過(guò) PartSupp 連接 */（6）DELETE 基本語(yǔ)句（刪除給定條件的所有記錄） 刪除顧客張三的所有訂單記錄。DELECT FROM Lineitem /* 先刪除張三的訂單明細(xì)記錄 */WHERE orderkey IN（SELECT orderkeyFROM Order O,Customer CWHERE O.custkey=C.custkey AND C.name=' 張三 '）;DELE

8、CT FROM Order /* 再刪除張三的訂單記錄 */WHERE custkey=（SELECT custkeyFROM CustomerWHERE name=' 張三 '）;實(shí)驗(yàn) 1.5 視圖實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康氖煜?SQL 語(yǔ)言有關(guān)視圖的操作， 能夠熟練使用 SQL 語(yǔ)句來(lái)創(chuàng)建需要的視圖， 定義數(shù)據(jù)庫(kù)外 模式，并能使用所創(chuàng)建的視圖實(shí)現(xiàn)數(shù)據(jù)管理。2、實(shí)驗(yàn)內(nèi)容和要求 針對(duì)給定的數(shù)據(jù)庫(kù)模式，以及相應(yīng)的應(yīng)用需求，創(chuàng)建視圖和帶 WITH CHECK OPTION 的視 圖，并驗(yàn)證視圖 WITH CHECK OPTION 選項(xiàng)的有效性。理解和掌握視圖消除執(zhí)行原理，掌 握可更新視圖和不

9、可更新視圖的區(qū)別。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn) 實(shí)驗(yàn)重點(diǎn)：創(chuàng)建視圖。 實(shí)驗(yàn)難點(diǎn)：可更新的視圖和不可更新的視圖之區(qū)別，WITH CHECK OPTION 的驗(yàn)證。4、實(shí)驗(yàn)報(bào)告示例（1）創(chuàng)建視圖（省略視圖列名）創(chuàng)建一個(gè) “海大汽配 ”供應(yīng)商供應(yīng)的零件視圖 V_DLMU_PartSupp1 ，要求列出供應(yīng)零件的編 號(hào)、零件名稱、可用數(shù)量、零售價(jià)格、供應(yīng)價(jià)格和備注等信息。CREATE VIEW V_DLMU_PARTSUPP1AS /*由 SELECT 子句目標(biāo)列組成視圖屬性 */SELECT P.partkey,P.name,PS.availqty,P.retailprice,PS.supplycost,P.

10、commentFROM Part P,PartSupp PS,Supplier SWHERE P.partkey=PS.partkey AND S.suppkey=PS.suppkey AND S.name=' 海大汽配 '(2) 創(chuàng)建視圖(不能省略列名的情況)創(chuàng)建一個(gè)視圖 V_CustAvgOrder, 按顧客統(tǒng)計(jì)平均每個(gè)訂單的購(gòu)買金額和零件數(shù)量， 要求輸出 顧客編號(hào)、姓名，平均購(gòu)買金額和平均購(gòu)買零件數(shù)量。CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgquantity) ASSELECT C.custkey,MAX(

11、C.name),A VG(O.totalprice),A VG(L.quantity)FROM Customer C,Orders O,Lineitem LWHERE C.custkey=O.custkey AND L.orderkey=O.orderkeyGROUP BY C.custkey;(3) 創(chuàng)建視圖( WITH CHECK OPTION )使 用 WITH CHECK OPTION ， 創(chuàng) 建 一 個(gè) “海 大 汽 配 ”供 應(yīng) 商 供 應(yīng) 的 零 件 視 圖 V_DLMU_PartSupp2 ，要求列出供應(yīng)零件的編號(hào)、可用數(shù)量和供應(yīng)價(jià)格等信息。然后通過(guò)該 視圖分別增加、刪除和修改

12、一條 “海大汽配 ”零件供應(yīng)記錄，驗(yàn)證 WITH CHECK OPTION 是 否起作用。CREATE VIEW V_DLMU_PartSupp2ASSELECT partkey,suppkey,availqty,supplycostFROM PartSuppWHERE suppkey=(SELECT suppkeyFROM SupplierWHERE name=' 海大汽配 ')WITH CHECK OPTION;INSERT INTO V_DLMU_PartSupp2VALUES (58889,5048,704,77760); UPADTE V_DLMU_PartSupp2

13、SET supplycost=12WHERE suppkey=58889;DELETE FROM V_DLMU_PartSupp2WHERE suppkey=58889;（4）可更新的視圖（行列子集視圖）使 用 WITH CHECK OPTION ， 創(chuàng) 建 一 個(gè) “海 大 汽 配 ”供 應(yīng) 商 供 應(yīng) 的 零 件 視 圖 V_DLMU_PartSupp4 ，要求列出供應(yīng)零件的編號(hào)、可用數(shù)量和供應(yīng)價(jià)格等信息。然后通過(guò)該 視圖分別增加、 刪除和修改一條 “海大汽配 ”零件供應(yīng)記錄，驗(yàn)證該視圖是否是可更新的，并 比較上述 “（ 3）創(chuàng)建視圖 ”實(shí)驗(yàn)任務(wù)與本任務(wù)結(jié)果有何異同。CREATE VIEW

14、 V_DLMU_PartSupp3ASSELECT partkey,suppkey,availqty,supplycostFROM PartSuppWHERE suppkey=（SELECT suppkeyFROM SupplierWHERE name=' 海大汽配 '）;INSERT INTO V_DLUM_PartSupp3VALUES（58889,5048,704,77760）;UPDATE V_DLMU_PartSupp3SET supplycost=12WHERE suppkey=58889;DELETE FROM V_DLMU_PartSupp3WHERE supp

15、key=58889;（5）可更新的視圖INSERT INTO V_CustAvgOrderVALUES（100000,NULL,20,2000）;（6）刪除視圖（ RESTRICT/CASCADE ）創(chuàng)建顧客訂購(gòu)零件明細(xì)視圖 V_CustOrd, 要求列出顧客編號(hào)、姓名、購(gòu)買零件數(shù)、金額，然后在該視圖的基礎(chǔ)上，在創(chuàng)建(2)的視圖 V_CustAvgOrder, 然后使用 RESTRICT 選項(xiàng)和CASCADE 選項(xiàng)刪除視圖 V_CustOrd 。CREATE VIEW V_CustOrd(custkey,cname,qty,extprice)ASSELECT C.custkey,C.name,

16、L.quantity,L.extendedpriceFROM Customer C,Order O,Lineitem LWHERE C.custkey=O.custkey AND O.orderkey=L.orderkey;CREATE VIEW V_CustAvgOrder(custkey,cname,avgqty,avgprice)ASSELECT custkey,MAX(cname),A VG(qty),A VG(extprice)FROM V_CustOrd/* 在視圖 V_CustOrd 上再創(chuàng)建視圖 */GROUP BY custkey;DROP VIEW V_CustOrd RE

17、STRICT;DROP VIEW V_CustOrd CASCADE;實(shí)驗(yàn) 1.6 索引實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康?掌握索引設(shè)計(jì)原則和技巧，能夠創(chuàng)建合適的索引以提高數(shù)據(jù)庫(kù)查詢、統(tǒng)計(jì)分析效率。2、實(shí)驗(yàn)內(nèi)容和要求針對(duì)給定的數(shù)據(jù)庫(kù)模式和具體應(yīng)用要求， 創(chuàng)建唯一索引、 函數(shù)索引、 復(fù)合索引等； 修改索引； 刪除索引。設(shè)計(jì)相應(yīng)的 SQL 查詢驗(yàn)證索引有效性。學(xué)習(xí)利用 EXPLAIN 命令分析 SQL 查詢 是否使用了所創(chuàng)建的索引，并能夠分析其原因，執(zhí)行 SQL 查詢并估算索引提高查詢效率的 百分比。要求實(shí)驗(yàn)數(shù)據(jù)集達(dá)到 10 萬(wàn)條記錄以上的數(shù)據(jù)量，以便驗(yàn)證索引效果。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn) 實(shí)驗(yàn)重點(diǎn)：創(chuàng)建索引。實(shí)驗(yàn)難點(diǎn)：

18、設(shè)計(jì) SQL 查詢驗(yàn)證索引有效性。4、實(shí)驗(yàn)報(bào)告示例1）創(chuàng)建唯一索引在零件表的零件名稱字段上創(chuàng)建唯一索引。CREATE UNIQUE INDEX Idx_part_nameON Part(name);(3) 創(chuàng)建復(fù)合索引(對(duì)兩個(gè)及兩個(gè)以上的屬性創(chuàng)建索引，稱為復(fù)合索引) 在零件表的制造商和品牌兩個(gè)字段上創(chuàng)建一個(gè)復(fù)合索引。CREATE UNIQUE INDEX Idx_part_mfgr_brand ON Part(mfgr,brand);(4) 修改索引名稱修改零件表的名稱字段上的索引名。ALTER INDEX Idx_part_name_hash RENAME TO Idx_part_name_

19、hash_new;(5) * 驗(yàn)證索引效率創(chuàng)建一個(gè)函數(shù) TestIndex, 自動(dòng)計(jì)算 sql 查詢執(zhí)行的時(shí)間。CREATE FUNCTION TestIndex(p_part_name CHAR(55) RETURN INTEGERAS/*自定義函數(shù)Testindex():輸入?yún)?shù)為零件名稱，返回SQL查詢的執(zhí)行時(shí)間*/DECLAREbegintime TiMESTAMP;endtime TiMESTAMP;durationtime iNTEGER;BEGNSELECT CLOCK_TiMESTAMP() iNTO begintime;/*記錄查詢執(zhí)行的開(kāi)始時(shí)間*/PERFORM *FROM

20、 Part WHERE name=p_partname; /*執(zhí)行 SQL 查詢，不保存查詢 結(jié)果 */SELECT CLOCK_TiMESTAMP() iNTO endtime;SELECT DATEDiFF(,ms?,begintime,endtime) iNTO durationtime;RETURN durationtime;/*計(jì)算并返回查詢執(zhí)行時(shí)間，時(shí)間單位為毫秒 ms*/END;/*查看當(dāng)零件表Part數(shù)據(jù)模型比較小，并且無(wú)索引時(shí)的執(zhí)行時(shí)間*/SELECT Testindex(, 零件名稱 ?);iNSERT iNTO Part/*不斷倍增零件表的數(shù)據(jù)，直到 50 萬(wàn)條記錄 */

21、SELECT partkey+(SELECT COUNT(*) FROM Part),Name,mfgr,brand,type,size,container,retailprice,commentFRPM Part;/*查看當(dāng)零件表 Part 數(shù)據(jù)模型比較大，但無(wú)索引時(shí)的執(zhí)行時(shí)間 */SELECT TestIndex(, 零件名稱 ?);CREATE INDEX part_name ON Part(name);/* 在零件表的零件名稱字段上創(chuàng)建索引 */*查看零件表 Part 數(shù)據(jù)規(guī)模比較大，有索引時(shí)的執(zhí)行時(shí)間*/SELECT TestIndex();實(shí)驗(yàn) 2 安全性語(yǔ)言實(shí)驗(yàn)實(shí)驗(yàn) 2.1 自主

22、存取控制實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康?掌握自主存取控制缺陷的定義和維護(hù)方法。2、實(shí)驗(yàn)內(nèi)容和要求 定義用戶、角色，分配權(quán)限給用戶、角色，回收權(quán)限，以相應(yīng)的用戶名登錄數(shù)據(jù)庫(kù)驗(yàn)證權(quán)限 分配是否正確。 選擇一個(gè)應(yīng)用場(chǎng)景， 使用自主存取控制機(jī)制設(shè)置權(quán)限分配。 可以采用兩種方 案。方案一：采用 SYSTEM 超級(jí)用戶登錄數(shù)據(jù)庫(kù)，完成所有權(quán)限分配工作，然后用相應(yīng)用 戶名登錄數(shù)據(jù)庫(kù)已驗(yàn)證權(quán)限分配正確性；方案二：采用 SYSTEM 用戶登錄數(shù)據(jù)庫(kù)創(chuàng)建 3 個(gè)部門經(jīng)理用戶，并分配相應(yīng)的權(quán)限， 然后分別用 3 個(gè)經(jīng)理用戶名登錄數(shù)據(jù)庫(kù)，創(chuàng)建相應(yīng)部門的USER、ROLE ，并分配相應(yīng)權(quán)限。下面的實(shí)驗(yàn)報(bào)告示例采用了實(shí)驗(yàn)方案一。驗(yàn)證權(quán)

23、限分配之前，請(qǐng)備份好數(shù)據(jù)庫(kù)，針對(duì)不 同的用戶所具有的權(quán)限，分別設(shè)計(jì)相應(yīng)的 SQL 語(yǔ)句加以驗(yàn)證。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn) 實(shí)驗(yàn)重點(diǎn)：定義角色，分配權(quán)限和回收權(quán)限。 實(shí)驗(yàn)難點(diǎn)：實(shí)驗(yàn)方案二實(shí)現(xiàn)權(quán)限的再分配和回收。4、實(shí)驗(yàn)報(bào)告示例（1）創(chuàng)建用戶O為采購(gòu)、銷售和客戶管理等3個(gè)部門的經(jīng)理創(chuàng)建用戶標(biāo)識(shí)，要求具有創(chuàng)建用戶或角色的權(quán)利。CREATE USER David WITH CREATEROLE PASSWORD '123456'CREATE USER Tom WITH CREATEROLE PASSWORD '123456'CREATE USER Kathy WITH CRE

24、ATEROLE PASSWORD '123456'?為采購(gòu)、銷售和客戶管理等3個(gè)部門的職員創(chuàng)建用戶標(biāo)識(shí)和用戶口令。CREATE USER Jeffery WITH PASSWORD '123456'CREATE USER Jane WITH PASSWORD '123456'CREATE USER Mike WITH PASSWORD '123456'（2）創(chuàng)建角色并分配權(quán)限（為各個(gè)部門分別創(chuàng)建一個(gè)查詢角色，并分配相應(yīng)的查詢權(quán)限。CREATE ROLE PurchaseQueryRole;GRANT SELECT ON Part

25、TO PurchaseQueryRole;GRANT SELECT ON Supplier TO PurchaseQueryRole;GRANT SELECT ON PartSupp TO PurchaseQueryRole;CREATE ROLE SaleQueryRole;GRANT SELECT ON Order TO SaleQueryRole;GRANT SELECT ON Lineitem TO SaleQueryRole;CREATE ROLE CustomerQueryRole;GRANT SELECT ON Customer TO CustomerQueryRole;GRAN

26、T SELECT ON Nation TO CustomerQueryRole;GRANT SELECT ON Region TO CustomerQueryRole;為各個(gè)部門分別創(chuàng)建一個(gè)職員角色，對(duì)本部門信息具有查看、插入權(quán)限。CREATE ROLE PurchaseEmployeeRole;GRANT SELECT ， INSERT ON Part TO PurchaseEmployeeRole;GRANT SELECT ， INSERT ON Supplier TO PurchaseEmployeeRole;GRANT SELECT ， INSERT ON TABLE PartSupp

27、 TO PurchaseEmployeeRole;CREATE ROLE SaleEmployeeRole;GRANT SELECT ， INSERT ON Order TO SaleEmployeeRole;GRANT SELECT ， INSERT ON Lineitem TO SaleEmployeeRole;CREATE ROLE CustomerEmployeeRole;GRANT SELECT ， INSERT ON Customer TO CustomerEmployeeRole;GRANT SELECT ， INSERT ON Nation TO CustomerEmploye

28、eRole;GRANT SELECT ， INSERT ON Region TO CustomerEmployeeRole;為各個(gè)部門創(chuàng)建一個(gè)經(jīng)理角色，相應(yīng)角色對(duì)本部門的信息具有完全控制權(quán)限，對(duì)其他部門 的信息具有查詢權(quán)。經(jīng)理有權(quán)給本部門資源分配權(quán)限。CREATE ROLE PurchaseManagerRole WITH CREATEROLE ；GRANT ALL ON Part TO PurchaseManagerRole ；GRANT ALL ON Supplier TO PurchaseManagerRole ；GRANT ALL ON PartSupp TO PurchaseMana

29、gerRole ；GRANT SaleQueryRole TO PurchaseManagerRole ；GRANT CustomerQueryRole TO PurchaseManagerRole ；CREATE ROLE SaleManagerRole WITH CREATEROLE ；GRANT ALL ON Order TO SaleManagerRoleGRANT ALL ON Lineitem TO SaleManagerRoleGRANT SaleQueryRole TO SaleManagerRoleGRANT PurchaseQueryRole TO SaleManagerR

30、oleCREATE ROLE CustomerManagerRole WITH CREATEROLE ；GRANT ALL ON Customer TO CustomerManagerRoleGRANT ALL ON Nation TO CustomerManagerRoleGRANT ALL ON Region TO CustomerManagerRoleGRANT SaleQueryRole TO CustomerManagerRoleGRANT PurchaseQueryRole TO CustomerManagerRole（3）給用戶分配權(quán)限仔合部門經(jīng)理分配權(quán)限。GRANT Purch

31、aseManagerRole TO David WITH ADMIN OPTION;GRANT SaleManagerRole TO Tom WITH ADMIN OPTION;GRANT CustomerManagerRole TO Kathy WITH ADMIN OPTION;彰合各部門職員分配權(quán)限GRANT PurchaseEmployeeRole TO Jeffery;GRANT SaleEmployeeRole TO Jane;GRANT CustomerEmployeeRole TO Mike;（4）回收角色或用戶權(quán)限創(chuàng)攵回客戶經(jīng)理角色的銷售信息查看權(quán)限。REVOKE SaleQ

32、ueryRole FROM CustomerManagerRole;空回收MIKE的客戶部門職員權(quán)限。REVOKE CustomerEmployeeRole FROM Mike;（5）驗(yàn)證權(quán)限分配正確性C以 David用戶名登錄數(shù)據(jù)庫(kù)，驗(yàn)證采購(gòu)部門經(jīng)理的權(quán)限SELECT * FROM Part;DELETE * FROM Order;g回收MIKE的客戶部門職員權(quán)限SELECT * FROM Customer;SELECT * FROM Part;實(shí)驗(yàn) 2.2 審計(jì)實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康?掌握數(shù)據(jù)庫(kù)審計(jì)的設(shè)置和管理方法，以便監(jiān)控?cái)?shù)據(jù)庫(kù)操作，維護(hù)數(shù)據(jù)庫(kù)安全。2、實(shí)驗(yàn)內(nèi)容和要求打開(kāi)數(shù)據(jù)庫(kù)審計(jì)開(kāi)關(guān)。 以具

33、有審計(jì)權(quán)限的用戶登錄數(shù)據(jù)庫(kù)， 設(shè)置審計(jì)權(quán)限， 然后以普通用戶 登錄數(shù)據(jù)庫(kù)，執(zhí)行相應(yīng)的數(shù)據(jù)操縱 sql 語(yǔ)句，驗(yàn)證相應(yīng)審計(jì)設(shè)置是否生效，最后在一具有審 計(jì)權(quán)限的用戶登錄數(shù)據(jù)庫(kù)，查看是否存在相應(yīng)的審計(jì)信息。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn)實(shí)驗(yàn)重點(diǎn)：數(shù)據(jù)庫(kù)對(duì)象級(jí)審計(jì)，數(shù)據(jù)庫(kù)語(yǔ)句級(jí)審計(jì)。實(shí)驗(yàn)難點(diǎn)： 合理地設(shè)置各種審計(jì)信息。一方面，為了保護(hù)系統(tǒng)重要的敏感數(shù)據(jù)，需要系統(tǒng)地 設(shè)置各種審計(jì)信息， 不能留有各種漏洞， 以便隨時(shí)監(jiān)督系統(tǒng)使用情況， 一旦出現(xiàn)問(wèn)題也便于 追查；另一方面，審計(jì)信息設(shè)置過(guò)多會(huì)嚴(yán)重影響數(shù)據(jù)庫(kù)的使用性能，因此需要合理配置。4、實(shí)驗(yàn)報(bào)告示例（1）審計(jì)開(kāi)關(guān)g顯示當(dāng)前審計(jì)開(kāi)關(guān)狀態(tài)SHOW AUDIT_TRAIL

34、;g打開(kāi)審計(jì)開(kāi)關(guān)SET AUDIT_TRAIL TO ON;（2）數(shù)據(jù)庫(kù)操作審計(jì)寸客戶信息表上的刪除操作設(shè)置審計(jì)。AUDIT DELETE ON Sales.Customer BY ACCESS;糾普通用戶登錄，執(zhí)行 sql語(yǔ)句。DELETE Sales.Customer WHERE custkey=1011;查看數(shù)據(jù)庫(kù)對(duì)象審計(jì)信息，驗(yàn)證審計(jì)設(shè)置是否生效。SELECT * FROM SYS_AUDIT_OBJECT;（3）語(yǔ)句級(jí)審計(jì)（J：對(duì)表定義的更改語(yǔ)句 ALTER設(shè)置審計(jì)AUDIT ALTER TABLE BY ACCESS;?查看所有數(shù)據(jù)庫(kù)所有語(yǔ)句級(jí)審計(jì)設(shè)置，驗(yàn)證審計(jì)設(shè)置是否生效SELE

35、CT * FROM SYS_STMT_AUDIT_OPTS;以普通用戶登錄，執(zhí)行 sql語(yǔ)句，驗(yàn)證審計(jì)設(shè)置是否生效ALTER TABLE Customer ADD COLUMN tt INT;查看所有審計(jì)信息SELECT * FROM SYS_AUDIT_TRAIL;做實(shí)四、實(shí)驗(yàn)心得 通過(guò)本次實(shí)驗(yàn)，知道了定義用戶、角色，分配權(quán)限給用戶、角色，回收權(quán)限，并以相應(yīng)的用 戶名登陸數(shù)據(jù)庫(kù)驗(yàn)證權(quán)限分配是否正確的方法。 并且知道了數(shù)據(jù)庫(kù)審計(jì)的目的和方法。驗(yàn)的同時(shí)，對(duì) sql 語(yǔ)句有了更熟練的運(yùn)用。實(shí)驗(yàn) 3 完整性語(yǔ)言實(shí)驗(yàn)實(shí)驗(yàn) 3.1 實(shí)體完整性實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康?掌握實(shí)體完整性的定義和維護(hù)方法。2、實(shí)驗(yàn)內(nèi)容

36、和要求 定義實(shí)體完整性，刪除實(shí)體完整性。能夠?qū)懗鰞煞N方式定義實(shí)體完整性的 SQL 語(yǔ)句：創(chuàng)建 表時(shí)定義實(shí)體完整性、創(chuàng)建表后定義實(shí)體完整性。設(shè)計(jì) SQL 語(yǔ)句驗(yàn)證完整性約束是否起作 用。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn) 實(shí)驗(yàn)重點(diǎn)：創(chuàng)建表時(shí)定義實(shí)體完整性。 實(shí)驗(yàn)難點(diǎn)：有多個(gè)候選碼時(shí)實(shí)體完整性的定義。4、實(shí)驗(yàn)報(bào)告示例(1) 創(chuàng)建表時(shí)定義實(shí)體完整性(列級(jí)實(shí)體完整性) 定義供應(yīng)商表的實(shí)體完整性。CREATE TABLE Supplier( suppkey INTEGER CONSTRAINT c1 PRIMARY KEY, name CHAR(25),address VARCHAR(40), nationkey IN

37、TEGER, phone CHAR(15), acctbal REAL,comment VARCHAR(101);(2) 創(chuàng)建表時(shí)定義實(shí)體完整性(表級(jí)實(shí)體完整性)CREATE TABLE Supplier( suppkey INTEGER, name CHAR(25), address VARCHAR(40), nationkey INTEGER, phone CHAR(15),acctbal REAL, comment VARCHAR(101), CONSTRAINT c1 PRIMARY KEY(suppkey);(3) 創(chuàng)建表后定義實(shí)體完整性 定義供應(yīng)商表。CREATE TABLE Su

38、pplier( suppkey INTEGER, name CHAR(25), address VARCHAR(40), nationkey INTEGER, phone CHAR(15), acctbal REAL, comment VARCHAR(101);ALTER TABLE SupplierADD CONSTRAINT c1 PRIMARY KEY(suppkey);(4) 定義實(shí)體完整性(主碼由多個(gè)屬性組成) 定義供應(yīng)關(guān)系表的實(shí)體完整性。CREATE TABLE PartSupp( partkey INTEGER, suppkey INTEGER, availqty INTEGER

39、, supplycost REAL, comment VARCHAR(199), PRIMARY KEY(partkey,suppkey);nationkey 作為主碼，(5) 有多個(gè)候選碼時(shí)定義實(shí)體完整性 定義國(guó)家表的實(shí)體完整性，其中 nationkey 和 name 都是候選碼，選擇 name 上定義唯一性約束。CREATE TABLE nation( nationey INTEGER CONSTRAINT c1 PRIMARY KEY, name CHAR(25) UNIQUE,regionkey INTEGER, comment VARCHAR(152);(6) 刪除實(shí)體完整性刪除國(guó)家

40、實(shí)體的主碼。ALTER TABLE nationDROP CONSTRAINT c1;(7) 增加兩條相同記錄，驗(yàn)證實(shí)體完整性是否起作用/* 插入兩條主碼相同的記錄就會(huì)違反實(shí)體完整性約束 */INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)VALUES(11, ?test1?,?test1?,?101?,?12345678?,0.0,?test1?);INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment

41、)VALUES(11, ?test2?,?test2?,?102?,?12345?,0.0,?test2?);5.思考題(1) 所有列級(jí)完整性約束都可以改寫成表級(jí)完整性約束，而表級(jí)完整性約束不一定能改寫 成列級(jí)完整性約束。請(qǐng)舉例說(shuō)明。答：當(dāng)主碼由多個(gè)屬性組成時(shí)，只能定義表級(jí)完整性約束。(2) 什么情況下會(huì)違反實(shí)體完整性約束， DBMS 將做何種違約處理？答： 1.主碼值不唯一 2.主碼的各個(gè)屬性有空。違約處理：拒絕執(zhí)行、級(jí)聯(lián)執(zhí)行等操作。實(shí)驗(yàn) 3.2 參照完整性實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康恼莆諈⒄胀暾缘亩x和維護(hù)方法。2、實(shí)驗(yàn)內(nèi)容和要求定義參照完整性， 定義參照完整性的違約處理， 刪除參照完整性。 寫出兩

42、種方式定義參照完整性的 SQL 語(yǔ)句：創(chuàng)建表時(shí)定義參照完整性、創(chuàng)建表后定義參照完整性。3、實(shí)驗(yàn)重點(diǎn)和難點(diǎn)實(shí)驗(yàn)重點(diǎn)：創(chuàng)建表時(shí)定義參照完整性。實(shí)驗(yàn)難點(diǎn)：參照完整性的違約處理定義。4、實(shí)驗(yàn)報(bào)告示例(1) 創(chuàng)建表時(shí)定義參照完整性 先定義地區(qū)表的實(shí)體完整性，再定義國(guó)家表的參照完整性。CREATE TABLE region( regionkey INTEGER PRIMARY KEY , name CHAR(25), comment VARCHAR(152);CREATE TABLE nation(nationkey INTEGER PTIMARY KEY,name CHAR(25),regionkey

43、INTEGER REFERENCES region(regionkey),/* 列級(jí)參照完整性 */ comment VARCHAR(152);或者：CREATE TABLE nation(nationkey INTEGER PTIMARY KEY,name CHAR(25),regionkey INTEGER,comment VARCHAR(152),CONSTRAINT c1 FOREIGN KEY(regionkey) REFERENCES region(regionkey); /* 表級(jí)參照完整性 */2)創(chuàng)建表后定義參照完整性定義國(guó)家表的參照完整性。CREATE TABLE nati

44、on( nationkey INTEGER PTIMARY KEY, name CHAR(25), regionkey INTEGER, comment VARCHAR(152);ALTER TABLE nationADD CONSTRAINT c1 FOREIGNKEY(regionkey) REFERENCES region(regionkey);(3) 定義參照完整性(外碼由多個(gè)屬性組成)定義訂單項(xiàng)目表的參照完整性。CREATE TABLE PartSupp( partkey INTEGER, suppkey INTEGER, availqty INTEGER, supplycost R

45、EAL, comment VARCHAR(199), PRIMARY KEY(partkey,suppkey);CREATE TABLE Lineitem( orderkey INTEGER REFERENCES Orders(orderkey), partkey INTEGER REFERENCES Part(partkey), suppkey INTEGER REFERENCES Supplier(suppkey), linenumber INTEGER, quantity REAL,PRIMARY KEY(orderkey,linenumber), FOREIGN KEY(partkey

46、,suppkey) REFERENCES PartSupp(partkey,suppkey);(4) 定義參照完整性的違約處理 定義國(guó)家表的參照完整性， 當(dāng)刪除或修改被參照表記錄時(shí)， 設(shè)置參照表中相應(yīng)記錄的值為空。CREATE TABLE nation( nationkey INTEGER PRIMARY KEY, name CHAR(25), regionkey INTEGER, comment VARCHAR(152),CONSTRAINT c1 FOREIGN KEY(regionkey) REFERENCES Region(regionkey) ON DELETE SET NULLON UPDATE SET NULL);(5) 刪除參照完整性 刪除國(guó)家表的外碼。ALTER TABLE nationDROP CONSTRING c1;(6) 插入一條國(guó)家記錄，驗(yàn)證參照完整性是否起作用/* 插入一條國(guó)家記錄，如果 1001'號(hào)地區(qū)記錄不存在，違反參照完整性約束*/INSERT INTO nation(nationkey,name,regionkey,comment) VALUES(1001, ?nation1?,1001,?comment1?)