3.8 View

Question 1

Refer to the following CREATE VIEW statement and view table.

Answer 1

The name of the view table immediately follows the CREATE VIEW keywords.

Question 2

Refer to the following CREATE VIEW statement and view table.

Answer 2

FacultyName AS Professor in the view query indicates that Professor replaces FacultyName in the view table.

Question 3

Refer to the following CREATE VIEW statement and view table.

Question 4

What is a view table in database management?

Answer 4

A view table is a virtual table created by a SELECT statement that does not store data permanently but presents data from one or more tables like a regular table.

Question 5

How do views help optimize database design for users?

Answer 5

• Views can combine and restructure data from multiple tables without altering the underlying database design.
• It’s easier for users to access and understand complex datasets.

Question 6

What is the function of a materialized view?

Answer 6

1. It can improve performance by providing fast access to pre-computed data.
2. A materialized view stores the result of a view query.

must be refreshed when the base table data changes

Question 7

What is the primary advantage of using views to manage sensitive data?

Answer 7

Views can exclude sensitive columns, such as salary data, thereby providing access to only the necessary information for specific users or applications.

Question 8

Why might view tables not allow insert, update, or delete operations?

Answer 8

Operations on view tables may not map cleanly to underlying base tables, leading to undefined or problematic data updates, particularly with primary keys and aggregates.

Question 9

What is the purpose of the WITH CHECK OPTION clause in view creation?

Answer 9

It ensures that any INSERT or UPDATE operations on the view; meet the conditions specified in the view query’s WHERE clause, preventing changes that would render the data invisible in the view.

Question 10

In a view created with a join, what are the tables in the FROM clause referred as?

Answer 10

They are known as base tables, which are the source tables from which the view draws its data.

Question 11

What are the advantages of saving complex queries as views?

Answer 11

Views can encapsulate complex queries, saving time for users who need to run those queries repeatedly without having to write them out each time.

Question 12

How is database performance related to using views?

Answer 12

• Using views designed with optimal queries can enhance performance because they allow for pre-optimized query execution.
• The performance of a query on a view may vary from a direct query on base tables.

Question 13

What does the CREATE VIEW statement do?

Answer 13

It creates a new view by specifying the view’s name, columns, and the SELECT statement that defines it.

Question 14

Why might users prefer a view that joins tables, like Employee and Address?

Answer 14

1. A view can present combined data in a single table format
2. Views simply data access for users who need information from both tables.

Question 15

What happens when a view is queried in SQL?

Answer 15

The database processes the view’s SELECT statement along with the user query, treating it as a single combined query against the base tables.

Question 16

Can views improve query security and why?

Answer 16

Yes, by allowing access to specific data while keeping other data, possibly sensitive, hidden from the user.

Question 17

How do views assist in simplifying complex database queries?

Answer 17

They encapsulate complex logic within a SELECT statement, allowing users to query the view directly without understanding the details.

Question 18

What restriction applies to views when performing an INSERT operation?

Answer 18

An insert may be problematic for a view if:

1. It the insert doesn’t include necessary primary keys
2. Attempts to insert into aggregated columns.

📖

Question 19

Can a view be defined based on other views?

Answer 19

Yes, a view can be built using other views, though they are not considered base tables.

Question 20

Describe how a view and a base table differ in data storage.

Answer 20

• Views do not store data; they only describe a way to retrieve data from base tables
• Base tables physically store data.

Question 21

What is a join view?

SELECT DepartmentName, EmployeeName 
FROM Department 
INNER JOIN Employee ON DepartmentID = Employee.DepartmentID

Answer 21

A join view involves combining data from two tables

Question 22

What role does a view play in data abstraction?

Answer 22

Views abstract away complex queries and database structures, presenting a simplified data interface to users.

To abstract (away), specifically in computer science, refers to intentionally obscuring the details of how something works in order to simplify.

Question 23

How often must materialized views be refreshed?

Answer 23

They must be refreshed whenever the base table data changes, to ensure accuracy.

Question 24

Why might databases disallow certain data modifications through views?

Answer 24

Because the modification might not map correctly back to the base tables, creating ambiguity or undefined behavior.

Question 25

Why is the `WITH CHECK OPTION` useful?

Answer 25

It ensures data integrity by *preventing inserts or updates* that would make data invisible to the view.

Question 26

Can you delete data directly from a view?

Answer 26

Generally, deletes are restricted from views due to potential ambiguity *in corresponding changes to base tables*.

Question 27

How are views utilized to maintain *data consistency*?

Answer 27

By allowing *only controlled access to data* via the view, thus ensuring data access patterns don’t violate **business rules**.

Question 28

What is the `WITH CHEAK OPTION` clause?

Answer 28

``` CREATE VIEW ViewName [ ( Column1, Column2, ... ) ] AS SelectStatement [WITH CHECK OPTION] ; ``` ## Footnote Disregard the brackets for the With CHEAK opt [📖](https://app.milanote.com/1TqbXz1f69yoeE?p=N4oXWlErVzz)

Question 29

Why would adding to `SalesEmployee` fail? 1. ID: 8520 1. Name: Jiho Chen 1. Department: 80

Answer 29

The new employee is inserted into `Employee` but is not in department 51 and therefore does not appear in the view table.

Question 30

Write a statement to prevent inserts and updates which wouldn't satisfy the view query `WHERE` clause.

Answer 30

``` CREATE VIEW SalesEmployee (ID, Name, Department) AS SELECT * FROM Employee WHERE DepartmentCode = 51 WITH CHEAK OPTION; ```

Question 31

What is a standard view?

Answer 31

A standard view is essentially a saved SELECT query that acts as a virtual table. The query is executed every time the view is accessed. ## Footnote Standard views are ideal for real-time data retrieval.

Question 32

What is a materialized view?

Answer 32

A materialized view stores the result set of the query as a physical table in the database. ## Footnote Materialized views are useful for improving performance in complex queries and aggregations.

Question 33

When should you use standard views?

Answer 33

Use standard views when you need real-time data and do not want to store redundant data. ## Footnote They are good for simple queries or when the data changes frequently.

Question 34

When should you use materialized views?

Answer 34

Use materialized views when performance is critical, the underlying data doesn't change too frequently, and you can tolerate some latency in the data being displayed. ## Footnote They are suitable for complex queries, aggregations, and reporting.

Question 35

True or False: A standard view stores data as a physical table.

Answer 35

False ## Footnote Standard views do not store data; they execute the query each time they are accessed.

Question 36

Fill in the blank: A materialized view is similar to a standard view, but it _______.

Answer 36

stores the result set of the query as a physical table in the database. ## Footnote This allows for faster data retrieval compared to standard views.

Question 37

What is a materialized view?

Answer 37

A materialized view stores the result set of a query as a physical table.

Question 38

How does a materialized view differ from a regular view?

Answer 38

A materialized view holds pre-computed results, while a regular view executes its underlying query every time it is accessed.

Question 39

What is a key advantage of using materialized views?

Answer 39

Significantly speeds up query performance.

Question 40

What issue can arise with materialized views due to pre-computation?

Answer 40

The data can become stale as the underlying data changes.

Question 41

What must be done to maintain data consistency in materialized views?

Answer 41

The materialized view must be periodically refreshed.

Question 42

True or False: Users of a materialized view must always provide search terms.

Answer 42

False.

Question 43

What is not a general restriction for materialized views regarding user input?

Answer 43

Users interacting with the view don't necessarily have to provide search terms.

Question 44

Are indexes required for the tables referenced in a materialized view?

Answer 44

No, indexes are not a strict requirement.

Question 45

What can improve the performance of the initial creation and refresh of a materialized view?

Answer 45

Indexes can improve performance.

Question 46

Is compression a requirement for using materialized views?

Answer 46

No, compression is not inherently tied to the use of materialized views.

Question 47

Fill in the blank: A materialized view must be _______ to reflect the latest data in the base tables.

Answer 47

refreshed.

Question 48

What technique can be used to optimize storage in relation to materialized views?

Answer 48

Compression.