Understanding Database 11 - Database Programming Techniques

Database Application system 

 

# 3-Tier Architecture

1. Client: GUI, Web interface, display of info [i.e. HTML, CSS, JS]
2. Application server or Web server: Application programs, web pages - stores biz rules, implement logic [i.e. divided by NodeJS, Python, Java, PHP, C; host language + SQL, NoSQL;  Data sub-language]
3. Database server: Database Management System - hosts DBMS (accept and process queries and commands) [i.e. MySQL, MongoDB]

• Interactive interface: interface where SQL/NoSQL commands can be typed directly into a monitor for execution
• Execute file of command: file created through an interactive interface (import tool; PHPMyAdmin, typing @<filename> in oracle)
• Application program: used as canned transactions by end users to query DB or execute transactions on DB (making airline reservations)

#The sequence of Interaction in the DB program

1. Client program opens or establishes a connection to DB server
2. Client program submits queries or updates to the DB 
3. No access needed -> Client program closes or terminates the connection to DB

#Approach to DB programming: Application Program

1. Using a library of DB functions or classes to connect DB, prepare and execute a query, execute an update, loop over the query result 
2. Embedding DB command in a general-purpose programming language, where a precompiled scans the source code, identify and extract database statement for processing by DBMS 
3. Designing a brand-new language from scratch is compatible with the DB model and query language - usually referred to as DB programming language. 

## Using a library of DB functions

1. Include/import the library of functions- API -> 1
2. create a connection to DB
3. prepare SQL statements and parameters
4. Execute SQL 
5. process query results
6. retrieve values from the processed query result
7. output result 

*** Cursor: control structure that enables iteration over records in DB (loop over query results, one record at a time) 

1. Initialize a cursor pointing to DB -> 7(python example)
2. Execute SQL/NoSQL statements to DB using cursor -> 8(python example)
3. fetch data into local variables from cursor (One tuple at a time) -> 9(python example)
4. iterates over -> 10, 11(python example)
5. close the cursor when done fetching all tuples -> 12(python example)

Lecture materials

Lecture materials

## Embedding DB command in a general-purpose programming language: Stored Procedure 

stored and executed by DBMS at the DB server (The other two are in application server)

- when it needed several application , if reduction in data transfer required, enhance the process of generating views

 

Databse Programming Techinque issues

1. Impedance mismatch occurs as a result of incompatibilities between a host programming language model and the database model – programming language data type differs from attributes data type.
2. Therefore, a different binding is needed for each programming language and DBMS because different programming languages have different data types
3. A binding is required to map each query result’s data structure (table), to the appropriate data structure in the programming language.
4. A mechanism is required to loop over the query result (one tuple at a time) to extract individual values from the tuples.