Information Technology Reference
In-Depth Information
Data on
storage device
Figure 5.12
Logical and Physical Access
Paths
Physical access
path (PAP)
DBMS
Logical access
path (LAP)
Management
inquiries
Other
software
Application
programs
the inventory level for the same product by 200 units because more inventory was just re-
ceived. Without proper database control, one of the inventory updates might be incorrect,
resulting in an inaccurate inventory level for the product.
Concurrency control
can be used
to avoid this potential problem. One approach is to lock out all other application programs
from access to a record if the record is being updated or used by another program.
concurrency control
A method of dealing with a situation
in which two or more people need to
access the same record in a
database at the same time.
Manipulating Data and Generating Reports
After a DBMS has been installed, employees, managers, and consumers can use it to review
reports and obtain important information. For example, the Food Allergen and Consumer
Protection Act, effective in 2006, requires that food manufacturing companies generate re-
ports on the ingredients, formulas, and food preparation techniques for the public. Using a
DBMS, a company can easily manage this requirement.
Some databases use
Query-by-Example (QBE)
, which is a visual approach to developing
database queries or requests. Like Windows and other GUI operating systems, you can per-
form queries and other database tasks by opening windows and clicking the data or features
you want (see Figure 5.13).
In other cases, database commands can be used in a programming language. For example,
C++ commands can be used in simple programs that will access or manipulate certain pieces
of data in the database. Here's another example of a DBMS query: SELECT * FROM EM-
PLOYEE WHERE JOB_CLASSIFICATION = “C2”. The * tells the program to include
all columns from the EMPLOYEE table. In general, the commands that are used to manip-
ulate the database are part of the
data manipulation language (DML)
. This specific language,
provided with the DBMS, allows managers and other database users to access, modify, and
make queries about data contained in the database to generate reports. Again, the application
programs go through schemas and the DBMS before actually getting to the physically stored
data on a device such as a disk.
In the 1970s, D. D. Chamberlain and others at the IBM Research Laboratory in San
Jose, California, developed a standardized data manipulation language called
Structured Query
Language (SQL
, pronounced like
sequel
). The EMPLOYEE query shown earlier is written in
SQL. In 1986, the American National Standards Institute (ANSI) adopted SQL as the stan-
dard query language for relational databases. Since ANSI's acceptance of SQL, interest in
making SQL an integral part of relational databases on both mainframe and personal com-
puters has increased. SQL has many built-in functions, such as average (AVG), the largest
value (MAX), the smallest value (MIN), and others. Table 5.6 contains examples of SQL
commands.
data manipulation language
(DML)
The commands that are used to
manipulate the data in a database.
