Computer Science 380:

Principles of Database Systems

Chapter 4

Gregory M. Kapfhammer


creative commons licensed ( BY-NC-SA ) flickr photo shared by danmachold

Deepen Understanding of SQL Concepts

Relational Join

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!

Relational Views

What is a view?

How would I create a view?

How do I use a view?

What is a materialized view?

View Update

Creating a view

Materializing a view

Modifying a view

When is a view updatable?

What are the challenges?

Normally, avoid modifying the view

Materializing Views

Store a view in the RDBMS

When should we change the view?

Why do we want to materialize?

What are the trade-offs?

DML Statements

SQL delete

SQL insert

SQL update

General form and specific details

Integrity Constraints

NOT NULL

UNIQUE

CHECK

FOREIGN and PRIMARY KEY

In HSQLDB a CHECK can call a Java function!

What are the trade-offs for CHECK constraints?

Assertion Checking

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

Referential Integrity

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?

Constraint Enforcement

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?

Enforcement Options

#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?

SQL Data Types

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)