CIS275 - Chapter 8: Database Programming

Question 1

____ contain control flow statements that determine the execution order of program steps.

Answer 1

Imperative languages

Control flow statements include loops for repeatedly executing code and conditionals for conditionally executing code.

Question 2

_____ are composed of procedures, also called functions or subroutines.

Answer 2

Procedural languages

Most languages developed prior to 1990 are procedural. Ex: C and COBOL.

Question 3

_____ organize code into classes.

Answer 3

Object-oriented languages

A class combines variables and procedures into a single construct. Most languages developed since 1990 are object-oriented. Ex: Java, Python, and C++.

Question 4

_____ do not contain control flow statements.

Answer 4

Declarative languages

Each statement declares what result is desired, using logical expressions, rather than how the result is processed.

Question 5

Compilers for declarative languages

Answer 5

optimizers

the compiler determines an optimal way to process each declarative statement.

SQL is the leading example of a declarative language.

Question 6

Answer 6

Question 7

Building applications with both SQL and a general-purpose language is called _____.

Answer 7

database programming

Database programming presents two challenges:

Syntax gap. The syntax of SQL and most other languages are quite different. Ex: Groups of statements, called blocks, are fundamental to most procedural and object-oriented languages. SQL has no blocks.

Paradigm gap. Query processing is fundamentally different in SQL and procedural or object-oriented languages. Ex: A result set with many rows is processed in a single SQL statement, but usually requires loops and conditional statements in other languages.

Question 8

1. A declarative language specifies what result is desired. The SQL selects all airline names and flight numbers departing from JFK airport after noon.
2. A procedural language specifies how the result is processed. The code fragment is written in C.
3. printf() displays column headings. The string containing % characters specifies the heading format.

Answer 8

4. The for loop checks every row of the Flight table. Variable i is the row number.
5. The if statement selects flights departing from JFK airport after noon.
6. printf() displays the airline name and flight number of selected flights.

Question 9

Answer 9

Question 10

_____ codes SQL statements directly in a program written in another language.

Answer 10

Embedded SQL

The keywords EXEC SQL precede all SQL statements so the compiler can distinguish SQL from other statements. Ex: Embedded SQL in C for Oracle Database is called Pro*C.

Embedded SQL allows the programmer to write SQL statements directly within another programming language. This approach is conceptually simple but suffers three problems:

Gaps. Most programming today uses object-oriented languages. The syntax and paradigm gap between object-oriented language and SQL is wide, so programs are difficult to write and maintain.

Compile steps. The SQL statements and host language must be compiled with different compilers, in two steps.

Network traffic. Application programs usually run on a client computer, connected to the database server via a network. With embedded SQL, each query generates a ‘round-trip’ from client to server, increasing network traffic and execution time.

For the above reasons, embedded SQL is no longer widely used.

Question 11

_____ extends the SQL language with control flow statements, creating a new programming language.

Answer 11

Procedural SQL

Procedural SQL is limited compared to general-purpose languages, however, and is used primarily for database applications. Ex: In Oracle Database, procedural SQL is called PL/SQL.

Procedural SQL has several advantages over embedded SQL:

Gaps. Procedural SQL gracefully extends SQL. The syntax and paradigm gaps are minimal so programs are relatively easy to write and maintain.

Compile steps. Procedural SQL is compiled in one step rather than two.

Network traffic. Client applications typically call SQL procedures for execution on the database server. Each procedure executes multiple database operations, reducing network traffic and execution time.

Optimization level. The database optimizes entire procedures rather than individual queries, resulting in better optimization and faster query execution.

Procedural SQL languages vary significantly by database and do not offer the full capabilities of general-purpose languages. Ex: Most procedural SQL languages are not object-oriented. For these reasons, procedural SQL is commonly used for limited database tasks.

Question 12

a library of procedures or classes.

Answer 12

application programming interface, or API

The library links an application programming language to a computer service, such as a database, email, or web service. The procedure or class declarations are written in the application programming language. Ex: JDBC is a library of Java classes that access relational databases.

APIs address the limitations of embedded SQL and procedural SQL:

Gaps. Since API procedures and class declarations are written in the application language, applications are compiled in one step, the syntax gap disappears, and the paradigm gap is reduced.

Applications. Database APIs are available for general-purpose, object-oriented languages. Applications are not limited to database processing tasks and procedural languages.

Database independence. Unlike procedural SQL, language and API syntax is independent of data source.

For the reasons above, APIs are the most commonly used database programming technique.

Question 13

1. The reservationConnection object connects the program to the reservation database.
2. The listFlights object contains the SELECT statement and connects to the Reservation database.
3. The flightReader object points to individual rows of the result table.

Answer 13

4. ExecuteReader() executes the SELECT statement stored in the listFlights object.
5. Read() moves flightReader to the next result table row. The while loop repeats until all rows have been read.
6. GetInt32(0) and GetString(1) return the first and second columns of the current FlightReader row.

Question 14

Answer 14

Question 15

Answer 15

Question 16

Embedded SQL statements appear in programs written in another language, called the _____.

Answer 16

host language

To distinguish SQL from host language statements, embedded SQL begins with the keyword EXEC SQL, followed by an SQL statement and a semicolon.

Programs containing embedded SQL are compiled in two steps:

A precompiler translates SQL statements into host language statements and function calls.

The host language compiler translates the host language program to an executable program.

Question 17

a variant of embedded SQL designed for Java.

Answer 17

SQLJ

SQLJ is similar to standard embedded SQL, but the syntax is adapted to Java. SQLJ is not widely supported, since object-oriented languages like Java commonly use an API rather than embedded SQL.

Question 18

1. The C program begins with int main() and ends with return 0.
2. printf() is a C statement that outputs text. \n prints a new line.

Answer 18

3. An embedded SQL statement begins with EXEC SQL and ends with a semicolon. The UPDATE statement executes within the C program.
4. The precompiler translates embedded SQL to C statements and function calls.

Question 19

Answer 19

Question 20

1. The CONNECT TO statement creates a connection to the database at internet address 100.320.420.999.
2. FlightConnection is active.

Answer 20

3. Subsequent queries refer to the FlightConnection database.
4. When the connection is no longer needed, release the associated resources.

Question 21

Answer 21

Question 22

a host language variable that appears in SQL statements.

Answer 22

shared variable

The results of SELECT statements are assigned to shared variables for further processing in the host language. Shared variables must be declared between BEGIN DECLARE SECTION and END DECLARE SECTION statements.

Question 23

1. Three shared variables are declared between BEGIN DECLARE SECTION and END DECLARE SECTION.
2. The C code allows the user to input a flight number. The shared variable flight is assigned the number.
3. The query selects rows with column FlightNumber equal to the shared variable flight.

Answer 23

4. The shared variable airline is assigned AirlineName. The shared variable airport is assigned AirportCode.
5. The C code outputs the result of the SELECT statement.

Answer 24

Question 25

an embedded SQL variable that identifies an individual row of a result table.

Answer 25

cursor ## Footnote Cursors are necessary to process queries that return multiple rows. Cursors are managed with four embedded SQL statements: DECLARE CursorName CURSOR FOR Statement; creates a cursor named CursorName that is associated with the query Statement. OPEN CursorName; executes the query associated with CursorName and positions the cursor before the first row of the result table. FETCH FROM CursorName INTO :SharedVariable1, :SharedVariable2, ... ; advances CursorName to the next row of the result table and copies selected values into the shared variables. CLOSE CursorName; releases the result table associated with the cursor.

Question 26

1. DECLARE CURSOR creates a cursor named FlightCursor that is associated with the SELECT query. 2. OPEN executes the query and positions FlightCursor before the result table's first row. 3. FETCH moves the cursor to first row and assigns column values to corresponding shared variables.

Answer 26

4. INCLUDE declares SQLSTATE to a C program. The value "00000" indicates the cursor is pointing to a valid row. 5. While the cursor points to a valid row, output the query result and fetch the next row. 6. When the cursor moves past the last row, the while loop terminates and the cursor is released.

Question 27

Answer 27

Question 28

Embedded SQL statements that are generated at run-time are called \_\_\_\_\_.

Answer 28

dynamic SQL dynamic SQL is compiled as the program executes.

Question 29

Embedded SQL statements that are fixed in program code are called \_\_\_\_\_.

Answer 29

static SQL Static SQL statements are compiled by the precompiler, while dynamic SQL is compiled as the program executes. For this reason, static SQL executes faster than dynamic SQL.

Question 30

1. Two shared variables are declared. queryString will later contain an SQL statement. 2. The user inputs an SQL query, which is saved in queryString. 3. The query is compiled and saved as QueryName. 4. The user inputs flight number 692. The query executes and deletes rows containing flight number 692.

Answer 30

1. Two shared variables are declared. queryString will later contain an SQL statement. 2. The user inputs an SQL query, which is saved in queryString. 3. The query is compiled and saved as QueryName. 4. The user inputs flight number 692. The query executes and deletes rows containing flight number 692.

Question 31

Answer 31

Question 32

Answer 32

(A) TO (B) RoomConnection (C) USER (D) SET (E) RoomConnection (A) TO: Specifies the server address in a CONNECT command. In this case, 127.0.0.1 is the server address. (B) RoomConnection: Connection name in a CONNECT command that follows AS. (C) USER: Gives the login name in a CONNECT command. (D) SET: Chooses which connection to use (more than one may be open at a time). (E) RoomConnection: Connection name in the DISCONNECT command.

Question 33

Answer 33

Expected: (A) BEGIN (B) DECLARE (C) END (D) SECTION (E) roomSize (A), (B): BEGIN DECLARE SECTION: Begins the shared variable declaration section. (C), (D): END DECLARE SECTION: Ends the shared variable declaration section. (E): roomSize: Following INTO, names the variable into which the value of the Capacity column is saved. The colon is required.

Question 34

Answer 34

Expected: (A) CURSOR FOR (B) OPEN (C) FETCH (D) FROM (E) RoomCursor (A) DECLARE RoomCursor CURSOR FOR: Creates a cursor named RoomCursor. (B) OPEN RoomCursor: Prepares the cursor for use. (C) FETCH: Gets one result from the cursor. (D) FROM: Names the cursor to use in a FETCH statement. (E) RoomCursor: Names the cursor to use in the CLOSE statement.

Question 35

an extension of the SQL language that includes procedures, functions, conditionals, and loops.

Answer 35

Procedural SQL Procedural SQL is intended for database applications and does not offer the full capabilities of general-purpose languages such as Java, Python, and C.

Question 36

Procedural SQL can, in principle, be used for complete programs. More often, however, the language is used to create procedures that interact with the database and accomplish limited tasks. These procedures are compiled by and stored in the database, and therefore are called\_\_\_\_\_.

Answer 36

stored procedures Stored procedures can be called from a command line or a host program written in another language. Host program calls to stored procedures can be coded with embedded SQL or built into an API.

Question 37

a standard for procedural SQL that extends the core SQL standard.

Answer 37

SQL/Persistent Stored Modules (SQL/PSM SQL/PSM is implemented in many relational databases with significant variations and different names.

Question 38

Answer 38

Question 39

A stored procedure is declared with a _____ statement, which includes the procedure name, optional parameter declarations, and the procedure body.

Answer 39

CREATE PROCEDURE

Question 40

A _____ has an optional IN, OUT, or INOUT keyword, a ParameterName, and a Type.

Answer 40

parameter declaration

Question 41

The _____ consists of either a simple statement, such as SELECT and DELETE, or a compound statement.

Answer 41

procedure body Compound statements, described below, are commonly used in stored procedures to code complex database tasks.

Question 42

A stored procedure is executed with a \_\_\_\_\_.

Answer 42

CALL statement. The CALL statement includes the procedure name and a parameter list. The types of the CALL parameters must be compatible with the types of the corresponding CREATE PROCEDURE parameters.

Question 43

1. The FlightCount stored procedure has an input parameter airline and an output parameter quantity. 2. The stored procedure body is a single SELECT statement. 3. The INTO clause assigns the COUNT(\*) value to the quantity parameter.

Answer 43

4. The stored procedure is called from the command line. airline is assigned 'British Airways'. @result is assigned the resulting quantity value. 5. The value of the user-defined variable @result is displayed with a SELECT statement. The Flight table has 41 British Airways flights.

Question 44

an SQL variable that must begin with an @ character.

Answer 44

user-defined variable When called from a different programming language, call parameter syntax varies greatly, depending on the language and API.

Question 45

Answer 45

Question 46

a series of statements between a BEGIN and an END keyword.

Answer 46

compound statement The statements may be variable declarations, assignment statements, if statements, while loops, and other common programming constructs.

Question 47

The _____ statement creates variables for use inside stored procedures.

Answer 47

DECLARE statement A variable has a name, data type, and an optional default value.

Question 48

The _____ assigns a variable with an expression.

Answer 48

SET statement The variable must be declared prior to the SET statement, and the expression may be any valid SQL expression.

Question 49

The _____ is similar to statements in other programming languages.

Answer 49

IF statement ## Footnote The statement includes a logical expression and a statement list. If the expression is true, the statements in the list are executed. If the expression is false, the statements are ignored. IF statements have optional ELSEIF and ELSE clauses. ELSEIF and ELSE clauses are executed when the expression in the IF clause is false, as in other programming languages.

Question 50

The _____ also includes an expression and a statement list.

Answer 50

WHILE statement The statement list repeatedly executes as long as the expression is true. When the expression is false, the WHILE statement terminates, and the statement following END WHILE executes.

Question 51

1. The AddFlights() stored procedure inserts new flights into the Flight table. The body is a compound statement. 2. The declare statements create and initialize two variables. departTime is assigned the startTime parameter. flightNum is assigned 540. 3. The WHILE loop repeats as long as departTime is ≤ endTime.

Answer 51

4. Each pass through the loop inserts a new flight. 5. flightNum is incremented by 100. If departTime is before noon, add 30 minutes. If departTime is noon or later, add an hour.

Question 52

A _____ is like a stored procedure that returns a single value.

Answer 52

stored function

Question 53

1. The cursor named flightCursor retrieves data for all flights. 2. The finishedReading variable is initially FALSE. The handler sets finishedReading to TRUE when the cursor moves past the last row. 3. OPEN executes flightCursor's associated query and sets the cursor prior to the first result table row. 4. FETCH moves the cursor to the first row and assigns column values to procedure variables.

Answer 53

5. The WHILE loop repeats until the cursor moves past the last row. 6. The IF statement changes the departure time for the selected flight. 7. FETCH moves the cursor to the next row. 8. CLOSE releases cursor resources when no longer needed.

Question 54

A stored function is declared with a _____ statement.

Answer 54

CREATE FUNCTION

Question 55

1. The stored function computes an employee's tax and returns an integer. The function reads SQL data with a SELECT statement. 2. The employee's salary is retrieved and assigned to the income variable. 3. Tax is computed as 10% of income exceeding $25,000 and returned.

Answer 55

4. Stored functions are called within any SQL expression, like built-in functions. 5. Lisa Ellison's salary is $40,000. Tax is ($40,000 - $25,000) \* 10% = $1,500, displayed as a result table.

Question 56

Answer 56

Question 57

a stored procedure or a stored function

Answer 57

trigger Triggers have neither parameters nor a return value. Triggers read and write tables but do not communicate directly with a calling program. Triggers are not explicitly invoked by a CALL statement or within an expression. Instead triggers are associated with a specific table and execute whenever the table is changed. Triggers are used to enforce business rules automatically as data is updated, inserted, and deleted.

Question 58

The _____ statement specifies a TriggerName followed by four required keywords:

Answer 58

CREATE TRIGGER ## Footnote ON TableName identifies the table associated with the trigger. INSERT, UPDATE, or DELETE indicates that the trigger executes when the corresponding SQL operation is applied to the table. BEFORE or AFTER determines whether the trigger executes before or after the insert, update, or delete operation. FOR EACH ROW indicates the trigger executes repeatedly, once for each row affected by the insert, update, or delete operations.

Question 59

1. The ExamGrade trigger executes before inserts to the Exam table. 2. One insert statement can create multiple rows. The trigger executes once for each new row. 3. The trigger automatically determines exam grade and updates the Grade column in new table rows.

Answer 59

4. Inserting new rows causes the ExamGrade trigger to execute before the rows are inserted. 5. The Grade column contains values set by the ExamGrade trigger.

Question 60

Answer 60

Question 61

Answer 61

Expected: (A) CREATE (B) IN (C) DEFAULT (D) SET (E) WHILE (F) END (A) CREATE PROCEDURE begins the definition of a stored procedure. (B) IN roomName VARCHAR(40) declares roomName as an input parameter with type VARCHAR(40). (C) DECLARE n INT DEFAULT 1; assigns the initial value of n to be 1. (D) SET n = n + 1; sets the value of n to n + 1. (E) END WHILE ends a WHILE statement. (F) END ends the definition of the stored procedure AddSeats.

Question 62

Answer 62

Expected: (A) CREATE (B) IN (C) INT (D) BEGIN (E) SET (F) WHILE (A) CREATE PROCEDURE begins the definition of a stored procedure. (B) IN howMany INT declares howMany as an input parameter. (C) IN howMany INT declares the type of howMany to be INT. (D) BEGIN begins a compound statement. (E) SET n = n + 1; sets the value of n to n + 1. (F) END WHILE ends a WHILE statement.

Question 63

Answer 63

Expected: (A) CURSOR (B) HANDLER (C) FROM (D) INTO (E) IF (F) CLOSE (A) CURSOR follows the cursor name when declaring a cursor. (B) HANDLER follows CONTINUE when declaring a handler. (C) FETCH FROM roomCursor retrieves the next result from roomCursor. (D) INTO follows the cursor name to copy selected values into nam. (E) IF follows END to end the IF statement. (F) CLOSE closes the cursor before the procedure exits.

Question 65

An _____ specifies the interaction between an application and a computer service, such as a database, email, or web service.

Answer 65

application programming interface (API) If the application is written in a procedural language like C, the API specifies procedure calls. If the application is written in an object-oriented language like Java, the API specifies classes.

Question 66

Answer 66

Question 67

1. The FlightCount procedure is written in procedural SQL and stored in the Reservation database. 2. The java.sql namespace implements the JDBC API. 3. The reservation object opens a connection to the reservation database. close() releases the connection.

Answer 67

4. prepareCall() compiles CALL FlightCount and saves the statement in the flightCall object. ? characters are placeholders for parameters. 5. Java code inputs an airline name to the Java variable airlineName. 6. The airline parameter is set to airlineName, the quantity parameter is registered as an integer, and the query is executed. 7. The Java variable total is assigned the Quantity value and displayed.

Question 68

Answer 68

Question 69

1. Java applications communicate via the JDBC API with the JDBC driver manager. 2. The driver manager communicates with each data source via a different driver. 3. If an API has no driver for a data source, the API may communicate indirectly via another API.

Answer 69

4. The ODBC driver for JDBC communicates with Excel and CSV data sources via the ODBC driver manager.

Question 70

Answer 70

Question 71

Database API implementations typically contain two software layers: A _____ connects directly to the database.

Answer 71

driver Although all relational databases support standard SQL, implementations vary somewhat, and most databases offer non-standard extensions. Consequently, a different driver is necessary for each database.

Question 72

Database API implementations typically contain two software layers: The driver manager connects the application to the drivers.

Answer 72

driver manager The application communicates with the driver manager using API procedure calls. The driver manager forwards each call to the correct driver. When one application connects to several databases, the driver manager selects a driver based on the active connection.

Question 73

a file containing Python classes, functions, or variables.

Answer 73

module Python programs must connect to a database prior to executing queries.

Question 74

1. The import statements make Connector/Python code and errorcode module available to the program. 2. The mysql.connector.connect() function creates a MySQLConnection object named reservationConnection. 3. If the connection fails, the except block executes and prints an error message.

Answer 74

4. If the connection succeeds, the else block executes. Subsequent queries use the reservationConnection object. 5. When database processing is finished, close() releases the connection.

Question 75

Answer 75

Question 76

1. The cursor() method creates a cursor called flightCursor, associated with the Reservation database. 2. The execute() method executes the INSERT statement in the flightQuery string.

Answer 76

3. The commit() method saves the insert in the database before the cursor is released.

Question 77

Answer 77

Question 78

1. The user enters flight data. 2. flightQuery contains three %s placeholders. Values are assigned when the cursor executes. 3. flightData tuple contains three values.

Answer 78

4. When the cursor executes, flightData values are assigned to placeholders. Python data types are automatically converted to MySQL data types. 5. commit() saves the insert in the database, and close() releases the cursor resources.

Question 79

Answer 79

Question 80

1. flightCursor executes a SELECT query. 2. fetchall() returns a set of tuples. Each tuple is a row of the result table. 3. The for statement processes each tuple in the set. row[0] contains data from the first column, and row[1] from the second column.

Answer 80

1. flightCursor executes a SELECT query. 2. fetchall() returns a set of tuples. Each tuple is a row of the result table. 3. The for statement processes each tuple in the set. row[0] contains data from the first column, and row[1] from the second column.

Question 81

Answer 81

Question 82

1. FlightCount counts the number of flights for an airline. 2. flightData contains an airline name and a 0 placeholder for the quantity parameter. 3. callproc() calls FlightCount with the input tuple flightData and returns the output tuple to result. 4. result[1] is the second entry of the result tuple.

Answer 82

1. FlightCount counts the number of flights for an airline. 2. flightData contains an airline name and a 0 placeholder for the quantity parameter. 3. callproc() calls FlightCount with the input tuple flightData and returns the output tuple to result. 4. result[1] is the second entry of the result tuple.

Question 83

Answer 83

Question 84

Answer 84

Expected: (A) connect (B) cursor (C) rowcursor (D) query (E) fetchall (F) close (G) close (A) connect: Establishes a connection, providing login and database information as parameters. (B) cursor: Creates a cursor for the connection. (C), (D) rowcursor.execute(query): Executes query and makes results available to the cursor. (E) fetchall: Retrieves all results from the cursor in one function call. (F) rowcursor.close(): Closes the cursor. A cursor must be released with the close() method prior to closing the associated connection. (G) connection.close(): Closes the connection.

Question 85

Answer 85

Expected: (A) connect (B) cursor (C) execute (D) query (E) fetchall (F) close (G) connection (A) connect: Establishes a connection, providing login and database information as parameters. (B) cursor: Creates a cursor for the connection. (C), (D) rowcursor.execute(query): Executes query and makes results available to the cursor. (E) fetchall: Retrieves all results from the cursor in one function call. (F) rowcursor.close(): Closes the cursor. A cursor must be released with the close() method prior to closing the associated connection. (G) connection.close(): Closes the connection.

Question 86

1. The web browser sends a request to the web server for a PHP script called db.php. The web server executes the PHP script. 2. The DSN indicates that PDO should connect to a MySQL server running on localhost (127.0.0.1) and use the database named "Reservation". 3. The PDO constructor uses the DSN and MySQL username and password to create a database connection.

Answer 86

4. If the connection succeeds, the PHP script uses the PDO object to execute queries. 5. If the connection fails, the PDOException produces a fatal error. 6. If PHP is configured to show fatal errors, the web server responds with a web page showing the error details, which is then displayed by the web browser.

Question 87

Answer 87

Question 88

1. The PDO object creates a connection to MySQL. 2. setAttribute() makes PDO throw a PDOException if an error occurs executing an SQL query. 3. The query() method executes the INSERT statement in the $sql string. The flight is inserted into the Flight table.

Answer 88

4. MySQL server returns information to the web server indicating the result of the INSERT statement. The information is returned from query() as a PDOStatement. 5. The rowCount() method returns 1 because one row was inserted into the Flight table.

Question 89

Answer 89

Question 90

1. A user enters flight information into a form. The form submits to addflight.php, and submitted values are placed in the $\_POST array. 2. The INSERT statement uses ? as placeholders for three values.

Answer 90

3. perpare() prepares the parameterized statement and returns a PDOStatement. 4. bindValue() binds the values from $\_POST to the parameter identifiers. 5. execute() executes the INSERT statement. addflight.php displays a confirmation message.

Question 91

Answer 91

Question 92

1. The prepared statement is executed(), selecting China Airlines flights out of PEK. 2. fetch() returns an array containing a single row from the results table. 3. The while loop processes each row until all rows are processed. Result values are accessed from $row using the column names.

Answer 92

1. The prepared statement is executed(), selecting China Airlines flights out of PEK. 2. fetch() returns an array containing a single row from the results table. 3. The while loop processes each row until all rows are processed. Result values are accessed from $row using the column names.

Question 93

Answer 93

Question 94

1. FlightCount counts the number of flights for an airline. 2. The prepared statement uses a CALL statement to call FlightCount with the airline "China Airlines".

Answer 94

3. execute() calls FlightCount. The OUT parameter quantity is assigned to the @count variable. 4. A second query fetches the value of @count.

Question 95

Answer 95