Transcript Database Design for Object

Database Design for ObjectRelational 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 SQL1992)
– 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.
2.
3.
4.
5.
6.
7.
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
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
BIBLIO
Bib_ID
Author
Title
Keyword
Name
Address
12
Smith
London
43
Worthy
arts
25
Jones
York
25
Satire
sport
Age
CREATE TYPE Aut_TYP AS
OBJECT (Name varchar2(30),
CREATE TYPE Biblio_TYP
AS OBJECT
Address varchar2(100),
(Bib_ID number,
Age 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
BIBLIO
Bib_ID
12
25
Author
Name
Address
Smith
London
43
Peters
Glasgow
32
Jones
York
25
CREATE TYPE Aut_TYP AS
OBJECT (Name varchar2(30),
Address varchar2(100),
Age number);
Same
Title
Keyword
Worthy
arts
Satire
sport
Age
CREATE TYPE
Aut_Nested as TABLE
OF Aut_TYP;
Then use Aut_Nested in
full definition (later)
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
Simple_Bib_Typ
Title
Worthy
Satire
Author
Smith
Peters
Jones
Keyword
arts
sport
CREATE TYPE Simple_Bib_Typ as OBJECT
(title varchar2(200),
author aut_names_typ,
keyword varchar2(20));
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 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)
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 ObjectRelational 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
BIBLIO
Bib_ID
12
25
Author
Name
Address
Smith
London
1972
Peters
Glasgow
1980
Jones
York
1985
CREATE TYPE Aut_TYP AS
OBJECT (Name varchar2(30),
Address varchar2(100),
Same
Dob number); + method for age
Title
Keyword
Worthy
arts
Satire
sport
Dob
CREATE TYPE
Aut_Nested as TABLE
OF Aut_TYP;
Then use Aut_Nested in
full definition (later)
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 -- 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
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 26Mb; 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
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