Database Design for Object-Relational Systems
I. Nested Structures/Methods
Nick Rossiter
November 2003
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
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)
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)
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
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
Some Extensions in O-R Approach – not all covered here
1. Attributes can be grouped within tables
2. First Normal Form (1NF) is not necessary
3. User-Defined Types (UDT) can be built
4. Inheritance is possible
5. Tables can be keyed on object identifiers
6. Foreign ‘keys’ can hold object identifiers
7. Enhanced basic types for multimedia
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
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
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
Example – Objects
Bib_ID Author Title Key-word
Name Address Age
12 Smith London 43 Worthy arts
25 Jones York 25 Satire sport
CREATE TYPE Aut_TYP AS OBJECT (Name varchar2(30),
Address varchar2(100),
Age number);
BIBLIO
CREATE TYPE Biblio_TYP AS OBJECT
(Bib_ID number,
Author Aut_TYP,
Title varchar2(200), …);
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)
Example – Nested Objects
Bib_ID Author Title Key-word
Name Address Age
12 Smith London 43 Worthy arts
Peters Glasgow 32
25 Jones York 25 Satire sport
BIBLIO
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
Nested
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
Example -- simple attributes
CREATE TYPE Simple_Bib_Typ as OBJECT(title varchar2(200), author aut_names_typ, keyword varchar2(20));
Title Author KeywordSmithWorthyPeters
arts
Satire Jones sport
Simple_Bib_Typ
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
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
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
Example methodCREATE TYPE BODY Aut_typ ASMEMBER FUNCTION calc_age RETURN NUMBER ISage number; BEGINage := SYSDATE – SELF.date_birth; RETURN age; END calc_age;END;Notes: SELF is default parameter passed to method (current object)
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?
Database Design for Object-Relational Systems
II. Further O-R Extensions
Nick Rossiter
November 2003
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
Earlier Example – Nested Objects
Bib_ID Author Title Key-word
Name Address Dob
12 Smith London 1972 Worthy arts
Peters Glasgow 1980
25 Jones York 1985 Satire sport
BIBLIO
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
Nested
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 ;
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
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.
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
Specialising -- AuthorsCREATE 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
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.
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
Extension 6 -- oids can be referenced
• Foreign ‘keys’ can hold object identifiers
• Gives rapid addressing
• Uses REF instead
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
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
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.
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)
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
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