Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 24 25 26 27 28 29 < 30 > 31 32 33 34 35 36 .. 218 >> Next

A table acquires data attributes when you define its columns in a CREATE TABLE or ALTER TABLE statement with at least a name and a data type phrase.

Data attributes control the internal representation of stored data and determine how that data is presented to a user. You can use the FORMAT phrase to change the external representation returned by a SELECT query.

The table definition directly affects the performance of applications that access that table. Proper planning helps you define a table correctly at the time of creation.

I Consider the following when planning your tables:

2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Populating Tables

Issue Considerations

Data type Columns for the same entity in different tables should have the same data type, and, for

consistency easy recognition, the same name.

For example, if the column title is EmpNo in one table, it should be EmpNo in another table.

Consistency of data across tables can be critical, depending on how the tables will be queried. The major issues are as follows:

IF you are . THEN .
joining the columns of one table with the columns of another table make sure that: Join columns are of the same data type and size. Data is consistent in both tables. For example, to join two tables on the Employee Name column, the name notation must be the same in both tables (for instance, last_name_first_initial, such as "Omura H").
Changing data in one table data in other tables may be affected. For example, updating a department number in the Department table also affects the Employee table, which contains a DeptNo column. To maintain data integrity, you can use: A macro (also, a stored procedure, trigger, or an application program) to update all the tables affected by a change. Referential integrity constraints. (For more details, see "Using Referential Integrity" on page 7-7.)

[NOT] NULL phrase

It may not always be possible to specify a value when inserting a new data row. For example, a new employee may not immediately have a job title.

You can explicitly define the nullability of a column with a NULL or NOT NULL phrase as follows:

IF the nullability of a column is. THEN.
defined as NOT NULL, and no value is given for that column when a row is inserted the INSERT statement returns an error.
not defined, and no value is given for that column when a row is inserted a NULL is supplied automatically.

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Populating Tables

Issue Considerations
If an application program requests data from a column (without NULL indicators) that allows nulls and a NULL is found, a substitute value that is compatible with the data type of the column is returned to the application instead of a null. The stubstitute value (a zero, blank, or zero-length element string) might be misleading, because NULL is an unknown.
[WITH] DEFAULT phrase As an alternative to allowing a column to default to NULL, you can define the column with the DEFAULT or WITH DEFAULT phrase. In this case, the specified constant or the system default value is substituted for an unknown value. The result might be misleading if a column is defined using a DEFAULT constant or a WITH DEFAULT system value. However, the application is able to interpret such results more easily than a NULL. (For more information on DEFAULT values and NULL substitutions returned to the host, see Teradata RDBMS SQL Reference, Volume 3.)
Aggregation results During aggregation, an unknown represented by a: NULL value is ignored in the calculation Default value is included in the calculation For example, assume you want the average salary of employees in Department 300: SELECT DeptNo, AVG(Salary) FROM Employee GROUP BY DeptNo WHERE DeptNo = 3 00 ; If a salary is not known, the result differs depending on how the column is defined:
IF the Salary column is defined with . THEN the result .
a DEFAULT value for an unknown value includes the default value as if it were the actual value. This may be far from the average result if all values were known.
NULL for an unknown value is the average of the salaries that are known. NULL is ignored. This may be closer to the actual average.

Changing the representation of an unknown You can use these tools to further control how unknowns are handled during aggregation:
IF you want to . THEN use the .
exclude substitutions by making NULL the default value for an unknown NULLIF function definition. For example: NULLIF(SALARY,defaultvalue) In this case, the default value is ignored in computing the average.
change an unknown represented by a NULL into the default value COALESCE function definition. For example: COALESCE(Salary,defaultvalue) where defaultvalue is the value to be used in computing the average.


2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS
Previous << 1 .. 24 25 26 27 28 29 < 30 > 31 32 33 34 35 36 .. 218 >> Next