via SQL. (You’ll learn more about the role
of the JOIN clause in SQL queries later in
this series.) Only experienced database
designers should denormalize. Increasing
redundancy might marginally improve
query performance, but it will always
increase the overall programming effort
and complexity, because multiple copies
of the same data must be kept in sync. The
process of syncing multiple copies of data
threatens data integrity.
DATA ACCESS AND THE SQL
EXECUTION ENVIRONMENT
Oracle software runs on many different
hardware architectures and operating
systems. The computer on which the Oracle
Database software resides is known as
the Oracle Database server. Additionally,
Oracle Database server can refer to the
Oracle Database software and its data. (The
remainder of this article refers to the latter
definition.) Specialized tools installed on
users’ computers enable them to access
data on the Oracle Database server. These
tools—called clients, or front ends—are
used to send SQL commands to the server,
or back end. Three such tools are Oracle SQL
Developer, Oracle’s SQL*Plus, and Oracle
Application Express SQL Workshop.
SQL commands instruct the server to
perform certain actions in the database.
A command can create a table, query a
table, change a table, add new data, or
update existing data, among other things.
In response to a query request, for example,
the server returns a result set to the client,
which then displays it to the user.
Before you can begin to use any of these
tools to communicate SQL requests to the
Oracle Database server, you must create
a database connection. If you are using
SQL Developer, read the “ 1. 4 Database
Connections” section in the Oracle SQL
Developer User’s Guide Release 2. 1, at bit.ly/
quzezk, to learn how to set up a connection
to your database. If you are using SQL*Plus
or SQL Workshop within Oracle Application
Express, ask your database administrator
to create a database connection for you.
You also need a database administrator
to create a username and a password for
you, with appropriate permissions that
Figure 5: A database schema diagram showing a mandatory foreign key relationship between the EMPLO YEE
and DEPAR TMENT tables
enable you to create your own objects. See
the “CREATE USER” section of the Oracle
Database SQL Language Reference 11g
Release 2 ( 11. 2) at bit.ly/qbCmy0.
USING ORACLE SQL DEVELOPER
Once you are connected to the database,
viewing data in the Oracle SQL Developer
environment is relatively straightforward.
Figure 6 shows the Tables node within the
Connections Navigator, a tree-based object-browser pane in Oracle SQL Developer.
To view the details of any of your tables,
expand the Tables node by clicking the plus
sign and then double-clicking the individual
table name. Figure 7 shows the result of
double-clicking the EMPLOYEE table in
the Connections Navigator. The table’s
column names are displayed vertically in the
Connections Navigator pane, and several
tabs that provide details about the table are
displayed to the right of that pane.
Figure 6: Tables node in the Oracle SQL Developer
Connections Navigator
COLUMNS AND DATAT YPES
By default, the Columns tab is displayed
first. It lists the table’s column names and
datatypes. It also shows which columns
allow null values (that is, the absence of
a value), which column or columns are
defined as the table’s primary key, and
any column comments. (You can see that
no primary key has been defined for the
EMPLOYEE table in Figure 7. You’ll learn
how to create a primary key later in this
article series.)
Every column has a datatype, chosen
during physical modeling and defined
when the table was created. For example,
the datatype for the SALARY column in the
EMPLOYEE table is NUMBER. Any column
defined with the NUMBER datatype permits
only numeric data. No text and no alpha
characters such as monetary symbols may
be stored in a column defined with this
datatype. Note that the SALARY column’s
datatype is defined as NUMBER( 9, 2). The
first number in the parentheses ( 9 in this
example) is referred to as the precision, and
the second number ( 2 in this example) is
known as the scale. This precision and this
scale mean that the SALARY column can
have a maximum of nine digits before the
decimal point and two digits after it (useful
for columns containing monetary data). If
a value with more than two digits after the
decimal point is inserted into the SALARY
column, no error will occur; the value will
simply be automatically rounded by the
Oracle Database server.
