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

From dbscript Online Help
Jump to: navigation, search
Line 5: Line 5:
 
| '''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 19:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
 
 
 
 
|-
 
|-
Line 23: Line 26:
 
| not null
 
| not null
 
|  
 
|  
−
| CUSTOMERS_PK
+
| primary key
−
|
 
 
|-
 
|-
 
| CUST_FIRST_NAME
 
| CUST_FIRST_NAME
Line 30: Line 32:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| first name of the customer
−
|
 
 
|-
 
|-
 
| CUST_LAST_NAME
 
| CUST_LAST_NAME
Line 37: Line 38:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| last name of the customer
−
|
 
 
|-
 
|-
 
| CUST_GENDER
 
| CUST_GENDER
Line 44: Line 44:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| gender; low cardinality attribute
−
| CUSTOMERS_GENDER_BIX
 
 
|-
 
|-
 
| CUST_YEAR_OF_BIRTH
 
| CUST_YEAR_OF_BIRTH
Line 51: Line 50:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer year of birth
−
| CUSTOMERS_YOB_BIX
 
 
|-
 
|-
 
| CUST_MARITAL_STATUS
 
| CUST_MARITAL_STATUS
Line 58: Line 56:
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer marital status; low cardinality attribute
−
| CUSTOMERS_MARITAL_BIX
 
 
|-
 
|-
 
| CUST_STREET_ADDRESS
 
| CUST_STREET_ADDRESS
Line 65: Line 62:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer street address
−
|
 
 
|-
 
|-
 
| CUST_POSTAL_CODE
 
| CUST_POSTAL_CODE
Line 72: Line 68:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| postal code of the customer
−
|
 
 
|-
 
|-
 
| CUST_CITY
 
| CUST_CITY
Line 79: Line 74:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| city where the customer lives
−
|
 
 
|-
 
|-
 
| CUST_CITY_ID
 
| CUST_CITY_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 93: Line 86:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer geography: state or province
−
|
 
 
|-
 
|-
 
| CUST_STATE_PROVINCE_ID
 
| CUST_STATE_PROVINCE_ID
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 107: Line 98:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| foreign key to the countries table (snowflake)
−
|
 
 
|-
 
|-
 
| CUST_MAIN_PHONE_NUMBER
 
| CUST_MAIN_PHONE_NUMBER
Line 114: Line 104:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| customer main phone number
−
|
 
 
|-
 
|-
 
| CUST_INCOME_LEVEL
 
| CUST_INCOME_LEVEL
Line 121: Line 110:
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer income level
−
|
 
 
|-
 
|-
 
| CUST_CREDIT_LIMIT
 
| CUST_CREDIT_LIMIT
Line 128: Line 116:
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer credit limit
−
|
 
 
|-
 
|-
 
| CUST_EMAIL
 
| CUST_EMAIL
Line 135: Line 122:
 
| null
 
| null
 
|  
 
|  
−
|  
+
| customer email id
−
|
 
 
|-
 
|-
 
| CUST_TOTAL
 
| CUST_TOTAL
 
| VARCHAR2(14)
 
| VARCHAR2(14)
 
| not null
 
| not null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 148: Line 133:
 
| NUMBER
 
| NUMBER
 
| not null
 
| not null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 155: Line 139:
 
| NUMBER
 
| NUMBER
 
| null
 
| null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 162: Line 145:
 
| DATE(7)
 
| DATE(7)
 
| null
 
| null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 169: Line 151:
 
| DATE(7)
 
| DATE(7)
 
| null
 
| null
−
|
 
 
|  
 
|  
 
|  
 
|  
Line 178: Line 159:
 
|  
 
|  
 
|  
 
|  
 +
|}
 +
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|-
 +
| CUSTOMERS_PK
 +
| CUST_ID
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| CUSTOMERS_MARITAL_BIX
 +
|
 +
| CUST_MARITAL_STATUS
 +
|-
 +
| CUSTOMERS_YOB_BIX
 +
|
 +
| CUST_YEAR_OF_BIRTH
 +
|-
 +
| CUSTOMERS_GENDER_BIX
 
|  
 
|  
 +
| CUST_GENDER
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''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 ===
 
{| 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 195: Line 274:
 
|}
 
|}
  
−
 
+
=== 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"

Revision as of 08:29, 11 February 2010

wikibot

Table SH.CUSTOMERS
Description dimension table


Columns

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

Primary Key Columns
CUSTOMERS_PK CUST_ID

Indexes

Index Type Columns
CUSTOMERS_MARITAL_BIX CUST_MARITAL_STATUS
CUSTOMERS_YOB_BIX CUST_YEAR_OF_BIRTH
CUSTOMERS_GENDER_BIX CUST_GENDER

Check Constraints

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

Relation Column Referenced Column
SH.COUNTRIES COUNTRY_ID COUNTRY_ID

Detail Tables

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