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 .. 32 33 34 35 36 37 < 38 > 39 40 41 42 43 44 .. 76 >> Next


Introduction to the Teradata RDBMS for UNIX

6-1
Views

Creating and Altering Views

Creating and Altering Views

This topic discusses the SQL statements used to create and alter Introduction views.

Use the CREATE VIEW statement to create views for the Teradata Creating a Tera^ta RDBMS. The view definitions are stored in the Data Dictionary.

RDBMS View

Suppose you wanted to create a view called Employee_Info for a personnel clerk who needs access to the Personnel database, but whose access needs to be restricted. The clerk needs to know the following information for employees:

• Employee name

• Job title

• Department

The SQL to create the view looks like this:

CREATE VIEW Employee_Info (Employee,

JobTitle,

Department

)

AS SELECT Name,

JobTitle,

DeptName

FROM Employee,

Department

WHERE Employee.DeptNo = Department.DeptNo ;

The SQL to perform a simple query of this view looks like this: SELECT *

FROM Employee_Info ;

This query produces the following results table.

6-2

Introduction to the Teradata RDBMS for UNIX
Views

Creating and Altering Views

Altering a Teradata RDBMS View

Employee JobTitle Department
Peterson J Payroll Ck Administration
Moffit H Recruiter Administration
Leidner P Secretary Exec Office
Smith T Engineer Engineering
Omura H Programmer Engineering
Kemper R Assembler Manufacturing
Aguilan J Manager Manufacturing
Phan A Vice Pres Exec Office
Marston A Secretary Engineering
Reed C Technician Engineering
Chin M Controller Administration
Watson L Vice Pres Engineering
Regan R Purchaser Manufacturing

Use the REPLACE VIEW statement to alter the characteristics of an existing view.

For example, suppose you want to replace the Employee_Info view with one that includes an employee number column. The SQL to alter the view looks like this:

REPLACE VIEW Employee_Info (Number,

Employee,

Department

)

AS SELECT EmpNo,

Name,

DeptName FROM Employee,

Department

WHERE Employee.DeptNo = Department.DeptNo ;

If the view named Employee_Info had not existed previously, this SQL statement would create it.

Introduction to the Teradata RDBMS for UNIX

6-3
Views

Why You Should Use Views

Introduction

Simplified User Perception of the Database

Security for Restricting Table Access and Updates

Well-Defined, High Performance Access to the Database

Why You Should Use Views

This topic discusses the reasons you should use views with your Teradata RDBMS.

Among the topics discussed are:

• Simplified user perception of the database.

• Security for restricting table access and updates.

• Well-defined, well-tested, high performance access to data.

• Logical data independence, which minimizes application modification if base tables need to be restructured.

Views simplify user perception of the database in two ways:

• They allow users to focus on the part of the database that is of interest to them and only on that part.

• They simplify retrieval by eliminating the need to formulate complex queries.

Views can be constructed in such a way that target users can see only the data columns that they need to see and no others. For example, a clerk might need to know the department number and years of service for an employee, but should not know the salary for that employee. You can construct a view that permits the clerk to see the information required and no other columns in the table. The unviewable data is sometimes called hidden data.

Forcing users to access the database through views is an effective means of securing hidden data and, effectively, of maintaining authorization control.

Users sometimes have difficulty formulating SQL queries that report the information they need efficiently.

Views provide a means for application programmers to develop and test SQL statements that are highly optimized. These views can then be provided to users who can use them without worrying about tying up system resources needlessly.

Well written macros provide the same facility.

6-4

Introduction to the Teradata RDBMS for UNIX
Views

Why You Should Use Views

Logical Data Independence

Logical data independence is one of the hallmarks of relational database management systems, and views are one of the easier ways to provide logical data independence in the face of restructuring the database.

Sometimes it becomes necessary to reorganize the database in such a way that columns previously belonging to one table are now allocated to a different table. This sort of reorganization is not generally encouraged, but one example of when it might become necessary is a vertical split of a table for performance reasons.

Using views to present these columns to end users greatly lessens, if not removes, the need to change user applications that use those views.

Introduction to the Teradata RDBMS for UNIX

6-5
Views

Restrictions on DML Operations on Views
Previous << 1 .. 32 33 34 35 36 37 < 38 > 39 40 41 42 43 44 .. 76 >> Next