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

From dbscript Online Help
Jump to: navigation, search
 
(One intermediate revision 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.CUSTOMERS
 
| SH.CUSTOMERS
Line 8: Line 8:
 
| '''Description
 
| '''Description
 
| dimension table
 
| dimension table
−
|-
 
 
|}
 
|}
−
 
  
 
=== Columns ===
 
=== Columns ===
Line 20: Line 18:
 
| '''Default
 
| '''Default
 
| '''Description
 
| '''Description
−
+
 
−
|-
+
|- valign="top"
 
| CUST_ID
 
| CUST_ID
 
| NUMBER
 
| NUMBER
Line 27: Line 25:
 
|  
 
|  
 
| primary key
 
| primary key
−
|-
+
|- valign="top"
 
| CUST_FIRST_NAME
 
| CUST_FIRST_NAME
 
| VARCHAR2(20)
 
| VARCHAR2(20)
Line 33: Line 31:
 
|  
 
|  
 
| first name of the customer
 
| first name of the customer
−
|-
+
|- valign="top"
 
| CUST_LAST_NAME
 
| CUST_LAST_NAME
 
| VARCHAR2(40)
 
| VARCHAR2(40)
Line 39: Line 37:
 
|  
 
|  
 
| last name of the customer
 
| last name of the customer
−
|-
+
|- valign="top"
 
| CUST_GENDER
 
| CUST_GENDER
 
| CHAR(1)
 
| CHAR(1)
Line 45: Line 43:
 
|  
 
|  
 
| gender; low cardinality attribute
 
| gender; low cardinality attribute
−
|-
+
|- valign="top"
 
| CUST_YEAR_OF_BIRTH
 
| CUST_YEAR_OF_BIRTH
 
| NUMBER(4, 0)
 
| NUMBER(4, 0)
Line 51: Line 49:
 
|  
 
|  
 
| customer year of birth
 
| customer year of birth
−
|-
+
|- valign="top"
 
| CUST_MARITAL_STATUS
 
| CUST_MARITAL_STATUS
 
| VARCHAR2(20)
 
| VARCHAR2(20)
Line 57: Line 55:
 
|  
 
|  
 
| customer marital status; low cardinality attribute
 
| customer marital status; low cardinality attribute
−
|-
+
|- valign="top"
 
| CUST_STREET_ADDRESS
 
| CUST_STREET_ADDRESS
 
| VARCHAR2(40)
 
| VARCHAR2(40)
Line 63: Line 61:
 
|  
 
|  
 
| customer street address
 
| customer street address
−
|-
+
|- valign="top"
 
| CUST_POSTAL_CODE
 
| CUST_POSTAL_CODE
 
| VARCHAR2(10)
 
| VARCHAR2(10)
Line 69: Line 67:
 
|  
 
|  
 
| postal code of the customer
 
| postal code of the customer
−
|-
+
|- valign="top"
 
| CUST_CITY
 
| CUST_CITY
 
| VARCHAR2(30)
 
| VARCHAR2(30)
Line 75: Line 73:
 
|  
 
|  
 
| city where the customer lives
 
| city where the customer lives
−
|-
+
|- valign="top"
 
| CUST_CITY_ID
 
| CUST_CITY_ID
 
| NUMBER
 
| NUMBER
Line 81: Line 79:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| CUST_STATE_PROVINCE
 
| CUST_STATE_PROVINCE
 
| VARCHAR2(40)
 
| VARCHAR2(40)
Line 87: Line 85:
 
|  
 
|  
 
| customer geography: state or province
 
| customer geography: state or province
−
|-
+
|- valign="top"
 
| CUST_STATE_PROVINCE_ID
 
| CUST_STATE_PROVINCE_ID
 
| NUMBER
 
| NUMBER
Line 93: Line 91:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| COUNTRY_ID
 
| COUNTRY_ID
 
| NUMBER
 
| NUMBER
Line 99: Line 97:
 
|  
 
|  
 
| foreign key to the countries table (snowflake)
 
| foreign key to the countries table (snowflake)
−
|-
+
|- valign="top"
 
| CUST_MAIN_PHONE_NUMBER
 
| CUST_MAIN_PHONE_NUMBER
 
| VARCHAR2(25)
 
| VARCHAR2(25)
Line 105: Line 103:
 
|  
 
|  
 
| customer main phone number
 
| customer main phone number
−
|-
+
|- valign="top"
 
| CUST_INCOME_LEVEL
 
| CUST_INCOME_LEVEL
 
| VARCHAR2(30)
 
| VARCHAR2(30)
Line 111: Line 109:
 
|  
 
|  
 
| customer income level
 
| customer income level
−
|-
+
|- valign="top"
 
| CUST_CREDIT_LIMIT
 
| CUST_CREDIT_LIMIT
 
| NUMBER
 
| NUMBER
Line 117: Line 115:
 
|  
 
|  
 
| customer credit limit
 
| customer credit limit
−
|-
+
|- valign="top"
 
| CUST_EMAIL
 
| CUST_EMAIL
 
| VARCHAR2(30)
 
| VARCHAR2(30)
Line 123: Line 121:
 
|  
 
|  
 
| customer email id
 
| customer email id
−
|-
+
|- valign="top"
 
| CUST_TOTAL
 
| CUST_TOTAL
 
| VARCHAR2(14)
 
| VARCHAR2(14)
Line 129: Line 127:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| CUST_TOTAL_ID
 
| CUST_TOTAL_ID
 
| NUMBER
 
| NUMBER
Line 135: Line 133:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| CUST_SRC_ID
 
| CUST_SRC_ID
 
| NUMBER
 
| NUMBER
Line 141: Line 139:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| CUST_EFF_FROM
 
| CUST_EFF_FROM
 
| DATE(7)
 
| DATE(7)
Line 147: Line 145:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| CUST_EFF_TO
 
| CUST_EFF_TO
 
| DATE(7)
 
| DATE(7)
Line 153: Line 151:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| CUST_VALID
 
| CUST_VALID
 
| VARCHAR2(1)
 
| VARCHAR2(1)
Line 166: Line 164:
 
| '''Primary Key
 
| '''Primary Key
 
| '''Columns
 
| '''Columns
−
|-
+
|- valign="top"
 
| CUSTOMERS_PK
 
| CUSTOMERS_PK
 
| CUST_ID
 
| CUST_ID
Line 177: Line 175:
 
| '''Type
 
| '''Type
 
| '''Columns
 
| '''Columns
−
+
 
−
|-
+
|- valign="top"
 +
| CUSTOMERS_GENDER_BIX
 +
|
 +
| CUST_GENDER
 +
|- valign="top"
 
| CUSTOMERS_MARITAL_BIX
 
| CUSTOMERS_MARITAL_BIX
 
|  
 
|  
 
| CUST_MARITAL_STATUS
 
| CUST_MARITAL_STATUS
−
|-
+
|- valign="top"
 
| CUSTOMERS_YOB_BIX
 
| CUSTOMERS_YOB_BIX
 
|  
 
|  
 
| CUST_YEAR_OF_BIRTH
 
| CUST_YEAR_OF_BIRTH
−
|-
 
−
| CUSTOMERS_GENDER_BIX
 
−
|
 
−
| CUST_GENDER
 
 
|}
 
|}
  
Line 198: Line 196:
 
| '''Expression
 
| '''Expression
 
| '''Description
 
| '''Description
−
+
 
−
|-
+
|- valign="top"
 
| SYS_C005164
 
| SYS_C005164
 
| "CUST_ID" IS NOT NULL
 
| "CUST_ID" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005165
 
| SYS_C005165
 
| "CUST_FIRST_NAME" IS NOT NULL
 
| "CUST_FIRST_NAME" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005166
 
| SYS_C005166
 
| "CUST_LAST_NAME" IS NOT NULL
 
| "CUST_LAST_NAME" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005167
 
| SYS_C005167
 
| "CUST_GENDER" IS NOT NULL
 
| "CUST_GENDER" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005168
 
| SYS_C005168
 
| "CUST_YEAR_OF_BIRTH" IS NOT NULL
 
| "CUST_YEAR_OF_BIRTH" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005169
 
| SYS_C005169
 
| "CUST_STREET_ADDRESS" IS NOT NULL
 
| "CUST_STREET_ADDRESS" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005170
 
| SYS_C005170
 
| "CUST_POSTAL_CODE" IS NOT NULL
 
| "CUST_POSTAL_CODE" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005171
 
| SYS_C005171
 
| "CUST_CITY" IS NOT NULL
 
| "CUST_CITY" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005172
 
| SYS_C005172
 
| "CUST_CITY_ID" IS NOT NULL
 
| "CUST_CITY_ID" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005173
 
| SYS_C005173
 
| "CUST_STATE_PROVINCE" IS NOT NULL
 
| "CUST_STATE_PROVINCE" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005174
 
| SYS_C005174
 
| "CUST_STATE_PROVINCE_ID" IS NOT NULL
 
| "CUST_STATE_PROVINCE_ID" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005175
 
| SYS_C005175
 
| "COUNTRY_ID" IS NOT NULL
 
| "COUNTRY_ID" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005176
 
| SYS_C005176
 
| "CUST_MAIN_PHONE_NUMBER" IS NOT NULL
 
| "CUST_MAIN_PHONE_NUMBER" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005177
 
| SYS_C005177
 
| "CUST_TOTAL" IS NOT NULL
 
| "CUST_TOTAL" IS NOT NULL
 
|  
 
|  
−
|-
+
|- valign="top"
 
| SYS_C005178
 
| SYS_C005178
 
| "CUST_TOTAL_ID" IS NOT NULL
 
| "CUST_TOTAL_ID" IS NOT NULL
Line 267: Line 265:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[SH.COUNTRIES_(table)|SH.COUNTRIES]]
 
| [[SH.COUNTRIES_(table)|SH.COUNTRIES]]
 
| COUNTRY_ID
 
| COUNTRY_ID
Line 280: Line 278:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[SH.SALES_(table)|SH.SALES]]
 
| [[SH.SALES_(table)|SH.SALES]]
 
| CUST_ID
 
| CUST_ID
 
| CUST_ID
 
| CUST_ID
 
|}
 
|}

Latest revision as of 10:36, 27 August 2011

wikibot[edit]

Table SH.CUSTOMERS
Description dimension table

Columns[edit]

Column Data Type Nullable Default Description
CUST_ID NUMBER not null primary key
CUST_FIRST_NAME VARCHAR2(20) not null first name of the customer
CUST_LAST_NAME VARCHAR2(40) not null last name of the customer
CUST_GENDER CHAR(1) not null gender; low cardinality attribute
CUST_YEAR_OF_BIRTH NUMBER(4, 0) not null customer year of birth
CUST_MARITAL_STATUS VARCHAR2(20) null customer marital status; low cardinality attribute
CUST_STREET_ADDRESS VARCHAR2(40) not null customer street address
CUST_POSTAL_CODE VARCHAR2(10) not null postal code of the customer
CUST_CITY VARCHAR2(30) not null city where the customer lives
CUST_CITY_ID NUMBER not null
CUST_STATE_PROVINCE VARCHAR2(40) not null customer geography: state or province
CUST_STATE_PROVINCE_ID NUMBER not null
COUNTRY_ID NUMBER not null foreign key to the countries table (snowflake)
CUST_MAIN_PHONE_NUMBER VARCHAR2(25) not null customer main phone number
CUST_INCOME_LEVEL VARCHAR2(30) null customer income level
CUST_CREDIT_LIMIT NUMBER null customer credit limit
CUST_EMAIL VARCHAR2(30) null customer email id
CUST_TOTAL VARCHAR2(14) not null
CUST_TOTAL_ID NUMBER not null
CUST_SRC_ID NUMBER null
CUST_EFF_FROM DATE(7) null
CUST_EFF_TO DATE(7) null
CUST_VALID VARCHAR2(1) null

Primary Key[edit]

Primary Key Columns
CUSTOMERS_PK CUST_ID

Indexes[edit]

Index Type Columns
CUSTOMERS_GENDER_BIX CUST_GENDER
CUSTOMERS_MARITAL_BIX CUST_MARITAL_STATUS
CUSTOMERS_YOB_BIX CUST_YEAR_OF_BIRTH

Check Constraints[edit]

Check Constraint Expression Description
SYS_C005164 "CUST_ID" IS NOT NULL
SYS_C005165 "CUST_FIRST_NAME" IS NOT NULL
SYS_C005166 "CUST_LAST_NAME" IS NOT NULL
SYS_C005167 "CUST_GENDER" IS NOT NULL
SYS_C005168 "CUST_YEAR_OF_BIRTH" IS NOT NULL
SYS_C005169 "CUST_STREET_ADDRESS" IS NOT NULL
SYS_C005170 "CUST_POSTAL_CODE" IS NOT NULL
SYS_C005171 "CUST_CITY" IS NOT NULL
SYS_C005172 "CUST_CITY_ID" IS NOT NULL
SYS_C005173 "CUST_STATE_PROVINCE" IS NOT NULL
SYS_C005174 "CUST_STATE_PROVINCE_ID" IS NOT NULL
SYS_C005175 "COUNTRY_ID" IS NOT NULL
SYS_C005176 "CUST_MAIN_PHONE_NUMBER" IS NOT NULL
SYS_C005177 "CUST_TOTAL" IS NOT NULL
SYS_C005178 "CUST_TOTAL_ID" IS NOT NULL

Foreign Keys[edit]

Relation Column Referenced Column
SH.COUNTRIES COUNTRY_ID COUNTRY_ID

Detail Tables[edit]

Detail Table Column Referencing Column
SH.SALES CUST_ID CUST_ID
Retrieved from "http://dbshelp.devio.at/index.php?title=SH.CUSTOMERS_(table)&oldid=3091"