《数据库系统原理》实验报告

...wd......wd......wd...学生实验报告〔理工类〕课程名称：数据库系统原理专业班级：14软件工程1班学生学号：1412101055学生姓名：孟祥辉所属院部：软件工程学院指导教师：麻春艳2015——2016学年第二学期金陵科技学院教务处制实验报告书写要求实验报告原则上要求学生手写，要求书写工整。假设因课程特点需打印的，要遵照以下字体、字号、间距等的具体要求。纸张一律采用A4的纸张。实验报告书写说明实验报告中一至四项内容为必填项，包括实验目的和要求；实验仪器和设备；实验内容与过程；实验结果与分析。各院部可根据学科特点和实验具体要求增加工程。填写本卷须知〔1〕细致观察，及时、准确、如实记录。〔2〕准确说明，层次清晰。〔3〕尽量采用专用术语来说明事物。〔4〕外文、符号、公式要准确，应使用统一规定的名词和符号。〔5〕应独立完成实验报告的书写，严禁抄袭、复印，一经发现，以零分论处。实验报告批改说明实验报告的批改要及时、认真、仔细，一律用红色笔批改。实验报告的批改成绩采用百分制，具体评分标准由各院部自行制定。实验报告装订要求实验批改完毕后，任课教师将每门课程的每个实验工程的实验报告以自然班为单位、按学号升序排列，装订成册，并附上一份该门课程的实验大纲。

实验工程名称：数据库定义与操作语言实验学时：2同组学生姓名：孟陈、陈晓雪、季佰军实验地点：1318实验日期：5.19实验成绩：批改教师：批改时间：一、实验目的1、理解和掌握数据库DDL语言，能够熟练地使用SQLDDL语句创立、修改和删除数据库、模式和根本表。2、掌握SQL册亨徐设计根本标准，熟练运用SQL语言实现数据根本查询，包括单表查询、分组统计查询和连接查询3、掌握SQL嵌套查询和集合查询等,各种高级查询的设计方法等.4、熟悉数据库的数据更新操作，能够使用sql语句对数据库进展数据的插入、修改、删除操作。5、熟悉sql语言有关系图的操作，能够熟练使用sql语言来创立需要的视图，定义数据库外模式，并能使用所创立的视图实现数据管理。6、掌握所以设计原则和技巧，能够创立适宜的索引以提高数据库查询、统计分析效率。二、实验内容和要求1、理解和掌握SQLDDL语句的语法，特别是各种参数的具体含义和使用方法；使用sql语句创立、修改和删除数据库、模式和根本表。掌握sql语句常见语法错误的调试方法。2、针对TPC-H数据库设计各种单表查询sql语句、分组统计查询语句；设计单个表针对自身的连接查询，涉及多个表的连接查询。理解和掌握sql查询语句各个子句的特点和作用，按照sql程序设计标准写出具体的sql查询语句，并调试通过。3、针对TPC-H数据库，证券分析用户查询要求，设计各种嵌套查询和集合查询。4、针对TPC-H数据库设计单元主唱入、批量数据插入、修改数据和删除数据的sql语句。理解和掌握insert、update、delete语法构造的各个组成成分，结合嵌套sql子查询，分别设计几个不同形式的插入、修改和删除数据的语句，并调试成功。5、针对给定的数据库模式，以及相应的应用要求，创立视图和带WITHCHECKOPTION的视图，并验证视图WITHCHECKOPTION选项的有效性。理解和掌握试图消解执行原理，掌握可更新视图和不可更新视图的区别。6、针对给定的数据库模式和具体应用需求，创立唯一索引、函数索引、复合索引等；修改索引；删除索引。设计相应的sql查询验证索引有效性，学习利用EXPLAIN命令分析sql查询是否使用了所创立的索引，并能够分析其原因，执行sql查询并估算索引提高查询效率的百分比，要求实验数据到达10万条记录以上的数据量，以便验证所以效果.三、实验过程1、数据库定义实验定义数据库采用中文字符集创立名为TCHP的数据库。CREATEDATABASETPCHENCODING=’GBK’;定义模式在数据库TPCH中创立名为SALES的模式。CreateSCHEMASales;定义根本表在TPCH数据库的Sales模式中创立8个根本表。/*设置当前会话的搜索路径为sales模式、public模式，根本表就会自动创立在sales模式下。*/SETSEARCH_PATHTOSales,Public;CREATETABLERegion(regionkeyINTEGERPRIMARYKEY,nameCHAR(25),commentVARCHAR(152));CREATETABLENation(nationkeyINTEGERPRIMARYKEY,nameCHAR(25),addressVARCHAR(40),regionkeyINTEGERREFERENCESREGION(REGIONKEY),commentVARCHAR(152));CREATETABLESupplier(suppkeyINTEGERPRIMARYKEY,nameCHAR(25),addressVARCHAR(40),nationkeyINTEGERREFERENCESNation(nationkey),phoneCHAR(15),acctbalREAL,commentVARCHAR(101));CREATETABLEPart(partkeyINTEGERPRIMARYKEY,nameVARCHAR(55),mfgrCHAR(25), /*制造厂*/brandCHAR(10),typeVARCHAR(25),sizeINTEGER,containerCHAR(10),retailpriceREAL,commentVARCHAR(23));CREATETABLEPartSupp(partkeyINTEGERREFERENCESPart(partkey),suppkeyINTEGERREFERENCESSupplier(suppkey),availqtyINTEGER,supplycostREAL,comment varchar(199),PRIMARYKEY(parkey,suppkey));CREATETABLECostomer(custkeyINTEGERPRIMARYKEY,nameVARCHAR(25),addressVARCHAR(40),nationkeyINTEGERREFERENCESNation(nationkey),phoneCHAR(15),acctbalREAL,mktsegmentCHAR(10),commentVARCHAR(117));CREATETABLEOrders(orderkeyINTEGERPRIMARYKEY,custkeyINTEGERREFERENCESCustomer(custkey),orderstatusCHAR(1),totalpriceREAL,orderdateDATE,orderpriorityINTEGER,commentVARCHAR(79));CREATETABLELineitem(orderkeyINTEGERREFERENCESOrder(orderkey),partkeyINTEGERREFERENCESPart(partkey),suppkeyINTEGERREFERENCESSupplier(suppkey),linenumberINTEGER,quantityREAL,extendedpriceREAL,discountREAL,taxREAL,returnflagCHAR(1),linestatusCHAR(1),shipinstructCHAR(25),shipmodeCHAR(10),commentVARCHAR(44),PRIMARYKEY(orderkey,linenumber),FOREIGNKEY(Partkey,suppkey)REFERENCESPartSupp(partkey,suppkey));2、数据根本查询〔1〕单表查询〔实现投影操作〕查询供给商的名称、地址和联系。SELECTEname,address,phoneFROMSupplier;〔2〕单表查询〔实现选择操作〕查询最近一周内提交的总价大于1000元的订单的编号、顾客编号等订单的所有信息。SELECT*FROMSales.OrdersWHERECURRENT_DATE-orderdata<7ANDtotalprice>1000;〔3〕不带分组过滤条件的分组统计查询统计每个顾客的订购金额SELECTC.custkey,SUM(O.totalprice)FROMcustomerC,OrdersOWHEREC.custkey=O.custkeyGROUPBYC.custkey;带分组过滤条件的分组统计查询查询订单平均金额超过1000元的顾客编号及其姓名SELECTC.custkey，MAX〔C.name〕FROMCustomerC,OrdersOWHEREC.custkey=O.custkeyGROUPBYC.custkey;HAVINGAVG〔O.totalprice〕>1000;表单自身连接查询查询与“金仓集团〞在同一个国家的供给商编号、名称和地址信息。SELECTF.suppkey,F.name，F.addressFROMSupplierF,SupplierSWHEREF.nationkey=S.nationkeyANDS.name='金仓集团';两表连接查询〔普通连接〕查询供给价格大于零售价格的零件名、制造商名、零售价格和供给价格。SELECTP.name,P.mfgr,P.retailprice,PS.supplycostFROMPartP,PartsuppPSWHEREP.retailprice>PS.supplycost;两表连接查询〔自然连接〕查询供给价格大于零售价格的零件名、制造商名、零售价格和供给价格。SELECTP.name,P.mfgr,P.retailprice,PS.supplycostFROMPartP,PartsuppPSWHEREP.partkey=PS.partkeyAND P.retailprice>PS.supplycost;〔8〕三表连接查询查询顾客“苏举库〞订购的订单编号、总价及其订购的零件编号、数量和明细价格。SELECTO.orderkey,O.totalprice,L.partkey,L.quantity,L.extendedpriceFROMCustomC,OrdersO,LineitemLWHEREC.custkey=O.custkeyANDO.orderkey=L.orderkeyANDC.name='苏举库';3、数据高级查询实验〔1〕IN嵌套查询查询订购了“海大〞制造的“船舶模拟驾驶舱〞的顾客。SELECTcustkey，nameFROMCustomerWHEREcustkeyIN(SELECTO.custkey FROMOrdersO,LineitmeL，PartSuppPS,PartP WHEREO.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND P.mfgr='海大'ANDP.name='船舶模拟驾驶舱');SELECTcustkey,nameFROMCustomerWHEREcuskeyIN(SELECTO.custkey FROMOrdersO,LineitemL,PartP WHEREO.orderkey=L.orderkeyAND L.partkey=P.partkeyAND p.mfgr='海大'ANDP.name='船舶模拟驾驶舱');〔2〕单层EXISTS嵌套查询查询没有购置过“海大〞制造的“船舶模拟驾驶舱〞的顾客。SELECTcustkey,nameFROMCustomerWHERENOTEXISTS(SELECTO.custkey FROMOrdersO,LineitemL,PartSuppPS,PartP WHEREC.cuskey=O.custkeyAND O.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND p.mfgr='海大'ANDP.name='船舶模拟驾驶舱');〔3〕双层EXISTS嵌套查询查询至少购置过顾客“张三〞购置过的全部零件的顾客姓名。SELECTCA.nameFROMCustomerCAWHERENOTEXISTS 〔SELECT* FROMCustomerCB,OdersOB,LineitemLB WHERECB.custkey=OB.custkeyAND OB.orderkey=LB.orderkeyAND CB.name='张三'AND NOTEXISTS(SELECT* FROMOrdersOC,LineitemLC WHERECA.custkey=LC.custkeyAND OC.orderkey=LC.orderkeyAND LB.suppkey=LC.suppkeyAND LB.partkey=LC.partkey));〔4〕FROM子句中的嵌套查询查询订单平均金额超过1万元的顾客中的中国籍顾客信息。SELECTC.*FROMCustomerC,(SELECTcustkey FROMOrders GROUPBYcustkey HAVINGAVG(totalprice)>10000)B,NationN WHEREC.custkey=B.custkeyAND C.nationkey=N.nationkeyANDN.name='中国';〔5〕集合查询〔交〕查询顾客“张三〞和“李四〞都订购过的全部零件的信息。SELECTP.*FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartPWHEREC.custkey=O.custkeyANDO.orderkey=L.orderkeyAND L.suppkey=PS.suppkeyANDL.partkey=PS.partkeyAND PS.partkey=P.partkeyANDC.name='李四';INTERSECTIONSELECTP.*FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartPWHEREC.cuskey=O.custkeyAND O.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND C.name='李四';〔6〕集合查询〔并〕查询顾客“张三〞和“李四〞订购的全部零件的信息。SELECTP.*FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartPWHEREC.cuskey=O.custkeyAND O.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND C.name='张三';UNIONSELECTP.*FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartPWHEREC.cuskey=O.custkeyAND O.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND C.name='李四';〔7〕集合查询〔差〕顾客“张三〞订购过而“李四〞没订购过的零件的信息。SELECTP.*FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartPWHEREC.cuskey=O.custkeyAND O.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND C.name='张三';EXCEPTSELECTP.*FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartPWHEREC.cuskey=O.custkeyAND O.orderkey=L.orderkeyAND L.partkey=PS.partkeyAND L.suppkey=PS.suppkeyAND PS.partkey=P.partkeyAND C.name='李四';4、数据更新实验〔1〕INSERT根本语句(插入全部列的数据)插入一条顾客记录，要求每列都给一个合理的值。INSERTINTOCustomerVALUES(30,'张三','北京市',40,,0.00,'Northeast','VIPCustomer');〔2〕INSERT根本语句〔插入局部列的数据〕插入一条订单记录，给出必要的几个字段值。INSERTINTOLineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate)VALUES(862,ROUND(RANDOM()*100,0,479,1,10,'2012-3-6');/*RANDOM()函数为随机小数生成函数，ROUND()为四舍五入函数*/〔3〕批量数据INSERT语句①创立一个新的顾客表，把所有中国籍顾客插入到新的顾客表中。CREATETABLENewCustmoerASSELECT*FROMCustomerWITHNODATA;/*WITHNODATA子句使得SELECT查询只生成一个结果模式，不查询出实际数据*/INSERTINTONewCustomer /*批量插入SELECT语句查询结果到NewCustomer表中*/SELECTC.*FROMCostomerC,NationNWHEREC.nationkey=N.nationkeyANDN.name='中国';②创立一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息。CREATETABLEShoppingStat(custkeyINTEGER,quantityREAL,totalpriceREAL);INSERTINTOShoppingStatSELECTC.custkey,Sum(L.quantity),Sum(O.totalprice) /*对分组后的数据求总和*/FROMCustomerC,OrderO,LineitemLWHEREC.custkey=O.custkeyANDO.orderkey=L.orderkeyGROUPBYC.custkey③倍增零件表的数据，屡次重复执行，直到总记录数到达50万为止。INSERTINTOPartSELECTpartkey+(SELECTCOUNT(*)FROMPart),name,mfgr,brand,type,size,container,retailprice,commentFROMPart;〔4〕UPDATE语句〔插入局部记录的局部列值〕“金仓集团〞供给的所有零件的供给本钱价下降10%。UPDATEPartSuppSETsupplycost=supplycost*0.9WHEREsuppkey=(SELECTsuppkey /*找出要修改的那些记录*/FROMSupplierWHEREname='金仓集团');〔5〕UPDATE语句〔利用一个表中的数据修改另外一个表中的数据〕利用Part表中的零售价格来修改Lineitem中的extendedprice,其中extendedprice=Part.retailprice*quantity。UPDATELineitemLSETL.extendedprice=P.retailprice*L.quantityFROMPartPWHEREL.partkey=P.partkey;/*Lineitem表也可以直接与Part表相连接，而不需通过PartSupp连接*/〔6〕DELETE根本语句〔删除给定条件的所有记录〕删除顾客张三的所有订单记录。DELECTFROMLineitem /*先删除张三的订单明细记录*/WHEREorderkeyIN(SELECTorderkeyFROMOrderO,CustomerCWHEREO.custkey=C.custkeyANDC.name='张三');DELECTFROMOrder /*再删除张三的订单记录*/WHEREcustkey=(SELECTcustkeyFROMCustomerWHEREname='张三');5、视图创立视图〔省略视图列名〕创立一个“海大汽配〞供给商供给的零件视图V_DLMU_PartSupp1，要求列出供给零件的编号、零件名称、可用数量、零售价格、供给价格和备注等信息。CREATEVIEWV_DLMU_PARTSUPP1AS /*由SELECT子句目标列组成视图属性*/SELECTP.partkey,P.name,PS.availqty,P.retailprice,PS.supplycost,PmentFROMPartP,PartSuppPS,SupplierSWHEREP.partkey=PS.partkeyANDS.suppkey=PS.suppkeyANDS.name='海大汽配';创立视图〔不能省略列名的情况〕创立一个视图V_CustAvgOrder,按顾客统计平均每个订单的购置金额和零件数量，要求输出顾客编号、姓名，平均购置金额和平均购置零件数量。CREATEVIEWV_CustAvgOrder(custkey,cname,avgprice,avgquantity)ASSELECTC.custkey,MAX(C.name),AVG(O.totalprice),AVG(L.quantity)FROMCustomerC,OrdersO,LineitemLWHEREC.custkey=O.custkeyANDL.orderkey=O.orderkeyGROUPBYC.custkey;创立视图〔WITHCHECKOPTION〕使用WITHCHECKOPTION，创立一个“海大汽配〞供给商供给的零件视图V_DLMU_PartSupp2，要求列出供给零件的编号、可用数量和供给价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配〞零件供给记录，验证WITHCHECKOPTION是否起作用。CREATEVIEWV_DLMU_PartSupp2ASSELECTpartkey,suppkey,availqty,supplycostFROMPartSuppWHEREsuppkey=(SELECTsuppkeyFROMSupplierWHEREname='海大汽配')WITHCHECKOPTION;INSERTINTOV_DLMU_PartSupp2VALUES(58889,5048,704,77760);UPADTEV_DLMU_PartSupp2SETsupplycost=12WHEREsuppkey=58889;DELETEFROMV_DLMU_PartSupp2WHEREsuppkey=58889;可更新的视图〔行列子集视图〕使用WITHCHECKOPTION，创立一个“海大汽配〞供给商供给的零件视图V_DLMU_PartSupp4，要求列出供给零件的编号、可用数量和供给价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配〞零件供给记录，验证该视图是否是可更新的，并比拟上述“〔3〕创立视图〞实验任务与本任务结果有何异同。CREATEVIEWV_DLMU_PartSupp3ASSELECTpartkey,suppkey,availqty,supplycostFROMPartSuppWHEREsuppkey=(SELECTsuppkeyFROMSupplierWHEREname='海大汽配');INSERTINTOV_DLUM_PartSupp3VALUES(58889,5048,704,77760);UPDATEV_DLMU_PartSupp3SETsupplycost=12WHEREsuppkey=58889;DELETEFROMV_DLMU_PartSupp3WHEREsuppkey=58889;〔5〕可更新的视图INSERTINTOV_CustAvgOrderVALUES(100000,NULL,20,2000);删除视图〔RESTRICT/CASCADE〕创立顾客订购零件明细视图V_CustOrd,要求列出顾客编号、姓名、购置零件数、金额，然后在该视图的根基上，在创立〔2〕的视图V_CustAvgOrder,然后使用RESTRICT选项和CASCADE选项删除视图V_CustOrd。CREATEVIEWV_CustOrd(custkey,cname,qty,extprice)ASSELECTC.custkey,C.name,L.quantity,L.extendedpriceFROMCustomerC,OrderO,LineitemLWHEREC.custkey=O.custkeyANDO.orderkey=L.orderkey;CREATEVIEWV_CustAvgOrder(custkey,cname,avgqty,avgprice)ASSELECTcustkey,MAX(cname),AVG(qty),AVG(extprice)FROMV_CustOrd /*在视图V_CustOrd上再创立视图*/GROUPBYcustkey;DROPVIEWV_CustOrdRESTRICT;DROPVIEWV_CustOrdCASCADE;6、索引创立唯一索引在零件表的零件名称字段上创立唯一索引。CREATEUNIQUEINDEXIdx_part_nameONPart(name);创立函数索引〔对某个属性的函数创立索引，称为函数索引〕在零件表的零件名称字段上创立一个零件名称长度的函数索引。CREATEINDEXIdx_part_name_funONPart(LENGTH(name));〔3〕创立复合索引〔对两个及两个以上的属性创立索引，称为复合索引〕在零件表的制造商和品牌两个字段上创立一个复合索引。CREATEUNIQUEINDEXIdx_part_mfgr_brandONPart(mfgr,brand);*创立聚簇索引在零件表的制造商字段上创立一个聚簇索引。CREATEUNIQUEINDEXIdx_part_mfgrONPart(mfgr);CLUSTERIdx_part_mfgrONPart;创立Hash索引零件表的名称字段上创立一个Hash索引。CREATEINDEXIdx_part_name_hashONPartUSINGHASH(name);修改索引名称修改零件表的名称字段上的索引名。ALTERINDEXIdx_part_name_hashRENAMETOIdx_part_name_hash_new;〔7〕分析某个SQL查询语句执行时是否使用了索引EXPLAINSELECT*FROMpartWHEREname='零件';*验证索引效率创立一个函数TestIndex,自动计算sql查询执行的时间。CREATEFUNCTIONTestIndex(p_part_nameCHAR(55))RETURNINTEGERAS /*自定义函数TestIndex()：输入参数为零件名称，返回SQL查询的执行时间*/DECLAREbegintimeTIMESTAMP;endtimeTIMESTAMP;durationtimeINTEGER;BEGNSELECTCLOCK_TIMESTAMP()INTObegintime; /*记录查询执行的开场时间*/PERFORM*FROMPartWHEREname=p_partname; /*执行SQL查询，不保存查询结果*/SELECTCLOCK_TIMESTAMP()INTOendtime;SELECTDATEDIFF(‘ms’,begintime,endtime)INTOdurationtime;RETURNdurationtime; /*计算并返回查询执行时间，时间单位为毫秒ms*/END;/*查看当零件表Part数据模型比拟小，并且无索引时的执行时间*/SELECTTestIndex(‘零件名称’);INSERTINTOPart /*不断倍增零件表的数据，直到50万条记录*/SELECTpartkey+(SELECTCOUNT(*)FROMPart),Name,mfgr,brand,type,size,container,retailprice,commentFRPMPart;/*查看当零件表Part数据模型比拟大，但无索引时的执行时间*/SELECTTestIndex(‘零件名称’);CREATEINDEXpart_nameONPart(name); /*在零件表的零件名称字段上创立索引*//*查看零件表Part数据规模比拟大，有索引时的执行时间*/SELECTTestIndex();四、实验心得通过本次实验，我知道只有正确理解数据库模式构造，才能正确设计数据库查询。连接查询是数据库sql查询中最重要的查询，连接查询的设计要特别注意，不同的查询表达，其查询执行的性能会有很大差异。正确地设计和执行数据更新语句，确保正确地录入数据和更新数据，才能保证查询的数据正确。当数据更新失败时，一个主要原因是更新数据时违反了完整性约束。实验工程名称：安全性语言实验实验学时：2同组学生姓名：孟陈、陈晓雪、季佰军实验地点：1318实验日期：5.26实验成绩：批改教师：批改时间：实验目的掌握自主存取控制缺陷的定义和维护方法。掌握数据库审计的设置和管理方法，以便监控数据库操作，维护数据库安全。实验内容和要求定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设置权限分配。可以采用两种方案。方案一：采用SYSTEM超级用户登录数据库，完成所有权限分配工作，然后用相应用户名登录数据库已验证权限分配正确性；方案二：采用SYSTEM用户登录数据库创立3个部门经理用户，并分配相应的权限，然后分别用3个经理用户名登录数据库，创立相应部门的USER、ROLE，并分配相应权限。2、翻开数据库审计开关。以具有审计权限的用户登录数据库，设置审计权限，然后以普通用户登录数据库，执行相应的数据操纵sql语句，验证相应审计设置是否生效，最后在一具有审计权限的用户登录数据库，查看是否存在相应的审计信息。三、实验过程1、自主存取控制实验〔1〕创立用户eq\o\ac(○,1)为采购、销售和客户管理等3个部门的经理创立用户标识，要求具有创立用户或角色的权利。CREATEUSERDavidWITHCREATEROLEPASSWORD'123456';CREATEUSERTomWITHCREATEROLEPASSWORD'123456';CREATEUSERKathyWITHCREATEROLEPASSWORD'123456';eq\o\ac(○,2)为采购、销售和客户管理等3个部门的职员创立用户标识和用户口令。CREATEUSERJefferyWITHPASSWORD'123456';CREATEUSERJaneWITHPASSWORD'123456';CREATEUSERMikeWITHPASSWORD'123456';〔2〕创立角色并分配权限eq\o\ac(○,1)为各个部门分别创立一个查询角色，并分配相应的查询权限。CREATEROLEPurchaseQueryRole;GRANTSELECTONTABLEPartTOPurchaseQueryRole;GRANTSELECTONTABLESupplierTOPurchaseQueryRole;GRANTSELECTONTABLEPartSuppTOPurchaseQueryRole;CREATEROLESaleQueryRole;GRANTSELECTONTABLEOrderTOSaleQueryRole;GRANTSELECTONTABLELineitemTOSaleQueryRole;CREATEROLECustomerQueryRole;GRANTSELECTONTABLECustomerTOCustomerQueryRole;GRANTSELECTONTABLENationTOCustomerQueryRole;GRANTSELECTONTABLERegionTOCustomerQueryRole;eq\o\ac(○,2)为各个部门分别创立一个职员角色，对本部门信息具有查看、插入权限。CREATEROLEPurchaseEmployeeRole;GRANTSELECT，INSERTONTABLEPartTOPurchaseEmployeeRole;GRANTSELECT，INSERTONTABLESupplierTOPurchaseEmployeeRole;GRANTSELECT，INSERTONTABLEPartSuppTOPurchaseEmployeeRole;CREATEROLESaleEmployeeRole;GRANTSELECT，INSERTONTABLEOrderTOSaleEmployeeRole;GRANTSELECT，INSERTONTABLELineitemTOSaleEmployeeRole;CREATEROLECustomerEmployeeRole;GRANTSELECT，INSERTONTABLECustomerTOCustomerEmployeeRole;GRANTSELECT，INSERTONTABLENationTOCustomerEmployeeRole;GRANTSELECT，INSERTONTABLERegionTOCustomerEmployeeRole;eq\o\ac(○,3)为各个部门创立一个经理角色，相应角色对本部门的信息具有完全控制权限，对其他部门的信息具有查询权。经理有权给本部门资源分配权限。CREATEROLEPurchaseManagerRoleWITHCREATEROLE；GRANTALLONTABLEPartTOPurchaseManagerRole；GRANTALLONTABLESupplierTOPurchaseManagerRole；GRANTALLONTABLEPartSuppTOPurchaseManagerRole；GRANTSaleQueryRoleTOPurchaseManagerRole；GRANTCustomerQueryRoleTOPurchaseManagerRole；CREATEROLESaleManagerRoleWITHCREATEROLE；GRANTALLONTABLEOrderTOSaleManagerRoleGRANTALLONTABLELineitemTOSaleManagerRoleGRANTSaleQueryRoleTOSaleManagerRoleGRANTPurchaseQueryRoleTOSaleManagerRoleCREATEROLECustomerManagerRoleWITHCREATEROLE；GRANTALLONTABLECustomerTOCustomerManagerRoleGRANTALLONTABLENationTOCustomerManagerRoleGRANTALLONTABLERegionTOCustomerManagerRoleGRANTSaleQueryRoleTOCustomerManagerRoleGRANTPurchaseQueryRoleTOCustomerManagerRole〔3〕给用户分配权限eq\o\ac(○,1)给部门经理分配权限。GRANTPurchaseManagerRoleTODavidWITHADMINOPTION;GRANTSaleManagerRoleTOTomWITHADMINOPTION;GRANTCustomerManagerRoleTOKathyWITHADMINOPTION;eq\o\ac(○,2)给各部门职员分配权限GRANTPurchaseEmployeeRoleTOJeffery;GRANTSaleEmployeeRoleTOJane;GRANTCustomerEmployeeRoleTOMike;〔4〕回收角色或用户权限eq\o\ac(○,1)收回客户经理角色的销售信息查看权限。REVOKESaleQueryRoleFROMCustomerManagerRole;eq\o\ac(○,2)回收MIKE的客户部门职员权限。REVOKECustomerEmployeeRoleFROMMike;〔5〕验证权限分配正确性eq\o\ac(○,1)以David用户名登录数据库，验证采购部门经理的权限SELECT*FROMPart;DELETE*FROMOrder;eq\o\ac(○,2)回收MIKE的客户部门职员权限SELECT*FROMCustomer;SELECT*FROMPart;2、审计实验〔1〕审计开关eq\o\ac(○,1)显示当前审计开关状态SHOWAUDIT_TRAIL;eq\o\ac(○,2)翻开审计开关SETAUDIT_TRAILTOON;〔2〕数据库操作审计eq\o\ac(○,1)对客户信息表上的删除操作设置审计。AUDITDELETEONSales.CustomerBYACCESS;eq\o\ac(○,2)以普通用户登录，执行sql语句。DELETESales.CustomerWHEREcustkey=1011;eq\o\ac(○,3)查看数据库对象审计信息，验证审计设置是否生效。SELECT*FROMSYS_AUDIT_OBJECT;〔3〕语句级审计eq\o\ac(○,1)对表定义的更改语句ALTER设置审计AUDITALTERTABLEBYACCESS;eq\o\ac(○,2)查看所有数据库所有语句级审计设置，验证审计设置是否生效SELECT*FROMSYS_STMT_AUDIT_OPTS;eq\o\ac(○,3)以普通用户登录，执行sql语句，验证审计设置是否生效ALTERTABLECustomerADDCOLUMNttINT;eq\o\ac(○,4)查看所有审计信息SELECT*FROMSYS_AUDIT_TRAIL;四、实验心得通过本次实验，知道了定义用户、角色，分配权限给用户、角色，回收权限，并以相应的用户名登陆数据库验证权限分配是否正确的方法。并且知道了数据库审计的目的和方法。做实验的同时，对sql语句有了更熟练的运用。实验工程名称：完整性语言实验实验学时：2同组学生姓名：孟陈、陈晓雪、季佰军实验地点：1318实验日期：6.2实验成绩：批改教师：批改时间：实验目的掌握实体完整性的定义和维护方法；掌握参照完整性的定义和维护方法；掌握用户自定义完整性的定义和维护方法；实验内容和要求1、定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的SQL语句：创立表时定义实体完整性、创立表后定义实体完整性。设计SQL语句验证完整性约束是否起作用。2、定义参照完整性，定义参照完整性的违规处理，删除参照完整性。写出两种方式定义参照完整性的SQL语句：创立表时定义参照完整性、创立表后定义参照完整性。3、针对具体应用语义，选择NULL/NOTNULL、DEFAULT、UNIQUE、CHECK等，定义属性上的约束条件。三、实验过程1、实体完整性实验〔1〕创立表时定义实体完整性(列级实体完整性)定义供给商表的实体完整性。CREATETABLESupplier(suppkeyINSERTCONSTRAINTPK_supplierPRIMARYKEY,nameCHAR(25),addressVARCHAR(40),nationkeyINSERT,phoneCHAR(15),acctbalREAL,commentVARCHAR(101));〔2〕创立表时定义实体完整性〔表级实体完整性〕定义供给商表的实体完整性。CREATETABLESupplier(suppkeyINSERT,nameCHAR(25),addressVARCHAR(40),nationkeyINSERT,phoneCHAR(15),acctbalREAL,commentVARCHAR(101),CONSTRAINTPK_supplierPRIMARYKEY(suppkey));〔3〕创立表后定义实体完整性定义供给商表。CREATETABLESupplier(suppkeyINSERT,nameCHAR(25),addressVARCHAR(40),nationkeyINSERT,phoneCHAR(15),acctbalREAL,commentVARCHAR(101));ALTERTABLESupplier/*再修改供给商表，增加实体完整性*/ADDCONSTRAINTPK_SupplierPRIMARYKEY(suppkey);〔4〕定义实体完整性〔主码由多个属性组成〕定义供给关系表的实体完整性。CREATETABLEPartSupp(partkeyINTEGER,suppkeyINTEGER,availqtyINTEGER,supplycostREAL,commentVARCHAR(199),PRIMARYKEy(partkey,suppkey));/*主码由多个属性组成，实体完整性必须定义在表级*/〔5〕有多个候选码时定义实体完整性定义国家表的实体完整性，其中nationkey和name都是候选码，选择nationkey作为主码，name上定义唯一性约束。CREATETABLEnation(nationkeyINTEGERCONSTRAINTPK_nationPRIMARYKEY，nameCHAR(25)UNIQUE,regionkeyINSERT,commentVARCHAR(152));〔6〕删除实体完整性删除国家实体的主码。ALTERTABLEnationDROPCONSTRAINTPK_nation;〔7〕增加两条一样记录，验证实体完整性是否起作用/*插入两条主码一样的记录就会违反实体完整性约束*/INSERTINTOSupplier(suppkey,name,address,nationkey,phone,acctbal,comment)VALUES(11,'test1','test1',101,'12345678',0.0,'test1');INSERTINTOSupplier(suppkey,name,address,nationkey,phone,acctbal,comment) VALUES(11,'test2','test2',102,'23456789',0.0,'test2');2、参照完整性实验〔1〕创立表时定义参照完整性先定义地区表的实体完整性，再定义国家表上的参照完整性。CREATETABLEregion〔regionkeyIINTEGERPRIMARYKEY，nameCHAR(25)，commentVARCHAR(152));CREATETABLEnation(nationkeyIINTEGERPRIMARYKEY，nameCHAR(25),regionkeyINTEGERREFERENCESRegion(regionkey),/*列级参照完整性*/commentVARCHAR(152));或者CREATETABLEnation(nationkeyINTEGERPRIMARYKEY，nameCHAR(25),regionkeyINTEGER,commentVARCHAR(152),CONSTRAINTFK_Nation_regionkeyFOREIGNKEY(regionkey)REFERENCESRegion(regionkey));/*表级参照完整性*/〔2〕创立表后定义参照完整性定义国家表的参照完整性。CREATETABLEnation(nationkeyINTEGERPRIMARYKEY,nameCHAR(25),regionkeyINTEGER,commentVARCHAR(152));ALTERTABLENationFOREIGNKEY(regionkey)REFERENCESRegion(regionkey);〔3〕定义参照完整性〔外码由多个属性组成〕定义订单工程表的参照完整性。CREATETABLEPartSupp(partkeyINTEGER,suppkeyINTEGER,availqtyINTEGER,supplycostREAL,commentVARCHAR(199),PRIMARYKEy(partkey,suppkey));CREATETABLELineitem(orderkeyINTEGERREFERENCESOrders(orderkey),partkeyINTEGERREFERENCESPart(partkey),suppkeyINTEGERREFERENCESSupplier(suppkey),linenumberINTEGER,quantityREAL，extendedpriceREAL,discountREAL，taxREAL，returnflagCHAR(1),linestatusCHAR(1),shipdateDATE,commitdateDATE,receiptdateDATE,shipinstructCHAR(25),shipmodeCHAR(10),commentVARCHAR(44),PRIMARYKEY(orderkey,linenumber),FOREIGNKEY(partkey,suppkey)REFERENCESPartSupp(partkey,suppkey));〔4〕定义参照完整性的违约处理定义国家表的参照完整性，当删除或修改被参照表时，设置参照表中相应记录的值为空值。CREATETABLEnation(nationkeyINTEGERPRIMARYKEY,nameCHAR(25),regionkeyINTEGER,commentVARCHAR(152),CONSTRAINTFK_Nation_regionkeyFOREIGNKEY(regionkey)REFERENCESRegion(regionkey)ONDELETESETNULLONUPDATESETNULL〕；〔5〕删除参照完整性删除国家表的外码ALTERTABLEnationDROPCONSTRAINTFK_Nation_regionkey;〔6〕插入一条国家纪录，验证参照完整性是否起作用/*插入一条国家纪录，如果‘1001’号地区记录不存在，违反参照完整性约束。*/INSERTINTONation(nationkey,name,regionkey,comment)VALUES(1001,'nation1',1001,'comment1');3、用户自定义完整性实验定义属性NULL/NOTNULL约束定义地区表各属性的NULL/NOTNULL属性.CREATETABLEregion(regionkeyINTEGERNOTNULLPRIMARYKEY,nameCHAR(25)NOTNULL,commentVARCHAR(152)NULl);定义属性DEFAULT约束定义国家表的regionkey的缺省属性值为0值，表示其他地区。CREATETABLEnation(nationkeyINTEGERPRIMARYKEY,nameCHAR〔25〕，regionkeyINTEGERDEFAULT0,commentVARCHAR(152),CONSTRAINTFK_Nation_regionkeyFOREIGNKEY(regionkey)REFERENCESRegion(region));〔3〕定义属性UNIQUE约束定义国家表的名称属性必须唯一的完整性约束。CREATETABLEnation(nationkeyINTEGERPRIMARYKEY,nameCHAR(25)UNIQUE,regionkeyINTEGER,commentVARCHAR(152));〔4〕使用CHECK使用CHECK定义订单工程表中某些属性应该满足的约束。CREATETABLELineitem(orderkeyINTEGERREFERENCESOrders(orderkey),partkeyINTEGERREFERENCESPart(partkey),suppkeyINTEGERREFERENCESSupplier(suppkey),linenumberINTEGER,quantityREAL,extendedpriceREAL,discountREAL,taxREAL,returnflagCHAR(1),linestatusCHAR(1),shipdateDATE,commitdateDATE,receiptdateDATE,shipinstructCHAR(25),shipmodeCHAR(10),commentVARCHAR(44)，PRIMARYKEY(orderkey,linenumber),FOREIGNKEY(partkey,suppkey)REFERENCESPartSupp(partkey,suppkey),CHECK(shipdate<receiptdate),/*装运日期<签收日期*/CHECK(returnflagIN('A','R','N')));/*退货标记为A或R或N*/〔5〕修改Lineitem的一条记录验证是否违反CHECK约束UPDATEsales.LineitemSETshipdate='2015-01-05',receiptdate='2015-01-01'WHEREorderkey=5005ANDlinenumber=1;四、实验心得通过本次实验，我掌握了实体完整性的定义和维护方法，动手操作了定义实体完整性，删除实体完整性，并且可以设计SQL语句验证完整性约束是否起作用。同时，掌握了参照完整性的定义和维护方法，动手体验了定义参照完整性，并且操作了定义参照完整性的违规处理以及删除参照完整性。除此之外，还掌握了用户自定义完整性的定义和维护。此次实验使我学习了完整性相关知识，更促使我进一步学习。实验工程名称：触发器实验实验学时：2同组学生姓名：孟陈、陈晓雪、季佰军实验地点：1318实验日期：6.9实验成绩：批改教师：批改时间：一、实验目的掌握数据库触发器的设计和使用方法二、实验内容和要求定义BEFORE触发器和AFTER触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。三、实验过程1.AFTER触发器〔1〕在Lineitem表上定义一个UPDATE触发器，当修改订单明细〔即修改订单明细价格extendedprice、折扣discount、税率tax〕时，自动修改订单Orders的TotalPrice，以保持数据一致性。totalprice=totalprice+extendedprice*〔1-discount〕*〔1+tax〕)CREATEORREPLACETRIGGERTRI_Price_UPDATEAFTERUPDATEOFextendedprice,discount,taxONLineitemFOREACHROWASDECLAREL_valuediffREALBEGIN/*订单明细修改后，计算订单含税折扣价总价的修正值*/L_valuediff=NEW.extendedprice*〔1-NEW.discount〕*〔1+NEW.tax〕-OLD.extendedprice*〔1-OLD.discount〕*〔1+OLD.tax〕；/*更新订单的含税折扣总价*/UPDATEOrdersSETtotalprice=totalprice+L_valuediffWHEREorderkey=NEW.orderkey;END’(2)在Lineitem表上定义一个INSERT触发器，当增加一项订单明细时，自动修改订单Orders的TotalPrice，以保持数据一致性。CREATEORREPLACETRIGGERTRI_Lineitem_Price_INSERTAFTERINSERTONLineitemFOREACHROWASDECLAREL_valuediffREALBEGINL_valuediff=NEW.extendedprice*〔1-NEW.discount〕*〔1+NEW.tax〕；/*增加订单明细项后，计算订单含税折扣价总价的修正值*/UPDATEOrdersSETtotalprice=totalprice+L_valuediff/*更新订单的含税折扣价总价*/WHEREorderkey=NEW.orderkey;END;(3)在Lineitem表上定义一个DELETE触发器，当删除一项订单明细时，自动修改订单Orders的TotalPrice，以保持数据一致性。CREATEORREPLACETRIGGERTRI_Lineitem_Price_DELETEAFTERDELETEONLineitemFOREACHROWASDECLAREL_valuediffREAL;BEGINL_valuediff=-OLD.extendedprice*〔1-OLD.discount〕*〔1+OLD.tax〕；/*删除订单明细项后，计算订单含税折扣价总家的修正值*/UPDATEOrdersSETtotalprice=totalprice+L_valuediff/*更新订单的含税折扣价总价*/WHEREorderkey=NEW.orderkey；END；〔4〕验证触发器TRI_Lineitem_Price_UPDATE。/*查看1854号订单的含税折扣价总价totalprice*/SELECTtotalpriceFROMOrdersWHEREorderkey=1854；/*激活触发器，修改1854号订单第一个明细项的税率，该税率增加0.5%*/UPDATELineitemSETtax=tax+0.005WHEREorderkey=1854ANDlinenumber=1;/*再次查看1854号订单的含税折扣总价totalprice是否有变化，如有变化，则是触发器起作用了，否则触发器没有起作用*/SELECTtotalpriceFROMOrdersWHEREorderkey=1854；2.BEFORE触发器〔1〕在Lineitem表上定义一个BEFORE_UPDATE触发器，当修改订单明细中的数量(quality)时，先检查供给表PartSupp中的可用数量availqty是否足够.CREATORREPLACETRIGGERTRI_Lineitem_Quality_UPDATEBEFOREUPDATEOFqualityONLineitemFOREACHROWASDECLAREL_valuediffINSERT；L_availqtyINSERT；BEGIN/*计算订单明细项修改时，订购数量的变化值*/L_valuediff=NEW.quantity-OLD.quantity；/*查询当前订单明细项对应零件供给记录中的可用数量*/SELECTavailqtyINTOL_availqtyFROMPartSuppWHEREpartkey=NEW.partkeyANDsuppkey=NEW.suppkey;IF(L_availqty-L_valuediff>=0)THENBEGIN/*如果可用数量可以满足订单订购数量，则提示ENOUGH*/RAISENOTICE‘AvailablequantityisENOUGH‘；/*修改当前订单明细项对应零件供给记录中的可用数量*/UPDATEPartSuppSETavailqty=availqty-L_valuediffWHEREpartkey=NEW.partkeyANDsuppkey=NEW.suppkey;END;ELSE/*如果可用数量不能满足订单订购数量，则更新过程异常中断*/RAISEEXCEPTION‘AvailablequantityisNOTENOUGH’；ENDIF；END；〔2〕在Lineitem表上定义一个BEFOREINSERT触发器，当插入订单明细，先检查供给表PartSupp中的可用数量availqty是否足够。CREATEORREPLACETRIGGERTRI_Lineitem_Quantity_UPDATEBEFOREINSERTONLineitemFOREACHROWASDECLAREL_valuediffINSERT；L_aavailqtyINSERT；BEGINL_valuediff=NEW.quantity；/*获得插入订单明细项的订购数量*//*查询当前订单明细项对应零件供给记录中的可用数量*/SELECTavailqtyINTOL_availqtyFROMPartSuppWHEREpartkey=NEW.partkeyANDsuppkey=NEW.suppkey;IF(L_availqty-L_valuediff>=0)THENBEGIN/*如果可用数量可以满足订单订购数量，则提示ENOUGH*/RAISENOTICE’AvailqtyquantityisENOUGH’;/*修改当前订单明细项对应零件供给记录中的可用数量*/UPDATEPartSuppSETavailqty=availqty-L_valuediffWHEREpartkey=NEW.partANDsuppkey=NEW.suppkey;END;ELSE/*如果可用数量不能满足订单订购数量，则插入过程异常中断*/RAISEEXCEPTION‘AvailablequantityisNOTENOUGH’;ENDIF;END;(3)在Lineitem表上定义一个BEFORE