Object Relational Databases

1. Object Relational Databases www.AssignmentPoint.com www.assignmentpoint.com

2. Motivation • The use of database systems has become widespread. • e.g. Multimedia databases, CAD, Hypertext databases • More and more demands are being placed on these systems. • Relational systems are not suited to meet some of these demands e.g. complex data types. • The object-oriented community was growing therefore it was natural to try and apply some of its features to the database community. www.assignmentpoint.com

3. Object-Oriented vs Object-Relational Databases • Object-Oriented Databases • Extend OO programming to include features required for database systems e.g. persistent objects. • Object-Relational Databases • Extend the relational model to include useful features from object-orientation e.g complex types. • Add constructs to relational query languages e.g. SQL to deal with these extensions. www.assignmentpoint.com

4. Object-Relational Databases • Extends relational databases to include useful object-oriented features. • e.g. better data type support • Must extend the query language (SQL) in order to manipulate this data. • Relational systems require that databases be in 1NF. • i.e. each attribute value must be atomic. • These normalisation requirements will have to be lifted. www.assignmentpoint.com

5. Violating 1NF title author-list publisher keyword-list (name, branch) Compilers {Smith, Jones} (McGraw-Hill, NY) {parsing, analysis} Networks {Jones, Frick} (Oxford,London) {Internet, Web} title author pub-name pub-branch keyword Compliers Smith McGraw-Hill NY parsing Compliers Jones McGraw-Hill NY parsing Compliers Smith McGraw-Hill NY analysis Compliers Jones McGraw-Hill NY analysis Networks Jones Oxford London Internet Networks Frick Oxford London Internet Networks Jones Oxford London Web Networks Frick Oxford London Web • title ->> author • title ->> keyword • title -> pub-name, pub-branch www.assignmentpoint.com

6. Decomposing into 4NF Authors title author Compilers Smith Compilers Jones Networks Jones Networks Frick Keywords title keyword Compilers parsing Compilers analysis Networks Internet Networks Web Books4 title pub-name pub-branch Compilers McGraw-Hill NY Networks Oxford London • Users then need to join tables to retrieve information from more than one table. • Non-decomposed table may be more natural for user. www.assignmentpoint.com

7. Structured and Collection Types • Consider the following, to define the relation books: create typePublisher as (namevarchar(20), branch varchar(20)) create typeBook as (titlevarchar (20), author-arrayvarchar(20) array[10], pub-datedate, publisher Publisher, keyword-setsetof(varchar(20))) create tablebooksof Book • Differs from relational databases as attributes can be sets, arrays and attributes can be composite. www.assignmentpoint.com

8. Structured and Collection Types • Book table could be created without books type: create tablebooks (titlevarchar (20), author-arrayvarchar(20) array[10], pub-datedate, publisher Publisher, keyword-setsetof(varchar(20))) www.assignmentpoint.com

9. Inheritance can be at the level of types or the level of tables. Inheritance at the level of types: create typePerson (name varchar(20), address varchar(20)) create typeStudentcreate type Teacher under Person under Person (degree varchar(20), (salary integer, department varchar(20)) department varchar(20),) Student and Teacher are subtypes of Person. Inheritance – Types & Tables www.assignmentpoint.com

10. Multiple Inheritance • Consider the following: create type TeachingAssistant under Student, Teacher • Department is inherited from both Student and Teacher so there is some ambiguity. • Department in Teacher is department teaches in. • Department in Student is department taught in. • Renaming should be used to avoid this: create type TeachingAssistant under Student with (department as student-dept), Teacher with (department as teacher-dept) www.assignmentpoint.com

11. Inheritance - Tables create tablepeopleofPerson create tablestudents ofStudent under people create tableteachers of Teacher under people create table teaching-assistants ofTeaching Assistant under students, teachers • In relational systems, sub and super tables would have to be linked directly using primary keys. • Each tuple in the subtable (e.g. students, teachers) is implicitly present in its supertable (e.g. people) www.assignmentpoint.com

12. Querying with Complex Types • Composite Attributes • E.g. Find the title and name of publisher for each book: select title, publisher. name frombooks • Set-valued attributes • E.g. Find all the books that have “database” as one of their keywords. select title from books where ‘database’ in (unnest(keyword-set)) www.assignmentpoint.com

13. Querying (con’t) • Arrays • E.g. Find the three authors of the “Database System Concepts book. selectauthor-array[1], author-array[2],author-array[3] frombooks where title = ‘Database System Concepts’ • E.g. Find title, author pairs for each book. selectB.title, A frombooksas B, unnest(B.author-array) as A • author-array is a collection-valued field, can be used in from clause. www.assignmentpoint.com

14. Nesting and Unnesting • Nesting: Transforming a nested relation into 1NF. • e.g. select name, A as author, date.day, date.month, date.year, K as keyword from doc as B, B.author-list as A, B.keyword-list as K • Unnesting: Transforming 1NF relation into nested relation. • e.g. select title,set(author) as author-set, Publisher(pub-name,pub-branch) as publisher, set(keyword) as keyword-set from flat-books group by title, publisher where flat-books is the 1NF version of the table. www.assignmentpoint.com

15. Functions • Functions can be defined by users. • Use either a programming language or DML e.g. SQL • e.g. An SQL (extended) function that given a document returns the number of authors. create function author-count(title varchar(20)) returns integer begin declare a-countinteger; selectcount(author) into a-count from authors where authors.title = title return a-count end • Query: Find the name of all documents that have more than one author. select title from books4 where author-count(title) > 1 www.assignmentpoint.com

16. Creating Objects and Complex Values • Inserting a tuple into relation books. insert intobooksvalues (‘Compliers’, array[‘Smith’, ‘Jones],Publisher(‘McGraw-Hill’,’NY’), set(‘parsing’, ‘analysis’)) • composite attributes: use parenthesis • set valued attributes : use keyword set and () • arrays: use keyword array and [] • Creating a type e.g. Publisher. create functionPublisher (n varchar(20), b varchar(20)) returnsPublisher begin set name = n; set branch = b; end www.assignmentpoint.com