Exam Flashcards

1
Q

What are the basic OO features that must be supported in an ORDBMS?

A
  • Object identity
  • classes or types
  • inheritance
  • encapsulation
  • overriding + late binding
  • complex objects
  • completeness (for method implementations)
  • extensibility
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the different ways in which objects (i.e. instances of structured types) may exist?

A
  • > 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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the meaning of subtype substitutability and how is it realized in an ORDBMS?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are typed tables and what characteristics do they provide to objects?

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How can we modify the state of objects in typed tables?

A

modify obj attributes by updating column values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How do table hierarchies relate to type hierarchies?

A

(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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How is the concept of object identity supported in ORDBMSs ?

A

(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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the different forms of equality and how are they used in SQL?

A
  • > 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the goal of UDT ordering and the different types of comparison supported?

A

-> 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…]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What was added to SQL in order to support Object Views?

A

-> 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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

ROW [nullable types] -> (union)-> heterogenous elem
ARRAY [not support]-> LIST-> homogeneous elems, ordered
MULTISET [eli. dupl.] -> SET -> homogeneous elems, unordered

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can collections be constructed and accessed?

A
  • > Construction: by enumeration (from expression), by query (from table data)
  • > access: UNNEST table function (index access for array)
  • > (also possible comparison, assignment, cast)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Give an overview over arrays and their use in SQL.

A
  • > characteristics: maximal instead of fixed lenght, any type
  • > operations: access by index, cardinality, comparison, construction…
  • > DDL: ARRAY[n], DML: ARRAY[v1, v2, …] / ARRAY[query]
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the general idea and the different kinds of User-defined Routines?

A
  • 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)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are the differences between procedures and functions in SQL?

A
  • > 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)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Which additional functionality is provided by table functions?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Compare table functions and views as mechanisms for virtual tables?

A

TFs support dynamic parameters

20
Q

What is the general idea and the algorithm for subject routine determination in SQL?

A
  • > 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.
21
Q

What are methods in SQL and how do they differ from functions?

A

-> 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;

22
Q

How does subject routine determination work for instance methods?

A
  • > 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
23
Q

Give an overview of how the modification of object state works in SQL.

A
  • > Values of STs can’t be modified, just copied through mutators (no side-effects)
  • > Rows of typed tables are modified with UPDATE statement
24
Q

How does SQL support object encapsulation?

A
  • > State is only accessible through methods (separate iface/impl.)
  • > Public interface definition not supported
  • > Implemented through privileges
25
Q

Give an overview of the functionality provided by SQLJ part. 1

A

Implement SQL routines using java (LANGUAGE JAVA PARAMETER STYLE JAVA)

  • > JAR file as a database object
  • > SQL/JRT(2003)
  • > arguments must be SQL data type
26
Q

What is the functionality provided by SQLJ part 2?

A
  • > Define SQL types using java (Java class -> SQL UDT)
  • > based on install_jar
  • > mapping state + behavior
  • > Automatic mapping to java object on fetch/method invocation (Java serializ., JDBS SQL data interface)
27
Q

Explain the idea behind locators as an approach to external access to structured types.

A
  • > Reference to SQL data item inside DB , which can be used in statements
  • > Generated by SQL engine and transferred to app (NOT an SQL data type*** /DB level concept)
    • Introduced for LOBs
  • > specify host variable AS LOCATOR -> impl. dependent integer pointer
  • ** but types can be specified as locators
28
Q

Give an overview over Transforms

A
  • > UDFs invoked automatically when UDT values are exchanged between SQL/app
  • > associate UDT with transform groups (pars of functions from-SQL to-SQL)
  • maps predefined value
29
Q

Give an overview of complex value transfers.

A
  • Uses transparent proprietary format, supported jointly by DB+API(JDBC e.g.)
  • Generic object structures on app side (e.g. getString(1)
30
Q

Compare the advantages and disadvantages of each approach.

A
  • > Locators:
    • Values remains in DB, avoids unnecessary transformation/transfer (+)
    • Available for collections (+)
    • Manipulation of values restricted to SQL operations (-)
  • > Transforms:
    • Flexible: tailored UDT eexchange, accomodate existing formats (+)
    • additional effort: transform functions, parsing/generation (-)
  • > Complex-value transfer:
    • generic representation for dynamic apps (+)
    • allows user-defined mapping; improved integration (+)
    • potential development impact (~)
    • only for java (-)
31
Q

What are the main goals of OLAP?

A
  • Decision support to knowledge worker (analyse company data to discover patterns/trends)
  • based on integrated data warehouse [separate from OLTP systems -> periodic refresh)
    • > Organize and centralize corporate data (historical) from various heterogeneous sources (ETL)
32
Q

What is a Data Warehouse and its basic architecture?

A

Separate datastore modeled by multidimensional historical data from various sources, supporting OLAP.
SOURCES -> ETL -> WAREHOUSE -> TOOLS

33
Q

Explain the goals of multidimensional Data Modeling.

A
  • > set of numerical measures associated to a combination of (hierarchical) dimensions
  • > Dimensions group and qualify numerical fact data, aggregating measures on the groups

MOLAP (specific storage for multidimensional data) vs ROLAP (relies on RDBMS capabilities, enhanced SQL).

34
Q

What is the role of the multidimensional cube in OLAP and its supported operations?

A
  • > way to arrange/interpret multidimensional data: each dimension in an axis
    • > points contain measures (cube cells)
  • > rollup/drill-down: increase/decrease level of aggregation (day month, country city)
  • > Slice/dice: selection and projection (fix one dimension/restrict (internal) one or more dim.)
  • > Split/Merge: rem/add dimensions.
35
Q

What are the main concepts of Multidimensional Data Modeling?

A

MD Schema: set of dimensions + set of measures (of a cube)

**dimensions: partially ordered set of category attributes (primary(finest granule), classification (hierarchy), dimension(additional info))

36
Q

Explain and compare the terms Star Schema and Snowflake Schema.

A

Ways of modeling MD data in RDBMS
Star:
* fact table on the center has pointers to dimension tables and associated measures
* dimension attributes on dimension table -> more performance
Snowflake:
* Dimension tables are normalized, reflecting dimension hierarchy
* less redundant

37
Q

What is the idea of Windowed Table functions and how do they compare with traditional set functions?

A
  • Operates on a Window of table, calculating one value for each row based on the other rows in that Window (moving/cumulative aggreegate values) -> tuple-based aggregation.
  • Grouping with set functions -> one result per group
38
Q

Explain the concept of window table, window and window function.

A
  • Table can have multiple independent windows associated
  • Window: defines for each row, a set of related rows used to compute additional atts
  • Window function: applied over each row, together with other rows in the win, returning single value.
39
Q

What are the elements of a window definition and how are they expressed in SQL?

A

Window partitioning, window ordering, window frame
- Window partitioning: Forming groups, but rows are retained.
- Window ordering: defines order of rows within partition
- Window frame: Defined relative to each row, further restricts set of rows
SELECT… FUNCTION(column) OVER (PARTITION BY expr1 ORDER BY expr2 (ROWS/RANGE)

40
Q

What are the goals and main characteristics of XML?

A
  • original intent: document markup language (tags = extra information)
  • separate document content from structure: describe documents for interchange
  • meta-language: language for defining other languages
  • markup = metadata at specific parts (instance-level): self-describing documents
    • > schema (optional) = “global metadata (vocabulary, strcture, allowed content …)
41
Q

Overview XML schema.

A

(class of documents -> instance documents conforms to it)

  • closer to general understanding of a database schema. Add. to DTD, supports:
    • > typing/constraints of values, typed references, UDTs, XML syntax, namespaces, list types, inheritance, unique, foreign key… -> more complicated than DTD
42
Q

How can XML schema map ER models with 1:n and n:m relationships?

A
  • > entities = XML elements (xsd:key simulates ER keys)
  • > 1:n relationships: nesting N subelements inside (sequence)
    • > by local element definition (element name=””) or global (elment ref=””)
    • > requires xsd:key/xsd:keyref for uniqueness + referential integrity
      • > nesting alone insufficient
  • > n:m relationships: key+keyref in helper element (similar to relational table)
    • > flat modelling with pointers
43
Q

Overview of Xquery data model

A
  • Free model of XML data: document is a tree, nodes are elements, attributes, text
  • XDM is an extension: supports sequences of items (collection of nodes/atomic values)
    • > intermediate and final results of query evaluation (closure property)
    • > unnested, heterogeneous, typed items (XML schema)
  • Nodes: have identity, 7 types, document order
44
Q

Path Expressions, their components and evaluation mechanism

A
  • maps a context node to a sequence of nodes (initial/ implies document root)
  • consist of a sequence of steps, each one containing:
    • > AXIS: direction of navigation (target nodes) ->result in document order
    • > Node test: type/name of qualifying nodes
      • name test: element, attribute name, wildcard, namespace (ns:elem)
      • type test: only nodes of specific type -> element( ), attribute( ), text( ), node( )
        • support (name) and (name,type)
  • Predicate (optional): filters the set of qualifying nodes (syntax: [ ])
    • > boolean expression: selects nodes for which it evaluates to true
    • > numeric expression: selects the node in a specific position
    • > existence test: nodes for which expression does not evaluate to empty seq
      • does not test value! person[@married] returns persons with attr “married”, regardless of its value
  • Evaluation: step by step, from left to right, starting from external ctx or doc root
    • > sequence of each step becomes context for next step
      • iterate over input: sets each node as context and evaluates step
    • > sort by document order, eliminating duplicates (distinct-document-order)
      • empty sequence as result allowed
45
Q

What is the structure of FLOWR expressions and how are they evaluated?

A
  • FOR and LET bind expressions to variables, creating a tuple stream
    • > FOR iterates over values in the sequence
  • WHERE filters the stream by evaluating an expression on the bound vars
  • ORDER BY sorts the filtered stream
  • RETURN applies an expression to construct the desired output
    • > each tuple in the stream becomes an item in the result sequence
46
Q

What is the motivation and the different scenarios of SQL/XML integration?

A
  • goals: flexible exchange between relational data and XML, reliable XML management
    • > native XML DBMS not mature enough
  • simple reuse of OR capabilities is not sufficient
    • > LOBs for XML storate: coarse granularity
    • > decompose into tables: complex and inefficient
  • hybrid relational /XML data management: storage, acces, query, APIs
    • > view/ access relational as XML/ XML as relational