Databases - Test 2, Quiz 4 - SQL (views, indexes)
Define SQL indices
An access path from a SQL base relation to a stored file
In regards to views, what does an update operation entail?
Changing something in regards to the base relations the view is based on.
A base relation can have at most ____, but any number of _____
Clustering indexes, non-clustering indexes
Do a practice problem involving CREATE VIEW
Cool.
Do a practice problem involving queries on views.
Cool.
Do a practice problem updating a view.
Cool.
Write out the CREATE INDEX statement
Cool.
Write out the command to drop an index.
Cool.
Write out the command to remove a view.
Cool.
A SQL base relation corresponds to
a stored file
Define unambiguous, in the context of views and updates.
If only one update on the base relations can accomplish the desired update on the view.
Define view, in the context of SQL.
Single virtual table derived from other tables
If the base tables a view is defined on are changed, what should occur?
The DBMS should update the view.
What must be considered when considering how to update a view?
What the user most likely intended to accomplish with the update operation.
Can a view update be enacted through several different possible operations on the base relations?
Yes.
Can an index can be created on one or more attributes?
Yes.
In regards to views and updates, we cannot guarantee
any view can be updated
Views defined on aggregate functions ____
are not update-able
The other tables that a view could be derived from are
base tables or previously defined views
A clustering and non-unique index in SQL is similar to the ____
clustering index
A view does not necessarily ____, which limits _____
exist in physical form; the possible update operations
Each index is given a ____
index name
In regards to views, the DBMS is responsible for
keeping the view always up to date if the base tables the view is defined on are modified
Views defined on multiple tables using joins are generally
not update-bable
A clustering and unique index in SQL is similar to the ___
primary index
A non-clustering index is similar to the ___
secondary index
Views can also be used as a
security and authorization mechanism
A view can be defined to _____
simplify frequently occurring events
The view attribute names can be inherited from ____
the attribute names of the tables in the defining query
A view with a single defining table is update-able if
the view attributes contain the primary key
If a view can be mapped to more than one update on the base relations,
we must have a procedure for choosing the the desired update
The view is realized when not at the time of view definition, but ___
when specifying a query on the view
The most common implementation of indexes is the ___
B tree