Stonebraker et al. – Chap. 4

1. Stonebraker et al. – Chap. 4 • Complex Objects • 3 type constructors: • Composites (row types) • Sets (collection types) • References

2. Stonebraker and Brown • Row type • A row – is “…a data type consisting of a record of values…” • “Create row type…” • Then table to hold instances

3. Stonebraker and Brown • So, table – container to hold row types • Different way of thinking

4. Stonebraker and Brown • Can also define each column as instances of a row type

5. Stonebraker and Brown • Sets as a type constructor is useful – • Sets, multisets, lists • Proposed in SQL-3

6. Stonebraker and Brown • References • References (pointers) • Reference to a row, collection, base data type

7. Stonebraker and Bronw • Look at example of row type on p. 63-64 – next slides • Note differences to our usual create table statements. • Hmmmm

8. Using Type Constructors • Create row type phone_t ( area varchar(3), number varchar(7), description varchar(20)); Create row type auto_t ( name varchar(12), year int, license varchar(7)); (continue) From Stonebraker and Brown, page number 62

9. Create row type employee_t ( name varchar(30), startdate date, salary int, address varchar(30), city varchar(30), state char(2), zipcode int); Create table emp of type employee_t; (continue) From Stonebraker and Brown, page number 62

10. Using Type Constructors • Create row type phone_t ( area varchar(3), number varchar(7), description varchar(20)); Create row type auto_t ( name varchar(12), year int, license varchar(7)); (continue) From Stonebraker and Brown, page number 62

11. Create row type dept_t ( dname varchar(30), floor int, manager varchar(30), phone phone_t, autos set(auto_t), manager_ref ref(employee_t) references (emp), colors set (varchar(30)), workers set(ref(employee_t) references (emp))); • Create table dept of type dept_t; From Stonebraker and Brown, page number 62 - 64

12. Stonebraker and Brown • To manipulate row types • Need SQL extensions • Changes needed: • 1. User-def functions –take argument & return result • 2. Function – in FROM • 3. Cascaded dot notation –ref attributes

13. Cascaded Dot Notation • It is useful in the manipulation of rows. • Reference of the column of a table table_name.column_name • Select phone.number from dept where dname = ‘shoe’; • Select dname from dept where phone.area = ‘510’; (continue) From Stonebraker and Brown, page numbers 65 - 66

14. Manipulating Collections of Rows • Select dname from dept where has85(autos); • Functions in the From Clause: create function transport_autos ( ) returns set (auto_t); as select autos from dept where dname = ‘transportation’; (continue) From Stonebraker and Brown, page number 66

15. Cascaded Dot Notation select dname from dept Where 1985 in autos.year; • Select dname from dept where 1985 in autos.year and ‘Ford’ in autos.name; • To accomplish the same thing: select dname from dept where autos.(year=1985 and name=‘Ford’); (continue) From Stonebraker and Brown, page number 67

16. Stonebraker and Brown • Use references – pointers • Replace foreign key with pointer to another table • So, a column can contain a reference to an instance of a row type stored in another table • Significant departure from relational

17. Using References: select e.startdate from emp e, dept d where e.name = d.manager and d.dname = ‘shoe’; • Select manager_ref from dept where dname = ‘shoe’; From Stonebraker and Brown, page number 68 - 69

18. Stonebraker and Brown • OID is the pointer • So, SELECT will return pointer (OID) • Have to deref if want value you are seeking • Result of deref is a row of data • Advantages and disadvantages for references

19. Stonebraker and Brown • Manipulating collections of rows • Want to put functions in FROM clause • Could use cascaded dot notation • See p. 66-67 – previous slides • Want to manipulate sets the way manipulate rows

20. Stonebraker and Brown • Small group • Use the previous example – replace one of your foreign keys with a reference • So, create a type and table with a reference • Then give sample DML to retrieve information from it.

21. Stonebraker and Brown • Good O-R DBMS allows– • 1. New base types and functions • 2. New complex objects and functions • Supports these 2 type systems for- • Naturalness, encapsulation, OIDs, and data conversion/ordering

22. Stonebraker and Brown • Naturalness – easy for user to understand • Encapsulation – • OIDs – references only available for complex types • Data conversion and ordering – cast functions support format conversion

23. Stonebraker and Brown • Summary chap. 4 – • Feature 1 – rich collection of complex types must be supported • Feature 2 – functions for all complex types • Feature 3 – no limit to size of complex types • Feature 4 – SQL support for complex types

24. Start Stonebraker and Brown – chap 6 • Inheritance • Applies only to composite types • Sample p. 87 and 88 – next slides • Notice “under” key word

25. Data Inheritance • Create type person_t ( name varchar(30)); • Create type student_t ( gpa float) under person_t; (continue) From Stonebraker and Brown, page number 87 - 88

26. Create type employee_t ( salary int, startdate date, address varchar(30), city varchar(30), state varchar(30), zipcode int) under person_t; From Stonebraker and Brown, page number 87 - 88

27. Stonebraker and Brown • Allows you to group composite types into an inheritance hierarchy • Still need tables for types • Allows subtype to inherit data elements from supertypes • Function inheritance also

28. Stonebraker and Brown • Summary – • Feature 1 – data and function inheritance • Feature 2 – support for overloading • Feature 3 – types and tables must be separate concepts • Feature 4 – support for multiple inheritance

29. Stonebraker and Brown • Look at your design – • Where could some O-R features be used? • Give an example of a base data type extension, complex object, and inheritance • Write the SQL DDL for one of them. • Write the SQL DML to retrieve it