Object Relational Databases www.AssignmentPoint.com www.assignmentpoint.com
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
