数据库系统概念PPT第九章_对应原版教材第五版

Chapter9 Object BasedDatabases ComplexDataTypesandObjectOrientationStructuredDataTypesandInheritanceinSQLTableInheritanceArrayandMultisetTypesinSQLObjectIdentityandReferenceTypesinSQLImplementingO RFeaturesPersistentProgrammingLanguagesComparisonofObject OrientedandObject RelationalDatabases Overview Traditionaldatabaseapplications Largeamountofrelativelysimpletypeofdata Shortandsimpletransaction task Somenewapplicationsneeddatamanagementsupport Withcomplexdatatype computeraideddesign CAD andgeographicalinformationsystems GIS Object RelationalDatabasesystem Object OrientedDatabaseSystem Withlongandcomplextasks verycomplexdataanalysis DatawarehouseandDataMining Object RelationalVsObjectOriented Object RelationalBasedontraditionalRelationalDatamodelExtendthedatamodeltosupportcomplexdatatype includingobjectorientationandconstructstodealwithaddeddatatypes Allowattributesoftuplestohavecomplextypes includingnon atomicvaluessuchasnestedrelations Preserverelationalfoundations inparticularthedeclarativeaccesstodata whileextendingmodelingpower DrivenbycommercialDBMSvendors Object OrientedBasedonobject orientedprogramminglanguages suchasC Java Extendthedatapersistencyfeaturetotheprogramminglanguagetosupportdatastorageandmanipulation ComplexTypesandSQL 1999 ExtensionstoSQLtosupportcomplextypesinclude CollectionandlargeobjecttypesNestedrelationsareanexampleofcollectiontypesStructuredtypesNestedrecordstructureslikecompositeattributesInheritanceObjectorientationIncludingobjectidentifiersandreferencesOurdescriptionismainlybasedontheSQL 1999standardNotfullyimplementedinanydatabasesystemcurrentlyButsomefeaturesarepresentineachofthemajorcommercialdatabasesystemsReadthemanualofyourdatabasesystemtoseewhatitsupports StructuredTypesandInheritanceinSQL StructuredtypescanbedeclaredandusedinSQLcreatetypeNameas firstnamevarchar 20 lastnamevarchar 20 finalcreatetypeAddressas streetvarchar 20 cityvarchar 20 zipcodevarchar 20 notfinalNote finalandnotfinalindicatewhethersubtypescanbecreatedStructuredtypescanbeusedtocreatetableswithcompositeattributescreatetablecustomer nameName addressAddress dateOfBirthdate Dotnotationusedtoreferencecomponents name firstname StructuredTypes cont User definedrowtypescreatetypeCustomerTypeas nameName addressAddress dateOfBirthdate notfinalCanthencreateatablewhoserowsareauser definedtypecreatetablecustomerofCustomerType Methods Canaddamethoddeclarationwithastructuredtype methodageOnDate onDatedate returnsintervalyearMethodbodyisgivenseparately createinstancemethodageOnDate onDatedate returnsintervalyearforCustomerTypebeginreturnonDate self dateOfBirth endWecannowfindtheageofeachcustomer selectname lastname ageOnDate current date fromcustomer Inheritance Supposethatwehavethefollowingtypedefinitionforpeople createtypePerson namevarchar 20 addressvarchar 20 UsinginheritancetodefinethestudentandteachertypescreatetypeStudentunderPerson degreevarchar 20 departmentvarchar 20 createtypeTeacherunderPerson salaryinteger departmentvarchar 20 Subtypescanredefinemethodsbyusingoverridingmethodinplaceofmethodinthemethoddeclaration MultipleInheritance SQL 1999andSQL 2003donotsupportmultipleinheritanceIfourtypesystemsupportsmultipleinheritance wecandefineatypeforteachingassistantasfollows createtypeTeachingAssistantunderStudent TeacherToavoidaconflictbetweenthetwooccurrencesofdepartmentwecanrenamethemcreatetypeTeachingAssistantunderStudentwith departmentasstudent dept Teacherwith departmentasteacher dept ConsistencyRequirementsforSubtables Consistencyrequirementsonsubtablesandsupertables Eachtupleofthesupertable e g people cancorrespondtoatmostonetupleineachoftheimmediatesubtables e g studentsandteachers Thatis nooverlapamongsubtables AdditionalconstraintinSQL 1999 Alltuplescorrespondingtoeachother thatis withthesamevaluesforinheritedattributes mustbederivedfromonetuple insertedintoonetable Thatis eachentitymusthaveamostspecifictypeWecannothaveatupleinpeoplecorrespondingtoatupleeachinstudentsandteachers ArrayandMultisetTypesinSQL Exampleofarrayandmultisetdeclaration createtypePublisheras namevarchar 20 branchvarchar 20 createtypeBookas titlevarchar 20 author arrayvarchar 20 array 10 pub datedate publisherPublisher keyword setvarchar 20 multiset createtablebooksofBookSimilartothenestedrelationbooks butwitharrayofauthorsinsteadofset CreationofCollectionValues Arrayconstructionarray Silberschatz Korth Sudarshan Multisetsmultisetset computer database SQL Tocreateatupleofthetypedefinedbythebooksrelation Compilers array Smith Jones Publisher McGraw Hill NewYork multiset parsing analysis Toinserttheprecedingtupleintotherelationbooksinsertintobooksvalues Compilers array Smith Jones Publisher McGraw Hill NewYork multiset parsing analysis QueryingCollection ValuedAttributes Tofindallbooksthathavetheword 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 Unnesting Thetransformationofanestedrelationintoaformwithfewer orno relation valuedattributesuscalledunnesting E g selecttitle A author publisher nameaspub name publisher branchaspub branch K keywordfrombooksasB unnest B author array asA author unnest B keyword set asK keyword Nesting Nestingistheoppositeofunnesting creatingacollection valuedattributeNOTE SQL 1999doesnotsupportnestingNestingcanbedoneinamannersimilartoaggregation butusingthefunctioncollect 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 1NFVersionofNestedRelation 1NFversionofbooks flat books Nesting Cont Anotherapproachtocreatingnestedrelationsistousesubqueriesintheselectclause selecttitle array selectauthorfromauthorsasAwhereA title B titleorderbyA position asauthor array Publisher pub name pub branch aspublisher multiset selectkeywordfromkeywordsasKwhereK title B title askeyword setfrombooks4asB Object IdentityandReferenceTypes Objectorientedlanguageprovidetheabilitytorefertoobjects InSQL wecandefinethisreferencetypeusingobject identity E g DefineatypeDepartmentwithafieldnameandafieldheadwhichisareferencetothetypePerson withtablepeopleasscope createtypeDepartment namevarchar 20 headref Person scopepeople WecanthencreateatabledepartmentsasfollowscreatetabledepartmentsofDepartmentWecanomitthedeclarationscopepeoplefromthetypedeclarationandinsteadmakeanadditiontothecreatetablestatement createtabledepartmentsofDepartment headwithoptionsscopepeople Object Identifier Thetypeoftheobject identifiermustbespecifiedaspartofthetypedefinitionofthereferencedtableThereareseveralwaysofgeneratingobject identifierSystemgenerated Userdefined Useprimarykeyasobject identifier SystemgeneratedOID WecandefinetheObjectIdentifieras CreatetablepeopleofPersonRefisperson idsystemgeneratedTocreateatuplewithareferencevalue wecanfirstcreatethetuplewithanullreferenceandthensetthereferenceseparately insertintodepartmentsvalues CS null updatedepartmentssethead selectp person idfrompeopleaspwherename John wherename CS UserGeneratedIdentifiers ThetabledefinitioncanspecifythatthereferenceisusergeneratedcreatetypePerson namevarchar 20 addressvarchar 20 refusingvarchar 20 createtablepeopleofPersonrefisperson idusergeneratedWhencreatingatuple wemustprovideauniquevaluefortheidentifier insertintopeople person id name address values 01284567 John 23CoyoteRun Wecanthenusetheidentifiervaluewheninsertingatupleintodepartmentsinsertintodepartmentsvalues CS 02184567 UsingPrimaryKeyasOID Canuseanexistingprimarykeyvalueastheidentifier createtypePerson namevarchar 20 primarykey addressvarchar 20 reffrom name createtablepeopleofPersonrefisperson idderivedWheninsertingatuplefordepartments wecanthenuseinsertintodepartmentsvalues CS John PathExpressions Findthenamesandaddressesoftheheadsofalldepartments selecthead name head addressfromdepartmentsAnexpressionsuchas head name iscalledapathexpressionPathexpressionshelpavoidexplicitjoinsIfdepartmentheadwerenotareference ajoinofdepartmentswithpeoplewouldberequiredtogetattheaddressMakesexpressingthequerymucheasierfortheuser ImplementingO RFeatures SimilartohowE RfeaturesaremappedontorelationschemasSubtableimplementationEachtablestoresprimarykeyandthoseattributesdefinedinthattableor Eachtablestoresbothlocallydefinedandinheritedattributes PersistentProgrammingLanguages LanguagesextendedwithconstructstohandlepersistentdataProgrammercanmanipulatepersistentdatadirectlynoneedtofetchitintomemoryandstoreitbacktodisk unlikeembeddedSQL Persistentobjects byclass explicitdeclarationofpersistencebycreation specialsyntaxtocreatepersistentobjectsbymarking makeobjectspersistentaftercreationbyreachability objectispersistentifitisdeclaredexplicitlytobesoorisreachablefromapersistentobject Systems Earlytomiddle1990s ObjectOrientedDatabasesystemwasveryhot manyPersistentversionsofC systemwerebuilt However themarketforsuchdataba