Databases Reference
In-Depth Information
Tables, Rows, and Columns
A
, is a two-
dimensional structure used to hold related information. A database consists of
one or more related tables.
table
in a relational database, alternatively known as a
relation
Don't confuse a relation with relationships. A relation is essentially a table, and a rela-
tionship is a way to correlate, join, or associate two tables.
in a table is a collection or instance of one thing, such as one
employee or one line item on an invoice. A
A
row
table
The basic construct of a relational data-
base that contains rows and columns of
related data.
contains all the informa-
tion of a single type, and the piece of data at the intersection of a row and a
column, a
column
, is the smallest piece of information that can be retrieved with
the database's query language. (Oracle's query language, SQL, is the topic of
Chapter 2, “SQL*Plus and iSQL*Plus Basics.”) For example, a table with
information about employees might have a column called
field
that
contains all of the employees' last names. Data is retrieved from a table by
filtering on both the row and the column.
LAST_NAME
SQL, which stands for Structured Query Language, supports the database compo-
nents in virtually every modern relational database system. SQL has been refined and
improved by the American National Standards Institute (ANSI) for more than 20
years. As of Oracle9i, Oracle's SQL engine conforms to the ANSI SQL:1999 (also
known as SQL3) standard, as well as its own proprietary SQL syntax that existed in
previous versions of Oracle. Until Oracle9i, only SQL:1992 (SQL2) syntax was fully
supported. As of Oracle 10g, the Core SQL:2003 features are fully supported with a
couple minor exceptions.
Primary Keys, Datatypes, and Foreign Keys
The examples throughout this topic will focus on the hypothetical work of Scott
Smith, database developer and entrepreneur. He just started a new widget com-
pany and wants to implement a few of the basic business functions using the Ora-
cle relational database to manage his Human Resources (HR) department.
relation
A two-dimensional structure used to
hold related information, also known
as a table.
Most of Scott's employees were hired away from one of his previous employers,
some of whom have over 20 years of experience in the field. As a hiring incentive,
Scott has agreed to keep the new employees' original hire date in the new database.
You'll learn about database design in the following sections, but let's assume
for the moment that the majority of the database design is completed and some
tables need to be implemented. Scott creates the
row
A group of one or more data elements in
a database table that describes a per-
son, place, or thing.
table to hold the basic
employee information, and it looks something like this:
EMP
