Databases Reference
In-Depth Information
Exhibit 21-3.
Seven important queries and their relative frequency of
execution.
Query
Number
Relative Frequency of
Query Execution
SQL Query
1
Select * from Department where DeptName =
“Accounting”
25 percent
2
Select * from Department where DeptName = “Sales”
or DeptName = “Production”
5 percent
3
Select * from Employee, Department where Budget =
200,000 and Employee.DeptName = Depart-
ment.DeptName
15 percent
4
Select * from Department where Budget = 500,000
5 percent
5
Select * from Department where 100,000 < Budget and
Budget < 500,000
15 percent
6
Select * from Employee where EmpName = “Jones”
20 percent
7
Update Employee
15 percent
umn as the primary key. The Department table has two columns with Dept-
Name as the primary key. In addition, assume that the example workload
consists of the six representative queries presented in Exhibit 3. A repre-
sentative query is a query that is representative of homogeneous class of
queries. For example, Query 6 is representative of queries in the form:
Select <attribute list> from Employee where EmpName = <some name>
Also assume that while updates to the Employee table are 15 percent of the
workload, updates to the Department table are not significant because they
seldom occur.
With the relational schema and the anticipated workload in place, the
DBA is ready to begin performing the four phases in the initial physical
database design process.
Phase 1: Determine File Structures
Most DBMSs support three basic file structures: heap files, sorted files,
and hashed files. Each of these structures organize multiple data pages. A
data page is a unit of data that is transferred between disks and main mem-
ory and usually contains several database records. DBAs design physical
databases so the number of data pages being transferred is minimized for
frequently performed queries.
Two of the three basic file structures involve a search key. A search key
is one or more columns of a table that are frequently used to access records
of the file. Often, but not always, the most frequently used search key is the
primary key of the table. DBAs design physical databases to efficiently
access database tables using search keys. Some tables have multiple search
Search WWH ::
Custom Search