COMP 440 Final Review

Procedural SQL is compiled in two steps, with a precompiler followed by a compiler. (T/F)

False, Embedded SQL requires a precompiler for SQL, followed by a compiler for the host language. Procedural SQL is an extension of SQL and compiled in one step.

Most Procedural SQL languages implement the entire SQL/PSM standard. (T/F)

False, The SQL/PSM standard is complex and has evolved after leading Procedural SQL languages were created. Most Procedural SQL languages do not implement the entire standard.

A connection consists of a database address only. (T/F)

False, A connection specifies authorization credentials for a database as well as the database address.

A stored procedure is compiled every time the procedure is called. (T/F)

False, compiled only once when created

RETURN and RETURNS are equivalent keywords. (T/F)

False, RETURN assigns a return value to the stored function and appears within the body. RETURNS specifies a data type for the return value and appears between the CREATE FUNCTION clause and the body.

The <parameter-declaration> has the same syntax in stored procedures and stored functions. (T/F)

False, The <parameter-declaration> may contain IN, OUT, and INOUT parameters in stored procedures, but not in stored functions.

Each database requires a different driver manager. (T/F)

False, each database requires a different driver, but all drivers communicate with a single driver manager.

The EXEC SQL keyword is used in procedural SQL. (T/F)

False, embedded

Stored functions have no parameters. (T/F)

False, have input parameters but not output parameters

___________ languages combine data and procedures into classes.

Object Oriented

A precompiler translates embedded SQL to

Host language

Precedes a parameter name in a <parameter-declaration>.

INOUT

A(n) _______ statement must appear before an EXECUTE statement without the IMMEDIATE keyword.

PREPARE

What are connections in Embedded SQL?

a communication link between a host program and a database server

Procedural languages

are composed of procedures, also called functions or subroutines Ex: C and COBOL

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

body

What is Embedded SQL?

codes SQL statements directly in a program written in another language

A ______________ is a series of statements between a BEGIN and an END keyword.

compound statement

Imperative languages

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

What does this line of code do: CONNECT TO Database AS ConnectionName USER AccountName USING Password;

creates a connection called ConnectionName to the database specified as Database

Paradigm gap

difference of query processing in SQL and procedural or object-oriented languages

Syntax gap

difference of syntax for example SQL syntax is different from Java syntax

Declarative languages

do not contain control flow statements Ex: SQL

The precompiler cannot compile ______ SQL statements.

dynamic

Embedded SQL statements that are generated at run-time are called

dynamic SQL

What does this line of code do: EXECUTE StatementName;

executes the query associated with StatementName.

Contains an SQL stored procedure call.

flightCall

A(n) ____________ determines how to process statements in a declarative language.

optimizer

Object-oriented languages

organize code into classes Ex: Java, Python, C++

C contains control flow statements and SQL does not. This is an example of the ______ gap.

paradigm

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

parameter declaration

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

precompiler

Compiles an SQL statement and associates the statement with a Java object.

prepareCall()

What are two types of imperative languages?

procedural and object oriented

Identifies the database location and provides database access credentials.

reservation

What does this line of code do: SET CONNECTION ConnectionName;

sets the database associated with ConnectionName as the 'active' database

A ___________ is a host language variable that appears in SQL statements

shared variable

Statements that are written explicitly in program code are ______ SQL.

static

Embedded SQL statements that are fixed in program code are called

static SQL

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

stored function

Open Database Connectivity (ODBC)

supports many programming languages and data sources. ODBC was developed in parallel with SQL/CLI and conforms closely to the standard.

SQL uses many keywords, like SELECT, FROM, and WHERE. C relies heavily on punctuation, like {} and #. This is an example of the ______ gap.

syntax

Database programming presents two challenges:

syntax gap and paradigm gap

What does this line of code do: DISCONNECT ConnectionName;

terminates the connection

A _________________ is an SQL variable that must begin with an @ character.

user-defined variable

Which API is often used with the C# language ?

ADO.NET

Database API implementations typically contain two software layers:

-A driver connects directly to the database -The driver manager connects the application to the drivers

List of compound statements

-DECLARE -SET -IF -WHILE -CASE -REPEAT -RETURN

Stored procedures can be called from:

-Other stored procedures -The MySQL Command-Line Client -C, C++, Java, Python, and other programming languages

Embedded SQL Disadvantages

-gaps -compile steps -network traffic

Optional in CREATE PROCEDURE statements.

<parameter-declaration>

Shared variables can appear in which clause of an embedded SELECT statement?

Both INTO and WHERE clauses

Embedded SQL is commonly supported in which language?

C

A stored procedure is executed with a ______ statement. This statement includes the procedure name and a parameter list.

CALL

If a value is updated in a table, is a trigger activated?

No, because UPDATE doesn't activate the trigger

The ________ statement executes a query.

OPEN

___________ languages contain control flow statements but no classes.

Procedural

Affords the greatest database security.

Procedural SQL

Compiles and stores complex data processing tasks on the database server.

Procedural SQL

____________ is the leading declarative language.

SQL

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

SQL/Persistent Stored Modules (SQL/PSM)

________ is a variant of embedded SQL designed for Java.

SQLJ

All embedded SQL statements require an active connection. (T/F)

True

Each database connection consumes computer resources. (T/F)

True

Procedural SQL is specified in the SQL standard. (T/F)

True

Procedural SQL languages differ significantly, depending on the database. (T/F)

True

Several connections can exist at the same time. (T/F)

True

Stored functions may be executed from the MySQL command line. (T/F)

True

The ODBC API for the Java language consists of Java classes. (T/F)

True

Returns the value of a stored procedure output parameter.

getInt()

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

host language

Name 3 leading wide column databases

-BigTable -HBase -Cassandra

Graph databases support a variety of query languages:

-Cypher -Gremlin -SPARQL

Name 3 leading document databases

-MongoDB -CouchDB -Couchbase

Name 3 leading graph database systems

-Neo4j -Azure Cosmos DB -GraphDB

The CREATE TRIGGER statement specifies a TriggerName followed by four required keywords:

-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.

What does 'NoSQL' stand for?

-SQL is not supported -SQL is not the only query language supported

Leading Database API's

-SQL/Call Language Interface (SQL/CLI), an extension of the SQL standard -ODBC supports many programming languages and data sources. ODBC was developed in parallel with SQL/CLI and conforms closely to the standard. -JDBC for Java applications -DB-API for Python applications -ADO.NET for .NET applications. .NET is a Microsoft environment, and C# is the most popular .NET language. -PDO for PHP applications. PHP is a widely used programming language for building dynamic websites.

Key-value databases are used for applications with these requirements:

-Simple data models -Data access via the key -Large numbers of small records -Fast read and write

A trigger is like a stored procedure or a stored function, with two differences:

-Triggers have neither parameters nor a return value. -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.

Examples of document formats

-XML -JSON

SQL queries are defined and executed with Statement methods:

-executeQuery() -executeUpdate() -execute() -close()

What are some similarities between wide column and key-value databases?

-fast read and write so rather than updating an existing value, a new version is saved -they support very large numbers of rows

Big data requirements

-flexible schema -horizontal scaling -relaxed transaction requirements -extensive replication -sharding -rapid insert and analysis

ResultSet methods:

-getInt() -getDouble() -getString() -getMetaData() -getWarnings()

What are some differences between wide column and key-value databases?

-instead of data being stored as a value its stored in multiple columns then grouped in families -flexible schema -required info to locate a value

Name 4 NoSQL databases

-key value database -wide column database -document database -graph database

Four major categories of NoSQL databases:

-key-value database -wide column database -document database -graph database

At a high level, database APIs are similar and support common capabilities, including:

-manage connections -prepare queries -execute queries with single row results -execute queries with multiple row results -call stored procedures

Procedural SQL Advantages

-minimal gaps -one compiling step -reduced network traffic -better optimization

CallableStatement methods

-prepareCall() -setInt() -setString() -registeredOutParameter()

Physical structure of key value databases includes

-replicated data -sharding -hashing -eventual consistency

Transactional data requirements

-stable schema -vertical scaling -stringent transaction requirements -limited replication -partitioning -rapid insert, update, delete

In a wide column database model, what is required to access a value?

-table name -key -column family name -column name

In a relational database model, what is required to access a value?

-table name -primary key -column name

Four ways big data and transactional data differ

-volume -velocity -variety -veracity

Cursors are managed with four embedded SQL statements:

1) DECLARE CursorName CURSOR FOR Statement; 2) OPEN CursorName; 3) FETCH FROM CursorName INTO :SharedVariable1, :SharedVariable2, ... ; 4) CLOSE CursorName;

Dynamic SQL is managed with two embedded SQL statements:

1) PREPARE StatementName FROM :StatementString; 2) EXECUTE StatementName;

Why are prepare statements important?

An SQL injection attack is when a user intentionally enters values that alter the intent of an SQL statement. The preparedStatement.executeQuery() method prevents SQL injection when assigning values to placeholders.

Which graph database system is a multi-model database, supporting all four NoSQL database models?

Azure Cosmos DB

A ________ document is a binary representation of JSON with additional type information.

BSON

Which wide column database system was the first?

BigTable

The ________ statement releases resources associated with a cursor.

CLOSE

Connections are managed with three embedded SQL statements:

CONNECT TO Database AS ConnectionName USER AccountName USING Password; SET CONNECTION ConnectionName; DISCONNECT ConnectionName;

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

CREATE PROCEDURE

The __________ interface extends PreparedStatement with methods that call stored procedures

CallableStatement

Which wide column database system is the highest ranked?

Cassandra

Visa processes approximately 30 billion credit card transactions per quarter (three months) and stores 10 years of history in a database for use in fraud detection. Assume each transaction is approximately 1000 bytes.

Characteristics of both big data and transactional data

Which interface contains the createStatement() method?

Connection

_____________ is the official JDBC (Java Database Connectivity) driver for MySQL

Connector/J

Which document database system is designed to support mobile devices and offline updates with eventual consistency?

CouchDB

Which document database system supports both key-value and document database models?

Couchbase

Which API supports only one language ?

DB-API

The ________ statement associates a cursor name with a query.

DECLARE

Database APIs support:

Databases and other data sources

____________ languages do not explicitly specify how results are processed.

Declarative

Which key-value database characteristic is available only as a cloud service from Amazon Web Services.

DynamoDB

Has become less popular with the rise of object-oriented programming.

Embedded SQL

Usually generates a network round trip for each SQL query.

Embedded SQL

Name three database programming techniques

Embedded SQL Procedural SQL API

The _____ statement assigns query results to shared variables.

FETCH

The ________ statement moves a cursor to the next row of a result table.

FETCH

Different database programming techniques cannot be combined in one application. (T/F)

False

Wide column databases are optimized for vertical scaling. (T/F)

False

All key-value databases support transactions. (T/F)

False, Although some key-value databases can be configured to support transactions with ACID properties, most cannot. Consequently, replicated data may occasionally become inconsistent.

All NoSQL databases fall into exactly one category (key-value, wide column, document, and graph database). (T/F)

False, Hybrid databases offer support for some or all of the four NoSQL database models.

Key-value databases support foreign keys and referential integrity. (T/F)

False, Key-value databases are optimized for fast read and write in very large, sharded databases. Enforcing database rules like referential integrity requires transactions across multiple shards, which degrades performance.

Key-value databases require a fixed schema for all values. (T/F)

False, No schema is specified for a key-value database. The data structure and meaning can vary from one value to the next.

A connection must always be created within the try clause of a try-catch statement. (T/F)

False, a connection can be created without a try-catch statement. However, connections are usually created within a try clause so failures are processed in a catch clause rather than causing the program to abort.

Every driver implements the full capabilities of the API. (T/F)

False, some data sources do not support all API capabilities. Ex: .csv files do not support transaction management. When a data source does not support an API capability, the driver cannot support the capability.

Which graph database system supports the triple store database model?

GraphDB

Which wide column database system is open source and based off BigTable?

HBase

Horizontal scaling

Increase capacity by adding large numbers of low-cost components working in parallel.

Vertical scaling

Increase capacity by increasing speed and size of a limited number of machines.

What is the most common data type used to represent documents?

JSON

Which document database system stores documents in BSON format, a variation of JSON optimized for fast read and write.

MongoDB

Which graph database system is the most widely used?

Neo4j

Relational databases typically implement index-free adjacency.

No, use indexes to locate individual rows.

The ________ statement positions a cursor before the first row of a result table.

OPEN

Which key-value database characteristic stores values as unformatted series of bytes.

Oracle NoSQL

The ___________ interface extends the Statement interface and supports SQL query parameters.

PreparedStatement

Which key-value database characteristic stores key-value data in RAM

Redis

Connector/J does not support cursors. Instead, query results are retrieved with the ___________ interface.

ResultSet

Which language is designed for triple store databases?

SPARQL

Sharding

Split large data sets across multiple machines working in parallel.

Partitioning

Split large data sets, such as a table, across separate files on one machine.

The __________ interface defines and executes SQL queries. Statement objects are created with the Connection interface createStatement() method.

Statement

Store procedure calls

Stored procedures are saved in the database and often called from general-purpose languages like Java. Stored procedure parameters are labeled IN or OUT. IN parameters input data to the stored procedure. OUT parameters output data from the stored procedure.

PreparedStatement supports SQL query parameters:

The SQL query contains one or more ? characters to represent query parameters.

A connection must be created prior to executing any database operation. (T/F)

True

A secondary API can be layered on top of a third API, for a total of three API layers. (T/F)

True

All MongoDB documents must be stored in a collection. (T/F)

True

Key-value databases are optimized for queries that specify the key. (T/F)

True

Key-value databases store and access data using hashing techniques. (T/F)

True

MongoDB documents may store nested documents. (T/F)

True

MongoDB stores documents in a binary-encoded format. (T/F)

True

The driver manager communicates with all drivers using the same commands. (T/F)

True

Wide column databases are suitable for more complex applications than key-value databases. (T/F)

True

A size limit exists for a BSON document. (T/F)

True 16MB

Shared variables appear in an EXECUTE statement with the ______ clause.

USING

In a key-value database, the value is:

Unstructured or structured data, depending on the key-value database.

Can graph databases have indexes?

Yes

In Gremlin, what operation is similar to a relational INSERT?

addV()

Data generated by new internet and multimedia applications is commonly called

big data

YouTube users view approximately 4 million videos per minute worldwide. YouTube tracks all views and information about video content to optimize advertising shown to each user. Assume YouTube stores approximately 10,000 bytes per view annually.

big data

The executeQuery() method:

both compiles and executes an SQL statement

What does this line of code do: PREPARE StatementName FROM :StatementString;

compiles the query in the shared variable StatementString and associates the variable with StatementName.

A ____________ is an object of the Connection interface that is created with the DriverManager.getConnection() method, specifying the database server address, database name, login username, and password.

connection

A _______ is an embedded SQL variable that identifies an individual row of a result table

cursor

In a ____________ , edges have a starting and ending vertex and are depicted as arrows

directed graph

A ______________ database stores data as a collection of documents. It may contain multiple collections, just as a relational database may contain multiple tables.

document

What is Procedural SQL?

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

A _______ database model is a network of vertices and edges.

graph

In Gremlin, what is the first component of a query?

graph name

In the declaration section, shared variables are assigned a data type in:

host language

Programming languages are either __________ or declarative.

imperative

Makes JDBC classes available to the Java program.

import java.sql.*;

If the shard key is a value other than the document identifier, an ________ must be created on the value.

index

What is an API?

is a library of procedures or classes

In a key-value database, the key:

is unique and also specifies the location of a value

What is a ? indicate in a prepared statement?

it is a placeholder for any value such as letters or numbers

In a key-value database model, what is required to access a value?

key

A ___________ database represents data as a key and a value. The key is a unique identifier used to access values. The value is the data managed by the system.

key-value

What is index free adjacency?

means that each vertex contains a pointer, or physical address, to all connected vertices.

A __________________, also called a hybrid database, supports the data models of several categories. Ex: OrientDB is a leading hybrid database and supports database models of all four categories.

multi-model database

In Gremlin, what operation is similar to a relational join?

out()

The most common type of graph database is a

property graph

A ________ assigns contiguous ranges of shard key values to each shard.

range function

Compared to NoSQL databases, what level of big data support do relational databases offer today?

relational database support for big data is catching up to NoSQL databases in many big data requirements

What is a disadvantage of index-free adjacency as compared to indexes?

restructuring data storage may take longer

In a document database, the ________ can be either the document identifier or another value.

shard key

Structured data created within an organization, with sizes ranging from gigabytes to terabytes, is called

transactional data

The California Department of Motor Vehicles processes vehicle registrations, driver's licenses, and traffic violations for 26 million registered drivers. Assume each driver creates 4 transactions per year at 1000 bytes per transaction, on average, and 5 years of data are stored in a database.

transactional data

A less common type of graph database is a ___________ , also known as a resource description framework (RDF) database

triple store

In an __________ , edges have no direction and are depicted as lines.

undirected graph

What is the size of a typical value?

usually KB or MB

A ___________ consists of multiple tables, each with a key and multiple column families. It uses relational terms like table, row, and column, but with different meanings.

wide column database