Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/151

Click to flip

151 Cards in this Set

  • Front
  • Back
a database contains information stored in a ___ format
structured
a relational database contains two or more
related tables
related tables are not supported in
flat-file or hierarchical databases
relational database compared to flat-file
better support for multiple users
data operations require complex, specialized software
flat-file database compared to relational
smaller files
allows data to be accessed using a variety of programming languages
operating system file access software is used for data access
number of rows (tuples) in a relation
cardinality
number of attributes in a relation (columns in a table)
degree
row or record
tuple
a virtual table based on a query
view
whether or not NULL values are allowed
nullability
table required to support many-to-many relationships
intersection table
identifying characteristic of an entity
attribute
table (relation) is the basic storage unit
made up of rows (tuples) and columns (attributes)
indicated by a zero and a set of crows feet or asterisk
zero-to-many
indicated by a line and a set of crows feet (many) or asterisk
one-to-many
documented using a zero and a line
zero-to-one
documented using a line and a line
one-to-one
identify attribute domains
create ER diagrams
identify primary and foreign keys
conceptual design
identify relationships and relationship cardinality
normalize data
logical design
identify physical database objects including tables, views, primary indexes, and secondary indexes
create data dictionary
physical design
an attribute can contain only a single value
each row must be uniquely identified
1NF
all non-key attributes must be functionally dependent on the primary key
2NF
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)
3NF
any candidate key cannot be dependent on any part of another candidate key
Boyce-Codd normal
typical to normalize to the
3NF
denormalization is used to
combine data for faster retrieval and updates
each item can be uniquely identified, enforced through primary key
entity integrity
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
relational integrity
ensuring that the value entered in a column is valid, enforced through constraints, data types, and sometimes through triggers
domain integrity
ensuring that business rules are met and enforced through business rules (constraints and triggers)
enterprise integrity
enforce uniqueness
primary key
enforce relationships
foreign key
when a row is deleted from the referenced table, all referencing rows are also deleted
ON DELETE CASCADE
when a key value is updated in the referenced table, all referencing row key values are also updated
ON UPDATE CASCADE
require value matching wildcard, within range boundaries, or in value list
check
provides default value for column
default
provide custom access to a relation or set of relations based on a selection query
view
order data and improve read performance but can degrade write performace
index
sorts data in order and shares a physical structure with the table
primary (clustered) index
organizes data but does not change physical sort order
secondary (non-clustered)
model for DDL statements used during physical design phase
DBDL
statements used to create, modify, and delete database objects
DDL
statements used to modify table contents
DML
remove granted permissions
REVOKE
deny permission
DENY
returns qualifying rows from right table and all rows from the left table
left outer join
returns qualifying rows from the left table and all rows from the right table
right outer join
all SELECT column_list items must be included in ___ ___ list or be aggregates
GROUP BY
CREATE syntax
CREATE tablename(column_name data_type optional_nullability optional_constraint, ...)
_
replaces one character of any character type
returns only some of the columns from the source relation
(pi)
projection
returns only some of the rows from the source relation
(sigma)
selection
combine relations into single result relation, removing all duplicates
(U)
union
find rows common to two relations and return as new relation
(upside down U)
intersection
return single relation based on qualifying rows in two relations
(two triangles touching at point)
join
natural join assumes join condition and do not explicitly specify the condition
return the rows from one relation that are not present in the other relation
(-)
difference
returns every possible pair value as new relation
(X)
Cartesian product
expression structure for
selection and projection
unary statement - operator followed by relation name
expression structure for
union, intersection, join, difference, and Cartesian product
use binary operators to combine unary statements
JDBC bases its structure on the structure of
ODBC
___ can be used to connect to and access any ODBC-compliant database product for which the vendor provides a ___ driver
JDBC
applets compile on the ___ and require ___
server, JDBC
___ are interpreted, can be embedded on a Web page, and do not rely on JDBC
scripts
to have an application reference multiple data sources, ___
have the application load multiple drivers
you must load the driver before it can be referenced by
DriverManager methods
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
Pure Java Driver
communicates with the ODBC driver to pass requests through to the database rather than directly with the database
JDBC-ODBC bridge driver
makes calls directly to the database
Native API driver
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
JDBC-Net driver
general syntax using DriverManager getConnection method
Connection conn = DriverManager.getConnection(url)
Connection conn = DriverManager.getConnection(url)

url can be
string variable or literal string
Connection conn = DriverManager.getConnection(url)

general syntax for url is
jdbc:driver_name:subname
Connection conn = DriverManager.getConnection(url)

driver_name is the
database connectivity mechanism (loaded driver or other connectivity method)
Connection conn = DriverManager.getConnection(url)

subname values
vary by driver
Connection conn = DriverManager.getConnection(url)

url must start with
jdbc
___ can return a valid connection or SQLException
getConnection
___ and ___ must be provided when required by the DBMS for validation
user name, password
use ___ object to execute individual statements
Statement
executing individual statements with Statement object
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
what is created by executeQuery method
result set
use get methods (getString, etc.) to
retrieve column values
retrieves character (string) values, CHAR and VARCHAR type values
getString
retrieves INTEGER type values (32-bit signed)
getLong
retrieves SMALLINT type values (16-bit signed)
getShort
retrieves DECIMAL type values
getBigDecimal
using wrong get method
causes exception to be thrown
retrieve values by column name or number with numbering starting with
1
cursor initially points to empty row immediately before the first row, must call ResultSet ___ method to advance and increment cursor
next
returns number of rows changed
executeUpdate
returns result set
executeQuery
returns Boolean value, true if the statement executes without error
execute
the Connection object ___ method controls automatic statement commit
setAutoCommit
set to true - statements commit as executed
set to false - statements must be committed manually
any statements not committed when the connection is closed are
rolled back
must create a PreparedStatement object with
prepareStatement method
pass SQL statement as prepareStatement
parameter
use ___ ___ for replaceable parameters
question marks
use ___ ___ and pass the parameter number (starting with 1) and value to set values for execute
set methods (like setString)
on subsequent set calls, any parameters not explicitly set
use the most recently supplied parameter value
use ___, ___, or ___ to execute prepared statements
executeUpdate, executeQuery, execute
exist only in memory and are only available to the connection that created the statement
prepared statements
deleted when the connection is closed
prepared statements
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
the statements will execute against the database as standard SQL statements and return a result as appropriate
stored in database and available to all connections
stored procedure
must create CallableStatement (with prepareCall method) and pass procedure name to execute
stored procedure
create ___ object to retrieve metadata
DatabaseMetaData
returns DBMS name
getDatabaseProductName
returns the product version number
getDatabaseProductVersion
returns the database's JDBC driver name
getDriverName
returns the JDBC driver version number
getDriverVersion
returns Boolean
supportsANSI92EntryLevelSQL
returns Boolean
supportsANSI92IntermediateLevelSQL
returns Boolean
supportsANSI92FullLevelSQL
returns Booleans based on support, not on auto commit status
supportsTransactions
accepts catalog, schemaPattern, and ProcedureNamePattern and returns stored procedures
getProcedures
accepts catalog, schemaPattern, TableNamePattern, and Types and returns table descriptions
getTables
returns available schemas
getSchemas
returns available catalogs (referred to as available databases in some DBMS products)
getCatalogs
database that supports full SQL (supportsANSI92FullLevelSQL()) will also return ___ for supportsANSI92IntermediateLevelSQL() and supportsANSI92EntryLevelSQL()
true
create ___ object to retrieve result set metadata
ResultSetMetaData (call ResultSet objects getMetaData method)
returns number of columns
getColumnCount
accepts column as integer and returns column name
getColumnName
accepts column as integer and returns number of digits left of decimal or number of characters in CHAR or VARCHAR type
getPrecision
accepts column as integer and returns number of digits right of decimal
getScale
accepts column number and returns the source table and column names
getTableName
accepts column number and returns integer value representing a data type
getColumnType
accepts column number and returns the column data type name
getColumnTypeName
scrollable
does not show changes to data
TYPE_SCROLL_INSENSITIVE
scrollable
dynamically updates to show changes to data
TYPE_SCROLL_SENSITIVE
not scrollable, allows forward navigation only
TYPE_FORWARD_ONLY
result is read only (cannot be modified)
CONCUR_READ_ONLY
result can be modified
CONCUR_UPDATABLE
use ___ to modify values
update method (updateString, etc.)
use ___ method to add row to result
insertRow
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
to create a cursor
call ___ to add statements to the batch
addBatch
call ___ to remove all statements from the batch
clearBatch
call ___ to execute statements added to batch
executeBatch
___ method returns an array of integer values with each entry in the array as the number of rows updated when a statement is executed
executeBatch
the same batch can include both ___ and ___ objects
Statement, PreparedStatement
___ clients affect how data is displayed, not how it is retrieved
graphical
graphical database clients often use ___ results instead of server-side cursors for data processing and display
client-side
API
Application Programming Interface
DBDL
Database Definition Language
DBMS
Database Management System
DML
Data Manipulation Language
ER
Entity-Relationship
JDBC
Java Database Connectivity
ODBC
Open Database Connectivity
SQL
Structured Query Language
XML
Extensible Markup Language
DDL
Data Definition Language