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

Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 19 20 21 22 23 24 < 25 > 26 27 28 29 30 31 .. 76 >> Next


A relation is said to be in fifth normal form (5NF - sometimes called projection-join normal form, or PJ/NF) if and only if every join dependency in the relation is a consequence of the candidate keys of the relation.

This makes 5NF the final possible normal form to be achieved by taking projections and using joins. It is guaranteed to be free of all anomalies that can be removed by taking projections, but not necessarily of all possible anomalies.

3-8

Introduction to the Teradata RDBMS for UNIX
The Relational Model

Referential Integrity

Introduction

Enforcing RI in the Teradata RDBMS

Primary and Foreign Keys

Indexes

Referential Integrity

Referential integrity (RI) is a key concept for the relational model.

RI is defined by the Referential Integrity Rule, which states that a relational database cannot contain any unmatched foreign key values.

To implement RI in the Teradata RDBMS, you have three choices:

• Use the referential constraint checks supplied by the database software

• Write your own, site-specific macros.

• Enforce constraints through application code.

For review, a primary (parent) key is the candidate key selected to identify each tuple in a relation uniquely.

A foreign key is a (possibly composite) attribute of one relation whose values are required to match those of the primary key of some other relation.

An index is a special file used to speed retrieval. The typical index contains two fields: a value and a pointer to instances of that value in a data table. Because the Teradata RDBMS uses hashing to distribute rows across the AMPs, the value is condensed into an entity called a row hash, which is used as the pointer. The row hash is not the value, but a mathematically transformed address. The Teradata RDBMS uses this transformed address as a retrieval index.

The Teradata RDBMS uses indexes to define row uniqueness. This means that each row in a table must be identified uniquely, either by a unique primary index or by a unique secondary index.

The value or values chosen to be the unique index for a table are frequently the same values identified as the primary key during the data modeling process, but there is not any hard and fast rule that makes this so. In fact, physical database design considerations often lead to values other than the primary key being chosen as the unique index for a table

Introduction to the Teradata RDBMS for UNIX

3-9
The Relational Model

Referential Integrity

How do Primary Keys and Primary Indexes Relate?

The following table demonstrates some of the conceptual differences between primary indexes and primary keys.

Primary key Primary index
Naming convention used to ensure referential integrity Physical access mechanism
Required by the Teradata RDBMS only if referential integrity checks are to be performed Required by Teradata RDBMS
16-column limit
IF the Teradata RDBMS performs . . . THEN the column limit is . . .
referential integrity checks 16.
no referential integrity checks Unlimited.

Defined by CREATE TABLE statement Defined by CREATE TABLE statement
Must be unique May be unique or nonunique
Identifies a row uniquely Distributes rows
Values cannot be changed Values can be changed
May not be null May be null
Does not imply access path Defines most common access path

Referential integrity is a mechanism to keep you from accidentally What is Referential corrupting your database. Suppose you have a table like the

Integrity? following:

ORDER PART

Order Number Part Number Quantity
PK Not Null
FK FK
1 1 110
1 2 275
2 1 152

Part number and order number, each a foreign key in this relation, also form the composite primary key.

3-10

Introduction to the Teradata RDBMS for UNIX
The Relational Model

Referential Integrity

The Referential Constraint

Suppose you were to go the PART NUMBER table and delete the row defined by the primary key value 1. The key for the first and third rows in the ORDER PART table are now corrupted because there is no row in the PART NUMBER table with a primary key of 1 to support them. Such a situation exhibits a loss of referential integrity.

Now, suppose you had a mechanism to prevent this from happening? If you tried to delete the row with a primary key value of 1 from the PART NUMBER table, the database management system does not allow you to remove the row. This is the way the Teradata RDBMS maintains referential integrity.

If a row is selected for deletion, insertion, or updating that is in any way related to rows in another table, those related rows are also updated or deleted.

The table containing the referencing rows is the child table, while the table containing the referenced rows is the parent table. References between tables are made by means of foreign keys. Each foreign key in a child table is a primary key in the parent table. The combination of the foreign key, the parent key, and the relationship between them is called the referential constraint.
Previous << 1 .. 19 20 21 22 23 24 < 25 > 26 27 28 29 30 31 .. 76 >> Next