Database Final 2

Front 1

steps for using JDBC

Back 1

1. establish two running programs (database server and client/application server)
2. create connectivity between the two
3. perform database query
4. process the return results

Front 2

establish two running programs

Back 2

database side: ensure jdbc driver installed
java side: import java.sql.*

Front 3

create a connection

Back 3

try{
Class.forName("org.postgresql.Driver")
}
catch(ClassNotFoundException ex){
System.out.println(ex);
}

Front 4

make connection

Back 4

connection con = null;
try{
con = DriverManager.getConnection(hostcomputername, user, passwd);
}

Front 5

ask something for database

Back 5

try{
Statement stmt = con.createStatement();
Resultset rs = stmt.executeQuery(query);
}

Front 6

prepared statement

Back 6

PreparedStatement ptstmt = con.prepareStatement("Insert Into T (c1, c2, c3) Values "
+ "(?, ?, ?)");

Front 7

EDI

Back 7

electronic data interchange
a form of electronic commerce that supports computer to computer communications

Front 8

Compile steps for query

Back 8

1. Lex and Parse the input
2. Compile parse-tres to abstract code
3. optimize abstract code
4. compile abstract code to physical code
5. optimize physical code

Front 9

sql execution steps for query

Back 9

1. Lex and Parse input
2. create a logical plan
3. optimize logical plan
4. consider, physical trade-offs, generate physical plan

Front 10

three primary classes of join algorithm

Back 10

1. block-nested loop
2. merge-join
3. hash-join

Front 11

I/O cost of block-nested loops
(runs through the complete inner relation, S, for each chunk of R)

Back 11

if S fits in M-1 buffers: B(R) + B(S)
if S does not fit: (B(S)/M-1) * (M-1 + B(R))

Front 12

difference between block-nested loop and simple nested loop

Back 12

for block-nested loop you dedicate most memory to the outer loop, compared to simple that ignores the availability of RAM and ignores data on disk and reads in blocks

Front 13

cost of Merge-Join

Back 13

B(R) + B(S)

Front 14

for two phase external sorting, sorting a file f bigger than available memory requires....

Back 14

2*( |f| / B) I/O reads + 2 * ( |f| /B) I/O writes
|f|: size of the file in bytes
B: number of bytes in a block

Front 15

merge-sort cost with no index

Back 15

5(B(R) + B(S))

Front 16

affine model

Back 16

f(n) = c + c'(n-1)
c: seek time for first block
c': weighted average of rotational latency + track to rack seek time for each additional block

Front 17

estimating result size of selects in tuples

Back 17

Result size: T(R)/V(R,A)

Front 18

estimating result size of multiple selects

Back 18

Result size: T(R)/(V(r, a) *(r, b) * ...)

Front 19

estimating size of disjunction

Back 19

T(S) = n(1 - (1 - m1/n)(1 - m2/n))
m1 tuples that satisy predicate 1
m2 tuples that satisfy predicate 2

Front 20

I/O cost of Table Scan

Back 20

I/O cost: c * B(R)
or c * B(R) + height of B+ tree

Front 21

estimating join size results

Back 21

T(R)T(S)/max(V(R,y),V(S,y))