Object-Relational Databases
Database Management Peter Wood Object-Relational Databases User-Defined Types Type Inheritance Table Inheritance
Why is the relational model so successful? I
Data independence
I
High level query language - SQL
I
Query optimisation
I
Support for integrity constraints
I
Well-understood database design
I
Transaction management and concurrency
Array and Multiset Types Object Identity and References
Why might we need Object-Oriented Databases?
Database Management Peter Wood Object-Relational Databases User-Defined Types Type Inheritance Table Inheritance Array and Multiset Types
I
For some applications 1NF is too strict. I I I
I
Document management and web site management Geographic and Statistical database management Biological data management
SQL is not a complete programming language such as C++ and Java — early versions did not support abstract data types
Object Identity and References
Object-Oriented Approach
Database Management Peter Wood Object-Relational Databases User-Defined Types
I
Couple an Object-Oriented programming language with a DBMS I
One approach is to implement on top of a relational DBMS.
I
Object-Oriented databases failed to deliver robust and versatile systems which can compete with relational database technology
I
Object-Relational approach — integrate Object-Oriented features into existing relational DBMSs
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Object-Relational Databases
Database Management Peter Wood Object-Relational Databases
Object-relational systems (and SQL:1999) provide a number of extra features over relational systems, including: I
user-defined types
I
type inheritance
I
table inheritance
I
array and multiset types
I
object identity
I
reference types
I
methods
User-Defined Types Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Creating User-Defined Types
Database Management Peter Wood Object-Relational Databases
CREATE TYPE NameType AS ( firstname varchar(20), lastname varchar(20)) FINAL; CREATE TYPE AddressType AS ( street varchar(20), city varchar(20) postcode varchar(8)) NOT FINAL; FINAL and NOT FINAL relate to subtyping (see later)
User-Defined Types Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Using User-Defined Types
Database Management Peter Wood Object-Relational Databases User-Defined Types
CREATE TABLE person ( name NameType, address AddressType, dateOfBirth date); Note that the above table definition uses the user-defined types NameType and AddressType, along with the built-in type date
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Using User-Defined Types
Database Management Peter Wood Object-Relational Databases User-Defined Types
CREATE TABLE person ( name NameType, address AddressType, dateOfBirth date); Note that the above table definition uses the user-defined types NameType and AddressType, along with the built-in type date An SQL example using the above definition is: SELECT name.firstname, name.lastname FROM person;
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Type Inheritance
Database Management Peter Wood
CREATE TYPE PersonType ( name Name, address Address) NOT FINAL; CREATE TYPE StudentType UNDER PersonType ( degree varchar(20), department varchar(20)) FINAL; StudentType is a subtype of PersonType. NOT FINAL means the type can have subtypes. FINAL means the type cannot have subtypes. StudentType inherits the attributes of PersonType.
Object-Relational Databases User-Defined Types Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Table Inheritance
Database Management Peter Wood Object-Relational Databases User-Defined Types Type Inheritance
CREATE TABLE person OF PersonType;
Table Inheritance Array and Multiset Types Object Identity and References
CREATE TABLE student OF StudentType UNDER person; Table student will have all attributes of StudentType (which includes those of PersonType).
Table Inheritance
Database Management Peter Wood Object-Relational Databases User-Defined Types Type Inheritance
CREATE TABLE person OF PersonType;
Table Inheritance Array and Multiset Types Object Identity and References
CREATE TABLE student OF StudentType UNDER person; Table student will have all attributes of StudentType (which includes those of PersonType). Every tuple in student is implicitly also in person, so SELECT * FROM person retrieves all person tuples, including student tuples.
Array and Multiset Types Multiset (a set with possibly duplicate elements) types were added in SQL:2003.
Database Management Peter Wood Object-Relational Databases User-Defined Types
CREATE TYPE Book AS ( title varchar(20), authors varchar(20) array [10], keywords varchar(20) multiset) NOT FINAL; CREATE TABLE books OF Book;
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Array and Multiset Types Multiset (a set with possibly duplicate elements) types were added in SQL:2003.
Database Management Peter Wood Object-Relational Databases User-Defined Types
CREATE TYPE Book AS ( title varchar(20), authors varchar(20) array [10], keywords varchar(20) multiset) NOT FINAL; CREATE TABLE books OF Book; INSERT INTO books VALUES (’Database System Concepts’, array[’Silberschatz’, ’Korth’, ’Sudarshan’], multiset[’computer’, ’database’, ’SQL’]);
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Array and Multiset Types Multiset (a set with possibly duplicate elements) types were added in SQL:2003.
Database Management Peter Wood Object-Relational Databases User-Defined Types
CREATE TYPE Book AS ( title varchar(20), authors varchar(20) array [10], keywords varchar(20) multiset) NOT FINAL; CREATE TABLE books OF Book; INSERT INTO books VALUES (’Database System Concepts’, array[’Silberschatz’, ’Korth’, ’Sudarshan’], multiset[’computer’, ’database’, ’SQL’]); SELECT authors[1] FROM books WHERE ’database’ IN (UNNEST(keywords));
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Object Identity and References
Database Management Peter Wood
Tuples in a table can be declared to have identifiers (using REF IS).
Object-Relational Databases User-Defined Types
This allows for data sharing (using REF) without duplication, e.g. a couple having the same children CREATE TABLE person OF PersonType ( REF IS personID SYSTEM GENERATED); CREATE TABLE Department ( name varchar(20), head REF(PersonType) SCOPE person); The reference is restricted to tuples of the table person. Makes references behave like foreign keys.
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
Object Identity and References
Database Management Peter Wood
Tuples in a table can be declared to have identifiers (using REF IS).
Object-Relational Databases User-Defined Types
This allows for data sharing (using REF) without duplication, e.g. a couple having the same children CREATE TABLE person OF PersonType ( REF IS personID SYSTEM GENERATED); CREATE TABLE Department ( name varchar(20), head REF(PersonType) SCOPE person); The reference is restricted to tuples of the table person. Makes references behave like foreign keys. SELECT name, deref(head).name FROM Department;
Type Inheritance Table Inheritance Array and Multiset Types Object Identity and References
