1 / 19

Object Relational Features

Explore object-relational databases, UDTs, inheritance, encapsulation, & more. Learn about SQL enhancements & extensions for complex objects and user-defined types.

jamescrawford
Download Presentation

Object Relational Features

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. ADVANCED DATABASES Object Relational Features Khawaja Mohiuddin Assistant Professor Department of Computer Sciences Bahria University (Karachi Campus) Khawaja.mohiuddin@bimcs.edu.pk https://sites.google.com/site/khawajamcs Contents for this lecture are taken from: Chapter 10 of “Database Systems: Models, Languages …”, 6th Ed.” by Elmasri and Navathe (Chapter 11 of “Fundamentals of Database Systems” 6th Ed. by Elmasri and Navathe)

  2. Objectives • To explain what Object-relation databases are • To cover the Object Database extensions to SQL • To understand how UDTs are declared • To go over examples of following Object Database concepts: • Inheritance • Encapsulation • Reference Type • Instantiability • Referencing and dereferencing attributes

  3. Object-Relational Databases • Relational DBMS (RDBMS) vendors recognized need to add object database features. • The resulting database systems characterized as Object-Relational Database Systems or ORDBMS. • SQL first specified by Chamberlin and Boyce (1974), underwent enhancements and standardization in 1989 and 1992. New standard, initially called SQL3 and later known as SQL:99 • Starting with SQL3 version, features from object databases were incorporated into the SQL standard. • At first, these extensions were known as SQL/Object SQL standard 2008 for RDBMSs includes many ODBMS features - originally known as SQL/Object - now have merged into main SQL specification known as SQL/Foundation.

  4. Object-Relational Features:Object Database Extensions to SQL • Relational model with object database enhancements referred to as the object-relational model. • Type constructors • Specify complex objects • Includes the row type (tuple or struct) constructor and array type • set, list, and bag constructors were included later in the standard • Mechanism for specifying object identity included • Encapsulation of operations • Provided through user-defined types (UDTs) • May include operations also • Inheritance mechanisms • Provided using keyword UNDER

  5. User-Defined Types (UDT) and Complex Structures for Objects • To allow the creation of complex-structured objects • To separate the declaration of a type from creation of a table • Can be used as either the type for an attribute or as the type for a table • UDT within UDT (complex structure for objects in a table) • UDT syntax: • CREATE TYPE TYPE_NAME AS (<component declarations>);

  6. User-Defined Types and Complex Structures for Objects (cont’d.) • UDT example: CREATE TYPE STREET_ADDR_TYPE AS ( NUMBER VARCHAR (5), STREET_NAME VARCHAR (25), APT_NO VARCHAR (5), SUITE_NO VARCHAR (5) ); CREATE TYPE USA_ADDR_TYPE AS ( STREET_ADDR STREET_ADDR_TYPE, CITY VARCHAR (25), ZIP VARCHAR (10) ); • CREATE TYPE USA_PHONE_TYPE AS ( • PHONE_TYPE VARCHAR (5), • AREA_CODE CHAR (3), • PHONE_NUM CHAR (7) • ); • CREATE TYPE PERSON_TYPE AS ( NAME VARCHAR (35), SEX CHAR, BIRTH_DATE DATE, PHONES USA_PHONE_TYPEARRAY[4], ADDR USA_ADDR_TYPE . .

  7. User-Defined Types and Complex Structures for Objects (cont’d.) • ROW TYPE • Directly create a structured attribute using the keyword ROW if a UDT does not have any operation • CREATE TYPE USA_ADDR_TYPE AS ( • STREET_ADDR ROW ( NUMBER VARCHAR (5), • STREET_NAME VARCHAR (25), • APT_NO VARCHAR (5), • SUITE_NO VARCHAR (5) ), • CITY VARCHAR (25), • ZIP VARCHAR (10) • );

  8. User-Defined Types and Complex Structures for Objects (cont’d.) • Array type • Reference elements using []Example: PHONES USA_PHONE_TYPE ARRAY[4] • PHONES[1] refers to the first location value in a PHONES attribute • CARDINALITY function • Returns the current number of elements in an array • PHONES[CARDINALITY (PHONES)] refers to the last element in the array • Dot notation: • ADDR.CITY refers to the CITY component of an ADDR attribute

  9. Object Identifiers Using Reference Types • Reference type • Create unique system-generated object identifiers. Example: REF IS SYSTEM GENERATED • CREATE TYPE PERSON_TYPE AS ( • NAME VARCHAR (35), • SEX CHAR, • BIRTH_DATE DATE, • PHONES USA_PHONE_TYPE ARRAY [4], • ADDR USA_ADDR_TYPE • INSTANTIABLE • NOT FINAL • REF IS SYSTEM GENERATED • INSTANCE METHOD AGE() RETURNS INTEGER; • CREATE INSTANCE METHOD AGE() RETURNS INTEGER • FOR PERSON_TYPE • BEGIN • RETURN /* CODE TO CALCULATE A • PERSON’S AGE FROM TODAY’S DATE • AND SELF.BIRTH_DATE */ • END; • );

  10. Object Identifiers Using Reference Types (cont’d.) • Reference type (cont’d.) • Complete Syntax is: REF IS <OID_ATTRIBUTE> <VALUE_GENERATION_METHOD>; • <OID_ATTRIBUTE> is user declared attribute name to indentify individual tuples in a table • The options for <VALUE_GENERATION_METHOD> are: • SYSTEM GENERATED - system will automatically generate a unique identifier for each tuple • DERIVED - user provided primary key value to identify tuples is applied

  11. Creating Tables Based on the UDTs • INSTANTIABLE • Specify that UDT is instantiable (see PERSON_TYPE declaration) • Causes one or more tables to be created • UDTs STREET_ADDR_TYPE and USA_ADDR_TYPE are noninstantiable, and hence can only be used as types for attributes, but not as a basis for table creation CREATE TABLE PERSON OF PERSON_TYPE REF IS PERSON_ID SYSTEM GENERATED; • Create a table PERSON based on the PERSON_TYPE UDT • Attribute PERSON_ID will hold the system-generated object identifier whenever a new PERSON record (object) is created and inserted in the table

  12. Encapsulation of Operations • User-defined type • Specify methods (or operations) in addition to the attributes • Format: CREATE TYPE <TYPE-NAME> ( <LIST OF COMPONENT ATTRIBUTES AND THEIR TYPES> <DECLARATION OF FUNCTIONS (METHODS)> ); • Example: method Age() of type PERSON_TYPE

  13. Encapsulation of Operations (cont’d.) • Constructor function TYPE_T( ) • Returns a new object of that type • Observer function X.A • Returns the value of attribute A of TYPE_T if X is of type TYPE_T • Mutator function • Sets the value of the attribute to a new value • User Defined functions (Internal Functions) • Format INSTANCEMETHOD <NAME> (<ARGUMENT_LIST>) RETURNS <RETURN_TYPE>;

  14. Encapsulation of Operations (cont’d.) • External functions • Written in a host language, with only their signature (interface) appearing in the UDT definition • Format DECLARE EXTERNAL <FUNCTION_NAME> <SIGNATURE> LANGUAGE <LANGUAGE_NAME>; • Categories of attributes and functions • PUBLIC (visible at the UDT interface) • PRIVATE (not visible at the UDT interface) • PROTECTED (visible only to subtypes)

  15. Specifying Inheritance and Overloading of Functions • Inheritance rules: • All attributes inherited (phrase NOT FINAL must be included) • Order of supertypes in UNDER clause determines inheritance hierarchy • Instance of a subtype can be used in every context in which a supertypeinstance is used • Subtype can redefine any function defined in supertype • When a function is called, best match selected based on types of all arguments • For dynamic linking, runtime types of parameters is considered

  16. Specifying Inheritance and Overloading of Functions (cont’d.) • Example of inheritance of types: • CREATE TYPE EMPLOYEE_TYPE UNDER PERSON_TYPE AS ( • JOB_CODE CHAR (4), • SALARY FLOAT, • SSN CHAR (11) • INSTANTIABLE • NOT FINAL ); • CREATE TYPE MANAGER_TYPE UNDER EMPLOYEE_TYPE AS ( • DEPT_MANAGED CHAR (20) • INSTANTIABLE ); • Example of inheritance of tables: • CREATE TABLE EMPLOYEE OF EMPLOYEE_TYPE • UNDER PERSON; • CREATE TABLE MANAGER OF MANAGER_TYPE • UNDER EMPLOYEE;

  17. Specifying Relationships via Reference • Component attribute of one tuple may be a reference to a tuple of another table • Specified using keyword REF • Keyword SCOPE • Specify name of table whose tuples referenced • Concept is similar to foreign key • Dot notation • Build path expressions that refer to the component attribute of tuples and row types

  18. Specifying Relationships via Reference • –> • Used for dereferencing for an attribute whose type is REF • SELECT E.Employee–>NAME • FROM EMPLOYMENT AS E • WHERE E.Company–>COMP_NAME = ‘ABCXYZ’; • In SQL, –> is has the same meaning assigned to it in the C programming language. Thus, if r is a reference to a tuple and a is a component attribute in that tuple, then r –> a is the value of attribute a in that tuple.

  19. Conclusion In this Lecture, we… • To explain what Object-relation databases are • To cover the Object Database extensions to SQL • To understand how UDTs are declared • To go over examples of following Object Database concepts: • Inheritance • Encapsulation • Reference Type • Instantiability • Referencing and dereferencing attributes

More Related