Transcript ppt
Object-Relational
Databases
Adapted from Database Systems
Concepts: Silberschatz, Korth, Sudarshan
1
Motivation
The use of database systems has become
widespread.
e.g. Multimedia databases, CAD, Hypertext databases
More and more demands are being placed on these
systems.
Relational systems are not suited to meet some of
these demands e.g. complex data types.
The object-oriented community was growing
therefore it was natural to try and apply some of its
features to the database community.
2
Object-Oriented vs
Object-Relational Databases
Object-Oriented Databases
Extend OO programming to include features required
for database systems e.g. persistent objects.
Object-Relational Databases
Extend the relational model to include useful features
from object-orientation e.g complex types.
Add constructs to relational query languages e.g. SQL
to deal with these extensions.
3
Object-Relational Databases
Extends relational databases to include useful object-
oriented features.
e.g. better data type support
Must extend the query language (SQL) in order to
manipulate this data.
Relational systems require that databases be in 1NF.
i.e. each attribute value must be atomic.
These normalisation requirements will have to be
lifted.
4
Violating 1NF
title
author-list
Compilers
Networks
{Smith, Jones}
{Jones, Frick}
title
Compliers
Compliers
Compliers
Compliers
Networks
Networks
Networks
Networks
author
Smith
Jones
Smith
Jones
Jones
Frick
Jones
Frick
pub-name
McGraw-Hill
McGraw-Hill
McGraw-Hill
McGraw-Hill
Oxford
Oxford
Oxford
Oxford
publisher
(name, branch)
(McGraw-Hill, NY)
(Oxford,London)
pub-branch
NY
NY
NY
NY
London
London
London
London
keyword-list
{parsing, analysis}
{Internet, Web}
keyword
parsing
parsing
analysis
analysis
Internet
Internet
Web
Web
title ->> author
title ->> keyword
title -> pub-name, pub-branch
5
Decomposing into 4NF
Authors
title
Compilers
Compilers
Networks
Networks
author
Smith
Jones
Jones
Frick
Keywords
title
Compilers
Compilers
Networks
Networks
keyword
parsing
analysis
Internet
Web
Books4
title
Compilers
Networks
pub-name
McGraw-Hill
Oxford
pub-branch
NY
London
Users then need to join tables to retrieve information from more than
one table.
Non-decomposed table may be more natural for user.
6
Structured and Collection Types
Consider the following, to define the relation books:
create type Publisher as
(name varchar(20) ,
branch varchar(20))
create type Book as
(title varchar (20),
author-array varchar(20) array[10],
pub-date date,
publisher Publisher,
keyword-set setof(varchar(20)))
create table books of Book
Differs from relational databases as attributes can be
sets, arrays and attributes can be composite.
7
Structured and Collection
Types
Book table could be created without books type:
create table books
(title varchar (20),
author-array varchar(20) array[10],
pub-date date,
publisher Publisher,
keyword-set setof(varchar(20)))
8
Inheritance – Types & Tables
Inheritance can be at the level of types or the level of tables.
Inheritance at the level of types:
create type Person
(name varchar(20),
address varchar(20))
create type Student
create type Teacher
under Person
under Person
(degree varchar(20),
(salary integer,
department varchar(20))
department varchar(20),)
Student and Teacher are subtypes of Person.
9
Multiple Inheritance
Consider the following:
create type TeachingAssistant
under Student, Teacher
Department is inherited from both Student and
Teacher so there is some ambiguity.
Department in Teacher is department teaches in.
Department in Student is department taught in.
Renaming should be used to avoid this:
create type TeachingAssistant
under Student with (department as student-dept),
Teacher with (department as teacher-dept)
10
Inheritance - Tables
create table people of Person
create table students of Student
under people
create table teachers of Teacher
under people
create table teaching-assistants of Teaching Assistant
under students, teachers
In relational systems, sub and super tables would have to be linked
directly using primary keys.
Each tuple in the subtable (e.g. students, teachers) is implicitly present
in its supertable (e.g. people)
11
Querying with Complex Types
Composite Attributes
E.g. Find the title and name of publisher for each book:
select title, publisher. name
from books
Set-valued attributes
E.g. Find all the books that have “database” as one of
their keywords.
select title
from books
where ‘database’ in (unnest(keyword-set))
12
Querying (con’t)
Arrays
E.g. Find the three authors of the “Database
System Concepts book.
select author-array[1], author-array[2],author-array[3]
from books
where title = ‘Database System Concepts’
E.g. Find title, author pairs for each book.
select B.title, A
from books as B, unnest(B.author-array) as A
author-array is a collection-valued field, can
be used in from clause.
13
Nesting and Unnesting
Nesting: Transforming a nested relation into 1NF.
e.g.
select name, A as author, date.day, date.month,
date.year, K as keyword
from doc as B, B.author-list as A, B.keyword-list as K
Unnesting: Transforming 1NF relation into nested
relation.
e.g.
select title,set(author) as author-set, Publisher(pub-name,pub-branch)
as publisher, set(keyword) as keyword-set
from flat-books
group by title, publisher
where flat-books is the 1NF version of the table.
14
Functions
Functions can be defined by users.
Use either a programming language or DML e.g. SQL
e.g. An SQL (extended) function that given a document returns the
number of authors.
create function author-count(title varchar(20))
returns integer
begin
declare a-count integer;
select count(author) into a-count
from authors
where authors.title = title
return a-count
end
Query: Find the name of all documents that have more than one author.
select title
from books4
where author-count(title) > 1
15
Creating Objects and Complex
Values
Inserting a tuple into relation books.
insert into books values
(‘Compliers’, array[‘Smith’, ‘Jones],Publisher(‘McGraw-Hill’,’NY’),
set(‘parsing’, ‘analysis’))
composite attributes: use parenthesis
set valued attributes : use keyword set and ()
arrays: use keyword array and []
Creating a type e.g. Publisher.
create function Publisher (n varchar(20), b varchar(20))
returns Publisher
begin
set name = n;
set branch = b;
end
16