[PPT] - SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided PowerPoint Presentation

SLIDE 1

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44

SLIDE 2

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

1. SQL/Framework, contains information common to all parts of the standard

and describes the parts.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44

SLIDE 3

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

1. SQL/Framework, contains information common to all parts of the standard

and describes the parts.

2. SQL/Foundation, Data definition and data maniputlation syntax and

semantics, including SQL embedded in non-object programming languages.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44

SLIDE 4

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

1. SQL/Framework, contains information common to all parts of the standard

and describes the parts.

2. SQL/Foundation, Data definition and data maniputlation syntax and

semantics, including SQL embedded in non-object programming languages.

3. SQL/CLI (Call Level Inteface), the API for programming languages,

corresponds to ODBC.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44

SLIDE 5

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

1. SQL/Framework, contains information common to all parts of the standard

and describes the parts.

2. SQL/Foundation, Data definition and data maniputlation syntax and

semantics, including SQL embedded in non-object programming languages.

3. SQL/CLI (Call Level Inteface), the API for programming languages,

corresponds to ODBC.

4. SQL/PSM (Persistent Stored Modules), stored routines, external routines,

and procedural language extensions to SQL.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44

SLIDE 6

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

1. SQL/Framework, contains information common to all parts of the standard

and describes the parts.

2. SQL/Foundation, Data definition and data maniputlation syntax and

semantics, including SQL embedded in non-object programming languages.

3. SQL/CLI (Call Level Inteface), the API for programming languages,

corresponds to ODBC.

4. SQL/PSM (Persistent Stored Modules), stored routines, external routines,

and procedural language extensions to SQL.

5. SQL/MED (Management of External Data) provides extensions to SQL that

define foreign-data wrappers and datalink types to allow SQL to manage data that is accessible to, but not managed by, an SQL-based DBMS.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 1 / 44

SLIDE 7

SQL3 / SQL:2008 . . .

6. SQL/OLB (Object Language Bindings), defines the syntax and symantics of

SQLJ, which is SQL embedded in Java.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 2 / 44

SLIDE 8

SQL3 / SQL:2008 . . .

6. SQL/OLB (Object Language Bindings), defines the syntax and symantics of

SQLJ, which is SQL embedded in Java.

7. The SQL/MM (Multimedia), which extends SQL to deal with large, complex

and maybe streaming data, like video, audio and spatial data.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 2 / 44

SLIDE 9

SQL3 / SQL:2008 . . .

6. SQL/OLB (Object Language Bindings), defines the syntax and symantics of

SQLJ, which is SQL embedded in Java.

7. The SQL/MM (Multimedia), which extends SQL to deal with large, complex

and maybe streaming data, like video, audio and spatial data.

8. SQL/Schemata defines the Information Schema and Definition Schema,

providing a common set of tools to make SQL databases and objects self-describing.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 2 / 44

SLIDE 10

SQL3 / SQL:2008 . . .

6. SQL/OLB (Object Language Bindings), defines the syntax and symantics of

SQLJ, which is SQL embedded in Java.

7. The SQL/MM (Multimedia), which extends SQL to deal with large, complex

and maybe streaming data, like video, audio and spatial data.

8. SQL/Schemata defines the Information Schema and Definition Schema,

providing a common set of tools to make SQL databases and objects self-describing.

9. SQL/JRT (Java Routines and Types), specifies the ability to invoke static Java

methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL structured user-defined types.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 2 / 44

SLIDE 11

SQL3 / SQL:2008 . . .

6. SQL/OLB (Object Language Bindings), defines the syntax and symantics of

SQLJ, which is SQL embedded in Java.

7. The SQL/MM (Multimedia), which extends SQL to deal with large, complex

and maybe streaming data, like video, audio and spatial data.

8. SQL/Schemata defines the Information Schema and Definition Schema,

providing a common set of tools to make SQL databases and objects self-describing.

9. SQL/JRT (Java Routines and Types), specifies the ability to invoke static Java

methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL structured user-defined types.

10. SQL/XML, specifies SQL-based extensions for using XML in conjunction with

SQL.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 2 / 44

SLIDE 12

SQL3:2008 – row types

A row type, ”ROW TYPE”, is a sequence of pairs, name and data type, and corresponds to either a composite attribute in EER modelling or a row in a table.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 3 / 44

SLIDE 13

SQL3:2008 – row types

A row type, ”ROW TYPE”, is a sequence of pairs, name and data type, and corresponds to either a composite attribute in EER modelling or a row in a table. E.g.: create table person ( ssn char(11), name row (fname varchar(25), lname varchar(25)), address row (streetname varchar(25), streetno smallint, postalcode char(6), city varchar(25)));

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 3 / 44

SLIDE 14

SQL3:2008 – row types

A row type, ”ROW TYPE”, is a sequence of pairs, name and data type, and corresponds to either a composite attribute in EER modelling or a row in a table. E.g.: create table person ( ssn char(11), name row (fname varchar(25), lname varchar(25)), address row (streetname varchar(25), streetno smallint, postalcode char(6), city varchar(25))); insert into person values (’451112-0356’, row(’Serafim’,’Dahl’), row(’Blomsterv¨ agen’,12,’131 37’,’Nacka’));

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 3 / 44

SLIDE 15

SQL3:2008 – user defined types (UDT)

DISTINCT TYPE, is the simplest form. Used to semantically distinguish data

with the same underlying structure

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 4 / 44

SLIDE 16

SQL3:2008 – user defined types (UDT)

DISTINCT TYPE, is the simplest form. Used to semantically distinguish data

with the same underlying structure , e.g.: CREATE DISTINCT TYPE SocialSecurityNumber AS CHAR(11); CREATE DISTINCT TYPE EmployeeNumber AS CHAR(11);

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 4 / 44

SLIDE 17

SQL3:2008 – user defined types (UDT)

DISTINCT TYPE, is the simplest form. Used to semantically distinguish data

with the same underlying structure , e.g.: CREATE DISTINCT TYPE SocialSecurityNumber AS CHAR(11); CREATE DISTINCT TYPE EmployeeNumber AS CHAR(11); Note the distinction here between type and domain. A domain is a restriction

n type to clarify which values that may be stored in a table column. The

domain is in both cases CHAR(11).

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 4 / 44

SLIDE 18

SQL3:2008 – user defined types (UDT)

DISTINCT TYPE, is the simplest form. Used to semantically distinguish data

with the same underlying structure , e.g.: CREATE DISTINCT TYPE SocialSecurityNumber AS CHAR(11); CREATE DISTINCT TYPE EmployeeNumber AS CHAR(11); Note the distinction here between type and domain. A domain is a restriction

n type to clarify which values that may be stored in a table column. The

domain is in both cases CHAR(11).

A UDT consists, in the general case, of one or more attribute definitions, a

number (even zero) of routine declarations and a number (even zero) of

perator declarations.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 4 / 44

SLIDE 19

SQL3:2008 – user defined types (UDT)

DISTINCT TYPE, is the simplest form. Used to semantically distinguish data

with the same underlying structure , e.g.: CREATE DISTINCT TYPE SocialSecurityNumber AS CHAR(11); CREATE DISTINCT TYPE EmployeeNumber AS CHAR(11); Note the distinction here between type and domain. A domain is a restriction

n type to clarify which values that may be stored in a table column. The

domain is in both cases CHAR(11).

A UDT consists, in the general case, of one or more attribute definitions, a

number (even zero) of routine declarations and a number (even zero) of

perator declarations.
A UDT may be specified in so many ways that an example has to be enough.

Suppose that we want to represent persons:

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 4 / 44

SLIDE 20

SQL3:2008 – UDT . . .

CREATE TYPE PersonType AS ( ssn SocialSecurityNumber CHECK (alive(ssn)), firstname VARCHAR(15), lastname VARCHAR(15)) INSTANTIABLE NOT FINAL REF IS SYSTEM GENERATED INSTANCE METHOD age() RETURNS INTEGER, INSTANCE METHOD age(ssn SocialSecurityNumber) RETURNS PersonType; CREATE INSTANCE METHOD age() RETURNS INTEGER; FOR PersonType BEGIN RETURN /* code to calculate age */ END; CREATE INSTANCE METHOD age(ssn SocialSecurityNumber) RETURNS PersonType FOR PersonType BEGIN SELF.ssn = ssn RETURN SELF END;

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 5 / 44

SLIDE 21

SQL3:2008 – observers and mutators

For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

SLIDE 22

SQL3:2008 – observers and mutators

For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

SLIDE 23

SQL3:2008 – observers and mutators

For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator. The observer for firstname in the PersonType is: FUNCTION firstname (p PersonType) RETURNS VARCHAR(15) RETURN p.firstname;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

SLIDE 24

SQL3:2008 – observers and mutators

For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator. The observer for firstname in the PersonType is: FUNCTION firstname (p PersonType) RETURNS VARCHAR(15) RETURN p.firstname; and the corresponding mutator: FUNCTION firstname (p PersonType RESULT, newname VARCHAR(15)) RETURNS PersonType BEGIN p.firstname = newname; RETURN p; END;

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 6 / 44

SLIDE 25

SQL3:2008 – constructors

A constructor is created as well. The default constructor takes no arguments and sets all attributes to their defaults (which almost always is a bad idea).

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 7 / 44

SLIDE 26

SQL3:2008 – constructors

A constructor is created as well. The default constructor takes no arguments and sets all attributes to their defaults (which almost always is a bad idea). The user may (must??) redefine the default constructor and a reasonable constructor for PersonType might be: CREATE CONSTRUCTOR METHOD PersonType ( ssn SocialSecurityNumber, firstname VARCHAR(15), lastname VARCHAR(15)) RETURNS PersonType BEGIN SET SELF.ssn = ssn; SET SELF.firstname = firstname; SET SELF.lastname = lastname; RETURN SELF; END;

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 7 / 44

SLIDE 27

SQL3:2008 – User Defined Routine (UDR)

The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

SLIDE 28

SQL3:2008 – User Defined Routine (UDR)

The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

SLIDE 29

SQL3:2008 – User Defined Routine (UDR)

The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database A UDR can be defined as part of a UDT or as part of a schema. I may be written in almost any language, even directly in SQL(3)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

SLIDE 30

SQL3:2008 – User Defined Routine (UDR)

The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database A UDR can be defined as part of a UDT or as part of a schema. I may be written in almost any language, even directly in SQL(3) Procedures are invoked by the command CALL and can have parameters of type IN, OUT or INOUT as in ADA

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 8 / 44

SLIDE 31

SQL3:2008 – UDR . . .

CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

SLIDE 32

SQL3:2008 – UDR . . .

CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’thumbnail’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

SLIDE 33

SQL3:2008 – UDR . . .

CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’thumbnail’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’DETERMINISTIC’ means the the function always gives the same result for the same input (= no side effects)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

SLIDE 34

SQL3:2008 – UDR . . .

CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’thumbnail’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’DETERMINISTIC’ means the the function always gives the same result for the same input (= no side effects) ’NO SQL’ means the the function does not contain any SQL statements (= no embedded SQL in this case)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

SLIDE 35

SQL3:2008 – UDR . . .

CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’thumbnail’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’DETERMINISTIC’ means the the function always gives the same result for the same input (= no side effects) ’NO SQL’ means the the function does not contain any SQL statements (= no embedded SQL in this case) Other options: ’CONTAINS SQL’, ’READS SQL DATA’ and ’MODIFIES SQL DATA’

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 9 / 44

SLIDE 36

SQL3:2008 – UDR, polymorphism

Polymorphism in the shape of overloading exists

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

SLIDE 37

SQL3:2008 – UDR, polymorphism

Polymorphism in the shape of overloading exists but with restrictions:

methods may redefine the content of superclass methods with the same

name and the same signature,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

SLIDE 38

SQL3:2008 – UDR, polymorphism

Polymorphism in the shape of overloading exists but with restrictions:

methods may redefine the content of superclass methods with the same

name and the same signature,

two methods in the same schema may carry the same name as long as they

differ in signature,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

SLIDE 39

SQL3:2008 – UDR, polymorphism

Polymorphism in the shape of overloading exists but with restrictions:

methods may redefine the content of superclass methods with the same

name and the same signature,

two methods in the same schema may carry the same name as long as they

differ in signature,

Interesting: If no method is found that has the exact signature of the call (that

can be inferred from the call) SQL tries to find a “closest match” and attempts to invoke that method if one is found.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 10 / 44

SLIDE 40

SQL3:2008 – reference types and object identity

Just as in Postgres there is an implicit ’oid’ assigned to every row in every table. The objective is never to reuse an oid the ensure reference integrity. They are stored in the database, may be shared among databases, and constitute a direct reference to a specific row in a specific table in a database (or, if shared, a specific database in the actual server).

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 11 / 44

SLIDE 41

SQL3:2008 – sub- and supertypes

CREATE TYPE empType UNDER PersonType AS ( empNo employeeNumber, position VARCHAR(10) DEFAULT ’Assistant’, salary DECIMAL(8,2), department VARCHAR(10), INSTANCE METHOD isBoss () RETURNS BOOLEAN INSTANTIABLE NOT FINAL; CREATE INSTANCE METHOD isBoss () RETURNS BOOLEAN FOR empType BEGIN IF SELF.position=’Boss’ THEN RETURN True; ELSE RETURN False; ENDIF END;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 12 / 44

SLIDE 42

SQL3:2008 – sub- and supertypes

CREATE TYPE empType UNDER PersonType AS ( empNo employeeNumber, position VARCHAR(10) DEFAULT ’Assistant’, salary DECIMAL(8,2), department VARCHAR(10), INSTANCE METHOD isBoss () RETURNS BOOLEAN INSTANTIABLE NOT FINAL; CREATE INSTANCE METHOD isBoss () RETURNS BOOLEAN FOR empType BEGIN IF SELF.position=’Boss’ THEN RETURN True; ELSE RETURN False; ENDIF END;

A type has the type of all its supertypes and, thus, the type system corresponds closely to classes in OOP languages.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 12 / 44

SLIDE 43

SQL3:2008 – sub- and supertypes . . .

A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

SLIDE 44

SQL3:2008 – sub- and supertypes . . .

A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

SLIDE 45

SQL3:2008 – sub- and supertypes . . .

A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

SLIDE 46

SQL3:2008 – sub- and supertypes . . .

A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types. You may have to create a mix of table inheritance and OO-inheritance.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

SLIDE 47

SQL3:2008 – sub- and supertypes . . .

A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types. You may have to create a mix of table inheritance and OO-inheritance. The privileges to create types and subtypes are standard database privileges.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 13 / 44

SLIDE 48

SQL3:2008 – tables

To guarantee backwards compatibility with earlier SQL standards you must still use ’CREATE TABLE’ even if the table consists of just a UDT.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

SLIDE 49

SQL3:2008 – tables

To guarantee backwards compatibility with earlier SQL standards you must still use ’CREATE TABLE’ even if the table consists of just a UDT. Tables are still the only means for persistence

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

SLIDE 50

SQL3:2008 – tables

To guarantee backwards compatibility with earlier SQL standards you must still use ’CREATE TABLE’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

SLIDE 51

SQL3:2008 – tables

To guarantee backwards compatibility with earlier SQL standards you must still use ’CREATE TABLE’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g CREATE TABLE employee ( info empType, PRIMARY KEY (empNo));

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

SLIDE 52

SQL3:2008 – tables

To guarantee backwards compatibility with earlier SQL standards you must still use ’CREATE TABLE’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g CREATE TABLE employee ( info empType, PRIMARY KEY (empNo));

r

CREATE TABLE employee OF empType ( REF IS empID SYSTEM GENERATED, PRIMARY KEY (empNo));

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 14 / 44

SLIDE 53

SQL3:2008 – tables with references

CREATE TABLE order (

rderNo orderNumber,

item itemNo NOT NULL, quantity INTEGER NOT NULL, unit VARCHAR(5) NOT NULL, client SocialSecurityNumber NOT NULL, representative REF(empType) SCOPE employee REFERENCES ARE CHECKED ON DELETE CASCADE, PRIMARY KEY (orderNo));

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 15 / 44

SLIDE 54

SQL3:2008 – problems with table references

If we create a table ’client’ as CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn));

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 44

SLIDE 55

SQL3:2008 – problems with table references

If we create a table ’client’ as CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn)); we will spread information about persons over two tables (’empType’ is a subtype to ’PersonType’). If we use the method regularly then we are in troubles. . .

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 44

SLIDE 56

SQL3:2008 – problems with table references

If we create a table ’client’ as CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn)); we will spread information about persons over two tables (’empType’ is a subtype to ’PersonType’). If we use the method regularly then we are in troubles. . . It might be better to create the table differently: CREATE TABLE client UNDER person( deductionrate SMALLINT, latestPurchase DATE);

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 16 / 44

SLIDE 57

SQL3:2008 – problems with table references . . .

Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

SLIDE 58

SQL3:2008 – problems with table references . . .

Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive:

An insert is automatically distributed over tables and “supertables”.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

SLIDE 59

SQL3:2008 – problems with table references . . .

Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive:

An insert is automatically distributed over tables and “supertables”.
An update is automatically propagated to all involved rows in all involved

tables.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

SLIDE 60

SQL3:2008 – problems with table references . . .

Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive:

An insert is automatically distributed over tables and “supertables”.
An update is automatically propagated to all involved rows in all involved

tables.

A delete is also automatically proagated to all involved tables.
DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 17 / 44

SLIDE 61

SQL3:2008 – collection types, ’ARRAY’

So far (and it seems to stop at that), two collection types have been implemented, ’ARRAY’ and ’MULTISET’.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

SLIDE 62

SQL3:2008 – collection types, ’ARRAY’

So far (and it seems to stop at that), two collection types have been implemented, ’ARRAY’ and ’MULTISET’. ARRAY works approximately as in programming languages: Suppose that we add relative PersonType ARRAY to the ’employee’-table.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

SLIDE 63

SQL3:2008 – collection types, ’ARRAY’

So far (and it seems to stop at that), two collection types have been implemented, ’ARRAY’ and ’MULTISET’. ARRAY works approximately as in programming languages: Suppose that we add relative PersonType ARRAY to the ’employee’-table. Then we can find the ssn of “closest” and “most distant” realtive by SELECT relative[1].ssn as closestRelSsn, relative[CARDINALITY(relative)].ssn as mostDistantRelSsn FROM employee e WHERE e.ssn=’451112-0356’;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

SLIDE 64

SQL3:2008 – collection types, ’ARRAY’

So far (and it seems to stop at that), two collection types have been implemented, ’ARRAY’ and ’MULTISET’. ARRAY works approximately as in programming languages: Suppose that we add relative PersonType ARRAY to the ’employee’-table. Then we can find the ssn of “closest” and “most distant” realtive by SELECT relative[1].ssn as closestRelSsn, relative[CARDINALITY(relative)].ssn as mostDistantRelSsn FROM employee e WHERE e.ssn=’451112-0356’; All collection types have the ’CARDINALITY’ method that returns the actual number

f elements in a collection.

OBS that prior to SQL3 you had to indicate the max number of elements that your collection could contain.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 18 / 44

SLIDE 65

SQL3:2008 – collection types, ’MULTISET’

You may use relative MULTISET(PersonType) instead of ’ARRAY’ and ask for information about the relatives by SELECT n.ssn, n.firstname FROM employee e, UNNEST (e.relative) AS n(ssn,firstname, lastname) WHERE e.ssn = ’451112-0356’;

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 19 / 44

SLIDE 66

SQL3:2008 – collection types, ’MULTISET’ . . .

It would be possible to represent the nested relation from lecture 1 Document Title Author Date Search-words Lang code Lang DBTheory Lindqvist 940322 database 46 swedish Dahl relation normalform ODBMS Johnson 940312 persistent english Peterson transient

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 20 / 44

SLIDE 67

SQL3:2008 – collection types, ’MULTISET’ . . .

It would be possible to represent the nested relation from lecture 1 Document Title Author Date Search-words Lang code Lang DBTheory Lindqvist 940322 database 46 swedish Dahl relation normalform ODBMS Johnson 940312 persistent english Peterson transient with (according to MS): CREATE TABLE document ( title VARCHAR(50), author VARCHAR(50) MULTISET, date DATE, search VARCHAR(50) MULTISET, language ROW (code integer, language VARCHAR(20)) );

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 20 / 44

SLIDE 68

SQL3:2008 – Persistent Stored Modules (PSM)

PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

SLIDE 69

SQL3:2008 – Persistent Stored Modules (PSM)

PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions:

Declarations, e.g.:

DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss();

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

SLIDE 70

SQL3:2008 – Persistent Stored Modules (PSM)

PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions:

Declarations, e.g.:

DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss();

IF ... THEN ... ELSE ... END IF

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

SLIDE 71

SQL3:2008 – Persistent Stored Modules (PSM)

PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions:

Declarations, e.g.:

DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss();

IF ... THEN ... ELSE ... END IF
CASE lowercase(x)

WHEN ’a’ THEN SET a = 1; WHEN ’b’ THEN SET a = 2; SET b = 1; WHEN ’default THEN set b = 2; END CASE;

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 21 / 44

SLIDE 72

SQL3:2008 – Persistent Stored Modules (PSM) . . .

Repetition statements, where blocks of SQL statements can be executed and you

may loop over the result tables

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

SLIDE 73

SQL3:2008 – Persistent Stored Modules (PSM) . . .

Repetition statements, where blocks of SQL statements can be executed and you

may loop over the result tables

FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO

... END FOR;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

SLIDE 74

SQL3:2008 – Persistent Stored Modules (PSM) . . .

Repetition statements, where blocks of SQL statements can be executed and you

may loop over the result tables

FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO

... END FOR;

WHILE NOT b DO

... END WHILE;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

SLIDE 75

SQL3:2008 – Persistent Stored Modules (PSM) . . .

Repetition statements, where blocks of SQL statements can be executed and you

may loop over the result tables

FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO

... END FOR;

WHILE NOT b DO

... END WHILE;

REPEAT

... UNTIL NOT b END REPEAT;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

SLIDE 76

SQL3:2008 – Persistent Stored Modules (PSM) . . .

Repetition statements, where blocks of SQL statements can be executed and you

may loop over the result tables

FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO

... END FOR;

WHILE NOT b DO

... END WHILE;

REPEAT

... UNTIL NOT b END REPEAT;

A ’CALL’ statement to execute procedures and a ’RETURN’ statement which allows

returning the result of executing an SQL statement from a function invocation.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 22 / 44

SLIDE 77

SQL3:2008 – exceptions

SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

SLIDE 78

SQL3:2008 – exceptions

SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

SLIDE 79

SQL3:2008 – exceptions

SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action; DECLARE cond CONDITION [ FOR SQLSTATE sqlStatus ]

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

SLIDE 80

SQL3:2008 – exceptions

SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action; DECLARE cond CONDITION [ FOR SQLSTATE sqlStatus ] An error (exception) signal may be explicitly sent or resent. SIGNAL sqlStatus; RESIGNAL sqlStatus;

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 23 / 44

SLIDE 81

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SLIDE 82

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

check input data

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SLIDE 83

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

check input data
enforce complex integrity requirements

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SLIDE 84

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

check input data
enforce complex integrity requirements
message-sending

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SLIDE 85

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

check input data
enforce complex integrity requirements
message-sending
uphold transaction logs

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SLIDE 86

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

check input data
enforce complex integrity requirements
message-sending
uphold transaction logs
replication in distributed DBMS

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SLIDE 87

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

check input data
enforce complex integrity requirements
message-sending
uphold transaction logs
replication in distributed DBMS

CREATE TRIGGER triggername BEFORE | AFTER event ON table [ REFERENCING aliaslist ] [ FOR EACH { ROW | STATEMENT } ] [ WHEN triggercondition ] triggercode

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 24 / 44

SLIDE 88

SQL3:2008 – triggers, example

CREATE TRIGGER setnull-trigger BEFORE UPDATE ON employee REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN newrow.phone = ’’ SET newrow.phone = NULL

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 25 / 44

SLIDE 89

SQL3:2008 – triggers, example

CREATE TRIGGER setnull-trigger BEFORE UPDATE ON employee REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN newrow.phone = ’’ SET newrow.phone = NULL CREATE TRIGGER check-department BEFORE INSERT ON employee REFERENCING NEW ROW AS newrow WHEN newrow.dept NOT IN (SELECT name FROM department) SIGNAL avd-not-found(newrow.dept)

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 25 / 44

SLIDE 90

SQL3:2008 – triggers, example . . .

CREATE TRIGGER update-supply AFTER INSERT ON order REFERENCING NEW ROW AS newrow FOR EACH ROW UPDATE supply SET volume = volume - newrow.quantity WHERE item = newrow.item

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 26 / 44

SLIDE 91

ORDBMS – some systems

Three of the major DBMS providers – IBM, Informix and Oracle – have extended their RDBMS to ORDBMS or universal servers.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 27 / 44

SLIDE 92

ORDBMS – some systems

Three of the major DBMS providers – IBM, Informix and Oracle – have extended their RDBMS to ORDBMS or universal servers. They have – each with their own technique – extended their DBMS to handle pluggable modules that extends both its data storage possibilities, its type system, its optimizer and its functionality. Let us look att each of these three.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 27 / 44

SLIDE 93

ORDBMS, – DB2 Relational Extenders

The first IBM attempt to implement SQL3 was in DB2 version 5, where UDTs, UDFs, LOBs, triggers and stored procedures were introduced, some of them with IBM specific notation.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 28 / 44

SLIDE 94

ORDBMS, – DB2 Relational Extenders

The first IBM attempt to implement SQL3 was in DB2 version 5, where UDTs, UDFs, LOBs, triggers and stored procedures were introduced, some of them with IBM specific notation. In version 6 they introduced abstract data types and extendibility as described on the pervious slide. With the system you get four Relational extenders that can be used to create new extenders.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 28 / 44

SLIDE 95

ORDBMS, – DB2 Relational Extenders . . .

Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

SLIDE 96

ORDBMS, – DB2 Relational Extenders . . .

Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

SLIDE 97

ORDBMS, – DB2 Relational Extenders . . .

Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

SLIDE 98

ORDBMS, – DB2 Relational Extenders . . .

Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available. They are simple to install and activate.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

SLIDE 99

ORDBMS, – DB2 Relational Extenders . . .

Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available. They are simple to install and activate. Once plugged in and activated they act as a fully integral part of the DBMS

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 29 / 44

SLIDE 100

ORDBMS, – DB2 Relational Extenders . . .

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 30 / 44

SLIDE 101

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 102

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 103

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 104

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

access methodes,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 105

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

access methodes,
indexing methods,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 106

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

access methodes,
indexing methods,
optimizing methods,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 107

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

access methodes,
indexing methods,
optimizing methods,
functions to manipulate the type of data that is defined in the datablade,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 108

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

access methodes,
indexing methods,
optimizing methods,
functions to manipulate the type of data that is defined in the datablade,
rule definitions and

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

SLIDE 109

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

one ADT,
any number of ADF

,

access methodes,
indexing methods,
optimizing methods,
functions to manipulate the type of data that is defined in the datablade,
rule definitions and
restrictions that, once defined, allows the DBMS to cheack them the same way as any

built-in restrictions

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 31 / 44

SLIDE 110

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class values

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

SLIDE 111

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

stored,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

SLIDE 112

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

stored,
inspected (you may query all parts),

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

SLIDE 113

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

stored,
inspected (you may query all parts),
indexed,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

SLIDE 114

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

stored,
inspected (you may query all parts),
indexed,
sent to application programs,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

SLIDE 115

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

stored,
inspected (you may query all parts),
indexed,
sent to application programs,
sent as function parameters

et.c.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

SLIDE 116

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

stored,
inspected (you may query all parts),
indexed,
sent to application programs,
sent as function parameters

et.c.

and they may be defined in terms of already existing ADTs and/or predefined types.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 32 / 44

SLIDE 117

ORDBMS, – Informix DataBlades . . .

There are three basic ADTs in datablades:

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 33 / 44

SLIDE 118

ORDBMS, – Informix DataBlades . . .

There are three basic ADTs in datablades:

row-types

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 33 / 44

SLIDE 119

ORDBMS, – Informix DataBlades . . .

There are three basic ADTs in datablades:

row-types
distinct types

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 33 / 44

SLIDE 120

ORDBMS, – Informix DataBlades . . .

There are three basic ADTs in datablades:

row-types
distinct types
“opaque types” – that may be implemented in C, C++ or Java. With “opaque”

is meant that you cannot “look into” them. They are not part of the inspectable values.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 33 / 44

SLIDE 121

ORDBMS, – Informix DataBlades . . .

A UDF can be programmed in Informix Stored Procedure Language (SPL), C, C++, or Java.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 34 / 44

SLIDE 122

ORDBMS, – Informix DataBlades . . .

A UDF can be programmed in Informix Stored Procedure Language (SPL), C, C++, or Java. Those that are programemd in C, C++, or Java are compiled and stored in a shared object file or a DDL and when the function is invoked, the object code is linked into the running program and executed in the server runtime memory.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 34 / 44

SLIDE 123

ORDBMS, – Informix DataBlades . . .

A UDF can be programmed in Informix Stored Procedure Language (SPL), C, C++, or Java. Those that are programemd in C, C++, or Java are compiled and stored in a shared object file or a DDL and when the function is invoked, the object code is linked into the running program and executed in the server runtime memory. You may store your own runtime modules for each language in the DBMS in order to ensure that you use the appropriate runtime for each used language (the runtime against which compilation was performed)

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 34 / 44

SLIDE 124

ORDBMS, – Informix DataBlades . . .

You may implement your set of access methods, which is essentially a set of functions to

start index scanning

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 35 / 44

SLIDE 125

ORDBMS, – Informix DataBlades . . .

You may implement your set of access methods, which is essentially a set of functions to

start index scanning
get the next tuple

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 35 / 44

SLIDE 126

ORDBMS, – Informix DataBlades . . .

You may implement your set of access methods, which is essentially a set of functions to

start index scanning
get the next tuple
insert a tuple

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 35 / 44

SLIDE 127

ORDBMS, – Informix DataBlades . . .

You may implement your set of access methods, which is essentially a set of functions to

start index scanning
get the next tuple
insert a tuple
delete a tuple

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 35 / 44

SLIDE 128

ORDBMS, – Informix DataBlades . . .

You may implement your set of access methods, which is essentially a set of functions to

start index scanning
get the next tuple
insert a tuple
delete a tuple

This, turn means that you may design and install specialized indexes like R-trees to manage spatial data types.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 35 / 44

SLIDE 129

ORDBMS, – Informix DataBlades . . .

There is even an API that allows databalades to use other datablades.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 36 / 44

SLIDE 130

ORDBMS, – Informix DataBlades . . .

There is even an API that allows databalades to use other datablades. Datablades may be stored in the database (of course) and they may manipulae their own definition (reflection ??).

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 36 / 44

SLIDE 131

ORDBMS, – Informix DataBlades . . .

There is even an API that allows databalades to use other datablades. Datablades may be stored in the database (of course) and they may manipulae their own definition (reflection ??). This makes them data driven and simple to change, manage and . . . extend.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 36 / 44

SLIDE 132

ORDBMS, – Informix DataBlades . . .

There is even an API that allows databalades to use other datablades. Datablades may be stored in the database (of course) and they may manipulae their own definition (reflection ??). This makes them data driven and simple to change, manage and . . . extend. There is a development kit for datablade developers.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 36 / 44

SLIDE 133

ORDBMS, – Informix DataBlades . . .

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 37 / 44

SLIDE 134

ORDBMS, – Oracle Cartridges

Oracle Cartridge is a key component in Oracle Network Computing Architecture (NCA).

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 38 / 44

SLIDE 135

ORDBMS, – Oracle Cartridges

Oracle Cartridge is a key component in Oracle Network Computing Architecture (NCA). The other components are

client applications,

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 38 / 44

SLIDE 136

ORDBMS, – Oracle Cartridges

Oracle Cartridge is a key component in Oracle Network Computing Architecture (NCA). The other components are

client applications,
Oracle Universal Application Server and

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 38 / 44

SLIDE 137

ORDBMS, – Oracle Cartridges

Oracle Cartridge is a key component in Oracle Network Computing Architecture (NCA). The other components are

client applications,
Oracle Universal Application Server and
Oracle Universal Server.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 38 / 44

SLIDE 138

ORDBMS, – Oracle Cartridges

Oracle Cartridge is a key component in Oracle Network Computing Architecture (NCA). The other components are

client applications,
Oracle Universal Application Server and
Oracle Universal Server.

All communicate using open standard protocols internally and externally

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 38 / 44

SLIDE 139

ORDBMS, – Oracle Cartridges . . .

From a DB view Oracle Universal Server that provides extedible, scalable data storage facilities.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 39 / 44

SLIDE 140

ORDBMS, – Oracle Cartridges . . .

From a DB view Oracle Universal Server that provides extedible, scalable data storage facilities. A cartridge is a pluggable module that may be manipulated by other software

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 39 / 44

SLIDE 141

ORDBMS, – Oracle Cartridges . . .

From a DB view Oracle Universal Server that provides extedible, scalable data storage facilities. A cartridge is a pluggable module that may be manipulated by other software Its internal structure is defined in standard SQL3 and its interface uses the IMG IDL (as CORBA does)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 39 / 44

SLIDE 142

ORDBMS, – Oracle Cartridges . . .

From a DB view Oracle Universal Server that provides extedible, scalable data storage facilities. A cartridge is a pluggable module that may be manipulated by other software Its internal structure is defined in standard SQL3 and its interface uses the IMG IDL (as CORBA does) You may use C, C++, PL/SQL or Java when developing cartridges.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 39 / 44

SLIDE 143

ORDBMS, – Oracle Cartridges . . .

From a DB view Oracle Universal Server that provides extedible, scalable data storage facilities. A cartridge is a pluggable module that may be manipulated by other software Its internal structure is defined in standard SQL3 and its interface uses the IMG IDL (as CORBA does) You may use C, C++, PL/SQL or Java when developing cartridges. In the NCA architecture there are three kinds of cartridges (the classification shows their expected location in the NCA architecture)

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 39 / 44

SLIDE 144

ORDBMS, – Oracle Cartridges . . .

A data cartridge is managed in the DBMS and can access Oracles extensible

database services.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 40 / 44

SLIDE 145

ORDBMS, – Oracle Cartridges . . .

A data cartridge is managed in the DBMS and can access Oracles extensible

database services.

An application server cartridge may be managed either by the application

server or in the database server depending on hos tightly application logics are tied to data stored in DB. In the last version of Oracle that I looked into they were stored in the application server and communicated with DBMS via CORBA/IIOP .

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 40 / 44

SLIDE 146

ORDBMS, – Oracle Cartridges . . .

A data cartridge is managed in the DBMS and can access Oracles extensible

database services.

An application server cartridge may be managed either by the application

server or in the database server depending on hos tightly application logics are tied to data stored in DB. In the last version of Oracle that I looked into they were stored in the application server and communicated with DBMS via CORBA/IIOP .

A client cartrig runs on the client side using the usual standard API:s.
DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 40 / 44

SLIDE 147

ORDBMS, – Oracle Cartridges . . .

Cartridge-objects communicate via a special internal bus, the Inter-Cartridge Exchange (ICX).

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 41 / 44

SLIDE 148

ORDBMS, – Oracle Cartridges . . .

Cartridge-objects communicate via a special internal bus, the Inter-Cartridge Exchange (ICX). The ICX is implemented as a set of libraries and services that enable all the internal programs, in a set of programs that together form an application, to quite freely communicate with each others and surrounding servers and services.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 41 / 44

SLIDE 149

ORDBMS, – Oracle Cartridges . . .

Cartridge-objects communicate via a special internal bus, the Inter-Cartridge Exchange (ICX). The ICX is implemented as a set of libraries and services that enable all the internal programs, in a set of programs that together form an application, to quite freely communicate with each others and surrounding servers and services. The ICX uses HTTP and IIOP for communication and performs automatically certain conversions if necessary.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 41 / 44

SLIDE 150

ORDBMS, – Oracle Cartridges . . .

Cartridge-objects communicate via a special internal bus, the Inter-Cartridge Exchange (ICX). The ICX is implemented as a set of libraries and services that enable all the internal programs, in a set of programs that together form an application, to quite freely communicate with each others and surrounding servers and services. The ICX uses HTTP and IIOP for communication and performs automatically certain conversions if necessary. The ICX has an API to manage contact with ActiveX, Java (using CORBA/IDL – IIOP – JDBC), and a large number of programming languages and application programs on the market.

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 41 / 44

SLIDE 151

ORDBMS, – Oracle Cartridges . . .

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 42 / 44

SLIDE 152

Finally

The brains behind most of the ideas in the ORDBMS world is the team, headed by Michael Stonebraker, that created Ingres, Postgres, Illustra and, finally, the Informix Dynamic Server.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 43 / 44

SLIDE 153

Finally

The brains behind most of the ideas in the ORDBMS world is the team, headed by Michael Stonebraker, that created Ingres, Postgres, Illustra and, finally, the Informix Dynamic Server. The three big dragons – IBM, Informix and Oracle – have seen success but in site

f all the similarities there are differences

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 43 / 44

SLIDE 154

Finally

The brains behind most of the ideas in the ORDBMS world is the team, headed by Michael Stonebraker, that created Ingres, Postgres, Illustra and, finally, the Informix Dynamic Server. The three big dragons – IBM, Informix and Oracle – have seen success but in site

f all the similarities there are differences

DB2 (supposedly) builds on conformance with the SQL3 standard, Oracle on the use of open standards for communication, mainly IDL and CORBA while Informix has focused on the hardships with adding non-relational stuff in a relational system and the difficulty to mold them into the system and therefore delivers powerful tools for developing datablades and to install them into the system (BladeSmith, Bladepack och Blademanager)

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 43 / 44

SLIDE 155

Some other systems

AMOS II (Uppsala University Database laboratory) Cach´ e (InterSystems) Data Access FrameWork / Total (CinCom) Enterprise Application Server (Sybase) KE Texpress (Knowledge Engineering) JBMS (Cloudscape) Lincks (Link¨

ping University)

Matisse (Matisse) Microsoft Repository (Microsoft) ODB-II (Fujitsu) OSMOS (Unisys) Phasme DBMS (Frederic Andres) Polyhedra (Polyhedra) PostgreSQL (UCB) UniSQL (Cincom)

DD2471 (Lecture 08)

Modern database systems & their applications Spring 2012 44 / 44