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.