Database Design for Object-Relational Systems

1. Database Design for Object-Relational Systems I. Nested Structures/Methods Nick Rossiter November 2003

2. SQL Standards • 1987: SQL-1 ISO standard, relational • 1989: SQL-1 addendum (integrity) • 1992: SQL-2 (SQL-1992) updated ISO standard, relational • 1999: SQL-1999 (SQL-3), object-relational • ????: SQL-4 in progress

3. O-R systems • Postgres/Illustra • University of California at Berkeley • Developed from Ingres • Oracle 9i (some features are better handled than others) • MS SQL-Server (some features) • DB2 (IBM’s flagship)

4. Sources of Information • SQL-1999 documentation (very bulky, 1,000 pages plus!) • Connolly & Begg (chapter on subject, 27 in 3rd edition, p.891-940) • Elmasri & Navathe (chapter 13, 3rd edition)

5. Coverage in this Course • Not complete • Two lectures assigned • Concentrate on: • key differences from relational model • design aspects • Seminars will give exploration of small examples with subtle semantics

6. Why object-relational? • Relational model (as developed in SQL-1992) • Satisfactory for tables (administrative data) • Less suited to richer structures: • Multimedia (images, graphics, designs) • Web (full text, structured text, multimedia) • Complex objects (part of parts) • With extensive overlap between these categories • So not suited to many newer application areas

7. Some Extensions in O-R Approach – not all covered here • Attributes can be grouped within tables • First Normal Form (1NF) is not necessary • User-Defined Types (UDT) can be built • Inheritance is possible • Tables can be keyed on object identifiers • Foreign ‘keys’ can hold object identifiers • Enhanced basic types for multimedia

8. Oracle 9i • Uses Object construction as basis for meeting SQL-1999 standard • New types are typically constructed as Objects • Then use objects in o-o manner for flexible data structuring with methods • Hold objects in tables for persistence • Still have SQL for searching/updating

9. Details of Extensions • Sample tables given • SQL syntax is for Oracle 9i • Syntax varies slightly from one RDBMS to another • Degree to which features are implemented also varies from system to system

10. Extension 1: Grouping of Attributes • Can define objects (row-types in standard): • as a collection of attributes • named • used as SQL data type in a similar way to basic data types (char, integer, date,...) • e.g. in Viking Brewery, an order-line, one line (item) of the order, could be an object called ordline

11. Bib_ID Author Title Key-word Name Address Age 12 Smith London 43 Worthy arts 25 Jones York 25 Satire sport Example – Objects BIBLIO CREATE TYPE Aut_TYP AS OBJECT (Name varchar2(30), Address varchar2(100), Age number); CREATE TYPE Biblio_TYP AS OBJECT (Bib_ID number, Author Aut_TYP, Title varchar2(200), …);

12. Extension 2: 1NF not necessary • 1NF – all values are atomic (single-valued) • In O-R attributes (simple and those grouped within types): • May be multiply-occurring • Potentially dramatic effect on data structuring • Higher normal forms are still valuable where have keys: • Still use 2NF, 3NF, (BCNF, 5NF) • Sometimes known as ¬1NF (not 1NF, !1NF)

13. Bib_ID Author Title Key-word Name Address Age 12 Smith London 43 Worthy arts Peters Glasgow 32 25 Jones York 25 Satire sport Example – Nested Objects BIBLIO Nested CREATE TYPE Aut_TYP AS OBJECT (Name varchar2(30), Address varchar2(100), Age number); CREATE TYPE Aut_Nested as TABLE OF Aut_TYP; Then use Aut_Nested in full definition (later) Same

14. Simple attributes in arrays • Simple attributes may also be declared as multiply occurring in other ways. • Can be declared for instance as: • Varray (variable storage arrays) • CREATE TYPE Aut_names_typ AS VARRAY(10) OF varchar2(30); • Allows up to 10 authors with dynamic allocation of storage • Proportional to number of authors to be held

15. Example -- simple attributes Simple_Bib_Typ CREATE TYPE Simple_Bib_Typ as OBJECT (title varchar2(200), author aut_names_typ, keyword varchar2(20));

16. Extension 3: Objects (UDTs) • Objects in Oracle provide User-Defined Types of SQL-1999 standard: • typed attributes • local variables • Methods provide both: • observer functions of standard • deriving (observing) data values • mutator functions of standard • updating data values • ability for actions to derive virtual data • calculated or derived from input data values • In Viking Brewery calculate invoice totals

17. Methods • Defined by the user, using SQL, PL/SQL, JAVA, or C/C++. • Member functions/procedures: • Get values • ADD MEMBER FUNCTION get_title RETURN VARCHAR2(200); • Do calculations and derivations • Function returns a single value

18. Example -- attribute age • Age can be stored from direct user input • Soon gets out of date • Better calculate from: current_date minus date_of_birth • Analogous situations apply to: • calculated totals for, say, invoices • running totals of points in, say, sporting league tables • Similar to spreadsheet capability

19. Example method CREATE TYPE BODY Aut_typ AS MEMBER FUNCTION calc_age RETURN NUMBER IS age number; BEGIN age := SYSDATE – SELF.date_birth; RETURN age; END calc_age; END; Notes: SELF is default parameter passed to method (current object)

20. Searching • Can search on function using dot notation (object.method) in SQL SELECT … FROM Authors WHERE Authors. Calc_age() < 30 Notes: • Retrieves all authors currently younger than 30 • Simplified – what if more than one author?

21. Database Design for Object-Relational Systems II. Further O-R Extensions Nick Rossiter November 2003

22. Declaring Types as Tables • For extensions 1-3 last week: • Methods are declared with types • Aggregate constructions (e.g. nested, varray) produce container types • All types are held in tables for persistence

23. Bib_ID Author Title Key-word Name Address Dob 12 Smith London 1972 Worthy arts Peters Glasgow 1980 25 Jones York 1985 Satire sport Earlier Example – Nested Objects BIBLIO Nested CREATE TYPE Aut_TYP AS OBJECT (Name varchar2(30), Address varchar2(100), Dob number); + method for age CREATE TYPE Aut_Nested as TABLE OF Aut_TYP; Then use Aut_Nested in full definition (later) Same

24. Final Coding for Nested Table Create Table biblio ( Bib_ID number, Author Aut_nested, Title varchar2(200), Keyword varchar2(30) ) Nested Table Author Store as nested_author return as locator ;

25. Comments • Aut_nested was declared on previous slide as table of Aut_TYP which in turn was declared earlier as collection of name, address, age • Aut_nested is called a container type • Other attributes of Biblio are declared in normal SQL-1992 syntax • Nested table is held in separate file called nested_author • Locator is pointer to particular position in this file (efficient for de-referencing) • Table Biblio is the structure that is searched and updated

26. Extension 4: Inheritance is possible • Subtables are the mechanism • Simple mechanism for inheritance of attributes, functions. • Principle of substitutability • wherever an instance of a supertable is expected, an instance of one of its subtables can be used instead. • Subtable is-a Table is semantics test.

27. Subtables • Subtables can be nested to any level. • A subtable inherits all the properties and functions of its supertable. • Viewpoint is Specialization abstraction. • Only single inheritance is possible (one supertable per subtable) • Original aim had been multiple inheritance

28. Specialising -- Authors CREATE TYPE aut_decd_typ UNDER aut_typ AS OBJECT (date_of_death date); *** redefine member function get_age *** • Notes: • UNDER indicates subtype • Types and subtypes are defined as tables eventually • Aut_decd_typ has all attributes and functions of aut_typ plus those in aut_decd_typ (functions may be redefined in subtype) • Above is SQL-1999 syntax

29. Extension 5: object identifiers • oid (object identifier): • is assigned by the system, • is generally short in length • does not change as the content changes. • An oid corresponds closely to a storage address, enhancing efficiency. • Either oids or conventional keys can be used as appropriate in O-R.

30. Example of oids Create table biblio of biblio_typ ( *** any constraints *** ) OBJECT IDENTIFIER IS SYSTEM GENERATED; Notes: • uses biblio_typ from slide 11, lecture 1 on or • no primary key is declared • instead oid organization is used • values for oid are generated by system

31. Extension 6 -- oids can be referenced • Foreign ‘keys’ can hold object identifiers • Gives rapid addressing • Uses REF instead

32. Example -- cross-reference with oids CREATE TABLE library ( (library varchar2(30), shelf varchar2(20), bib_id number, cat_entry REF biblio_typ; Note: Attribute cat_entry in lib_cat table holds oid of biblio_typ giving direct cross-reference

33. Extension 7: New and improved types for multimedia • Binary Large Objects • For data as bits – e.g. image, audio, video • Volumes very large – single image often 2-6Mb; audio 10Mb+, video 100Mb+. • Not interpreted by database system • No member functions for internal manipulation • Need associated program to open them • Enables bit data to be integrated in storage with other data

34. Manipulation of BLOBs • BLOBs (Binary Large Objects): • comparisons can be made between one BLOB and another (at binary level); • BLOBs can be concatenated; • BLOBs can be searched for substrings; • overlays can be placed on BLOBs (replacements made in particular areas); • BLOBs can be trimmed (leading/trailing characters removed); • the lengths of BLOBs can be returned; • the position of strings in BLOBs can be returned.

35. New multimedia type • New data type is CLOB (Character Large OBject) used when it is known that the large object will consist of characters only. • As BLOB but limited further facilities for character handling: • folding (case changes)

36. Restrictions • Restrictions on BLOB/CLOB; • cannot use in some operations such as join, group by, order by, union, intersect. • manipulation can be clumsy • Why? • Type of output not clear • Performance problems

37. Example -- multimedia types CREATE TABLE archive (document_id number, author aut_TYP, article blob(50M), text_version clob(500K), picture blob(20M), author_introductory_speech blob(100M) ) PRIMARY KEY document_id ; Note: • 50M is 50 megabytes, • 500K is 500 kilobytes

38. Summary for O-R • Still experimental to some extent • Not stable enough for some users • Principles are important as to direction in databases (o-r, o-o) • Bring databases and programming languages closer together