15.00 - Procedure - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Procedure

Perform the following procedure to complete the Report/Query Analysis form for each transaction:

1 Begin the analysis with standard reports, load requirements, and similar data.

2 Identify the tables involved and enter the names of each on a Report/Query Analysis form.

3 Identify which columns are printed and enter the names of each on the same Report/Query Analysis form that documents their tables.

4 Identify whether rows are accessed by specific column values.

If so, enter the names of each column for which access values must be obtained and mark them with a V.

Note that these must specify equijoin conditions matching on a single value.

5 Identify whether tables must be joined.

If so, enter the names of the join columns and mark them with a J.

Note that these must specify equijoin conditions.

6 Identify the number of rows that qualify for processing. This is not the number of rows in the answer set, but the number of rows that must be processed in order to obtain the answer set.

Use these guidelines to determine what values to use.

a Identify the number of rows, if any, that qualify for value access.

b Identify the number of rows, if any, that qualify for join access.

c Select the smaller of 1 and 2 and use it as the value for the number of rows processed.

d If there are no value access rows and no join access rows, then use the number of rows in the table.

Examples  

There are over 1,000 patients in the hospital database used for these examples. Each patient is hospitalized long-term (where long‑term means one year).

Example  

The Patient Detail by Room Assignment report derives from the patient and bed tables. Values and selected demographics for those tables are modeled as follows.

 

Patient

Cardinality: 1,000

Patient_ID

Bed_Number

Nurse_ID

Doctor_ID

PK

FK, ND

FK

FK

701

A

201

501

702

B

201

502

703

C

202

502

 

Bed

Cardinality: 1,500

Bed_Number

Room_Number

PK, ND

FK

A

101

B

102

C

103

 

Patient Detail by Room Assignment

Room_ Number

Patient_ID

Bed_Number

Nurse_ID

101

701

A

201

716

F

233

723

T

205

102

702

B

201

 

725

Q

201

 

748

L

222

 

Frequency or Importance Ranking: __________________________

Report/Query Description:  Patient detail by room assignment __________

Table: Patient

                       Number of Output Rows: 1,000

Input Value

Or Source

Bed

Bed Number

 

 

Access Column(s)

Bed Number

 

 

 

Table: Bed

                       Number of Output Rows: 1,500

Input Value

Or Source

Patient

Bed Number

 

 

Access Column(s)

Bed Number

 

 

 

Table:

                       Number of Output Rows:

Input Value

Or Source

 

 

 

Access Column(s)

 

 

 

 

Table:

                       Number of Output Rows:

Input Value

Or Source

 

 

 

Access Column(s)

 

 

 

Example  

The Billing Report for Patient 705 is more complex than the report analyzed in the previous example. The cardinality of the PatientServiceHistory table is based on the assumption that there is one year of service data kept in the history file, each patient receives an average of two services per day, and there are roughly 1,000 patients in the hospital at any time.

The calculation is done as follows.

     1,000 patients * 365 days * 2 services = 730,000 rows.

 

Service

 

Cardinality: 125

Service_ID

Description

Cost

PK

 

 

801

Appendectomy

                    $   500.00

 

802

Tonsillectomy

                    $   350.00

 

803

Anesthesia

                    $1,000.00

 

 

Patient_Service_History

Cardinality: 730,000

Patient_ID

Service_ID

Timestamp

PK

FK

FK

 

701

801

2007-08-05 11:39

701

803

2007-08-05 23:59

705

801

2007-08-05   8:23

705

802

2007-08-05 13:43

705

803

2007-08-05 18:32

705

814

2007-08-05 23:56

 

Billing Report for Patient 705, August 2000

 

Date

Service_ID

Description

Cost

08/05/2007

801

Appendectomy

$    500.00

 

802

Tonsillectomy

$    350.00

 

803

Anesthesia

$1,000.00

 

Total for 08/05/2007

$1,850.00

 

 

 

 

 

08/28/2007

814

Face lift

$3,200.00

 

 

Total for 08/28/2007

$3,200.00

 

Example  

This report is based on the same tables as that of the previous example. It reports summary information from those tables for all patients on a particular date. The value for the date column is obtained using the EXTRACT function on the Timestamp value for a performed service.

 

Billing Report for August 5, 2007

Patient_ID

Number_of_Services

Total_Cost

701

                  2

$ 1,500.00

705

                  4

$ 2,100.00

713

                  1

$    320.00

783

                12

$ 2,400.00

795

                  2

$    890.00

 

Frequency or Importance Ranking:

Report/Query Description:  Billing report for a specific date                                           

Table: Patient_Service_History

Number of Output Rows:    2,000

Input Value

Or Source

Current_Date

Service

Service_ID

 

Access Column(s)

Current_Date

Service_ID

 

 

Table: Service

Number of Output Rows:          2

Input Value

Or Source

Patient_Service_History

Service_ID

 

 

Access Column(s)

Service_ID

 

 

 

Table:          

 

 

Number of Output Rows:

Input Value

Or Source

 

 

 

Access Column(s)

 

 

 

 

Table:

Number of Output Rows:

Input Value

Or Source

 

 

 

Access Column(s)