CHum 240

Database Integrity

Data integrity -- it is secure from unexpected changes;
nothing changes, dissapears or appears without explicit
orders.

Referential integrity -- proper relationships are
automatically maintained when those relationships
are properly defined.

Database at rest reflects a state of without 
contradictions.

E.g. total sales * sale price = total assets


ACID Test

Atomic -- Nothing is done unless everything can be done.
Rollback any partial changes. Commit logs.

Consistent -- Changes occur as expected without unnecessary
side-effects. Also, all constraints are enforced so that
database works as expected.

Isolated -- A users is protected from seeing intermediate 
results of changes made by other users. Can still get
unexpected results.

Durable -- Commited changes are permanent despite any 
system failures.


Constraints

PRIMARY KEY

Unique, non-null values only.

UNIQUE

Table-wide unique or NULL values only.


DEFAULT

Provides a reasonable default value when none is given.
Can be used with 'NOT NULL' to make in possible to insert
a row without specifing a value for that column.


CHECK 

Tests a value for valid form or range.

  create table statesales (
    stateid char(2) check (abbr >= 'aa' and abbr <= 'zz'),
    sales int check (sales > 0),
    type varchar(8) check (type in ('book','disc','e-book'))
  );

Can also use a pattern like this:

    userid char(4) check (userid like '[a-z][0-9][0-9][1-9]')


Foreign Keys / REFERENCES 

Can't insert unless corresponding value exists in other
table.

    userid char(4) references users(userid),

Columns that are keys in other tables are foreign keys.

A table that other tables reference cannot be deleted.

A row with a unique key referenced by another table row
cannot be deleted.

What about deletion?

   userid integer primary key references user(userid) on delete restrict,
   userid integer primary key references user(userid) on delete cascade,
   userid integer primary key references user(userid) on delete set null,
  

Triggers 


Triggers can be used like CHECK constraints, or they can execute
SQL statements on certain operations in order to maintain
referential integrity.

  create trigger usernamecopy
  on user
  for insert
  as
    update usernames
    set name = inserted.name
    where userid = inserted.userid;

  create trigger usernamedelete
  on user
  for delete
  as
    delete from usernames
    where name = inserted.name;
  


Transactions

BEGIN TRANSACTION

COMMIT TRANSACTION

ROLLBACK TRANSACTION


Stored procedures

High-end SQL servers support a programming language specifically
designed for database management. 

Stored procedures can be attached to triggers or executed by
queries.

Allow for optimization and business logic.


- - - -

Copyright ©2007 Brigham Young University