数据库系统概念全套配套课件PPT ch22.ppt 36页

'Chapter22:Object-BasedDatabases Chapter22:Object-BasedDatabasesComplexDataTypesandObjectOrientationStructuredDataTypesandInheritanceinSQLTableInheritanceArrayandMultisetTypesinSQLObjectIdentityandReferenceTypesinSQLImplementingO-RFeaturesPersistentProgrammingLanguagesComparisonofObject-OrientedandObject-RelationalDatabases Object-RelationalDataModelsExtendtherelationaldatamodelbyincludingobjectorientationandconstructstodealwithaddeddatatypes.Allowattributesoftuplestohavecomplextypes,includingnon-atomicvaluessuchasnestedrelations.Preserverelationalfoundations,inparticularthedeclarativeaccesstodata,whileextendingmodelingpower.Upwardcompatibilitywithexistingrelationallanguages. ComplexDataTypesMotivation:Permitnon-atomicdomains(atomicindivisible)Exampleofnon-atomicdomain:setofintegers,orsetoftuplesAllowsmoreintuitivemodelingforapplicationswithcomplexdataIntuitivedefinition:allowrelationswheneverweallowatomic(scalar)values—relationswithinrelationsRetainsmathematicalfoundationofrelationalmodelViolatesfirstnormalform. ExampleofaNestedRelationExample:libraryinformationsystemEachbookhastitle,alist(array)ofauthors,Publisher,withsubfieldsnameandbranch,andasetofkeywordsNon-1NFrelationbooks 4NFDecompositionofNestedRelationSupposeforsimplicitythattitleuniquelyidentifiesabookInrealworldISBNisauniqueidentifierDecomposebooksinto4NFusingtheschemas:(title,author,position)(title,keyword)(title,pub-name,pub-branch)4NFdesignrequiresuserstoincludejoinsintheirqueries. ComplexTypesandSQLExtensionsintroducedinSQL:1999tosupportcomplextypes:CollectionandlargeobjecttypesNestedrelationsareanexampleofcollectiontypesStructuredtypesNestedrecordstructureslikecompositeattributesInheritanceObjectorientationIncludingobjectidentifiersandreferencesNotfullyimplementedinanydatabasesystemcurrentlyButsomefeaturesarepresentineachofthemajorcommercialdatabasesystemsReadthemanualofyourdatabasesystemtoseewhatitsupports StructuredTypesandInheritanceinSQLStructuredtypes(a.k.a.user-definedtypes)canbedeclaredandusedinSQLcreatetypeNameas(firstnamevarchar(20),lastnamevarchar(20))finalcreatetypeAddressas(streetvarchar(20),cityvarchar(20),zipcodevarchar(20))notfinalNote:finalandnotfinalindicatewhethersubtypescanbecreatedStructuredtypescanbeusedtocreatetableswithcompositeattributescreatetableperson(nameName,addressAddress,dateOfBirthdate)Dotnotationusedtoreferencecomponents:name.firstname StructuredTypes(cont.)User-definedrowtypescreatetypePersonTypeas(nameName,addressAddress,dateOfBirthdate)notfinalCanthencreateatablewhoserowsareauser-definedtypecreatetablecustomerofCustomerTypeAlternativeusingunnamedrowtypes.createtableperson_r(namerow(firstnamevarchar(20),lastnamevarchar(20)),addressrow(streetvarchar(20),cityvarchar(20),zipcodevarchar(20)),dateOfBirthdate) MethodsCanaddamethoddeclarationwithastructuredtype.methodageOnDate(onDatedate)returnsintervalyearMethodbodyisgivenseparately.createinstancemethodageOnDate(onDatedate)returnsintervalyearforCustomerTypebeginreturnonDate-self.dateOfBirth;endWecannowfindtheageofeachcustomer:selectname.lastname,ageOnDate(current_date)fromcustomer ConstructorFunctionsConstructorfunctionsareusedtocreatevaluesofstructuredtypesE.g.createfunctionName(firstnamevarchar(20),lastnamevarchar(20))returnsNamebeginsetself.firstname=firstname;setself.lastname=lastname;endTocreateavalueoftypeName,weusenewName(‘John’,‘Smith’)NormallyusedininsertstatementsinsertintoPersonvalues(newName(‘John’,‘Smith),newAddress(’20MainSt’,‘NewYork’,‘11001’),date‘1960-8-22’); TypeInheritanceSupposethatwehavethefollowingtypedefinitionforpeople:createtypePerson(namevarchar(20),addressvarchar(20))UsinginheritancetodefinethestudentandteachertypescreatetypeStudentunderPerson(degreevarchar(20),departmentvarchar(20))createtypeTeacherunderPerson(salaryinteger,departmentvarchar(20))Subtypescanredefinemethodsbyusingoverridingmethodinplaceofmethodinthemethoddeclaration MultipleTypeInheritanceSQL:1999andSQL:2003donotsupportmultipleinheritanceIfourtypesystemsupportsmultipleinheritance,wecandefineatypeforteachingassistantasfollows:createtypeTeachingAssistantunderStudent,TeacherToavoidaconflictbetweenthetwooccurrencesofdepartmentwecanrenamethemcreatetypeTeachingAssistantunderStudentwith(departmentasstudent_dept),Teacherwith(departmentasteacher_dept)Eachvaluemusthaveamost-specifictype TableInheritanceTablescreatedfromsubtypescanfurtherbespecifiedassubtablesE.g.createtablepeopleofPerson;createtablestudentsofStudentunderpeople;createtableteachersofTeacherunderpeople;TuplesaddedtoasubtableareautomaticallyvisibletoqueriesonthesupertableE.g.queryonpeoplealsoseesstudentsandteachers.Similarlyupdates/deletesonpeoplealsoresultinupdates/deletesonsubtablesTooverridethisbehaviour,use“onlypeople”inqueryConceptually,multipleinheritanceispossiblewithtablese.g.teaching_assistantsunderstudentsandteachersButisnotsupportedinSQLcurrentlySowecannotcreateaperson(tupleinpeople)whoisbothastudentandateacher ConsistencyRequirementsforSubtablesConsistencyrequirementsonsubtablesandsupertables.Eachtupleofthesupertable(e.g.people)cancorrespondtoatmostonetupleineachofthesubtables(e.g.studentsandteachers)AdditionalconstraintinSQL:1999:Alltuplescorrespondingtoeachother(thatis,withthesamevaluesforinheritedattributes)mustbederivedfromonetuple(insertedintoonetable).Thatis,eachentitymusthaveamostspecifictypeWecannothaveatupleinpeoplecorrespondingtoatupleeachinstudentsandteachers ArrayandMultisetTypesinSQLExampleofarrayandmultisetdeclaration:createtypePublisheras(namevarchar(20),branchvarchar(20));createtypeBookas(titlevarchar(20),author_arrayvarchar(20)array[10],pub_datedate,publisherPublisher,keyword-setvarchar(20)multiset);createtablebooksofBook; CreationofCollectionValuesArrayconstructionarray[‘Silberschatz’,`Korth’,`Sudarshan’]Multisetsmultiset[‘computer’,‘database’,‘SQL’]Tocreateatupleofthetypedefinedbythebooksrelation:(‘Compilers’,array[`Smith’,`Jones’],newPublisher(`McGraw-Hill’,`NewYork’),multiset[`parsing’,`analysis’])Toinserttheprecedingtupleintotherelationbooksinsertintobooksvalues(‘Compilers’,array[`Smith’,`Jones’],newPublisher(`McGraw-Hill’,`NewYork’),multiset[`parsing’,`analysis’]); QueryingCollection-ValuedAttributesTofindallbooksthathavetheword“database”asakeyword,selecttitlefrombookswhere‘database’in(unnest(keyword-set))WecanaccessindividualelementsofanarraybyusingindicesE.g.:Ifweknowthataparticularbookhasthreeauthors,wecouldwrite:selectauthor_array[1],author_array[2],author_array[3]frombookswheretitle=`DatabaseSystemConcepts’Togetarelationcontainingpairsoftheform“title,author_name”foreachbookandeachauthorofthebookselectB.title,A.authorfrombooksasB,unnest(B.author_array)asA(author)ToretainorderinginformationweaddawithordinalityclauseselectB.title,A.author,A.positionfrombooksasB,unnest(B.author_array)withordinalityasA(author,position) UnnestingThetransformationofanestedrelationintoaformwithfewer(orno)relation-valuedattributesuscalledunnesting.E.g.selecttitle,Aasauthor,publisher.nameaspub_name,publisher.branchaspub_branch,K.keywordfrombooksasB,unnest(B.author_array)asA(author),unnest(B.keyword_set)asK(keyword)Resultrelationflat_books NestingNestingistheoppositeofunnesting,creatingacollection-valuedattributeNestingcanbedoneinamannersimilartoaggregation,butusingthefunctioncolect()inplaceofanaggregationoperation,tocreateamultisetTonesttheflat_booksrelationontheattributekeyword:selecttitle,author,Publisher(pub_name,pub_branch)aspublisher,collect(keyword)askeyword_setfromflat_booksgroupbytitle,author,publisherTonestonbothauthorsandkeywords:selecttitle,collect(author)asauthor_set,Publisher(pub_name,pub_branch)aspublisher,collect(keyword)askeyword_setfromflat_booksgroupbytitle,publisher Nesting(Cont.)Anotherapproachtocreatingnestedrelationsistousesubqueriesintheselectclause,startingfromthe4NFrelationbooks4selecttitle,array(selectauthorfromauthorsasAwhereA.title=B.titleorderbyA.position)asauthor_array,Publisher(pub-name,pub-branch)aspublisher,multiset(selectkeywordfromkeywordsasKwhereK.title=B.title)askeyword_setfrombooks4asB Object-IdentityandReferenceTypesDefineatypeDepartmentwithafieldnameandafieldheadwhichisareferencetothetypePerson,withtablepeopleasscope:createtypeDepartment(namevarchar(20),headref(Person)scopepeople)WecanthencreateatabledepartmentsasfollowscreatetabledepartmentsofDepartmentWecanomitthedeclarationscopepeoplefromthetypedeclarationandinsteadmakeanadditiontothecreatetablestatement:createtabledepartmentsofDepartment(headwithoptionsscopepeople)Referencedtablemusthaveanattributethatstorestheidentifier,calledtheself-referentialattributecreatetablepeopleofPersonrefisperson_idsystemgenerated; InitializingReference-TypedValuesTocreateatuplewithareferencevalue,wecanfirstcreatethetuplewithanullreferenceandthensetthereferenceseparately:insertintodepartmentsvalues(`CS’,null)updatedepartmentssethead=(selectp.person_idfrompeopleaspwherename=`John’)wherename=`CS’ UserGeneratedIdentifiersThetypeoftheobject-identifiermustbespecifiedaspartofthetypedefinitionofthereferencedtable,andThetabledefinitionmustspecifythatthereferenceisusergeneratedcreatetypePerson(namevarchar(20)addressvarchar(20))refusingvarchar(20)createtablepeopleofPersonrefisperson_idusergeneratedWhencreatingatuple,wemustprovideauniquevaluefortheidentifier:insertintopeople(person_id,name,address)values(‘01284567’,‘John’,`23CoyoteRun’)WecanthenusetheidentifiervaluewheninsertingatupleintodepartmentsAvoidsneedforaseparatequerytoretrievetheidentifier:insertintodepartmentsvalues(`CS’,`02184567’) UserGeneratedIdentifiers(Cont.)Canuseanexistingprimarykeyvalueastheidentifier:createtypePerson(namevarchar(20)primarykey,addressvarchar(20))reffrom(name)createtablepeopleofPersonrefisperson_idderivedWheninsertingatuplefordepartments,wecanthenuseinsertintodepartmentsvalues(`CS’,`John’) PathExpressionsFindthenamesandaddressesoftheheadsofalldepartments:selecthead–>name,head–>addressfromdepartmentsAnexpressionsuchas“head–>name”iscalledapathexpressionPathexpressionshelpavoidexplicitjoinsIfdepartmentheadwerenotareference,ajoinofdepartmentswithpeoplewouldberequiredtogetattheaddressMakesexpressingthequerymucheasierfortheuser ImplementingO-RFeaturesSimilartohowE-RfeaturesaremappedontorelationschemasSubtableimplementationEachtablestoresprimarykeyandthoseattributesdefinedinthattableor,Eachtablestoresbothlocallydefinedandinheritedattributes PersistentProgrammingLanguagesLanguagesextendedwithconstructstohandlepersistentdataProgrammercanmanipulatepersistentdatadirectlynoneedtofetchitintomemoryandstoreitbacktodisk(unlikeembeddedSQL)Persistentobjects:Persistencebyclass-explicitdeclarationofpersistencePersistencebycreation-specialsyntaxtocreatepersistentobjectsPersistencebymarking-makeobjectspersistentaftercreationPersistencebyreachability-objectispersistentifitisdeclaredexplicitlytobesoorisreachablefromapersistentobject ObjectIdentityandPointersDegreesofpermanenceofobjectidentityIntraprocedure:onlyduringexecutionofasingleprocedureIntraprogram:onlyduringexecutionofasingleprogramorqueryInterprogram:acrossprogramexecutions,butnotifdata-storageformatondiskchangesPersistent:interprogram,pluspersistentacrossdatareorganizationsPersistentversionsofC++andJavahavebeenimplementedC++ODMGC++ObjectStoreJavaJavaDatabaseObjects(JDO) PersistentC++SystemsExtensionsofC++languagetosupportpersistentstorageofobjectsSeveralproposals,ODMGstandardproposed,butnotmuchactionoflatepersistentpointers:e.g.d_Refcreationofpersistentobjects:e.g.new(db)T()Classextents:accesstoallpersistentobjectsofaparticularclassRelationships:RepresentedbypointersstoredinrelatedobjectsIssue:consistencyofpointersSolution:extensiontotypesystemtoautomaticallymaintainback-referencesIteratorinterfaceTransactionsUpdates:mark_modified()functiontotellsystemthatapersistentobjectthatwasfetchedintomemoryhasbeenupdatedQuerylanguage PersistentJavaSystemsStandardforaddingpersistencetoJava:JavaDatabaseObjects(JDO)PersistencebyreachabilityBytecodeenhancementClassesseparatelydeclaredaspersistentBytecodemodifierprogrammodifiesclassbytecodetosupportpersistenceE.g.FetchobjectondemandMarkmodifiedobjectstobewrittenbacktodatabaseDatabasemappingAllowsobjectstobestoredinarelationaldatabaseClassextentsSinglereferencetypenodifferencebetweenin-memorypointerandpersistentpointerImplementationtechniquebasedonhollowobjects(a.k.a.pointerswizzling) Object-RelationalMappingObject-RelationalMapping(ORM)systemsbuiltontopoftraditionalrelationaldatabasesImplementorprovidesamappingfromobjectstorelationsObjectsarepurelytransient,nopermanentobjectidentityObjectscanberetriedfromdatabaseSystemusesmappingtofetchrelevantdatafromrelationsandconstructobjectsUpdatedobjectsarestoredbackindatabasebygeneratingcorrespondingupdate/insert/deletestatementsTheHibernateORMsystemiswidelyuseddescribedinSection9.4.2ProvidesAPItostart/endtransactions,fetchobjects,etcProvidesquerylanguageoperatingdireclyonobjectmodelqueriestranslatedtoSQLLimitations:overheads,especiallyforbulkupdates ComparisonofO-OandO-RDatabasesRelationalsystemssimpledatatypes,powerfulquerylanguages,highprotection.Persistent-programming-language-basedOODBscomplexdatatypes,integrationwithprogramminglanguage,highperformance.Object-relationalsystemscomplexdatatypes,powerfulquerylanguages,highprotection.Object-relationalmappingsystemscomplexdatatypesintegratedwithprogramminglanguage,butbuiltasalayerontopofarelationaldatabasesystemNote:ManyrealsystemsblurtheseboundariesE.g.persistentprogramminglanguagebuiltasawrapperonarelationaldatabaseoffersfirsttwobenefits,butmayhavepoorperformance. EndofChapter22 Figure22.05 Figure22.07'