Ciw Database Specialist

Front 1

a database contains information stored in a ___ format

Back 1

structured

Front 2

a relational database contains two or more

Back 2

related tables

Front 3

related tables are not supported in

Back 3

flat-file or hierarchical databases

Front 4

relational database compared to flat-file

Back 4

better support for multiple users
data operations require complex, specialized software

Front 5

flat-file database compared to relational

Back 5

smaller files
allows data to be accessed using a variety of programming languages
operating system file access software is used for data access

Front 6

number of rows (tuples) in a relation

Back 6

cardinality

Front 7

number of attributes in a relation (columns in a table)

Back 7

degree

Front 8

row or record

Back 8

tuple

Front 9

a virtual table based on a query

Back 9

view

Front 10

whether or not NULL values are allowed

Back 10

nullability

Front 11

table required to support many-to-many relationships

Back 11

intersection table

Front 12

identifying characteristic of an entity

Back 12

attribute

Front 13

table (relation) is the basic storage unit

Back 13

made up of rows (tuples) and columns (attributes)

Front 14

indicated by a zero and a set of crows feet or asterisk

Back 14

zero-to-many

Front 15

indicated by a line and a set of crows feet (many) or asterisk

Back 15

one-to-many

Front 16

documented using a zero and a line

Back 16

zero-to-one

Front 17

documented using a line and a line

Back 17

one-to-one

Front 18

identify attribute domains
create ER diagrams
identify primary and foreign keys

Back 18

conceptual design

Front 19

identify relationships and relationship cardinality
normalize data

Back 19

logical design

Front 20

identify physical database objects including tables, views, primary indexes, and secondary indexes
create data dictionary

Back 20

physical design

Front 21

an attribute can contain only a single value
each row must be uniquely identified

Back 21

1NF

Front 22

all non-key attributes must be functionally dependent on the primary key

Back 22

2NF

Front 23

no attributes that are not part of the primary key are dependent on one or more attributes that are also not part of the primary key (transitive dependency)

Back 23

3NF

Front 24

any candidate key cannot be dependent on any part of another candidate key

Back 24

Boyce-Codd normal

Front 25

typical to normalize to the

Back 25

3NF

Front 26

denormalization is used to

Back 26

combine data for faster retrieval and updates

Front 27

each item can be uniquely identified, enforced through primary key

Back 27

entity integrity

Front 28

enforcing relationships between tables using foreign and primary keys to ensure each row in the referencing table refers to only one row in the referenced table

Back 28

relational integrity

Front 29

ensuring that the value entered in a column is valid, enforced through constraints, data types, and sometimes through triggers

Back 29

domain integrity

Front 30

ensuring that business rules are met and enforced through business rules (constraints and triggers)

Back 30

enterprise integrity

Front 31

enforce uniqueness

Back 31

primary key

Front 32

enforce relationships

Back 32

foreign key

Front 33

when a row is deleted from the referenced table, all referencing rows are also deleted

Back 33

ON DELETE CASCADE

Front 34

when a key value is updated in the referenced table, all referencing row key values are also updated

Back 34

ON UPDATE CASCADE

Front 35

require value matching wildcard, within range boundaries, or in value list

Back 35

check

Front 36

provides default value for column

Back 36

default

Front 37

provide custom access to a relation or set of relations based on a selection query

Back 37

view

Front 38

order data and improve read performance but can degrade write performace

Back 38

index

Front 39

sorts data in order and shares a physical structure with the table

Back 39

primary (clustered) index

Front 40

organizes data but does not change physical sort order

Back 40

secondary (non-clustered)

Front 41

model for DDL statements used during physical design phase

Back 41

DBDL

Front 42

statements used to create, modify, and delete database objects

Back 42

DDL

Front 43

statements used to modify table contents

Back 43

DML

Front 44

remove granted permissions

Back 44

REVOKE

Front 45

deny permission

Back 45

DENY

Front 46

returns qualifying rows from right table and all rows from the left table

Back 46

left outer join

Front 47

returns qualifying rows from the left table and all rows from the right table

Back 47

right outer join

Front 48

all SELECT column_list items must be included in ___ ___ list or be aggregates

Back 48

GROUP BY

Front 49

CREATE syntax

Back 49

CREATE tablename(column_name data_type optional_nullability optional_constraint, ...)

Front 50

_

Back 50

replaces one character of any character type

Front 51

returns only some of the columns from the source relation
(pi)

Back 51

projection

Front 52

returns only some of the rows from the source relation
(sigma)

Back 52

selection

Front 53

combine relations into single result relation, removing all duplicates
(U)

Back 53

union

Front 54

find rows common to two relations and return as new relation
(upside down U)

Back 54

intersection

Front 55

return single relation based on qualifying rows in two relations
(two triangles touching at point)

Back 55

join
natural join assumes join condition and do not explicitly specify the condition

Front 56

return the rows from one relation that are not present in the other relation
(-)

Back 56

difference

Front 57

returns every possible pair value as new relation
(X)

Back 57

Cartesian product

Front 58

expression structure for
selection and projection

Back 58

unary statement - operator followed by relation name

Front 59

expression structure for
union, intersection, join, difference, and Cartesian product

Back 59

use binary operators to combine unary statements

Front 60

JDBC bases its structure on the structure of

Back 60

ODBC

Front 61

___ can be used to connect to and access any ODBC-compliant database product for which the vendor provides a ___ driver

Back 61

JDBC

Front 62

applets compile on the ___ and require ___

Back 62

server, JDBC

Front 63

___ are interpreted, can be embedded on a Web page, and do not rely on JDBC

Back 63

scripts

Front 64

to have an application reference multiple data sources, ___

Back 64

have the application load multiple drivers

Front 65

you must load the driver before it can be referenced by

Back 65

DriverManager methods

Front 66

also known as a Native-Protocol Java driver
makes calls directly to the DBMS proprietary network protocol
driver can be deployed without having to worry about system compatibility

Back 66

Pure Java Driver

Front 67

communicates with the ODBC driver to pass requests through to the database rather than directly with the database

Back 67

JDBC-ODBC bridge driver

Front 68

makes calls directly to the database

Back 68

Native API driver

Front 69

translates calls into a generic network protocol rather than a DBMS-specific network protocol
calls are translated by the database server into DBMS-specific calls
typically used with middleware components

Back 69

JDBC-Net driver

Front 70

general syntax using DriverManager getConnection method

Back 70

Connection conn = DriverManager.getConnection(url)

Front 71

Connection conn = DriverManager.getConnection(url)

url can be

Back 71

string variable or literal string

Front 72

Connection conn = DriverManager.getConnection(url)

general syntax for url is

Back 72

jdbc:driver_name:subname

Front 73

Connection conn = DriverManager.getConnection(url)

driver_name is the

Back 73

database connectivity mechanism (loaded driver or other connectivity method)

Front 74

Connection conn = DriverManager.getConnection(url)

subname values

Back 74

vary by driver

Front 75

Connection conn = DriverManager.getConnection(url)

url must start with

Back 75

jdbc

Front 76

___ can return a valid connection or SQLException

Back 76

getConnection

Front 77

___ and ___ must be provided when required by the DBMS for validation

Back 77

user name, password

Front 78

use ___ object to execute individual statements

Back 78

Statement

Front 79

executing individual statements with Statement object

Back 79

1. create a connection object
2. create with Connection object createStatement method
3. call the Statement object's execute method and pass the query as a parameter
4. close the connection when finished

Front 80

what is created by executeQuery method

Back 80

result set

Front 81

use get methods (getString, etc.) to

Back 81

retrieve column values

Front 82

retrieves character (string) values, CHAR and VARCHAR type values

Back 82

getString

Front 83

retrieves INTEGER type values (32-bit signed)

Back 83

getLong

Front 84

retrieves SMALLINT type values (16-bit signed)

Back 84

getShort

Front 85

retrieves DECIMAL type values

Back 85

getBigDecimal

Front 86

using wrong get method

Back 86

causes exception to be thrown

Front 87

retrieve values by column name or number with numbering starting with

Back 87

1

Front 88

cursor initially points to empty row immediately before the first row, must call ResultSet ___ method to advance and increment cursor

Back 88

next

Front 89

returns number of rows changed

Back 89

executeUpdate

Front 90

returns result set

Back 90

executeQuery

Front 91

returns Boolean value, true if the statement executes without error

Back 91

execute

Front 92

the Connection object ___ method controls automatic statement commit

Back 92

setAutoCommit
set to true - statements commit as executed
set to false - statements must be committed manually

Front 93

any statements not committed when the connection is closed are

Back 93

rolled back

Front 94

must create a PreparedStatement object with

Back 94

prepareStatement method

Front 95

pass SQL statement as prepareStatement

Back 95

parameter

Front 96

use ___ ___ for replaceable parameters

Back 96

question marks

Front 97

use ___ ___ and pass the parameter number (starting with 1) and value to set values for execute

Back 97

set methods (like setString)

Front 98

on subsequent set calls, any parameters not explicitly set

Back 98

use the most recently supplied parameter value

Front 99

use ___, ___, or ___ to execute prepared statements

Back 99

executeUpdate, executeQuery, execute

Front 100

exist only in memory and are only available to the connection that created the statement

Back 100

prepared statements

Front 101

deleted when the connection is closed

Back 101

prepared statements

Front 102

when using prepared statements and the target database does not support prepared statements, the Java driver creates a dummy class that emulates prepared statement behavior

Back 102

the statements will execute against the database as standard SQL statements and return a result as appropriate

Front 103

stored in database and available to all connections

Back 103

stored procedure

Front 104

must create CallableStatement (with prepareCall method) and pass procedure name to execute

Back 104

stored procedure

Front 105

create ___ object to retrieve metadata

Back 105

DatabaseMetaData

Front 106

returns DBMS name

Back 106

getDatabaseProductName

Front 107

returns the product version number

Back 107

getDatabaseProductVersion

Front 108

returns the database's JDBC driver name

Back 108

getDriverName

Front 109

returns the JDBC driver version number

Back 109

getDriverVersion

Front 110

returns Boolean

Back 110

supportsANSI92EntryLevelSQL

Front 111

returns Boolean

Back 111

supportsANSI92IntermediateLevelSQL

Front 112

returns Boolean

Back 112

supportsANSI92FullLevelSQL

Front 113

returns Booleans based on support, not on auto commit status

Back 113

supportsTransactions

Front 114

accepts catalog, schemaPattern, and ProcedureNamePattern and returns stored procedures

Back 114

getProcedures

Front 115

accepts catalog, schemaPattern, TableNamePattern, and Types and returns table descriptions

Back 115

getTables

Front 116

returns available schemas

Back 116

getSchemas

Front 117

returns available catalogs (referred to as available databases in some DBMS products)

Back 117

getCatalogs

Front 118

database that supports full SQL (supportsANSI92FullLevelSQL()) will also return ___ for supportsANSI92IntermediateLevelSQL() and supportsANSI92EntryLevelSQL()

Back 118

true

Front 119

create ___ object to retrieve result set metadata

Back 119

ResultSetMetaData (call ResultSet objects getMetaData method)

Front 120

returns number of columns

Back 120

getColumnCount

Front 121

accepts column as integer and returns column name

Back 121

getColumnName

Front 122

accepts column as integer and returns number of digits left of decimal or number of characters in CHAR or VARCHAR type

Back 122

getPrecision

Front 123

accepts column as integer and returns number of digits right of decimal

Back 123

getScale

Front 124

accepts column number and returns the source table and column names

Back 124

getTableName

Front 125

accepts column number and returns integer value representing a data type

Back 125

getColumnType

Front 126

accepts column number and returns the column data type name

Back 126

getColumnTypeName

Front 127

scrollable
does not show changes to data

Back 127

TYPE_SCROLL_INSENSITIVE

Front 128

scrollable
dynamically updates to show changes to data

Back 128

TYPE_SCROLL_SENSITIVE

Front 129

not scrollable, allows forward navigation only

Back 129

TYPE_FORWARD_ONLY

Front 130

result is read only (cannot be modified)

Back 130

CONCUR_READ_ONLY

Front 131

result can be modified

Back 131

CONCUR_UPDATABLE

Front 132

use ___ to modify values

Back 132

update method (updateString, etc.)

Front 133

use ___ method to add row to result

Back 133

insertRow

Front 134

create a Statement object and pass the result set type constants to define the cursor
call the Statement object's execute method and pass the SQL query statement to create a ResultSet object

Back 134

to create a cursor

Front 135

call ___ to add statements to the batch

Back 135

addBatch

Front 136

call ___ to remove all statements from the batch

Back 136

clearBatch

Front 137

call ___ to execute statements added to batch

Back 137

executeBatch

Front 138

___ method returns an array of integer values with each entry in the array as the number of rows updated when a statement is executed

Back 138

executeBatch

Front 139

the same batch can include both ___ and ___ objects

Back 139

Statement, PreparedStatement

Front 140

___ clients affect how data is displayed, not how it is retrieved

Back 140

graphical

Front 141

graphical database clients often use ___ results instead of server-side cursors for data processing and display

Back 141

client-side

Front 142

API

Back 142

Application Programming Interface

Front 143

DBDL

Back 143

Database Definition Language

Front 144

DBMS

Back 144

Database Management System

Front 145

DML

Back 145

Data Manipulation Language

Front 146

ER

Back 146

Entity-Relationship

Front 147

JDBC

Back 147

Java Database Connectivity

Front 148

ODBC

Back 148

Open Database Connectivity

Front 149

SQL

Back 149

Structured Query Language

Front 150

XML

Back 150

Extensible Markup Language

Front 151

DDL

Back 151

Data Definition Language