1 / 40

Section 1

Section 1. Object Relational Databases. Section Content. 1.1 Introduction 1.2 Abstract Data Types 1.3 Inheritance and Identity 1.4 Rules 1.5 Using Oracle. 1.1 Introduction. The need for richer storage mechanisms Multimedia applications Incorporation of business rules

pearlie
Download Presentation

Section 1

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. Section 1 Object Relational Databases

  2. Section Content • 1.1 Introduction • 1.2 Abstract Data Types • 1.3 Inheritance and Identity • 1.4 Rules • 1.5 Using Oracle CA306 Object-Relational Databases

  3. 1.1 Introduction • The need for richer storage mechanisms • Multimedia applications • Incorporation of business rules • Reusability (inheritance) • Nested complex types • Relationships • Options • Object-oriented databases ? • Object-relational databases ? CA306 Object-Relational Databases

  4. Advantages • The main advantages come from reuse and sharing. • Reuse comes from the ability to extend the database server so that core functionality is performed centrally, rather than coded in each application. • An example is a complex type (or extended base type) which is defined within the database, but is used by many applications. Previously it was required to define this type in every application that used it, and develop the interface between the software ‘type’ and its representation in the database. Sharing is a consequence of this reuse. • From a practical point of view, end-users are happier to make the smaller ‘leap’ from relational to object-relational, rather that have to deal with a completely different paradigm (object-oriented). CA306 Object-Relational Databases

  5. Disadvantages • The ORDBMS is more complex and thus has increased costs. • Relational purists believe that the simplicity of the original model was its strength. • Pure object-oriented database engineers are unhappy with the object-relational terminology which is based on the relational model and not on object-oriented software engineering concepts. • An example is “user-defined data types” v “classes”. • Thus, there is a large semantic gap between the o-o and o-r database worlds. • ORDBMS engineers are data focused while OODB engineers have models which attempt to mirror the real-world (data & behaviour). CA306 Object-Relational Databases

  6. Third Generation Database System Manifesto The third-generation DSM was devised by Stonebraker’s group (of proposers) and defines those principles that ORDBMS designers should follow. • A third-generation DBMS must have a rich type system. • Inheritance is a good idea. • Functions (including database procedures and methods) and encapsulation are a good idea. • Unique identifiers for tuples should be assigned by the DBMS only if a user-defined primary key is unavailable. • Rules (triggers or constraints) will become a major feature in future database systems. They should not be associated with a specific function or collection. CA306 Object-Relational Databases

  7. Manifesto (contd.) • All programming access to a database should be through a non-procedural, high-level access language (such as SQL). • There should be more that one way to specify collections: one using enumeration of members, and a second using the query language to specify membership. • Updateable views are essential. • Performance indicators have nothing to do with data models. • Third-generation DBMSs must be accessible from multiple high-level languages. • Persistent forms of multiple high-level languages are a good idea. • SQL is ‘intergalactic data-speak’ regardless of its many faults. • Queries and results should be the lowest level of communication between client and server. CA306 Object-Relational Databases

  8. Sections Covered • 1.1 Introduction • 1.2 Abstract Data Types • 1.3 Inheritance and Identity • 1.4 Rules • 1.5 Using Oracle CA306 Object-Relational Databases

  9. 1.2 Abstract Data Types • There is a need to extend the base types provided in RDBMS and SQL as many real-world problems are difficult to express using simple base types. • All types are defined as “Abstract Data Types”. • An ADT includes a name, length (in bytes), procedures for converting a value from internal (database) to external (user) representation (and vice versa), and a default value. DEFINE TYPE int4 IS (InternalLength = 4, InputProc = CharToInt4, OutputProc = Int4toChar, Default = “0”) • Using Postgres the ADT int4 is defined. The CharToInt4 and Int4toChar procedures are implemented in C++ or Java and registered with the system using a DEFINE PROCEDURE command. CA306 Object-Relational Databases

  10. ADT Operations • Operations on ADTs are defined by specifying the number and type of operand, the return type, the precedence and associativity of the operator, and the procedure that implements it. • It may also specify procedures to be called (eg. a sort). DEFINE OPERATOR “+” (int4,int4) RETURNS int4 IS (Proc = Plus, Precedence = 5, Associativity = “left”) • The procedure Plus (that implemented +) is programmed using C++ or Java. • In this case there are 2 operands of type int4; the return type is int4, the precedence is 5 (in relation to other operations on int4); and parsing starts from the left. CA306 Object-Relational Databases

  11. Collection Data Types • A Collection type Tc is a named group of instances of another type Tb. For example, a collection Tc called DatabaseStudents is a collection of all type Tb (Student type) which are studying CA306. • There are three built-in forms of collections: SET, MULTISET and LIST. They differ in the rules that are applied to their contents. • SETs obey the rules of mathematical sets (relations). This means that a set can contain no more than one instance of a given value. In other words, an object (ref) can appear only once in a set. • LISTs contain numbered elements. • MULTISETs are SETs which permit duplicated (often referred to as BAGs). CA306 Object-Relational Databases

  12. Defining COLLECTION types • SET (integer not NULL) • LIST (varchar(40) not NULL) • Multiset (PersonName not NULL) • LIST (LIST (Revenue not NULL) not NULL) • It is possible to define a table that includes several COLLECTION columns. • CREATE TABLE Collection_Sampes ( Id integer not null primary key, List_sample LIST(varchar(16) not NULL), Set_sample SET(integer not null), Mset_sample MULTISET(Authors not NULL) ); CA306 Object-Relational Databases

  13. COLLECTION instances • Each COLLECTION type has a corresponding constructor: SET{}, MULTISET{}, or LIST{}, which enforce their respective rules. For example, if you try to insert a duplicate into a SET, it is disallowed. INSERT INTO Collection_Samples VALUES ( 1, LIST {‘John’, ‘Paul’, ‘George’, ‘Ringo’}, SET {63,64,65,66,67,68,69}, MULTISET{John,Paul,Paul,Paul,John,John} ); CA306 Object-Relational Databases

  14. Sections Covered • 1.1 Introduction • 1.2 Abstract Data Types • 1.3 Inheritance and Identity • 1.4 Rules • 1.5 Using Oracle CA306 Object-Relational Databases

  15. 1.3 Inheritance and Identity • A type is declared using the CREATE command. • A type inherits all attributes from its parents unless an attribute is overridden in the type definition. • Multiple inheritance is supported but a clash of (inherited) names will disallow the type definition. • Key specifications are also inherited. CREATE Person (fname = char[15], lname = char[15], sex = char, DataOfBirth = data) KEY (lname) CREATE Employee (StaffNo = char[5], position = char[10], salary = float4, Dept = char[4]) INHERITS(Person) CA306 Object-Relational Databases

  16. Inheritance Examples • The type Employee includes those attributes declared explicitly, together with those inherited from the Person relation. • The key is the inherited key from Person. • An instance is added to the Employee type using the APPEND command. APPEND Employee(StaffNo = “A123”, lname=“Bloggs”, fname=“Joe”, sex = “M”, DateOFBirth=“10/10/71”, position=“Sales”, Salary = “35000”) • A query to return members of this relation uses the RETRIEVE command. RETRIEVE (E.StaffNo, E.lname, E.position) FROM E IN Employee CA306 Object-Relational Databases

  17. Object Identity • Each type has an implicitly named attribute oid to represent the unique identifier of an object instance. • Each oid is created and maintained by the database. • Users can access but not update an oid. • The oid can be used by applications in the normal way. CREATE Dept (Manager = Employee, dname = char[25], location = char[25]) KEY (dname) • The Manager attribute is a reference to an object of the Employee type. CA306 Object-Relational Databases

  18. Identity Example • If it is necessary to add a new department (object) and create a reference to an object of another type (Employee), we could do so using a query. APPEND (Manager = Employee(e.oid), dname = “Sales”, location = “floor 2”) FROM e in Employee WHERE e.StaffID = “A332” • This creates a link between the new instance of the Dept type and an existing instance of the Employee type. CA306 Object-Relational Databases

  19. Sections Covered • 1.1 Introduction • 1.2 Abstract Data Types • 1.3 Inheritance and Identity • 1.4 Rules • 1.5 Using Oracle CA306 Object-Relational Databases

  20. 1.4 Rules • Rules are valuable in that they protect the integrity of data in a database. • Relational databases have referential integrity for foreign key management. • The general form of a rule is “on the occurrence of event x do action y”. • The are four variations in the proposed standard for ORDBs: update-update, query-update, update-query, and query-query rules. CA306 Object-Relational Databases

  21. Update-Update Rules • In this case, the event is an update, and the action is an update. • This is useful in cases where it is necessary to implement an audit eg. Create a new tuple in the Audit relation with username, date and description, each time a change is made to the Salary relation. CREATE RULE Salary_Update AS ON UPDATE TO Salary DO insert into Audit Values ($username, date, Salary.lname) • In the above example, the current username, date and the lname of the updated employee (in Salary) are recorded. Note that if we were only interested in one or some group of employees we could use a where clause (see next example). CA306 Object-Relational Databases

  22. Query-Update Rules • In this case, the event is a query, and the action is an update. • Similar to the previous example: a user is accessing the Salary relation (for a specific employee), and the system automatically records it. In this case, only for employee A515. CREATE RULE Salary_Access AS ON SELECT TO Salary where salary.StaffID = “A515” DO insert into Audit Values ($username, date, Salary.lname) • Many relational databases systems cannot implement query-update rules. CA306 Object-Relational Databases

  23. Update Query Rules • In this case, the event is an update, and the action is a query (which uses the results in a message). • Suppose that the deletion of tuples from the Author table is not recommended since new titles may come into stock. CREATE RULE Author_Delete_Alert AS ON DELETE TO Author DO ShowMessage “Deleting “+Author.name+”prevents new titles being entered into the database” • The query in this case is select Author.name which is used in the message. CA306 Object-Relational Databases

  24. Query-Query Rules • In this case, both the event and the action are read-only queries. • A example is where one retrieval operation will require an attribute from some other relation. • For example, when viewing details for a customer (from the Customer relation), their credit may be listed as “A2”, where the actual value for “A2” is inside a Credit relation. (Note we could do the same using a join query) CREATE RULE Credit_View AS ON SELECT TO Customer X DO Select C.value From Credit C Where C.id = X.CredRating CA306 Object-Relational Databases

  25. Guidelines Designers of rules must guard against or be aware of: • Multiple rules fired by the same event. • Chain rules that cause infinite loops. • Aborting the action part of a rule may terminate the whole transaction. In general, this should be avoided, and the rule part of the transaction becomes a new transaction in itself. CA306 Object-Relational Databases

  26. Triggers • A trigger is an SQL statement that is executed by the DBMS as a side effect of a modification to a table. • The basic format of a CREATE TRIGGER statement: CREATE TRIGGER name BEFORE | AFTER <event> ON <table name> REFERENCING <some values> FOR EACH ROW | STATEMENT WHEN <trigger condition> • A trigger has a name and an associated timing (before or after). CA306 Object-Relational Databases

  27. Trigger Timing • BEFORE INSERT • BEFORE UPDATE • BEFORE DELETE • AFTER INSERT • AFTER UPDATE • AFTER DELETE CA306 Object-Relational Databases

  28. AFTER INSERT trigger CREATE TRIGGER InsertMailshotTable AFTER INSERT ON PropertyForRent REFERENCING NEW ROW AS pfr BEGIN INSERT INTO Mailshot VALUES (SELECT ……. FROM …. WHERE …. END; CA306 Object-Relational Databases

  29. Sections Covered • 1.1 Introduction • 1.2 Abstract Data Types • 1.3 Inheritance and Identity • 1.4 Rules • 1.5 Using Oracle CA306 Object-Relational Databases

  30. 1.5 Using Oracle • Recent and current versions of Oracle have tried to include object-oriented features in some form. • Those features include: • User-defined data types • Methods • Object identifiers • references CA306 Object-Relational Databases

  31. User-Defined Data Types • Oracle supports two user-defined data types: object and collection types. CREATE TYPE AddressType AS OBJECT ( street VARCHAR(25), city VARCHAR(25), postcode VARCHAR(2) ); • The CREATE TYPE command allows us to create a new type. Types can subsequently be used to define tables. CA306 Object-Relational Databases

  32. Employee Example • Methods can be specified in user-defined types. CREATE TYPE PersonType AS OBJECT ( fname VARCHAR(25), lname VARCHAR(25), sex CHAR, DOB date, address AddressType, MEMBER FUNCTION Get_age RETURN INTEGER ); • The Get_age method uses the DOB attribute to calculate the current age. • A table definition for employees: CREATE TABLE Employee OF PersonType (lname PRIMARY KEY); CA306 Object-Relational Databases

  33. Methods • Methods are classified as member, static or comparison. • A member function is a function that always has an implicit SELF parameter as its first parameter, whose type is the containing object type. This method adheres to true object-oriented style and finds all its arguments among the attributes of the “object”. • A static method is a function that does not have an implicit SELF parameter. These methods are invoked using a type qualifier eg. Person.Get_age(). • A comparison method is used for comparing instances of object types. They can be defined in two ways: • a map method uses Oracle to compare built-in types; • an order method uses its own internal logic to compare two objects. • Note that both types cannot be defined for the same object. CA306 Object-Relational Databases

  34. Constructor Methods • Every object type has a system-defined constructor method that constructs a new object. • The constructor method has the same name as the object type and has parameters with the same names and types as the object type’s attributes. Employee(‘Joe’, ‘Bloggs’ ,’M’, ’10/10/71’, AddressType(’10 Glasnevin Ave’, ‘Dublin’, ‘9’)); CA306 Object-Relational Databases

  35. Object Identifiers • Objects that appear in object tables are called row objects and “objects” that occupy relational tables are called column objects. • Every “row” object in an object table has an associated logical logical identifier. • The unique OID may be specified to come from the row’s primary key, or to be system-generated. • OIDs can be used to fetch and navigate objects. CREATE TABLE Employee OF PersonType (lname PRIMARY KEY) OBJECT IDENTIFIER PRIMARY KEY; CA306 Object-Relational Databases

  36. References • Oracle provides a built-in data type called REF to encapsulate references to row objects of a specified object type. • A REF can be used to examine or update the object it refers to, and to obtain a copy of the object it refers to. • A REF value can be assigned another REF value or can be assigned NULL. Users cannot assign arbitrary values to a REF type. CREATE TYPE DepartmentType AS OBJECT ( dname VARCHAR(25), address AddressType, manager REF Employee, MEMBER FUNCTION Get_Emp_CountRETURN INTEGER ); CA306 Object-Relational Databases

  37. Collection Types • Oracle supports two collection types: array and table types. • An array is an ordered set of data elements of the same type. Each element has an index. CREATE TYPE FullnameType AS VARRAY(3) of VARCHAR(25); • The above definition creates a collection of 3 strings used as a name. This definition may then be used in some TYPE or TABLE definition. • A nested table is an unordered set of data elements of the same data type. It has a single column of either built-in or (user-defined) object type. CA306 Object-Relational Databases

  38. Nested Tables • If the column is an object type, it can be viewed as a multi-column table. • Begin by creating a table of object types. CREATE TYPE AddressType AS OBJECT ( street VARCHAR(25), city VARCHAR(25), postcode VARCHAR(2) ); CREATE TYPE AddressCollection AS TABLE OF AddressType; CA306 Object-Relational Databases

  39. Nested Table Example • Once defined, it is then necessary to declare an object of this type (declares a table), and then insert into the target table. address AddressCollection; • Now create the table for employees which have multiple addresses. CREATE TABLE Employee OF PersonType (lname PRIMARY KEY) OBJECT IDENTIFIER PRIMARY KEY NESTED TABLE address STORE AS AddressStorageTable; CA306 Object-Relational Databases

  40. Differences • Arrays have a maximum size; nested tables do not. • Individual elements can be deleted from a nested table, but not from an array. • Oracles stores array data “in-line” but stores nested tables as an external (system generated) table. • When stored in the database, arrays retain their ordering, but nested tables do not. CA306 Object-Relational Databases

More Related