1.15.2.3 SELECT INTO #table_name
<select_into_local_temporary_table> ::= SELECT also see <select>
[ <summarizer> ] in Chapter 3
[ <row_range> ]
<select_list>
INTO <temporary_table_name>
[ <from_clause> ]
[ <where_clause> ]
[ <group_by_clause> ]
[ <having_clause> ]
<temporary_table_name> ::= "#" { ( <alphabetic> | <numeric> ) }
With this format the table name must begin with a number sign (#) to inform
SQL Anywhere that it is a table name rather than a variable name appearing in
the INTO clause.
The SELECT INTO #table_name method is very powerful — not only does
it create the table but it loads it with data at the same time. Here’s how it works:
The temporary table column names and data types are taken from the select list,
and the rows are filled by executing the SELECT. This means the columns in
the select list must actually have names; in the case of an expression you can
use “AS identifier” to give it a name. For more information about the SELECT
statement, see Chapter 3, “Selecting.”
Here is an example where an exact copy of table t is made in the temporary
table #t; it has the same column names, same data types, and same rows of data:
CREATE TABLEt( permanent table
c1 INTEGER,
c2 VARCHAR ( 10 ),
c3 TIMESTAMP );
INSERT t VALUES ( 1, 'AAA', CURRENT TIMESTAMP );
INSERT t VALUES ( 2, 'BBB', CURRENT TIMESTAMP );
SELECT * INTO #t FROM t; temporary copy
Tip: If you want to know what the data type of a column actually is, code it in
a SELECT and call the EXPRTYPE function. For example, SELECT EXPRTYPE (
'SELECT * FROM #t', 2 ) shows that the second column of #t is 'varchar(10)'.
Tables created with SELECT INTO #table_name have nested scope just like the
ones created with CREATE TABLE #table_name. They are also safe to use
inside a transaction because SELECT INTO #table_name doesn’t cause an auto
-
matic COMMIT as a side effect.
Tip: The INSERT #t SELECT * FROM t command can be used to add more
rows to a table that was created with SELECT INTO #t, without having to list the
column names in either command. For more information about the INSERT
statement, see Chapter 2, “Inserting.”
1.16 Normalized Design
Normalization is the refinement of a database design to eliminate useless redun
-
dancy in order to reduce effort and the chances of error. Redundant data
increases effort by making it necessary to change the same data in multiple
40 Chapter 1: Creating
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
locations. Errors occur and inconsistencies creep into the data when that extra
effort is not taken.
Redundancy can be useful if it increases safety and reliability. For example,
a check digit is redundant because it can be derived from other data, but it is
useful because it catches input errors. Most redundant data, however, gets that
way by accident, and it serves no useful purpose.
Each step in normalization changes one table into two or more tables with
foreign key relationships among them. The process is defined in terms of “nor
-
mal forms,” which are guidelines for achieving higher and higher levels of
refinement. There are six normal forms, numbered one through five, plus an
intermediate level called Boyce-Codd Normal Form, which falls between num
-
bers three and four.
It’s not important to identify each normal form as the normalization pro
-
gresses; it’s just important to remove redundancies and prevent inconsistencies
in the data. The normal forms are presented here because they identify different
problems that are commonly encountered and the changes they require.
Here is a table that violates the first three normal forms; it represents a sim
-
ple paper-based order form with a unique order number plus information about
the client, salesperson, and products ordered:
CREATE TABLE order_form (
order_number INTEGER NOT NULL PRIMARY KEY,
client_name VARCHAR ( 100 ) NOT NULL,
shipping_address VARCHAR ( 1000 ) NOT NULL,
salesperson_name VARCHAR ( 100 ) NOT NULL,
salesperson_phone VARCHAR ( 100 ) NOT NULL,
salesperson_commission NUMERIC ( 6, 3 ) NOT NULL,
product_number_1 INTEGER NOT NULL,
product_description_1 VARCHAR ( 100 ) NOT NULL,
requested_quantity_1 INTEGER NOT NULL,
estimated_shipping_date_1 DATE NOT NULL,
product_number_2 INTEGER NULL,
product_description_2 VARCHAR ( 100 ) NULL,
requested_quantity_2 INTEGER NULL,
estimated_shipping_date_2 DATE NULL,
product_number_3 INTEGER NULL,
product_description_3 VARCHAR ( 100 ) NULL,
requested_quantity_3 INTEGER NULL,
estimated_shipping_date_3 DATE NULL );
1.16.1 First Normal Form
First Normal Form (1NF) eliminates rows with a variable number of columns,
and all repeating columns and groups of columns. Relational databases don’t
allow variable numbers of columns, but it is possible to have different columns
holding the same kind of data. The order_form table has three such groups of
data, each containing product number and description, order quantity, and ship
-
ping date. This violates First Normal Form.
Repeating columns cause several problems: First, it is difficult to increase
the maximum number of entries without changing the schema. Second, it is dif
-
ficult to write application code to process multiple entries because they all have
different column names. Finally, it is difficult to determine how many entries
are actually filled in without defining a separate counter column or storing a
special value; in this example NULL is used to indicate missing data.
Chapter 1: Creating
41
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The solution is to split order_form into order_header and order_detail with
the repeating columns moved down into order_detail. The order_number col
-
umn in order_detail is a foreign key pointing to the order_header table; this
makes order_detail a repeating child of order_header. The product_number col
-
umn is part of the primary key to identify different detail rows that are part of
the same order.
CREATE TABLE order_header (
order_number INTEGER NOT NULL PRIMARY KEY,
client_name VARCHAR ( 100 ) NOT NULL,
shipping_address VARCHAR ( 1000 ) NOT NULL,
salesperson_name VARCHAR ( 100 ) NOT NULL,
salesperson_phone VARCHAR ( 100 ) NOT NULL,
salesperson_commission NUMERIC ( 6, 3 ) NOT NULL );
CREATE TABLE order_detail (
order_number INTEGER NOT NULL REFERENCES order_header,
product_number INTEGER NOT NULL,
product_description VARCHAR ( 100 ) NOT NULL,
requested_quantity INTEGER NOT NULL,
estimated_shipping_date DATE NOT NULL,
PRIMARY KEY ( order_number, product_number ) );
The number of order_detail rows in a single order is now truly variable with no
artificial maximum. Each order_detail row can be processed like any other in an
application program loop, and the number of rows can be easily counted.
1.16.2 Second Normal Form
Second Normal Form (2NF) eliminates any non-key column that only depends
on part of the primary key instead of the whole key. The order_detail table has a
two-column primary key (order_number and product_number), but the prod-
uct_description column only depends on product_number. This violates Second
Normal Form.
One problem here is redundancy: If a product description changes, it must
be changed in every order_detail row containing that value. Another problem is
there’s no place to store a new product number and description until that prod
-
uct is ordered.
The solution is to move product_description up into a new table, prod
-
uct_catalog, which holds information about products separate from orders. The
order_detail table becomes product_order, and the product_number column
becomes a foreign key pointing to the new product_catalog table.
CREATE TABLE order_header (
order_number INTEGER NOT NULL PRIMARY KEY,
client_name VARCHAR ( 100 ) NOT NULL,
shipping_address VARCHAR ( 1000 ) NOT NULL,
salesperson_name VARCHAR ( 100 ) NOT NULL,
salesperson_phone VARCHAR ( 100 ) NOT NULL,
salesperson_commission NUMERIC ( 6, 3 ) NOT NULL );
CREATE TABLE product_catalog (
product_number INTEGER NOT NULL PRIMARY KEY,
product_description VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_order (
order_number INTEGER NOT NULL REFERENCES order_header,
42 Chapter 1: Creating
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
product_number INTEGER NOT NULL REFERENCES product_catalog,
requested_quantity INTEGER NOT NULL,
estimated_shipping_date DATE NOT NULL,
PRIMARY KEY ( order_number, product_number ) );
Redundancy is eliminated because the product_description for each different
product is stored exactly once. Plus, there is now a place to store product infor
-
mation before the first order is received and after the last order has been deleted.
1.16.3 Third Normal Form
Third Normal Form (3NF) eliminates any non-key column that does not depend
on the primary key. In the order table the salesperson_phone column depends on
salesperson_name, which is not part of the primary key. This violates Third
Normal Form.
The problems are the same as with Second Normal Form. First, there is
redundancy: If a salesperson’s phone number changes, it must be changed in
every order row containing that value. Second, there is no place to store infor
-
mation about a new salesperson until that person gets an order.
The solution is to move the salesperson columns up into a new table, sales-
person, with the new salesperson_id column as the primary key. The order table
becomes sales_order, with a salesperson_id column added as a foreign key
pointing to the new salesperson table.
CREATE TABLE salesperson (
salesperson_id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR ( 100 ) NOT NULL,
phone VARCHAR ( 100 ) NOT NULL );
CREATE TABLE sales_order (
order_number INTEGER NOT NULL PRIMARY KEY,
client_name VARCHAR ( 100 ) NOT NULL,
shipping_address VARCHAR ( 1000 ) NOT NULL,
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
salesperson_commission NUMERIC ( 6, 3 ) NOT NULL );
CREATE TABLE product_catalog (
product_number INTEGER NOT NULL PRIMARY KEY,
product_description VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_order (
order_number INTEGER NOT NULL REFERENCES sales_order,
product_number INTEGER NOT NULL REFERENCES product_catalog,
requested_quantity INTEGER NOT NULL,
estimated_shipping_date DATE NOT NULL,
PRIMARY KEY ( order_number, product_number ) );
Redundancy is eliminated because information about each salesperson is stored
exactly once. Also, there is now a place to store salesperson information before
the first order is received and after the last order has been deleted.
Normalization depends on the business rules governing the data. It is not
always possible to normalize a design by simply looking at the schema. For
example, if each salesperson receives a fixed commission for all sales, the sales
-
person_commission column should also be moved to the salesperson table. In
this example, however, salesperson_commission remains in the sales_order
table because the commission can change from order to order.
Chapter 1: Creating
43
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Normalization isn’t always obvious or clear-cut; mistakes are possible, and
it’s important not to get carried away. For example, the client_name column
may also be a candidate for its own table, especially if other client-related col
-
umns are added, such as phone number, billing address, and so on. The
shipping_address column may not be one of those columns, however. It may be
more closely related to the order than the client, especially if one client has
more than one shipping address, or if an order can be shipped to a third party.
1.16.4 Boyce-Codd Normal Form
Boyce-Codd Normal Form (BCNF) eliminates any dependent column that does
not depend on a candidate key. A candidate key is one or more columns that
uniquely identify rows in the table. A table may have more than one candidate
key, only one of which may be chosen as the primary key.
BCNF is slightly stronger than 3NF. BCNF refers to “any dependent col
-
umn” whereas 3NF talks about “any non-key column.” Another difference is
that BCNF refers to candidate keys, not just primary keys.
In the following example, salesperson_skill identifies which skills are pos
-
sessed by which salespersons. Both salesperson_id and salesperson_name are
unique for all salespersons. That means salesperson_name, together with
sales_skill_id, forms a candidate key for salesperson_skill; this is shown as a
UNIQUE constraint separate from the PRIMARY KEY.
CREATE TABLE sales_skill (
sales_skill_id INTEGER NOT NULL PRIMARY KEY,
description LONG VARCHAR );
CREATE TABLE salesperson_skill (
salesperson_id INTEGER NOT NULL,
salesperson_name VARCHAR ( 100 ) NOT NULL,
sales_skill_id INTEGER NULL REFERENCES sales_skill,
PRIMARY KEY ( salesperson_id, sales_skill_id ),
UNIQUE ( salesperson_name, sales_skill_id ) );
The salesperson_skill table is in Third Normal Form because there are no col
-
umns that violate the rule that non-key columns must depend on the primary
key, simply because there are no non-key columns at all; every column in sales
-
person_skill is part of one or the other candidate keys.
However, salesperson_skill is not in Boyce-Codd Normal Form because
salesperson_name depends on salesperson_id, and vice versa, and neither one of
those columns forms a candidate key all by itself. The solution is to move one
of the offending columns, either salesperson_id or salesperson_name, to the
salesperson table.
CREATE TABLE salesperson (
salesperson_id INTEGER NOT NULL PRIMARY KEY,
salesperson_name VARCHAR ( 100 ) NOT NULL UNIQUE );
CREATE TABLE sales_skill (
sales_skill_id INTEGER NOT NULL PRIMARY KEY,
description LONG VARCHAR );
44 Chapter 1: Creating
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CREATE TABLE salesperson_skill (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
sales_skill_id INTEGER NULL REFERENCES sales_skill,
PRIMARY KEY ( salesperson_id, sales_skill_id ) );
In practice it’s hard to tell the difference between Third Normal Form and
Boyce-Codd Normal Form. If you transform a table into Third Normal Form,
the chances are good that it will also be in Boyce-Codd Normal Form because
you removed all the redundancies, regardless of the subtle differences in the
definitions.
In fact, chances are your Third Normal Form database design will also be in
Fourth and Fifth Normal Form. The next two sections discuss the rare situations
where Fourth and Fifth Normal Forms differ from Third.
1.16.5 Fourth Normal Form
Fourth Normal Form (4NF) eliminates multiple independent many-to-many
relationships in the same table. In the following example the salesperson_skill
table represents two many-to-many relationships. First, there is a relationship
where one salesperson may have many sales skills, and conversely, one sales
skill can be shared by multiple salespersons. Second, there is a many-to-many
relationship between salesperson and technical skill. These two relationships are
independent; a salesperson’s technical and sales skills do not depend on one
another, at least as far as this design is concerned.
CREATE TABLE salesperson (
salesperson_id INTEGER NOT NULL PRIMARY KEY,
salesperson_name VARCHAR ( 100 ) NOT NULL );
CREATE TABLE sales_skill (
sales_skill_id INTEGER NOT NULL PRIMARY KEY,
description LONG VARCHAR );
CREATE TABLE technical_skill (
technical_skill_id INTEGER NOT NULL PRIMARY KEY,
description LONG VARCHAR );
CREATE TABLE salesperson_skill (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
sales_skill_id INTEGER NOT NULL REFERENCES sales_skill,
technical_skill_id INTEGER NOT NULL REFERENCES technical_skill,
PRIMARY KEY ( salesperson_id, sales_skill_id, technical_skill_id ) );
It is not clear how the rows in salesperson_skill should be filled when a sales
-
person has different numbers of sales and technical skills. Should special
“blank” values be used for the missing skills, should disjointed rows be filled
with either sales or technical skills but not both, or should a cross product of all
combinations of sales and technical skills be constructed? All these alternatives
have problems with redundancy or complex rules for updating, or both.
The solution is to replace salesperson_skill with two separate tables, as
follows:
CREATE TABLE salesperson_sales_skill (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
sales_skill_id INTEGER NOT NULL REFERENCES sales_skill,
PRIMARY KEY ( salesperson_id, sales_skill_id ) );
Chapter 1: Creating
45
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CREATE TABLE salesperson_technical_skill (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
technical_skill_id INTEGER NOT NULL REFERENCES technical_skill,
PRIMARY KEY ( salesperson_id, technical_skill_id ) );
These tables are in Fourth Normal Form because different many-to-many rela
-
tionships are represented by different tables.
1.16.6 Fifth Normal Form
Fifth Normal Form (5NF) splits one table into three or more if the new tables
have smaller primary keys, less redundancy, and can be joined to reconstruct the
original. This differs from the other normal forms, which divide one table into
two.
Here is an example where salesperson_company_line contains information
about which company’s product lines are handled by which salesperson. The
following special business rule applies: If a salesperson handles a product line,
and a company makes that product line, then that salesperson handles that prod
-
uct line made by that company. This is a three-way relationship where the
individual many-to-many relationships are not independent, so salesper-
son_company_line is in Fourth Normal Form.
CREATE TABLE salesperson (
salesperson_id INTEGER NOT NULL PRIMARY KEY,
salesperson_name VARCHAR ( 100 ) NOT NULL );
CREATE TABLE company (
company_id VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
company_name VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_line (
product_line_id VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
product_line_description VARCHAR ( 100 ) NOT NULL );
CREATE TABLE salesperson_company_line (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
company_id VARCHAR ( 10 ) NOT NULL REFERENCES company,
product_line_id VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
PRIMARY KEY ( salesperson_id, company_id, product_line_id ) );
Not only does salesperson_company_line require redundant values to be stored,
it is possible to violate the special business rule with these rows:
INSERT salesperson_company_line VALUES ( 1, 'Acme', 'cars' );
INSERT salesperson_company_line VALUES ( 2, 'Acme', 'trucks' );
INSERT salesperson_company_line VALUES ( 2, 'Best', 'cars' );
The first row, for salesperson 1, proves that Acme makes cars. The second row
indicates that salesperson 2 also handles Acme, albeit for trucks. The third row
shows salesperson 2 does handle cars, this time for Best. Where is the row that
shows salesperson 2 handles cars for Acme?
The salesperson_company_line table is not in Fifth Normal Form because it
can (and probably should) be split into the following three tables:
CREATE TABLE salesperson_company (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
company_id VARCHAR ( 10 ) NOT NULL REFERENCES company,
PRIMARY KEY ( salesperson_id, company_id ) );
46 Chapter 1: Creating
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CREATE TABLE company_line (
company_id VARCHAR ( 10 ) NOT NULL REFERENCES company,
product_line_id VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
PRIMARY KEY ( company_id, product_line_id ) );
CREATE TABLE salesperson_line (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
product_line_id VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
PRIMARY KEY ( salesperson_id, product_line_id ) );
Here is how the three new tables can be filled, with a SELECT to rebuild the
original table including the row showing that yes, salesperson 2 does in fact
handle cars for Acme:
INSERT salesperson_company VALUES ( 1, 'Acme' );
INSERT salesperson_company VALUES ( 2, 'Acme' );
INSERT salesperson_company VALUES ( 2, 'Best' );
INSERT company_line VALUES ( 'Acme', 'cars' );
INSERT company_line VALUES ( 'Acme', 'trucks' );
INSERT company_line VALUES ( 'Best', 'cars' );
INSERT salesperson_line VALUES ( 1, 'cars' );
INSERT salesperson_line VALUES ( 2, 'cars' );
INSERT salesperson_line VALUES ( 2, 'trucks' );
SELECT DISTINCT
salesperson_company.salesperson_id,
company_line.company_id,
salesperson_line.product_line_id
FROM salesperson_company
JOIN company_line
ON salesperson_company.company_id = company_line.company_id
JOIN salesperson_line
ON salesperson_company.salesperson_id = salesperson_line.salesperson_id
AND company_line.product_line_id = salesperson_line.product_line_id;
Tables requiring a separate effort to reach Fifth Normal Form are extremely
rare. In this example, if the special business rule was not in effect the original
salesperson_company_line table would be the correct choice because it imple
-
ments a three-way many-to-many relationship among salesperson, company,
and product line and it would already be in Fifth Normal Form. In most cases,
once you’ve reached Third Normal Form, you’ve reached Boyce-Codd, Fourth,
and Fifth Normal Forms as well.
1.17 Chapter Summary
This chapter described how to create the five different types of tables in SQL
Anywhere 9: global permanent, remote, proxy, global temporary, and local tem
-
porary. Also discussed were the basic column data types; column properties like
COMPUTE and DEFAULT; and column and table constraints such as CHECK,
PRIMARY KEY, foreign key, and UNIQUE. The 12 rules for relational data
-
bases and the six normal forms of good database design were explained.
The next chapter moves on to the second step in the life cycle of a database:
inserting rows.
Chapter 1: Creating
47
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This page intentionally left blank.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 2
Inserting
2.1 Introduction
The second step in the life cycle of a relational database, after creating the
tables, is to populate those tables with data. SQL Anywhere offers three distinct
techniques: the INSERT, LOAD TABLE, and ISQL INPUT statements.
The INSERT statement comes in two flavors, depending on whether you
want to explicitly provide VALUES for each column, one row per INSERT, or
to copy an entire set of rows into a table from some other source with a single
INSERT, where the “other source” is anything a SELECT can produce.
Those two flavors of INSERT are broken down further in this chapter, into
five separate formats depending on whether values are provided for some or all
of the target table’s columns and whether the AUTO NAME feature is used.
LOAD TABLE and ISQL INPUT provide two different ways to insert data
into a table from an external file.
Each of these techniques offer interesting features discussed in this chapter.
For example, the ON EXISTING UPDATE clause lets you turn an INSERT into
an UPDATE when primary keys collide, LOAD TABLE takes dramatic short-
cuts to offer better performance, and the ISQL INPUT statement can be used to
load fixed-layout records and other file formats from legacy sources.
2.2 INSERT
The INSERT statement comes in five different formats, discussed in the next
five sections:
n
INSERT a single row using a VALUES list for all the columns.
n
INSERT a single row using a column name list and matching VALUES list.
n
INSERT multiple rows using a SELECT to retrieve values for all the
columns.
n
INSERT multiple rows using a column name list and a matching SELECT.
n
INSERT multiple rows using the WITH AUTO NAME clause instead of a
column name list.
49
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét