----------------------------------------------------------------------
-- Demo for individual and general tracker.
-- Follows example in
--   C.J. Date, An Introduction to Database Systems, 8th edition, 2004
----------------------------------------------------------------------

create table basedata (name varchar primary key, sex char(1), children integer, job varchar, salary integer, tax integer, audits integer);

insert into basedata (name, sex, children, job, salary, tax, audits) values
	('Alf', 'm', 3, 'Programmer', 50000, 10000, 3),
	('Bea', 'f', 2, 'Physician', 130000, 10000, 0),
	('Cyn', 'f', 0, 'Programmer', 56000, 18000, 1),
	('Dee', 'f', 2, 'Builder', 60000, 12000, 1),
	('Ern', 'm', 2, 'Clerk', 44000, 4000, 0),
	('Fay', 'f', 1, 'Artist', 30000, 0, 0),
	('Guy', 'm', 0, 'Lawyer', 190000, 0, 0),
	('Hal', 'm', 3, 'Homemaker', 44000, 2000, 0),
	('Ivy', 'f', 4, 'Programmer', 64000, 10000, 1),
	('Joy', 'f', 1, 'Programmer', 60000, 20000, 1);


create view stats as (select sex, children, job, salary, tax, audits from basedata);

-- aggregate queries over stats
-- 
-- attacker knows:
-- a) Alf is in the database
-- b) Alf is Programmer
-- c) Alf is male

-- Individual tracker for Alf: sex='m' and job='programmer'

-- 1) no restriction on result cardinality c 

select count(salary) from stats where sex='m' and job='Programmer';

select sum(salary) from stats where sex='m' and job='Programmer';

-- 2) only queries with at least c >=b result tuples (lower bound b=2)

select count(salary) from stats;
select count(salary) from stats where not (sex='m' and job='Programmer');

select sum(salary) from stats;
select sum(salary) from stats where not (sex='m' and job='Programmer');

-- 3) only queries with b <= c <= n - b (upper and lower bound, b=2)

-- verify that Alf is the only male programmer
select count(salary) from stats where sex='m';
select count(salary) from stats where sex='m' and not job='Programmer';

-- get salary of Alf
select sum(salary) from stats where sex='m';
select sum(salary) from stats where sex='m' and not job='Programmer';

-- General tracker: audits=0

-- Step 1: guess audit=0 is a tracker

-- Step 2: verify tracker
select count(salary) from stats where audits = 0;
select count(salary) from stats where not audits = 0;

-- Step 3: cardinality of forbitten query

select count(salary) from stats where (sex='m' and job='Programmer') or audits=0;
select count(salary) from stats where (sex='m' and job='Programmer') or not audits=0;

-- Step 4: total sum of salaries

select sum(salary) from stats where audits=0; -- this query would be enough since audits!=0 for Alf
select sum(salary) from stats where not audits=0;

-- Step 5: total sum of salaries + Alf's salary

select sum(salary) from stats where (sex='m' and job='Programmer') or audits=0; -- this query would be enough
select sum(salary) from stats where (sex='m' and job='Programmer') or not audits=0;

