1 / 45

Data Modelling

This lecture explores Object-Relational Models, focusing on User-Defined Types (UDTs) in SQL-1999 and Oracle 10g. Learn how to create UDTs for objects, appreciate new multimedia data types, and manage object behavior through functions. Gain insights into inheritance for generalization-specialization hierarchies, and understand the manipulation of Binary Large Objects (BLOBs) and Character Large Objects (CLOBs). This foundational knowledge enhances your capability to create tailored data structures for complex applications.

tal
Télécharger la présentation

Data Modelling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Modelling Lecture 10 (c): Object-Relational (O-R) Model Nick Rossiter

  2. Learning Objectives • To be able to create user-defined types for objects • To appreciate the new types available for multimedia data • To use functions for handling behaviour of objects • To realise inheritance for generalisation-specialisation hierarchies

  3. Activity 1User-defined Types User-defined Types

  4. User-Defined Types (UDT) • Cornerstone of: • the SQL-1999 and Oracle 10g approach to object-relational structures • Idea is that: • Wherever you use standard SQL types • Such as char, number, varchar2 • You can use types written by yourself for a particular purpose

  5. Example UDT Creation in SQL Plus-- Authors CREATE OR REPLACE TYPE aut_type AS OBJECT ( name char(6), age number, address varchar2(200) ); / UDT attribute type Name ofUDT UDT attribute name End of input for type

  6. Object UDT • CREATE OR REPLACE • if type exists already, REPLACE • if type does not exist, CREATE • OBJECT • collection of attribute-type pairs and functions • Syntax of CREATE TYPE • similar to CREATE TABLE but more flexible

  7. Usage of Types • In CREATE TABLE • as a user-defined type for an attribute • as the sole component of the table • In interfaces to Java (e.g. SQLJ) and other languages, such as C++. • In PL/SQL (Procedural Language/SQL)

  8. Types can be built from types CREATE OR REPLACE TYPE aut_nested AS TABLE OF aut_type; / Name of new type Type upon which it is based Construction aut_nested is a repeating group, held as a nested table, of name, age and address

  9. Using aut_nested Create Table bib ( bib_id number, title varchar2(100), authors aut_nested, keyword char(20), constraint uniq_bibid primary key (bib_id) ) Nested Table authors Store as nested_aut return as locator; UDT Name of attribute of UDT aut_nested Held in this file in Oracle system pointer

  10. Code represents table below Table name Bib .

  11. Array UDT -- Handle multiple keywords • Can create a type that is an array of another type e.g. • CREATE or replace TYPE keyw_array AS VARRAY(6) OF char(20); • / Name of new type Array of variable size, 6 members in this case Type of each member

  12. Using aut_nested, keyw_array Create Table bib ( bib_id number, title varchar2(100), authors aut_nested, keywords keyw_array, constraint uniq_bibid primary key (bib_id) ) Nested Table authors Store as nested_aut return as locator; UDT for keywords

  13. Code stores table below Bib .

  14. Insertion of Data – SQL Plus Normal insertion for ‘flat’ values insert into bib values (12, 'Worthy', aut_nested(aut_type('Smith', 36, 'London'), aut_type('Peters', 70, 'Bristol') ), keyw_array('history', 'politics') ); Two author entries for bib_id=12 Two keywords for bib_id=12

  15. Insertion of UDT values • Type name followed by values in brackets in the order declared in the UDT • aut_nested(…..) • aut_type(……) • keyw_array(…..) • These are constructors in object talk

  16. Displaying UDT contents SELECT * FROM BIB; displays all flat values and constructed values

  17. Searching Nested Tables SELECT b.title, a.address FROM bib b, table (b.authors) a WHERE a.name = ‘Smith’; • Path is through outer table bib to inner nested table authors • Retrieves pairs of values for author ‘Smith’ • title from bib plus single address from authors table instance table shows the path needed to access the authors nest

  18. Multimedia objects

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

  20. 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.

  21. 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)

  22. 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

  23. Example -- multimedia types Create Table bib ( bib_id number, title varchar2(100), authors aut_nested, keyword keyw_array, cover_page_facsimile blob, introduction clob constraint uniq_bibid primary key (bib_id) ) Nested Table authors Store as nested_aut return as locator; / type binary large object type char large object

  24. Explanation • Cover_page_facsimile • type blob, binary large object, can use parameters to control size and handling • could be jpeg or tiff formatted object reproducing the cover page of the book (c1Mb) • Introduction • type clob, character large object, can use parameters to control size and handling • a long text object holding perhaps a 20 page introduction, c10,000 words or c60kb.

  25. Insertion into LOBs insert into bib values (6, 'Score', aut_nested(aut_type('Wilson', 54, 'Exeter') ), keyw_array('sport', 'cricket', 'football'), '0001FF', 'This is a very long introduction going over some 16,000 words' ); Char input into clob as string Binary input into blob as hex (0..9,A..F)

  26. Other insertion methods • Often from files • unrealistic to type in bulky data • binary data is not suitable for input by people • With very large files • data can be held as file outside database system • Size limit • 4Gb is maximum size for one blob or clob entry • can have many blob/clob per row

  27. functions

  28. O-R Facilities used to date with bib • Nesting authors • Arrays keywords • Binary LOB cover page • Char LOB introduction full text • Note: facilities are orthogonal -- free of side effects (independent of each other) • Now look at functions (methods) and searching.

  29. Functions • Similar to procedures • But return one value only • Like methods in object-oriented paradigm • Defined as member functions for a particular type of object • Develop ADT (Abstract Data Types) with data structures and methods

  30. Functions in Oracle • Defined by the user, using SQL, PL/SQL, JAVA, or C/C++. • Member functions: • Set up header for function e.g. ALTER TYPE aut_type ADD MEMBER FUNCTION get_age RETURN number; • Do calculations and derivations • Function returns a single value Type for which member function defined Name of function Type returned by function

  31. 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

  32. Alter AUT_TYPE CREATE OR REPLACE TYPE aut_type AS OBJECT ( name char(20), date_of_birth date, address varchar2(200) ) / Replaces age

  33. Example of Function CREATE OR REPLACE TYPE BODY aut_type AS MEMBER FUNCTION get_age RETURN NUMBER IS age number; BEGIN age := (SYSDATE - date_of_birth)/365.25; /* subtracting two dates gives difference in days */ RETURN age; END get_age; END; / Local variable Current date

  34. Searching nested table on function Displays calc Value for age • SELECT b.title, a.name, a.get_age() • FROM bib b, table (b.authors) a • WHERE a.get_age() > 50; • Finds bibliographic title and only those nested authors aged over 50. • Note get_age() is a calculated attribute (derived from date_of_birth) • Can also search nested tables with normal attributes e.g. Name. Searches on function get_age

  35. Notes on Searching • The search on a function in SQL uses the dot notation (object.method) which is similar to techniques in Java. • Arrays cannot be searched in SQL Plus • PL/SQL gives powerful manipulation of all object-relational structures

  36. inheritance

  37. Inheritance Concepts • Important aspect of object-oriented programming • Specialisation • Classes are arranged in graphs in supertype-subtype relationships • Supertype is general class • Subtype has properties and methods of supertype • plus specialised properties and methods of its own • Subtype inherits properties and methods of supertype

  38. Inheritance in O-R • Not in Oracle 8i • Introduced in Oracle 9i/10g • Important development in claiming ability to handle objects • In SQL-1999 and Oracle 10g • Only single inheritance is supported • Achieved through declaring one type, say A, UNDER another, say B. A is subtype of B.

  39. Example for Inheritance: General bibliography • Take bibliographic data (references to literature) • Have various kinds of bibliographies but all have general features in common: • title • author • keywords

  40. Example for Inheritance: Specialised forms • BOOK (ISBN, publishers, total pages) • REFERENCE BOOK (as Book, plus subject) • JOURNAL (ISSN, volume, page range)

  41. Design in Oracle 10g - general Bib supertype CREATE OR REPLACE TYPE bib_type AS OBJECT ( title varchar2(100), authors aut_nested, keyword keyw_array ) NOT FINAL; / Name of type Enables subtypes to be based upon it Similar to earlier Bib table but identifier omitted

  42. Design in Oracle 10g - special Journal subtype Is-A CREATE OR REPLACE TYPE journal_bib_type UNDER bib_type ( journal_title varchar2(200), volume number, page_first number, page_last number ) FINAL; / supertype subtype specialised attributes for journal No subtypes can be based Upon Journal (default)

  43. Benefits of O-R in Design • Many Information System (IS) analysis techniques have concepts such as inheritance, aggregation and process. • O-R also directly provides such concepts • O-R enables direct transfer of IS analysis into the database design

  44. Oracle 10g O-R • Principles are important as to direction in databases (o-r, o-o) • Brings databases and programming languages closer together • Not stable enough for some users • Undoubted benefits for newer application areas such as CAD, library, multimedia. • Traditional administrative data processing applications may linger with SQL-92

  45. Summary • Reviewed main features of O-R • User-defined types, multimedia types, functions (methods) and inheritance • Showed how to use the new types • Discussed usability and advantages and disadvantages of new techniques

More Related