数据库系统基础教程第七章答案.doc
《数据库系统基础教程第七章答案.doc》由会员分享,可在线阅读,更多相关《数据库系统基础教程第七章答案.doc(17页珍藏版)》请在冰豆网上搜索。
7.1.1
a)
CREATETABLEMovies(
title CHAR(100),
year INT,
length INT,
genre CHAR(10),
studioName CHAR(30),
producerC# INT,
PRIMARYKEY(title,year),
FOREIGNKEY(producerC#)REFERENCESMovieExec(cert#)
);
or
CREATETABLEMovies(
title CHAR(100),
year INT,
length INT,
genre CHAR(10),
studioName CHAR(30),
producerC# INT REFERENCESMovieExec(cert#),
PRIMARYKEY(title,year)
);
b)
CREATETABLEMovies(
title CHAR(100),
year INT,
length INT,
genre CHAR(10),
studioName CHAR(30),
producerC# INT REFERENCESMovieExec(cert#)
ONDELETESETNULL
ONUPDATESETNULL,
PRIMARYKEY(title,year)
);
c)
CREATETABLEMovies(
title CHAR(100),
year INT,
length INT,
genre CHAR(10),
studioName CHAR(30),
producerC# INT REFERENCESMovieExec(cert#)
ONDELETECASCADE
ONUPDATECASCADE,
PRIMARYKEY(title,year)
);
d)
CREATETABLEStarsIn(
movieTitle CHAR(100)REFERENCESMovie(title),
movieYear INT,
starName CHAR(30),
PRIMARYKEY(movieTItle,movieYear,starName)
);
e)
CREATETABLEStarsIn(
movieTitle CHAR(100)REFERENCESMovie(title)
ONDELETECASCADE,
movieYear INT,
starName CHAR(30),
PRIMARYKEY(movieTItle,movieYear,starName)
);
7.1.2
Todeclaresuchaforeign-keyconstraintbetweentherelationsMovieandStarsIn,valuesofthereferencingattributesinMovieshouldappearinMovieStarasuniquevalues.However,basedonprimarykeydeclarationinrelationStarIn,theuniquenessofmoviesisguaranteedwithmovieTitle,movieYear,andstarNameattributes.EvenwithtitleandyearasreferencingattributesthereisnowayofreferencinguniquemoviefromStarsInwithoutstarNameinformation.Therefore,suchaconstraintcannotbeexpressedusingaforeign-keyconstraint.
7.1.3
ALTERTABLEProduct
ADDPRIMARYKEY(model);
ALTERTABLEPC
ADDFOREIGNKEY(model)REFERENCESProduct(model);
ALTERTABLELaptop
ADDFOREIGNKEY(model)REFERENCESProduct(model);
ALTERTABLEPrinter
ADDFOREIGNKEY(model)REFERENCESProduct(model);
7.1.4
ALTERTABLEClasses
ADDPRIMARYKEY(class);
ALTERTABLEShips
ADDPRIMARYKEY(name);
ALTERTABLEShips
ADDFOREIGNKEY(class)REFERENCESClasses(calss);
ALTERTABLEBattles
ADDPRIMARYKEY(name);
ALTERTABLEOutcomes
ADDFOREIGNKEY(ship)REFERENCESShips(name);
ALTERTABLEOutcomes
ADDFOREIGNKEY(battle)REFERENCESBattles(name);
7.1.5
a)
ALTERTABLEShips
ADDFOREIGNKEY(class)REFERENCESClasses(class)
ONDELETESETNULL
ONUPDATESETNULL;
Inadditiontotheabovedeclaration,classmustbedeclaredtheprimarykeyforClasses.
b)
ALTERTABLEOutcome
ADDFOREIGNKEY(battle)REFERENCESBattles(name)
ONDELETESETNULL
ONUPDATESETNULL;
c)
ALTERTABLEOutcomes
ADDFOREIGNKEY(ship)REFERENCESShips(name)
ONDELETESETNULL
ONUPDATESETNULL;
7.2.1
a)
year INT CHECK(year>=1915)
b)
length INT CHECK(length>=60ANDlength<=250)
c)
studioName CHAR(30)
CHECK(studioNameIN(‘Disney’,Fox’,‘MGM’,‘Paramount’))
7.2.2
a)
CREATETABLELaptop(
…
speed DECIMAL(4,2) CHECK(speed>=2.0)
…
);
b)
CREATETABLEPrinter(
…
type VARCHAR(10)
CHECK(typeIN(‘laser’,‘ink-jet’,‘bubble-jet’))
…
);
c)
CREATETABLEProduct(
…
type VARCHAR(10)
CHECK(typeIN(‘pc’,‘laptop’,‘printer’))
…
);
d)
CREATETABLEProduct(
…
model CHAR(4)
CHECK(modelIN(SELECTmodelFROMPC
UNIONALL
SELECTmodelFROMlaptop
UNIONALL
SELECTmodelFROMprinter))
…
);
*notethisdoesn’tchecktheattributeconstraintviolationcausedbydeletionsfromPC,laptop,orprinter
7.2.3
a)
CREATETABLEStarsIn(
…
starNameCHAR(30)
CHECK(starNameIN(SELECTnameFROMMovieStar
WHEREYEAR(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)OR
studioNameIN(SELECTnameFROMStudio
WHEREpresC#=producerC#))
…
);
7.2.4
a)
CHECK(speed>=2.0ORprice<=600)
b)
CHECK(screen>=15ORhd>=40ORprice<=1000)
7.2.5
a)
CHECK(classNOTIN(SELECTclassFROMClasses
WHEREbore>16))
b)
CHECK(classNOTIN(SELECTclassFROMClasses
WHEREnumGuns>9ANDbore>14))
c)
CHECK(shipIN(SELECTs.nameFROMShipss,Battlesb,Outcomeso
WHEREs.name=o.shipAND
b.name=o.batt