第16講

基于對象的數據庫

重慶大學計算機學院課程名稱:數據庫系統

--------------------對象關系模型ORDBMExtendtherelationaldatamodelbyincluding

objectorientation面向對象

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

向上兼容

withexistingrelationallanguages.對象關系數據庫管理系統ORDBMS基于對象關系數據模型的數據庫管理系統1.1對象關系數據模型Object-RelationalDataModels一對象關系數據庫基本概念關系模型設計面臨障礙：關系模型僅支持非常有限的數據類型，難以描述許多客觀存在的復雜結構(記錄結構，多值屬性，繼承)這些復雜結構E-R模型本身是能很好的支持的，但在形成相應關系模式結構時被強制轉換為簡單的SQL數據類型將傳統關系模型擴展到對象關系模型是為了想要使用面向對象特征的關系數據庫用戶提供一個移植途徑，以更好地滿足應用的實際需要SQL標準SQL1999版本(以及SQL2003)，特別增加了對象關系擴展描述部分注意，大多數數據庫產品都只支持SQL標準的一個子集，需要實際查閱相關的用戶手冊對象關系數據模型的用途動機Motivation:Permitnon-atomicdomains(indivisible不可分的)Exampleofnon-atomicdomain:setofintegers,orsetoftuplesAllowsmoreintuitivemodelingforapplicationswithcomplexdata直觀說明Intuitivedefinition:allowrelationswheneverweallowatomic(scalar)values, 比如：relationswithinrelationsRetains保留mathematicalfoundationofrelationalmodelViolates

違反firstnormalform.例子：關系看著屬性值傳統處理方式：表示數據對象傳統方式存在的不足：不足分析復雜數據類型1.2復雜數據類型Example:libraryinformationsystem（圖書館信息系統）Eachbookhastitle,asetofauthors,Publisher,andasetofkeywordsNon-1NFrelation

books：ExampleofaNestedRelation結構:內容:多值屬性記錄結構屬性多值屬性Removeawkwardness笨拙offlat-books

扁平關系byassumingthatthefollowingmultivalueddependencieshold:titleauthortitle

keywordtitle

pub-name,pub-branchDecompose

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

definedbyjoinof4NFrelations:eliminatestheneedforuserstoperformjoins,butlosestheone-to-onecorrespondencebetweentuplesanddocuments.Andhasalargeamountofredundancy新方式表示的優點Nestedrelationsrepresentationismuchmorenaturalhere.Problemswith4NFSchemaExtensionstoSQLtosupportcomplextypesinclude:CollectionandlargeobjecttypesNestedrelationsareanexampleofcollectiontypesStructuredtypesNestedrecordstructureslikecompositeattributesInheritanceObjectorientationIncludingobjectidentifiersandreferencesOurdescriptionismainlybasedontheSQL:1999standardNotfullyimplementedinanydatabasesystemcurrentlyButsomefeaturesarepresentineachofthemajorcommercialdatabasesystemsReadthemanualofyourdatabasesystemtoseewhatitsupportsComplexTypesinSQL:1999二SQL支持的復雜數據類型創建：StructuredtypescanbedeclaredandusedinSQL createtypeNameas

(firstname

varchar(20),

lastnamevarchar(20))

final

createtypeAddressas

(streetvarchar(20),

city varchar(20),

zipcode varchar(20))

notfinal

Note:

final

and

notfinal

indicatewhethersubtypescanbecreated (如:final---不允許在該類型下創建子類，見后9.3.2節,p.242）使用：Structuredtypescanbeusedtocreatetableswithcompositeattributescreatetablecustomer(

name Name, address Address, dateOfBirth

date)說明：Dotnotationusedtoreferencecomponentsofstructuredatrubites:name.firstnameStructuredTypes-可作屬性使用2.1SQL-結構類型創建：User-definedrowtypes(可以利用已定義結構定義新的結構)createtype

CustomerType

as(

nameName, addressAddress, dateOfBirth

date)

notfinal使用：Canthencreateatablewhoserowsareauser-definedtypecreatetable

customer

of

CustomerTypeStructuredTypes-可作表使用2.1SQL-結構類型結構定義時可申明方法：Canaddamethoddeclarationwithastructuredtype.

method

ageOnDate(onDate

date)

returnsintervalyear方法體需要單獨編寫：Methodbodyisgivenseparately.create

instancemethod

ageOnDate(onDate

date)

returnsintervalyear for

CustomerTypebegin return

onDate-self.dateOfBirth;當前日期減去出生日期end注：SELFreferstothistuple

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

name.lastname,ageOnDate(current_date)from

customer注：Dot是系統提供的缺省方法！補充：另一例子如何提取復雜結構里的數據2.1SQL-結構類型

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

…… END;AnotherExampleofmethods

參考：《數據庫系統基礎教程》(中譯本)，斯坦福大學，機械工業出版社出版，2009.08,1版declarationdefinition10.1510.172.1SQL-結構類型子類繼承：Supposethatwehavethefollowingtypedefinitionforpeople:

createtype

Person

(namevarchar(20),

addressvarchar(20)) Using

inheritancetodefinethestudentandteachertypes

createtype

Student

underPerson

(degreevarchar(20),

departmentvarchar(20))

createtypeTeacher

underPerson

(salaryinteger,

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

createtypeTeachingAssistant

underStudent,TeacherToavoidaconflictbetweenthetwooccurrencesofdepartmentwecanrenamethemcreatetypeTeachingAssistant

under

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

Teacherwith(departmentasteacher_dept

)*MultipleInheritance

-同時為多個類型的子類2.2SQL-繼承類型Student類型的屬性名說明：數組-在SQL1999增加，多重集合-在SQL2003增加定義方式：Exampleofarrayandmultisetdeclaration: createtypePublisheras結構類型

(name

varchar(20),

branchvarchar(20))

createtypeBookas

(titlevarchar(20),

author-arrayvarchar(20)array[10],

數組-可存10個姓名,每個20字符

pub-datedate,

publisherPublisher,

keyword-setvarchar(20)multiset

)

可存多個關鍵字,每個20子符

createtablebooksof

Book該關系模式允許存儲的實例就是本課開始時希望的復雜數據對象ArrayTypes&MultisetTypes2.3SQL-數組和多重集合類型數組構造器Arrayconstruction：array[‘Silberschatz’,`Korth’,`Sudarshan’]多重集合構造器Multisetsconstruction： multisetset[‘computer’,‘database’,‘SQL’]產生復雜結構元組值Tocreateatupleofthetypedefinedbythebooksrelation: (‘Compilers’,array[`Smith’,`Jones’],

new

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

注:new是指調用Publisher構造函數(和調用參數)為Publisher屬性創建一個值，Publisher構造函數可用createfunction定義，如p.242的name構造函數定義。插入復雜結構元組到表中Toinserttheprecedingtupleintotherelationbooks：insertinto

books

values

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

new

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

.multiset[`parsing’,`analysis’])CreationofCollectionValues(inSQL1999)2.3SQL-數組和多重集合類型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