Object Oriented Databases

1. Object Oriented Databases Bringing objects into databases

2. An object database schema ADDRESS Street: String City: String PERSON SS#: String Name: String Address: Address EMPLOYEE ISA PERSON Salary: Integer Boss: Employee Departments: {Department} COMPANY Name: String Head quarters: Address Departments: {Department} CEO: Employee DEPARTMENT Name: String Office: Address Boss: Employee Employees: {Employee}

3. An object database query SELECT e.Name FROM e in EMPLOYEE, d in DEPARTMENT WHERE e in d.Employees AND e.Address.City = d.Office.City

4. Object database concepts Car Person [ss#:String,age:Integer] Classes Types <#11, {25,7}> Objects {25,7} [a:1,b:2] Complex values ‘bo’ #11 Simple values 25

5. Simple and complex values A is the set of all attributes O is the set of all object identifiers D is the union of integers, reals, strings, and booleans Elements in O U D are called simple values Elements of the form [A1:w1, …, An:wn] are tuple valueswhere Ai is an attribute and wi a value Elements of the form {w1, …, wn} are set values A complex value is a tuple value or a set value

6. Objects An object is a pair (o, w) o is an object identifier w is a value

7. Examples of objects A = {SS#, Name, Address, Main office, Departments, CEO} O = {#11, #12, #13, #14, #21, #31, #41} • (#11, [SS#: ‘420101-1100’, Name: ‘Bo Ek’, Address: [Street: ‘Storg. 4’, Town: ‘Karlstad’]]) • (#14,[Name: ‘Acme’,Head office: [Street: ‘Storg. 12’, Town: ‘Karlstad’],Departments: {#21, #31, #41},CEO: #11])

8. Value and object equality • Value equalityTwo objects are equal if their values are equal(o1, w1) = (o2, w2) iff w1 = w2 • Object equalityTwo objects are equal if their object identifiers are the same(o1, w1) = = (o2, w2) iff o1 = o2

9. Surface and deep equality Surface equality: equality without substituting object references Deep equality: equality after substituting object references by values w1 = [A: o1] w2 = [A: o2] (o1, w) (o2, w) where o1 o2 w1 not surface equal to w2 w1 deep equal to w2

10. Surface and deep equality w1 = [A: o1, B: 25] w2 = [A: o2, B: o3] w3 = [A: o2, B: o4] (o1, 25) (o2, 50) (o3, 25) (o4, o1) w1 deep equal to w2 ?w1 deep equal to w3?w2 deep equal to w3?

11. Types and classes Intuitions: • Values with the same structure are described by a type • Object identifiers for similar objects are grouped into a class A class can be associated with a type to specify the structure of the objects of the class A class can be used as a type for typing references between objects

12. Types Let K be a set of classes. • Base types: {integer, float, string, boolean} • Reference types: K • Tuple types: elements of the form[A1:T1, …, An:Tn], where Ai is an attribute and Ti en typa type • Set types: {T}, where T is a type A complex type is a tuple type or a set type

13. An example type K = {Car, Company, City} EMPLOYEE SS#: Integer Name: String Lives in: City Works at: Company Owns: {Car} Tuple type Base type Reference type Set type

14. Object database schema An object database schema consists of: • A set of class names K • For each k in K, a type that specifies the structure of k

15. Object database extension • Given an object database schema S, an extension for S associates each k in K with a set of object identifiers

16. An example extension #1 #2 #3 #4 K #5 Animal Bird #4 #5 Mammal #1 #2 Dog #3 #1 #2

17. Types, classes, objects,and values [Pnr: String, Namn: String, Lön: Integer] K Types Person [Regnr: String, Färg: String, Ägare: Person] Bil [Pnr: ‘5501010415’, Namn: ‘Per Svensson’, Lön: 25000] #11 #12 [Regnr: ‘ABC123’, Färg: ‘Röd’, Ägare: #11] #21 O #22 W

18. Query languages Viktiga egenskaper • Allmängiltighet • Deklarativitet • Optimerbarhet • Slutenhet • Uttryckskraft • Utvidgningsbarhet

19. Two simple queries SELECT a FROM a in EMPLOYEE WHERE Salary > 20000 SELECT SS#, Name FROM EMPLOYEE WHERE Salary > 20000

20. Path expressions (dot notation) SELECT Name FROM Company WHERE Head_quarters.City = “London” SELECT Name, Head_quarters.Street FROM Company WHERE Head_quarters.City = “London”

21. Sets in queries What does this query mean? SELECT c FROM c in COMPANY WHERE CEO IN (SELECT e FROM e in EMPLOYEE WHERE Employee.Address.City = “Lund”)

22. Sets in queries SELECT c.Name FROM c in COMPANY WHERE c.Departments.Office.City = {“London”, “Lund”}

23. Sets and dot notation SELECT c.Departments.Employees.Salary FROM c in COMPANY c.Departments.Employees.Salary {f1, f2, f3} {a1, a2} {a3, a4} {a5} 100 200 150 100 150 {f1, f2, f3} {{a1, a2}, {a3, a4}, {a5}} ? {a1, a2, a3, a4, a5} ?

24. Sets and dot notation SELECT c.Departments.Employees.Salary FROM c in COMPANY {f1, f2, f3} {a1, a2} {a3, a4} {a5} 100 200 150 100 150 Answer: {100, 150, 200}

25. Exercises • Which employees work at the same departments as their bosses? • Which (pair of) employees work at the same departments? • Which companies do not have any employee living in London? • In which companies do all employees live in London?

26. Exercises • Which employees work only at departments that are situated in London? • Which employees do not work at a department in London? These queries become harder if the attribute “Departments” is removed. Try to write the queries also without this attribute.

27. Exercises • In which companies are there employees who live in cities where the company does not have any department? • In which companies does the boss of each employee live in the same city as the employee?

28. Which employees work at the same departments as their bosses?

29. Which (pair of) employees work at the same departments?

30. Which companies do not have any employee living in London?

31. In which companies do all employees live in London?

32. Which employees work only at departments that are situated in London?

33. Which employees do not work at a department in London?

34. In which companies are there employees who live in cities where the company does not have any department?

35. In which companies does the boss of each employee live in the same city as the employee?

36. Which employees work at the same departments as their bosses? SELECT e.Name FROM e in EMPLOYEE WHERE e.Departments = e.Boss.Departments

37. Which (pair of) employees work at the same departments? SELECT e1.Name, e2.Name FROM e1 in EMPLOYEE, e2 in EMPLOYEE WHERE e1.Departments = e2.Departments

38. Which companies do not have any employee living in London? SELECT c FROM c in COMPANY WHERE ‘London’ NOT IN c.Departments.Employees.Address.City

39. In which companies do all employees live in London? SELECT c FROM c in COMPANY WHERE c.Departments.Employees.Address.City = {‘London’}

40. Which employees work only at departments that are situated in London? SELECT e.Name FROM e IN EMPLOYEE WHERE NOT EXISTS (SELECT d FROM DEPARTMENT WHERE e in d.Employees AND d.Office.City <> ‘London’) SELECT e.Name FROM e in EMPLOYEE WHERE e.Departments.Office.City = {‘London’}

41. Which employees do not work at a department in London? SELECT e.Name FROM e in EMPLOYEE WHERE NOT EXISTS (SELECT d FROM d in DEPARTMENT WHERE e in d.Employees AND d.Office.City = ‘London’) SELECT e.Name FROM e in EMPLOYEE WHERE ‘London’ NOT IN e.Departments.Office.City

42. In which companies are there employees who live in cities where the company does not have any department? SELECT c FROM c IN COMPANY WHERE c.Departments.Employees.Address.City MINUS c.Departments.Office.City <> Ø

43. In which companies does the boss of each employee live in the same city as the employee? SELECT c FROM c in COMPANY WHERE NOT EXISTS (SELECT e FROM e in EMPLOYEE WHERE e IN c.Departments.Employees AND e.Boss.Address.City <> e.Address.City)

44. Which employees work at a department in a city where they do not live? SELECT e.Name FROM e in EMPLOYEE, d in DEPARTMENT WHERE e in d.Employees AND d.Office.City <> e.Address.City

45. Which employees live in the same city as (at least one of) their boss(es)? SELECT e.Name FROM e in EMPLOYEE, d in DEPARTMENT WHERE e in d.Employees AND e.Address.City = d.Boss.Address.City