數(shù)據(jù)庫(kù)系統(tǒng)教學(xué)課件：第17講-基于對(duì)象的數(shù)據(jù)庫(kù)

第16講

基于對(duì)象的數(shù)據(jù)庫(kù)

重慶大學(xué)計(jì)算機(jī)學(xué)院課程名稱(chēng):數(shù)據(jù)庫(kù)系統(tǒng)

--------------------對(duì)象關(guān)系模型ORDBMExtendtherelationaldatamodelbyincluding

objectorientation面向?qū)ο?/p>

andconstructstodealwithaddeddatatypes.Allowattributesoftuplestohavecomplextypes,includingnon-atomicvaluessuchasnestedrelations.Preserverelationalfoundations,inparticularthedeclarativeaccesstodata,whileextendingmodelingpower.Upwardcompatibility

向上兼容

withexistingrelationallanguages.對(duì)象關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)ORDBMS基于對(duì)象關(guān)系數(shù)據(jù)模型的數(shù)據(jù)庫(kù)管理系統(tǒng)1.1對(duì)象關(guān)系數(shù)據(jù)模型Object-RelationalDataModels一對(duì)象關(guān)系數(shù)據(jù)庫(kù)基本概念關(guān)系模型設(shè)計(jì)面臨障礙：關(guān)系模型僅支持非常有限的數(shù)據(jù)類(lèi)型，難以描述許多客觀存在的復(fù)雜結(jié)構(gòu)(記錄結(jié)構(gòu)，多值屬性，繼承)這些復(fù)雜結(jié)構(gòu)E-R模型本身是能很好的支持的，但在形成相應(yīng)關(guān)系模式結(jié)構(gòu)時(shí)被強(qiáng)制轉(zhuǎn)換為簡(jiǎn)單的SQL數(shù)據(jù)類(lèi)型將傳統(tǒng)關(guān)系模型擴(kuò)展到對(duì)象關(guān)系模型是為了想要使用面向?qū)ο筇卣鞯年P(guān)系數(shù)據(jù)庫(kù)用戶提供一個(gè)移植途徑，以更好地滿足應(yīng)用的實(shí)際需要SQL標(biāo)準(zhǔn)SQL1999版本(以及SQL2003)，特別增加了對(duì)象關(guān)系擴(kuò)展描述部分注意，大多數(shù)數(shù)據(jù)庫(kù)產(chǎn)品都只支持SQL標(biāo)準(zhǔn)的一個(gè)子集，需要實(shí)際查閱相關(guān)的用戶手冊(cè)對(duì)象關(guān)系數(shù)據(jù)模型的用途動(dòng)機(jī)Motivation:Permitnon-atomicdomains(indivisible不可分的)Exampleofnon-atomicdomain:setofintegers,orsetoftuplesAllowsmoreintuitivemodelingforapplicationswithcomplexdata直觀說(shuō)明Intuitivedefinition:allowrelationswheneverweallowatomic(scalar)values, 比如：relationswithinrelationsRetains保留mathematicalfoundationofrelationalmodelViolates

違反firstnormalform.例子：關(guān)系看著屬性值傳統(tǒng)處理方式：表示數(shù)據(jù)對(duì)象傳統(tǒng)方式存在的不足：不足分析復(fù)雜數(shù)據(jù)類(lèi)型1.2復(fù)雜數(shù)據(jù)類(lèi)型Example:libraryinformationsystem（圖書(shū)館信息系統(tǒng)）Eachbookhastitle,asetofauthors,Publisher,andasetofkeywordsNon-1NFrelation

books：ExampleofaNestedRelation結(jié)構(gòu):內(nèi)容:多值屬性記錄結(jié)構(gòu)屬性多值屬性Removeawkwardness笨拙offlat-books

扁平關(guān)系byassumingthatthefollowingmultivalueddependencieshold:titleauthortitle

keywordtitle

pub-name,pub-branchDecompose

flat-doc扁平文檔內(nèi)容into4NFusingtheschemas: （由4NF分解算法）authors(title,author)keywords(title,keyword)book4(title,pub-name,pub-branch)4NFDecompositionofNestedRelation1.2復(fù)雜數(shù)據(jù)類(lèi)型4NFDecompositionofflat–books傳統(tǒng)方式表示的不足4NFdesignrequiresuserstoincludejoinsintheirqueries.1NFrelationalviewflat-books

definedbyjoinof4NFrelations:eliminatestheneedforuserstoperformjoins,butlosestheone-to-onecorrespondencebetweentuplesanddocuments.Andhasalargeamountofredundancy新方式表示的優(yōu)點(diǎn)Nestedrelationsrepresentationismuchmorenaturalhere.Problemswith4NFSchemaExtensionstoSQLtosupportcomplextypesinclude:CollectionandlargeobjecttypesNestedrelationsareanexampleofcollectiontypesStructuredtypesNestedrecordstructureslikecompositeattributesInheritanceObjectorientationIncludingobjectidentifiersandreferencesOurdescriptionismainlybasedontheSQL:1999standardNotfullyimplementedinanydatabasesystemcurrentlyButsomefeaturesarepresentineachofthemajorcommercialdatabasesystemsReadthemanualofyourdatabasesystemtoseewhatitsupportsComplexTypesinSQL:1999二SQL支持的復(fù)雜數(shù)據(jù)類(lèi)型創(chuàng)建：StructuredtypescanbedeclaredandusedinSQL createtypeNameas

(firstname

varchar(20),

lastnamevarchar(20))

final

createtypeAddressas

(streetvarchar(20),

city varchar(20),

zipcode varchar(20))

notfinal

Note:

final

and

notfinal

indicatewhethersubtypescanbecreated (如:final---不允許在該類(lèi)型下創(chuàng)建子類(lèi)，見(jiàn)后9.3.2節(jié),p.242）使用：Structuredtypescanbeusedtocreatetableswithcompositeattributescreatetablecustomer(

name Name, address Address, dateOfBirth

date)說(shuō)明：Dotnotationusedtoreferencecomponentsofstructuredatrubites:name.firstnameStructuredTypes-可作屬性使用2.1SQL-結(jié)構(gòu)類(lèi)型創(chuàng)建：User-definedrowtypes(可以利用已定義結(jié)構(gòu)定義新的結(jié)構(gòu))createtype

CustomerType

as(

nameName, addressAddress, dateOfBirth

date)

notfinal使用：Canthencreateatablewhoserowsareauser-definedtypecreatetable

customer

of

CustomerTypeStructuredTypes-可作表使用2.1SQL-結(jié)構(gòu)類(lèi)型結(jié)構(gòu)定義時(shí)可申明方法：Canaddamethoddeclarationwithastructuredtype.

method

ageOnDate(onDate

date)

returnsintervalyear方法體需要單獨(dú)編寫(xiě)：Methodbodyisgivenseparately.create

instancemethod

ageOnDate(onDate

date)

returnsintervalyear for

CustomerTypebegin return

onDate-self.dateOfBirth;當(dāng)前日期減去出生日期end注：SELFreferstothistuple

instance指出該方法在CustomerType的實(shí)例上執(zhí)行方法的使用方式：Wecannowfindtheageofeachcustomer:select

name.lastname,ageOnDate(current_date)from

customer注：Dot是系統(tǒng)提供的缺省方法！補(bǔ)充：另一例子如何提取復(fù)雜結(jié)構(gòu)里的數(shù)據(jù)2.1SQL-結(jié)構(gòu)類(lèi)型

.P.451(example9.21:EndtoMethod….)Separately,weneedtodefinethemethodhouseNumber()astheform: CREATEMETHODhouseNumber()RETURNSCHAR(10) FORAddressType BEGIN

…… END;AnotherExampleofmethods

參考：《數(shù)據(jù)庫(kù)系統(tǒng)基礎(chǔ)教程》(中譯本)，斯坦福大學(xué)，機(jī)械工業(yè)出版社出版，2009.08,1版declarationdefinition10.1510.172.1SQL-結(jié)構(gòu)類(lèi)型子類(lèi)繼承：Supposethatwehavethefollowingtypedefinitionforpeople:

createtype

Person

(namevarchar(20),

addressvarchar(20)) Using

inheritancetodefinethestudentandteachertypes

createtype

Student

underPerson

(degreevarchar(20),

departmentvarchar(20))

createtypeTeacher

underPerson

(salaryinteger,

departmentvarchar(20))多態(tài)性：SubtypescanredefinemethodsbyusingoverridingmethodinplaceofmethodinthemethoddeclarationInheritance2.2SQL-繼承類(lèi)型不支持：SQL:1999andSQL:2003donotsupportmultipleinheritance假想的類(lèi)型繼承：Ifourtypesystemsupportsmultipleinheritance,wecandefineatypeforteachingassistantasfollows:

createtypeTeachingAssistant

underStudent,TeacherToavoidaconflictbetweenthetwooccurrencesofdepartmentwecanrenamethemcreatetypeTeachingAssistant

under

Studentwith(departmentasstudent_dept),分情況選擇繼承

Teacherwith(departmentasteacher_dept

)*MultipleInheritance

-同時(shí)為多個(gè)類(lèi)型的子類(lèi)2.2SQL-繼承類(lèi)型Student類(lèi)型的屬性名說(shuō)明：數(shù)組-在SQL1999增加，多重集合-在SQL2003增加定義方式：Exampleofarrayandmultisetdeclaration: createtypePublisheras結(jié)構(gòu)類(lèi)型

(name

varchar(20),

branchvarchar(20))

createtypeBookas

(titlevarchar(20),

author-arrayvarchar(20)array[10],

數(shù)組-可存10個(gè)姓名,每個(gè)20字符

pub-datedate,

publisherPublisher,

keyword-setvarchar(20)multiset

)

可存多個(gè)關(guān)鍵字,每個(gè)20子符

createtablebooksof

Book該關(guān)系模式允許存儲(chǔ)的實(shí)例就是本課開(kāi)始時(shí)希望的復(fù)雜數(shù)據(jù)對(duì)象ArrayTypes&MultisetTypes2.3SQL-數(shù)組和多重集合類(lèi)型數(shù)組構(gòu)造器Arrayconstruction：array[‘Silberschatz’,`Korth’,`Sudarshan’]多重集合構(gòu)造器Multisetsconstruction： multisetset[‘computer’,‘database’,‘SQL’]產(chǎn)生復(fù)雜結(jié)構(gòu)元組值Tocreateatupleofthetypedefinedbythebooksrelation: (‘Compilers’,array[`Smith’,`Jones’],

new

Publisher(`McGraw-Hill’,`NewYork’), multiset[`parsing’,`analysis’])

注:new是指調(diào)用Publisher構(gòu)造函數(shù)(和調(diào)用參數(shù))為Publisher屬性創(chuàng)建一個(gè)值，Publisher構(gòu)造函數(shù)可用createfunction定義，如p.242的name構(gòu)造函數(shù)定義。插入復(fù)雜結(jié)構(gòu)元組到表中Toinserttheprecedingtupleintotherelationbooks：insertinto

books

values

(‘Compilers’,array[`Smith’,`Jones’],

new

Publisher(`McGraw-Hill’,`NewYork’),

.multiset[`parsing’,`analysis’])CreationofCollectionValues(inSQL1999)2.3SQL-數(shù)組和多重集合類(lèi)型Tofindallbooksthathavetheword“database”asakeyword, selecttitle

frombooks

where‘database’in(unnest(keyword-set))WecanaccessindividualelementsofanarraybyusingindicesE.g.:Ifweknowthataparticularbookhasthreeauthors,wecouldwrite: selectauthor-array[1],author-array[2],author-array[3]

frombooks

wheretitle=`DatabaseSystemConcepts’Togetarelationcontainingpairsoftheform“title,author-name”foreachbookandeachauthorofthebookselectB.title,A.author

frombooksasB,unnest(B.author-array)asA(author)Toreta