Database Reference
In-Depth Information
characters in length, in the
orders
table, a much shorter
product_id
, of perhaps
a few bytes, can be used and stored in place of the product's name.
Another advantage is that changes and corrections are easily made. In this
example, the table
category
is used to categorize each product. If it is discovered
that an incorrect category was assigned to a particular product item, only the
category_id
in the
product
table needs to be updated. Without the
product
and
category
tables, it may be necessary to update hundreds of thousands of
records in the
orders
table.
A third advantage is that products can be added to the database prior to any orders
being placed. Similarly, new categories can be created in anticipation of entirely
new product lines being added to the online retailer's offerings later.
In a relational database design, the preference is not to duplicate pieces of data
such as the customer's name across multiple records. The process of reducing
such duplication is known as
normalization
. It is important to recognize that a
database that is designed to process transactions may not necessarily be optimally
designed for analytical purposes. Transactional databases are often optimized to
handle the insertion of new records or updates to existing records, but not
optimally tuned to perform ad-hoc querying. Therefore, in designing analytical
data warehouses, it is common to combine several of the tables and create one
larger table, even though some pieces of data may be duplicated.
Regardless of a database's purpose, SQL is typically used to query the contents of
the relational database tables as well as to insert, update, and delete data. A basic
SQL query against the
customer
table may look like this.
SELECT first_name,
last_name
FROM customer
WHERE customer_id = 666730
first_name last_name
Mason Hu
This query returns the customer information for the customer with a
customer_id
of 666730. This SQL query consists of three key parts:
•
SELECT:
Specifies the table columns to be displayed
•
FROM:
Specifies the name of the table to be queried
