Databases Reference
In-Depth Information
Columns and their types
are defined when a
table is created.
Column names must
be distinct.
Rows must
contain data for
each column.
Individual cell
values can be
updated.
ID
ITEM_ID
PRICE
COLOR
SIZE
1
12744
14.99
green
large
2
56289
15.99
yellow
extra large
All data in a
single row
is grouped
together on
disk.
New rows are
inserted at the
end of a table.
3
43589
13.99
blue
small
4
35734
24.45
red
medium
insert
Columns may reference IDs of other tables.
Figure 3.2
The basic rules of a row-store system. Row stores are created by first
declaring a table with a fixed number of columns, each with a distinct name and a data
type. Data is added row-by-row to the system, and each row must have data present for
each column. All the data in a row is added as a unit and stored as a unit on disk.
Tables are created using a specialized language called a
data definition language
or
DDL
. The entire table, with all column definitions and their data types, must
be created before the first row is inserted into the table. Indexes are also cre-
ated for columns on large tables that have many rows to increase access speed.
Columns must have unique names within a table and a single data type (for
example, string, date, or decimal) which is created when a table is first defined.
The semantics or meaning of each column is stored in an organization's data
dictionary.
New data is added to a table by inserting new rows using
INSERT
statements or
bulk loading functions. Repeating fields are associated with parent rows by ref-
erencing the parent's row identifier.
The
SQL
INSERT
statement can be used to insert a new row with any available
data that's provided to the
INSERT
statement.
SQL
UPDATE
operations can then
be used to change specific values of a row, but row identifiers must be used to
indicate what row to update.
Reports are generated to create logical business documents from each table by
selecting all related rows with
JOIN
statements.
Database rules, called
triggers
, can be set up to automatically delete all rows asso-
ciated with a business record.
Many commercial
RDBMS
s started with simple features. In time, new features were
added to meet the needs of large
enterprise resource planning
(
ERP
) systems until they
became robust and hardened for reliable commercial use. Initially, organizations
needed a way to store financial information in order to produce accurate business
statements.
RDBMS
s were created to store information about assets, sales, and pur-
chases from which a
SQL
reporting system created queries (reports) to show the
