Lecture 14
---------------------

Rainbow grades: only partially visible. Could not yet figure out how
to code in optional homeworks.

Deadline extension for hw#5: monday midnight

   1. Do not put in 'Query 1' etc. in your hw solutions. I only put them for
   your own benefit.

   2. Use ASCII

   3. Make sure your queries run, do not use non-sql text


------------------------------


No isolation.....
A=50
B=50

     T1                 T2
     Read A
     A=A-10
                        Read A
			A=A-10

                        Read B
			B=B+10

                        Write A  -40
			Write B  -60
     Read B
     B=B+10
     Write A -40
     Write B -70
     

  A=40 // B=70 -- A+B=100   A+B=110

-----------

levels of isolation....


SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---------------

drop table d;
drop table c;

drop table a;
drop table b;

create table a(id int primary key, val varchar(10)) ;

create table b(id int primary key, val varchar(10)) ;

create table c(
     aid int
     , bid int 
     , val varchar(10)
     , primary key (aid, bid)
     , foreign key (aid) references a(id)
       on delete cascade
     , foreign key (bid) references b(id)
       on delete set null
) ;       

create table d(
    id int primary key
    , aid int
    , bid int
    , foreign key (aid, bid) references c(aid,bid)
      on update cascade on delete set null);

insert into a values(1,'a');
insert into a values(2,'b');
insert into b values(10,NULL);
insert into b values(20,'d');
insert into c values(1,20,'e');
insert into c values(2,20,'f');
insert into d values(1,1,20);
insert into d values(2,1,20);


------------------------------------

Procedural Programming!
-----------------------

Query complexity  ---- Amount of data transmitted from db server to a program


--> connect to a db
--> run a query
--> process results of query
--> status/error handling