Chapter 9: Object-Based Databases

صفحه 1:
Chapter 9: Obput-Bwsed Ouanbwee 0 م6 له 0 سمو مه مطم وا ‎Gop‏ 

صفحه 2:
+ Ohapger ©: Object Braed ‏مك17‎ Oncol Osta Dypes ord ‏م0 وال‎ Ginctred Oot Pures ‏جص جد" جب‎ 1 GOL 1 cherie rr ‏مس لت‎ Types mr GGL ‏را سا‎ ocd RePereuce Dupes ta GGL Aeoplecrecticn O-R Perches ] Procranerien Lanes ‏همم(‎ of Objet Ortedted ced Object(Rekaiocd Datbares Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 وه‎ 

صفحه 3:
Obpvt-Rekaoad Oda Dudes 0 Cxtecd ‏سنوی له موی اما بخ روا لت ول مان بل‎ tr ‏ال‎ uth added data types. اسه حصا سای رطخ را ‎Blow otidbutes oP tuples to hove coop‏ ای له بو ۳ ‏چیه سرام با و ول توارط‎ ty deta, whe ‏عمجم لت طسو‎ © Opword cowpatbliy wil: exiotey retticod krone. Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 وه‎ 

صفحه 4:
Cowplex Ona Types سس ۲ ا = سس مب یبوط جاص اه اه رو اه و مود موی جر لس © ط موی رت اون ‎Por‏ مطلن مق ی ‎lowe‏ © اسك فا ۱ © dw rektioes wheurver we dow oor (ecdar) udves — ‏سا مت ای‎ اص ات همم( ‎Retaees wohewatrd‏ )© رت مت سا مان 8 Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ee ©Sbervehnts, Cork ced Cnakershe 

صفحه 5:
‎a Desied Reliiog‏ او واممومبرظط ‎Reyiword-set ‎{parsing, analysis} {Internet, Web] ‎ ‎ ‎4 ‏و دا رما تا © ‎Guck book kos‏ © ‏بعل © ‎© asic ‏له‎ ‏له ,ال © ‏وا دده © سا ماج ل لا ‎publisher ‎(name, branch) (McGraw-Hill, New York) (Oxford, London) ‏وه ‎ ‎author-set ‎{Smith, Jones} (Jones, Frick] ‎ ‎title ‎Compilers ‎Networks ‎ ‎Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‎ ‎ ‎ ‎ ‎ ‎ 

صفحه 6:
+ @OP Osvvupostva oP Osstied Rekiva © Rewove whwardeess of Aarbooks by assuccieg thot the Polouterg ‏عمط‎ ‏:لاا صصص مس‎ © tke aber © be ‏الم سسسسط ص‎ © ‏موه ا وج )۳ ما مت(‎ © (te, cnthor) ۶ ‏سا عط)‎ ( © ‏احم لمع‎ ( Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 وه‎ 

صفحه 7:
+ @OP Osvrwpostva oP Pke-books title author title keyword Compilers | Smith Compilers | parsing Compilers | Jones Compilers | analysis Networks | Jones Networks | Internet Networks | Frick Networks | Web authors keywords title pub-name | pub-branch Compilers | McGraw-Hill | New York Networks Oxford London books4 Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, 

صفحه 8:
+ Problews wt POP Ochewa BL POC desk requires were ‏مات تا‎ joke fa herr queries. BOC rektiod view Aatborke dePiced by ior of ۵000 ‏زاس‎ ‎© hones the werd Por were ty ‏مسجو و‎ (but bevy the vaio ‏مها مروت‎ Moker onl dani. © Derd hae ohare erm oP redkenkney Bl WDested ‏اس مهو تا‎ wore coke here. Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 وه‎ 

صفحه 9:
+ Ovwpex Types ard OGL0009 ۲ ‏سس‎ ty GGL ty support voce turer tok! ۶ ‏وا ای ون‎ oben! res ‏بو یلیر ایو(‎ oe perth rb dense pee ۶ ‏سور سس‎ ‏اه سوه سل مه لو له(‎ © ‏سس‎ ‎© Obert ‏مس‎ ‎١ ‏ای‎ oben ‏سس واس لجن داسلا‎ 19 ‏امه ۵0:66 سم لس لته وماج حصي سا0‎ © Ort Ally ‏ماوت‎ tc coy ‏تاه موه ول‎ © @utsowe Protwes we ‏وا مر‎ rack of the wap pouwercid ‏تعسو ال‎ ۱ Read the wocud oP pour dotubose syste to see wht f supports Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 مه‎ 

صفحه 10:
+ ‏ی تسیب ی‎ axl Iderkxoe i OGL ۱ ‏سوام سای سر ام‎ acd wed ta OGL ‏را وی‎ Denver 1 ‏,سمس‎ ‎۱ [ Pred تسا( ‎erode up‏ (ومنسيي ‏ سم یا بلا (ر یی مس ‎wt Prod‏ © Dore: Pad ced wot Pred kedoot whether subypes cua be created On ekred epee vo be word ‏صقن و‎ Rbk ‏واه موی رت‎ Dred be canine ( one Dene, unkbess — Dekbron, ‏)سا‎ ۸۲۵۸ ete) ۳ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 11:
+ Gectred Tyupee (aod) ۲ OsercePred raw ies rede ype Owirwer Type us ( core Dare, ukbess ‏مس‎ ‎chateOP Birt te) wt Pro © Oog thea peut o tube whose rows ore a wserdefioed type Drede tbe rusixrer BP Oustrrer Type Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ea 

صفحه 12:
+ Detar ۳ Conard ‏ما ارت و‎ wi ‏تسجيا اجه و‎ ‏)سرت بسا‎ cle) ‏رس ای‎ yoror 19 ‏راجت تسف تلا لت(‎ ‏رن‎ ketnce webod oe OnDate (ra Dote ete) ‏جر ار ویر‎ Por Outre Type begs retara oxh\Date - we. chateOP Birk, od ۴ We ven ce Piel he oe oP ‏تس رت‎ ‏وود‎ cre kesknnre, weOuDate (ouredt_xkte) Brow ‏-وسحاصج‎ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏موه‎ 

صفحه 13:
4 ‏یج‎ that we have the ‏ار ما سا بشما‎ create type Person (name varchar(20), address varchar(20)) ® Using inheritance to define the student and teacher types create type Student under Person (degree varchar(20), department varchar(20)) create type Teacher under Person (salary integer, department varchar(20)) ™ Subtypes can redefine methods by using overriding method in place of method in the method declaration Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 هه‎ 

صفحه 14:
QOulpls Ieheriawe ۲ SQL:1999 and SQL:2003 do not support multiple inheritance 18 If our type system supports multiple inheritance, we can define a type for teaching assistant as follows: create type Jeaching Assistant under Student, Teacher ® To avoid a conflict between the two occurrences of department we can rename them create type Teaching Assistant under Student with (department as student_dept), Teacher with (department as teacher_dept ) Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ene ©Sbervehnts, Cork ced Cnakershe 

صفحه 15:
+ ‏روا رسب سر ق)‎ bioe ۲ ‏رون‎ requireweuts vo subtibles ced supertables. © Back tiple oP he supertdble (ey. perp) von corresprenl yf ost oe Rake i euck of the subebkes (e.. stvcbuts onl teurhers) :00 و مه تمس ۶ Ol ‏لب‎ coneepoadkn ‏مه امه وا‎ uth the sre ches Por icherted airbnies) cet be derived Brow ove tiple (eerie fst ocer toble). ۲ DW ket is, cack cally ost hove 3 wost spevPic ype ۱ De ‏اس اد و صا موی وم دا موی و سا پم‎ ter studs ‏وولو اه‎ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 16:
+ Orray wad Ouisst Types tc OGL ۱ ‏سابریا‎ of array onl ‏انس یی‎ ۳ (cane vearcke{20), Iroxeh varcka{20)) rede ype Povk ‏وه‎ ‎(abe verke{20), whore vetke{O) ary [0], ‏تسم‎ te, ‏عصساصاهم‎ (Pubboker, kepwordert vardka(O) ‏طح‎ ( ‏قوب‎ tebe bites oP Book Broker wy he cevted rekava booby, but uss array oP near ‏اب‎ pet Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏هه‎ 

صفحه 17:
Oreviva oP Ovleviod Odes 198 Array construction array [‘Silberschatz’, Korth’, Sudarshan’] ™ Multisets © multisetset [‘computer’, ‘database’, ‘SQL’] ™@ To create a tuple of the type defined by the books relation: (‘Compilers’, array[ Smith’, Jones’], Publisher (* McGraw-Hill’, New York’), multiset [‘ parsing’, analysis’ ]) ® To insert the preceding tuple into the relation books insert into books values (‘Compilers’, array[* Smith’, Jones’], Publisher (* McGraw-Hill’, New York’), multiset [‘ parsing’, analysis’ ]) Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ew ©Sbervehnts, Cork ced Cnakershe 

صفحه 18:
ص جا8) اس واه برس + “Do Prod oll books thot have the word “database” oso keyword, ‏سد‎ ‎Prow ‏بلس‎ ‏سروس ۰ بل مان‎ (( © We von wees iedvidudd eleweuts oP oa array by vstery fences © Gy: 1P we koow thot ‏اما لو و‎ kes three aukors, we could wre! vebot whore), wuir-eraf{S], ouhr—wra [9] Prow books where tie =“ Odtdbuse Systew Overs’ BP o eet orektios ooctaicieg pairs oF the Pore “the, cuhor-caee” Por euck book ord pack cuhor of the books setent (P..tie, (P.cruthkor Brow books ws 0, wacwet (B-cadiororry) war B (crater) 1 2109 ‏اه رو ات و لهچ مت رم مدر‎ velent tite, B.cukor, P.pestion Brow bockew (, wort ((B.cndtor-orry) wats orci © (snake, prostters ) Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 19:
سند 4 ۲ ‏و و ود ما اوه وه مب‎ wis Pewer (or a) neki ‎ur caked vacestag.‏ اه لسن ‎Bx,‏ ‎vob file, Bros ouhor, pubksher crane ws pub one,‏ ‎publeker broackes pub_broak, keyword‏ ‎Brow books ws , vemest( P.ndior_orra) we P (cuter),‏ ‎wenest (P.kepword_set) ve K (hepreord )‏ ‎Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏موه‎ 

صفحه 20:
مس + یلسانت ه ‎oP voces, crear‏ ون سا روما 0 ۳ ‎nord‏ مق ند مهم هویب ۹ ‎ee pbloher,‏ ) وال تلم ‎Pubboher‏ ساقت بعال سای سرا )مین 0 ‎prlbboher‏ رسای سا موی (ط وال ی ارام ‎BP cent‏ ‎car cnsor oe,‏ ( نت ) امصامت جلة اسجادد بسجساساطم جه ] د_لسمصا هه ( ل-ستمص]) اصامد 9 ‎by fe, publoher‏ ی Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 21:
GOP Osrswa oP Oevied Rekiva dO€ versica oP books title author pub-name pub-branch keyword Compilers | Smith McGraw-Hill | New York parsing Compilers Jones McGraw-Hill New York parsing Compilers | Smith McGraw-Hill New York analysis Compilers | Jones McGraw-Hill | New York analysis Networks Jones Oxford London Internet Networks Frick Oxford London Internet Networks Jones Oxford London Web Networks Frick Oxford London Web Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, 

صفحه 22:
+ Oxsary (Ovu.) © ‏صا جا اهر اس بو و ام لت‎ se subqueries ta the sete ‏.عصهام‎ volt ‏لته اعد ) تمجه‎ Brow whore P where Othe = D.the order by ‏رو له(‎ ‏یش سس سس نب‎ ‏و‎ ‏جه حيصا محا‎ where (the = B.tie) ‏اساسا وه‎ Brow bork we B Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 23:
یه لب یه الل BH Oehice o type Depo wits Piekd ft Piet Ludkis te a reP er Wo the pe Persvu, wih table people os scope: prewe ype Deported ( cnnve varchar (CD), head reP (Perera) soope revrk) © We con hea rede a kble ‏جنتصاتا هط‎ ‏موز هن بو سول ماوق بو‎ Oe oxo owt he decloraica soope people Pro ‏او اجه وا سا‎ ‏لین‎ oo oddhiicg to the pred ‏مت وال‎ ‏ون‎ thle eporkorcts of Depart (heud wil pious soppe pevrl) Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 24:
+ Areca RePereweTyped Oder ‎tiple uth o ol‏ حصلا سيمت ‎a rePereae ude, we cos Brot‏ 2100 1لا ‎rePerewe ond hea set he reherewe separ!‏ ‎hie deportes‏ وی ‎nd)‏ ت ا سح عمط صلس ‎vet hud = (eb p.perso_td‏ مجه ‎Brow people‏ ‎where ence 2 ‏(صامل"‎ ‎here cre = "OG! ‎Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 25:
Over ®evercied WeuPers De pe of the objectideatPier ust be spevitied us port oP the type ‏اجه متا‎ vePereored table, ood © OVhe tbe dePaticg weet speci) tho the ‏لتحي عو وه وا‎ rede ype (Persva (cow varckar{SO) © kes orruikny o tuple, we oad provide a vague udu Por the ‏تا‎ treet tei people (persva_id, cone, adress ) vies (OdCOFSO’?”, loka’, “GO Ovpote Runt) BH We pan hea wee he hewRer vdue whed keeriic ope ‏عمط و‎ © Qvoide wed Por ‏موه و‎ query to retrieve the ‏تا‎ feeert tito deporkreuts uhes((OG’, 0906666۵ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 26:
+ Over @everded ‘keuPers (Ova.) :ميلا جما جه صقي بصا بحم باط مد حك ‎Oo‏ ۴ مسج عونا ملحن با نوم (00) ‎(weave varchar‏ ((60)اسس لت ‎re row (cre)‏ ‎pred bbe peork oP Persva‏ حول مریم ی ور ‎se‏ ما مه رن سبط ۲ هه مه ما ۴ ‎ewe hie Sparc‏ ‎veker("OG', loko’)‏ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 27:
سپ هن + تسود اه ان مان وله له یه با لب ۲ له <- لو ‎heud -> unre,‏ وولو وس م۳ و ره لاو و تام مه و و ۱ ‎one‏ ماو اه با ووم لوت ۱ head were ot orePereure, 0 pit oP deportireuts wi peop would be required to yet ‏لیا و‎ © Dches expreveiny he ‏مه ای هو‎ Por the user Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, eer ©Sbervehnts, Cork ced Cnakershe 

صفحه 28:
+ Topics] OAR ‏و‎ Grok ty haw @-R Feces ore ‏اوه ما وی لو‎ Gobble ‏ات‎ ‎© Cork nble stores priwary hey aed those utiibutes dePiaed to thot tobe © Gack tible stores bok local dePiced avd tchertied ‏مان‎ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 29:
+ Perssied Proqrawiay bauyuaes BB Lexenanes exteuded wik cocsinunts to houde persistect det B Prxranxver ran wade persisted dais deci ۶ ‏روت و ای( نله بت‎ ond store tt back to deb (ucthe ewbeckted Gav) ۲ Persea ober! © ‏سوه معا اروص - حصا نوا‎ ‏مه له - من وا‎ ty ‏زان مس مه‎ © by warkiey = wohe obievis persisted ‏مس اه‎ © by reachubliiy - object is persisted P itis declared exptetly to be sv or is ‏ارم‎ Brow ot persisted object Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 30:
+ Obpot ect wd Potters رو ‎Drees oP pervaceure of object‏ ۲ ‎exertion of a seco proechere‏ محل بلس سس لجوجو 10 © مج و مور و و تاه مه مد رات موی © ۱ urn! worvss proqnny exenuioes, bul aot iP dote-sioracge Porat oa ‏ا‎ © ersten kierprourny, phe persisted worves chia reoryenizaire ‏ممما سوا بسعل لجن 0 يك للا‎ Reece © Or « OOO C++ ١ ‏سهان‎ ‎© Jan © kava Datcbure Objrts (DO) Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 31:
+ Onupureou tl O-0 and O4R ‏لت‎ ۲ Rechte ‏سوه‎ ‎© kop dala tyrer, powerPul very keeaanes, kids protection. ۲ Corse prowawornrtmnane-boed OO08s © couple data yes, integrative wi progroannioy ‏مج‎ high perPorecace. © Obpctrehtond systews © popes chia tires, powerPul query keener, hich protein. © Woe: Oey ‏ولا ما سا مرو ال‎ اما وم وس هچ ‎bul‏ مها مور موم بر © و چم عم رو لا سا ند ما ماه ‎database‏ Dende ‏سار‎ Orwmrte - O* Otire, Orn 8, OOOO, ‏سا0 لح 0 لا سواه 1 موه‎ 

صفحه 32:
Gad oP Okaper 

Chapter 9: Object-Based Databases Database System Concepts ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Database System Concepts ©Silberschatz, Korth and Sudarshan Chapter 9: Object-Based Databases  Complex Data Types and Object Orientation  Structured Data Types and Inheritance in SQL  Table Inheritance  Array and Multiset Types in SQL  Object Identity and Reference Types in SQL  Implementing O-R Features  Persistent Programming Languages  Comparison of Object-Oriented and Object-Relational Databases Database System Concepts - 5th Edition, Aug 9, 2005. 9.2 ©Silberschatz, Korth and Sudarshan Object-Relational Data Models  Extend the relational data model by including object orientation and constructs to deal with added data types.  Allow attributes of tuples to have complex types, including non-atomic values such as nested relations.  Preserve relational foundations, in particular the declarative access to data, while extending modeling power.  Upward compatibility with existing relational languages. Database System Concepts - 5th Edition, Aug 9, 2005. 9.3 ©Silberschatz, Korth and Sudarshan Complex Data Types   Motivation:  Permit non-atomic domains (atomic  indivisible)  Example of non-atomic domain: set of integers,or set of tuples  Allows more intuitive modeling for applications with complex data Intuitive definition:  allow relations whenever we allow atomic (scalar) values — relations within relations  Retains mathematical foundation of relational model  Violates first normal form. Database System Concepts - 5th Edition, Aug 9, 2005. 9.4 ©Silberschatz, Korth and Sudarshan Example of a Nested Relation  Example: library information system  Each book has   title,  a set of authors,  Publisher, and  a set of keywords Non-1NF relation books Database System Concepts - 5th Edition, Aug 9, 2005. 9.5 ©Silberschatz, Korth and Sudarshan 4NF Decomposition of Nested Relation   Remove awkwardness of flat-books by assuming that the following multivalued dependencies hold:  title author  title keyword  title pub-name, pub-branch Decompose flat-doc into 4NF using the schemas:  (title, author )  (title, keyword )  (title, pub-name, pub-branch ) Database System Concepts - 5th Edition, Aug 9, 2005. 9.6 ©Silberschatz, Korth and Sudarshan 4NF Decomposition of flat–books Database System Concepts - 5th Edition, Aug 9, 2005. 9.7 ©Silberschatz, Korth and Sudarshan Problems with 4NF Schema  4NF design requires users to include joins in their queries.  1NF relational view flat-books defined by join of 4NF relations:   eliminates the need for users to perform joins,  but loses the one-to-one correspondence between tuples and documents.  And has a large amount of redundancy Nested relations representation is much more natural here. Database System Concepts - 5th Edition, Aug 9, 2005. 9.8 ©Silberschatz, Korth and Sudarshan Complex Types and SQL:1999  Extensions to SQL to support complex types include:  Collection and large object types   Structured types  Nested record structures like composite attributes  Inheritance  Object orientation   Nested relations are an example of collection types Including object identifiers and references Our description is mainly based on the SQL:1999 standard  Not fully implemented in any database system currently  But some features are present in each of the major commercial database systems  Read the manual of your database system to see what it supports Database System Concepts - 5th Edition, Aug 9, 2005. 9.9 ©Silberschatz, Korth and Sudarshan Structured Types and Inheritance in SQL  Structured types can be declared and used in SQL create type Name as (firstname varchar(20), lastname varchar(20)) final create type Address as (street varchar(20), city varchar(20), zipcode varchar(20)) not final    Note: final and not final indicate whether subtypes can be created Structured types can be used to create tables with composite attributes create table customer ( name Name, address Address, dateOfBirth date) Dot notation used to reference components: name.firstname Database System Concepts - 5th Edition, Aug 9, 2005. 9.10 ©Silberschatz, Korth and Sudarshan Structured Types (cont.)  User-defined row types create type CustomerType as ( name Name, address Address, dateOfBirth date) not final  Can then create a table whose rows are a user-defined type create table customer of CustomerType Database System Concepts - 5th Edition, Aug 9, 2005. 9.11 ©Silberschatz, Korth and Sudarshan Methods  Can add a method declaration with a structured type. method ageOnDate (onDate date) returns interval year  Method body is given separately. create instance method ageOnDate (onDate date) returns interval year for CustomerType begin return onDate - self.dateOfBirth; end  We can now find the age of each customer: select name.lastname, ageOnDate (current_date) from customer Database System Concepts - 5th Edition, Aug 9, 2005. 9.12 ©Silberschatz, Korth and Sudarshan Inheritance  Suppose that we have the following type definition for people: create type Person (name varchar(20), address varchar(20))  Using inheritance to define the student and teacher types create type Student under Person (degree varchar(20), department varchar(20)) create type Teacher under Person (salary integer, department varchar(20))  Subtypes can redefine methods by using overriding method in place of method in the method declaration Database System Concepts - 5th Edition, Aug 9, 2005. 9.13 ©Silberschatz, Korth and Sudarshan Multiple Inheritance  SQL:1999 and SQL:2003 do not support multiple inheritance If our type system supports multiple inheritance, we can define a type for teaching assistant as follows: create type Teaching Assistant under Student, Teacher  To avoid a conflict between the two occurrences of department we can rename them  create type Teaching Assistant under Student with (department as student_dept ), Teacher with (department as teacher_dept ) Database System Concepts - 5th Edition, Aug 9, 2005. 9.14 ©Silberschatz, Korth and Sudarshan Consistency Requirements for Subtables  Consistency requirements on subtables and supertables.  Each tuple of the supertable (e.g. people) can correspond to at most one tuple in each of the subtables (e.g. students and teachers)  Additional constraint in SQL:1999: All tuples corresponding to each other (that is, with the same values for inherited attributes) must be derived from one tuple (inserted into one table).  That is, each entity must have a most specific type  We cannot have a tuple in people corresponding to a tuple each in students and teachers Database System Concepts - 5th Edition, Aug 9, 2005. 9.15 ©Silberschatz, Korth and Sudarshan Array and Multiset Types in SQL  Example of array and multiset declaration: create type Publisher as (name varchar(20), branch varchar(20)) create type Book as (title varchar(20), author-array varchar(20) array [10], pub-date date, publisher Publisher, keyword-set varchar(20) multiset ) create table books of Book  Similar to the nested relation books, but with array of authors instead of set Database System Concepts - 5th Edition, Aug 9, 2005. 9.16 ©Silberschatz, Korth and Sudarshan Creation of Collection Values Array construction array [‘Silberschatz’,`Korth’,`Sudarshan’]  Multisets   multisetset [‘computer’, ‘database’, ‘SQL’] To create a tuple of the type defined by the books relation: (‘Compilers’, array[`Smith’,`Jones’], Publisher (`McGraw-Hill’,`New York’), multiset [`parsing’,`analysis’ ])  To insert the preceding tuple into the relation books insert into books values (‘Compilers’, array[`Smith’,`Jones’], Publisher (`McGraw-Hill’,`New York’), multiset [`parsing’,`analysis’ ])  Database System Concepts - 5th Edition, Aug 9, 2005. 9.17 ©Silberschatz, Korth and Sudarshan Querying Collection-Valued Attributes  To find all books that have the word “database” as a keyword, select title from books where ‘database’ in (unnest(keyword-set ))  We can access individual elements of an array by using indices  E.g.: If we know that a particular book has three authors, we could write: select author-array[1], author-array[2], author-array[3] from books where title = `Database System Concepts’ To get a relation containing pairs of the form “title, author-name” for each book and each author of the book select B.title, A.author from books as B, unnest (B.author-array) as A (author )  To retain ordering information we add a with ordinality clause select B.title, A.author, A.position from books as B, unnest (B.author-array) with ordinality as A (author, position )  Database System Concepts - 5th Edition, Aug 9, 2005. 9.18 ©Silberschatz, Korth and Sudarshan Unnesting  The transformation of a nested relation into a form with fewer (or no) relation- valued attributes us called unnesting.  E.g. select title, A as author, publisher.name as pub_name, publisher.branch as pub_branch, K.keyword from books as B, unnest(B.author_array ) as A (author ), unnest (B.keyword_set ) as K (keyword ) Database System Concepts - 5th Edition, Aug 9, 2005. 9.19 ©Silberschatz, Korth and Sudarshan Nesting  Nesting is the opposite of unnesting, creating a collection-valued attribute  NOTE: SQL:1999 does not support nesting  Nesting can be done in a manner similar to aggregation, but using the function colect() in place of an aggregation operation, to create a multiset  To nest the flat-books relation on the attribute keyword:  select title, author, Publisher (pub_name, pub_branch ) as publisher, collect (keyword) as keyword_set from flat-books groupby title, author, publisher To nest on both authors and keywords: select title, collect (author ) as author_set, Publisher (pub_name, pub_branch) as publisher, collect (keyword ) as keyword_set from flat-books group by title, publisher Database System Concepts - 5th Edition, Aug 9, 2005. 9.20 ©Silberschatz, Korth and Sudarshan 1NF Version of Nested Relation 1NF version of books flat-books Database System Concepts - 5th Edition, Aug 9, 2005. 9.21 ©Silberschatz, Korth and Sudarshan Nesting (Cont.)  Another approach to creating nested relations is to use subqueries in the select clause. select title, array ( select author from authors as A where A.title = B.title order by A.position) as author_array, Publisher (pub-name, pub-branch) as publisher, multiset (select keyword from keywords as K where K.title = B.title) as keyword_set from books4 as B Database System Concepts - 5th Edition, Aug 9, 2005. 9.22 ©Silberschatz, Korth and Sudarshan Object-Identity and Reference Types  Define a type Department with a field name and a field head which is a reference to the type Person, with table people as scope: create type Department ( name varchar (20), head ref (Person) scope people)  We can then create a table departments as follows create table departments of Department  We can omit the declaration scope people from the type declaration and instead make an addition to the create table statement: create table departments of Department (head with options scope people) Database System Concepts - 5th Edition, Aug 9, 2005. 9.23 ©Silberschatz, Korth and Sudarshan Initializing Reference-Typed Values  To create a tuple with a reference value, we can first create the tuple with a null reference and then set the reference separately: insert into departments values (`CS’, null) update departments set head = (select p.person_id from people as p where name = `John’) where name = `CS’ Database System Concepts - 5th Edition, Aug 9, 2005. 9.24 ©Silberschatz, Korth and Sudarshan User Generated Identifiers  The type of the object-identifier must be specified as part of the type definition of the referenced table, and  The table definition must specify that the reference is user generated create type Person (name varchar(20) address varchar(20)) ref using varchar(20) create table people of Person ref is person_id user generated  When creating a tuple, we must provide a unique value for the identifier: insert into people (person_id, name, address ) values (‘01284567’, ‘John’, `23 Coyote Run’)  We can then use the identifier value when inserting a tuple into departments  Avoids need for a separate query to retrieve the identifier: insert into departments values(`CS’, `02184567’) Database System Concepts - 5th Edition, Aug 9, 2005. 9.25 ©Silberschatz, Korth and Sudarshan User Generated Identifiers (Cont.)  Can use an existing primary key value as the identifier: create type Person (name varchar (20) primary key, address varchar(20)) ref from (name) create table people of Person ref is person_id derived  When inserting a tuple for departments, we can then use insert into departments values(`CS’,`John’) Database System Concepts - 5th Edition, Aug 9, 2005. 9.26 ©Silberschatz, Korth and Sudarshan Path Expressions  Find the names and addresses of the heads of all departments: select head –>name, head –>address from departments  An expression such as “head–>name” is called a path expression  Path expressions help avoid explicit joins  If department head were not a reference, a join of departments with people would be required to get at the address  Makes expressing the query much easier for the user Database System Concepts - 5th Edition, Aug 9, 2005. 9.27 ©Silberschatz, Korth and Sudarshan Implementing O-R Features  Similar to how E-R features are mapped onto relation schemas  Subtable implementation  Each table stores primary key and those attributes defined in that table or,  Each table stores both locally defined and inherited attributes Database System Concepts - 5th Edition, Aug 9, 2005. 9.28 ©Silberschatz, Korth and Sudarshan Persistent Programming Languages  Languages extended with constructs to handle persistent data  Programmer can manipulate persistent data directly   no need to fetch it into memory and store it back to disk (unlike embedded SQL) Persistent objects:  by class - explicit declaration of persistence  by creation - special syntax to create persistent objects  by marking - make objects persistent after creation  by reachability - object is persistent if it is declared explicitly to be so or is reachable from a persistent object Database System Concepts - 5th Edition, Aug 9, 2005. 9.29 ©Silberschatz, Korth and Sudarshan Object Identity and Pointers   Degrees of permanence of object identity  Intraprocedure: only during execution of a single procedure  Intraprogram: only during execution of a single program or query  Interprogram: across program executions, but not if data-storage format on disk changes  Persistent: interprogram, plus persistent across data reorganizations Persistent versions of C++ and Java have been implemented   C++  ODMG C++  ObjectStore Java  Java Database Objects (JDO) Database System Concepts - 5th Edition, Aug 9, 2005. 9.30 ©Silberschatz, Korth and Sudarshan Comparison of O-O and O-R Databases  Relational systems   Persistent-programming-language-based OODBs   complex data types, integration with programming language, high performance. Object-relational systems   simple data types, powerful query languages, high protection. complex data types, powerful query languages, high protection. Note: Many real systems blur these boundaries  E.g. persistent programming language built as a wrapper on a relational database offers first two benefits, but may have poor performance. Database System Concepts - 5th Edition, Aug 9, 2005. 9.31 ©Silberschatz, Korth and Sudarshan End of Chapter Database System Concepts ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Database System Concepts ©Silberschatz, Korth and Sudarshan