Please enable JavaScript.
Coggle requires JavaScript to display documents.
Zybooks 3 - Coggle Diagram
Zybooks 3
View Tables
-
View tables where Views restructure table columns and data types without changes to the underlying database design
View table is a table name associated with a SELECT statement, called the view query. The CREATE VIEW statement creates a view table and specifies the view name, query and optionally, column names. If column names are not specified column names are the same as in the view query result table
Query Views
-
Unlike base table data, view table data is not normalyl stored
Instead, when a view table appears in an SQL statement, the view query is merged with the SQL query. The database executes the merged query against base tables
In some databases, view data can be stored.
-
Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed.
To avoid the overhead of refreshing view, MySQL and many other databases do not support materialized views
Advantages of views
-
-
Save optimized queries
Often, the same result table can be generated with equivalent SELECT statements.
Although the results of equivalent statements are the same, performance may vary.
To ensure fast execution, the optimal statement can be saved as a view and distributed to database users
Updating Views
View tables are commonly used in SELECT statements. Using view in INSERT, UPDATE and DELETE statements is problematic
examples
Primary keys
If a base table primary key does not appear in a view, an insert to the view generates a NULL primary key value. Since primary keys may not be NULL, the insert is not allowed.
Aggretated values
A view query may contain aggregate functions such as AVG() or SUM(). One aggregate value corresponds to many base table values.
An update or insert to the view may create a new aggregate value, which must be converted to many base table values
The conversion is undefined, so the insert or update is not allowed
Join views
In a join view, foreign keys of one base table may match primary keys of another.
A delete from a view might delete foreign key rows only, or primary key rows only, or both the primary and foreign key rows.
-
WITH CHECK OPTION clause
-
Looks like it fails, where it has been changed
To stop confusion, you can do WITH CHECK OPTION which rejects inserts and updates that don't satisfy the view query WHERE clause
Join Queries
JOIN is a SELECT statement that combines data from two tables, known as left and right table.
-
-
-
-
-
Relational Algebra
-
-
-
Union, Intersect and Difference
Compatible tables
-
-
Union, intersect, and difference operate on compatible tables and, collectively, are called set operations
-
-
-
MySQL
Doesn't support all these, you have to do it in a special workaround way
Rename and Aggregate
-
Aggregate operaions
applies aggregate functions like SUM(), AVG(), MIN(), and MAX()
-
Query Optimization
-
Query Optimizer
converts an SQL query into a sequence of low-level database actions, called the query execution Plan
-
-
-
-
Subqueries
-
Correlated Subqueries
-
If a column name in the correlated subquery is identical to a column name in the outer query, the TableName.COlumnName differentiates the columns.
-
-
Flattening subqueries
-
Most databases optimize a subquery and outer query separatly, whereas joins are optimized in one pass
-
-
Steps
-
- Add INNER JOIN clauses for each subquery table
- Move comparisons between subquery and outer query columns to ON clauses
- Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses
- If necessary, remove duplicate rows with SELECT DISTINCT
-
Equijoins, Self-Joins and Cross-Joins
-
Self-Joins
-
Can compare any columns of a table, as long as the columns have comparable data types.
If a foreign key and the referenced primary key are in the same table, a self-join commonly compares those columns.
-
-
-
-