Difference between revisions of "SH.SALES (table)"
| Line 5: | Line 5: | ||
| '''Table | | '''Table | ||
| SH.SALES | | SH.SALES | ||
| + | |- valign="top" | ||
| + | | '''Description | ||
| + | | facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys | ||
|- | |- | ||
|} | |} | ||
| + | === Columns === | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
| Line 15: | Line 19: | ||
| '''Nullable | | '''Nullable | ||
| '''Default | | '''Default | ||
| − | | ''' | + | | '''Description |
| − | |||
|- | |- | ||
| Line 23: | Line 26: | ||
| not null | | not null | ||
| | | | ||
| − | | | + | | FK to the products dimension table |
| − | |||
|- | |- | ||
| CUST_ID | | CUST_ID | ||
| Line 30: | Line 32: | ||
| not null | | not null | ||
| | | | ||
| − | | | + | | FK to the customers dimension table |
| − | |||
|- | |- | ||
| TIME_ID | | TIME_ID | ||
| Line 37: | Line 38: | ||
| not null | | not null | ||
| | | | ||
| − | | | + | | FK to the times dimension table |
| − | |||
|- | |- | ||
| CHANNEL_ID | | CHANNEL_ID | ||
| Line 44: | Line 44: | ||
| not null | | not null | ||
| | | | ||
| − | | | + | | FK to the channels dimension table |
| − | |||
|- | |- | ||
| PROMO_ID | | PROMO_ID | ||
| Line 51: | Line 50: | ||
| not null | | not null | ||
| | | | ||
| − | | | + | | promotion identifier, without FK constraint (intentionally) to show outer join optimization |
| − | |||
|- | |- | ||
| QUANTITY_SOLD | | QUANTITY_SOLD | ||
| Line 58: | Line 56: | ||
| not null | | not null | ||
| | | | ||
| − | | | + | | product quantity sold with the transaction |
| − | |||
|- | |- | ||
| AMOUNT_SOLD | | AMOUNT_SOLD | ||
| Line 65: | Line 62: | ||
| not null | | not null | ||
| | | | ||
| + | | invoiced amount to the customer | ||
| + | |} | ||
| + | |||
| + | === Indexes === | ||
| + | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| + | |- style="background:silver" | ||
| + | | '''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 === | ||
| + | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| + | |- style="background:silver" | ||
| + | | '''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 === | |
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
Revision as of 09:29, 11 February 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 |
