The sales_demo database includes the following tables:
The tables are listed alphabetically, not in the order in which they are created. The customer, geography, product, and time tables are the dimensions for the sales fact table.
The sales_demo database is not an ANSI-compliant database.
The following sections describe the column names, data types, and column descriptions for each table. A SERIAL field serves as the primary key for the district_code column of the geography table. The primary and foreign key relationships that exist between the fact (sales) table and its dimension tables are not defined, however, because data-loading performance improves dramatically when the database server does not enforce constraint checking.
The customer table contains information about sales customers. Table 25 shows the columns of the customer table.
| Name | Type | Description |
|---|---|---|
| customer_code | INTEGER | Customer code |
| customer_name | CHAR(31) | Customer name |
| company_name | CHAR(20) | Company name |
The geography table contains information about the sales district and region. Table 26 shows the columns of the geography table.
| Name | Type | Description |
|---|---|---|
| district_code | SERIAL | District code |
| district_name | CHAR(15) | District name |
| state_code | CHAR(2) | State code |
| state_name | CHAR(18) | State name |
| region | SMALLINT | Region name |
The product table contains information about the products sold through the data warehouse. Table 27 shows the columns of the product table.
| Name | Type | Description |
|---|---|---|
| product_code | INTEGER | Product code |
| product_name | CHAR(31) | Product name |
| vendor_code | CHAR(3) | Vendor code |
| vendor_name | CHAR(15) | Vendor name |
| product_line_code | SMALLINT | Product line code |
| product_line_name | CHAR(15) | Name of product line |
The sales fact table contains information about product sales and has a pointer to each dimension table. For example, the customer_code column references the customer table, the district_code column references the geography table, and so on. The sales table also contains the measures for the units sold, revenue, cost, and net profit. Table 28 shows the columns of the sales table.
| Name | Type | Description |
|---|---|---|
| customer_code | INTEGER | Customer code (references customer) |
| district_code | SMALLINT | District code (references geography) |
| time_code | INTEGER | Time code (references time) |
| product_code | INTEGER | Product code (references product) |
| units_sold | SMALLINT | Number of units sold |
| revenue | MONEY(8,2) | Amount of sales revenue |
| cost | MONEY(8,2) | Cost of sale |
| net_profit | MONEY(8,2) | Net profit of sale |
The time table contains time information about the sale. Table 29 shows the columns of the time table.
| Name | Type | Description |
|---|---|---|
| time_code | INTEGER | Time code |
| order_date | DATE | Order date |
| month_code | SMALLINT | Month code |
| month_name | CHAR(10) | Name of month |
| quarter_code | SMALLINT | Quarter code |
| quarter_name | CHAR(10) | Name of quarter |
| year | INTEGER | Year |