1 / 47

Prof: Dr Rahgozar Euhanna Ghadimi, Mostafa Keikha

Object Relational Model. Discussion, Implementation. Prof: Dr Rahgozar Euhanna Ghadimi, Mostafa Keikha. Foreword. “ Object databases will gain ground, but their share of the world ’ s data will still be very small.

oona
Télécharger la présentation

Prof: Dr Rahgozar Euhanna Ghadimi, Mostafa Keikha

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Object Relational Model Discussion, Implementation Prof: Dr Rahgozar Euhanna Ghadimi, Mostafa Keikha Advanced DB System

  2. Foreword • “Object databases will gain ground, but their share of the world’s data will still be very small. The big change will come from the relational databases as they extend their databases to support more object-oriented features.” Martin Fowler, Oracle Press, 1999 Advanced DB System

  3. outline • History of DB Models • Object-Relational Model • Base features • Concepts • Examples • Object Relation mapping (ORM) • “Modern” ORM Solutions • Conclusion Advanced DB System

  4. History of DB Models • 1950 File Systems, Punched Cards • 1960 Hierarchical (IMS IBM Mainframes) • 1970 Network (CODASYL, IDMS) • 1980 Relational (ORACLE, DB2, Sybase) • 1990 Object-Oriented, Object-Relational (O2, ORACLE9i) Advanced DB System

  5. Relational Model • Emergence of data model • Data independence • High-level approach • Standardization • Built-in data types • Little abstraction • Separation between data and operations Advanced DB System

  6. Object-Oriented Model • Complex application datatypes • Object Abstraction • Encapsulation of behavior • High performance for specific application • No backwards compatibility • Closely tied to language and application Advanced DB System

  7. Object-Relational Model • Synthesis of two worlds • Upward compatibility • Robustness of mature systems • Legacy problems • Backward Compatibility Advanced DB System

  8. Main Features Relation is still the fundamental abstraction, with integration of OO features • Structured Types Non-atomic types • Methods Special operations can be defined for a type • References Pointers to tuples • SQL-99 includes many of the object-relational features to be described. Advanced DB System

  9. Structured Types - I • Attributes of relation schemas can be • Atomic • Relation schemas: Nested Relations Advanced DB System

  10. Structured Types - II Nested 4NF 1NF Advanced DB System

  11. Structured Types • UDT – User Defined Type • A UDT can be the type of a table • A UDT can be the type of an attribute belonging to some table • A UDT can inherit from another UDT CREATE TYPE T AS <attribute and method declarations>; Advanced DB System

  12. Nested Relations Example CREATE TYPE AddressType AS ( street CHAR(50), city CHAR(20) ); CREATE TYPE AddressTypeTable AS TABLE OF AddressType; CREATE TYPE StarType AS ( name CHAR(30), address AddressTypeTable ); CREATE TABLE MovieStar OF StarType; Advanced DB System

  13. Methods – SQL99 • Special operations defined for a type • In SQL, implementation defined with Presistent Stored Modules (PSM) language METHOD m() RETURNS <TYPE>; Advanced DB System

  14. Methods - Example CREATE TYPE AddressType AS ( street CHAR(50), city CHAR(20) ) METHOD houseNumber() RETURNS CHAR(10); CREATE METHOD houseNumber() RETURNS CHAR(10) FOR AddressType BEGIN … END; Advanced DB System

  15. References - I • Allow a tuple t refer to a tuple s rather than including s in t Advanced DB System

  16. References - II • If attribute A has a type that is a reference to a tuple in relation with schema R, we denote A as A(*R) • If A is a set of references, we denote A as A({*R}) Advanced DB System

  17. References – SQL99 - I • A table which type is a UDT may have a reference column that serves as its “ID” In CREATE TABLE statement, add REF IS <attribute name> <how generated> Where <how generated> is either • SYSTEM_GENERATED : DBMS generates unique IDs • DERIVED: DBMS uses primary key of the relation for IDs Advanced DB System

  18. References – SQL99 – I - Example CREATE TYPE MovieType AS ( title CHAR(30), year INTEGER ); CREATE TABLE Movie OF MovieType ( REF IS movieID DERIVED, PRIMARY KEY (title, year) ); Advanced DB System

  19. References – SQL99 - II Reference to a tuple of type T REF(T) Reference to tuples in relation R, where R is a table whose type is the UDT T REF(T) SCOPE R Advanced DB System

  20. References – SQL99 - II – Example CREATE TYPE StarType AS ( name CHAR(30), address AddressType, bestMovie REF(MovieType) SCOPE Movie ); Advanced DB System

  21. Object Relation mapping (ORM) • although OR DBs solve many problems, they are not so much practical. • Why? • ORM Advanced DB System

  22. What do RDBs do well? • Work with large amounts of data • Searching, sorting • Work with sets of data • Joining, aggregating • Sharing • Concurrency (Transactions) • Many applications • Integrity • Constraints • Transaction isolation Advanced DB System

  23. What do RDBs do badly? • Modeling • No polymorphism • Fine grained models are difficult • Business logic • Stored procedures (really they are sufficient?) Goal: Take advantage of those things that relational databases do well Without leaving the language of objects / classes Final goal :Do less work, Happy DBA Advanced DB System

  24. “Modern” ORM Solutions • Transparent Persistence • Persistent/transient instances • Automatic Dirty Checking • Transitive Persistence • Lazy Fetching • Outer Join Fetching • Runtime SQL Generation Advanced DB System

  25. Auction Object Model Advanced DB System

  26. Persistent Class public class AuctionItem { private Long _id; private Set _bids; private Bid _successfulBid private String _description; public Long getId() { return _id; } private void setId(Long id) { _id = id; } public String getDescription() { return _description; } public void setDescription(String desc) { _description=desc; } … } • Default constructor • Get/set pairs • Collection property is an interface type • Identifier property Advanced DB System

  27. XML Mapping <class name=“AuctionItem” table=“AUCTION_ITEM”> <id name=“id” column=“ITEM_ID”> <generator class=“native”/> </id> <property name=“description” column=“DESCR”/> <many-to-one name=“successfulBid” column=“SUCCESSFUL_BID_ID”/> <set name=“bids” cascade=“all” lazy=“true”> <key column=“ITEM_ID”/> <one-to-many class=“Bid”/> </set> </class> • Readable metadata • Column / table mappings • Candidate key generation strategy • Collection metadata • Fetching strategies Advanced DB System

  28. Dirty Checking • Retrieve an AuctionItem and change description Session session = sessionFactory.openSession(); Transaction tx = s.beginTransaction(); AuctionItem item = (AuctionItem) session.get(ActionItem.class, itemId); item.setDescription(newDescription); tx.commit(); session.close(); Advanced DB System

  29. Transitive Persistence • Retrieve an AuctionItem and create a new persistent Bid Bid bid = new Bid(); bid.setAmount(bidAmount); Session session = sf.openSession(); Transaction tx = session.beginTransaction(); AuctionItem item = (AuctionItem) session.get(ActionItem.class, itemId); bid.setItem(item); item.getBids().add(bid); tx.commit(); session.close(); Advanced DB System

  30. Detachment • Retrieve an AuctionItem and create a new persistent Bid Session session = sf.openSession(); Transaction tx = session.beginTransaction(); AuctionItem item = (AuctionItem) session.get(ActionItem.class, itemId); tx.commit(); session.close(); item.setDescription(newDescription); Session session2 = sf.openSession(); Transaction tx = session2.beginTransaction(); session2.update(item); tx.commit(); session2.close(); Advanced DB System

  31. Optimizing Data Access • Eager (Outer Join) Fetching • Lazy Fetching • Batch Fetching Advanced DB System

  32. Outer Join Fetching AuctionItem item = (AuctionItem) s.get(ActionItem.class, itemId); SELECT … FROM AUCTION_ITEM ITEM LEFT OUTER JOIN BID BID1 ON BID1.ITEM_ID = ITEM.ITEM_ID LEFT OUTER JOIN BID BID2 ON BID2.BID_ID = ITEM.SUCCESSFUL_BID WHERE ITEM.ITEM_ID = ? Advanced DB System

  33. Lazy Fetching AuctionItem item = (AuctionItem) session.get(ActionItem.class, itemId); SELECT … FROM AUCTION_ITEM ITEM WHERE ITEM.ITEM_ID = ? Iterator iter = item.getBids().iterate(); SELECT … FROM BID BID WHERE BID.ITEM_ID = ? item.getSuccessfulBid().getAmount(); SELECT … FROM BID BID WHERE BID.BID_ID = ? Advanced DB System

  34. Data Access mechanism <class name=“AuctionItem” table=“AUCTION_ITEM”> <id name=“id” column=“ITEM_ID”> <generator class=“native”/> </id> <property name=“description” column=“DESC”/> <many-to-one name=“successfulBid” outer-join=“true” column=“SUCCESSFUL_BID_ID”/> <set name=“bids” cascade=“all” Lazy=“true”> <key column=“ITEM_ID”/> <one-to-many class=“Bid”/> </set> </class> Advanced DB System

  35. Optimizing Data Access • Minimize row reads • Use lazy fetching • N+1 Selects Problem (too many roundtrips) • Minimize database roundtrips • Use outer join fetching • Cartesian Product Problem (huge result set) • (Much less important) Minimize column reads Advanced DB System

  36. Optimizing Data Access Solution: Runtime Fetch Strategies • Say what objects you need • Navigate the object graph Advanced DB System

  37. Intermediate Query Language “Minimal” OO concepts combine with of ANSI SQL Native SQL Queries with OO methods (DQE) Possible options Advanced DB System

  38. Intermediate Query Language • Make SQL be object oriented • Classes and properties instead of tables and columns • Polymorphism • Associations • Much less shorter than SQL • Full support for relational operations • Inner/outer/full joins, cartesian products • Projection • Aggregation (max, avg) and grouping • Ordering • Subqueries • SQL function calls Advanced DB System

  39. Intermediate Query Language • IQL is a language for talking about “sets of objects” • It unifies relational operations with object models Simplest IQL Query: from AuctionItem i.e. get all the AuctionItems: List allAuctions =session.createQuery(“from AuctionItem”) .list(); Advanced DB System

  40. Intemediate Query Language More realistic example: select item from AuctionItem item join item.bids bid where item.description like ‘hib%’ and bid.amount > 100 i.e. get all the AuctionItems with a Bid worth > 100 and description that begins with “hib” Advanced DB System

  41. Intermediate Query Language Projection: select item.description, bid.amount from AuctionItem item join item.bids bid where bid.amount > 100 order by bid.amount desc i.e. get the description and amount for all the AuctionItems with a Bid worth > 100 Advanced DB System

  42. Intermediate Query Language Aggregation: select max(bid.amount), count(bid) from AuctionItem item left join item.bids bid group by item.type order by max(bid.amount) Advanced DB System

  43. Intermediate Query Language Runtime fetch strategies: AuctionItem item = session.createQuery(…) .uniqueResult(); //associations already fetched item.getBids().iterator(); item.getSuccessfulBid().getAmount(); from AuctionItem item left join fetch item.bids join fetch item.successfulBid where item.id = 12 Advanced DB System

  44. Criteria Queries List auctionItems = session.createCriteria(AuctionItem.class) .setFetchMode(“bids”, FetchMode.EAGER) .add( Expression.like(“description”, description) ) .createCriteria(“successfulBid”) .add( Expression.gt(“amount”, minAmount) ) .list(); Equivalent IQL: from AuctionItem item left join fetch item.bids where item.description like :description and item.successfulbid.amount > :minAmount Advanced DB System

  45. Example Queries AuctionItem item = new AuctionItem(); item.setDescription(“hib”); Bid bid = new Bid(); bid.setAmount(1.0); List auctionItems = session.createCriteria(AuctionItem.class) .add( Example.create(item).enableLike(MatchMode.START) ) .createCriteria(“bids”) .add( Example.create(bid) ) .list(); Equivalent IQL: from AuctionItem item join item.bids bid where item.description like ‘hib%’ and bid.amount > 1.0 Advanced DB System

  46. Conclusion • whether Object-Relation mapping can be successful forever? • they are continue until they can convert objects to relation. Advanced DB System

  47. References [1]. Mapping Objects to Relational Databases: O/R Mapping in Detail, Scott W.ambler July 4 2005 [2] A Relational Model of Data for Large Shared Data Banks , E. F. Codd, 1970. [3] Achievement and weaknesses of object oriented databases , Sikha Bagui, Department of Computer Science, University of West Florida, U.S.A, Published by ETH Zurich,2003 . [4] On type systems for object oriented database programming language, Yuri Leontiev , M. Tammer Ozsa , Duane Szafron, ACM Computing Surveys,2002 [5] object/Relational Access Layers, a roadmap, Missing Links and More Patterns, wolfgang keller , 3rd European conference of pattern languages of programming and computing [6] DB2's Object-Relational Highlights http://www-106.ibm.com/developerworks/db2/library/techarticle/zeidenstein/0108zeidenstein.html [7]Simple Strategies for Complex Data: Oracle9i Object-Relational Technology http://www.oracle.com/technology/product/oracle9i/pdf/simple_strat_for_complex_rel2.pdf Advanced DB System

More Related