Difference between revisions of "SH.SALES (table)"

From dbscript Online Help
Jump to: navigation, search
 
(2 intermediate revisions by the same user not shown)
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
−
|-
+
|- 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 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| PROD_ID
 
| PROD_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| FK to the products dimension table
−
| SALES_PROD_BIX
+
|- valign="top"
−
|-
 
 
| CUST_ID
 
| CUST_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| FK to the customers dimension table
−
| SALES_CUST_BIX
+
|- valign="top"
−
|-
 
 
| TIME_ID
 
| TIME_ID
 
| DATE(7)
 
| DATE(7)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| FK to the times dimension table
−
| SALES_TIME_BIX
+
|- valign="top"
−
|-
 
 
| CHANNEL_ID
 
| CHANNEL_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| FK to the channels dimension table
−
| SALES_CHANNEL_BIX
+
|- valign="top"
−
|-
 
 
| PROMO_ID
 
| PROMO_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| promotion identifier, without FK constraint (intentionally) to show outer join optimization
−
| SALES_PROMO_BIX
+
|- valign="top"
−
|-
 
 
| QUANTITY_SOLD
 
| QUANTITY_SOLD
 
| NUMBER(10, 2)
 
| NUMBER(10, 2)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| product quantity sold with the transaction
−
|
+
|- valign="top"
−
|-
 
 
| AMOUNT_SOLD
 
| AMOUNT_SOLD
 
| NUMBER(10, 2)
 
| NUMBER(10, 2)
 
| 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
 +
 +
|- valign="top"
 +
| SALES_CHANNEL_BIX
 
|  
 
|  
 +
| CHANNEL_ID
 +
|- valign="top"
 +
| SALES_CUST_BIX
 +
|
 +
| CUST_ID
 +
|- valign="top"
 +
| SALES_PROD_BIX
 
|  
 
|  
 +
| PROD_ID
 +
|- valign="top"
 +
| SALES_PROMO_BIX
 +
|
 +
| PROMO_ID
 +
|- valign="top"
 +
| SALES_TIME_BIX
 +
|
 +
| TIME_ID
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
  
 +
|- valign="top"
 +
| SYS_C005075
 +
| "PROD_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005076
 +
| "CUST_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005077
 +
| "TIME_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005078
 +
| "CHANNEL_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005079
 +
| "PROMO_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005080
 +
| "QUANTITY_SOLD" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| 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"
Line 76: 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
 
|}
 
|}

Latest revision as of 10:36, 27 August 2011

wikibot[edit]

Table SH.SALES
Description facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys

Columns[edit]

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[edit]

Index Type Columns
SALES_CHANNEL_BIX CHANNEL_ID
SALES_CUST_BIX CUST_ID
SALES_PROD_BIX PROD_ID
SALES_PROMO_BIX PROMO_ID
SALES_TIME_BIX TIME_ID

Check Constraints[edit]

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[edit]

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
Retrieved from "http://dbshelp.devio.at/index.php?title=SH.SALES_(table)&oldid=3095"