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

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''Table | SH.CUSTOMERS |- |} {| border="1" cellpadding="5" cellspacing="0" style="bord...)
 
 
(3 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.CUSTOMERS
 
| SH.CUSTOMERS
−
|-
+
|- valign="top"
 +
| '''Description
 +
| dimension table
 
|}
 
|}
  
−
 
+
=== 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"
−
|-
 
 
| CUST_ID
 
| CUST_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
 
|  
 
|  
−
| CUSTOMERS_PK
+
| primary key
−
|
+
|- valign="top"
−
|-
 
 
| CUST_FIRST_NAME
 
| CUST_FIRST_NAME
−
| VARCHAR2
+
| VARCHAR2(20)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| first name of the customer
−
|
+
|- valign="top"
−
|-
 
 
| CUST_LAST_NAME
 
| CUST_LAST_NAME
−
| VARCHAR2
+
| VARCHAR2(40)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| last name of the customer
−
|
+
|- valign="top"
−
|-
 
 
| CUST_GENDER
 
| CUST_GENDER
 
| CHAR(1)
 
| CHAR(1)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| gender; low cardinality attribute
−
| CUSTOMERS_GENDER_BIX
+
|- valign="top"
−
|-
 
 
| CUST_YEAR_OF_BIRTH
 
| CUST_YEAR_OF_BIRTH
−
| NUMBER
+
| NUMBER(4, 0)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer year of birth
−
| CUSTOMERS_YOB_BIX
+
|- valign="top"
−
|-
 
 
| CUST_MARITAL_STATUS
 
| CUST_MARITAL_STATUS
−
| VARCHAR2
+
| VARCHAR2(20)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer marital status; low cardinality attribute
−
| CUSTOMERS_MARITAL_BIX
+
|- valign="top"
−
|-
 
 
| CUST_STREET_ADDRESS
 
| CUST_STREET_ADDRESS
−
| VARCHAR2
+
| VARCHAR2(40)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer street address
−
|
+
|- valign="top"
−
|-
 
 
| CUST_POSTAL_CODE
 
| CUST_POSTAL_CODE
−
| VARCHAR2
+
| VARCHAR2(10)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| postal code of the customer
−
|
+
|- valign="top"
−
|-
 
 
| CUST_CITY
 
| CUST_CITY
−
| VARCHAR2
+
| VARCHAR2(30)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| city where the customer lives
−
|
+
|- valign="top"
−
|-
 
 
| CUST_CITY_ID
 
| CUST_CITY_ID
 
| NUMBER
 
| NUMBER
Line 87: Line 79:
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| CUST_STATE_PROVINCE
 
| CUST_STATE_PROVINCE
−
| VARCHAR2
+
| VARCHAR2(40)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer geography: state or province
−
|
+
|- valign="top"
−
|-
 
 
| CUST_STATE_PROVINCE_ID
 
| CUST_STATE_PROVINCE_ID
 
| NUMBER
 
| NUMBER
Line 101: Line 91:
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| COUNTRY_ID
 
| COUNTRY_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| foreign key to the countries table (snowflake)
−
|
+
|- valign="top"
−
|-
 
 
| CUST_MAIN_PHONE_NUMBER
 
| CUST_MAIN_PHONE_NUMBER
−
| VARCHAR2
+
| VARCHAR2(25)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer main phone number
−
|
+
|- valign="top"
−
|-
 
 
| CUST_INCOME_LEVEL
 
| CUST_INCOME_LEVEL
−
| VARCHAR2
+
| VARCHAR2(30)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer income level
−
|
+
|- valign="top"
−
|-
 
 
| CUST_CREDIT_LIMIT
 
| CUST_CREDIT_LIMIT
 
| NUMBER
 
| NUMBER
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer credit limit
−
|
+
|- valign="top"
−
|-
 
 
| CUST_EMAIL
 
| CUST_EMAIL
−
| VARCHAR2
+
| VARCHAR2(30)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer email id
−
|
+
|- valign="top"
−
|-
 
 
| CUST_TOTAL
 
| CUST_TOTAL
−
| VARCHAR2
+
| VARCHAR2(14)
 
| not null
 
| not null
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| CUST_TOTAL_ID
 
| CUST_TOTAL_ID
 
| NUMBER
 
| NUMBER
Line 150: Line 133:
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| CUST_SRC_ID
 
| CUST_SRC_ID
 
| NUMBER
 
| NUMBER
Line 157: Line 139:
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| CUST_EFF_FROM
 
| CUST_EFF_FROM
−
| DATE
+
| DATE(7)
 
| null
 
| null
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| CUST_EFF_TO
 
| CUST_EFF_TO
−
| DATE
+
| DATE(7)
 
| null
 
| null
 
|  
 
|  
 
|  
 
|  
−
|
+
|- valign="top"
−
|-
 
 
| CUST_VALID
 
| CUST_VALID
−
| VARCHAR2
+
| VARCHAR2(1)
 
| null
 
| null
 
|  
 
|  
 
|  
 
|  
 +
|}
 +
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|- valign="top"
 +
| CUSTOMERS_PK
 +
| CUST_ID
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|- valign="top"
 +
| CUSTOMERS_GENDER_BIX
 +
|
 +
| CUST_GENDER
 +
|- valign="top"
 +
| CUSTOMERS_MARITAL_BIX
 
|  
 
|  
 +
| CUST_MARITAL_STATUS
 +
|- valign="top"
 +
| CUSTOMERS_YOB_BIX
 +
|
 +
| CUST_YEAR_OF_BIRTH
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
  
 +
|- valign="top"
 +
| SYS_C005164
 +
| "CUST_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005165
 +
| "CUST_FIRST_NAME" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005166
 +
| "CUST_LAST_NAME" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005167
 +
| "CUST_GENDER" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005168
 +
| "CUST_YEAR_OF_BIRTH" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005169
 +
| "CUST_STREET_ADDRESS" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005170
 +
| "CUST_POSTAL_CODE" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005171
 +
| "CUST_CITY" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005172
 +
| "CUST_CITY_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005173
 +
| "CUST_STATE_PROVINCE" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005174
 +
| "CUST_STATE_PROVINCE_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005175
 +
| "COUNTRY_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005176
 +
| "CUST_MAIN_PHONE_NUMBER" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005177
 +
| "CUST_TOTAL" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| SYS_C005178
 +
| "CUST_TOTAL_ID" 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 188: 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 195: Line 272:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| 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 201: 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 11: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"