Gregory M. Kapfhammer
Connect two tables together
Inputs: Figures 4.1 and 4.2
Output: Figure 4.3
More than two inputs possible!
Inner, outer, natural, and other options
Make sure that you understand the intuition!
What is a view?
How would I create a view?
How do I use a view?
What is a materialized view?
Creating a view
Materializing a view
Modifying a view
When is a view updatable?
What are the challenges?
Normally, avoid modifying the view
Store a view in the RDBMS
When should we change the view?
Why do we want to materialize?
What are the trade-offs?
General form and specific details
FOREIGN and PRIMARY KEY
In HSQLDB a CHECK can call a Java function!
What are the trade-offs for CHECK constraints?
Express a condition that we want the database to satisfy
#1: Create an assertion
#2: Is it initially valid?
#3: Check the assertion at every state manipulation
Trade-off: Expressivity vs. Checking Cost
A FOREIGN KEY between Instructor and Department
What if a DELETE statement leads to a violation?
Maybe the DBMS should allow "cascading"?
ON DELETE CASCADE or ON UPDATE CASCADE
What are the risks associated with these options?
Re-focus our attention on transaction processing
A transaction can contain many steps
What if a constraint is violated by a step?
What options can the DBMS consider?
#1: Check constraints immediately
#2: Constraints initially deferred
#3: Constraints are deferrable
What are the trade-offs associated with these options?
How would we determine which option is best?
Date, time, and timestamp
What are the similarities and differences?
Also, there are "large object" types
CLOB: Character large object
BLOB: Binary large object
book_review clob(10 KB)
image blob(10 MB)