Examples | Use of Location Entities| Teradata Vantage - Example: The Location Entity Before It Is Fully Attributed - Advanced SQL Engine - Teradata Database
Database Design
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā¢
Introduction to Database Design
Overview
Designing for Teradata Database
Overview
Teradata Database
Data Placement to Support Parallel Processing
Synchronization of Parallel Operations
Design Considerations
Usage Considerations: OLTP and Data Warehousing
Usage Considerations: Summary Data and Detail Data
Usage Considerations: Simple and Complex Queries
Usage Considerations: Ad Hoc Queries
Databases and Data Modeling
Database Design Life Cycle
Designing for OLTP and Designing for Data Warehousing Support
ANSI/X3/SPARC Three Schema Architecture
Requirements Analysis
Logical Database Design
Activity Transaction Modeling
Physical Database Design
Requirements Analysis
Overview
Developing an Enterprise Data Model
Semantic Data Modeling
Overview
The Entity-Relationship Model
Entities, Relationships, and Attributes
Translating Entities and Relationships Into Tables
Relationship Theory
One-to-One Relationships
One-to-Many Relationships
Many-to-Many Relationships
Moving From an Entity-Relationship Analysis to Normalization
The Normalization Process
Overview
Properties of Relations and Their Logical Manipulation
Functional, Transitive, and Multivalued Compatibilities
The Normal Forms
Third and Boyce-Codd Normal Forms
Decomposing Relations
Identifying Candidate Primary Keys
Foreign Keys
The Referential Integrity Rule
Domains and Referential Integrity
Normalization and Database Design Problems
General Procedure for Achieving a Normalized Set of Relations
Advantages of Normalization for Physical Database Implementation
Denormalized Physical Schemas and Ambiguity
The Activity Transaction Modeling Process
Overview
Purpose of the ATM Process
Goals of the ATM Process
Terminology Used in the ATM Process
Example: The Location Entity Before It Is Fully Attributed
Example: A Randomly Selected Row From the Location Entity
Example: CustNum Column From the Location Entity
Example: The Primary Key Column for the Location Entity
Example: The Three Foreign Keys For the Location Entity
Domains
Column Names and Constraints
Guidelines for Naming Columns
Key Values and Relationships Among Tables
Domains Form
Constraints Form
System Form
Application Form
Report/Query Analysis Form
Table Form
Filling Out the Table Form
Table Form Example
Table Form: Basic Information
Table Form: Column-Level Information
Table Form: Miscellaneous Column-Level Information
Table Form: Access Information
Table Form: Data Demographics for Single-Column Database Objects
Maximum and Typical Column Value Frequencies
Table Form: Data Demographics for Multicolumn Database Objects
Row Size Calculation Form
Denormalizing the Physical Schema
Overview
Denormalization Issues
Commonly Performed Denormalizations
Alternatives to Denormalization
Denormalizing with Repeating Groups
Denormalizing Through Prejoins
Denormalizing through Join Indexes
Derived Data Attributes
Denormalizing Through Global Temporary and Volatile Tables
Denormalizing Through Views
Dimensional Modeling, Star, and Snowflake Schemas
Indexes and Maps
Overview
Primary Indexes and Primary AMP Indexes
Secondary Indexes
Join Indexes
Hash Indexes
Index Considerations
Index Type Comparisons
Evaluating Indexes
Maps
Primary Index, Primary AMP Index, and NoPI Objects
Overview
Primary Indexes and Primary AMP Indexes
Primary Index Defaults
Unique and Nonunique Primary Indexes
Partitioned and Nonpartitioned Primary Indexes
Choosing an Indexing Method for a Column-Partitioned Table or Join Index
NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes
Column Partitioning
Column Partitioning Performance
Storage and Other Overhead Considerations for Partitioning
Advantages and Disadvantages of Partitioned Primary Indexes
Usage Recommendations For Row Partitioning
Single-Level Partitioning
Single-Level Partitioning Case Studies
Multilevel Partitioning
3-Level Row Partitioning Example
Summary of Primary Index Selection Criteria
Principal Criteria for Selecting a Primary Index
Selecting a Primary Index for a Queue Table
Column Distribution Demographics and Primary Index Selection
Scenario 1
Scenario 2
Scenario 3
Scenario 4
Performance Considerations for Primary Indexes
Duplicate Row Checks for SET Tables with NUPIs
Minimizing Duplicate NUPI Row Checks
Secondary Indexes
Overview
Space Considerations
Unique Secondary Indexes
Nonunique Secondary Indexes
NUSI Bit Mapping
NUSIs and Query Covering
Value-Ordered NUSIs and Range Conditions
Selecting a Secondary Index
Secondary Index Access Summarized by Example
Join and Hash Indexes
Overview
Join Indexes
Using Join Indexes
Join Index Design Tips
Join Index Benefits and Costs
Maintenance Cost as a Function of Number of Hits Per Data Block
Maintenance Cost as a Function of Row Size
Join Index Maintenance Cost as a Function of Insert Method
Cost/Benefit Analysis of Join Indexes
Cost/Benefit Analysis for Join Indexes
Join Index Types
Simple Join Indexes
Defining a Simple Join Index on a Binary Join Result
Defining and Using a Simple Join Index With an n-way Join Result
Single-Table Join Indexes
Single-Table Join Index
Aggregate Join Indexes
Sparse Join Indexes
Using Outer Joins in Join Index Definitions
Using Outer Joins to Define Join Indexes
Creating Join Indexes Using Outer Joins
Join Indexes and Tactical Queries
Join Index Definition Restrictions
Improving Join Index Performance
Join Index Storage
Value-Ordered Storage of Join Index Rows
Hash Indexes
Collecting Statistics on Hash Index Columns
Hash Index Definition Restrictions
Hash and Join Index Interactions With Other Teradata Database Systems and Features
Tradeoffs for Join or Hash Indexes
Designing for Database Integrity
Overview
Sources of Data Quality Problems
Logical Integrity Constraints
How Relational Databases Are Built From Logical Propositions
Inclusion Compatibilities
Semantic Integrity Constraint Types
Semantic Constraint Specifications
Semantic Constraint Enforcement
Updatable Cursors and Semantic Database Integrity
Semantic Integrity Constraints for Updatable Views
Summary of Fundamental Database Principles
Physical Database Integrity
Disk I/O Integrity Checking
About Reading or Repairing Data from Fallback
Designing for Missing Information
Overview
Semantics of SQL Nulls
Inconsistencies in How SQL Treats Nulls
Bivalent and Higher-Valued Logics
Alternatives To Nulls for Representing Missing Information
Systematic Use of Default Values
Redesigning the Database to Eliminate the Need for Nulls
Manipulating Nulls With SQL
Logical and Arithmetic Operations on Nulls
NULL Literals
Hashing on Nulls
Null Sorts as the Lowest Value in a Collation
Searching for Nulls Using a SELECT Request
Searching for Nulls and Nonnulls In the Same Search Condition
Excluding Nulls From Query Results
Nulls and the Outer Join
Using Data Compression
Overview
Compression Types Supported by Vantage
Identifying Uncompressed, Single-Value Compressed, and MultiValue Compressed Tables
Multivalue Compression
Tradeoffs Between Multivalue Compression and Storage Requirements for Compressed Values
Algorithmic Compression
Row Compression
Row Header Compression
Autocompression
Using Hash Index and Join Index Row Compression
Block-Level Compression
Hardware-Based Block-Level Compression
Software-Based Block-Level Compression
Compressing Data Loaded into Empty Subtables Set to AUTOTEMP
Interaction between Block-Level Compression and TVS Temperature Query Band Values
Controlling BLC at the System Level Using the DBS Control Utility
Block-Level Compression Usage Notes
Interaction between Block-Level Compression Settings
CPU Considerations on Different Teradata Platforms
Finding Tables and Databases with Compressed Data Blocks
Obtaining Information about Tables with Compressed Data Blocks
About System and AMP Outages During Compression
CPU Costing for Software-Based BLC
Estimating BLC Space Savings and CPU Usage
Choosing a Software-based Compression Scheme
Combining Compression Methods
Related Topics
Database-Level Capacity Planning Considerations
Overview
Capacity Planning
Storing Data Efficiently
Base Table Row Format
Hash and Join Index Row Structures
Presence Bits
Table Headers
Sizing Structured UDT Columns
System-Derived and System-Generated Columns
Data Type Considerations
Numeric Data Types
Integer Data Types
Non-INTEGER Numeric Data Types
Byte Data Types
DateTime Data Types
Interval Data Types
Period Data Types
Character Data Types
XML/XMLTYPE Data Type
JSON Data Type (Text-Based Format)
JSON Data Type (Binary Format)
JSON Data Type (Universal Binary Format)
DATASET Data Type
User-Defined Data Types
Array Data Types
Row Size Calculation
Sizing Databases, Users, and Profiles
Sizing Base Tables, LOB Subtables, XML Subtables, and Index Subtables
Sizing Base Tables, Hash Indexes, and Join Indexes
Sizing a LOB or XML Subtable
Sizing a Unique Secondary Index Subtable
Sizing a Nonunique Secondary Index Subtable
Sizing User-Defined Routines
Sizing a Reference Index Subtable
Sizing Spool Space
Sizing a Query Capture Database
Sizing Table Space Empirically
System-Level Capacity Planning Considerations
Overview
Database Size Considerations
Teradata Secure Zones inside a Database
System Disk Contents
Data Disk Contents
Data Disk Space
Permanent Space Allocations
Estimating Database Size Requirements
About Global Space Accounting
Determining Available User Table Data Space
Designing for Backups
Design Issues for Tactical Queries
Overview
Tactical Queries Defined
Scalability Considerations for Tactical Queries
Localizing the Work
Database Design Techniques to Support Localized Work
Single-AMP Queries and Partitioned Tables
Recommendations for Tactical Queries and Row-Partitioned Tables
Sparse Join Indexes and Tactical Queries
All-AMP Queries
All-AMP Tactical Queries and Partitioned Tables
Application Opportunities for Tactical Queries
Other Tools Useful for Monitoring and Managing Tactical Queries
Monitoring Active Work
Notation Conventions
Overview
Table Column Definition and Constraint Abbreviations
Character Symbols
Predicate Calculus and Set Theory Notation Used in This Document
Dependency Theory Notation Used in This Document
Teradata System Limits
Overview
System Limits
Database Limits
Session Limits
Designing With Task-Oriented Profiles
Overview
Concepts, Policies, User Profiles, and Rules
Summary Physical Design Scenario
Overview
Prerequisites for the Process Review
Process Review
Sample Worksheet Forms
Overview
Designing Tables for Optimal Performance
Overview
Minimizing Table Size
Reducing the Number of Table Columns
Adjusting the DATABLOCKSIZE and MERGEBLOCKRATIO Table Parameters
Adjusting FREESPACE
Using Identity Columns, Compression, and Referential Integrity for Optimal Performance Design
Using Indexes to Enhance Performance
Understanding the Effects of Altering Tables
Compression Methods
Overview
Multivalue Compression
Algorithmic Compression
Block-Level Compression
Temperature-Based Block-Level Compression
Row Compression
Autocompression
Row Header Compression
References
Teradata Publications Related to Database Design
Additional Information
Teradata Links
LocationNum
CustNum
State
Country
PK, SA
FK, NN
FK
FK, NN
2
10
CA
USA
7
0
CA
USA
1
2
NY
USA
4
7
PR
USA