数据库系统基础教程第二章答案

ExerciserelationAccounts,theattributesare:acctNo,type,balanceForrelationCustomers,theattributesare:firstName,lastName,idNo,accountExerciserelationAccounts,thetuplesare:(12345,savings,12000),(23456,checking,1000),(34567,savings,25)ForrelationCustomers,thetuplesare:(Robbie,Banks,901-222,12345),(Lena,Hand,805-333,12345),(Lena,Hand,805-333,23456)ExerciserelationAccountsandthefirsttuple,thecomponentsare:123456acctNosavingstype12000balanceForrelationCustomersandthefirsttuple,thecomponentsare:RobbiefirstNameBankslastName901-222idNo12345accountExerciserelationAccounts,arelationschemais:Accounts(acctNo,type,balance)ForrelationCustomers,arelationschemais:Customers(firstName,lastName,idNo,account)Exerciseexampledatabaseschemais:Accounts(acctNo,type,balance)Customers(firstName,lastName,idNo,account)Exercisesuitabledomainforeachattribute:acctNoIntegertypeStringbalanceIntegerfirstNameStringlastNameStringidNoString(becausethereisahyphenwecannotuseInteger)accountIntegerExerciseequivalentwaytopresenttheAccountrelation:acctNobalancetype3456725savings234561000checking1234512000savingsAnotherequivalentwaytopresenttheCustomersrelation:idNofirstNamelastNameaccount805-333LenaHand23456805-333LenaHand12345901-222RobbieBanks12345Exerciseofattributesthatarecreatedforprimarilyservingaskeysinarelation:UniversalProductCode(UPC)usedwidelyinUnitedStatesandCanadatotrackproductsinstores.SerialNumbersonawidevarietyofproductstoallowthemanufacturertoindividuallytrackeachproduct.VehicleIdentificationNumbers(VIN),auniqueserialnumberusedbytheautomotiveindustrytoidentifyvehicles.Exercisecanorderthethreetuplesinanyof3!=6ways.Also,thecolumnscanbeorderedinanyof3!=6ways.Thus,thenumberofpresentationsis6*6=36.Exercisecanorderthethreetuplesinanyof5!=120ways.Also,thecolumnscanbeorderedinanyof4!=24ways.Thus,thenumberofpresentationsis120*24=2880Exercisecanorderthethreetuplesinanyofm!ways.Also,thecolumnscanbeorderedinanyofn!ways.Thus,thenumberofpresentationsisn!m!ExerciseTABLEProduct(makerCHAR(30),modelCHAR(10)PRIMARYKEY,typeCHAR(15));ExerciseTABLEPC(modelCHAR(30),speedDECIMAL(4,2),ramINTEGER,hdINTEGER,priceDECIMAL(7,2));ExerciseTABLELaptop(modelCHAR(30),speedDECIMAL(4,2),ramINTEGER,hdINTEGER,screenDECIMAL(3,1),priceDECIMAL(7,2));ExerciseTABLEPrinter(modelCHAR(30),colorBOOLEAN,typeCHAR(10),priceDECIMAL(7,2));ExerciseTABLEPrinterDROPcolor;ExerciseTABLELaptopADDodCHAR(10)DEFAULT‘none’;ExerciseTABLEClasses(classCHAR(20),typeCHAR(5),countryCHAR(20),numGunsINTEGER,boreDECIMAL(3,1),displacementINTEGER);ExerciseTABLEShips(nameCHAR(30),classCHAR(20),launchedINTEGER);ExerciseTABLEBattles(nameCHAR(30),dateDATE);ExerciseTABLEOutcomes(shipCHAR(30),battleCHAR(30),resultCHAR(10));ExerciseTABLEClassesDROPbore;ExerciseTABLEShipsADDyardCHAR(30);ExerciseR1:=σspeed≥(PC)R2:=πmodel(R1)model100510061013ExerciseR1:=σhd≥100(Laptop)R2:=Product(R1)R3:=πmaker(R2)makerEABFGExerciseR1:=σmaker=B(ProductPC)R2:=σmaker=B(ProductLaptop)R3:=σmaker=B(ProductPrinter)R4:=πmodel,price(R1)R5:=πmodel,price(R2)R6:=πmodel,price(R3)R7:=R4R5R6modelprice100464910056301006104920071429ExerciseR1:=σcolor=trueANDtype=laser(Printer)R2:=πmodel(R1)model30033007ExerciseR1:=σtype=laptop(Product)R2:=σtype=PC(Product)R3:=πmaker(R1)R4:=πmaker(R2)R5:=R3–R4makerFGExerciseR1:=ρPC1(PC)R2:=ρPC2(PC)R3:=R1=AND<>R2R4:=πhd(R3)hd25080160ExerciseR1:=ρPC1(PC)R2:=ρPC2(PC)R3:=R1=AND=ANDmodel2)R3R5:=πmaker(R4)makerBEExerciseR1:=πmodel,speed(PC)R2:=πmodel,speed(Laptop)R3:=R1R2R4:=ρR4(model2,speed2)(R3)R5:=πmodel,speed(R3(speedspeed2)R2R5:=R4(maker3=makerANDspeed3<>speed2ANDspeed3<>speed)R3R6:=πmaker(R5)makerADEExerciseR1:=πmaker,model(ProductPC)R2:=ρR2(maker2,model2)(R1)R3:=ρR3(maker3,model3)(R1)R4:=ρR4(maker4,model4)(R1)R5:=R1(maker=maker2ANDmodel<>model2)R2R6:=R3(maker3=makerANDmodel3<>model2ANDmodel3<>model)R5R7:=R4(maker4=makerAND(model4=modelORmodel4=model2ORmodel4=model3))R6R8:=πmaker(R7)makerABDEExerciseR1:=σbore≥16(Classes)R2:=πclass,country(R1)classcountryIowaUSANorthCarolinaUSAYamatoJapanExerciseR1:=σlaunched<1921(Ships)R2:=πname(R1)nameHarunaHieiKirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyalOakRoyalSovereignTennesseeExerciseR1:=σbattle=DenmarkStraitANDresult=sunk(Outcomes)R2:=πship(R1)shipBismarckHoodExerciseR1:=ClassesShipsR2:=σlaunched>1921ANDdisplacement>35000(R1)R3:=πname(R2)nameIowaMissouriMusashiNewJerseyNorthCarolinaWashingtonWisconsinYamatoExerciseR1:=σbattle=Guadalcanal(Outcomes)R2:=Ships(ship=name)R1R3:=ClassesR2R4:=πname,displacement,numGuns(R3)namedisplacementnumGunsKirishima320008Washington370009ExerciseR1:=πname(Ships)R2:=πship(Outcomes)R3:=ρR3(name)(R2)R4:=R1R3nameCaliforniaHarunaHieiIowaKirishimaKongoMissouriMusashiNewJerseyNorthCarolinaRamilliesRenownRepulseResolutionRevengeRoyalOakRoyalSovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDukeofYorkFusoHoodKingGeorgeVPrinceofWalesRodneyScharnhorstSouthDakotaWestVirginiaYamashiroExerciseFromassumingthateveryclasshasoneshipnamedaftertheclass.R1:=πclass(Classes)R2:=πclass(σname<>class(Ships))R3:=R1–R2classBismarckExerciseR1:=πcountry(σtype=bb(Classes))R2:=πcountry(σtype=bc(Classes))R3:=R1∩R2countryJapanGt.BritainExerciseR1:=πship,result,date(Battles(battle=name)Outcomes)R2:=ρR2(ship2,result2,date2)(R1)R3:=R1(ship=ship2ANDresult=damagedANDdate500(PC)=øModel1011violatesthisconstraint.ExerciseAND≤(PC×Laptop))=øModels2002,2006,2008violatetheconstraint.Exercise>16(Classes))=øTheYamatoclassviolatestheconstraint.Exercise>9ANDbore>14(Classes))=øNoviolationstotheconstraint.ExercisecomplexexpressionisbestseenasasequenceofstepsinwhichwedefinetemporaryrelationsR1throughR5thatstandfornodesofexpressiontrees.Hereisthesequence:R1(class,name):=πclass,name(ClassesShips)R2(class2,name2):=ρR2(class2,name2)(R1)R3(class3,name3):=ρR3(class3,name3)(R1)R4(class,name,class2,name2):=R1(class=class2ANDname<>name2)R2R5(class,name,class2,name2,class3,name3):=R4(class=class3ANDname<>name3ANDname2<>name3)R3TheconstraintisR5=øTheKongo,IowaandRevengeclassesviolatetheconstraint.Exercise=bb(Classes))∩πcountry(σtype=bc(Classes))=øJapanandGt.Britainviolatetheconstraint.ExercisecomplexexpressionisbestseenasasequenceofstepsinwhichwedefinetemporaryrelationsR1throughR5thatstandfornodesofexpressiontrees.Hereisthesequence:R1(ship,battle,result,class):=πship,battle,result,class(Outcomes(ship=name)Ships)R2(ship,battle,result,numGuns):=πship,battle,result,numGuns(R1Classes)R3(ship,battle):=πship,battle(σnumGuns<9ANDresult=sunk(R2))R4(ship2,battle2):=ρR4(ship2,battle2)(πship,battle(σnumGuns>9(R2)))R5(ship2):=πship2(R3(battle=battle2)R4)TheconstraintisR5=øNoviolationstotheconstraint.SincetherearesomeshipsintheOutcomestablethatarenotintheShipstable,weareunabletodeterminethenumberofgunsonthatship.ExerciserastheschemaA1,A2,…,AnandsastheschemaB1,B2,…,Bn:πr(R)πs(S)=øwhereistheantisemijoinExerciseformofaconstraintasE1=E2canbeexpressedastheothertwoconstraints.Usingthe“equatinganexpressiontotheemptyset”method,wecansimplysay:E1–E2=øAsacontainment,wecansimplysay:E1⊆E2ANDE2⊆E1Thus,theformE1=E2ofaconstraintcannotexpressmorethanthetwootherformsdiscussedinthissection.