SQL for ORDBMS

1
SQL for ORDBMS
2
Contents

• SQL standards
• SQL procedures
• SQL types
• Distinct types, UDTs, LOBs, collections, REFs
• Triggers
• Encompassing transaction support
• SQL/MM

3
SQL standards

• SQL has been used in relational databases for 20
  years, and was defined as a standard in 1986.
• Not all implementations follow the standard
• Enhancements led to SQL2 which became SQL-92.
• SQL3 was planned to include extensions for OO.
• It was delayed, and eventually became SQL1999
• There will be more versions!

4
SQL procedures

• SQL-92 was not computationally complete
• Procedures and functions in SQL are now supported
• SQL/PSM (Persistent Stored Modules) was developed
  in 1996
• Other languages such as Java are also supported
• SQL1999 incorporated this feature

5
SQL types (1)

• Simple types for numbers, text have been in the
  standard all the time
• SQL1999 includes LOBs (large objects) with both
  CLOB and BLOB variants
• It also has LOB locators to access LOBs without
  necessarily moving the LOB from a server to a
  client

6
SQL types (2)

• Distinct types (DT) allow you to use a new name
  for a type, associated with an existing type
• CREATE DISTINCT TYPE payNo AS INTEGER
• Restricts use of payNo values they cant be
  combined with other integers

7
SQL types (3)

• Row types
• CREATE ROW TYPE fullname
• (forename VARCHAR(20),
• surname VARCHAR(20) )
• Row types can be used to define cells in a table,
  or whole rows.

8
SQL types (4)

• Collections
• Sets, bags (multisets), lists, ARRAY
• Operations, such as
• Union, Intersect, Difference (sets, bags)
• Counting number of elements
• Concatenating lists
• etc.

9
SQL types (5)

• Structured User-defined types
• Attributes defined for them
• Methods, functions and procedures defined
• Attributes accessible only via system-generated
  set and get functions (observer and mutator)
• Type hierarchies allowed inheritance
• Similar to Javas single-inheritance

10
SQL types (6)

• REF types
• These hold references to other data objects
• In effect, they hold object-identifiers to
  reference the object itself

11
Triggers

• Triggers are procedures that are run when a
  trigger event occurs
• A trigger event is associated with an UPDATE,
  INSERT or DELETE operation on a base table, and
  is either BEFORE the operation or AFTER it.
• For example, a trigger might log all before and
  after values of a row when it is updated.

12
Triggers and integrity

• The built-in integrity constraints supported by a
  DBMS can be complemented by user-defined triggers
• A trigger can check the consistency of the data
  before an operation occurs

13
SQL for Multimedia

• User-defined types and LOBs provide a way of
  incorporating MM data into a database
• The data representation is held in a LOB
• Associated fields can be used to add features
• The methods and functions can provide specialised
  operations for the multimedia data
• The database designer could define all this
• Or, these could be provided as a library feature

14
SQL/MM

• A group of developers looked at providing
  Multimedia support in SQL, called SQL/MM.
• The developments were done in parallel with the
  development of the SQL1999 standard
• They used ideas similar to the UDTs.

15
Some limitations of SQL1999

• With simple data types, the index mechanisms were
  well-defined and efficient implementations were
  possible
• How do you provide efficient implementations with
  complex data?
• Video sound have streams of data should you
  manage the timing of these?

16
Limitations (2)

• In a distributed system, where a client is
  handling large MM objects on a server,
• How do you decide whether to transfer the object
  to the client or perform the operation at the
  server?
• How do you manage the distribution of the
  functions?

17
Summary

• SQL has developed from relational databases to
  Object-relational
• Standard SQL1999 incorporates object features
  that can support MM
• SQL/MM was a parallel development
• There are still limitations in the language and
  its implementations