CIS275 - Chapter 8: Database Programming Flashcards
____ contain control flow statements that determine the execution order of program steps.
Imperative languages
Control flow statements include loops for repeatedly executing code and conditionals for conditionally executing code.
_____ are composed of procedures, also called functions or subroutines.
Procedural languages
Most languages developed prior to 1990 are procedural. Ex: C and COBOL.
_____ organize code into classes.
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++.
_____ do not contain control flow statements.
Declarative languages
Each statement declares what result is desired, using logical expressions, rather than how the result is processed.
Compilers for declarative languages
optimizers
the compiler determines an optimal way to process each declarative statement.
SQL is the leading example of a declarative language.
Building applications with both SQL and a general-purpose language is called _____.
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.
- A declarative language specifies what result is desired. The SQL selects all airline names and flight numbers departing from JFK airport after noon.
- A procedural language specifies how the result is processed. The code fragment is written in C.
- printf() displays column headings. The string containing % characters specifies the heading format.
- The for loop checks every row of the Flight table. Variable i is the row number.
- The if statement selects flights departing from JFK airport after noon.
- printf() displays the airline name and flight number of selected flights.
_____ codes SQL statements directly in a program written in another language.
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.
_____ extends the SQL language with control flow statements, creating a new programming language.
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.
a library of procedures or classes.
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.
- The reservationConnection object connects the program to the reservation database.
- The listFlights object contains the SELECT statement and connects to the Reservation database.
- The flightReader object points to individual rows of the result table.
- ExecuteReader() executes the SELECT statement stored in the listFlights object.
- Read() moves flightReader to the next result table row. The while loop repeats until all rows have been read.
- GetInt32(0) and GetString(1) return the first and second columns of the current FlightReader row.
Embedded SQL statements appear in programs written in another language, called the _____.
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.
a variant of embedded SQL designed for Java.
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.
- The C program begins with int main() and ends with return 0.
- printf() is a C statement that outputs text. \n prints a new line.
- An embedded SQL statement begins with EXEC SQL and ends with a semicolon. The UPDATE statement executes within the C program.
- The precompiler translates embedded SQL to C statements and function calls.
- The CONNECT TO statement creates a connection to the database at internet address 100.320.420.999.
- FlightConnection is active.
- Subsequent queries refer to the FlightConnection database.
- When the connection is no longer needed, release the associated resources.
a host language variable that appears in SQL statements.
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.
- Three shared variables are declared between BEGIN DECLARE SECTION and END DECLARE SECTION.
- The C code allows the user to input a flight number. The shared variable flight is assigned the number.
- The query selects rows with column FlightNumber equal to the shared variable flight.
- The shared variable airline is assigned AirlineName. The shared variable airport is assigned AirportCode.
- The C code outputs the result of the SELECT statement.
an embedded SQL variable that identifies an individual row of a result table.
cursor
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.
- DECLARE CURSOR creates a cursor named FlightCursor that is associated with the SELECT query.
- OPEN executes the query and positions FlightCursor before the result table’s first row.
- FETCH moves the cursor to first row and assigns column values to corresponding shared variables.
- INCLUDE declares SQLSTATE to a C program. The value “00000” indicates the cursor is pointing to a valid row.
- While the cursor points to a valid row, output the query result and fetch the next row.
- When the cursor moves past the last row, the while loop terminates and the cursor is released.
Embedded SQL statements that are generated at run-time are called _____.
dynamic SQL
dynamic SQL is compiled as the program executes.
Embedded SQL statements that are fixed in program code are called _____.
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.
- Two shared variables are declared. queryString will later contain an SQL statement.
- The user inputs an SQL query, which is saved in queryString.
- The query is compiled and saved as QueryName.
- The user inputs flight number 692. The query executes and deletes rows containing flight number 692.
- Two shared variables are declared. queryString will later contain an SQL statement.
- The user inputs an SQL query, which is saved in queryString.
- The query is compiled and saved as QueryName.
- The user inputs flight number 692. The query executes and deletes rows containing flight number 692.
(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.
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.
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.
an extension of the SQL language that includes procedures, functions, conditionals, and loops.
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.
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_____.
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.
a standard for procedural SQL that extends the core SQL standard.
SQL/Persistent Stored Modules (SQL/PSM
SQL/PSM is implemented in many relational databases with significant variations and different names.
A stored procedure is declared with a _____ statement, which includes the procedure name, optional parameter declarations, and the procedure body.
CREATE PROCEDURE
A _____ has an optional IN, OUT, or INOUT keyword, a ParameterName, and a Type.
parameter declaration
The _____ consists of either a simple statement, such as SELECT and DELETE, or a compound statement.
procedure body
Compound statements, described below, are commonly used in stored procedures to code complex database tasks.
A stored procedure is executed with a _____.
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.
- The FlightCount stored procedure has an input parameter airline and an output parameter quantity.
- The stored procedure body is a single SELECT statement.
- The INTO clause assigns the COUNT(*) value to the quantity parameter.
- The stored procedure is called from the command line. airline is assigned ‘British Airways’. @result is assigned the resulting quantity value.
- The value of the user-defined variable @result is displayed with a SELECT statement. The Flight table has 41 British Airways flights.
an SQL variable that must begin with an @ character.
user-defined variable
When called from a different programming language, call parameter syntax varies greatly, depending on the language and API.
a series of statements between a BEGIN and an END keyword.
compound statement
The statements may be variable declarations, assignment statements, if statements, while loops, and other common programming constructs.
The _____ statement creates variables for use inside stored procedures.
DECLARE statement
A variable has a name, data type, and an optional default value.
The _____ assigns a variable with an expression.
SET statement
The variable must be declared prior to the SET statement, and the expression may be any valid SQL expression.
The _____ is similar to statements in other programming languages.
IF statement
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.
The _____ also includes an expression and a statement list.
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.
- The AddFlights() stored procedure inserts new flights into the Flight table. The body is a compound statement.
- The declare statements create and initialize two variables. departTime is assigned the startTime parameter. flightNum is assigned 540.
- The WHILE loop repeats as long as departTime is ≤ endTime.
- Each pass through the loop inserts a new flight.
- flightNum is incremented by 100. If departTime is before noon, add 30 minutes. If departTime is noon or later, add an hour.
A _____ is like a stored procedure that returns a single value.
stored function
- The cursor named flightCursor retrieves data for all flights.
- The finishedReading variable is initially FALSE. The handler sets finishedReading to TRUE when the cursor moves past the last row.
- OPEN executes flightCursor’s associated query and sets the cursor prior to the first result table row.
- FETCH moves the cursor to the first row and assigns column values to procedure variables.
- The WHILE loop repeats until the cursor moves past the last row.
- The IF statement changes the departure time for the selected flight.
- FETCH moves the cursor to the next row.
- CLOSE releases cursor resources when no longer needed.
A stored function is declared with a _____ statement.
CREATE FUNCTION
- The stored function computes an employee’s tax and returns an integer. The function reads SQL data with a SELECT statement.
- The employee’s salary is retrieved and assigned to the income variable.
- Tax is computed as 10% of income exceeding $25,000 and returned.
- Stored functions are called within any SQL expression, like built-in functions.
- Lisa Ellison’s salary is $40,000. Tax is ($40,000 - $25,000) * 10% = $1,500, displayed as a result table.
a stored procedure or a stored function
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.
The _____ statement specifies a TriggerName followed by four required keywords:
CREATE TRIGGER
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.
- The ExamGrade trigger executes before inserts to the Exam table.
- One insert statement can create multiple rows. The trigger executes once for each new row.
- The trigger automatically determines exam grade and updates the Grade column in new table rows.
- Inserting new rows causes the ExamGrade trigger to execute before the rows are inserted.
- The Grade column contains values set by the ExamGrade trigger.
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.
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.
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.
An _____ specifies the interaction between an application and a computer service, such as a database, email, or web service.
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.
- The FlightCount procedure is written in procedural SQL and stored in the Reservation database.
- The java.sql namespace implements the JDBC API.
- The reservation object opens a connection to the reservation database. close() releases the connection.
- prepareCall() compiles CALL FlightCount and saves the statement in the flightCall object. ? characters are placeholders for parameters.
- Java code inputs an airline name to the Java variable airlineName.
- The airline parameter is set to airlineName, the quantity parameter is registered as an integer, and the query is executed.
- The Java variable total is assigned the Quantity value and displayed.
- Java applications communicate via the JDBC API with the JDBC driver manager.
- The driver manager communicates with each data source via a different driver.
- If an API has no driver for a data source, the API may communicate indirectly via another API.
- The ODBC driver for JDBC communicates with Excel and CSV data sources via the ODBC driver manager.
Database API implementations typically contain two software layers:
A _____ connects directly to the database.
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.
Database API implementations typically contain two software layers:
The driver manager connects the application to the drivers.
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.
a file containing Python classes, functions, or variables.
module
Python programs must connect to a database prior to executing queries.
- The import statements make Connector/Python code and errorcode module available to the program.
- The mysql.connector.connect() function creates a MySQLConnection object named reservationConnection.
- If the connection fails, the except block executes and prints an error message.
- If the connection succeeds, the else block executes. Subsequent queries use the reservationConnection object.
- When database processing is finished, close() releases the connection.
- The cursor() method creates a cursor called flightCursor, associated with the Reservation database.
- The execute() method executes the INSERT statement in the flightQuery string.
- The commit() method saves the insert in the database before the cursor is released.
- The user enters flight data.
- flightQuery contains three %s placeholders. Values are assigned when the cursor executes.
- flightData tuple contains three values.
- When the cursor executes, flightData values are assigned to placeholders. Python data types are automatically converted to MySQL data types.
- commit() saves the insert in the database, and close() releases the cursor resources.
- flightCursor executes a SELECT query.
- fetchall() returns a set of tuples. Each tuple is a row of the result table.
- The for statement processes each tuple in the set. row[0] contains data from the first column, and row[1] from the second column.
- flightCursor executes a SELECT query.
- fetchall() returns a set of tuples. Each tuple is a row of the result table.
- The for statement processes each tuple in the set. row[0] contains data from the first column, and row[1] from the second column.
- FlightCount counts the number of flights for an airline.
- flightData contains an airline name and a 0 placeholder for the quantity parameter.
- callproc() calls FlightCount with the input tuple flightData and returns the output tuple to result.
- result[1] is the second entry of the result tuple.
- FlightCount counts the number of flights for an airline.
- flightData contains an airline name and a 0 placeholder for the quantity parameter.
- callproc() calls FlightCount with the input tuple flightData and returns the output tuple to result.
- result[1] is the second entry of the result tuple.
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.
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.
- The web browser sends a request to the web server for a PHP script called db.php. The web server executes the PHP script.
- The DSN indicates that PDO should connect to a MySQL server running on localhost (127.0.0.1) and use the database named “Reservation”.
- The PDO constructor uses the DSN and MySQL username and password to create a database connection.
- If the connection succeeds, the PHP script uses the PDO object to execute queries.
- If the connection fails, the PDOException produces a fatal error.
- 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.
- The PDO object creates a connection to MySQL.
- setAttribute() makes PDO throw a PDOException if an error occurs executing an SQL query.
- The query() method executes the INSERT statement in the $sql string. The flight is inserted into the Flight table.
- MySQL server returns information to the web server indicating the result of the INSERT statement. The information is returned from query() as a PDOStatement.
- The rowCount() method returns 1 because one row was inserted into the Flight table.
- A user enters flight information into a form. The form submits to addflight.php, and submitted values are placed in the $_POST array.
- The INSERT statement uses ? as placeholders for three values.
- perpare() prepares the parameterized statement and returns a PDOStatement.
- bindValue() binds the values from $_POST to the parameter identifiers.
- execute() executes the INSERT statement. addflight.php displays a confirmation message.
- The prepared statement is executed(), selecting China Airlines flights out of PEK.
- fetch() returns an array containing a single row from the results table.
- The while loop processes each row until all rows are processed. Result values are accessed from $row using the column names.
- The prepared statement is executed(), selecting China Airlines flights out of PEK.
- fetch() returns an array containing a single row from the results table.
- The while loop processes each row until all rows are processed. Result values are accessed from $row using the column names.
- FlightCount counts the number of flights for an airline.
- The prepared statement uses a CALL statement to call FlightCount with the airline “China Airlines”.
- execute() calls FlightCount. The OUT parameter quantity is assigned to the @count variable.
- A second query fetches the value of @count.
