Transactions:
----------------

- Atomicity:

begin;
   transaction
commit;  --rollback



Constraints:
---------------

Integrity rules for the database.

Constraint types:

- Database level (Assertions)

- Table level:

     - Primary key
     - Foreign Key
     - Unique
     - Not Null
     - Check (attribute level or table level)


create table abc (
     vala   varchar(10) primary key
     , valb   int
) ;

create table xyz(
    id int 
    , val1  varchar(10) 
    , val2  varchar(10) not null
    , val3  int
    , val4  int
    , primary key (id)    --not null implicitly true
    , foreign key (val1) references abc(vala)
      on delete cascade
    , foreign key (val2) references def(vald)
    , unique (val2, val3)
    , check (val1 > 4)
    , check (val1 <> 0 or val2 < 5)
) ;

-- If any constraint violated: the transaction causing the change is
   aborted, all changes are rolled back.



drop table b;
drop table a;

create table a (id1 int primary key, id3 int) ;
create table b (id2 int primary key
     , id1 int not null
     , foreign key (id1) references a(id1)
       on delete set null
       on update cascade);

insert into a values(1, 41);
insert into a values(2, 42);
insert into a values(4, 44);

insert into b values(21, 1);
insert into b values(22, 1);
insert into b values(24, 2);



--> restrict (default): if a tuple references the updated/deleted tuple, then the update/delete is rolled back.

--> cascade: cascade the change























drop table if exists tmp ;
drop table if exists results ;
drop table if exists bakers;
drop table if exists episodes;

create table bakers(
      baker        varchar(10) primary key
      , fullname   varchar(100)
      , age        int    not null
      , occupation varchar(100)
      , hometown   varchar(100)
);

create table episodes (
    id               int  primary key
    , title          varchar(100)
    , firstaired     date
    , viewers7day  float   check(viewers7day > 0)
    , signature       varchar(100)
    , technical       varchar(100)
    , showstopper     varchar(100)
) ;


create table results (
      episodeid int
     , baker   varchar(100)
     , result   varchar(20)   
     , primary key (episodeid, baker)
     , foreign key (episodeid) references episodes(id)
       on delete cascade
       on update cascade
     , foreign key (baker) references bakers(baker)
       on delete cascade
       on update cascade
);

create table tmp (
     id int primary key
     , episodeid int
     , baker   varchar(100)
     , foreign key (episodeid, baker) references results(episodeid, baker)
       on delete cascade
       on update set null
) ;


insert into bakers values('Antony','Antony Amourdoux',30,'Banker','London');
insert into bakers values('Briony','Briony Williams',33 ,'Full-time parent','Bristol');
insert into bakers values('Dan','Dan Beasley-Harling',36 ,'Full-time parent','London');
insert into bakers values('Imelda','Imelda McCarron',33 ,'Countryside recreation officer','County Tyrone');
insert into bakers values('Jon','Jon Jenkins',47 ,'Blood courier','Newport');
insert into bakers values('Karen','Karen Wright',60 ,'In-store sampling assistant','Wakefield');
insert into bakers values('Kim-Joy','Kim-Joy Hewlett',27 ,'Mental health specialist','Leeds');
insert into bakers values('Luke','Luke Thompson',30 ,'Civil servant/house and techno DJ','Sheffield');
insert into bakers values('Manon','Manon Lagrève',26 ,'Software project manager','London');
insert into bakers values('Rahul','Rahul Mandal',30 ,'Research scientist','Rotherham');
insert into bakers values('Ruby','Ruby Bhogal',29 ,'Project manager','London');
insert into bakers values('Terry','Terry Hartill',56 ,'Retired air steward','West Midlands');


insert into episodes values(1,'Biscuits', date '2018-8-28', 9.55,'24 Regional Biscuits','8 Wagon Wheels','3D Biscuit Self-Portrait');
insert into episodes values(2,'Cakes', date '2018-9-4', 9.31,'16 Traybakes','Le Gâteau Vert','Chocolate Collar Cake');
insert into episodes values(3,'Bread', date '2018-9-11', 8.91,'12 Chelsea Buns','8 Non-Yeasted Garlic Naan Breads','Korovai');
insert into episodes values(4,'Desserts', date '2018-9-18', 8.88,'Meringue Roulade','Raspberry Blancmange with 12 Langues du Chat biscuits','Melting Chocolate Ball Dessert');
insert into episodes values(5,'Spice', date '2018-9-25', 8.67,'Ginger Cake','12 Ma''amoul','Spiced Biscuit Chandelier');
insert into episodes values(6,'Pastry', date '2018-10-2', 9.30,'12 Samosas','6 Puits D''amour','Shaped Banquet Pie');
insert into episodes values(7,'Vegan', date '2018-10-9', 9.54,'8 Savoury Vegan Tartlets','Vegan Tropical Fruit Pavlova','Vegan Celebratory Cake');
insert into episodes values(8,'Danish', date '2018-10-16', 9.69,'2 Smørrebrød','14 Æbleskiver','Kagemand/Kagekone');
insert into episodes values(9,'Pâtisserie (Semi-final)', date '2018-10-23', 9.50,'24 Madeleines','Torta Setteveli','Parisian Pâtisserie Window');
insert into episodes values(10,'Final', date '2018-10-30', 10.34,'12 Iced Doughnuts','6 Campfire Pita Breads','Landscape Dessert');

insert into results values(1,'Manon','star baker') ;
insert into results values(1,'Imelda','eliminated') ;
insert into results values(2,'Rahul','star baker') ;
insert into results values(2,'Luke','eliminated') ;
insert into results values(3,'Rahul','star baker') ;
insert into results values(3,'Antony','eliminated') ;
insert into results values(4,'Dan','star baker') ;
insert into results values(5,'Terry','eliminated') ;
insert into results values(5,'Kim-Joy','star baker') ;
insert into results values(5,'Karen','eliminated') ;
insert into results values(6,'Briony','star baker') ;
insert into results values(6,'Dan','eliminated') ;
insert into results values(7,'Kim-Joy','star baker') ;
insert into results values(7,'Jon','eliminated') ;
insert into results values(8,'Ruby','star baker') ;
insert into results values(8,'Manon','eliminated') ;
insert into results values(9,'Ruby','star baker') ;
insert into results values(9,'Briony','eliminated') ;

insert into tmp values(1, 5, 'Kim-Joy');
insert into tmp values(2, 5, 'Kim-Joy');
insert into tmp values(3, 8, 'Manon');












Transactions:

Isolation levels:
-------------------

    - dirty read  - read uncommitted
    
    - no dirty read  - read committed
    - repeatable read  
    - serializable

START TRANSACTION ;   --- or BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

COMMIT ;


drop table if exists a;

create table a (id int) ;

insert into a values(1);
insert into a values(2);

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

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










Procedural programming:
-------------------------


Programming language + SQL
----------------------



DB Server supported languages:
-------------------------------
Specific to the DBMS or standard

plpgsql



Existing programming languages with DB hooks:
-----------------------------------------------
Some standardized, some specialized

- involving pre-compilers (embedded SQL)

- involving modules/drivers

    Java -> JDBC
    Python -> db2api
    

Frameworks:
-------------
Take over DB programming

Django
Grails

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


--> connect to db
--> cursor (query runner object)

execute queries:

-> queries that return a single tuple: run/read
-> queries that return a multiple tuples:
          run/open stream/iterate over it/close

-> statements that do not return anything but change data
         run - check status - number of tuples impacted

input data into query /output: read data returned by query

error handling//status processing


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

Find the top 3 most frequent y for each x in table R(x,y,z).


     q: select x, count(distinct y) from r group by x;


     for tuples in q:
         find the top 3


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

CREATE FUNCTION sales_tax(subtotal real, state varchar) RETURNS real AS $$
DECLARE
   adjusted_subtotal real ;
BEGIN
   IF state = 'NY' THEN
       adjusted_subtotal = subtotal * 0.08 ;
   ELSIF state = 'DE' THEN
       adjusted_subtotal = subtotal ;
   ELSE
       adjusted_subtotal = subtotal * 0.06;
   END IF ;
   RETURN adjusted_subtotal ;
END ;
$$ LANGUAGE plpgsql ;


















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


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













































































































Transactions:
----------------

- Atomicity:

begin;
   transaction
commit;  --rollback



Constraints:
---------------

Integrity rules for the database.

Constraint types:

- Database level (Assertions)

- Table level:

         - Primary key
	 - Foreign Key
	 - Unique
	 - Not Null
	 - Check (attribute level or table level)


create table abc (
     vala   varchar(10) primary key
     , valb   int
) ;

create table xyz(
    id int 
    , val1  varchar(10) 
    , val2  varchar(10) not null
    , val3  int
    , val4  int
    , primary key (id)    --not null implicitly true
    , foreign key (val1) references abc(vala)
      on delete cascade
    , foreign key (val2) references def(vald)
    , unique (val2, val3)
    , check (val1 > 4)
    , check (val1 <> 0 or val2 < 5)
) ;

-- If any constraint violated: the transaction causing the change is
   aborted, all changes are rolled back.



drop table b;
drop table a;

create table a (id1 int primary key, id3 int) ;
create table b (id2 int primary key
     , id1 int not null
     , foreign key (id1) references a(id1)
       on delete set null
       on update cascade);

insert into a values(1, 41);
insert into a values(2, 42);
insert into a values(4, 44);

insert into b values(21, 1);
insert into b values(22, 1);
insert into b values(24, 2);



--> restrict (default): if a tuple references the updated/deleted tuple, then the update/delete is rolled back.

--> cascade: cascade the change























drop table if exists tmp ;
drop table if exists results ;
drop table if exists bakers;
drop table if exists episodes;

create table bakers(
      baker        varchar(10) primary key
      , fullname   varchar(100)
      , age        int    not null
      , occupation varchar(100)
      , hometown   varchar(100)
);

create table episodes (
    id               int  primary key
    , title          varchar(100)
    , firstaired     date
    , viewers7day  float   check(viewers7day > 0)
    , signature       varchar(100)
    , technical       varchar(100)
    , showstopper     varchar(100)
) ;


create table results (
      episodeid int
     , baker   varchar(100)
     , result   varchar(20)	  
     , primary key (episodeid, baker)
     , foreign key (episodeid) references episodes(id)
       on delete cascade
       on update cascade
     , foreign key (baker) references bakers(baker)
       on delete cascade
       on update cascade
);

create table tmp (
     id int primary key
     , episodeid int
     , baker   varchar(100)
     , foreign key (episodeid, baker) references results(episodeid, baker)
       on delete cascade
       on update set null
) ;


insert into bakers values('Antony','Antony Amourdoux',30,'Banker','London');
insert into bakers values('Briony','Briony Williams',33 ,'Full-time parent','Bristol');
insert into bakers values('Dan','Dan Beasley-Harling',36 ,'Full-time parent','London');
insert into bakers values('Imelda','Imelda McCarron',33 ,'Countryside recreation officer','County Tyrone');
insert into bakers values('Jon','Jon Jenkins',47 ,'Blood courier','Newport');
insert into bakers values('Karen','Karen Wright',60 ,'In-store sampling assistant','Wakefield');
insert into bakers values('Kim-Joy','Kim-Joy Hewlett',27 ,'Mental health specialist','Leeds');
insert into bakers values('Luke','Luke Thompson',30 ,'Civil servant/house and techno DJ','Sheffield');
insert into bakers values('Manon','Manon Lagrève',26 ,'Software project manager','London');
insert into bakers values('Rahul','Rahul Mandal',30 ,'Research scientist','Rotherham');
insert into bakers values('Ruby','Ruby Bhogal',29 ,'Project manager','London');
insert into bakers values('Terry','Terry Hartill',56 ,'Retired air steward','West Midlands');


insert into episodes values(1,'Biscuits', date '2018-8-28', 9.55,'24 Regional Biscuits','8 Wagon Wheels','3D Biscuit Self-Portrait');
insert into episodes values(2,'Cakes', date '2018-9-4', 9.31,'16 Traybakes','Le Gâteau Vert','Chocolate Collar Cake');
insert into episodes values(3,'Bread', date '2018-9-11', 8.91,'12 Chelsea Buns','8 Non-Yeasted Garlic Naan Breads','Korovai');
insert into episodes values(4,'Desserts', date '2018-9-18', 8.88,'Meringue Roulade','Raspberry Blancmange with 12 Langues du Chat biscuits','Melting Chocolate Ball Dessert');
insert into episodes values(5,'Spice', date '2018-9-25', 8.67,'Ginger Cake','12 Ma''amoul','Spiced Biscuit Chandelier');
insert into episodes values(6,'Pastry', date '2018-10-2', 9.30,'12 Samosas','6 Puits D''amour','Shaped Banquet Pie');
insert into episodes values(7,'Vegan', date '2018-10-9', 9.54,'8 Savoury Vegan Tartlets','Vegan Tropical Fruit Pavlova','Vegan Celebratory Cake');
insert into episodes values(8,'Danish', date '2018-10-16', 9.69,'2 Smørrebrød','14 Æbleskiver','Kagemand/Kagekone');
insert into episodes values(9,'Pâtisserie (Semi-final)', date '2018-10-23', 9.50,'24 Madeleines','Torta Setteveli','Parisian Pâtisserie Window');
insert into episodes values(10,'Final', date '2018-10-30', 10.34,'12 Iced Doughnuts','6 Campfire Pita Breads','Landscape Dessert');

insert into results values(1,'Manon','star baker') ;
insert into results values(1,'Imelda','eliminated') ;
insert into results values(2,'Rahul','star baker') ;
insert into results values(2,'Luke','eliminated') ;
insert into results values(3,'Rahul','star baker') ;
insert into results values(3,'Antony','eliminated') ;
insert into results values(4,'Dan','star baker') ;
insert into results values(5,'Terry','eliminated') ;
insert into results values(5,'Kim-Joy','star baker') ;
insert into results values(5,'Karen','eliminated') ;
insert into results values(6,'Briony','star baker') ;
insert into results values(6,'Dan','eliminated') ;
insert into results values(7,'Kim-Joy','star baker') ;
insert into results values(7,'Jon','eliminated') ;
insert into results values(8,'Ruby','star baker') ;
insert into results values(8,'Manon','eliminated') ;
insert into results values(9,'Ruby','star baker') ;
insert into results values(9,'Briony','eliminated') ;

insert into tmp values(1, 5, 'Kim-Joy');
insert into tmp values(2, 5, 'Kim-Joy');
insert into tmp values(3, 8, 'Manon');












Transactions:

Isolation levels:
-------------------

    - dirty read  - read uncommitted
    
    - no dirty read  - read committed
    - repeatable read  
    - serializable

START TRANSACTION ;   --- or BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

COMMIT ;


drop table if exists a;

create table a (id int) ;

insert into a values(1);
insert into a values(2);

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

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










Procedural programming:
-------------------------


Programming language + SQL
----------------------



DB Server supported languages:
-------------------------------
Specific to the DBMS or standard

plpgsql



Existing programming languages with DB hooks:
-----------------------------------------------
Some standardized, some specialized

- involving pre-compilers (embedded SQL)

- involving modules/drivers

    Java -> JDBC
    Python -> db2api
    

Frameworks:
-------------
Take over DB programming

Django
Grails

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


--> connect to db
--> cursor (query runner object)

execute queries:

-> queries that return a single tuple: run/read
-> queries that return a multiple tuples:
          run/open stream/iterate over it/close

-> statements that do not return anything but change data
         run - check status - number of tuples impacted

input data into query /output: read data returned by query

error handling//status processing


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

Find the top 3 most frequent y for each x in table R(x,y,z).


     q: select x, count(distinct y) from r group by x;


     for tuples in q:
         find the top 3


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

CREATE FUNCTION sales_tax(subtotal real, state varchar) RETURNS real AS $$
DECLARE
   adjusted_subtotal real ;
BEGIN
   IF state = 'NY' THEN
       adjusted_subtotal = subtotal * 0.08 ;
   ELSIF state = 'DE' THEN
       adjusted_subtotal = subtotal ;
   ELSE
       adjusted_subtotal = subtotal * 0.06;
   END IF ;
   RETURN adjusted_subtotal ;
END ;
$$ LANGUAGE plpgsql ;


















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


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