Difference between revisions of "SH.SALES (table)"
| Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| − | |- | + | |- valign="top" |
| '''Table | | '''Table | ||
| SH.SALES | | SH.SALES | ||
| Line 8: | Line 8: | ||
| '''Description | | '''Description | ||
| facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys | | facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys | ||
| − | |||
|} | |} | ||
| − | |||
=== Columns === | === Columns === | ||
| Line 20: | Line 18: | ||
| '''Default | | '''Default | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| PROD_ID | | PROD_ID | ||
| NUMBER | | NUMBER | ||
| Line 27: | Line 25: | ||
| | | | ||
| FK to the products dimension table | | FK to the products dimension table | ||
| − | |- | + | |- valign="top" |
| CUST_ID | | CUST_ID | ||
| NUMBER | | NUMBER | ||
| Line 33: | Line 31: | ||
| | | | ||
| FK to the customers dimension table | | FK to the customers dimension table | ||
| − | |- | + | |- valign="top" |
| TIME_ID | | TIME_ID | ||
| DATE(7) | | DATE(7) | ||
| Line 39: | Line 37: | ||
| | | | ||
| FK to the times dimension table | | FK to the times dimension table | ||
| − | |- | + | |- valign="top" |
| CHANNEL_ID | | CHANNEL_ID | ||
| NUMBER | | NUMBER | ||
| Line 45: | Line 43: | ||
| | | | ||
| FK to the channels dimension table | | FK to the channels dimension table | ||
| − | |- | + | |- valign="top" |
| PROMO_ID | | PROMO_ID | ||
| NUMBER | | NUMBER | ||
| Line 51: | Line 49: | ||
| | | | ||
| promotion identifier, without FK constraint (intentionally) to show outer join optimization | | promotion identifier, without FK constraint (intentionally) to show outer join optimization | ||
| − | |- | + | |- valign="top" |
| QUANTITY_SOLD | | QUANTITY_SOLD | ||
| NUMBER(10, 2) | | NUMBER(10, 2) | ||
| Line 57: | Line 55: | ||
| | | | ||
| product quantity sold with the transaction | | product quantity sold with the transaction | ||
| − | |- | + | |- valign="top" |
| AMOUNT_SOLD | | AMOUNT_SOLD | ||
| NUMBER(10, 2) | | NUMBER(10, 2) | ||
| Line 71: | Line 69: | ||
| '''Type | | '''Type | ||
| '''Columns | | '''Columns | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| SALES_CUST_BIX | | SALES_CUST_BIX | ||
| | | | ||
| CUST_ID | | CUST_ID | ||
| − | |- | + | |- valign="top" |
| SALES_CHANNEL_BIX | | SALES_CHANNEL_BIX | ||
| | | | ||
| CHANNEL_ID | | CHANNEL_ID | ||
| − | |- | + | |- valign="top" |
| SALES_TIME_BIX | | SALES_TIME_BIX | ||
| | | | ||
| TIME_ID | | TIME_ID | ||
| − | |- | + | |- valign="top" |
| SALES_PROD_BIX | | SALES_PROD_BIX | ||
| | | | ||
| PROD_ID | | PROD_ID | ||
| − | |- | + | |- valign="top" |
| SALES_PROMO_BIX | | SALES_PROMO_BIX | ||
| | | | ||
| Line 100: | Line 98: | ||
| '''Expression | | '''Expression | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| SYS_C005075 | | SYS_C005075 | ||
| "PROD_ID" IS NOT NULL | | "PROD_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| SYS_C005076 | | SYS_C005076 | ||
| "CUST_ID" IS NOT NULL | | "CUST_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| SYS_C005077 | | SYS_C005077 | ||
| "TIME_ID" IS NOT NULL | | "TIME_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| SYS_C005078 | | SYS_C005078 | ||
| "CHANNEL_ID" IS NOT NULL | | "CHANNEL_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| SYS_C005079 | | SYS_C005079 | ||
| "PROMO_ID" IS NOT NULL | | "PROMO_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| SYS_C005080 | | SYS_C005080 | ||
| "QUANTITY_SOLD" IS NOT NULL | | "QUANTITY_SOLD" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| SYS_C005081 | | SYS_C005081 | ||
| "AMOUNT_SOLD" IS NOT NULL | | "AMOUNT_SOLD" IS NOT NULL | ||
| Line 137: | Line 135: | ||
| '''Column | | '''Column | ||
| '''Referenced Column | | '''Referenced Column | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| [[SH.CHANNELS_(table)|SH.CHANNELS]] | | [[SH.CHANNELS_(table)|SH.CHANNELS]] | ||
| CHANNEL_ID | | CHANNEL_ID | ||
| CHANNEL_ID | | CHANNEL_ID | ||
| − | |- | + | |- valign="top" |
| [[SH.CUSTOMERS_(table)|SH.CUSTOMERS]] | | [[SH.CUSTOMERS_(table)|SH.CUSTOMERS]] | ||
| CUST_ID | | CUST_ID | ||
| CUST_ID | | CUST_ID | ||
| − | |- | + | |- valign="top" |
| [[SH.PRODUCTS_(table)|SH.PRODUCTS]] | | [[SH.PRODUCTS_(table)|SH.PRODUCTS]] | ||
| PROD_ID | | PROD_ID | ||
| PROD_ID | | PROD_ID | ||
| − | |- | + | |- valign="top" |
| [[SH.PROMOTIONS_(table)|SH.PROMOTIONS]] | | [[SH.PROMOTIONS_(table)|SH.PROMOTIONS]] | ||
| PROMO_ID | | PROMO_ID | ||
| PROMO_ID | | PROMO_ID | ||
| − | |- | + | |- valign="top" |
| [[SH.TIMES_(table)|SH.TIMES]] | | [[SH.TIMES_(table)|SH.TIMES]] | ||
| TIME_ID | | TIME_ID | ||
| TIME_ID | | TIME_ID | ||
|} | |} | ||
Revision as of 07:16, 24 June 2010
wikibot
| Table | SH.SALES |
| Description | facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys |
Columns
| Column | Data Type | Nullable | Default | Description |
| PROD_ID | NUMBER | not null | FK to the products dimension table | |
| CUST_ID | NUMBER | not null | FK to the customers dimension table | |
| TIME_ID | DATE(7) | not null | FK to the times dimension table | |
| CHANNEL_ID | NUMBER | not null | FK to the channels dimension table | |
| PROMO_ID | NUMBER | not null | promotion identifier, without FK constraint (intentionally) to show outer join optimization | |
| QUANTITY_SOLD | NUMBER(10, 2) | not null | product quantity sold with the transaction | |
| AMOUNT_SOLD | NUMBER(10, 2) | not null | invoiced amount to the customer |
Indexes
| Index | Type | Columns |
| SALES_CUST_BIX | CUST_ID | |
| SALES_CHANNEL_BIX | CHANNEL_ID | |
| SALES_TIME_BIX | TIME_ID | |
| SALES_PROD_BIX | PROD_ID | |
| SALES_PROMO_BIX | PROMO_ID |
Check Constraints
| Check Constraint | Expression | Description |
| SYS_C005075 | "PROD_ID" IS NOT NULL | |
| SYS_C005076 | "CUST_ID" IS NOT NULL | |
| SYS_C005077 | "TIME_ID" IS NOT NULL | |
| SYS_C005078 | "CHANNEL_ID" IS NOT NULL | |
| SYS_C005079 | "PROMO_ID" IS NOT NULL | |
| SYS_C005080 | "QUANTITY_SOLD" IS NOT NULL | |
| SYS_C005081 | "AMOUNT_SOLD" IS NOT NULL |
Foreign Keys
| Relation | Column | Referenced Column |
| SH.CHANNELS | CHANNEL_ID | CHANNEL_ID |
| SH.CUSTOMERS | CUST_ID | CUST_ID |
| SH.PRODUCTS | PROD_ID | PROD_ID |
| SH.PROMOTIONS | PROMO_ID | PROMO_ID |
| SH.TIMES | TIME_ID | TIME_ID |
