Application Programming Flashcards
Embedded SQL
placing of SQL statements directly in a host program
Static SQL
embedded SQL in the case that the SQL statements in the program are static (they are the same each time the program is ran)
all statements begin with an introducer and end with a terminator
Static Embedded SQL program layout (in C)
introducer = “EXEC SQL”
“EXEC SQL INCLUDE SQLCA”
“Exec SQL begin declare section”
[declare stuff]
“Exec SQL end declare section”
[fetch inputs from user]
“Exec SQL” [query here]
(The variable defined above referred to as ‘:variable’
Dynamic Embedded SQL query
char* sqlprog = [query here]
“EXEC SQL prepare dynprog from :sqlprog”
char account[] = blah
“Exec SQL execute dynprog using :acount” (replace ? with account variable)
Cursors
data structures that allow for the handling of SQL queries that return multiple rows
Cursor structure
“Exec SQL declare [cursor_name] Cursor for [query] ;”
“Exec SQL open [cursor name]”
“Exec SQL fetch into :variable1, :variable2 …;”
"while (sqlca.sqlcode != 100) {
printf();
EXEC SQL fetch into
:variable1, :variable2
}"
“Exec SQL close [cursor name]”
Components of Client Server architecture
database client - connects to DB to manipulate data
client software - provides general and specific capabilities
Two Tier Client Server Model
data server and client
client tier communicates directly with data tier.
Pros and cons of Two Tier model
pro: flexible need not be restricted to predefined queries
cons: security, more code shipped to client, not good for large organizations
3 tier client server model
user layer, middleware layer, database layer
3 tier client server model for ODBC
application program, odbc driver manager, db server
ODBC Starter code
“HENV ;” (environment variable)
“HDBC ;” database connection variable
“SQLAllocEnv(&env);” (allocate sql environment)
“SQLAllocConnect(env, &conn);” (allocate sql connection)
“SQLConnect(conn, server url, …)”
“SQLDisconnect(conn);”
“SQLFreeConnect(conn);”
“SQLFreeEnv(env);”
JDBC Code
“Class.forName(driver)” (find, open, load appropriate driver)
“Connection conn = DriverManager.getConnection(url, user, pass);”
“Statement stat = conn.createStatement();”
“ResultSet rs = null;”
“rs = stat.executeQuery(sql_command);”
while(rs.next()) {
var = rs.getString();
}
entity set
a set of entities (think a table) that share the same properties
domain
set of permitted values for each attribute
composite attributes
an attribute consisting of its own attributes
relationship set
a mathematical relation among 2 or more entities
an instance of a relationship set is a set of relationships
can have attributes of its own
relationship set degree
the number of entities that participate in a relationship set
Cardinality
the number of entities to which another entity can be associated via a relationship set
ER Roles
aka line from entitty to relationship diamond back to same entity
ER Arrows
The arrow head represents the 1 side of the cardinality
= one to one
—- = many to man
Schema
the logical structure of the db
physical schema- design at a physical level
logical schema- design at a logical level
Instance
the actual content of the database at a particular point in time
