Part 2 Review Questions and Exercises

SOLUTIONSTOREVIEWQUESTIONSANDEXERCISESFORPART2–THERELATIONALMODELANDLANGUAGES(CHAPTERS4–9)DatabaseSystems:Instructor’sGuide-PartIII2SolutionstoReviewQuestionsandExercisesChapter4TheRelationalModel...................................................................................................................................................3Chapter5RelationalAlgebraandRelationalCalculus..................................................................................................................6Chapter6SQL:DataManipulation............................................................................................................................................19Chapter7SQL:DataDefinition..................................................................................................................................................26Chapter8AdvancedSQL...........................................................................................................................................................36Chapter9Query-By-Example....................................................................................................................................................38DatabaseSystems:Instructor’sGuide-PartIII3Chapter4TheRelationalModelReviewQuestions4.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DnEachelementinthen-tupleconsistsofanattributeandavalueforthatattribute.DiscussedfullyinSections4.2.2and4.2.3.4.3Describethedifferencesbetweenarelationandarelationschema.Whatisarelationaldatabaseschema?Arelationschemaisanamedrelationdefinedbyasetofattributeanddomainnamepairs.Arelationaldatabaseschemaisasetofrelationschemas,eachwithadistinctname.DiscussedinSection4.2.3.4.4Discussthepropertiesofarelation.Arelationhasthefollowingproperties:•hasanamethatisdistinctfromallotherrelationnamesintherelationalschema;DatabaseSystems:Instructor’sGuide-PartIII4•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.ExercisesThefollowingtablesformpartofadatabaseheldinarelationalDBMS:-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.DatabaseSystems:Instructor’sGuide-PartIII54.8Identifytheforeignkeysinthisschema.Explainhowtheentityandreferentialintegrityrulesapplytotheserelations.Foreachrelation,theprimarykeymustnotcontainanynulls.RoomisrelatedtoHotelthroughtheattributehotelNo.Therefore,thehotelNoinRoomshouldeitherbenullorcontainthenumberofan