Transcript Object Relational Databases

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
CA306 Object-Relational Databases
1-2
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
1-3
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
1-4
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
1-5
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
1-6
Manifesto (contd.)
• All programming access to a database should be through a nonprocedural, 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 highlevel 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
1-7
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
1-8
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
1-9
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
1-10
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
1-11
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
List_sample
Set_sample
Mset_sample
);
CA306 Object-Relational Databases
integer not null primary key,
LIST(varchar(16) not NULL),
SET(integer not null),
MULTISET(Authors not NULL)
1-12
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
1-13
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
1-14
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
1-15
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
1-16
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
1-17
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
1-18
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
1-19
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
1-20
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
1-21
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
1-22
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
1-23
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
1-24
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
1-25
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
1-26
Trigger Timing
• BEFORE INSERT
• BEFORE UPDATE
• BEFORE DELETE
• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE
CA306 Object-Relational Databases
1-27
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
1-28
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
1-29
1.5 Using Oracle
• Recent and current versions of Oracle have tried to include objectoriented features in some form.
• Those features include:
+
+
+
+
User-defined data types
Methods
Object identifiers
references
CA306 Object-Relational Databases
1-30
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
1-31
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
1-32
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
1-33
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
1-34
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
1-35
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_Count RETURN INTEGER );
CA306 Object-Relational Databases
1-36
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
1-37
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
1-38
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
1-39
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
1-40