3. Application Development and Admin¶

Required Reading¶

A Web server is a program running on the server machine, which accepts requests from a Web browser and sends back results in the form of HTML documents.
The common gateway interface (CGI) standard defines how the Web server communicates with application programs.
The application program typically communicates with a database server, through ODBC, JDBC, or other protocols, in order to get or store data.
HTTP is connectionless which means that the server does not maintain any information about the client between requests, and once a request is processed, the connection is closed.
HTTP is connectionless because:
- Servers can only handle a limited number of connections at a time.
- Closing the connection after each request allows the server to free up resources and be ready to handle the next request.
The Java servlet specification defines an application programming interface for communication between the Web server and the application program.
Server-side Scripting languages:
- Jscript from Microsoft.
- Java Server Pages (JSP) from Sun.
- PHP.
- Cloud Fusion Markup Language (CFML).
- Zope.

HTTP

Caching frequent database queries in a fast-access memory of the web server, as long as the data is not changed frequently.
Connection Pools:
- Opening and closing a connection to a database server for each request is very inefficient, a connection pool between the Web server and the database server is maintained.
- each request will get one connection from the pool, and when the request is finished, the connection is released to the pool.
- This way, the number of connections to the database server is limited, and the overhead of opening and closing connections is reduced.
Caching the final HTML pages:
- If the server responds with dynamically generated HTML pages, the overhead of generating the pages for each request is high.
- The computed HTML may be cached in the Web server, and the Web server can respond with the cached HTML page, instead of recomputing it.
Using Materialized Views:
- If a query is using heavy joins, it may be more efficient to create a materialized view of the result and query the view instead of the original tables.

DBMS queues

Detect bottlenecks
Tune system:
- Hardware level: if bottlenecks are due to hardware limitations, then the hardware should be upgraded, e.g., more memory, faster disk, etc.
- DBMS parameters Level: such as buffer size and checkpoint interval.
- Schema and transactions level: create indices, materialized views, use transactions appropriately, or optimize queries.

The right way to average out the numbers is to take the time to completion for the workload, rather than the average throughput for each transaction type.
The Harmonic mean is the correct way to average out the throughputs of different transactions. The harmonic mean is the reciprocal of the average of the reciprocals of the individual throughputs. as hm = n / (1/t1 + 1/t2 + ... + 1/tn).
Database-Application Classes:
- Online Transaction Processing (OLTP) and Decision Support (including Online analytical processing (OLAP)).
- OLTP implements a large number of small sequential updates, while OLAP implements a smaller number of long-running complex queries.
- DMBS can be optimized for OLTP or OLAP, or a mixture of both.

Performance metrics:
- TPS: Transactions per second, aka, throughput.
- Price per TPS: The cost of the system per unit of throughput.
An External audit is required for TPC benchmarking.
TPC-A:
- In 1989.
- Simulates a bank, where multiple sequential transaction updates are required per operation (e.g., deposit, withdrawal, transfer, etc.).
- Also includes simulations of users and end-to-end terminals (e.g., ATMs).
TPC-B:
- Designed to test the core performance of a DBMS and its interaction with the operating system.
TPC-C:
- Simulates an online retail business (includes, orders, payments, check order status, stock level, etc.).
- Widely used for Transaction processing Apps.
TPC-D:
- Simulates a decision support system (e.g., a data warehouse with sales/customers, orders, etc.).
- Widely used for Decision support Apps.
- Has 17 SQL queries, each with a different workload.
TCP-R:
- Used for Reporting applications.
- Similar to TPC-D, but has 22 SQL queries.
TCP-H:
- Ad-HOC benchmarking.
- Relies on TCP-R, but prevents materialized views from being used.
TCP-W:
- Web benchmarking.
- Simulates end-to-end Web applications, including static content, dynamic content, and database access.

SQL Standards:
1. Framework: provides an overview of the standard.
2. Foundation: defines the basics of the standards: types, schemas, tables, views, etc.
3. CLI (Call Level Interface): defines the interface between the application and the DBMS.
4. PSM (Persistent Stored Modules): defines extensions to SQL to support stored procedures and triggers.
5. Bindings: defines the standard of embedded SQL in different programming languages.
6. Distributed Transaction Processing.
7. Temporal: defines the standard for temporal data.
8. Multimedia standards.
9. MED (Management of External Data): defines the standard for accessing external data.
10. OLB (Object Language Binding): defines the standards of embedding SQL in JAVA.
Database Connectivity Standards:
- ODBC (Open Database Connectivity):
  - A standard for accessing databases from different programming languages; It is based on SQL/CLI.
  - ODBC does not support two-phase commit.
Object Database Standards
- The objective is to develop a standard architecture for distributed software applications based on the object-oriented model.
Simple Object Access Protocol (SOAP):
- A remote procedure call standard uses XML to encode data (both parameters and results), and uses HTTP as the transport protocol; that is, a procedure call becomes an HTTP request.

Activities Of E-commerce:
- Pre-Sale: includes advertising, marketing, and informing the potential buyer about the product.
- The Sale Process: includes price negotiation, quality of service, and other contractual matters.
- The marketplace: includes Auctions, reverse auctions, and other forms of trading such as stock exchanges.
- Payments.
- Delivery of the product.
- Post-Sale: this includes customer support, product returns, and other post-sale activities.
E-catalog:
- A database of products and services that can be accessed by customers.
- Must provide browsing and searching capabilities.
Marketplaces:
- Reverse Auction: a buyer posts a request for a product or service, and the sellers bid on the request.
- Auction: a seller posts a product or service, and the buyers bid on the product.
- Exchange: a buyer and seller agree on a price, and the transaction is completed.
Order Settlement:
- Includes the payment for the product, and the delivery of the product.
- The payment must be secure.
- The buyer must be authenticated, the credit card address must match the billing address, and the credit card must be valid to avoid fraud.
- The seller must not pass the credit card info to any other party.
- Data must be encrypted.
- The Secure Electronic Transaction (SET) must be followed.

Silberschatz, A., Korth, H.F., & Sudarshan, S. (2001). Database System Concepts (4^th ed.). New York, NY: McGraw-Hill. Available at Database System Concepts 4^th Edition By Silberschatz-Korth-Sudarshan.pdf ↩