SQL Basics - Create Table
International Spectrum - Jan/Feb '98
Nathan Rector
Natec Systems
Nater@northcoast.com
http://www.northcoast.com/~nater/
Introduction
More and more, the Multi-value markets are finding ways into the "main stream" markets. As users slowly come to understand the capacities of their computer and enterprise systems, they become less and less tolerant of systems that don't communicate with each other. Users are requiring their payroll systems to communicate with the sales systems, and the sales systems to communicate with the manufacturing systems.
ODBC has become the defacto connection medium between unlike systems. Since the core of ODBC is SQL, it is becoming a requirement for the multi-value professionals to understand the basics of how SQL works.
The last 2 articles I wrote have dealt with D3/NT and SQL. In Sept/Oct, I covered setting up the D3/NT ODBC server. In Nov/Dec, I covered D3's SQL-CREATE-TABLE which does most of the work of mapping an existing D3 file into an SQL table.
In next few articles I'll cover the basics of SQL and its syntax.
SQL basics:
ODBC/SQL uses a flat file structure called 'tables'. They are called tables because of how they are setup. Each column in the table is the same as an attribute in a multi-value file. Each row in the table would be the actual record in a multi-value file. Tables also use fix length fields.
Multi-value file:
101
001 Acme Corp
002 500 J St
003 707-445-1234
102
001 Smith Inc
002 Po Box 5
003 702-584-9865
ODBC/SQL File:
001 002 003
101 Acme Crop 500 J St 707-445-1234
102 Smith Inc Po Box 5 702-584-9865
One of the major advantages Multi-value systems have is the ability to save information in a multi-value format. ODBC/SQL does not have this ability. Similar results can be achieved by creating tables that are associated with other tables. These are called 'Nested tables'.
Nested Tables create an additional complexity to your file structure. They can be very simple, like the example below, or they can become very complex depending how your Multi-value data needs to be associated with the main information. Let's look at the example as above, but make the phone number field into a multi-value.
Multi-Value File:
101
001 Acme Corp
002 500 j sT
003 707-445-1234]707-445-6789
ODBC/SQL File:
Cust
001 002
101 Acme Corp 500 J St
Cust_Phone
001
101 707-445-1234
101 707-445-6789
As you can see, ODBC/SQL creates two separate files in order to create multi-dimensional associations. These file are associated together to create the same functionality as a single multi-valued file. At another time I'll explain how to combine the information so it looks and acts the same as it does in a Multi-value file.
Data types:
When working with SQL tables, you have to specify the type of data you are using each field. For example, if the data is a date, then the field must be specified as a date field. No other information is allowed to exist in that field other than date information. There are several common data types available:
CHARACTER (length) - This is string information. The length is specifies how long the field will be in the table. If the data is not the same length as the length specified, then it will pad the data with spaces to meet the length.
CHARACTER fields can store both numbers and letters. However, any program working with the information will not see a number in this field as a number, but as a string of data.
INTEGER (length) - a whole number. The length specifies how large the number can be.
NUMERIC (integer,decimal) - this is a numbers from 0 to 9 with a decimal. A number is only allowed a max of 12 characters. If the integer and decimal length is not specified, then the largest number this type can handle is 999,999.999999.
For example, NUMERIC (5,2) would a max number of 999.99
REAL - this is a floating point number. The precision depends on the hardware. For example, the precision on a 64-bit machine is larger than on a 32-bit machine.
FLOAT (precision) - if your data will be parted to other machines with a different floating pointing precision, then use this data type.
DATE - this is a date data type
TIME - this is a time data type
VARCHAR (length) - unknown type of data. This data works the same as the CHARACTER data type, but will not pad the data with spaces if the length of the data does not equal the length of the field.
CREATE TABLE command:
Now that you have seen the different data types and have a little bit of an understanding of how ODBC/SQL tables are put together, let's look at the CREATE TABLE command, and create the example tables above.
The first table that I show displays a simple file with no multi-value data:
CREATE TABLE CUST (
ID INTEGER,
NAME CHARACTER(20),
ADDRESS CHARACTER(30),
PHONE CHARACTER(13)
);
This next table is an example of a nested table that holds multi-value data:
CREATE TABLE CUST (
ID INTEGER,
NAME CHARACTER(20),
ADDRESS CHARACTER(30),
PRIMARY KEY (ID)
);
CREATE TABLE CUST_PHONE (
PHONE CHARACTER(13) REFERENCES CUST,
ID INTEGER,
PRIMARY KEY (ID)
) ;
As you can see, to create the multi-value effect, two tables are created. The first table is the primary file. The operator PRIMARY KEY is used to create an index for all the items in this table. This is done to help with accessing the information in a timely manner.
This article only scratches the surface of the complexity of creating files in ODBC/SQL. The next issue I'll cover more complex examples of the file in ODBC/SQL