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