Chapter 17 Object DBMSs Transparencies Chapter 17 Objectives

Chapter 17 Object DBMSs Transparencies

Chapter 17 - Objectives Advanced database applications. Unsuitability of RDBMSs database applications. for advanced Problems of storing objects in relational database. Definition for an OODBMS. Main features of the ODMG Object Data Standard. ©Pearson Education 2009 2

Chapter 17 - Objectives Advantages and disadvantages of OODBMSs. How the RDBMS has been extended into an ORDBMS. Main object SQL: 2006. ©Pearson Education 2009 management features of 3

Advanced database applications Interactive and Dynamic Web sites Office Information Systems (OIS) and Multimedia Systems Geographic Information Systems (GIS) Computer-aided Software Engineering (CASE). ©Pearson Education 2009 4

Interactive and dynamic web sites Consider online catalog for selling clothes. Web site maintains preferences for previous visitors and allows a visitor to: obtain 3 D rendering of any item based on color, size, fabric, etc; modify rendering to account for movement, illumination, backdrop, occasion, etc; select accessories to go with the outfit, from items presented in a sidebar; Need to handle multimedia content and to interactively modify display based on user preferences and user selections. Added complexity of providing 3 D rendering. ©Pearson Education 2009 5

Office Information Systems (OIS) Stores data relating to computer control of information in a business, including electronic mail, documents, invoices, and so on. Modern systems now handle free-form text, photographs, diagrams, audio and video sequences. Documents may have specific structure, perhaps described using mark-up language such as SGML, HTML, or XML. ©Pearson Education 2009 6

Geographic Information Systems (GIS) GIS database stores spatial and temporal information, such as that used in land management and underwater exploration. Much of data is derived from survey and satellite photographs, and tends to be very large. Searches may involve identifying features based, for example, on shape, color, or texture, using advanced pattern-recognition techniques. ©Pearson Education 2009 7

Computer-Aided Software Engineering (CASE) Stores data about stages of software development lifecycle. Designs of this type have some common characteristics: Data has many types, each with a small number of instances. Designs may be very large. Design is not static but evolves through time. Updates are far-reaching. Involves version control and configuration management. Cooperative engineering. ©Pearson Education 2009 8

Weaknesses of RDBMSs Poor Representation of “Real World” Entities Normalization leads to relations that do not correspond to entities in “real world”. Semantic Overloading Relational model has only one construct for representing data and data relationships: the relation. Relational model is semantically overloaded. Poor Support for Integrity and Enterprise Constraints ©Pearson Education 2009 9

Weaknesses of RDBMSs Homogeneous Data Structure Relational model assumes both horizontal and vertical homogeneity. Many RDBMSs now allow Binary Large Objects (BLOBs). Limited Operations RDBMs only have a fixed set of operations which cannot be extended. Difficulty Handling Recursive Queries ©Pearson Education 2009 10

Weaknesses of RDBMSs Impedance Mismatch Most DMLs lack computational completeness. To overcome this, SQL can be embedded in a high-level 3 GL. This produces an impedance mismatch mixing different programming paradigms. Estimated that as much as 30% of programming effort and code space is expended on this type of conversion. ©Pearson Education 2009 11

Weaknesses of RDBMSs Other Problems with RDBMSs Transactions are generally short-lived and concurrency control protocols not suited for long-lived transactions. Schema changes are difficult. RDBMSs are poor at navigational access. ©Pearson Education 2009 12

Storing objects in a relational database One approach to achieving persistence with an OOPL is to use an RDBMS as the underlying storage engine. Requires mapping class instances (i. e. objects) to one or more tuples distributed over one or more relations. To handle class hierarchy, have two basics tasks to perform: (1) design relations to represent class hierarchy; (2) design how objects will be accessed. ©Pearson Education 2009 13

Storing objects in a relational database ©Pearson Education 2009 14

Mapping classes to tables Number of strategies for mapping classes to tables, although each results in a loss of semantic information. (1) Map each class or subclass to a table: Staff (staff. No, name, position, salary) Manager (staff. No, bonus, mgr. Start. Date) Sales. Personnel (staff. No, sales. Area, car. Allowance) Secretary (staff. No, typing. Speed) ©Pearson Education 2009 15

Mapping classes to tables (2) Map each subclass to a table Manager (staff. No, name, position, salary, bonus, mgr. Start. Date) Sales. Personnel (staff. No, name, position, salary, sales. Area, car. Allowance) Secretary (staff. No, name, position, salary, typing. Speed) (3) Map the hierarchy to a single table Staff (staff. No, name, position, salary, bonus, mgr. Start. Date, sales. Area, car. Allowance, typing. Speed, type. Flag) ©Pearson Education 2009 16

OODBMSs No one agreed object data model. One definition: Object-Oriented Data Model (OODM) Data model that captures semantics of objects supported in object-oriented programming. Object-Oriented Database (OODB) Persistent and sharable collection of objects defined by an ODM. Object-Oriented DBMS (OODBMS) Manager of an ODB. ©Pearson Education 2009 17

OODBMS - Concepts ©Pearson Education 2009 18

Relationships represented using reference attributes, typically implemented using OIDs. Consider how to represent following binary relationships according to their cardinality: 1: 1 1: * *: *. ©Pearson Education 2009 19

1: 1 Relationships Add reference attribute to A and, to maintain referential integrity, reference attribute to B. ©Pearson Education 2009 20

1: * Relationships Add reference attribute to B and attribute containing set of references to A. ©Pearson Education 2009 21

*: * Relationships Add attribute containing set of references to each object. For relational database design, would decompose *: N into two 1: * relationships linked by intermediate entity. Can also represent this model in an ODBMS. ©Pearson Education 2009 22

*: * Relationships ©Pearson Education 2009 23

Alternative Modeling for *: * Relationships ©Pearson Education 2009 24

Referential integrity Several techniques to handle referential integrity: Do not allow user to explicitly delete objects. System is responsible for “garbage collection”. Allow user to delete objects when they are no longer required. System may detect invalid references automatically and set reference to NULL or disallow the deletion. ©Pearson Education 2009 25

Referential integrity Allow user to modify and delete objects and relationships when they are no longer required. System automatically maintains the integrity of objects. Inverse attributes can be used to maintain referential integrity. ©Pearson Education 2009 26

ODMG Standard Established by vendors of OODBMSs to define standards. Have produced an Object Model that specifies a standard model for the semantics of database objects. The Java binding was submitted to JCP as basis for Java Data Objects (JDO). In 2001, ODMG completed its work and disbanded. ©Pearson Education 2009 27

ODMG Object Model Basic modeling primitives are object/literal. Only an object has a unique identifier. Objects/literals can be categorized into types. All objects of given type exhibit common behavior and state. A type is itself an object. Behavior defined by set of operations that can be performed on or by object. State defined by values objects carry for a set of properties. ©Pearson Education 2009 28

ODMG Object Model Property may be either an attribute of object or relationship between object and one or more other objects. ODMS stores objects, enabling them to be shared by multiple users and applications. ODMS based on a schema defined in ODL. ©Pearson Education 2009 29

ODMG Object Definition Language (ODL) Language for defining the specifications of object types for ODMG-compliant systems (equivalent to DDL of traditional DBMSs). Main objective is to facilitate portability of schemas between complaint systems, while helping to provide interoperability between ODMSs. ODL defines the attributes and relationships of types and specifies the signatures of the operations (but not the implementation). ©Pearson Education 2009 30

ODMG Object Definition Language (ODL) ©Pearson Education 2009 31

ODL Example ©Pearson Education 2009 32

©Pearson Education 2009 33

ODMG Object Query Language (OQL) Provides declarative access to object database using SQL-like syntax. Does not provide explicit update operators leaves this to operations defined on object types. Can be used as a standalone language and as a language embedded in another language, for which an ODMG binding is defined (Smalltalk, C++, and Java). OQL can also invoke operations programmed in these languages. ©Pearson Education 2009 34

ODMG Object Query Language (OQL) Can be used for associative and navigational access: Associative query returns collection of objects. How these objects are located is responsibility of ODMS. Navigational query accesses individual objects and object relationships used to navigate from one object to another. Responsibility of application program to specify procedure for accessing the objects. ©Pearson Education 2009 35

ODMG Object Query Language (OQL) An OQL query is a function that delivers an object whose type may be inferred from operator contributing to query expression. ©Pearson Education 2009 36

Example 17. 2 Use of extents/traversal paths Get set of all staff (with identity) staff Get set of all distribution center managers (with identity) distribution. Centers. Managed. By ©Pearson Education 2009 37

Example 17. 2 Use of extents/traversal paths Find all distribution centers in Seattle SELECT d. d. Center. No FROM d IN distribution. Centers WHERE d. address. city = “Seattle”; Assume seattle. Centers is named object (from last query). Find all staff who work at that center. seattle. Centers. Has ©Pearson Education 2009 38

Example 17. 2 Use of extents/traversal paths Because of ambiguity over return result, cannot access assistants’ salaries using: seattle. Centers. Has. salary Instead use: SELECT [DISTINCT] s. salary FROM s IN seattle. Centers. Has; ©Pearson Education 2009 39

Example 17. 3 Use of aggregates How many staff work in New York. OQL aggregate can be applied within SELECT or to result of SELECT. Following equivalent: SELECT COUNT(a) FROM a IN assistants WHERE a. Works. At. address. city = “New York”; COUNT(SELECT 1 FROM a IN assistants WHERE a. Works. At. address. city = “New York”); ©Pearson Education 2009 40

Advantages of OODBMSs Enriched Modeling Capabilities. Extensibility. Removal of Impedance Mismatch. Applicability to Advanced Database Applications. Support for Schema Evolution. Support for Long Duration Transactions. More Expressive Query Language. Improved Performance. ©Pearson Education 2009 41

Disadvantages of OODBMSs Lack of Universal Data Model. Lack of Standards. Lack of Experience. Competition from ORDBMSs. Complexity. Lack of Support for Views. Lack of Support for Security. ©Pearson Education 2009 42

ORDBMSs An evolutionary approach to integrating the concept of the “object” into databases (OODBMS is more a revolutionary approach). RDBMSs currently dominant database technology with estimated sales $6 - $10 billion per year ($25 billion with tools sales included). . Some analysts believe the ORDBMS market will have a 50% larger share than the RDBMS. ©Pearson Education 2009 43

Advantages of ORDBMSs Resolves many of known weaknesses of RDBMS. Reuse and sharing: reuse comes from ability to extend server to perform standard functionality centrally; gives rise to increased productivity both for developer and end-user. Preserves significant body of knowledge and experience gone into developing relational applications. ©Pearson Education 2009 44

Disadvantages of ORDBMSs Complexity. Increased costs. Proponents of relational approach believe simplicity and purity of relational model are lost. Some believe RDBMS is being extended for what will be a minority of applications. OO purists not attracted by extensions either. SQL now extremely complex. ©Pearson Education 2009 45

SQL: 2006 – Some New Object Features Type constructors for row types and reference types. User-defined types (distinct types and structured types) that can participate in supertype/subtype relationships. User-defined procedures, functions, methods, and operators. Reference types and object identity. ©Pearson Education 2009 46

Row types Sequence of field name/data type pairs that provides data type to represent types of rows in tables. Allows complete rows to be: stored in variables, passed as arguments to routines, returned as return values from function calls. Also allows column of table to contain row values. ©Pearson Education 2009 47

Example 17. 4 – Use of row type CREATE TABLE Distribution. Center( d. Center. No CHAR(4), d. Address ROW(dstreet VARCHAR(60), dcity VARCHAR(20), d. State CHAR(2), z. Zip. Code CHAR(5))); INSERT INTO Distribution. Center VALUES (‘D 001’, ROW(‘ 8 Jefferson Way’, ‘Portland’, ‘OR’, ‘ 97201’)); ©Pearson Education 2009 48

User-Defined Types (UDTs) SQL: 2006 allows definition of UDTs. May be used in same way as built-in types. Subdivided into two categories: distinct types and structured types. Distinct type allows differentiation between same underlying base types: CREATE TYPE Actor. Number. Type AS VARCHAR(5) FINAL; CREATE TYPE Staff. Number. Type AS VARCHAR(5) FINAL; If we now try to treat an instance of one type as instance of the other, an error will be generated. ©Pearson Education 2009 49

UDTs – observer and mutator functions Value of an attribute can be accessed using common dot notation: p. n. Name p. name = ‘A. Smith’ SQL encapsulates each attribute through an observer (get) and a mutator (set) function. These functions can be redefined by user in UDT definition. FUNCTION name(p Person. Type) RETURNS VARCHAR(15) RETURN p. name; ©Pearson Education 2009 50

UDTs – observer and mutator functions These mutator would be: FUNCTION name(p Person. Type RESULT, new. Value VARCHAR(15)) RETURNS Person. Type BEGIN p. name=new. Value RETURN p; END; ©Pearson Education 2009 51

UDTs – constructor functions A (public) constructor function is automatically defined to create new instances of type: SET p = NEW Person. Type; The constructor function has same name as type, takes zero arguments, and returns a new instance with attributes set to their default values. User-defined constructor methods can be provided to initialize new instances. Must have same name as UDT but different parameters to public constructor. ©Pearson Education 2009 52

UDTs – constructor functions CREATE CONSTRUCTOR METHOD Person. Type ( nm VARCHAR(15), DOB DATE) RETURNS Person. Type BEGIN SET SELF. name = nm; SET SELF. DOB = DOB; RETURN SELF; END; SET p = NEW Person. Type(‘John White’ ’ 27 -May-1950’); ©Pearson Education 2009 53

Example 17. 5 –Definition of new UDT CREATE TYPE Person. Type AS (name VARCHAR(15), date. Of. Birth DATE) INSTANTIABLE NOT FINAL INSTANCE METHOD age () RETURNS INTEGER, INSTANCE METHOD age (DOB DATE) RETURNS Person. Type; CREATE INSTANCE METHOD age () RETURNS INTEGER FOR Person. Type BEGIN RETURN /* age calculated from SELF. date. Of. Birth */; END; CREATE INSTANCE METHOD age (DOB DATE) RETURNS Person. Type FOR Person. Type BEGIN SELF. date. Of. Birth = /* code to set date. Of. Birth from DOB*/; RETURN SELF; END; ©Pearson Education 2009 54

Subtypes/Supertypes UDTs can participate in subtype/supertype hierarchy using UNDER clause. Multiple inheritance is not supported. Subtype inherits all the attributes and behavior of its supertypes. Can define additional attributes and methods and can override inherited methods. Concept of substitutability supported: whenever instance of supertype expected instance of subtype can be used in its place. ©Pearson Education 2009 55

Example 17. 6 - Subtypes CREATE TYPE Staff. Type UNDER Person. Type AS ( staff. No VARCHAR(5), position VARCHAR(10) DEFAULT ‘Assistant’, salary DECIMAL(7, 2), e. Mail VARCHAR(20), d. Center. No CHAR(4)) INSTANTIABLE NOT FINAL REF IS SYSTEM GENERATED INSTANCE METHOD is. Manager () RETURNS BOOLEAN; CREATE INSTANCE METHOD is. Manager () RETURNS BOOLEAN BEGIN IF s. position = ‘Manager’ THEN RETURN TRUE; ELSE RETURN FALSE; END IF END) ©Pearson Education 2009 56

User-Defined Routines (UDRs) UDRs define methods for manipulating data. UDRs may be defined as part of a UDT or separately as part of a schema. An SQL-invoked routine may be a procedure, function, or method. May be externally provided in standard programming language or defined completely in SQL. ©Pearson Education 2009 57

UDRs Three types of method: constructor, initializes a newly created instance of UDT; instance, operates on specific instance of a UDT; static, operates at the UDT level. In first two cases, methods include an additional implicit SELF parameter of same type as UDT. ©Pearson Education 2009 58

UDRs Constructor method invoked using NEW expression. Instance method invoked using standard dot notation (p. name) or using generalized invocation format (p AS Staff. Type). name(). Static method invoked using “: : ” (Staff. Type: : total. Staff()) ©Pearson Education 2009 59

Reference types and object identity Reference types used to define relationships between row types and uniquely identify a row in a table. Reference type value can be stored in one table and used as direct reference to a row in some base table defined to be of this type (similar to C/C++ pointer type and OID of OODBMSs). Thus, references allow a row to be shared among multiple tables, and enable users to replace complex join definitions in queries with much simpler path expressions. REF IS SYSTEM GENERATED in CREATE TYPE indicates that actual values of associated REF type are provided by the system. 60 ©Pearson Education 2009

Example 17. 7 Table based on UDT CREATE TABLE Person ( info Person. Type); or CREATE TABLE Person OF Person. Type; ©Pearson Education 2009 61

Examples 17. 8/9 – Subtables & object identity CREATE TABLE Staff OF Staff. Type UNDER Person ( REF IS staff. ID SYSTEM GENERATED); CREATE TABLE Distribution. Center( d. Center. No CHAR(4) NOT NULL, d. Street VARCHAR(60) NOT NULL, d. City VARCHAR(20) NOT NULL, d. State CHAR(2) NOT NULL, d. Zip. Code CHAR(5) NOT NULL, mgr. Staff. ID REF(Staff. Type) SCOPE Staff REFERENCES ARE CHECKED ON DELETE CASCADE, PRIMARY KEY (d. Center. No)); ©Pearson Education 2009 62

Examples – Querying data SELECT s. name FROM Staff s WHERE s. position = ‘Manager’; SELECT s. name, s. age FROM Staff s WHERE s. is. Manager; ©Pearson Education 2009 63

Example 17. 12 – Deref operator SELECT d. mgr. Staff. ID–>name AS name FROM Distribution. Center d WHERE d. d. Center. No = ‘D 001’; SELECT DEREF(d. mgr. Staff. ID) AS Staff FROM Distribution. Center d WHERE d. d. Center. No = ‘D 001’; ©Pearson Education 2009 64