Creating the Sample Tables

The following scripts can be used to create the five sample tables. Please note that these scripts have been designed for maximum compatibility among as many different DBMSs as possible. Because of this, the scripts are neither optimized nor complete. Each script is followed by notes that describe some changes or additions you may need (or want) to make.

The VENDORS Table

CREATE TABLE Vendors
(
    vend_id        CHAR(10)    NOT NULL,
    vend_name    CHAR(50)    NOT NULL,
    vend_address    CHAR(50)    ,
    vend_city    CHAR(50)    ,
    vend_state    CHAR        ,
    vend_zip    CHAR(10)
);

• All tables should have primary keys defined. This table should use vend_id as its primary key.

• If you are using Informix, explicitly state NULL for the vend_address, vend_city, vend_state, and vend_zip columns.

• DB2 users will need to specify where the table is to be created.

The PRODUCTS Table

CREATE TABLE Products
(
    prod_id        CHAR(10)    NOT NULL,
    vend_id        CHAR(10)    NOT NULL,
    prod_name    CHAR(255)    NOT NULL,
    prod_price    DECIMAL(8,2)    NOT NULL,
    prod_desc    VARCHAR(1000)
);

• All tables should have primary keys defined. This table should use prod_id as its primary key.

• To enforce referential integrity, a foreign key should be defined on vend_id relating it to vend_id in VENDORS.

• If you are using Microsoft SQL Sever, Sybase, or Informix, you might want to use a datatype of MONEY instead of DECIMAL(8,2) for the prod_price column. DECIMAL(8,2) was used here because some DBMSs (most notably Oracle), do not support the MONEY datatype.

• If you are using Informix, explicitly state NULL for the prod_desc column.

• DB2 users will need to specify where the table is to be created.

The CUSTOMERS Table

CREATE TABLE Customers
(
    cust_id        CHAR(10)    NOT NULL,
    cust_name    CHAR(50)    NOT NULL,
    cust_address    CHAR(50)    ,
    cust_city    CHAR(50)    ,
    cust_state    CHAR        ,
    cust_zip    CHAR(10)    ,
    cust_contact    CHAR(50)    ,
    cust_email    CHAR(255)
);

• All tables should have primary keys defined. This table should use cust_id as its primary key.

• If you are using Informix, explicitly state NULL for the cust_address, cust_city, cust_state, cust_zip, cust_ contact, and cust_email columns.

• DB2 users will need to specify where the table is to be created.

The ORDERS Table

CREATE TABLE Orders
(
    order_num    INTEGER        NOT NULL,
    order_date    DATETIME    NOT NULL,
    cust_id        CHAR(10)    NOT NULL
);

• All tables should have primary keys defined. This table should use order_num as its primary key.

• To enforce referential integrity, a foreign key should be defined on cust_id relating it to cust_id in CUSTOMERS.

• If you are using Oracle, change the order_date datatype from DATETIME to DATE.

• DB2 users will need to specify where the table is to be created.

The ORDERITEMS Table

CREATE TABLE OrderItems
(
    order_num    INTEGER        NOT NULL,

    order_item    INTEGER        NOT NULL,
    prod_id        CHAR(10)    NOT NULL,
    quantity    INTEGER        NOT NULL,
    item_price    DECIMAL(8,2)    NOT NULL
);

• All tables should have primary keys defined. This table should use order_num and order_item as its primary keys.

• To enforce referential integrity, a foreign key should be defined on prod_id relating it to prod_id in PRODUCTS.

• If you are using Microsoft SQL Sever, Sybase, or Informix, you might want to use a datatype of MONEY, instead of DECIMAL(8,2), for the item_price column. DECIMAL(8,2) was used here because some DBMSs (most notably Oracle), do not support the MONEY datatype.

• DB2 users will need to specify where the table is to be created.