重庆大学2017级数据库原理与设计课程试题（A卷）

重庆大学试卷07版重庆大学试卷07版第1页共6页公平竞争、诚实守信、严肃考纪、拒绝作弊A卷公平竞争、诚实守信、严肃考纪、拒绝作弊重庆大学 数据库原理与设计 课程B卷2018~2019学年 第二学期开课学院：软件学院课程号：09004530 考试日期： 题号一二三四五六七八九十总分得分考试方式： 开卷 闭卷题号一二三四五六七八九十总分得分密一、ChooseonesuitablephrasemarkedA,B,CorDforeachquestion.(1.5pointseach,33points)arebuildingalogicaldatabasemodelforanorderentrysystem.EvaluatetheordersandproducttablesfromthelegacysystemshownAnemployeereceivesanorderfromacustomer.Eachcustomermayorderoneormoreproducts.Duringtheprocessofnormalizingthetables,youcreateanewcustomer封table.Whatwillbetherelationshipbetweenthecustomerandproducttables?Head(orders)={order_id,product_name,customer_id,date}Head(product)={product_id,product_name}A.One-to-oneB.many-to-OneC.Many-to-manythroughtheorderstablearedevelopingalogicaldatabasedesignforacustomerdatabase.Companypolicyrequiresthateachcustomerprovideaname,address,andtelephoneItispossibleformorethanonecustomertohavethesamename,resideatthesameaddress,orhavethesametelephoneEachcustomerisassignedanIDnumber,andthisIDnumberisusedintheorderstabletoidentifyeachcustomer’sorders.线Whichcolumnshouldyouusetouniquelyidentifytherecordsinthecustomertable?A.number B.CustomerIDnumberC.Telephonenumberandaddress D.CustomernameandtelephonenumberEvaluatethecustomer,orders,andemployeetablesshownAcustomerwithanordershouldnotbedeleted.Howshouldenforcearelationship

betweenthecustomerandorderstablestoensurereferentialintegrity?命题人：Head (customer)={customer_id, first_name, last_name, telephone_number}命题人：Head(orders)={order_id,date,order_total}Head(employee)={emp_id,first_name,last_name}组题人：AddaCHECKconstrainttotheorder_idcolumn.组题人：AddaCHECKconstrainttothecustomer_idcolumn.Addthecustomer_idcolumntotheorderstablewithaFOREIGNKEYconstraint.Addtheorder_idtothecustomertablewithaFOREIGNKEYconstraint.审题人：GiventableEMPLOYEEwithcolumnsEMPNOandandtableJOBwithcolumnsandTITLE,whatistheeffectofthefollowingstatement?审题人：UPDATEemployeeSETsalary=salary*1.15WHEREsalary<15000OREXISTS(SELECTidFROMjobWHEREjob.id=employee.empnoANDjob.title='MANAGER')命题时间：Employeeswhomakelessthan15,000andallmanagersaregivensalaryincreases.命题时间：Onlyemployeeswhoaremanagersthatmakelessthan15,000aregivensalaryincreases.Employeeswhoarenotmanagersandwhomakelessthan15,000aregivensalaryincreases.Onlyemployeeswhoarenotmanagersormakelessthan15,000aregivensalaryincreases.教务处制Theorderstablehasaregioncolumn.Whatisthebestmethodtoensurethatordersareonlytakenfromcustomerslivinginoneofthreeregions?教务处制AddaCHECKconstraintotheregioncolumn.Addaconstrainttotheorderstable.Addaconstrainttotheregioncolumn.AddaTRIGGERtotheregioncolumntovalidatethedataafteritiscommitted.Amanufacturerhashundredsofpiecesofequipment.Thisequipmentistrackedformaintenanceandavailabilitybytheequipment’sserialWhichconstraintcouldyouusetoensureserialnumbersarenotduplicated?A.CHECK B.UNIQUE D.FOREIGNKEYcreateahighlynormalizedlogicaldatabasemodel.implementthemodelandcreateallthenecessaryindexes.Whichaspectofdatabasequalitywillthismodelimpact?A.Security B.Performance C.Availability D.Extensibility下面关于函数依赖的叙述中，不正确的是（ ）X→YZXY→Z,X→Z,Y→ZX→ZX→Y′Evaluatethisstatement:USEsalesSELECTDISTINCTproduct_id,transaction_dateFROMline_itemORDERBYline_item_idWhatistrueaboutthestatement?Thestatementdisplaysonlyuniqueproduct_idvalues.Alltherowsthatcontainaproduct_idvaluearedisplayed.Alltherowsthatcontainproduct_idandtransaction_datevaluesaredisplayed.Thestatementdisplaysonlyuniquecombinationsoftheproduct_idandtransaction_datevalues.Evaluatethisstatement:USEhrSELECTdepartment_id,SUM(salary)FROMemployeeGROUPBYdepartment_idHAVINGemp_id>2001Whichclausewillcausethestatementtofail?A.SELECTdepartment_id,SUM(salary) B.FROMemployeeC.GROUPBYdepartment_id D.emp_id>2001Anadministratorissues:GRANTALLPRIVILEGESONappl.tab1TOuser1WITHGRANTOPTIONWhichofthefollowingstatementsisUSER1authorizedtoexecute?GRANTDROPONappl.tab1user8GRANTOWNERONappl.tab1user8GRANTONappl.tab1user8GRANTCONTROLONappl.tab1user8IftablesRandShavethesameheadings,withattributeschosenfromthesamedomainsandwiththesamemeanings,thenRandSare( )A.Object-Relationaltables B.Relationtables

C.Compatibletables D.RelationaltablesA( )ofatableTisthecandidatekeychosenbythedatabasedesignertouniquelyidentifyspecificrowsof A. Key B.Superkey C.Primarykey D.ForeignkeyWhichSELECTstatementisfalse?Selectcount(distinctcity)fromcustomers;Selectsum(dollars)astotaldollarsfromorders;selectpid,sum(qty)fromordersgroupbypid;Selectcidfromcustomerswherediscnt<max(discnt);EvaluatethisUserhrGoCreatetriggercmp_insertONemployeeForinsertAsUpdatecsethire_date=getdate()FromcandidatecinnerjoinemployeeeONc.candidate_id=e.emp_idWhenwillthistriggerfire?WhenarecordisupdatedintheemployeetableWhenarecordisupdatedinthecandidatetableWhenarecordisinsertedintotheemployeetable.Whenarecordisinsertedintothecandidatetable.Whichofthefollowingcanbedoneusingthestatement?AddaDefineanindex.Dropatablealias.Defineauniqueconstraint.GiventhefollowingDDLstatements,tab1(abcINT)VIEWv1ASSELECTa,b,cFROMtab1WHEREa<250 CHECKOPTIONWhichofthefollowingINSERTstatementswillfail?v1(200,2,3)v1(300,2,3)tab1(350,2,3)tab1(250,2,3)WhichofthefollowingistheoutcomeofthefollowingSQLstatements?CREATETABLEemployee(empnoINT,empnameCHAR(30))CREATEUNIQUEINDEXempno_indONemployee(empno)EveryvalueforEMPNOwillbedifferent.MultipleNULLvaluesareallowedintheEMPNOcolumn.AnadditionaluniqueindexcannotbecreatedontheEMPLOYEEtable.statementsontheEMPLOYEEtablewillresultinclustereddata.下面哪一个语句能查找名称以“book”字符串结尾的出版社？Selectpub_namefrompublisherswherepub_namelike‘_book’Selectpub_namefrompublisherswherepub_namelike‘%book’Selectpub_namefrompublisherswherepub_namelike‘^bookSelectpub_namefrompublisherswherepub_namelike‘[book’RSrs，则(R×S)操作结果的属性个数为（）A.r+s B.r-s C.r×s D.max(r,s)设有关系模式是R上成立的FD集则属性C的闭包C+（ ）A.BCD B.BD C.CD D.BCR(A,B,C)，FR上的函数依赖集，F={AC,BC}ρ＝｛A→C,B→C｝相对于F（ ）A.FDB.FD的分解C.FD的分解D.FD的分解二、 Filleachblankinthefollowingsentences.(10points,1pointeach)（ DB的方法。在关系代数运算中，从关系中取出满足条件的元组的运算称为（ 设X->Y是关系模式R的一个函数依赖，如果存在X的真子集X',使X'->Y成立，则称Y( )X。书证号、总编号、借书日期等，主键为总编号，则借书证号为借阅关系的( )。( 做，是一个不可分割的工作单位。

数据库安全性控制的常用方法：用户标识和鉴定（ 、视图、计、密码存储。一个关系模式为Y(X1,X2,X3,X4)，假定该关系存在着如下函数依赖(X1,X2)->X3,X1->X4,则该关系属于( )范式，因它存在着( )。假定一个E-R图包含有A实体和B实体，并且A和B之间存在着m:n的联系和B均无多值属性则转换成关系模型后包含有( )个关系模式( SQL储并作为一个单元处理。三、 ErrorDetection.Rightstatementismarkedby√,anderrorismarkedby×(10points,1pointeach)Itisnotlegaltouseasetfunctiondirectlyinawhereclause.Ifsetfunctioninwhereclause,subquerymustbeused.( )Anentityinstanceisacollectionofdistinguishablerealworldobjectswithcommonproperties.( )Ifoneentityismulti-valuedandoneissinglevalued,therelationshipisMany-to-Onerelationship. TheMANYsideinaMany-to-Onerelationshipistheonewithmulti--valuedparticipation.( )Aforeignkeyinatablecan’treferenceaprimarykeyinthesametable.( )Ifadatabaseschemaisinthenitalsoisin( )arevirtualtablescreatedoutofrealbasetables.( )Clusteredindexmeansrowsinsameorderasindexentries.( )Thecardinality（基数）ofthetableisthenumberofrowsinatable.( )Thelosueofthettibutest（+isthelaststofttibutsYsuhthtX→YisinF+.( )Inrelationtables,columnsthathavemulti-valuedattributesorhaveanyinternalstructurearepermitted.( )四、(1)SolveinrelationalalgebraandSQLstatementsthefollowingqueriesinvolvingtheCAPdatabase.(18points,6pointeach)CUSTOMERScidcnamecitydiscntpidpnamepidpnamecityquantitypriceAGENTaidanamecitypercentORDERSordnomonthcidaidpidqtydollarsGetcitiesofagentsbookinganorderfromcustomerc002.Findcidsofcustomerswhodidnotplaceanorderthroughagenta03.Findcidsofcustomerswhoordersallproducts(2)SolveinSQLstatementsthefollowingqueriesinvolvingtheCAPdatabase.(10points)(3points)GetallproductandagentIDsandthetotalquantityorderedoftheproductbytheagent,whenthisquantityexceed