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

完美WORD格式专业整理分享Exercise2.2.1aForrelationAccounts,theattributesare:acctNo,type,balanceForrelationCustomers,theattributesare:firstName,lastName,idNo,accountExercise2.2.1bForrelationAccounts,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)Exercise2.2.1cForrelationAccountsandthefirsttuple,thecomponentsare:123456acctNosavingstype12000balanceForrelationCustomersandthefirsttuple,thecomponentsare:RobbiefirstNameBankslastName901-222idNo12345accountExercise2.2.1dForrelationAccounts,arelationschemais:Accounts(acctNo,type,balance)ForrelationCustomers,arelationschemais:Customers(firstName,lastName,idNo,account)Exercise2.2.1eAnexampledatabaseschemais:Accounts(acctNo,type,balance)Customers(firstName,完美WORD格式专业整理分享lastName,idNo,account)Exercise2.2.1fAsuitabledomainforeachattribute:acctNoIntegertypeStringbalanceIntegerfirstNameStringlastNameStringidNoString(becausethereisahyphenwecannotuseInteger)accountIntegerExercise2.2.1gAnotherequivalentwaytopresenttheAccountrelation:acctNobalancetype3456725savings234561000checking1234512000savingsAnotherequivalentwaytopresenttheCustomersrelation:idNofirstNamelastNameaccount805-333LenaHand23456805-333LenaHand12345901-222RobbieBanks12345Exercise2.2.2Examplesofattributesthatarecreatedforprimarilyservingaskeysinarelation:UniversalProductCode(UPC)usedwidelyinUnitedStatesandCanadatotrackproductsinstores.SerialNumbersonawidevarietyofproductstoallowthemanufacturertoindividuallytrackeachproduct.VehicleIdentificationNumbers(VIN),auniqueserialnumberusedbytheautomotiveindustrytoidentifyvehicles.Exercise2.2.3aWecanorderthethreetuplesinanyof3!=6ways.Also,thecolumnscanbeorderedinanyof3!=6ways.Thus,thenumberofpresentationsis6*6=36.Exercise2.2.3bWecanorderthethreetuplesinanyof5!=120ways.Also,thecolumnscanbeorderedinanyof4!=24ways.Thus,thenumberofpresentationsis120*24=2880Exercise2.2.3c完美WORD格式专业整理分享Wecanorderthethreetuplesinanyofm!ways.Also,thecolumnscanbeorderedinanyofn!ways.Thus,thenumberofpresentationsisn!m!Exercise2.3.1aCREATETABLEProduct(makerCHAR(30),modelCHAR(10)PRIMARYKEY,typeCHAR(15));Exercise2.3.1bCREATETABLEPC(modelCHAR(30),speedDECIMAL(4,2),ramINTEGER,hdINTEGER,priceDECIMAL(7,2));Exercise2.3.1cCREATETABLELaptop(modelCHAR(30),speedDECIMAL(4,2),ramINTEGER,hdINTEGER,screenDECIMAL(3,1),priceDECIMAL(7,2));Exercise2.3.1dCREATETABLEPrinter(modelCHAR(30),colorBOOLEAN,typeCHAR(10),priceDECIMAL(7,2));Exercise2.3.1eALTERTABLEPrinterDROPcolor;Exercise2.3.1fALTERTABLELaptopADDodCHAR(10)DEFAULT‘none’;Exercise2.3.2aCREATETABLEClasses(classCHAR(20),typeCHAR(5),countryCHAR(20),numGunsINTEGER,boreDECIMAL(3,1),displacementINTEGER);Exercise2.3.2b完美WORD格式专业整理分享CREATETABLEShips(nameCHAR(30),classCHAR(20),launchedINTEGER);Exercise2.3.2cCREATETABLEBattles(nameCHAR(30),dateDATE);Exercise2.3.2dCREATETABLEOutcomes(shipCHAR(30),battleCHAR(30),resultCHAR(10));Exercise2.3.2eALTERTABLEClassesDROPbore;Exercise2.3.2fALTERTABLEShipsADDyardCHAR(30);Exercise2.4.1aR1:=σspeed≥3.00(PC)R2:=πmodel(R1)Exercise2.4.1bR1:=σhd≥100(Laptop)R2:=Product(R1)R3:=πmaker(R2)Exercise2.4.1cR1:=σmaker=B(ProductPC)R2:=σmaker=B(ProductLaptop)R3:=σmaker=B(ProductPrinter)R4:=πmodel,price(R1)R5:=πmodel,price(R2)R6:=πmodel,price(R3)model100510061013makerEABFG完美WORD格式专业整理分享R7:=R4R5R6modelprice100464910056301006104920071429Exercise2.4.1dR1:=σcolor=trueANDtype=laser(Printer)R2:=πmodel(R1)Exercise2.4.1eR1:=σtype=laptop(Product)R2:=σtype=PC(Product)R3:=πmaker(R1)R4:=πmaker(R2)R5:=R3–R4Exercise2.4.1fR1:=ρPC1(PC)R2:=ρPC2(PC)R3:=R1(PC1.hd=PC2.hdANDPC1.modelPC2.model)R2R4:=πhd(R3)Exercise2.4.1gR1:=ρPC1(PC)R2:=ρPC2(PC)R3:=R1(PC1.speed=PC2.speedANDPC1.ram=PC2.ramANDPC1.modelPC2.model)R2R4:=πPC1.model,PC2.model(R3)Exercise2.4.1hR1:=πmodel(σspeed≥2.80(PC))πmodel(σspeed≥2.80(Laptop))R2:=πmaker,model(R1Product)R3:=ρR3(maker2,model2)(R2)R4:=R2(maker=maker2ANDmodelmodel2)R3R5:=πmaker(R4)model30033007makerFGhd25080160PC1.modelPC2.model10041012makerB完美WORD格式专业整理分享Exercise2.4.1iR1:=πmodel,speed(PC)R2:=πmodel,speed(Laptop)R3:=R1R2R4:=ρR4(model2,speed2)(R3)R5:=πmodel,speed(R3(speedspeed2)R4)R6:=R3–R5R7:=πmaker(R6Product)makerBExercise2.4.1jR1:=πmaker,speed(ProductPC)R2:=ρR2(maker2,speed2)(R1)R3:=ρR3(maker3,speed3)(R1)R4:=R1(maker=maker2ANDspeedspeed2)R2R5:=R4(maker3=makerANDspeed3speed2ANDspeed3speed)R3R6:=πmaker(R5)Exercise2.4.1kR1:=πmaker,model(ProductPC)R2:=ρR2(maker2,model2)(R1)R3:=ρR3(maker3,model3)(R1)R4:=ρR4(maker4,model4)(R1)R5:=R1(maker=maker2ANDmodelmodel2)R2R6:=R3(maker3=makerANDmodel3model2ANDmodel3model)R5R7:=R4(maker4=makerAND(model4=modelORmodel4=model2ORmodel4=model3))R6R8:=πmaker(R7)Exercise2.4.2aEmakerADEmakerABDE完美WORD格式专业整理分享πmodelσspeed≥3.00PCExercise2.4.2bLaptopσhd≥100ProductπmakerExercise2.4.2cσmaker=Bπmodel,priceσmaker=Bπmodel,priceσmaker=Bπmodel,priceProductPCLaptopPrinterProductProduct完美WORD格式专业整理分享Exercise2.4.2dPrinterσcolor=trueANDtype=laserπmodelExercise2.4.2eσtype=laptopσtype