Exam Flashcards
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