Give the two reasons a database programmer needs a general-purpose programming language.
Not all queries can be expressed in SQL (it lacks the full expressive power of a general-purpose language); non-declarative actions (printing a report, interacting with a user, sending results to a GUI) cannot be done from within SQL.
Which three calls does an application program make through a database API?
Connect with the database server; send SQL commands to the database server; fetch tuples of result one-by-one into program variables.
ODBC vs JDBC: which languages does each work with?
ODBC works with C, C++, C#, and Visual Basic (ADO.NET sits on top of ODBC). JDBC works with Java.
What does the ODBC standard let an application do?
Communicate with a database server: open a connection with a database, send queries and updates, get back results.
What is JDBC, and what is its model for communicating with the database?
A Java API for communicating with database systems supporting SQL. Model: open a connection; create a "statement" object; execute queries using the Statement object to send queries and fetch results; exception mechanism to handle errors.
What is special about resources opened in "try ( … )" (try with resources)?
They are automatically closed at the end of the try block (Java 7, JDBC 4 onwards).
executeUpdate vs executeQuery.
executeUpdate runs an update such as an insert; executeQuery runs a select and returns a ResultSet, read row by row with rset.next().
How do you detect a null value read from a JDBC ResultSet?
Read the value (e.g., int a = rset.getInt("a");) then call rset.wasNull().
Why must you use a PreparedStatement when adding user input to a query?
Slide 13: always use preparedStatements while reading input from the user; NEVER create a query by concatenating strings — e.g., a name such as "D'Souza" breaks a concatenated query.
ResultSet vs ResultSetMetaData.
ResultSet holds the actual data (rows); ResultSetMetaData provides the structural data (metadata) about those records — structure, types, and properties of the columns.
In DatabaseMetaData calls, what do null, "" and "%" mean?
null: all catalogs/schemas; "": the current catalog/schema; "%": the same meaning as the SQL like clause.
What does KEY_SEQ give in getPrimaryKeys?
The position of the attribute in the primary key — required if a primary key has multiple attributes.
What is JDBC's default transaction behaviour called, and what does it mean?
"Automatic commit": each SQL statement is treated as a separate transaction, executed and committed automatically.
How do you run several statements as one transaction in JDBC?
conn.setAutoCommit(false); execute the statements; then conn.commit() to save or conn.rollback() to discard; setAutoCommit(true) restores the default.
What is embedded SQL?
Including SQL statements directly within a program written in a high-level programming language like C, C++, COBOL, or Java.
In embedded SQL, how are host variables declared and used?
Declared between EXEC SQL BEGIN DECLARE SECTION; and EXEC SQL END DECLARE SECTION;, and used inside SQL with a colon, e.g., VALUES (:employee_id, :name).
Which SQL standard introduced functions and procedures, and which added table functions?
SQL:1999 introduced functions and procedures; SQL:2003 added functions that return a relation as a result.
returns vs return in an SQL function.
returns indicates the variable-type that is returned (e.g., integer); return specifies the values that are returned as the result of invoking the function.
What is a stored procedure, and what does it provide?
Precompiled SQL code that can be saved and reused (with parameters). It provides code reusability, improved performance, and database security.
dept_count_proc(in dept_name, out d_count): how do you run it, and what are the two parameter kinds?
With the call statement (from an SQL procedure or embedded SQL), e.g., call dept_count_proc('Physics', d_count); in = input parameter, out = output parameter.
What is name overloading in SQL:1999?
More than one function/procedure of the same name, as long as the number of arguments differ, or at least the types of the arguments differ.
What does the for loop in SQL procedures permit?
Iteration over all results of a query (e.g., summing the budget of all departments).
Benefits and drawbacks of external language functions/procedures.
Benefits: more efficient for many operations, more expressive power. Drawbacks: code may run in the database system's address space; risk of accidental corruption of database structures; security risk (access to unauthorized data).
Two ways to deal with the security problems of external language routines.
Sandbox techniques (a safe language like Java), or running them in a separate process with no access to the database process' memory. Both have performance overheads.
What is a trigger?
A stored procedure in a database that automatically invokes whenever a special event in the database occurs (e.g., a row is inserted or specific columns are updated).
Three key differences between triggers and stored procedures.
1. Triggers cannot be manually invoked or executed. 2. Triggers cannot receive parameters. 3. A transaction cannot be committed or rolled back inside a trigger.
In trigger syntax, what do [before | after] and [for each row] specify?
before/after: when the trigger will be executed. for each row: a row-level trigger, executed for each affected row.
What is a statement-level trigger and when is it useful?
A single action executed for all rows affected by a transaction (for each statement instead of for each row); more efficient when SQL statements update a large number of rows.
When should you avoid triggers?
1. Performance concerns; 2. cross-database or cross-server operations; 3. infinite loop potential (A updates B and B's trigger updates A).
What is a CTE?
A common table expression — a WITH statement that names a result for later reference.
What two parts does a recursive CTE need?
An initialization (base) query that seeds the recursion, and a WHERE clause in the iterative query that decides when the recursion stops.
What is the transitive closure of the prereq relation?
The view (rec_prereq) of all courses that are a prerequisite, whether directly or indirectly, for a given course.
Why can't a non-recursive, non-iterative query compute transitive closure?
It can perform only a fixed number of joins of a table with itself, giving only a fixed number of levels; a database with more levels can always be built on which the query will not work.
What is the fixed point of a recursive view, and what does "monotonic" mean?
Fixed point: the final result of the iterative process. Monotonic: if tuples are added to prereq, rec_prereq contains all the tuples it had before, plus possibly more.
Is the result of rank() over (order by GPA desc) sorted?
No. An extra order by clause (e.g., order by s_rank) is needed to get the rows in sorted order.
rank vs dense_rank when 3 students share the top GPA.
rank: all three get 1 and the next rank is 4 (gaps). dense_rank: all three get 1 and the next is 2 (no gaps).
What is a window function, and which clause defines the window?
A function that performs calculations across a specified set of rows known as a "window"; the OVER clause defines it (PARTITION BY to partition, ORDER BY to order).
What is OLAP?
Online Analytical Processing: interactive analysis of data, allowing data to be summarized and viewed in different ways; multidimensional analysis on large volumes of data (e.g., in a data warehouse).
Describe a cross-tab (pivot-table).
Values of one dimension attribute form the row headers, values of another form the column headers, other dimension attributes are listed on top, and each cell holds the aggregate (sum) for the values that specify it.
What is a data cube?
A multidimensional generalization of a cross-tab; it can have n dimensions, and cross-tabs can be used as views on it.
When cross-tabs are represented as relations, what represents aggregates?
The value all; the SQL standard actually uses null in place of all, despite confusion with regular null values.
cube over three attributes: count the grouping lists and say where the count comes from.
Eight: the union of GROUP BY on every subset of the three attributes, including the empty grouping ( ).
rollup over (item_name, color, size): which lists are produced, and what rule picks them?
A union on every prefix of the specified attribute list (hierarchical aggregates). Four: (item_name, color, size), (item_name, color), (item_name), ( ).
What is drill down (roll down)?
The reverse of roll up: like zooming-in on the data cube; it navigates from less detailed record to more detailed data.
What is a slice?
A subset of the cube corresponding to a single value for one or more members of the dimension (e.g., temperature = cool), turning a 3-D cube into a 2-D one.
What is the pivot operation?
Also called rotation: a visualization operation that rotates the data axes in view to give an alternative presentation, e.g., swapping the rows and columns.
MOLAP vs ROLAP vs HOLAP.
MOLAP: multidimensional arrays in memory (the earliest). ROLAP: only relational database features. HOLAP: some summaries in memory, base data and other summaries in a relational database.