Modelos de Dados para Aplicações
Cien8ficas
Fabio Porto
MCTI – LNCC – DEXL Lab
fporto@lncc.br
hIp://dexl.lncc.br
3/28/12 Modelagem Dados e Workflow Cien8fico 1Parte II
Object‐RelaVonal Model
Agenda
•
IntroducVon
•
Main features
•
OR in Oracle
•
Conclusion
4New applicaVons
– BioinformaVcs – e‐learning – SimulaVons – MulVmedia – Geographic InformaVon Systems – Web Data integraVon – ScienVfic Workflow – …•
New requirements
– Structured objects, high volumes – New data types (video, XML,…) – Long transacVons (scienVfic workflows, S.E.,..) – OO Programming paradigm – IS not according to user s needsScienVfic Data Model requirements
• Complex relaVonships – ComposiVon • Stars in a galaxy • Nucleus within a cell – SpecializaVon • Stars, galaxies, dwarfs are sky objects; • Prokaryotes and eukaryotes are type of organisms – Ordering • Gene posiVon in a DNA strand • Complex aIributes – Sets, lists, objects • Organelles within a cell • Photometric parameters of a sky body • Exams within a hemogram report • Processes – DNA transcripVon – GravitaVonal lensing – Enzyme reacVon • FuncVonsBird applicaVon
Bird Name Region Desc Pic Song
Robin XXXXX yyyyy Migr North eagle mountain ♪ ♪ SELECT name,desc,song,pic FROM Bird WHERE INCLUDE (migr, country (´Italy´)); Which are the birds that includes Italy in their migraVon route? Why do we need to extend the RelaVonal Model? 7
Variety of Models
•
Different OO models
•
In this course :
–
Common principles found in OO DBMSs
–
Important alternaVves
•
Syntax close to ODMG
•
Object‐RelaVonal next (SQL3).
OR History
•
1980 – First OO DBMS
•
1992 – UNISQL/X – OO layer on top of relaVonal DBMS
(Oracle)
•
1992 – Open ODB HP (Odapter)
•
1993 – First ODMG Standard for OO DBMSs
•
1993 – Postgres (Univ. Berkeley) complete RO DBMS – UDT,
funcVons, Blobs, new query engine
– Sold to Montage ‐> Ilustra‐> Informix ‐> DB2 UDB – An open Source community developed under POstgreSQL•
1995 – IBM DB2 Universal server
•
1997 ‐ Oracle 7.3 Universal Server
•
1999 – Standard SQL3 for RelaVonal‐Object DBMS
9
Approaches
•
Principles and Models
–
2 approaches :
•
OO Programming languages
=> DBMS OO na%ve ‐
ODMG
standard v. 3.0
– ODMG, Object Data Management Group (2001 –end of acVviVes)
•
RelaVonal extensions
=> object‐relaVonal – standard
SQL 3
•
ODMG Data ManipulaVon Language : OQL
•
Object‐RelaVonal : PostgreSQL,Oracle
PerspecVve
File systems OODBMS RelaVonal DBMS OR DBMS Complex‐object Query capability M. Stonebreaker 1996 11OO approach
•
Set of methodologies and tools to develop structured
reusable sosware, by composiVon of independent
elements. [Khoshafian + Boral]
•
ObjecVve: programmers producVvity
– By means of : reuse•
Basic concepts
– Object encapsulaVons • Visible interface : operaVons (methods) • Hidden implementaVon : structure and code – inheritance•
OO Programming language
– Eiffel, Smalltalk, C++, Java … 12• Persistence
• Management of storage
• Data share
• Data integrity
• Security
• Query language
• logical / physical independence
• Development
• Complex structure
• Identity
• Encapsulation
• Class = factory
• Inheritance
• Reuse
• Classes library
DBMS
OOPL
DBMS OO
OO DBMS = OOPL + DBMS
13
Why a OO DBMS / OO PL?
It is a DBMS (beIer than PL):
•
Data persistence
•
Data transparency
•
DeclaraVve DML
– OpVmized by the DBMS
•
Data integrity
•
Security, concurrency, transacVon,..
14Why an OO DBMS / RDBMS?
Advantages over a RelaVonal DBMS :
•
Model, storage and access of complex structures
•
CompaVble with OO‐PL and OO app. modeling
•
Support to new data types (image, sound, XML,…)
•
versions, long transacVons
•
performances
15Differences among OODBMSs
•
Extension of DBMS approach
•
Different data model
•
Different programming languages (C++,
Smalltalk, Lisp …)
•
Level of coupling with PL
•
Performances
•
class library ± complete
•
other funcVons (versioning, schema
evoluVon, Vme, extensibility …)
16OODBMS ‐ Mandatory features
• It must be a DBMS containing the following faciliVes. – persistence, – secondary storage management, – concurrency, – recovery, – ad‐hoc query faciliVes. • It must be object‐oriented equipped with the following faciliVes. – complex objects, – object idenVty, – encapsulaVon, – types or classes, – inheritance, – overriding combined with late binding, – extensibility, – computaVonal completeness.17
Op%onal features
•
mulVple inheritance
•
type checking
•
inferencing
•
distribuVon
•
design transacVon
•
versions
Structure
19General View
SpecificaVon Structure Behavior Interface Class Instances (objects) ImplementaVon Struct Id 20Main Types
•
Class
– Specifies a schema for represenVng and operaVng
on objects;
– Structural component defines the schema;
– Behavioral component specifies operaVons;
– Instances are created and manipulated
conforming to their class definiVons;
– Set of instances also called an extent;
21
Class
Class Instances (extent) 22Interface
•
Specifies an abstract behavior for objects;
– Prototypes (or signature) of methods
definiVons;
•
Return type, method name, parameters, excepVon
– Example:
•
Boolean open(in Reference consumer );
•
Object getNext (in Reference consumer);
iterator boolean open(); Object getNext(); boolean close(); 23Structure elements
SpecificaVon Structure CollecVons List Bag Array Set Atomic float short string long Structured Time Date interval User Defined …IN ORACLE OR
3/28/12 Modelagem Dados e Workflow Cien8fico 24RelaVonal ‐ Simple Types
•
RelaVonal Model
–
AIributes of simple types;
–
SQL standard types: Integer, Character, Date,
Time etc..
–
RelaVon
•
name
•
AIribute definiVons – 1
stNF
•
Constraints (primary, foreign keys, simple domains,…)
•
Instances: set of tuples of RelaVon type
Complex Structure
•
OR abandons restricVons concerning First normal form;
•
AIributes are defined according to Domains that can be
complex;
– Row valued aIributes (composite columns); – CollecVon structures; – References;•
Domains are modeled through User Data Types (UDT)
Complex Structure
Course Title {Profs}
Name
Given at
Type Hours Weekdays
day Advanced
DB
Sppacapietra Undg. 108 Monday
Tuesday
Master 54 Monday
Conceptual
Model Sppac. PhD 54 Tuesday
Posit. Full Porto Vangenot Associ Assist. Full Full Wegmann Select d. day from c in course, p in c.profs, g in c.givenat, d in g.weekdays where c.Vtle= advancedDB and p.name= porto and g.type= master
Model for OR
Type Type Table aIribute of Extends as of methods ofType
•
Defines:
–
a data structure;
–
A set of operaVons
(methods);
•
Can be associated to:
–
An aIribute;
–
A table;
3/28/12 Modelagem Dados e Workflow Cien8fico 29 Cell .type string .DNA string String getType (); String getGene(int init, int end);UDT – ImplemenVng Complex Structure
•
User Defined Types
–
complex domain of values;
–
operaVons;
•
Can be used in aIribute definiVons and as
the type of tables;
–
Do not specify key (relaVons);
•
Each table implements a type.
Complex types
3/28/12 Modelagem Dados e Workflow Cien8fico 31 Cell .type string .DNA string .genes set of Gene String getType (); String getGene(int init, int end); Gene .name string .DNA string .GOTerm string Species string setGoTerm (String); String getDNA();UDT
3/28/12 Modelagem Dados e Workflow Cien8fico 32 Type Object ( a Class) Table (a set of Objects) Array (a list of objects)UDT Syntax
• CreaVng a user type: – Create Type nameOfType AS [object, table, varray] <aIribute and method declaraVon> • CreaVng Table of a user type – Create table nameOfTable OF nameOfType • Ex: – Create type address_type as object ( number number, street char(60), city char(40), code_postal char (6)) Create table Addresses OF address_type; • Type of aIributes Create table Student (id number(10) addr address_type, )Defining a type
•
Structure:
– AIributes
•
Simple types (SQL datatypes)
•
Complex types (other UDT, row, array)
•
References (point to types)
– Methods signature
Simple type aIributes
•
Refer to a property/quality value of an instance (tuple)
– DNA Strand – type string – Date of birth – type date (we consider it simple) – PaVent Name – type string (it maybe complex) – Telephone number – type number•
DisVnct type – a UDT defined based on a single built‐in data
type
– Create type shoe_size as integerComplex type aIributes
•
Structured informaVon:
– Address – {street, number, PObox, city}
•
CollecCons sets:
– Set of organelles of a cell
– Set of exams in a hemogram
•
CollecCons list: arrays
– List of nucleoVdes
Complex type aIribute
‐ examples
•
Structured
–
publishingInfo row (publisher string, year
decimal(4), ISBN decimal(11) )
•
CollecVons ‐ set
–
Create type Organelles as table of organelle
–
AIribute:
•
org Organelles
•
CollecVons – list (Arrays)
–
DNAStrand varray[2000] char(01)
Complex structures in Oracle
•structured :
create type item_type as object (itemnb numeric(3), producVd integer, qty numeric(3)) •collecVons:
set – (nested tables) – unordered and unbounded;
create type itemcollecVon_type as table of item_type; create type invoice_type as object (invoicenb integer, clientnb integer, total numeric(6,2), items itemcollecVon_type) •arrays: (varrays) ordered and bounded collecVons;
create type DNA_type as varray(2000) of char(01)CollecVons ‐ sets
•
Sets in tables:
–
create type itemcollecVon_type as table of
item_type;
create type invoice_type as object
(invoicenb integer,
total numeric(6,2),
items itemcollecCon_type);
Create table invoice of invoice_type
nested table items store as tabinvoice;
CollecVon type ‐ Array
•
List ‐ Array
– Specifies a mulVple occurrence of a certain type
– Fixed max size;
•
create type DNA_type as varray(2000) of
char(01)
ManipulaVng complex
structures ‐ insert
•
Create table item of item_type;
–
Insert into item values (item_type(1,10,100));
•
Create type invoice_type (nr integer,item
item_type)
•
Create table invoice of invoice_type;
–
Insert into invoice values(1,item_type(1,10,100));
ManipulaVng complex
structures ‐ insert
– create type itemcollecVon_type as table of item_type; create type invoice_type as object (invoicenb integer, total numeric(6,2), items itemcollecCon_type); – Create table invoice of invoice_type; – Insert into invoice values (1,10,itemcollecVon_type(item_type(10,100,1000),item_type(20,200, 2000),item_type(30,300,3000)))ManipulaVng complex
structures ‐ insert
•
Create type tel_type as object (numtel varchar(20));
•
Create type tels_type as varray(3) of tel_type;
•
Create type student_type as object
(id integer, address address_type,tels tels_type)
•
Create table student of student_type;
•
Insert into student values
(10,address_type(1, rue y , lausane ,1015),
tels_type(tel_type( 2222 ),tel_type( 3333 )))
Nested collecVons
Course Title {Profs}
Name Advanced DB Sppac. Conceptual Model Sppac. Posit. Full Porto Vangenot Assist. Assist. Full Full Wegmann Duplicate InformaVon
Nested versus new table
•
In m:n relaVonships
– RelaVonal Model implements a new relaVon
– Example:
•
Course, Professor and Course‐Professor
– Obtaining the number of professors that present
graduate courses:
•
Join Course with Course‐professor ‐> count the result
– In OR – Sets of professors in a course
•
Counts directly the number of professor in graduate
courses
Drawback of nested collecVon
•
Duplicated informaVon
–
The same professor informaVon appears with all
the courses he/she presents
•
BeIer soluVon
–
Represent related sets individually
•
Set of courses and Set of professors
–
Build pointer to each individual tuple
•
References
References ‐ PoinVng
Course Title Profs Name Advanced DB Sppac. Conceptual Model Posit. Fulll Vangenot Assist. Full Wegmann ref Porto Assist.References
•
In the Course example,
–
Prof informaVon is repeated for all courses
he/she presents;
–
In RelaVonal Model
•
Prof‐Course RelaVon
–
In OR
•
Prof‐Course RelaVon
•
Sets of Prof; Sets of Courses
–
References ‐> Pointers to tuples of a certain
Type
Reference Type
•
Reference – pointer to an object
•
Establish a physical associaVon between
instances of two types;
– customer ref customer_type
•
an aIribute of type REF includes a OID value
and informaVon on the table it is defined in;
•
Reference defined in types, not in tables;
Using ref
• create type address_type as object (street varchar(150), city varchar(100),zip_code char(10)); • create type customer_type as object (cusVd integer, cust_name varchar(100), custaddress address_type) • create type invoice_type as object (invoicenr numeric, customer ref customer_type); • Create table invoice of invoice_type (primary key (invoicenr)) • Create table customer of customer_type (primary key (cusVd))Accessing reference
•
InserVng
– insert into customer values (1,'Charles Trovalski',
address_type('rue x', 'lausanne', '1015'));
– insert into invoice
select 10,100, ref(c) from customer c where
cusVd=1
Reference scope
•
A type may be implemented by different
tables;
–
As references point to types, all implemenVng
tables would be affected by the reference;
–
To localize a reference to a certain
implementaVon of a type, use the SCOPE IS
clause;
•
aAribute scope is tablename
Reference scope
•
Create table carSell of invoice_type;
•
Create table motoSell of invoice_type;
•
Create type assurance_moto_type as object
(motomodel char(30),
seller numeric(4),
invoice ref invoice_type);
Create table assurance_moto of assurance_moto_type
(invoice scope is motosell)
Reference X Foreign key
Reference
Foreign key
Physical - OID
Logical-attribute value
Specified in type definition Specified in table definition
Extension to SQL
SQL
Navigation on query
No navigation
No unique constraint
Unique constraint possible
(1-1)
Example of implementaVon 1‐n
• Create type prof_type • Create type subject_type as object (id numeric, numcredit numeric(2), resp ref prof_type); • Create type resp_subj_type as object (subject ref subject_type); • Create type collecVon_subj_type as table of resp_subj_type; • Create or replace type prof_type as object (id integer, name varchar(100), subjects collecVon_subj_type); • Create table professor of prof_type (primary key (id)) nested table subjects store as tabsubj; • Create table subject of subject_type (primary key (id));Professor Subject id numcredit