OBJECT DATABASE SYSTEMS PART ONE Advanced database application

OBJECT DATABASE SYSTEMS PART ONE Þ Advanced database application areas Þ Problems associated with RDBMSs Þ Third Generation DBMSs Þ What is Object Orientation? Þ What is an OODBMS?

DATABASES ADVANCED APPLICATION AREAS Þ Computer-Aided Design (CAD). Þ Computer-Aided Manufacturing (CAM). Þ Computer-Aided Software Engineering (CASE). Þ Multimedia Systems. Þ Digital Publishing. Þ Geographic Information Systems (GIS). Þ Scientific and Medical Systems.

RELATIONAL DBMSs PROBLEMS Þ Poor representation of ‘real world’ entities. Þ Semantic overloading. Þ Homogeneous data structure. Þ Difficulty handling recursive queries. Þ Impedance mismatch.

RELATIONAL DBMSs PROBLEMS - REAL-WORLD OBJECTS ER Diagram - Car C# CAR Part of W# C# WHEEL Part of T# W# TRIM Has Part of Has S# C# SEAT

RELATIONAL DBMSs PROBLEMS - REAL-WORLD OBJECTS NORMALISATION Car {C#, Chassis#, No. Wheels, No. Seats, etc…} Wheel {W#, C#, Size, Pressure, etc…} Trim {T#, W#, Material, Cost, etc…} Seat {S#, C#, Material, Size, Cost, , etc…} To find out all details about a Car we would have to carry out a large number of (expensive) JOIN operations. Select * From Car, Wheel, Trim, Seat Where Car. C# = Wheel. C# And Car. C# = Seat. C# And Wheel. W# = Trim. W#;

RELATIONAL DBMSs PROBLEMS - SEMANTIC OVERLOADING ER Diagram D# Oversees P# D# DOCTOR Overseen by PATIENT D# Kills P# DOCTOR Killed by D# PATIENT Doctor {D#, Name, Surgery, etc…} Patient {P#, D#, Name, Address, DOB, etc…} We do not record the nature of the relationship between doctor and patient!

RELATIONAL DBMSs PROBLEMS - HOMOGENEOUS DATA STRUCTURE ACCOUNT TABLE ACCOUNT CUSTOMER BRANCH 200 324 345 350 400 456 JONES GRAY SMITH GREEN ONO KHAN STRATFORD BARKING STRATFORD BALANCE 1000. 00 23. 17 340. 14 500. 00 333. 00 ALL ROWS HAVE THE SAME NUMBER OF ATTRIBUTES ALL VALUES IN A COLUMN ARE OF THE SAME TYPE ALL ATTRIBUTE VALUES ARE ATOMIC

RELATIONAL DBMSs PROBLEMS - RECURSIVE QUERIES Question - Who does SMITH work for? Select E 2. ENAME From EMP E 1, EMP E 2 Where E 1. MGR = E 2. EMPNO And E 1. ENAME = “SMITH”; ? KING First Level Answer – SMITH works for FORD Select E 3. ENAME From EMP E 1, EMP E 2, EMP E 3 Where E 1. MGR = E 2. EMPNO And E 2. MGR = E 3. EMPNO And E 1. ENAME = “SMITH”; JONES FORD Second Level Answer - SMITH works for JONES Select E 4. ENAME From EMP E 1, EMP E 2, EMP E 3, EMP E 4 Where E 1. MGR = E 2. EMP And E 2. MGR = E 3. EMPNO And E 3. MGR = E 4. EMPNO And E 1. ENAME = “SMITH”; Third Level Answer – SMITH works for KING SMITH

RELATIONAL DBMSs PROBLEMS - IMPEDANCE MISMATCH SQL is a declarative, set-based language that is not computationally complete! Database applications require the use of a computationally complete, record-based, procedural language such as C, C++, Java, and PL/SQL. We therefore have to map sets of data into records using memory structures such as cursors. This is expensive in terms of application processing time and programming effort, accounting for around 30% of some projects!

THIRD GENERATION DBMSs MAIN AIMS • Support Complex Active Objects Allow both data and its associated behaviour to be modelled to any level of complexity. • Improve Extensibility Allow for the dynamic extension of both allowable data types and the behaviour associated with these types. • Reduce the Impedance Mismatch Ensure that there is a seamless integration between the DBMS data model and that of the programming language accessing the data.

Object Model WHAT IS AN ATOMIC (LITERAL) OBJECT? An atomic object is a container for a fixed value and serves the same purpose as a constant in a programming language. An atomic object cannot change its own state. Examples of atomic types and atomic objects Integer - e. g. 1, 2, 3, -5, -45, etc. . . Float - e. g. 1. 52, -0. 3456, 2. 000, etc. . . Boolean - i. e. True or False Char - e. g. a, b, c, @, #, !, etc. . . String - e. g. “Mark”, “Database Systems”

Object Model WHAT IS A (MUTABLE) OBJECT? UNIQUE OBJECT IDENTIFIER (OID) I am an object! ATTRIBUTES State NAME MARK DOB 14/02/1964 JOB LECTURER RELATIONSHIPS BEHAVIOUR Methods CHANGE JOB GET AGE

Object Model WHAT IS A CLASS? MARK I am an object! I am also an object! IAN

Object Model WHAT IS A CLASS? PERSON CLASS MARK I am a Person! So am I! PERSON NAME DOB JOB CHANGE JOB GET AGE IAN

Object Model WHAT IS AN OBJECT IDENTIFIER (OID)? Each object is uniquely identifiable from all other objects. When an object is first created it is assigned a value to identify it. This value is called its Object Identifier. Þ System generated. Þ Unique to that object. Þ Invariant in that it cannot be altered. Þ Independent of its attribute values. Þ Invisible to the user.

Object Model WHAT IS ENCAPSULATION? GET AGE CHANGE JOB METHOD NAME: MARK DOB: 14/02/64 JOB: LECTURER GET AGE METHOD OBJECT CHANGE JOB

Object Model WHAT IS A COMPLEX OBJECT? Is a Car a Complex Object? Yes, it’s an object that is made up of other objects! Wheels, Seats, Chassis, Exhaust, Steering Wheel, etc. . . And… a wheel itself is also a complex object! Tire, Trim, Hub Cap, etc. . .

Object Model WHAT IS A COMPLEX OBJECT? TIRE HUB CAP TRIM WHEEL SEAT REGISTRATION NUMBER WHEEL CAR SEAT CHASSIS WHEEL SEAT TRIM TIRE HUB CAP TRIM

Object Model WHAT IS A COMPLEX OBJECT? TIRE A Wheel IS-PART-OF a Car HUB CAP TRIM WHEEL SEAT TIRE HUB CAP TRIM WHEEL SEAT REGISTRATION NUMBER TIRE HUB CAP WHEEL CAR SEAT CHASSIS WHEEL SEAT A Car has a COLLECTION of Wheels TRIM TIRE HUB CAP TRIM

Object Model WHAT IS A COMPLEX OBJECT? CAR REG-NUMBER: CHASSIS: WHEELS: SEATS: WHEEL TIRE: STRING WHEEL HUB CAP: STRING TIRE: WHEEL TRIM: STRING HUB CAP: STRING TIRE: TRIM: STRING HUB CAP: STRING TRIM: STRING SET<WHEEL> SET<SEAT> SEAT TIRE: STRING SEAT HUB CAP: STRING TIRE: SEAT TRIM: STRING HUB CAP: STRING TYPE: TRIM: STRING COLOUR: STRING POSITION: STRING

Object Model WHAT IS A COLLECTION - SET? SET - An unordered collection of distinct objects of the same type e. g, Customers : SET <Customer>; BILL An instance of CUSTOMERS MARK HILDA MARIE CAROLINE

Object Model WHAT IS A COLLECTION - BAG? BAG - An unordered collection of objects of the same type e. g, Phone_calls : BAG <Telephone. Number>; 555 -9999 An instance of PHONE_CALLS 444 -3333 555 -9999 111 -3333 444 -3333

Object Model WHAT IS A COLLECTION - LIST? LIST - An ordered collection of objects of the same type e. g, Machine. Faults : LIST <Fault>; Fault at 11: 00: 01 Fault at 11: 00: 20 An instance of Machine. Faults Fault at 11: 31: 00 Fault at 11: 44: 33 Fault at 12: 00

Object Model WHAT IS A COLLECTION - ARRAY? ARRAY – Each object is stored at a particular position e. g, Study. Schedule : ARRAY <Task>; TASK #1 TASK #3 TASK #1 TASK #4 TASK #2 TASK #1 An instance of Study. Schedule 0 1 2 3 4 5 6 7 8 9 10 11 12 13

Object Model WHAT IS A STRUCTURE? A fixed number of named slots, each of which can contain an object of a particular type. e. g, Customers. Details : STRUCTURE < forenames : List < String>, family_name : String, customer_no : Integer > Marie An instance of Customer. Details Rebecca Campbell 9603456 Caroline forenames family_name customer_no

Object Model WHAT IS INHERITANCE? IS-A Person name address telephone_no change_name (. . . ) change_address (. . . ) Employee employee_no promote(. . . ) pay_employee (. . . ) IS-A Customer customer_no place_order(. . . ) make_payment(. . . ) IS-A Trade. Customer trade_discount% place_order(. . . ) make_payment(. . . )

Object Model WHAT IS MULTIPLE INHERITANCE? Person name address telephone_no change_name (. . . ) change_address (. . . ) Employee employee_no promote(. . . ) pay_employee (. . . ) Customer customer_no place_order(. . . ) make_payment(. . . ) IS-A Employee_Customer staff_discount_card place_order(. . . ) make_payment(. . . ) Trade. Customer trade_discount% place_order(. . . ) make_payment(. . . )

Object Model WHAT ARE OBJECT RELATIONSHIPS? Husband . . . . 1 husband_of 1 wife_of Wife . . . . one-to-one Child . . . . * child_of 1 mother_of Mother . . . . one-to-many Child . . . . * child_of * parent_of many-to-many Parent . . . .

Object Model WHAT ARE METHODS AND MESSAGES? CHANGE JOB METHOD BODY DISK GET A GE M ESSA GE E S O E M B A CH N J E G G SA USER PROGRAM NAME: MARK DOB: 14/02/64 JOB: LECTURER GET AGE METHOD BODY OBJECT

Object Model WHAT IS POLYMORPHISM? ANALOGUE CLOCK . . . Set. Time () Set. Alarm () Show. Time () DIGITAL CLOCK . . . Set. Time () Set. Alarm () Show. Time () DIFFERENT TYPES OF OBJECT RESPOND DIFFERENTLY TO THE SAME MESSAGE 11: 00 PM

Object Model WHAT IS OVERIDING? When a sub-class’s method body is used rather than the body of the super-class’s method it is known as overriding. METHOD BODY 1. Create order details 2. Calculate total cost SUB-CLASS Customer No. Place. Order() Make. Payment() SUPER-CLASS CUSTOMER METHOD BODY 1. Create order details 2. Calculate total cost 3. Apply Trade Discount IS-A TRADE CUSTOMER Trade. Discount% Place. Order()

Object Model WHAT IS LATE BINDING? Late or dynamic binding is the ability of the runtime system to determine which method body to execute depending on the type of an object. Customers : = SET <Customer> CUSTOMER TIRE: Customer STRING Trade HUB CAP: STRING TIRE: CUSTOMER TRIM: STRING HUB CAP: STRING TIRE: Trade Customer TRIM: STRING HUB CAP: NO CUSTOMER TRIM: STRIN PLACE_ORDER MAKE_PAYMENT FOR x IN Customers DO x. PLACE_ORDER END Where x : = an individual Customer Object! It doesn’t matter if a customer is a trade customer, late binding ensures the appropriate PLACE_ORDER method body is called!

OODBMS WHAT IS AN OODBMS? Object Oriented Database Management Systems (OODBMSs) are an attempt at marrying the power of Object Oriented Programming Languages with the persistence and associated technologies of a DBMS. OBJECT ORIENTED DATABASE MANAGEMENT SYSTEM OOPLs DBMSs Complex Objects Object Identity Methods & Messages Inheritance Polymorphism Extensibility Computational Completeness Persistence Disc Management Data Sharing Reliability Security Ad Hoc Querying

OODBMS WHAT AN OODBMS SHOULD SUPPORT? · Atomic and complex objects · Methods and messages · Object Identity · Single inheritance · Polymorphism - overloading and late-binding · Persistence · Shared Objects In addition an OODBMS can optionally support · Multiple inheritance · Exception messages · Distribution · Versioning

LECTURE PLAN OBJECT DATABASE SYSTEMS PART TWO Þ What is an OODBMS? Þ Advantages and Disadvantages of OODBMSs Þ What is an ORDBMS? Þ What is SQL 3? Þ Comparison of OODBMSs and ORDBMSs Þ When to use an OODBMS Þ When to use an ORDBMS

OODBMSs REQUIREMENTS AND FEATURES Requirements: Transparently add persistence to OO programming languages Ability to handle complex data - i. e. , Multimedia data Ability to handle data complexity - i. e. , Interrelated data items Add DBMS Features to OO programming languages Features: The host programming language is also the DML. The in-memory and storage models are merged. No conversion code between models and languages is needed.

TWO-LEVEL STORAGE MODEL FOR A RDBMS Main or virtual memory Transforming and type checking SQL Secondary storage

ACCESSING A RECORD USING AN RDBMS Application memory Page 3. Access object Record 4. Copy modified fields 2. Copy relevant fields DBMS Cache Page Record 5. Save page 1. Read page Secondary Storage Page Record

SINGLE-LEVEL STORAGE MODEL FOR AN OODBMS Main or virtual memory Secondary storage

ACCESSING AN OBJECT USING AN OODBMS Application memory 3. Access object Page 4. Swizzle pointers back, etc. Object 5. Save page 2. Swizzle pointers, etc. 1. Read page Secondary Storage Page Object

OODBMSs ADVANTAGES Þ Enriched modelling capabilities Þ Extensibility Þ Removal of Impedance Mismatch Þ Support for schema evolution. Þ Applicable for advanced database applications Þ Improved performance.

OODBMSs DISADVANTAGES Þ Lack of a universal data model Þ Lack of experience Þ Lack of standards. Þ Ad-hoc querying compromises encapsulation. Þ Locking at object-level impacts performance Þ Complexity Þ Lack of support for views Þ Lack of support for security

ORDBMSs WHAT IS AN ORDBMS? Definition: Object-Relational databases extend the Relational Data Model to address those weaknesses identified previously. An Object-Relational database adds features associated with object-oriented systems to the Relational Data Model. In essence ORDBMSs are an attempt to add OO to Tables!

ORDBMSs MAJOR DIFFERENCE BETWEEN AN ORDBMS AND AN OODBMSs try to add DBMS functionality to one or more OO programming languages. REVOLUTIONARY IN THAT THEY ABANDON SQL ORDBMSs try to add richer data types and OO features to a relational DBMS. EVOLUTIONARY IN THAT THEY EXTEND SQL

ORDBMSs WHAT IS SQL 3? SQL 3 or SQL/99: SQL 3 is a superset of SQL/92, in that it supports all of the constructs supported by that standard, as well as adding new ones of its own. Therefore, whatever worked in an implementation of SQL/92 should also work in an implementation of SQL 3. HOWEVER, IT SHOULD BE NOTED THAT AS YET THERE ARE NO IMPLEMENTATIONS OF SQL 3!

ORDBMSs WHAT IS NEW IN SQL 3? Þ Extended Base Types. Þ Row Types. Þ User-Defined Routines. Þ Sub-Types and Super-Types. Þ Sub-Tables and Super-Tables. Þ Reference Types and Object Identity. Þ Collection Types.

ORDBMSs SQL 3 - EXTENDED BASE TYPES ONE OF THE REASONS FOR THE MOVE AWAY FROM RDBMSs IS THE LIMITED BASE TYPE AVAILABILITY: BASE TYPES AVAILABLE IN SQL/92 INCLUDE: NUMBER, CHAR, DATE, . . . BASE TYPES IN SQL 3 ARE EXTENSIBLE. THEREFORE THE FOLLOWING MUCH NEEDED TYPES COULD BE ADDED (PROVIDED SOMEBODY CREATES THEM!) VIDEO, IMAGE, AUDIO, TEXT, SPATIAL, TEMPORAL, GEOGRAPHIC, WEB-PAGES, . . .

ORDBMSs SQL 3 - ROW TYPES BRANCHNO 10 20 30 ADDRESS STREET AREA CITY MAIN ST. HIGH RD. HIGH ST. BARKING LEYTON MARSDEN LONDON OXFORD CREATE TABLE branch ( branchno VARCHAR(3), address ROW ( street VARCHAR(25), area VARCHAR(15), city VARCHAR(15)); COLUMN ATTRIBUTES NO LONGER HAVE TO BE ATOMIC!

ORDBMSs SQL 3 - USER-DEFINED TYPES CREATE TYPE person_type AS ( PRIVATE date_of_birth DATE CHECK (date_of_birth > DATE ‘ 1900 -01 -1901’), PUBLIC name address tel_no VARCHAR(15) NOT NULL, VARCHAR(50) NOT NULL, VARCHAR(13) NOT NULL, FUNCTION get_age (P person_type) RETURNS INTEGER /* code to calculate age from date_of_birth */ RETURN END) NOT FINAL; An example of a User-Defined Routine (UDR)

ORDBMSs SQL 3 - SUB-TYPES & SUPER-TYPES PERSON-TYPE date of birth name address tel-no get_age() Sub-Types and Super-Types are used to allow for INHERITANCE in SQL 3 A Sub-Type can inherit from more than one Super-Type. Multiple Inheritance is allowed! STAFF-TYPE sno position salary branch is_manager()

ORDBMSs SQL 3 - SUB-TYPES & SUPER-TYPES CREATE TYPE staff_type UNDER person_type AS ( sno position salary bno VARCHAR(5) NOT NULL UNIQUE, VARCHAR(10) NOT NULL, NUMBER(7, 2), VARCHAR(3) NOT NULL, CREATE FUNCTION is_manager (s STAFF_TYPE) RETURNS BOOLEAN BEGIN IF s. position = ‘Manager’ THEN RETURN TRUE; ELSE RETURN FALSE; END IF END) NOT FINAL; Another example of a User-Defined Routine (UDR)

ORDBMSs SQL 3 - TYPES & TABLES In order to remain upwardly compatible with SQL-92, TYPES can ONLY be instantiated through SQL Tables! Therefore, in order to create instances of a type we first have to create a table to store those instances in! EXAMPLE: In order to create staff instances we first have to do one of the following: CREATE TABLE staff OF STAFF_TYPE ( PRIMARY KEY sno); CREATE TABLE staff ( info STAFF_TYPE, PRIMARY KEY sno);

ORDBMSs SQL 3 - SUB-TABLES & SUPER-TABLES PROBLEM - What happens if we create two or more tables for the same TYPE? How do we find all TYPE instances? EXAMPLE: FIND ALL MEMBERS OF STAFF! CREATE TABLE lecturer OF STAFF_TYPE ( PRIMARY KEY sno); CREATE TABLE admin OF STAFF_TYPE ( PRIMARY KEY sno); The only way of doing this is to declare the lecturer and admin tables as sub-tables of a staff super-table!

ORDBMSs SQL 3 - SUB-TABLES & SUPER-TABLES SUPER-TABLE CREATE TABLE staff OF STAFF_TYPE ( PRIMARY KEY sno); SUB-TABLES CREATE TABLE lecturer OF STAFF_TYPE UNDER staff ( PRIMARY KEY sno); CREATE TABLE admin OF STAFF_TYPE UNDER staff ( PRIMARY KEY sno); SOLUTION TO PROBLEM (FIND ALL STAFF): SQL> SELECT * FROM staff;

ORDBMSs SQL 3 - REFERENCE TYPES AND OIDS A Reference Type is the SQL 3 equivalent of an OID in an OODBMS. Reference Types allow a row to be shared among multiple tables, and enable users to replace complex join operations with path expressions! EXAMPLE: CREATE TYPE staff_type UNDER person_type AS ( sno VARCHAR(5) NOT NULL UNIQUE, position VARCHAR(10) NOT NULL, salary NUMBER(7, 2), Could point to a row in ANY next_of_kin REF(person_type) table containing a person_type! bno VARCHAR(3) NOT NULL) NOT FINAL; CREATE TABLE person OF person_type ( oid REF(person_type) VALUES ARE SYSTEM GENERATED);

ORDBMSs SQL 3 - REFERENCE TYPES AND OIDS To ensure that a REFERENCE is limited to a single table, a SCOPE has to be added to the table using the REFERENCE! EXAMPLE: CREATE TYPE staff_type UNDER person_type AS ( sno VARCHAR(5) NOT NULL UNIQUE, position VARCHAR(10) NOT NULL, salary NUMBER(7, 2), staff next_of_kin will point next_of_kin REF(person_type) to a row in the person table!) bno VARCHAR(3) NOT NULL) NOT FINAL; CREATE TABLE person OF person_type ( oid REF(person_type) VALUES ARE SYSTEM GENERATED); CREATE TABLE staff OF staff_type ( PRIMARY KEY sno, SCOPE FOR next_of_kin IS person);

ORDBMSs SQL 3 - COLLECTION TYPES SQL 3 COLLECTION TYPES ARE THE EQUIVALENT OF COLLECTION TYPES IN OODBMSs List (base) - ordered collection allows duplicates Array (base) - one-dimensional array, max no. Set (base) - unordered collection, no duplicates Multiset(base) - unordered collection, allows duplicates KNOWN AS A ‘BAG’ IN AN OODBMS!

ORDBMSs SQL 3 - COLLECTION TYPES EXAMPLE CREATE TABLE branch ( bno NUMBER(3), address ROW ( street VARCHAR(15), area VARCHAR(15), city VARCHAR(15) staff SET (STAFF_TYPE)); THE USE OF COLLECTION TYPES WITHIN TABLES ENSURE THAT TABLES NO LONGER HAVE TO BE IN 1 NF REPEATING GROUPS ARE ALLOWED!

ORDBMSs SQL 3 - OTHER ADDITIONS THE MAIN ADDITIONS TO SQL THAT ARE NOT SPECIFICALLY ASSOCIATED WITH ADDING OBJECT-SUPPORT ARE THE FOLLOWING: SQL IS NOW COMPUTATIONALLY COMPLETE PERSISTENT STORED MODULES ARE SUPPORTED TRIGGERS ARE SUPPORTED

OODBMS or ORDBMS CONSIDERATIONS OODBMS - put more emphasis on the role of the client, i. e. , Client side caching! This can radically improve long, process intensive, transactions. ORDBMS - SQL is still the language for data definition, manipulation and query – Still have Impedance Mismatch! OODBMSs have been optimised to directly support objectoriented applications and specific OO languages. ORDBMSs are supported by most of the ‘major players’ in the DBMS market place.

OODBMS or ORDBMS CONSIDERATIONS ORDBMS - Most third-party database tools are written for the relational model (SQL-92), and will therefore be backwardcompatible with SQL 3. ORDBMS - search, access and manipulate complex data types in the database with standard SQL (SQL 3), without breaking the rules of the relational data model. OODBMS – The ODMG standard group’s OQL is now the de -facto query language amongst OODBMS vendors. However, in order to use it, collection objects (known as extents) have to first be created for each class.

OODBMS or ORDBMS CONSIDERATIONS OODBMS - In order to realise the value of an OODBMS you must build your application using methods written in one of several object-oriented language (i. e. C++, Java, Smalltalk).

OODBMS or ORDBMS WHEN TO USE AN OODBMS In applications that generally retrieve relatively few (generally physically large) highly complex objects and work on them for fairly long periods of time. This will necessitate the use of client caching, pointer swizzling, and non-locking forms of concurrency control.

OODBMS or ORDBMS WHEN TO USE AN ORDBMS In applications that process a large number of shortlived (generally ad-hoc query) transactions on data items that can be arbitrarily complex in structure. Where the main emphasises is on efficient query optimisation to limit disk accesses, and traditional concurrency control is acceptable.