Transcript DATABASE SYSTEMS - 10p Course No. ??

1
UU - DIS - UDBL
DATABASE SYSTEMS - 10p
Course No. 2AD235
Spring 2002
A second course on development of
database systems
Kjell Orsborn
Uppsala Database Laboratory
Department of Information Technology, Uppsala University,
Uppsala, Sweden
Kjell Orsborn
2016-03-29
2
UU - DIS - UDBL
Introduction to Object-Oriented and ObjectRelational Databases
Kjell Orsborn
Uppsala Database Laboratory,
Department of Information Technology,
Uppsala University, Uppsala, Sweden
Kjell Orsborn
2016-03-29
3
UU - DIS - UDBL
Talk Outline
• Some General DBMS Concepts
– limitations of traditional DBMSs
•
•
•
•
•
History of DBMSs
Object-Oriented Databases
Object-Relational Databases
Differences
Standards
Kjell Orsborn
2016-03-29
4
UU - DIS - UDBL
Database Design
•
Database Design:
–
•
Schema:
–
•
A set of building blocks (data abstractions) to represent reality.
Each DBMS supports one Data Model.
The most common one is the Relational Data Model where data is represented in tables.
NOTICE: E.g. CAD people use the word ‘Data Model’ instead of ‘Schema’
Conceptual Data Model:
–
•
A description of properties of data in a database (i.e. a meta-database)
Data Model:
–
•
How to translate subset of reality into data representations in the database .
A very high level and user-oriented data model (often graphical).
CDM not necessarily representable in DBMS or computer!
Most common CDM is Entity-Relationship (ER) data model.
But also Extended ER models are common
Conceptual Schema Design
–
Kjell Orsborn
Produce a DBMS independent Conceptual Schema in the Conceptual Data Model
2016-03-29
5
UU - DIS - UDBL
Extended Entity-Relationship Diagram
Kjell Orsborn
2016-03-29
6
UU - DIS - UDBL
Logical Database Design
• Logical Database Design:
– How to translate Conceptual Schemas in the conceptual data model
(e.g. ER-schemas)
to a Conceptual Schema in the DBMS data model (e.g relational
tables)
• Logical Database Design for the Relational Data Model
includes:
– Key Identification: What attributes are used to identify rows in a
table?
– Normalization: Table decomposition to solve update problems,
normal forms
• PROBLEM: Semantics may disappear or be blurred when
data is translated to less expressive data model and
normalized
Kjell Orsborn
2016-03-29
7
UU - DIS - UDBL
Physical Database Design
•
Physical Database Design:
– Physical representation of the database schema optimized with respect to the access
patterns of critical applications.
•
Indexes:
– permit fast matching of records in table satisfying certain search conditions.
– The index structures are closely related to the internal physical representations of the
DBMS.
– Indexes can speed up execution considerably, as well as storing data usually accessed
together in the same table.
– Indexes permit the database to scale, i.e. the access times grow much slower than the
database size.
•
PROBLEM: New applications may require data and index structures that are not
supported by the DBMS. (e.g. calendars, numerical data, geographical data, data
exchange formats, etc.)
Kjell Orsborn
2016-03-29
8
UU - DIS - UDBL
The ANSI/SPARC three-schema Architecture
• Achieves Data Independence
Kjell Orsborn
2016-03-29
9
UU - DIS - UDBL
Data Independence
•
External View:
–
•
Data Independence:
–
•
The capability to change conceptual schema without having to change applications and interfaces to
views.
E.g.: create a new table, add a column to a table, or split a table into two tables
Physical Data Independence:
–
•
The capability to change the database schema without having to change applications.
NOTE: Data Independence is very important since databases continuously change!
Logical Data Independence:
–
•
Mapping Conceptual Schema --> subset of the database for a particular (group of) users.
The capability to change the physical schema without having to change applications and logical schema
(E.g. add/drop indexes, change data formats, etc.)
PROBLEM: Application programs still often have data dependencies, e.g. to map relational
database tables to application object structures.
Kjell Orsborn
2016-03-29
10
UU - DIS - UDBL
Database Manipulation
•
Query Language:
–
•
Typical query language operations are:
–
–
–
–
•
Searching for records fulfilling certain selection conditions
Iterating over entire tables applying update operations
Schema definition and evolution operators
Object-Oriented Databases have other operations such as create and delete objects
The user directly or indirectly calls SQL in the following ways:
–
–
–
•
Originally a QL could only specify more or less complex database searches.
Now the query language (SQL) is a general language for interactions with the database.
By running an interpreter that interactively executes SQL commands
By running an application program that contains calls to Embedded SQL
By running a graphical Database Browser to navigate through the database. (The browser internally
calls embedded SQL)
PROBLEM: Would like to be able to customize and extend query language for different
application areas.
Kjell Orsborn
2016-03-29
11
UU - DIS - UDBL
•
Views
View:
– A view is a mapping from the Conceptual Schema to a subset of the database as seen by a
particular (group of) users.
• SQL is a closed query language that maps tables into tables => SQL allows very general views
(derived tables) to be defined as single queries
•
Views provide:
– External schema
• Each user is given a set of views that map to relevant parts of the database
– Logical data independence
• When schema is modified views mapping new to old schema can be defined
– Encapsulation
• Views hide details of physical table structure
– Authorization
• The DBA can assign different authorization privileges to views of
different users
•
NOTICE: Views provide logical data independence.
Kjell Orsborn
2016-03-29
12
UU - DIS - UDBL
Evolution of Database Technology
1960
Hierarchical
(IMS)
1970
Network model
(CODASYL)
1980
Relational model
(e.g. ORACLE)
1990
1st Generation OODB
(e.g. Objectivity)
1997
Object Realtional DBMS
(e.g. SQL99)
Trees
Complex data structures
Tables
OO data structures
Object model
{}
Kjell Orsborn
2016-03-29
13
UU - DIS - UDBL
New DBMS Applications (for OODBMSs)
• Classical DBMS:
– Administrative applications, e.g. Banking (ATMs)
• Properties:
–
–
–
–
Very large structured data volumes
Very many small Transactions On-line (High transaction rates)
Occasional batch programs
High Security/Consistency
• New Needs for Engineering, Scientific databases, etc.:
–
–
–
–
–
Kjell Orsborn
Extensibility (on all levels)
Better performance
Expressability (e.g. Object-Orientation needed)
Tight PL Interfaces
Long transactions (work in ‘sand box’)
2016-03-29
14
UU - DIS - UDBL
New DBMS Applications (cont. ...)
Problem areas:
• CASE Computer Aided Software Engineering
• CAD Computer Aided Design
• CAM Computer Aided Manufacturing
• OIS Office Information Systems
• Multi-media databases
• Scientific Applications
• Hypertext databases (WWW)
Kjell Orsborn
2016-03-29
15
UU - DIS - UDBL
Object-Oriented Databases
Problems with using RDBMSs for OO applications
• Complex mapping from OO conceptual model to relations
• Complex mapping => complex programs and queries
• Complex programs => maintenance problems
• Complex programs => reliability problems
• Complex queries => database query optimizer may be very
slow
• Application vulnerable to schema changes
• Performance
Kjell Orsborn
2016-03-29
16
UU - DIS - UDBL
Object-Oriented Databases
• First generation ODBs
• Extend OO programming language with DBMS primitives
–
–
–
–
E.g. C++, SmallTalk, Java
Allow persistent data structures in C++ programs
Navigate through database using C++ primitives (as CODASYL)
An object store for C++, SmallTalk, Java, etc.
• Several products out, e.g.:
– Objectivity, Versant, ObjectStore, Gemstone, Poet , PJama, O2
Kjell Orsborn
2016-03-29
17
UU - DIS - UDBL
Object-Oriented Databases
•
Pros and cons:
+Long transactions with checkin/checkout model (sand box)
+Always same language (C++)
+High efficiency (but only for checked-out data)
- Primitive ‘query languages’ (now OQL standard proposed)
- No methods in database (all code executes in client, no stored procedures)
- Rudimentary data independence (no views)
- Limited concurrency
- Unsafe, database may crash
- Slow for many small transactions (e.g. ATM applications)
- May require extensive C++ or Java knowledge
Kjell Orsborn
2016-03-29
18
UU - DIS - UDBL
Object-Oriented Databases
Persistence
• Integrated with programming language:
– E.g. C++ with persistent objects
class PERSON { ... };
....
{PERSON P; // Local within block... }
static PERSON p; // Local for execution
persistent PERSON p; // Exists between program executions
•
Pointer swizzling:
–
–
–
–
–
Kjell Orsborn
Automatic conversion from disk addresses to MM addresses
References to data structures on disk (OIDs) look like regular C++ pointers!
Navigational access style.
Fast when database cached in main-memory of client!
Preprocessed by OODBMS for convenient extension of C++
2016-03-29
19
UU - DIS - UDBL
Object-Relational Databases
• Object-Relational DBMSs
• Idea:
–
–
–
–
–
Extend on RDBMS functionality
Customized (abstract) data types
Customized index structures
Customized query optimizers
Use declarative query languages, SQL extension (SQL99)
• Extensible DBMS
– Object-orientation for abstract data types
– Data blades (data cartridges, data extenders) are database server
‘plug-ins’ that provide:
• User definable index structures
• Cost hints and re-write rules for the query optimizer
Kjell Orsborn
2016-03-29
20
UU - DIS - UDBL
Object-Relational Databases
•
•
•
Pros and cons:
+Migration path to SQL
+Views, logical data independence possible
+Programming language independence
+Full DBMS functionality
+Stored procedures, triggers, constraints
+High transaction performance by avoiding data shipping
+Easy to use declarative queries
- Overkill for application needing just a C++ object store
- Performance may suffer compared to OODBs for applications needing just an
object store
- May be very difficult to extend index structures and query optimizers
Research prototypes: Iris (HP), Postgres (Berkeley), Starburst (IBM)
Products: Informix, OpenODB (Odapter), DB2
NOTE: On-going evolution of 1st gen. products to become more Object-Relational
Kjell Orsborn
2016-03-29
21
UU - DIS - UDBL
Object-Oriented Databases
• Literature:
– M.Stonebraker: Object-relational DBMSs - The next great wave, MorganKaufmann 1996
• Object-Oriented Manifestos
– First generation ODB Manifesto: State-of-the-art OODBs anno 1990
• Atkinsson et al: The OO Database System Manifesto in W.Kim, J-M. Nicolas,
S.Nishio (eds): 1st Intl. Conf. on Deductive and OO Databases
Early O2
– Object-relational DB Manifesto: Requirements for next generation DBMSs
anno 1990
• Stonebraker et. al.: Third-generation Data Base System Manifesto
SIGMOD Record, Vol. 20, No. 4, Dec.1991.
Kjell Orsborn
2016-03-29
22
UU - DIS - UDBL
Object-Oriented Databases
The Manifestos:
• Object identity
– E.g. for structure sharing:
Unique OIDs maintained by DBMS
E.g. Parent(:tore) = :ulla, Parent(:kalle)=:ulla
•
Complex objects
– Not only tables, numbers, strings but
sets, bags, lists, and arrays, i.e. non-1NF relations
– E.g. Courses(:tore) = {:c1,:c2,:c3}
•
Encapsulation
– Simplicity
Modularity
Security
Kjell Orsborn
2016-03-29
23
UU - DIS - UDBL
Object-Oriented Databases (manifesto cont. ...)
•
Extensibility
1. User-defined data types and operations on these new datatypes
•
•
•
e.g. datatypes: create type Person, create type Timepoint
e.g. operations. name(:tore), :t2 - :t1, :t2 > :t1, etc.
Both OO and OR allow abstract datatypes through object-orientation
2. Extensions of physical representations (including indexes) and corresponding
operations
•
•
OO/OR databases allow extensions of physical representations
OR databases allow definition of new indexes
3. Extensions of query processor with optimization algorithms and cost models
•
•
OR databases allow extensions of query processing
Class Hierarchies as modelling tool (both OO/OR)
–
Classification
•
–
–
e.g. Student subtype of Person
Shared properties
Specialization
•
Kjell Orsborn
Student subtype of Person with extra attributes University, Classes, …
2016-03-29
24
UU - DIS - UDBL
Object-Oriented Databases (manifesto cont. ...)
• Computational completeness
– OR databases: Turing complete ‘query’ language: SQL99 code executes on server
– OO databases: C++/Java code with embedded OQL statements executes in client
(web server)
• Persistence
– OO databases: transparent access to persistent object by swizzling
– OR databases: embedded queries to access persistent objects
• Secondary storage management
– OR databases: indexes can be implemented by user (difficult!)
• Concurrency
– OO databases: good support for long transactions
– OR database: good support for short transactions
• Ad hoc query facility
– OO Databases: weak
– OR Databases: very strong
Kjell Orsborn
2016-03-29
25
UU - DIS - UDBL
Object-Oriented Databases (manifesto cont. ...)
• Data independence
– OO Databases: weak
– OR Databases: strong
• Views
– Important for data independence
– Query language required
– Only in OR databases!
• Schema evolution
– Relational DBs have it!
– Fully supported in OR databases, primitive in OO databases
Kjell Orsborn
2016-03-29
26
UU - DIS - UDBL
Object Database Standards
• Object-Oriented DBMS Standard
– The ODMG standard proposal:
• R. Cattell, Ed.: The ODMG-93 Standard for Object Databases, MorganKaufmann Publishers, San Mateo, California, 1993.
– Includes an Object Data Model
– Object Query Language: OQL (different model than SQL99)
• Object-Relational DBMS Standards
– The SQL99 (SQL3) standard proposal:
• ISO-Final Draft International Standard (FDIS):
ISO/IEC FDIS 9075-2 Database Language SQL
–
–
–
–
–
Kjell Orsborn
Very large (>1000 pages)
SQL-92 is subset
Much more than object-orientation included
Triggers, procedural language, OO, error handling, etc.
Certain parts, e.g. standards for procedures, error handling, triggers,
already being included in the new SQL-99 standard.
2016-03-29
27
UU - DIS - UDBL
Data Exchange Formats
Purpose:
• Standardized formats for sending data between systems
– examples: STEP/EXPRESS, PDF, HTML, XML, VRML, MIDI, MP3,
etc.
• Engineering domain standard: STEP (standard for exchange of product
data)
– STEP is an industry wide ISO standard for exchange of mainly
engineering (CAx etc.) data
– separates meta-data (schema) and data as for databases
– EXPRESS is data model in database terms: i.e. it is the language in which
to define the schema.
– STEP models are standardized schemas for different engineering
application areas, e.g. AP209
– The exchanged data follows specialized STEP schemas,
e.g. PART 21 most common (XML based too, PART 29)
– CAx vendors normally not able to handle EXPRESS schemas
– Only PART 29 files following a specific schema, e.g. AP 209
Kjell Orsborn
2016-03-29
28
UU - DIS - UDBL
Data Exchange Formats
• The STEP/EXPRESS and database community sometimes use the
same terminology with different meanings:
• Data model:
– database world: schema language (i.e. EXPRESS is a data model)
– STEP/EXPRESS world: here a particular schema definition written in
EXPRESS
– We therefore avoid the word data model to minimize confusion
• Multi-level schema architecture:
– database world: external - conceptual - internal schemas
– STEP/EXPRESS world:
• Application protocol, AP (c.f. external schema)
• Integrated resources, IR (c.f. conceptual schema)
Kjell Orsborn
2016-03-29
29
UU - DIS - UDBL
Data Exchange Formats
• The XML language
• Extension of HTML to be able to define own tags in web documents,
– for example:
<polygon>
<line><start>1.2 1.3</start>
</end>2.1 3.4</end>
</line>
<line><start>2.1 3.4</start>
</end>4.6 4.2</end>
</line>
</polygon>
• Can also define DTD which is grammar for allowed tags in the
documents referencing it
• DTDs are more or less well specified schemas
• On-going work to define real schema language for XML: SMLSchema
• XML not object-oriented - only nested structures
Kjell Orsborn
2016-03-29
30
UU - DIS - UDBL
Introduction to AMOS II and AMOSQL
Kjell Orsborn
Uppsala Database Laboratory,
Department of Information Technology,
Uppsala University, Uppsala, Sweden
Kjell Orsborn
2016-03-29
31
UU - DIS - UDBL
Iris/OpenODB/Odapter/AMOS II Object-Relational DBMS
IRIS
• 1st Object-Relational DBMS: Iris research prototype developed in Database
Technology Department of HP Laboratories
• Iris’ query language OSQL is a functional query language
• OpenODB/Odapter is the HP product based on Iris
AMOS II
• AMOS II developed at UDBL but has its roots in Iris
• AMOS II runs on PCs under Windows NT/2000 and Solaris
• AMOS II uses query language AMOSQL
• AMOS II system is a fast main-memory DBMS
• AMOS II has single user or optional client-server configuration
• The object part of SQL99 is close to AMOSQL
• Mediator facilities: AMOS II is also a multi-database (mediator) system for
integrationg data from other databases
Kjell Orsborn
2016-03-29
32
UU - DIS - UDBL
AMOS II / Iris Data Model
• Basic elements in the AMOS II data model
Kjell Orsborn
2016-03-29
33
UU - DIS - UDBL
AMOS II Data Model
Objects:
• Atomic entities (no attributes)
• Belong to one or more types where one type is the most specific type
• Regard database as set of objects
• Built-in atomic types, literals:
– String, Integer, Real, Boolean
•
Collection types:
– Bag, Vector
•
Surrogate types:
– objects have unique object identifiers (OIDs)
– explicit creation and deletion
– DBMS manages OIDs
AMOSQL example:
–
Kjell Orsborn
create person instances :tore;
2016-03-29
34
UU - DIS - UDBL
AMOS II Data Model
Types:
• Classification of objects
– groups of OIDs belong to different types
•
•
Multiple inheritance supported
Organized in a type/subtype Directed Acyclic Graph
– defines that OIDs of one type is a subset of OIDs of other types
•
Types and functions are objects too
– of types “type” and “function”
•
Part of the AMOS II type hierarchy:
Kjell Orsborn
2016-03-29
35
UU - DIS - UDBL
AMOS II Data Model
Types continued…:
• Every object is an instance of at least one type
• A type set is associated with each OID
• Each OID has one most specific type
• Each surrogate type has an extent which is the set of objects having that type in
its type set.
• System understands subtype/supertype relationships
• Objects of user-defined types are instances of type Type and subtypes of
UserObject
• User defined objects always contains class UserObject in its type set
•
Object types may change dynamically (roles)
Kjell Orsborn
2016-03-29
36
UU - DIS - UDBL
AMOS II Data Model
Functions:
• Define semantics of objects:
–
–
–
–
properties of objects
relationships among objects
views on objects
stored procedures for objects
•
Functions are instances of type Function
•
•
More than one argument allowed
Bag valued results allowed, e.g. Parents
•
•
Multiple valued results allowed
Sets of multiple tuple valued results most general
Kjell Orsborn
2016-03-29
37
UU - DIS - UDBL
AMOS II Data Model
•
•
A function has two parts:
1) signature:
–
–
name and types or arguments and results
examples:
name(person p) -> charstring n
name(department d) -> charstring n
dept(employee e) -> department d
plus(number x, number y) ->number r
children(person m, person f) -> bag of person c
marriages(person p) -> bag of <Person s, Integer year>
•
2) implementation:
–
–
•
specifies how to compute outputs from valid inputs
non-procedural specifications, except for stored procedures
A function also contains an extent, i.e. a set of mappings from argument(s) to
result(s)
–
Kjell Orsborn
for example:
name(:tore) = ‘Tore’
name(:d1) = ‘Toys’
dept(:tore) = :d1
plus(1,2) = 3 or (1+2 = 3) Indefinite extent!
children(:tore,:ulla) = {:karl,:oskar}
marriages(:tore) = {<:eva, 1971>,<:ulla,1981>}
2016-03-29
38
UU - DIS - UDBL
AMOS II Data Model
AMOSQL has four kinds of functions:
• 1) stored functions (c.f. relational tables, object attributes)
– values stored explicitly in database
•
2) derived functions (c.f. relational views, object methods)
– defined in terms of queries and other functions using AMOSQL
– compiled and optimized by Amos when defined for later use
•
3) database procedures (c.f. stored procedures, object methods)
– for procedural computations over the database
•
4) foreign functions (c.f. object methods)
– escape to programming language (Java, C, or Lisp) e.g. for foreign database access
•
Functions can also be overloaded:
– overloaded functions have several different definition depending on the types of their
arguments and results.
Kjell Orsborn
2016-03-29
39
UU - DIS - UDBL
AMOSQL language - schema definition and
manipulation
• Creating types:
–
–
–
–
Kjell Orsborn
create
create
create
create
type
type
type
type
Person;
Student under Person;
Instructor under Person;
TAssistant under Student, Instructor;
2016-03-29
40
UU - DIS - UDBL
AMOSQL language - schema manipulation
•
Delete a type:
–
delete type Person;
– referential integrity maintained
– types Person, Student, Instructor and TAssistent also deleted
•
Create functions:
–
–
–
–
–
create
create
create
create
create
function
function
function
function
function
name (Person p) -> Charstring nm as stored;
name (Course) -> Charstring as stored;
teaches(Instructor) -> bag of Course as stored;
enrolled(Student) -> bag of Course as stored;
instructors(Course c) -> Instructor i as
select i where teaches(i) = c;
– The instructors function is the inverse of teaches
Kjell Orsborn
2016-03-29
41
UU - DIS - UDBL
AMOSQL language - schema manipulation
•
Delete functions:
– delete function teaches;
– referential integrity maintained.
• e.g. function instructors also deleted
•
•
Defining type and attributes:
–
create type Person properties
(name Charstring,
birthyear Integer,
hobby Charstring);
–
name, birthyear, hobby are defined together with type Person
Above equivalent to:
–
Kjell Orsborn
create
create
create
create
type Person;
function name(Person) -> Charstring as stored;
function birthyear(Person) -> Integer as stored;
function hobby(Person) -> Charstring as stored;
2016-03-29
42
UU - DIS - UDBL
AMOSQL language - schema manipulation
•
Example of inherited properties:
–
–
–
•
create type Person properties
(name Charstring key,
age Integer,
spouse Person);
create type Employee under Person properties
(dept Department);
Employee will have functions (attributes) name, age, spouse, dept
Can easily extend with new functions:
–
Kjell Orsborn
create function phone(Person) -> Charstring as stored;
2016-03-29
43
UU - DIS - UDBL
AMOSQL language - schema manipulation
•
Modeling relationships with cardinality constraints
–
–
•
Modeling properties of relationships by multi-argument stored functions:
–
•
create function enrolled(Student e nonkey) -> Course c nonkey as
stored;
create function teaches(Instructor i key) -> Course c nonkey as
stored;
create function score(Student,Course) -> Integer s as stored;
Modeling properties of relationships by multi-argument derived functions:
–
Kjell Orsborn
create function instructors(Student s, Course c) -> Teacher t as
select t where teaches(t) = c and enrolled(s) = c;
2016-03-29
44
UU - DIS - UDBL
AMOSQL language - data definition and
manipulation
•
Instance creation:
–
create Person(name, birthyear) instances
:risch (’T.J.M. Risch’, 1949),
:ketabchi (’M.A. Ketabchi’, 1950);
– equivalent formulation:
create Person instances :ketabchi, :risch;
set name(:risch) = ’T.J.M. Risch’;
set birthyear(:risch) = 1949;
set name(:ketabchi)= ’M.A. Ketabchi’;
set birthyear(:ketabchi)=1950;
•
Instance deletion:
–
Kjell Orsborn
delete :risch;
delete :ketabchi;
2016-03-29
45
UU - DIS - UDBL
AMOSQL language - data manipulation
•
•
Calling functions:
–
name(:risch);
’T.J.M. Risch’
–
equivalent formulation:
select name(:risch);
’T.J.M. Risch’
Adding elements to bag-valued functions:
–
Kjell Orsborn
add hobbies(:risch) = ‘Painting’;
add hobbies(:risch) = ‘Fishing’;
add hobbies(:risch) = ‘Sailing’;
hobbies(:risch);
‘Painting’
‘Fishing’
‘Sailing’
2016-03-29
46
UU - DIS - UDBL
AMOSQL language - data definition and
manipulation
•
Removing elements from set-valued functions:
–
•
Adding type to object:
–
•
remove hobbies(:risch) = ‘Fishing’;
hobbies(:risch);
‘Painting’
‘Sailing’
add type Teacher to :risch;
set teaches(:risch)= :math;
Removing type from object:
–
remove type Teacher from :risch;
teaches(:risch);
Error: Function teaches not defined for object
– This will also implicitly do
remove teaches(:risch) = :math;
Good for database evolution.
Kjell Orsborn
2016-03-29
47
UU - DIS - UDBL
AMOSQL queries
•
•
AMOSQL power: relationally complete and more
General format:
–
•
Example:
–
•
name(parents(friends(:risch)));
More SQLish:
–
•
select name(p), birthyear(p) from Person p;
Function composition simplifies queries that traverse function graph (Daplex
semantics):
–
•
select <expressions>
from <variable declarations>
where <predicate>;
select n
from Charstring n, Person par, Person fr
where n = name(par) and
par = parents(fr) and
fr = friends(:risch);
Works also for bag-valued arithmetic functions:
–
Kjell Orsborn
sqrt(sqrt(16.0));
2.0
-2.0
2016-03-29
48
UU - DIS - UDBL
AMOSQL examples
•
Examples of functions and ad hoc queries
create
create
create
create
function income(Person) -> Integer as stored;
function taxes(Person) -> Integer as stored;
function parents(Person) -> bag of Person as stored;
function netincome(Person p) -> Integer as
select income(p)-taxes(p);
create function sparents(Person c) -> Student as
select parents(c); /* Parent if parent is student;
bag of implicit for derived functions */
create function grandsparentsnetincomes(Person c) -> Integer as
select netincome(sparents(parents(c)));
select name(c)
from Person c
where grandsparentsnetincomes(c) > 100000 and income(c) <10000;
Kjell Orsborn
2016-03-29
49
UU - DIS - UDBL
AMOSQL aggregation functions
•
•
An aggregation function is a function that coerces some value to a single unit, a bag,
before it is called.
“bagged” arguments are not “distributed” as for other AMOSQL functions (no Daplex
semantics for aggregation functions)
–
•
Signature:
–
•
count(parents(friends(:risch)));
5
create function count(bag of Object) -> Integer as foreign ...;
Nested queries, local bags:
–
Kjell Orsborn
sum(select income(p) from Person p);
2016-03-29
50
UU - DIS - UDBL
AMOSQL quantification
•
•
Quantifiers
Existential and universal quantification over subqueries supported through two
aggregation operators:
–
–
create function notany(bag of object) -> boolean;
create function some(bag of object) -> boolean;
some tests if there exists some element in the bag
notany tests if there does not exist some element in the bag
•
Example:
–
Kjell Orsborn
create function maxincome(Dept d) -> Integer as
select income(p)
from Employee p
where dept(p) = d and
notany(select true from Employee q where income(q) > income(p));
2016-03-29
51
UU - DIS - UDBL
AMOSQL advanced updates
•
•
Set-oriented updates
Setting multiple function instances:
–
•
set salary(e) = s
from Employee e, Integer s
where s=salary(manager(e));
Removing values from set-valued functions:
Kjell Orsborn
–
remove friends(:risch) = f
from Person f
where age(f) > age(:risch);
–
remove friends(:risch) = p from Person p
where count(friends(p))>5;
2016-03-29
52
UU - DIS - UDBL
AMOSQL stored procedures
•
•
Database Procedures
For example to encapsulate database updates:
–
•
create function
as
begin
create person
set name(p) =
set income(p)
result p
end;
creperson(charstring nm, integer inc) -> person p
instances p;
nm;
= inc;
Optimized iterative update:
–
create function RemoveOldFriends(Person p) -> boolean as
begin
remove friends(p) = s
from Person s
where age(s) > age(p);
end;
RemoveOldFriends(:risch);
Kjell Orsborn
2016-03-29
53
UU - DIS - UDBL
AMOSQL sequences
Vectors (ordered sequences of objects)
• The datatype vector stores ordered sequences of objects of any type
• Vector declarations can be parameterized by declaring the type
Vector of <type> as for example:
–
–
create type Segment properties
(start Vector of Real,
stop Vector of Real);
create type Polygon properties
(segments Vector of Segment);
• Vector values have system provided constructors:
–
–
Kjell Orsborn
create Segment instances :s1, :S2;
set start(:s1)=Vector of Real(1.1, 2.3);
set stop(:s1)=Vector of Real(2.3, 4.6);
set start(:s2)=Vector of Real(2.8, 5.3);
create Polygon instances :p1;
set segments(:p1)=Vector of Segment(:s1, :s2);
2016-03-29
54
UU - DIS - UDBL
AMOSQL sequences
• Extended ER notation:
• Vector types can be used as any other type
• E.g. functions on sequences can be defined:
–
–
–
–
create function square(Number r)->Number as select r * r;
create function positive(Number r)->Number as select r where
r>=0;
create function length(Segment l) -> real
as select positive(sqrt(square(start(l)[0] - stop(l)[0]) +
square(start(l)[1] - stop(l)[1])));
create function length(Polygon p) -> real
as select sum(select length(segments(p)[i]) from Integer i);
• Vector queries:
–
–
–
Kjell Orsborn
length(:s1);
length(:p1);
select s from Segment s where length(s) > 1.34;
2016-03-29
55
UU - DIS - UDBL
AMOSQL schema queries
•
•
System data can be queried as any other database data as for example:
Find the names of the supertypes of EMPLOYEE:
–
•
Find the resolvents of an overloaded function:
–
•
name(resolvents(functionnamed(“AGE“)));
“DEPARTMENT.AGE->INTEGER”
“PERSON.AGE->INTEGER”
Find the types of the first argument of each resolvent of a function:
–
•
name(supertypes(typenamed(“EMPLOYEE”)));
“PERSON”
name(resolventtype(functionnamed(“AGE”)));
“DEPARTMENT”
“PERSON”
Find all functions whose single argument have type PERSON
–
Kjell Orsborn
attributes(typenamed(‘PERSON”));
“NAME”
“AGE”
2016-03-29
56
UU - DIS - UDBL
How to run AMOS II
• Install system on your PC by downloading it from
–
http://www.csd.uu.se/~udbl/amos/
• Run AMOS II with:
–
amos2
• User’s guide in:
–
http://www.csd.uu.se/~udbl/amos/doc/amos_users_guide.html
• Simple AMOS II tutorial in
–
Kjell Orsborn
http://www.csd.uu.se/~udbl/amos/doc/tut.pdf
2016-03-29
57
UU - DIS - UDBL
(AM)OSQL in Iris/OpenODB/AMOS II
• Summary:
•
•
•
•
•
•
•
(AM)OSQL provides flexible OR DBMS capabilities
Not hard wired object model, but dynamically extensible model
Extended subset of object part of SQL99
Very good support for ad hoc queries
Good schema modification operations
Object views
The key is the functional model of (AM)OSQL
Kjell Orsborn
2016-03-29