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

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

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
  • Can tell the system what to do if a referenced tuple is being deleted

  • Global Constraints
    • Single-table
  • Multi-table


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

Aggregate operations can be especially tricky when NULLs are present.