14 SQL System Constructs

Database management systems are software applications designed for very efficient manipulation of data that target a relatively small number of operations. Since they are also defined to operate over a fairly restrictive data model, they are extremely useful in situations where data consistency and safety are required. Here are some examples of capabilities found in DBMS that help in that regard:

• Transactions
  • A transaction is a sequence of queries and update statements executed as a single unit
  • For example, transferring money from one account to another
    • Both the deduction from one account and credit to the other account should happen, or neither should
• Triggers
  • A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database
• Integrity Constraints
  • Predicates on the database that must always hold
  • Key Constraints: Specifiying something is a primary key or unique

14.1 SQL as a Data Definition Language

The Structured Query Language (SQL) is both a Data Definition Language and a Data Manipulation Language

CREATE TABLE <name> ( <field> <domain>, ... )

INSERT INTO <name> (<field names>) VALUES (<field values>)

DELETE FROM <name> WHERE <condition>

UPDATE <name> SET <field name> = <value> WHERE <condition>

SELECT <fields> FROM <name> WHERE <condition>

We can create tables and specify primary key attributes which enforce integrity constraints at the system level

CREATE TABLE customer (
  ssn CHAR(9) PRIMARY KEY, 
  cname CHAR(15), address CHAR(30), city CHAR(10), 
  UNIQUE (cname, address, city)); 

Attribute constraints: Constraints on the values of attributes

bname char(15) not null

balance int not null, check (balance >= 0)

• Referential integrity: prevent dangling tuples

CREATE TABLE  branch(bname CHAR(15) PRIMARY KEY, ...);
CREATE TABLE loan(..., FOREIGN KEY bname REFERENCES branch);

• Can tell the system what to do if a referenced tuple is being deleted

• Global Constraints
  • Single-table

CREATE TABLE branch (...,
  bcity  CHAR(15), 
  assets INT, 
  CHECK (NOT(bcity = ‘Bkln’) OR assets > 5M))

• Multi-table

CREATE ASSERTION loan-constraint
  CHECK (NOT EXISTS (
    SELECT   * 
    FROM loan AS L
    WHERE  NOT EXISTS(
      SELECT   *
      FROM borrower B, depositor D, account A
      WHERE B.cname = D.cname  AND
        D.acct_no = A.acct_no  AND
        L.lno  = B.lno)))

14.2 Set Operations and Comparisons

• Set operations

select name 
from movieExec
union/intersect/minus
select name 
from movieStar

• Set Comparisons

select *
from movies
where year in [1990, 1995, 2000];

select *
from movies
where year not in (
  select extract(year from birthdate) 
  from MovieStar
);

14.3 Views

create view DisneyMovies
select *
from movie m
where m.studioname = 'disney';

Can use it in any place where a tablename is used. Views are used quite extensively to: (1) simplify queries, (2) hide data (by giving users access only to specific views). Views may be materialized or not.

14.4 NULLs

Value of any attribute can be NULL if value is unknown, or it is not applicable, or hidden, etc. It can lead to counterintuitive behavior. For example, the following query does not return movies where length = NULL

select * from movies where length >= 120 or length <= 120`

Aggregate operations can be especially tricky when NULLs are present.