CIS 550 Fall 2001

1. CIS 550Fall 2001 Handout 4 Object Oriented Databases

2. ISBN Title Author Keyword Publisher 0-201 Compilers J.D. Ullman Compiler Addison Wesley 0-201 Compilers J.D. Ullman Grammar Addison Wesley 0-201 Compilers A.V. Aho Compiler Addison Wesley 0-201 Compilers A.V. Aho Grammar Addison Wesley 0-201 Compilers J.E. Hopcroft Compiler Addison Wesley 0-201 Compilers J.E. Hopcroft Grammar Addison Wesley Why OODBS? • Consider a relational database in which we have information about Books: • Problem: Redundancy in design is forced by the fact that we cannot have set valued attributes.

3. “Normalized” Design • We could imagine another design: ISBN Title Publisher 0-201 Compilers Addison Wesley ISBN Author ISBN Keyword 0-201 J.D. Ullman 0-201 Compiler 0-201 A.V. Aho 0-201 Grammar 0-201 J.E. Hopcroft

4. Problems with NormalizedDesign • The “object” book has been split over different relations. • Artificial keys are introduced when modeling sets of sets. E.g. Cars can be be bought with certain features (CD, air conditioning, leather seats, etc). For each car, various packages of these features are available, each package being a particular set of features.

5. Problems, cont. • Impossible to represent order in relational data model • While SQL can perform “group-by”, it cannot return a grouped result. E.g. “Give me all the authors together with a list of books written by that author”. • We can call SQL from a “host” PL, but can we call external programs from SQL?

6. The natural solution ISBN Title Authors Keywords Publisher 0-201 Compilers {J.D. Ullman, {Compiler, Addison Wesley A.V. Aho, Grammar} J.E. Hopcroft} • This is an example of the nested relational model, in which values may themselves be sets or tuples. • What we’d like is a type system that allows us freely to combine sets and tuples (non-1NF relations) • This implies new operators: nest and unnest.

7. : (R) UNNEST {Author} Unnest • Given nested relation R on the previous slide, we can unnest on one of the set-valued fields: ISBN Title Author Keyword Publisher 0-201 Compilers J.D. Ullman {Compiler, Addison Wesley Grammar} 0-201 Compilers A.V. Aho {Compiler, Addison Wesley Grammar} 0-201 Compilers J.E. Hopcroft {Compiler, Addison Wesley Grammar}

8. NEST (R) {Author} Nest • We can also and get back the original relation. • But this isn't true in general! Consider the following example: A B 1 {2,3} 1 {3,4}

9. Object-orientation • Complex type system, including atomic types (string, integer, float, char, etc); collection types (sets, bags, lists); and the record type. • Object identity gives identification of objects independent of value. Contrast this with the notion of a key in the relational model. • Values with identity are called objects.

10. Object-orientation, cont. • Methods model the behavior of an object. For example, modifying the value of an object is often dangerous, and is encapsulated by methods (e.g. RaiseSalary and MovePoint). • Types + Identity + Methods are Classes. • Each of these new types gives rise to new operations on those types(as with nest and unnest)

11. Classes, Types and Names • Consider an schema representing persons, employees and consultants. class Person inherit Object public type tuple(name:string, male: boolean, spouse: Person, children: list(Person), dob: Date) public method age: integer end;

12. Schema, cont. class Employee inherit Person public type tuple(salary: integer, subordinates: unique set(Employee)) end; class Consultant inherit Person public type tuple(phone: integer, rate: integer, num_hours:integer) public method earnings: integer end;

13. Schema, cont. type Date tuple(month: integer, day: integer, year: integer) name Persons: unique set(Person) name Employees: unique set(Employee) • Note that Date is a user-defined type, and that Persons and Employees are the only entry-points into the database.

14. Select…from…where select p from p in Persons where not(p.male) Result is of type set(Person) • Print all females in the database. • Print the values of all females in the database. Result is of type set(tuple(name:string, male: boolean, spouse: Person, children: list(Person), dob: Date)) select *p from p in Persons where not(p.male)

15. Select…from…where, cont. • Print the names of all females in the database. Result is of type set(string). select distinct p.name from p in Persons where not(p.male)

16. More queries... • Print the name and birth-date of all females in the database who were born in July. Result is of type set(tuple(name:string, dob: Date)) select struct(name: p.name, dob: p.dob) from p in Persons where not(p.male) and p.dob.month= 7

17. Path expressions select p.spouse.name from p in Persons Result is of type set(string). • Print the names of all spouses. • Print the names and dob of all spouses who were born in June. Result is of type set(tuple(name:string, dob:Date)). select struct(name: p.spouse.name, dob: p.spouse.dob) from p in Persons where p.spouse.dob.month=6

18. Method Invocation • Methods are functions which implicitly have as a parameter “self” and optional additional parameters. None of the example methods have any additional parameters. • Print the names and age of all employees. select struct(name: e.name, age: e.age()) from e in Employees

19. Aggregate Operators • Aggregate operator count operates on collections of elements of any type, min and max operate on collections of ordered types, and sum operates on collections of numbers. • Print the number of Employees. • Note the difference from SQL: count(Employees) Result is of typeinteger. select count(*) from Employees

20. Aggregate operators, cont. max(select count(p.children) from p in Persons) • Print the maximum number of children of any person. • Print the name and names of children of the persons with the maximum number of children. select struct(name: p. name, children: select c.name from c in p.children) from p in Persons where count(p.children)= max(select count(p1.children) from p1 in Person)

21. Unnesting • Print all pairs (manager, employee) of managers in Employees. • Print the names of all subordinates of Joe Brown. select struct(manager: e.name, employee: s.name) from e in Employees, s in e.subordinates where count(e.subordinates) >0 select s.name from e in Employees, s in e.subordinates where e.name="Joe Brown"

22. An incorrect version • Compare the previous query with the following (incorrect) query: • The incorrect query would cause a compile time error because subordinates is of type set(Employee), and you can only use projection over a record type. select e.subordinates.name from e in Employees

23. Flatten • Flatten gets rid of one level of set (collection) nesting, e.g. it takes something of type set(set(element)) and produces something of type set(element) by taking the union of all sets of elements. (This is not the same as the “unnest” operator we have discussed.) flatten({{1,2},{3,4},{1,5}}) = {1,2,3,4,5,1}

24. An example... • Print the names of employees who are supervised by someone. Note that is of type set(set(Employees)). select s.name from s in flatten(select e.supervises from e in Employees) (select e.supervises from e in Employees)

25. Group by • Group by introduces a level of set nesting, and produces something of type set(tuple(label:..., partition: set(...))). • For each salary, print the employees earning that salary. Result is of type set(tuple(salary: integer, partition: set(struct(e:Employee)))). select * from e in Employees group by e.salary

26. Example, cont. • We could also have written the previous query as: select struct(salary: e.salary, partition:(select * from e1 in Employees where e.salary=e1.salary)) from e in Employees

27. Group by … having • “For each salary earned by more than 2 employees, print the set of names of employees earning that salary.” • Result is of type set(tuple(salary:integer, name:set(string))). select struct(salary: e.salary, names: (select e1.name from e1 in partition)) from e in Employee group by e.salary having count(partition)>2

28. Indexing into a list. • Lists are ordered sets, and are treated as arrays indexed starting from 0. We cannot into sets. • “Print all first-born children.” • Result is of type set(Person). Note the explicit test that the list was non-empty. We could also have used count. select p.children[0] from p in Person where p.children<>list()

29. Lists, cont. • “Print the names of all second-born children.” (Recall that list indexing starts with 0.) • Result is of type set(string). select p.children[1].name from p in Person where count(p.children)> 1

30. Sort... in… by • “select...from...where” creates a bag of elements from an input set, “select distinct...from...where” creates a set. To create a list of element as output from an input set, we use “sort...in...by”. • “Print the names and ages of employees by increasing age.” • Result type is list(tuple(name:string, age:integer)). sort x in (select struct(name:e.name, age:e.age()) from e in Employees) by x.age()

31. Creating sets from lists • A list can be converted to a set using listtoset. For example, suppose we want to print each person with children represented as a set rather than a list. • Result type is set(tuple(name:string,children:set(Person)). select struct(name:p.name, children: listtoset(p.children)) from p in Person

32. Set operations: difference • “Print the employees who are supervised by noone.” • Result type is set(Employee). select e from Employees where not(e in flatten(select e1.supervises from e1 in Employees)) OR (select e in Employees) -flatten(select e.supervises from e in Employees)

33. Set operations: union • “Print the names of all people who are either employees or consultants.” • But we cannot do this because “Consultants” is not a named entry point into our database! select e.name from e in Employees + select c.name from c in Consultants

34. Casting • Casting is sometimes necessary to support type inferencing. • For example, suppose we know that all people in our database are either Employees or Consultants: “Print the names of all consultants.” • Result type is set(string).The type of the expression “select (Person)e from e in Employees” is set(Person). select c.name from c in (Persons - (select (Person)e from e in Employees))

35. Casting, cont. • We could also have said • Since Employee inherits Person, the type of the expression would have been the most general type (Person), and Employee would be implicitly cast to a Person. Persons - Employees

36. Casting, cont. select struct(name:p.name, earnings:((Consultant)p).earnings()) from p in (Persons - (select (Person)e from e in Employees)) • “Print names and earnings of consultants.” • Note that is of type set(Person), and that the Person type does not have the method earnings. Persons - (select (Person)e from e in Employees

37. Quantifier Expressions • Expressions “exists” and “for all” also exist in OQL. For example: • “Print the names of people who have had at least one boy.” • “Print the names of people who had all boys.” select struct(name:p.name) from p in Persons where exists c in p.children: c.male select struct(name: p.name) from p in Persons where forall c in p.children: c.male

38. Element • Sometimes we want to turn a collection containing one element into the element type. • Note that this is not always safe, i.e. testing whether a collection has a single element is not something that can be checked at compile time, so a run-time error may occur.

39. Element, an example • “For all people with single-child families, print the person’s name and their child’s name.” • Note that this is safe since we explicitly checked the number of children. select struct(name:p.name, cname: element(select c.name from c in p.children)) from p in Persons where count(p.children)=1

40. Element, another example • “Print the tuple of information about John Kramer.” • If we know “name” is a key for Person, then the following query will be correct: • However, we do not really have the ability to specify keys in so there is no way that this can be checked at compile time! element(select *p from p in Persons where p.name="John Kramer")

41. Summary • OQL is a language for complex object databases. The types include: • base types- integer, string, boolean • tuple type - tuple( ) • the collection types - unique set (set), set (bag) and list • objects (class types) • For each type, basic operations (including constructor and destructor operations) are defined.

42. Summary, cont • For example, • For objects o, we can dereference (*o) and cast ((Person)o). • For tuples, we can project over attributes (e.g. p.name). • For sets, we can select elements, flatten, extract an element from a singleton set, take unions (+ or union), set difference (- or except) and intersection (* or intersect). • For lists, we can extract indexed elements and create lists by specifying an ordering on elements.