重慶大學(xué)2017級數(shù)據(jù)庫原理與設(shè)計課程試題（A卷）

重慶大學(xué)試卷07版重慶大學(xué)試卷07版第1頁共6頁公平競爭、誠實守信、嚴(yán)肅考紀(jì)、拒絕作弊A卷公平競爭、誠實守信、嚴(yán)肅考紀(jì)、拒絕作弊重慶大學(xué) 數(shù)據(jù)庫原理與設(shè)計 課程B卷2018~2019學(xué)年 第二學(xué)期開課學(xué)院：軟件學(xué)院課程號：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.教務(wù)處制Theorderstablehasaregioncolumn.Whatisthebestmethodtoensurethatordersareonlytakenfromcustomerslivinginoneofthreeregions?教務(wù)處制AddaCHECKconstraintotheregioncolumn.Addaconstrainttotheorderstable.Addaconstrainttotheregioncolumn.AddaTRIGGERtotheregioncolumntovalidatethedataafteritiscommitted.Amanufacturerhashundredsofpiecesofequipment.Thisequipmentistrackedformaintenanceandavailabilitybytheequipment’sserialWhichconstraintcouldyouusetoensureserialnumbersarenotduplicated?A.CHECK B.UNIQUE D.FOREIGNKEYcreateahighlynormalizedlogicaldatabasemodel.implementthemodelandcreateallthenecessaryindexes.Whichaspectofdatabasequalitywillthismodelimpact?A.Security B.Performance C.Availability D.Extensibility下面關(guān)于函數(shù)依賴的敘述中，不正確的是（ ）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”字符串結(jié)尾的出版社？Selectpub_namefrompublisherswherepub_namelike‘_book’Selectpub_namefrompublisherswherepub_namelike‘%book’Selectpub_namefrompublisherswherepub_namelike‘^bookSelectpub_namefrompublisherswherepub_namelike‘[book’RSrs，則(R×S)操作結(jié)果的屬性個數(shù)為（）A.r+s B.r-s C.r×s D.max(r,s)設(shè)有關(guān)系模式是R上成立的FD集則屬性C的閉包C+（ ）A.BCD B.BD C.CD D.BCR(A,B,C)，F(xiàn)R上的函數(shù)依賴集，F(xiàn)={AC,BC}ρ＝｛A→C,B→C｝相對于F（ ）A.FDB.FD的分解C.FD的分解D.FD的分解二、 Filleachblankinthefollowingsentences.(10points,1pointeach)（ DB的方法。在關(guān)系代數(shù)運算中，從關(guān)系中取出滿足條件的元組的運算稱為（ 設(shè)X->Y是關(guān)系模式R的一個函數(shù)依賴，如果存在X的真子集X',使X'->Y成立，則稱Y( )X。書證號、總編號、借書日期等，主鍵為總編號，則借書證號為借閱關(guān)系的( )。( 做，是一個不可分割的工作單位。

數(shù)據(jù)庫安全性控制的常用方法：用戶標(biāo)識和鑒定（ 、視圖、計、密碼存儲。一個關(guān)系模式為Y(X1,X2,X3,X4)，假定該關(guān)系存在著如下函數(shù)依賴(X1,X2)->X3,X1->X4,則該關(guān)系屬于( )范式，因它存在著( )。假定一個E-R圖包含有A實體和B實體，并且A和B之間存在著m:n的聯(lián)系和B均無多值屬性則轉(zhuǎn)換成關(guān)系模型后包含有( )個關(guān)系模式( 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（基數(shù)）ofthetableisthenumberofrowsinatable.( )Thelosueofthettibutest（+isthelaststofttibutsYsuhthtX→YisinF+.( )Inrelationtables,columnsthathavemulti-valuedattributesorhaveanyinternalstructurearepermitted.( )四、(1)SolveinrelationalalgebraandSQLstatementsthefollowingqueriesinvolvingtheCAPdatabase.(18points,6pointeach)CUSTOMERScidcnamecitydiscntpidpnamepidpnamecityquantitypriceAGENTaidanamecitypercentORDERSordnomonthcidaidpidqtydollarsGetcitiesofagentsbookinganorderfromcustomerc002.Findcidsofcustomerswhodidnotplaceanorderthroughagenta03.Findcidsofcustomerswhoordersallproducts(2)SolveinSQLstatementsthefollowingqueriesinvolvingtheCAPdatabase.(10points)(3points)GetallproductandagentIDsandthetotalquantityorderedoftheproductbytheagent,whenthisquantityexceed