Part 2 Review Questions and Exercises.docx
- 文档编号:12026979
- 上传时间:2023-04-16
- 格式:DOCX
- 页数:66
- 大小:256.09KB
Part 2 Review Questions and Exercises.docx
《Part 2 Review Questions and Exercises.docx》由会员分享,可在线阅读,更多相关《Part 2 Review Questions and Exercises.docx(66页珍藏版)》请在冰豆网上搜索。
Part2ReviewQuestionsandExercises
SOLUTIONSTOREVIEWQUESTIONS
ANDEXERCISES
FORPART2–THERELATIONALMODELANDLANGUAGES(CHAPTERS4–9)
SolutionstoReviewQuestionsandExercises
Chapter4TheRelationalModel
ReviewQuestions
4.1Discusseachofthefollowingconceptsinthecontextoftherelationaldatamodel:
(a)RelationAtablewithcolumnsandrows.
(b)AttributeAnamedcolumnofarelation.
(c)DomainThesetofallowablevaluesforoneormoreattributes.
(d)TupleArowofarelation.
(e)IntensionThestructureofarelationtogetherwithaspecificationofthedomainsandanyotherrestrictionsonpossiblevalues.
ExtensionAninstanceofthetuplesofarelation.
(f)DegreeThenumberofattributesinarelation.
CardinalityThenumberoftuplesinarelation.
EachtermdefinedinSection4.2.1.
4.2Describetherelationshipbetweenmathematicalrelationsandrelationsintherelationaldatamodel?
LetD1,D2,...,Dnbensets.TheirCartesianproductisdefinedas:
D1⨯D2⨯...⨯Dn={(d1,d2,...,dn)|d1∈D1,d2∈D2,...,dn∈Dn}
Anysetofn-tuplesfromthisCartesianproductisarelationonthensets.NowletA1,A2,...,AnbeattributeswithdomainsD1,D2,...,Dn.Thentheset{A1:
D1,A2:
D2,...,An:
Dn}isarelationschema.ArelationRdefinedbyarelationschemaSisasetofmappingsfromtheattributenamestotheircorrespondingdomains.Thus,relationRisasetofn-tuples:
(A1:
d1,A2:
d2,...,An:
dn)suchthatd1∈D1,d2∈D2,...,dn∈Dn
Eachelementinthen-tupleconsistsofanattributeandavalueforthatattribute.
DiscussedfullyinSections4.2.2and4.2.3.
4.3Describethedifferencesbetweenarelationandarelationschema.Whatisarelationaldatabaseschema?
Arelationschemaisanamedrelationdefinedbyasetofattributeanddomainnamepairs.Arelationaldatabaseschemaisasetofrelationschemas,eachwithadistinctname.DiscussedinSection4.2.3.
4.4Discussthepropertiesofarelation.
Arelationhasthefollowingproperties:
•hasanamethatisdistinctfromallotherrelationnamesintherelationalschema;
•eachcellcontainsexactlyoneatomic(single)value;
•eachattributehasadistinctname;
•thevaluesofanattributeareallfromthesamedomain;
•eachtupleisdistinct;therearenoduplicatetuples;
•theorderofattributeshasnosignificance;
•theorderoftupleshasnosignificance,theoretically.(However,inpractice,theordermayaffecttheefficiencyofaccessingtuples.)
DiscussedfullyinSection4.2.4.
4.5Discussthedifferencesbetweenthecandidatekeysandtheprimarykeyofarelation.Explainwhatismeantbyaforeignkey.Howdoforeignkeysofrelationsrelatetocandidatekeys?
Giveexamplestoillustrateyouranswer.
Theprimarykeyisthecandidatekeythatisselectedtoidentifytuplesuniquelywithinarelation.Aforeignkeyisanattributeorsetofattributeswithinonerelationthatmatchesthecandidatekeyofsome(possiblythesame)relation.DiscussedinSection4.2.5.
4.6Definethetwoprincipalintegrityrulesfortherelationalmodel.Discusswhyitisdesirabletoenforcetheserules.
TworulesareEntityIntegrity(Section4.3.2)andReferentialIntegrity(Section4.3.3).
4.7Whatisaview?
Discussthedifferencebetweenaviewandabaserelation.
Viewisthedynamicresultofoneormorerelationaloperationsoperatingonthebaserelationstoproduceanotherrelation.Baserelationexistsasasetofdatainthedatabase.Aviewdoesnotcontainanydata,ratheraviewisdefinedasaqueryononeormorebaserelationsandaqueryontheviewistranslatedintoaqueryontheassociatedbaserelations.SeeSection4.4.
Exercises
ThefollowingtablesformpartofadatabaseheldinarelationalDBMS:
-
Hotel(hotelNo,hotelName,city)
Room(roomNo,hotelNo,type,price)
Booking(hotelNo,guestNo,dateFrom,dateTo,roomNo)
Guest(guestNo,guestName,guestAddress)
whereHotelcontainshoteldetailsandhotelNoistheprimarykey;
Roomcontainsroomdetailsforeachhoteland(roomNo,hotelNo)formstheprimarykey;
Bookingcontainsdetailsofthebookingsand(hotelNo,guestNo,dateFrom)formstheprimarykey;
andGuestcontainsguestdetailsandguestNoistheprimarykey.
4.8Identifytheforeignkeysinthisschema.Explainhowtheentityandreferentialintegrityrulesapplytotheserelations.
Foreachrelation,theprimarykeymustnotcontainanynulls.
RoomisrelatedtoHotelthroughtheattributehotelNo.Therefore,thehotelNoinRoomshouldeitherbenullorcontainthenumberofanexistinghotelintheHotelrelation.Inthiscasestudy,itwouldprobablybeunacceptabletohaveahotelNoinRoomwithanullvalue.
BookingisrelatedtoHotelthroughtheattributehotelNo.Therefore,thehotelNoinBookingshouldeitherbenullorcontainthenumberofanexistinghotelintheHotelrelation.However,becausehotelNoisalsopartoftheprimarykey,anullvalueforthisattributewouldbeunacceptable.SimilarlyforguestNo.BookingisalsorelatedtoRoomthroughtheattributeroomNo.
4.9Producesomesampletablesfortheserelationsthatobservetherelationalintegrityrules.Suggestsomegeneralconstraintsthatwouldbeappropriateforthisschema.
Studentshouldprovidesomesampletables,observingentityandreferentialintegrity.Inparticular,ensuretheuniquenessforthecompositeprimarykeysoftheRoomandBookingtables.
Somegeneralconstraintsmaybe:
∙Therecanbenotwobookingsforthesameroominthesamehotelonthesameday.
∙FortheBookingrelation,dateFrommustbebeforedateTo.
∙Roompricemustbegreaterthan0andlessthan£200.
4.10AnalyzetheRDBMSsthatyouarecurrentlyusing.Determinethesupportthesystemprovidesforprimarykeys,alternatekeys,foreignkeys,relationalintegrity,andviews.
Thisisasmallstudentproject,theresultofwhichisdependentonthesystemanalyzed.
4.11ImplementtheaboveschemainoneoftheRDBMSsyoucurrentlyuse.Implement,wherepossible,theprimary,alternate,andforeignkeys,andappropriaterelationalintegrityconstraints.
Thisisasmallstudentproject,theresultofwhichisdependentontheRDBMSused.Ensurethatkeyshavebeenimplemented,andthatrelationshipshavebeenimplementediftheRDBMSsupportsthis.
Chapter5RelationalAlgebraandRelationalCalculus
ReviewQuestions
5.1Whatisthedifferencebetweenaproceduralandnon-procedurallanguage?
Howwouldyouclassifytherelationalalgebraandrelationalcalculus?
Procedurallanguage:
alanguagethatallowsusertotellthesystemwhatdataisneededandexactlyhowtoretrievethedata.
Non-procedurallanguage:
alanguagethatallowsusertostatewhatdataisneededratherthanhowitistoberetrieved.
Informally,wemaydescribetherelationalalgebraasa(high-level)procedurallanguage:
itcanbeusedtotelltheDBMShowtobuildanewrelationfromoneormorerelationsinthedatabase.Again,informally,wemaydescribetherelationalcalculusasanon-procedurallanguage:
itcanbeusedtoformulatethedefinitionofarelationintermsofoneormoredatabaserelations.
5.2Explainthefollowingterms:
∙relationallycomplete;
Alanguagethatcanbeusedtoproduceanyrelationthatcanbederivedusingtherelationalcalculusissaidtoberelationallycomplete.
∙closureofrelationaloperations.
Therelationalalgebraisatheoreticallanguagewithoperationsthatworkononeormorerelationstodefineanotherrelationwithoutchangingtheoriginalrelation(s).Thus,boththeoperandsandtheresultsarerelations,andsotheoutputfromoneoperationcanbecometheinputtoanotheroperation.Thisallowsexpressionstobenestedintherelationalalgebra,justaswecannestarithmeticoperations.Thispropertyiscalledclosure:
relationsareclosedunderthealgebra,justasnumbersareclosedunderarithmeticoperations.
5.3Definethefivebasicrelationalalgebraoperations.DefinetheJoin,Intersection,andDivisionoperationsintermsofthesefivebasicoperations.
Fivebasicoperationsare:
∙SelectionandProjection(Unary)
∙CartesianProduct,Union,andSetDifference(Binary).
ThereisalsotheJoin,Intersection,andDivisionoperations:
∙Canrewrite-JoinintermsofthebasicselectionandCartesianproductoperations:
R3FS=σF(R⨯S)
∙Canexpresstheintersectionoperationintermsofthesetdifferenceoperation:
RS=R-(R-S)
∙Canexpressthedivisionoperationintermsofthebasicoperations:
T1=C(R)
T2=C((SxT1)-R)
T=T1-T2
5.4DiscussthedifferencesbetweenthefiveJoinoperations:
Thetajoin,Equijoin,Naturaljoin,Outerjoin,andSemijoin.Giveexamplestoillustrateyouranswer.
Thetajoin
R
FS
ProducesarelationthatcontainstuplessatisfyingthepredicateFfromtheCartesianproductofRandS.
Equijoin
R
FS
ProducesarelationthatcontainstuplessatisfyingthepredicateF(whichonlycontainsequalitycomparisons)fromtheCartesianproductofRandS.
Naturaljoin
R
S
AnEquijoinofthetworelationsRandSoverallcommonattributesx.Oneoccurrenceofeachcommonattributeiseliminated.
(Left)Outerjoin
R
S
AjoininwhichtuplesfromRthatdonothavematchingvaluesinthecommonattributesofSarealsoincludedintheresultrelation.
Semijoin
R
FS
ProducesarelationthatcontainsthetuplesofRthatparticipateinthejoinofRwithS.
5.5Compareandcontrastthetuplerelationalcalculuswithdomainrelationalcalculus.Inparticular,discussthedistinctionbetweentupleanddomainvariables.
Inthetuplerelationalcalculus,weusevariable
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Part Review Questions and Exercises