Teradata

Each Foreign Key must exist as a Primary Key.

Foreign Keys can change values over time.

first : True

second : False

1. Foreign Keys can change values over time.

2. Each Foreign Key must exist as a Primary Key.

If the teradata is going to be only a data base means It won’t change the System development life cycle (SDLC)

If you are going to use the teradata utilities then it will change the Architecture or SDLC

If your schema is going to be in 3NF then there won’t be huge in change

There are 12 codd's rules applied to the teradata database

A clique is a set of Teradata nodes that share a common set of disk arrays. Cabling a subset of nodes to the same disk arrays creates a clique.

Each AMP vproc must have access to an array controller, which in turn accesses the physical disks. AMP vprocs are associated with one or more ranks

(or mirrored pairs) of data. The total disk space associated with an AMP is called a vdisk. A vdisk may have up to three ranks. Hence Vdisk will communicate

with physical storage through array controllers.

BTEQ(Basic teradata query)

It allows us to write SQL statements along with BTEQ commands. We can use BTEQ for importing,exporting and reporting purposes.

The commands start with a (.) dot and can be terminated by using (;), it is not mandatory to use (;). SQL statements doesnt start with a dot , but (;) is

compulsary to terminate the SQL statement.

BTEQ will assume any thing written with out a dot as a sql statement and requires a (;) to terminate it.

The GRANT option for any particular table depends on the privilages of the user. If it is an admin user you can grant privilages at any point of time.

The deafult roles associated with the newly created tables depend on he schema in which they are created.

V2R6 included the feature of replica in it.in which copy of data base are available on another system.meam V2R6 provide the additional data protaction as

comprison to V2R5 while if data from one system has been vanishes.

After creating tables dynamically in the Teardata, where is the GRANT table option usually done ? When tables are newly created, what is the default role and

The EXPLAIN facility is a teradata extension that provides you with an "ENGLISH" translation of the steps choosen by the optimizer to execute an SQL

statement.It may be used oin any valid teradata database with a preface called "EXPLAIN".

The following is an example:-

EXPLAIN select last_name,first_name FROM employees;

The EXPLAIN parses the SQL statement but does not execute it.

This provides the designer with an "execution stratergy".

The execution stratergy provides what an optimizer does but not why it choses them.

The EXPLAIN facility is used to analyze all joins and complex queries.

Performance tuning and optimization of a query involves collecting statistics on join columns, avoiding cross product join, selection of appropriate primary

index (to avoid skewness in storage) and using secondary index.

Avoiding NUSI is advisable.

Global Temporary tables (GTT) -

1. When they are created, its definition goes into Data Dictionary.

2. When materialized data goes in temp space.

3. thats why, data is active upto the session ends, and definition will remain there upto its not dropped using Drop table statement.

If dropped from some other session then its should be Drop table all;

4. you can collect stats on GTT.

Volatile Temporary tables (VTT) -

1. Table Definition is stored in System cache

2. Data is stored in spool space.

3. thats why, data and table definition both are active only upto session ends.

4. No collect stats for VTT.

Teradata will redirect the new inserted row as per its PI to the target AMP (on the basis of its row hash value), and if it find same row hash value in that

AMP (hash synonyms) then it start comparing the whole row, and find out if duplicate.

If its a duplicate it silently skips it without throwing any error.

Group by clause is used in Teradata to remove repetitive values in a column or group of columns, when we select result of a particular query. And it represent it as single entity.

11 1/2 codd,s rule is supported by teradata.

Yes we can but some times we need to check the value which lookup is returning like it happens in teradata if we are having lookup on varchar column and record is coming from source system it never works because when informatica creates cache from there it adds space to the values.so we always have to use ltrim(rtrim)) function in lookup query.

FSLDM --> Financial Services Logical Data Model (Teradata FS-LDM 7.0) its developed by Teradata for the financial sectors (Speciafially for Bank). it can be customised based on the user requirement.

StarSchma --> Its a relational database schema for representing multidimensional data. The data is stored in a central fact table, with one or more tables holding information on each dimension.

So both are entirely different.......

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

ODS=>Operational Data Source

1. Provides a single version of the truth

2. Minimizes synchronization