What are the basic OO features that must be supported in an ORDBMS?
- Object identity
- classes or types
- inheritance
- encapsulation
- overriding + late binding
- complex objects
- completeness (for method implementations)
- extensibility
What are the different ways in which objects (i.e. instances of structured types) may exist?
- > object as values: values of a ST (columns, attr. of other ST, domains, variables, expressions)
- No identity, no state modifications
-> objects as rows: in typed tables (oid column = identity, UPDATE statement = modification)
What is the meaning of subtype substitutability and how is it realized in an ORDBMS?
B < A : B supports at least the operations defined in A. B can be used everywhere (variable assignments; value of expressions; arguments of methods) an A is expected.
What are typed tables and what characteristics do they provide to objects?
Tables whose columns are attributes of an ST and rows are objects with an identity
-> provide identity (additional self-referencing column) + persistence + update capability
(create table … under = possible)
How can we modify the state of objects in typed tables?
modify obj attributes by updating column values
How do table hierarchies relate to type hierarchies?
(table inherits constraints, triggers, etc)
- > typed table can be declared under another typed table of a super type
- > table of A under table of B -> A under B
- > not all types in hierarchy must have a table
How is the concept of object identity supported in ORDBMSs ?
(identity -> existence != values) (sharing, updates, references …)
- > Primary keys not enough: modifiable, not uniform across tables, require join to navigate into obj
- > solution: oid column (pk) maintained by system + reference types
What are the different forms of equality and how are they used in SQL?
- > shallow-equality of objects = reference attributes compared by identity
- > Deep-equality = derefs nested objects, comparing atomic values by deep equality
- for both: require to create an ordering
What is the goal of UDT ordering and the different types of comparison supported?
-> goal: allow comparison (equality) of objects
-> types: EQUALS ONLZ(=, !=)ORDER FULL(all)
CREATE ORDERING FOR type [EQUALS ONLY | ORDER FULL] BY [STATE|[MAP|RELATIVE] WITH FUNCTION…]
What was added to SQL in order to support Object Views?
-> typed views + hierarchies (aka referenceable views)
-> mapping of table refs to view refs
CREATE VIEW … AS -> ONE typed table or view with same ST of view
How does the view type relate to the type of the base tables?
view type is independent, but OID must follow the rules
-> subviews: tupe must be direct subtype of superview type
base table must be proper subtable of superview base table
What are the supported composite types in SQL and how can they simulate other not supported collections types?
ROW [nullable types] -> (union)-> heterogenous elem
ARRAY [not support]-> LIST-> homogeneous elems, ordered
MULTISET [eli. dupl.] -> SET -> homogeneous elems, unordered
How can collections be constructed and accessed?
- > Construction: by enumeration (from expression), by query (from table data)
- > access: UNNEST table function (index access for array)
- > (also possible comparison, assignment, cast)
Give an overview over arrays and their use in SQL.
- > characteristics: maximal instead of fixed lenght, any type
- > operations: access by index, cardinality, comparison, construction…
- > DDL: ARRAY[n], DML: ARRAY[v1, v2, …] / ARRAY[query]
Give an overview over multisets, their construction and basic operations.
-> Characteristics: Varying-length, unordered
DDL: Att-name att-type MULTISET, DML: MULTISET[v1, v2, …]/MULTISET(query(1 column might be a ROW))
-> Operations: ELEMENT (m), SET (m) (elim dup.), CARDINALITY(m), UNION, EXCEPT, INTERSECT
What is the general idea and the different kinds of User-defined Routines?
- Named persisted code to be invoked in SQL (PSM/external)
- Integrated in schema, CREATE/ALTER/DROP, privileges
- Procedure (CALL statement), function (expression), method (type function)
What are the differences between procedures and functions in SQL?
- > Procedure -> Statement context
- OUT/INOUT parameters: modified by procedure and mode available to caller afterwords
- Dynamic Result Sets: Declare and return cursors
- > Function -> Expression context
- returns (mandatory) (no result sets)
Which additional functionality is provided by table functions?
Transform non-relational data on relational table dynamically
Returns a table (multiset of rows)
-> RETURNS TABLE (name type, name type, …)
-> RETURNS TABLE (SELECT… FROM…)
Used in FROM clause
-> SELECT … FROM TABLE (f(a,b) AS
Compare table functions and views as mechanisms for virtual tables?
TFs support dynamic parameters
What is the general idea and the algorithm for subject routine determination in SQL?
- > Decide which routine to invoke based on: Static types of args, type precedence list (subtype relation for STs) schema path, EXECUTE privilege
- > Algorithm: 1. set of candidates (name+ num of args) from all PATH if not fully qualified. 2. Eliminate unprivileged and incompatible arg types (not in precedence list). 3. Select best match from left to right. 4. Select first on PATH.
What are methods in SQL and how do they differ from functions?
-> SQL functions belonging to a structured type.
diffs:
-> Implicit SELF parameter (subject parameter)
-> Separate body and signature def (same schema as ST)
-> Invoked with dot notation: UDT-value.method()
CREATE TYPE T AS (…)
METHOD in RETURNS type;
CREATE METHOD m FOR T
BEGIN … END;
How does subject routine determination work for instance methods?
- > Just like for functions, but setting PATH to list of supertype schemas
- > Dynamic dispatch: decide between overriding methods based on dynamic type of SELF.
- Schema evolution affects actual invoked method
Give an overview of how the modification of object state works in SQL.
- > Values of STs can’t be modified, just copied through mutators (no side-effects)
- > Rows of typed tables are modified with UPDATE statement
How does SQL support object encapsulation?
- > State is only accessible through methods (separate iface/impl.)
- > Public interface definition not supported
- > Implemented through privileges
-
Chapter 1 - User-defined types and typed tables31
-
Chapter 2 - OR Views and Composite Types16
-
Chapter 3 - User-defined Routines and Object Behavior29
-
Chapter4 -> Application Programs and OR Capabilities11
-
5 - Data Analysis in SQL13
-
Chapter 6 - Window Tables and Window Functions in SQL15
-
Chapter 7 - XML Data Modelling15
-
Chapter 8 - XQUERY10
-
Chapter 9 - SQL/XML7
-
Exam46
