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

SolutionsChapter77.1.1a)CREATETABLEMovies(titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INT,PRIMARYKEY(title,year),FOREIGNKEY(producerC#)REFERENCESMovieExec(cert#));orCREATETABLEMovies(titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INTREFERENCESMovieExec(cert#),PRIMARYKEY(title,year));b)CREATETABLEMovies(titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INTREFERENCESMovieExec(cert#)ONDELETESETNULLONUPDATESETNULL,PRIMARYKEY(title,year));c)CREATETABLEMovies(titleCHAR(100),yearINT,lengthINT,genreCHAR(10),studioNameCHAR(30),producerC#INTREFERENCESMovieExec(cert#)ONDELETECASCADEONUPDATECASCADE,PRIMARYKEY(title,year));d)CREATETABLEStarsIn(movieTitleCHAR(100)REFERENCESMovie(title),movieYearINT,starNameCHAR(30),PRIMARYKEY(movieTItle,movieYear,starName));e)CREATETABLEStarsIn(movieTitleCHAR(100)REFERENCESMovie(title)ONDELETECASCADE,movieYearINT,starNameCHAR(30),PRIMARYKEY(movieTItle,movieYear,starName));7.1.2Todeclaresuchaforeign-keyconstraintbetweentherelationsMovieandStarsIn,valuesofthereferencingattributesinMovieshouldappearinMovieStarasuniquevalues.However,basedonprimarykeydeclarationinrelationStarIn,theuniquenessofmoviesisguaranteedwithmovieTitle,movieYear,andstarNameattributes.EvenwithtitleandyearasreferencingattributesthereisnowayofreferencinguniquemoviefromStarsInwithoutstarNameinformation.Therefore,suchaconstraintcannotbeexpressedusingaforeign-keyconstraint.7.1.3ALTERTABLEProductADDPRIMARYKEY(model);ALTERTABLEPCADDFOREIGNKEY(model)REFERENCESProduct(model);ALTERTABLELaptopADDFOREIGNKEY(model)REFERENCESProduct(model);ALTERTABLEPrinterADDFOREIGNKEY(model)REFERENCESProduct(model);7.1.4ALTERTABLEClassesADDPRIMARYKEY(class);ALTERTABLEShipsADDPRIMARYKEY(name);ALTERTABLEShipsADDFOREIGNKEY(class)REFERENCESClasses(calss);ALTERTABLEBattlesADDPRIMARYKEY(name);ALTERTABLEOutcomesADDFOREIGNKEY(ship)REFERENCESShips(name);ALTERTABLEOutcomesADDFOREIGNKEY(battle)REFERENCESBattles(name);7.1.5a)ALTERTABLEShipsADDFOREIGNKEY(class)REFERENCESClasses(class)ONDELETESETNULLONUPDATESETNULL;Inadditiontotheabovedeclaration,classmustbedeclaredtheprimarykeyforClasses.b)ALTERTABLEOutcomeADDFOREIGNKEY(battle)REFERENCESBattles(name)ONDELETESETNULLONUPDATESETNULL;c)ALTERTABLEOutcomesADDFOREIGNKEY(ship)REFERENCESShips(name)ONDELETESETNULLONUPDATESETNULL;7.2.1a)yearINTCHECK(year=1915)b)lengthINTCHECK(length=60ANDlength=250)c)studioNameCHAR(30)CHECK(studioNameIN(,Disney?,Fox?,,MGM?,,Paramount?))7.2.2a)CREATETABLELaptop(speedDECIMAL(4,2)CHECK(speed=2.0));b)CREATETABLEPrinter(typeVARCHAR(10)CHECK(typeIN(,laser?,,ink-jet?,,bubble-jet?)));c)CREATETABLEProduct(typeVARCHAR(10)CHECK(typeIN(,pc?,,laptop?,,printer?)));d)CREATETABLEProduct(modelCHAR(4)CHECK(modelIN(SELECTmodelFROMPCUNIONALLSELECTmodelFROMlaptopUNIONALLSELECTmodelFROMprinter)));*notethisdoesn?tchecktheattributeconstraintviolationcausedbydeletionsfromPC,laptop,orprinter7.2.3a)CREATETABLEStarsIn(starNameCHAR(30)CHECK(starNameIN(SELECTnameFROMMovieStarWHEREYEAR(birthdate)movieYear)));b)CREATETABLEStudio(addressCHAR(255)CHECK(addressISUNIQUE));c)CREATETABLEMovieStar(nameCHAR(30)CHECK(nameNOTIN(SELECTnameFROMMovieExec)));d)CREATETABLEStudio(NameCHAR(30)CHECK(nameIN(SELECTstudioNameFROMMovies)));e)CREATETABLEMovies(CHECK(producerC#NOTIN(SELECTpresC#FROMStudio)ORstudioNameIN(SELECTnameFROMStudioWHEREpresC#=producerC#)));7.2.4a)CHECK(speed=2.0ORprice=600)b)CHECK(screen=15ORhd=40ORprice=1000)7.2.5a)CHECK(classNOTIN(SELECTclassFROMClassesWHEREbore16))b)CHECK(classNOTIN(SELECTclassFROMClassesWHEREnumGuns9ANDbore14))c)CHECK(shipIN(SELECTs.nameFROMShipss,Battlesb,OutcomesoWHEREs.name=o.shipANDb.name=o.battleANDs.launchedYEAR(b.date)))7.2.6TheconstraintinExample7.6doesnotallowNULLvalueforgenderwhiletheconstraintinExample7.8allowsNULL.7.3.1a)ALTERTABLEMovieADDCONSTRAINTmyKeyPRIMARYKEY(title,year);b)ALTERTABLEMovieADDCONSTRAINTproducerCheckFOREIGNKEY(producerC#)REFERENCESMovieExec(cert#);c)ALTERTABLEMovieADDCONSTRAINTlengthCheckCHECK(length=60ANDlength=250);d)ALTERTABLEMovieStarADDCONSTRAINTnoDupInExecCHECK(nameNOTIN(SELECTnameFROMMovieExec));ALTERTABLEMovieExecADDCONSTRAINTnoDupInStarCHECK(nameNOTIN(SELECTnameFROMMovieStar));e)ALTERTABLEStudioADDCONSTRAINTnoDupAddrCHECK(addressisUNIQUE);7.3.2a)ALTERTABLEClassesADDCONSTRAINTmyKeyPRIMARYKEY(class,country);b)ALTERTABLEOutcomesADDCONSTRAINTbattleCheckFOREIGNKEY(battle)REFERENCESBattles(name);c)ALTERTABLEOutcomesADDCONSTRAINTshipCheckFOREIGNKEY(ship)REFERENCESShips(name);d)ALTERTABLEShipsADDCONSTRAINTclassGunCheckCHECK(classNOTIN(SELECTclassFROMClassesWHEREnumGuns14));e)ALTERTABLEShipsADDCONSTRAINTshipDateCheckCHECK(shipIN(SELECTs.nameFROMShipss,Battlesb,OutcomesoWHEREs.name=o.shipANDb.name=o.battleANDs.launched=YEAR(b.date)))7.4.1a)CREATEASSERTIONCHECK(NOTEXISTS((SELECTmakerFROMProductNATURALJOINPC)INTERSECT(SELECTmakerFROMProductNATURALJOINLaptop)));b)CREATEASSERTIONCHECK(NOTEXISTS(SELECTmakerFROMProductNATURALJOINPCWHEREspeedALL(SELECTL2.speedFROMProductP