3.8 View Flashcards
Refer to the following CREATE VIEW statement and view table.
The name of the view table immediately follows the CREATE VIEW keywords.
Refer to the following CREATE VIEW statement and view table.
FacultyName AS Professor in the view query indicates that Professor replaces FacultyName in the view table.
Refer to the following CREATE VIEW statement and view table.
What is a view table in database management?
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.
How do views help optimize database design for users?
- 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.
What is the function of a materialized view?
- It can improve performance by providing fast access to pre-computed data.
- A materialized view stores the result of a view query.
must be refreshed when the base table data changes
What is the primary advantage of using views to manage sensitive data?
Views can exclude sensitive columns, such as salary data, thereby providing access to only the necessary information for specific users or applications.
Why might view tables not allow insert, update, or delete operations?
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.
What is the purpose of the WITH CHECK OPTION clause in view creation?
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.
In a view created with a join, what are the tables in the FROM clause referred as?
They are known as base tables, which are the source tables from which the view draws its data.
What are the advantages of saving complex queries as views?
Views can encapsulate complex queries, saving time for users who need to run those queries repeatedly without having to write them out each time.
How is database performance related to using views?
- 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.
What does the CREATE VIEW statement do?
It creates a new view by specifying the view’s name, columns, and the SELECT statement that defines it.
Why might users prefer a view that joins tables, like Employee and Address?
- A view can present combined data in a single table format
- Views simply data access for users who need information from both tables.
What happens when a view is queried in SQL?
The database processes the view’s SELECT statement along with the user query, treating it as a single combined query against the base tables.
Can views improve query security and why?
Yes, by allowing access to specific data while keeping other data, possibly sensitive, hidden from the user.
How do views assist in simplifying complex database queries?
They encapsulate complex logic within a SELECT statement, allowing users to query the view directly without understanding the details.
What restriction applies to views when performing an INSERT operation?
An insert may be problematic for a view if:
- It the insert doesn’t include necessary primary keys
- Attempts to insert into aggregated columns.
Can a view be defined based on other views?
Yes, a view can be built using other views, though they are not considered base tables.
Describe how a view and a base table differ in data storage.
- Views do not store data; they only describe a way to retrieve data from base tables
- Base tables physically store data.
What is a join view?
SELECT DepartmentName, EmployeeName FROM Department INNER JOIN Employee ON DepartmentID = Employee.DepartmentID
A join view involves combining data from two tables
What role does a view play in data abstraction?
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.
How often must materialized views be refreshed?
They must be refreshed whenever the base table data changes, to ensure accuracy.
Why might databases disallow certain data modifications through views?
Because the modification might not map correctly back to the base tables, creating ambiguity or undefined behavior.
Why is the WITH CHECK OPTION useful?
It ensures data integrity by preventing inserts or updates that would make data invisible to the view.
Can you delete data directly from a view?
Generally, deletes are restricted from views due to potential ambiguity in corresponding changes to base tables.
How are views utilized to maintain data consistency?
By allowing only controlled access to data via the view, thus ensuring data access patterns don’t violate business rules.
What is the WITH CHEAK OPTION clause?
CREATE VIEW ViewName [ ( Column1, Column2, ... ) ] AS SelectStatement [WITH CHECK OPTION] ;
Disregard the brackets for the With CHEAK opt
Why would adding to SalesEmployee fail?
1. ID: 8520
1. Name: Jiho Chen
1. Department: 80
The new employee is inserted into Employee but is not in department 51 and therefore does not appear in the view table.
Write a statement to prevent inserts and updates which wouldn’t satisfy the view query WHERE clause.
CREATE VIEW SalesEmployee (ID, Name, Department)
AS SELECT *
FROM Employee
WHERE DepartmentCode = 51
WITH CHEAK OPTION;
