Database Concepts - Data Warehouse Systems: Design and Implementation - page 41

For example, the following query
WITH CustomerOrders AS (
SELECT O.CustomerID, O.OrderID,
SUM(D.Quantity * D.UnitPrice) AS Amount
FROM Orders O, OrderDetails D
WHERE O.OrderID = D.OrderID
GROUP BY O.CustomerID, O.OrderID )
SELECT CustomerID, MAX(Amount) AS MaxAmount
FROM CustomerOrders
GROUP BY CustomerID
combines in a single statement the view definition and the subsequent query
given in the previous section. It is worth noting that several temporary tables
can be defined in the WITH clause. We will extensively use CTEs throughout
this topic.
2.5 Physical Database Design
The objective of physical database design is to specify how database
records are stored, accessed, and related in order to ensure adequate perfor-
mance of a database application. Physical database design is related to query
processing, physical data organization, indexing, transaction processing, and
concurrency management, among other characteristics. In this section, we
provide a very brief overview of some of those issues that will be addressed
in detail for data warehouses in Chap. 7 .
Physical database design requires one to know the specificities of the given
application, in particular the properties of the data and the usage patterns of
the database. The latter involves analyzing the transactions or queries that
are run frequently and will have a significant impact on performance, the
transactions that are critical to the operations of the organization, and the
periods of time during which there will be a high demand on the database
(called the peak load ). This information is used to identify the parts of the
database that may cause performance problems.
There are a number of factors that can be used to measure the performance
of database applications. Transaction throughput is the number of
transactions that can be processed in a given time interval. In some systems,
such as electronic payment systems, a high transaction throughput is critical.
Response time is the elapsed time for the completion of a single transaction.
Minimizing response time is essential from the user's point of view. Finally,
disk storage is the amount of disk space required to store the database files.
However, a compromise usually has to be made among these factors. From a
general perspective, this compromise implies the following factors:
1. Space-time trade-off: It is often possible to reduce the time taken to
perform an operation by using more space, and vice versa. For example,