COMP9120 · Database Management Systems
Database Application Development
Database Application Development is the Week-7 topic of COMP9120 Database Management Systems at the University of Sydney, where SQL stops living in a terminal and starts running inside real Java or Python programs. You learn the call-level APIs (JDBC and the Python DB-API), why prepared statements defeat SQL injection, how stored procedures cut network round trips, and where indexes speed up the back end. It is the foundation for Assignment 2 (a 13% group project), and the lecturer flags it as out of scope for the final exam and the in-class quiz.
What this chapter covers
- 01Why the impedance mismatch forces a call-level API between set-oriented SQL and record-oriented code
- 02The four-step JDBC model: connect, create a Statement, execute, iterate the ResultSet cursor
- 03The Python DB-API workflow with psycopg2: connection, cursor, execute, fetchone / fetchmany / fetchall
- 04How SQL injection works and why string concatenation of user input is never safe
- 05Prepared / parameterised statements: bind placeholders, reuse the query plan, block injection
- 06Stored procedures in PL/pgSQL with IN / OUT / INOUT parameters, called via CallableStatement or callproc
- 07How moving logic server-side reduces network round trips between client and database
- 08Indexes as an access path: B+-tree vs hash, clustered vs unclustered, and when to add one
- 09Error handling: SQLException state codes in Java and the psycopg2 exception hierarchy in Python
Cutting network round trips with a stored procedure
- +1Client-side, each SQL statement is its own request/reply, so one order = 3 round trips.
- +1Total client-side round trips = 3 statements x 200 orders = 600.
- +1A stored procedure bundles the three statements into one server-side routine invoked with a single call.
- +1With the procedure, one order = 1 round trip, so 200 orders = 200 round trips.
- +1Round trips saved = 600 - 200 = 400.
- +1That is a 400/600 = 66.7% reduction, so latency and network load fall (fewer round trips = faster).
Key terms
- Impedance mismatch
- The gap between set-oriented SQL (which returns whole relations) and a record-oriented host language such as Java or Python; bridged with a call-level API and a cursor that walks results one row at a time.
- JDBC
- Java Database Connectivity: a DBMS-independent Java API. The workflow is acquire a Connection, create a Statement, execute a query, and iterate the ResultSet cursor, catching SQLException on error.
- Python DB-API
- Python's standard database interface (PEP 249), implemented per engine (psycopg2 for PostgreSQL). You open a connection, get a cursor, execute a query, then fetchone / fetchmany / fetchall the rows.
- Prepared statement
- A parameterised query whose plan is prepared once, then executed with bound host variables supplied through placeholders (? in JDBC, %s or %(name)s in the Python DB-API). It blocks SQL injection and allows plan reuse.
- SQL injection
- An attack where user input is concatenated into a query string so that supplied text (for example ' OR '1'='1) changes the query's meaning. Prepared statements prevent it by never treating parameter values as SQL.
- Stored procedure
- Application logic stored and run inside the DBMS, written in an SQL extension such as PostgreSQL's PL/pgSQL. Parameters may be IN, OUT or INOUT; it reduces network round trips and adds an abstraction layer.
- CallableStatement / callproc
- The mechanisms that invoke a stored procedure: JDBC's CallableStatement with prepareCall("{call proc(?,?)}") and registerOutParameter, or the Python cursor's callproc() method.
- Index (clustered vs unclustered)
- An access path mapping search-key values to records. A B+-tree serves equality and range; a hash serves equality only. A clustered index stores data in key order (cheap ranges); an unclustered one can cost about one I/O per matching row.
Database Application Development FAQ
Is Database Application Development on the COMP9120 final exam?
No. The Week-7 lecturer states this material is not covered in the final exam or the in-class quiz; the quiz covers Weeks 1-9 except Week 7. It is assessed through Assignment 2, a 13% group project that builds a client interface plus an advanced back end with stored procedures and indexes. Confirm the exact scope and dates on Canvas for your offering.
Do I need Java or Python for Assignment 2?
Assignment 2 requires either Java (with JDBC) or Python (with the DB-API / psycopg2); you pick one and learn the language yourself. The database concepts are the same either way: connect, execute, iterate results, use prepared statements, and add stored procedures and indexes on the server side.
Can AI help me with database application development in COMP9120?
Yes, as a tutor rather than an answer service. Sia can explain, step by step, how a prepared statement defeats an injection string, why a stored procedure saves round trips, or how a B+-tree index differs from a hash index, and can walk through a small worked example. It will not write your Assignment 2 code for you or promise a specific grade; the point is to understand the API so you can build and debug your own submission.
Studying with AI? Sia — free AI data science tutor works through COMP9120 step by step.
Exam move
Treat this chapter as build-and-test, not memorise: set up PostgreSQL with pgAdmin early and get a tiny Java (JDBC) or Python (psycopg2) program to connect, run one SELECT, and loop over the results before you touch the assignment logic. Then practise the three security-critical moves in isolation: rewrite a concatenated query as a prepared statement, wrap a multi-statement update in a PL/pgSQL stored procedure and call it, and add an index to a column that appears in a WHERE or JOIN. Because this topic is assessed by Assignment 2 rather than the timed exam, the marks reward working, secure code and a sensible back end, so spend your effort making the program run correctly and handling errors gracefully rather than reciting definitions.
