重慶大學(xué)試卷07版重慶大學(xué)試卷07版第1頁(yè)共6頁(yè)公平競(jìng)爭(zhēng)、誠(chéng)實(shí)守信、嚴(yán)肅考紀(jì)、拒絕作弊A卷公平競(jìng)爭(zhēng)、誠(chéng)實(shí)守信、嚴(yán)肅考紀(jì)、拒絕作弊重慶大學(xué) 數(shù)據(jù)庫(kù)原理與設(shè)計(jì) 課程B卷2018~2019學(xué)年 第二學(xué)期開課學(xué)院：軟件學(xué)院課程號(hào)：09004530 考試日期： 題號(hào)一二三四五六七八九十總分得分考試方式： 開卷 閉卷題號(hào)一二三四五六七八九十總分得分密一、 ChooseonesuitablephrasemarkedA,B,CorDforeachquestion.(1.5pointseach,36points)GiventhefollowingDDLstatements,tab1(abcINT)VIEWv1ASSELECTa,b,cFROMtab1WHEREa>250 CHECKOPTION封WhichofthefollowingINSERTstatementswillfail?v1(200,2,3)v1(300,2,3)tab1(350,2,3)tab1(250,2,3)WhichofthefollowingistheoutcomeofthefollowingSQLstatements?employee(empnoempnameCHAR(30))CREATEUNIQUEINDEXempno_indONemployee(empno)EveryvalueforEMPNOwillbedifferent.MultipleNULLvaluesareallowedintheEMPNOcolumn.線C.AnadditionaluniqueindexcannotbecreatedontheEMPLOYEEtable.D.INSERTstatementsontheEMPLOYEEtablewillresultinclustereddata.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-manythroughtheorderstable組題人：aredevelopingalogicaldatabasedesignforacustomerdatabase.Companypolicyrequiresthateachcustomerprovideaname,address,andtelephoneItispossibleformorethanonecustomertohavethesamename,resideatthesameaddress,orhavethesametelephoneEachcustomerisassignedanIDnumber,andthisIDnumberisusedintheorderstabletoidentifyeachcustomer’sorders.組題人：審題人：Whichcolumnshouldyouusetouniquelyidentifytherecordsinthecustomertable?審題人：A.number B.CustomerIDnumberC.TelephonenumberandaddressD.Customernameandtelephonenumber命題時(shí)間：Evaluatethecustomer,orders,andemployeetablesshownAcustomerwithanordershouldnotbedeleted.Howshouldenforcearelationshipbetweenthecustomerandorderstablestoensurereferentialintegrity?命題時(shí)間：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.Addtheorder_idtothecustomertablewithaFOREIGNKEYconstraint.教務(wù)處制Addthecustomer_idcolumntotheorderstablewithaFOREIGNKEYconstraint.教務(wù)處制GiventableEMPLOYEEwithcolumnsEMPNOandandtableJOBwithcolumnsandTITLE,whatistheeffectofthefollowingstatement?UPDATEemployeeSETsalary=salary*1.15WHEREsalary<15000OR EXISTS(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.SecurityB.Performance C.Availability D.Extensibility在以下關(guān)于索引的敘述中,完全正確的是（ ）索引不僅能提高查詢的速度,還能提高更新的速度索引提高了查詢的速度,但一般會(huì)降低更新的速度索引字段不能更新索引字段可以更新,但是必須在更新前關(guān)閉索引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.RelationtablesC.Compatibletables D.RelationaltablesA( )ofatableTisthecandidatekeychosenbythedatabasedesignertouniquelyidentifyspecificrowsofA. 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.RRFDρ={AB，BC}相對(duì)于F( )A、是無(wú)損聯(lián)接，也是保持FD的分解 B、是無(wú)損聯(lián)接，但不保持FD的解C不是無(wú)損聯(lián)接但保持FD的分解 D既不是無(wú)損聯(lián)接也不保持FD分解關(guān)系模式規(guī)范化的最起碼的要求是達(dá)到第一范式，即滿足（ 。每個(gè)非碼屬性都完全依賴于主碼主碼屬性唯一標(biāo)識(shí)關(guān)系中的的元組關(guān)系中的元組不可重復(fù)每個(gè)屬性都是不可分解的RS{t/t∈R∧t∈S}標(biāo)記的是（ ）A)R∪S B）R-S C）R×S D）R∩S下列四項(xiàng)中說(shuō)法不正確的是（ ）A)數(shù)據(jù)庫(kù)減少了數(shù)據(jù)冗余 B)數(shù)據(jù)庫(kù)中的數(shù)據(jù)可以共享C)數(shù)據(jù)庫(kù)避免了一切數(shù)據(jù)的重復(fù) D)數(shù)據(jù)庫(kù)具有較高的數(shù)據(jù)獨(dú)立性設(shè)有關(guān)系模式是R上成立的FD集則R的候選鍵為（ ）A.ABC B.BD C.AB D.AC下面關(guān)于函數(shù)依賴的敘述中，不正確的是（ ）X→YZXY→Z,X→Z,Y→ZX→ZX→Y′二、 Filleachblankinthefollowingsentences.(14points,1pointeach)阿姆斯特朗公理的（ ）規(guī)則是：如果X→Y并且Y→Z,么X→。

用樹型結(jié)構(gòu)表示實(shí)體類型及實(shí)體間聯(lián)系的數(shù)據(jù)模型稱為（ 。數(shù)據(jù)庫(kù)三級(jí)模式體系結(jié)構(gòu)的劃分，有利于保持?jǐn)?shù)據(jù)庫(kù)的（ 。在一個(gè)關(guān)系中，能唯一標(biāo)識(shí)元組的屬性集稱為關(guān)系模式的（ 。（ DB的方法。數(shù)據(jù)（ 指當(dāng)數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)改變時(shí)通過(guò)系統(tǒng)內(nèi)部的自映像或轉(zhuǎn)換功能，保持了數(shù)據(jù)的邏輯結(jié)構(gòu)不變。（ 機(jī)硬件、軟件和數(shù)據(jù)庫(kù)管理員。( SQL儲(chǔ)并作為一個(gè)單元處理。在關(guān)系代數(shù)運(yùn)算中，從關(guān)系中取出滿足條件的元組的運(yùn)算稱為（ 。設(shè)X->Y是關(guān)系模式R的一個(gè)函數(shù)依賴，如果存在X的真子集X',使X'->Y成立，則稱Y( )X。書證號(hào)、總編號(hào)、借書日期等，主鍵為總編號(hào)，則借書證號(hào)為借閱關(guān)系的( )。數(shù)據(jù)庫(kù)設(shè)計(jì)是指對(duì)于一個(gè)給定的應(yīng)用環(huán)境構(gòu)（ 建數(shù)庫(kù)及其應(yīng)用系統(tǒng)，使之能有效地存儲(chǔ)數(shù)據(jù)，滿足各種用戶的應(yīng)用需求。一個(gè)關(guān)系模式為R(X1，X2，X3，X4)，假定該關(guān)系存在著如下函數(shù)依賴(Xl，X2)->X3，X3->X4，則該關(guān)系屬于( )范式因它存在著( )。三、 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.( )helosueofthettibutest（+isthelaststofttibutsYsuhthtX→YisinF+.( )Inrelationtables,columnsthathavemulti-valuedattributesorhaveanyinternalstructurearepermitted.( )四、(1)SolveinrelationalalgebraandSQLstatementsthefollowingqueries