Chapter 2 - OR Views and Composite Types

Question 1

What is the functionality and the advantages of views?

Answer 1

logical data independence , app-specific representation of DB, authorization (DB way of achieving abstraction)

Question 2

What was added to SQL in order to support Object Views

Answer 2

*Typed views + hirearchies (referenceable views)
* Mapping of table refs to view refs
CREATE VIEW … AS -> ONE typed table or view with same ST of view

Question 3

What are the rules for Object IDs in Object Views?

Answer 3

• System generated not allowed, oid and refs must be cast to compatible ref types
• 1 view hierarchy = 1 table hierarchy (1:1)
  FROM clause of views def. can only refer to one single table (if typed , must have ONLY)

Question 4

How does the view type relate to the type of the base tables?

Answer 4

View type is independent, but OID must follow the rules
* Subviews: Type must be direct subtype of superview type
Base table must be proper subtable of superview base table

Question 5

How are the OR view restrictions handled in DB2?

Answer 5

• Only not needed n FROM
• View hierarchies over many tables
• Static disjointness checking for subviews

Question 6

Is it possible to create a view hierarchy over a single base table? What are the implications?

Answer 6

Yes -> Exploit OR over legacy tables

• > Derive selfref from pk
• > fk: scoped ref

Question 7

What are the supported composite types in SQL and how can they simulate other not supported collection types?

Answer 7

ROW (nullable types)-> —->(UNION -> Heterogeneous elems
ARRAY (not support) —–>(LIST) -> Homogeneous elems, ordered
MULTISET(elim. dup.) —>(SET) -> Homogeneous elems, unordered

Question 8

Give an overview of ROW types in SQL.

Answer 8

DDL type ROW (att-name, att type, ...)
DML constructor ROW (v1, v2, ...)
field access: col-name.att-name
comparison: same numeber of fields, pairwise compatible field types
     -> ordering consider field order

Question 9

How do row types compare to structured types?

Answer 9

ROW types don’t have a name

-> compatibility by structure

Question 10

What is the difference between row types and collections

Answer 10

Coll: all elements are of the same type (ARRAY+MULTISET)

Question 11

How can collections be constructed and accessed?

Answer 11

Constructions: by enumeration (from expression). by query (from table data)
Access: UNNEST table function (index access for arrays)
(Also possible: comparison, assignment, cast)

Question 12

Give an overview over arrays and their use in SQL

Answer 12

• > Characteristics: maximal instead of fixed length, any type
• > Operations: Access by index, cardinality, comparison, construction…
• > DDL: Array [n], dml: ARRAY[v1, v2, …]/Array(query)

Question 13

Give an overview over multisets, their construction and basic operations.

Answer 13

Characteristics: Varying-length, unordered
DDL: Att-name, att-type multiset, DML: MULTISET[v1, v2, …]/MULTISET(QUERY)
Operations: Element (m), SET (m), CARDINALITY(m), UNION, EXCEPT, INTERSECT

Question 14

What is the goal of the UNNEST operator?

Answer 14

Tranform collections into tables (access, iteration, query)

Question 15

What are the different predicates supported for multiset comparison?

Answer 15

Comparison (equal, unequal)

DISTINCT, MEMBER, SUBMULTISET, IS A SET

Question 16

Give an overview over multiset aggregates.

Answer 16

COLLECT: Values in a group transformed in a MULTISET
FUSION: union of multiset in group (UNION ALL)
INTERSECT: Intersect of multisets in group