數(shù)據(jù)庫(kù)處理課后習(xí)題答案

《數(shù)據(jù)庫(kù)處理》復(fù)習(xí)要點(diǎn)及參考答最近更新時(shí)間第一章Access2007（第一次作業(yè) 復(fù)習(xí)要 第二章結(jié)構(gòu)化查詢語(yǔ)言簡(jiǎn)介（第二次作業(yè) 復(fù)習(xí)要 Marcia干洗店項(xiàng)目練 第三章關(guān)系模型和規(guī)范化（第三次作業(yè) 復(fù)習(xí)重 Marcia干洗店項(xiàng)目練 第四 復(fù)習(xí)重 第五章E-R圖（第四次作業(yè) 復(fù)習(xí)重 Marcia干洗店項(xiàng) 第六章數(shù)據(jù)庫(kù)設(shè)計(jì)（第五次作業(yè) 復(fù)習(xí)重 Marcia洗衣店項(xiàng)目練 第七章數(shù)據(jù)庫(kù)創(chuàng)建、視圖、觸發(fā)器（第六次作業(yè) 復(fù)習(xí)重 第十章用SQLServer2008管理數(shù)據(jù)庫(kù)觸發(fā)器、過(guò) 第八章數(shù)據(jù)庫(kù)再設(shè) 第九章管理多用戶數(shù)據(jù) 第一 Access2007（第一次作業(yè)元數(shù)據(jù)Access的使用CreateaAccessdatabasenamedAnswerstotheProjectQuestions1.61-1.70arecontainedinthedatabaseDBPe11-IM-Ch01-WPC.accdb,whichisavailableonthetext’sWebsite( ThedatabaseiscreatedasdescribedinAppendixA.Thetwotablestobecreatedare:DEPARTMENT(DepartmentName,BudgetCode,OfficeNumber,Phone)EMPLOYEE(EmployeeNumber,Name,LastName,Department,Phone,)WhereAnunderlinedcolumnnameindicatesthetablekey(primarykey)ofthetable,andanitalicizedcolumnindicatesaforeignkeylinkinotables.Figure1-26showsthecolumncharacteristicsfortheWPCDEPARTMENTtable.Usingthecolumncharacteristics,createtheDEPARTMENTtableintheWPC.accdbdatabase.Figure1-27showsthedatafortheWPCDEPARTMENTtable.UsingDatasheetview,enterthedatashowninFigure1-27intoyourDEPARTMENTtable.Figure1-28showsthecolumncharacteristicsfortheWPCEMPLOYEEtable.Usingthecolumncharacteristics,createtheEMPLOYEEtableintheWPC.accdbdatabase.CreatetherelationshipandreferentialintegrityconstraintbetweenDEPARTMENTandEMPLOYEE.Enableenforcingofreferentialintegrityandcascadingofdataupdates,butdonotenablecascadingofdatafromdeletedrecords.UsingtheAccessformwizard,createadatainputformfortheEMPLOYEEtableandnameitWPCEmployeeDataForm.Makeanyadjustmentsnecessarytotheformsothatalldatadisplayproperly.UsethisformtoentertherestofthedataintheEMPLOYEEtableshowninFigure1-29intoyourEMPLOYEEtable.UsingtheAccessreportwizard,createareportnamedWedgewoodPacificCorporationEmployeeReportthatpresentsthedontainedinyourEMPLOYEEtablesortedbyemployeelastnameandthenbyemployeename.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.UsingtheAccessformwizard,createaformthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.Choosethedefaultoptionsforotherquestionsthatthewizardasks.OpenyourformandpagethroughyourToproducethereportasshownbelow,someworkintheFormDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinFormDesignview.UsingtheAccessreportwizard,createareportthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.ForthedontainedinyourEMPLOYEEtableinthereport,specifythatitwillbesortedbyemployeelastnameandthenbyemployeename.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.Explain,tothelevelofdetailinthischapter,whatisgoingonwithinAccessinProjectQuestions1.67,1.68,1.69,and1.70.What ponentcreatedtheformandreport?Whereisthedatastored?WhatroledoyouthinkSQLisplaying?AccessusesSQLSELECTstatementstoquerythedatabasetablesforthedatatobedisplayedintheformsandthereport.Theresultsofthequeryarestoredinatemporarytablecreatedtoholdthisdata,andthistableisthesourceofthedatadisplayedintheformandthereport.SQLisusedtogatherthedataneededfordisplayintheformandreport.第二章結(jié)構(gòu)化查詢語(yǔ)言簡(jiǎn)介（第二次作業(yè)SQL發(fā)現(xiàn)數(shù)據(jù)模式實(shí)驗(yàn)TheChangeCloseon TDayOfWeeK=The um,andaverageChangeCloseon MIN(ChangeClose)ASMinFridayChangeClose,MAX(ChangeClose)ASMaxFridayChangeClose,AVG(ChangeClose)ASAverageFridayChangeClose TDayOfWeeK=TheaverageChangeClosegroupedbyTYear.Show TYear,AVG(ChangeClose)ASAverageChangeClose GROUPBYTYearORDERBYTYear;TheaverageChangeClosegroupedbyTYearandTMonth.ShowTYearandSinceTYearandTMontharebeingdisplayed,itmakessensetosorttheresultsbyTYearandTMonthalthoughthisisnotexplicitlystatedinthequestion. TYear,AVG(ChangeClose)ASAverageChangeClose GROUPBYTYear,TMonthORDERBYTYear,TMonth;Unfortunay,thetableNDXdoesnotcontainanumericvalueofthemonth,soinordertosortthemonthscorrectly,weneedaTMonthNumberwhichhasacolumncontainingarepresentativenumberforeaonth(January=1,February=2,etc.).IntheDBPe11-NDX.accdbandDBPe11-IM-Ch02-NDX.accdbdatabases,thiscolumnisincludedinatablenamedNDX_FULL. TYear,AVG(ChangeClose)ASAverageFridayChangeClose GROUPBYTYear,TMonth,TMonthNumberORDERBYTYear,TMonthNumber;TheaverageChangeClosegroupedbyTYear,TQuarter,TMonthshownindescendingorderoftheaverage(youwillhavetogiveanametotheaverageinordertosortbyit).ShowTYear,TQuarter,andTMonth.Notethatmonthsappearinalphabeticalandnotcalendarorder.Explainwhatyouneedtodotoobtainmonthsincalendarorder. TYear,TQuarter,AVG(ChangeClose)ASAverageChangeClose GROUPBYTYear,TQuarter,TMonthORDERBYAverageChangeCloseDESC;Unfortunay,asdiscussedabove,AccesscannotprocesstheORDERBYclausecorrectlywhenanSQLbuilt-infunctionisused.Thecorrectresult,obtainedfromSQLServer2008,Inordertoobtainthemonthsincalendarorder,wewouldhavetouseanumericalvalueforeaonth(1,2,3,…,12)andsortbythosevalues. ThedifferencebetweentheumChangeCloseandtheminimumChangeClosegroupedbyTYear,TQuarter,TMonthshownindescendingorderofthedifference(youwillhavetogiveanametothedifferenceinordertosortbyit).ShowTYear,TQuarter,andTMonth. TYear,TQuarter,(MAX(ChangeClose)–MIN(ChangeClose))ASDifChangeClose GROUPBYTYear,TQuarter,TMonthORDERBYDifChangeCloseDESC;Unfortunay,asdiscussedabove,AccesscannotprocesstheORDERBYclausecorrectlybecauseitcontains iasedcomputedresult.Thecorrectresult,obtainedfromSQLServer2008,TheaverageChangeClosegroupedbyTYearshownindescendingorderoftheaverage(youwillhavetogiveanametotheaverageinordertosortbyit).Showonlygroupsforwhichtheaverageispositive. AVG(ChangeClose)ASAverageChangeClose GROUPBY AVG(ChangeClose)>0ORDERBYAverageChangeCloseDESC;Unfortunay,asdiscussedabve, AccesscannotprocesstheORDERBYclausecorrectlybecauseitcontains iasedcomputedresult.Thecorrectresult,obtainedfromSQLServer2008,Displayasinglefieldwiththedateintheform:day/monthy/year.Donotbeconcernedwithtrailingblanks.ThesolutiontothisquestionrequiresthestudenttousetheDBMShelpfunctionorotherreferencestofigureoutaconversionfunctiontoconvertthenumericaldayofthemonthtoacharacterstringthatcanbecombinedwithotherdataalreadyincharacterformat.ThetableNDXdoesnothaveanumericvalueformonth,sothenamesofthemonthswillappearinthesolution.Ifwewantthenumericvalueofthemonth,wecouldusetheNDX_Fulltable,whichhasanumericvalue.Wewouldneedtousethedatatypeconversiononthisfieldaswell.TheSQLStatementusingSQLServer2008characterstringfunctions CAST(TDayOfMonthASChar(2))+'/'TMonth+'/'+TYearASDisplayDate TDayOfMonth= TMonth='September' TYear='2001';TheSQLServer2008resultTheSQLStatement Access2007characterstringfunctionsSELECTCStr(TDayOfMonth)+'/TMonth+'/'+TYearASDisplayDate WHERENDX.TDayOfMonthANDNDX.TMonth='September'ANDNDX.TYear='2001';TheAccess2007resultItispossiblethatvolume(thenumberofsharestraded)hassomecorrelationwiththedirectionofthestockmarket.UsetheSQLyouhavelearnedinthischaptertoinvestigatethatpossibility.DevelopatleastfivedifferentSQLstatementsinyourinvestigation.Ifvolumeiscorrelatedwiththedirectionofthestockmarket,thismeansthatthereshouldbePOSITIVECORRELEATION:Highervolumewhenthemarketcloseshigher,NEGATIVECORRELATION:HighervolumewhenthemarketclosesWhendoesthemarketclosehigher?WhenNDX.ChangeCloseis TMonth,TDayOfMonth,TYear,ChangeClose ChangeClose>Whendoesthemarketcloselower?WhenNDX.ChangeCloseis TMonth,TDayOfMonth,TYear,ChangeClose ChangeClose<Now,whataretheaveragepositiveandnegative AVG(ChangeClose)ASAvgPositiveChange ChangeClose> AVG(ChangeClose)ASAvgNegativeChange ChangeClose<Now,whataretheaveragevolumesassociatedwiththepositiveandnegative AVG(ChangeClose)ASAvgPositiveChange,AVG(Volume)ASAvgVolumeOnPositiveChange ChangeClose> AVG(ChangeClose)ASAvgNegativeChange,AVG(Volume)ASAvgVolumeOnNegativeChange ChangeClose<So,whenthereisapositive,orupward,changeinthemarketwehaveaagevolumeof641417.1117318sharestraded,andwhenwehaveanegative,ordownward,changeinthemarketwehaveaagevolumeof6742500.66698428shares.Thesenumbersdonotlooksignificantlydifferent,wewillconcludethatthereisnocorrelationbetweenthedirectionofthemarketmovementandthevolumeofsharestraded(ifwewantedtobemoreformal,wecoulduseastatisticalprocedureanddoahypothesistestastowhetherornotthereisreallyastatisticallysignificantdifferencebetweenthesetwonumbers).MarciaMarcia'sDryCleaningisanupscaledrycleanersinawell-to-dosuburbanneighborhood.Marciamakesherbusinessstandoutfromthecompetitionbyprovidingsuperiorcustomerservice.Shewantstokeeptrackofeachofhercustomersandtheirorders.Ultimay,shewantstonotifythemthattheirclothesarereadyvia.Toprovidethisservice,shehasdevelopedaninitialdatabasewithseveraltables.Threeofthosetablesarethefollowing:CUSTOMER(CustomerID,Name,LastName,Phone,)ORDER(Number,CustomerNumber,DateIn,DateOut,TotalAmt)ORDER_ITEM(Number,ItemNumber,Item,ty,UnitPrice)Showalldataineachofthe NotethetwocustomersbothnamedBetsy ListthePhoneandLastNameofall Phone,LastName ListthePhoneandLastNameforallcustomerswithaNameof Phone,LastName Name=ListthePhone,DateIn,andDateOutofallordersinexcessof注意：由于ORDER是SQL括起來(lái) Phone,DateIn,DateOut CUSTOMER,[ORDER] TotalAmount CUSTOMER.CustomerID=ListthePhoneandNameofallcustomerswhosenamestartswithThecorrectSQL-92statement,whichusesthewildcard%, NameLIKEHowever,MSAccessusesthewildcard*,whichgivesthefollowingSQL NameLIKEListthePhoneandNameofallcustomerswhoselastnameincludesthecharacters,'cat'.ThecorrectSQL-92statement,whichusesthewildcard%, LastNameLIKEHowever,MSAccessusesthewildcard*,whichgivethefollowingSQL LastNameLIKEListthePhone,Name,andLastNameforallcustomerswhosesecondandthirdcharactersofphonenumberis23.Notethatsincethephonenumbersinthisdatabaseincludetheareacode,wearefindingphonenumberswith‘23’asthesecondandthirdnumbersintheareacode.Wecould,offcourse,writestatementstofind‘23’intheprefixorinthe4-digitsequenceportionofthephonenumber.ThecorrectSQL-92statement,whichusesthewildcards%and_, Name,LastName PhoneLIKEHowever,MSAccessusesthewildcards*and?,whichgivethefollowingSQL Name,LastName PhoneLIKEDeterminetheumandminimum注意：由于ORDER是SQL括起來(lái) MAX(TotalAmt)ASMaxTotalAmount,MIN(TotalAmt)ASMinTotalAmount Determinetheaverage注意：由于ORDER是SQL括起來(lái) AVG(TotalAmt)ASAvgTotalAmount Countthenumberof Count(*)ASNumberOfCustomers GroupcustomersbyLastNameandthenby GROUPBYCountthenumberofcustomershavingeachcombinationofLastNameand COUNT(*)AS GROUPBY ShowtheNameandLastNameofallcustomerswhohavehadanorderwithTotalAmountgreaterthan100.Useasubquery.PresenttheresultssortedbyLastNameinascendingorderandthen Nameindescendingorder.注意：由于ORDER是SQL括起來(lái) Name,LastName CustomerID(SELECTCustomerNumberFROM[ORDER]WHERETotalAmount>100)ORDERBYLastName,NameDESC;ShowtheNameandLastNameofallcustomerswhohavehadanorderwithTotalAmountgreaterthan100.Useajoin.PresenttheresultssortedbyLastNameinascendingorderandthenNameindescendingorder.注意：由于ORDER是SQL括起來(lái) Name,LastName CUSTOMER,[ORDER] CUSTOMER.CustomerID=[ORDER].CustomerNumber TotalAmount>100ORDERBYLastName,NameShowtheNameandLastNameofallcustomerswhohavehadanorderwithanItemnamed“DressShirt”.Useasubquery.PresenttheresultssortedbyLastNameinascendingorderandthen Nameindescendingorder.注意：由于ORDER是SQL括起來(lái) Name,LastName CustomerID(SELECTCustomerNumber Number(SELECTNumberFROMORDER_ITEMWHEREItem='DressShirt'))ORDERBYLastName,NameDESC;ShowtheNameandLastNameofallcustomerswhohavehadanorderwithanItemnamed“DressShirt”.Useajoin.PresenttheresultssortedbyLastNameinascendingorderandthenNameindescendingorder.注意：由于ORDER是SQL括起來(lái) Name, CUSTOMER,[ORDER], CUSTOMER.CustomerID=[ORDER].CustomerNumber [ORDER].Number=ORDER_ITEM.Number ORDER_ITEM.Item='DressShirt'ORDERBYLastName,NameShowtheName,LastNameandTotalAmountofallcustomerswhohavehadanorderwithanItemnamed“DressShirt”.Useajoinwithasubquery.PresentresultssortedbyLastNameinascendingorderandthenNameindescendingorder.注意：由于ORDER是SQL括起來(lái) Name,LastName,TotalAmount CUSTOMER,[ORDER] CUSTOMER.CustomerID=[ORDER].CustomerNumber [ORDER].NumberIN(SELECTNumberFROMORDER_ITEMWHEREItem='DressShirt')ORDERBYLastName,NameDESC;第三章關(guān)系模型和規(guī)范化（第三次作業(yè)1NF1NF-關(guān)系的特征-應(yīng)用，哪里不滿足2NF（不存在非主屬性對(duì)候選鍵的部分依賴）R(A,B,N,O,P)A->N2NF。,,BCNF--將表轉(zhuǎn)化為BCNF3.13ConsidertheSTAFF_MEETING(EmployeeName,ProjectName,Therowsofthistablerecordthefactthatanemployeefromaparticularprojectattendedameetingonthegivendate.Assumethataprojectmeetsatmostonceperday.Also,assumethatonlyoneemployeerepresentsagivenproject,butthatemployeescanbeassignedtomultipleprojects.StatethefunctionalSincetherecanonlybeoneprojectmeetingforaparticularprojectperday,we(ProjectName,Date)→Sincethereisonlyoneemployeeassignedtothemeetingsforeachproject,weProjectName→TransformthistableintooneormoretablesinBCNF.Statetheprimarykeys,candidatekeys,foreignkeys,andreferentialintegrityconstraints.(ProjectName,Date)→EmployeeNameProjectName→EmployeeNameSTAFF_MEETINGCANDIDATEKEYS:(ProjectName,IseverydeterminantacandidateNO,thereforetherelationisNOTinTherefore,moveProjectName→EmployeeintoanothertableSTAFF_MEETING_2(ProjectName,Date)STAFF_MEETING_EMPLOYEE(ProjectName,EmployeeName)STAFF_MEETING_2FUNCTIONALDEPENDENCIES:(ProjectName,Date)→(ProjectName,IseverydeterminantacandidateYES,thereforetherelationisinProjectName→EmployeeNameSTAFF_MEETING_2CANDIDATEKEYS:IseverydeterminantacandidateYES,thereforetherelationisinBCNFThetablesarenowallinBCNF.FINALSETOFSTAFF_MEETING_2(ProjectName,Date)STAFF_MEETING_EMPLOYEE(ProjectName,EmployeeName)REFENTIALINTEGRITYCONSTRAINTS:ProjectNameinSTAFF_MEETING_EMPLOYEEmustexistinIsyourdesigninpartbanimprovementovertheoriginaltable?Whatadvantagesanddisadvantagesdoesithave?Yes,thedesigninpartbisanimprovementovertheoriginaltable.TheadvantageisthatitisnotsubjecttomodificationanomaliessincealltablesareinBCNF.Theonlydisadvantageithasisthattheremustbestaffmeetingdataentered(ProjectNameandDateinSTAFF_MEETING_2)beforeanEmployeeNamecanbeenteredinSTAFF_MEETING_EMPLOYEE.Thismayseemillogicaltosomeoneenteringthedata.ConsidertheSTUDENT(Number,Name,Dorm,RoomType,DormCost,Club,ClubCost,Sibling,Assumethatstudentspaydifferentdormcosts,dependingonthetypeofroomtheyhave,butthatallmembersofaclubpaythesamecost.AssumethatstudentscanhavemultipleStateanymultivaluedWewillassumethatNumber→Namewherenameisnotunique(i.e.,theremaybemorethanone“JohnSmith”,eachwithadifferentstudentnumber).Thenthemultivalueddependenciesare:Number→→ClubNumber→→SiblingNumber→→NicknameNote:WecannotassumethatName→→NicknamebecauseNameisnotunique.Forexample,oneJohnSmithmayhavethenickname“Johnny”whileanotherJohnSmithhasthenickname”Joe.”IfName→→NicknamethenJohnSmith→→(“Johnny”,“Joe”)whichmeansthatbothnicknamesapplytobothJohnSmiths.Butthisisnotthecase–eachJohnSmithhasonlyonenickname,andtheyarenotthesame.StatethefunctionalWewillassumethatNumber→Namewherenameisnotunique(i.e.,theremaybemorethanoneJohnSmith,eachwithadifferentstudentnumber).Thenthefunctionaldependenciesare:Number→NameNumber→DormNumber→RoomTypeRoomType→DormCostClub→ClubCostNote:ThisassumesthatonlyRoomType→DormCost–thatis,thecostofacertaintypeofdormroomisthesamenomatterwhatdormthestudentislivingin.ternateassumptionwouldbethat(Dorm,RoomType)→DormCost,wherethecostofthetypeofdormroomvariesfromdormtodorm.TransformthistableintotwoormoretablessuchthateachtableisinBCNFandin4NF.Statetheprimarykeys,candidatekeys,foreignkeys,andreferentialintegrityWe’llmovetheobviousmultivalueddependenciesintotheirowntables,andthencheckforBCNF.IFwehaveBCNFandnomultivalueddependencies,wealsohave4NF:STEPONE:MOVEMULTIVALUEDDEPENDENCIESINTOSEPARATESTUDENT_2(Number,Name,Dorm,RoomType,DormCost)STUDENT_CLUB_MEMBERSHIP(Number,Club,ClubCost)STUDENT_SIBLING(Number,Sibling)STUDENT_NICKNAME(Number,Nickname)STEPTWO:CHECKEACHOFTHERESULTINGTABLESFORBNCF:STEPTWO(A):CHECKSTUDENTSTUDENT_2FUNCTIONALSTUDENT_2(Number,Name,Dorm,RoomType,Number→NameNumber→DormNumber→RoomTypeRoomType→DormCostSTUDENT_2CANDIDATEIseverydeterminantacandidateNO,RoomTypeisNOTacandidatekey.ThereforetherelationisNOTinBCNF.Therefore,moveRoomType→DormCostintoanothertableSTUDENT_3(Number,Name,Dorm,RoomType)DORM_RATE(RoomType,DormCost)STEPTWO(A)(1):CHECKSTUDENT_3:STUDENTFUNCTIONALDEPENDENCIES:STUDENT_3(Number,Name,Dorm,Number→NameNumber→DormNumber→RoomTypeSTUDENTCANDIDATEIseverydeterminantacandidateYES,ThereforeSTUDENT_3isinBNCF.STEPTWO(A)(2):CHECKSTUDENT:DORM_RATEFUNCTIONALDEPENDENCIES:DORM_RATE(RoomType,RoomType→DormCostDORM_RATECANDIDATEKEYS:IseverydeterminantacandidateYES,ThereforeDORM_RATEisinBNCF.STEPTWO(B):CHECKSTUDENT_CLUB_MEMBERSHIPSTUDENT_CLUB_MEMBERSHIP(Number,Club,(Number,Club)→ClubCostClub→ClubCost(Number,IseverydeterminantacandidateNO,ClubisnotacandidateTherefore,moveClub→ClubCostintoanotherSTUDENT_CLUB_MEMBERSHIP(Number,STUDENT_CLUB_COST(Club,STEPTWO(B)(1):CHECKSTUDENT_CLUB_MEMBERSHIP:STUDENT_CLUB_MEMBERSHIPFUNCTIONALDEPENDENCIES:STUDENT_CLUB_MEMBERSHIP(Number,Club) NumberdoesnotdetermineClub,andClubdoesnotdetermineNumber.(Number,IseverydeterminantacandidateYES,inthiscasethereareNOdeterminants,butthismeetsthecriteria!ThereforeSTUDENT_CLUB_MEMBERSHIPisinBNCF.Arethefieldsofthemultivalueddependencytheonlyfieldsinthistable?YES,ThereforeSTUDENT_CLUB_MEMBERSHIPisinSTEPTWO(B)(2):CHECKSTUDENT_CLUB_COST:STUDENT_CLUB_COSTFUNCTIONALDEPENDENCIES:STUDENT_CLUB_COST(Club,Club→STUDENT_CLUB_COSTCANDIDATEIseverydeterminantacandidateYES,ThereforeSTUDENT_CLUB_COSTisinBNCF.STEPTWO(C):CHECKSTUDENT_SIBLINGSTUDENT_SIBLING(Number, NumberdoesnotdetermineSibling,andSiblingdoesnotdetermineNumber.(Number,IseverydeterminantacandidateYES,inthiscasethereareNOdeterminants,butthismeetsthecriteria!ThereforeSTUDENT_SIBLINGisinArethefieldsofthemultivalueddependencytheonlyfieldsinthisYES,ThereforeSTUDENT_SIBLINGisin4NF.STEPTWO(D):CHECKSTUDENT_NCIKNAMESTUDENT_NICKNAMEFUNCTIONALDEPENDENCIES:STUDENT_NICKNAME(Number, NumberdoesnotdetermineNickname,andNicknamedoesnotdetermineNumber.(Number,IseverydeterminantacandidateYES,inthiscasethereareNOdeterminants,butthismeetsthecriteria!ThereforeSTUDENT_NICKNAMEisinArethefieldsofthemultivalueddependencytheonlyfieldsinthisYES,ThereforeSTUDENT_NICKNAMEisinALLTABLESARENOWINBCNFANDSTEPTHREE:STATEFINALMODELPrimaryKeysareunderlined.ForeignKeysareitalicized.Non-PrimaryKeyCandidateKeys(AlternatePrimaryKeys)arestatedfollowingeachrelationasAlternateKeys[NOTE:Noneexist].ReferentialIntegrityConstraintsarestatedfollowingeachSTUDENT_3(Number,Name,Dorm,WHERESTUDENT.RoomTypemustexistinDORM_RATE.RoomTypeDORM_RATE(RoomType,DormCost)WHERESTUDENT_CLUB_MEMBERSHIP.Numbermustexistin STUDENT_CLUB_MEMBERSHIP.ClubmustexistinSTUDENT_CLUB_COST(Club,ClubCost)STUDENT_SIBLING(Number,Sibling)WHERESTUDENT_SIBLING.NumbermustexistinSTUDENT_3.NumberSTUDENT_NICKNAME(Number,Nickname)WHERE mbermustexistinMarciaAssumethatMarciakeepsatableofdataabouthercustomers.Considerjustthefollowingpartofthattable:CUSTOMER(Phone,Name,ExplaintheconditionsunderwhicheachofthefollowingarePhone→ Name, Name)→(Phone,LastName) Name)→Phone→→Phone Phone→→(Name,Phone→ Name,TRUEwhenphonenumbersare Name)→TRUEwheneachcombinationofphonenumberandNameis(Phone,LastName) TRUEwheneachcombinationofphonenumberandLastNameis Name)→TRUEwheneachcombinationofNameandLastNameisPhone→→TRUEwhenaphonenumberisassociatedwithmorethanonePhone TRUEwhenaphonenumberisassociatedwithmorethanonePhone→→(Name,TRUEwhenaphonenumberisassociatedwithmorethanonecombinationofNameandIsconditionA.7thesameasconditionsA.5andA.6?WhyorwhyNo,itisIfweweredealingwithregularfunctionaldependencies,theanswerwouldbeyes,condition7isthesameastheconditions5and6.ThisisbecauseofthefactthatifA→(B,C),thenA→BandA→Butwhendealingwithmultivalueddependencies,somethingislostiftheyareequivalent.Considerthefollowingtable:234-1234→→(“John”,“Joan”)[Name]234-1234→→(“Smith”,“Jones”)[LastName]234-1234→→(“JohnSmith”,“JoanJones”)[Name,Thefactisthatcondition7givesus“JohnSmith”and“JoanJones,”bothofwhicharerecognizableindividualswithavalidcombinationofNameandLastName.Butifweallowacombinationofconditions5and6wealsoget“JohnJones”and“JoanSmith,”whicharenonexistentpeople.Thus,condition7showstheequivalentofacompositeidentifierorcompositekey,andthisisNOTequivalenttotherandomsumofitsparts.ConsidertheCUSTOMER(Phone,Name,LastName)ORDER(OrderNumber,DateIn,DateOut,Phone)StateanappropriatereferentialintegrityORDER.PhonemustexistinConsidertheCUSTOMER(Phone,Name,ORDER(OrderNumber,DateIn,DateOut,Name,LastName)Whatdoesthefollowingreferentialintegrityconstraintmean?ORDER(Name,LastName)mustbeinCUSTOMER(Name,IsthisconstraintthesameasthesetofreferentialintegrityORDER(Name)mustbeinCUSTOMER(Name)ORDER(LastName)mustbeinCUSTOMER(LastName)ExplainwhyorwhyNo,theseconstraintsarenotequivalent.TheconstraintORDER(Name,LastName)mustbeinCUSTOMER(Name,requiresthatthecompositeforeignkeycombinationoftheNameandLastName(“JoeSmith”)mustexistasthecompositeprimarykeyinonerecordinCUSTOMER,whiletheconstraintsetORDER(Name)mustbeinCUSTOMER(Name)ORDER(LastName)mustbeinCUSTOMERdoesnotacknowledgetheexistenceofthecompositekeys,andwouldbemetiftheName(“Joe”)existedinonerecordinCUSTOMERandtheLastName(“Smith”)existedinanother,differentrecordinCUSTOMER.SinceeachORDERshouldbeassociatedwithonespecificCUSTOMER,weshouldusetheconstraintwiththecompositekeys:ORDER(Name,LastName)mustbeinCUSTOMER(Name,DoyoupreferthedesigninBorthedesigninC?ExplainyourThedesigninBispreferablegiventhatnamesarenotunique.WemayhavemorethanoneCUSTOMERnamed“JoeSmith”,buteachofthemshouldhaveadifferentphonenumber.Iftheydon’t,weneedtoaddasurrogatekey(CustomerNumberorCustomerID).Atthesametime,thisdesignislimitedbythefactthatPhonemustbeuniqueifitistofunctionastheprimarykey.Thismeansthatwecanonlyassociateoneinahouseholdwiththatphoneandthereforethcount.Again,addingasurrogatekey(CustomerNumberorCustomerID)wouldsolvetheproblem(andwouldbeamuchbetterdesign).TransformthefollowingtableintotwoormoretablesinBCNFand4NF.Indicatetheprimarykeys,candidatekeys,foreignkeys,andreferentialintegrityconstraints.Makeandstateassumptionsasnecessary.ORDER(CustomerNumber,Name,LastName,Phone,OrderNumber,DateIn,DateOut,ItemType,ty,ItemPrice,ExtendedPrice,SpecialInstructions)CustomerNumber→(Name,LastName),butnotCustomerNumber→→(Name,LastName)[NOTE:ThisanticipatesquestionFbelow]CustomerNumber→→Phone.Thismeansthattheremaybemorethanonephonenumberforeachcustomer.OneCUSTOMERhasmanyORDERS,buteachORDERisassociatedwithonlyoneOneORDERhasmanyItemTypes,buteachItemTypeoccursonlyonceinanyItempricedoesnotvaryfromordertoorder,soItemTypedeterminesSpecialInstructionsisassociatedwitheachitemtype,sincedifferentinstructionsmaybegivenfordifferentitems.Ordernumberisanumberassignedtotheordersthemselves,withoutassociationwithanyparticularcustomer.Thuswehavesuchnumbersas“123454”,123455”,etc,ratherthan“Customer101,Order1”,Customer101,Order2”,Customer102,Order1”,etc.ItemPricecanvarywithordersothatspecialpricingoffersarereflectedintheSTEPONE:LISTTHEMULTIVALUEDThereisonemultivaluedTosimplifytheothersteps,we’llbreakthisoutintoit’sowntableORDER_2(CustomerNumber,Name,LastName,OrderNumber,DateIn,DateOut,ItemType,ty,ItemPrice,ExtendedPrice,SpecialInstructions)CUSTOMER_PHONE(CustomerNumber,TheforeignkeywillbeCustomerNumber,butwestillhavenormalizationtoBCNFtodo,sowe’lladdthereferentialintegrityconstraintforthisrelationat oftheprocesswhenwehaveallthenormalizedrelations.STEPTWO:LISTTHEFUNCTIONALORDER_2(CustomerNumber,Name,LastName,OrderNumber,DateIn,DateOut,ItemType,ty,ItemPrice,ExtendedPrice,SpecialInstructions)Theotherattributeshavethefollowingfunctionaldependencies.Notethatthereareseveralthatseemto“overlap”–forexampleboththedeterminantsOrderNumberand(CustomerNumber,OrderNumber)determinemanyofthesameattributes.Thatisnormalforasetofattributesthatcontainmanythemes,andthese“overlaps”willbeeliminatedinthenormalizationprocess:OrderNumber,ItemType)→(CustomerNumber,Name,LastName,DateIn,DateOut,ty,ItemPrice,ExtendedPrice,SpecialInstructions)OrderNumber→(CustomerNumber, Name,LastName,DateIn,DateOut)CustomerNumber→(Name,LastName)Notethatyourstudentsmaylistsomeothervalid,butredundant,functionalthereareseveralthatseemto“overlap”thefunctionaldependenciesshownabout.Thesewillhavecompositedeterminants–forexampleboththedeterminantOrderNumbershownaboveandthedeterminant(CustomerNumber,OrderNumber)shownbelowdeterminemanyofthesameattributes.Thatisnormalforasetofattributesthatcontainmanythemes,andthese“overlaps”willbeeliminatedinthenormalizationprocess:CustomerNumber,OrderNumber)→(Name,LastName,DateIn,DateOut)STEPTHREE:LISTTHEINITIALCANDIDATEKEYSIseverydetermi