Explain the following:

Question

(a) Why embedded SQL requires a precompiler
(b) Why a database operation has to be prepared before it can be executed.
Why embedded SQL can use host variables in SQL statements and JDBC cannot
(c) The difference between INSENSITIVE and KEYSET DRIVEN cursors
(d) Why deferred constraint checking is preferable in most transaction processing applica- tions.
(e) What are the advantages of stored procedures
(f) How can the same JDBC program be used to access different DBMSs

Answer 1

(a) Why embedded SQL requires a precompiler
Solution:
Because the compiler for the host language would not understand the syntax of the SQL statements
(b) Why a database operation has to be prepared before it can be executed.
Solution:
The system must make an execution plan as to how to execute the statement: what indexes to use, in what order to execute the different parts of the statement, etc.
(c) Why embedded SQL can use host variables in SQL statements and JDBC cannot.
Solution:
The precompiler for embedded SQL has a symbol table containing the information about the host variables so that it can generate the proper code to access and update those variables.
(d) The difference between INSENSITIVE and KEYSET DRIVEN cursors
Solution:
With an INSENSITIVE cursor, when the cursor is opened, the result set is computed and stored separately from the table. It can then be accessed with FETCH statements.
With a KEYSET DRIVEN cursor, when the cursor is opened a (new) table is created containing pointers to the rows satisfying the specified predicate. FETCH statements use the pointers to access the result set.
(e) Why deferred constraint checking is preferable in most transaction processing applica- tions.
Solution: Early statements in a transactions might make some constraints (temporar- ily) false, and then later statements might make them true again. Immediate constraint checking would make the transaction abort.
(f) What are the advantages of stored procedures Solution:
Among the advantages are:
• They need not be prepared every time they are used
• Less information need be passed between the application and the DBMS
• Authorization can be at the procedure level
• Security is improved
• The application need now know the schema of the database
• Maintenance is simplified
(g) How can the same JDBC program be used to access different DBMSs
Solution:
The same program can use different drivers for different DBMSs