The superstores_demo database includes the following tables. Although many tables have the same name as stores_demo tables, they are different. The tables are listed alphabetically, not in the order in which they are created.
This section lists the names, data types, and descriptions of the columns for each table in the superstores_demo database. The unique identifying value for each table (primary key) is shaded. Columns that represent extended data types are discussed in User-Defined Routines and Extended Data Types. Primary-foreign key relationships between the tables are outlined in Referential Relationships.
The call codes associated with customer calls are stored in the call_type table. Table 30 shows the columns of the call_type table.
| Name | Type | Description |
|---|---|---|
| call_code | CHAR(1) | Call code |
| codel_descr | CHAR (30) | Description of call code |
The catalog table describes each item in stock. Retail stores use this table when placing orders with the distributor. Table 31 shows the columns of the catalog table.
| Name | Type | Description |
|---|---|---|
| catalog_num | SERIAL(1001) | System-generated catalog number |
| stock_num | SMALLINT | Distributor stock number (foreign key to stock table) |
| manu_code | CHAR(3) | Manufacturer code (foreign key to stock table) |
| unit | CHAR(4) | Unit by which item is ordered (foreign key to stock table) |
| advert | ROW (picture BLOB, caption LVARCHAR) | Picture of item and caption |
| advert_descr | CLOB | Tag line underneath picture |
All customer calls for information on orders, shipments, or complaints are logged. The cust_calls table contains information about these types of customer calls. Table 32 shows the columns of the cust_calls table.
| Name | Type | Description |
|---|---|---|
| customer_num | INTEGER | Customer number (foreign key to customer table) |
| call_dtime | DATETIME YEAR TO MINUTE | Date and time call received |
| user_id | CHAR(18) | Name of person logging call (default is user login name) |
| call_code | CHAR(1) | Type of call (foreign key to call_type table) |
| call_descr | CHAR(240) | Description of call |
| res_dtime | DATETIME YEAR TO MINUTE | Date and time call resolved |
| res_descr | CHAR(240) | Description of how call was resolved |
In this hierarchy, retail_customer and whlsale_customer are subtables that are created under the customer supertable, as Figure 16 shows.
For information about table hierarchies, see the IBM Informix: Database Design and Implementation Guide.
The customer table contains information about the retail stores that place orders from the distributor. Table 33 shows the columns of the customer table.
| Name | Type | Description |
|---|---|---|
| customer_num | SERIAL | Unique customer identifier |
| customer_type | CHAR(1) | Code to indicate type of customer:
R = retail W = wholesale |
| customer_name | name_t | Name of customer |
| customer_loc | INTEGER | Location of customer (foreign key to location table) |
| contact_dates | LIST(DATETIME YEAR TO DAY NOT NULL) | Dates of contact with customer |
| cust_discount | percent | Customer discount |
| credit_status | CHAR(1) | Customer credit status:
D = deadbeat L = lost N = new P = preferred R = regular |
The retail_customer table contains general information about retail customers. Table 34 shows the columns of the retail_customer table.
| Name | Type | Description |
|---|---|---|
| customer_num | SERIAL | Unique customer identifier |
| customer_type | CHAR(1) | Code to indicate type of customer:
R = retail W = wholesale |
| customer_name | name_t | Name of customer |
| customer_loc | INTEGER | Location of customer |
| contact_dates | LIST(DATETIME YEAR TO DAY NOT NULL) | Dates of contact with customer |
| cust_discount | percent | Customer discount |
| credit_status | CHAR(1) | Customer credit status:
D = deadbeat L = lost N = new P = preferred R = regular |
| credit_num | CHAR(19) | Credit card number |
| expiration | DATE | Expiration data of credit card |
The whlsale_customer table contains general information about wholesale customers. Table 35 shows the columns of the whlsale_customer table.
| Name | Type | Description |
|---|---|---|
| customer_num | SERIAL | Unique customer identifier |
| customer_type | CHAR(1) | Code to indicate type of customer:
R = retail W = wholesale |
| customer_name | name_t | Name of customer |
| customer_loc | INTEGER | Location of customer |
| contact_dates | LIST(DATETIME YEAR TO DAY NOT NULL) | Dates of contact with customer |
| cust_discount | percent | Customer discount |
| credit_status | CHAR(1) | Customer credit status:
D = deadbeat L = lost N = new P = preferred R = regular |
| resale_license | CHAR(15) | Resale license number |
| terms_net | SMALLINT | Net term in days |
An order can include one or more items. One row exists in the items table for each item in an order. Table 36 shows the columns of the items table.
| Name | Type | Description |
|---|---|---|
| item_num | SMALLINT | Sequentially assigned item number for an order |
| order_num | INT8 | Order number (foreign key to orders table) |
| stock_num | SMALLINT | Stock number for item (foreign key to stock table) |
| manu_code | CHAR(3) | Manufacturer code for item ordered (foreign key to stock table) |
| unit | CHAR(4) | Unit by which item is ordered (foreign key to stock table) |
| quantity | SMALLINT | Quantity ordered (value must be > 1) |
| item_subtotal | MONEY(8,2) | Quantity ordered * unit price = total price of item |
In this hierarchy, location_non_us and location_us are subtables that are created under the location supertable, as shown in the diagram in Table Hierarchies. For information about table hierarchies, see the IBM Informix: Database Design and Implementation Guide.
The location table contains general information about the locations (addresses) that the database tracks. Table 37 shows the columns of the location table.
| Name | Type | Description |
|---|---|---|
| location_id | SERIAL | Unique identifier for location |
| loc_type | CHAR(2) | Code to indicate type of location |
| company | VARCHAR(20) | Name of company |
| street_addr | LIST(VARCHAR(25) NOT NULL) | Street address |
| city | VARCHAR(25) | City for address |
| country | VARCHAR(25) | Country for address |
The location_non_us table contains specific address information for locations (addresses) that are outside the United States. Table 38 shows the columns of the location_non_us table.
The location_us table contains specific address information for locations (addresses) that are in the United States. Table 39 shows the columns of the location_us table.
Information about the manufacturers whose sporting goods are handled by the distributor is stored in the manufact table. Table 40 shows the columns of the manufact table.
| Name | Type | Description |
|---|---|---|
| manu_code | CHAR(3) | Manufacturer code |
| manu_name | VARCHAR(15) | Name of manufacturer |
| lead_time | INTERVAL DAY(3) TO DAY | Lead time for shipment of orders |
| manu_loc | INTEGER | Manufacturer location (foreign key to location table) |
| manu_account | CHAR(32) | Distributor account number with manufacturer |
| account_status | CHAR(1) | Status of account with manufacturer |
| terms_net | SMALLINT | Distributor terms with manufacturer (in days) |
| discount | percent | Distributor volume discount with manufacturer |
The orders table contains information about orders placed by the customers of the distributor. Table 41 shows the columns of the orders table.
| Name | Type | Description |
|---|---|---|
| order_num | SERIAL8(1001) | System-generated order number |
| order_date | DATE | Date order entered |
| customer_num | INTEGER | Customer number (foreign key to customer table) |
| shipping | ship_t | Special shipping instructions |
| backlog | BOOLEAN | Indicates order cannot be filled because the item is back ordered |
| po_num | CHAR(10) | Customer purchase order number |
| paid_date | DATE | Date order paid |
The region table contains information about the sales regions for the distributor. Table 42 shows the columns of the region table.
| Name | Type | Description |
|---|---|---|
| region_num | SERIAL | System-generated region number |
| region_name | VARCHAR(20) UNIQUE | Name of sales region |
| region_loc | INTEGER | Location of region office (foreign key to location table) |
The sales_rep table contains information about the sales representatives for the distributor. Table 43 shows the columns of the sales_rep table.
| Name | Type | Description |
|---|---|---|
| rep_num | SERIAL(101) | System-generated sales rep number |
| name | name_t | Name of sales rep |
| region_num | INTEGER | Region in which sales rep works (foreign key to the region table) |
| home_office | BOOLEAN | Home office location of sales rep |
| sales | SET(ROW (month DATETIME YEAR TO MONTH, amount MONEY) NOT NULL) | Amount of monthly sales for rep |
| commission | percent | Commission rate for sales rep |
The state table contains the names and postal abbreviations, as well as sales tax information, for the 50 states of the United States. Table 44 shows the columns of the state table.
| Name | Type | Description |
|---|---|---|
| code | CHAR(2) | State code |
| sname | CHAR(15) | State name |
| sales_tax | percent | State sales tax |
The stock table is a catalog of the items sold by the distributor. Table 45 shows the columns of the stock table.
| Name | Type | Description |
|---|---|---|
| stock_num | SMALLINT | Stock number that identifies type of item |
| manu_code | CHAR(3) | Manufacturer code (foreign key to manufact) |
| unit | CHAR(4) | Unit by which item is ordered |
| description | VARCHAR(15) | Description of item |
| unit_price | MONEY(6,2) | Unit price |
| min_reord_qty | SMALLINT | Minimum reorder quantity |
| min_inv_qty | SMALLINT | Quantity of stock below which item should be reordered |
| manu_item_num | CHAR(20) | Manufacturer item number |
| unit_cost | MONEY(6,2) | Distributor cost per unit of item from manufacturer |
| status | CHAR(1) | Status of item:
A = active D = discontinued N = no order |
| bin_num | INTEGER | Bin number |
| qty_on_hand | SMALLINT | Quantity in stock |
| bigger_unit | CHAR(4) | Stock unit for next larger unit (for same stock_num and manu_code) |
| per_bigger_unit | SMALLINT | How many of this item in bigger_unit |
The stock_discount table contains information about stock discounts. (There is no primary key). Table 46 shows the columns of the stock_discount table.
| Name | Type | Description |
|---|---|---|
| discount_id | SERIAL | System-generated discount identifier |
| stock_num | SMALLINT | Distributor stock number (part of foreign key to stock table) |
| manu_code | CHAR(3) | Manufacturer code (part of foreign key to stock table) |
| unit | CHAR(4) | Unit by which item is ordered (each, pair, case, and so on) (foreign key to units table; part of foreign key to stock table) |
| unit_discount | percent | Unit discount during sale period |
| start_date | DATE | Discount start date |
| end_date | DATE | Discount end date |
The units table contains information about the units in which the inventory items can be ordered. Each item in the stock table is available in one or more types of container. Table 47 shows the columns of the units table.
| Name | Type | Description |
|---|---|---|
| unit_name | CHAR(4) | Units by which an item is ordered (each, pair, case, box) |
| unit_descr | VARCHAR(15) | Description of units |