-- DEFINING VIEWS


-- view about fragments: 

-----idf - fragment ID; 

-----nrrepl - number of replications of the fragment;

-----sites - list of the sites where the fragment is stored;

CREATE OR REPLACE VIEW INF_FRAGMENTS_SITES AS

  SELECT idf,count(*) AS nrrepl,listagg(ids,', ') WITHIN GROUP (order by ids) AS sites

 	FROM memf GROUP BY idf ORDER BY 1;



-- view about sites:

-----ids - site ID; 

-----nrfragm - number of fragments stored on the site;

-----fragments - list of the fragments stored on the site;

CREATE OR REPLACE VIEW INF_SITES_FRAGMENTS AS

  SELECT ids,count(*) as nrfragm,listagg(idf,',') WITHIN GROUP (order by idf) as fragments

  	FROM memf GROUP BY ids ORDER BY 1;



-- view about queries distributed on sites:

-----ids - site ID; 

-----nrqueries - number of queries processed on the site;

-----queries - list of the queries processed on the site;

CREATE OR REPLACE VIEW INF_SITES_QUERIES as

  SELECT ids,count(*) as nrqueries,listagg(idq,', ') WITHIN GROUP (order by idq) as queries

	FROM queries GROUP BY ids ORDER BY 1;

-- view about sites distributed on queries:

-----idq - query ID; 

-----nrsites - number of sites where the query is processed;

-----sites - list of the sites where the query is processed;

CREATE OR REPLACE VIEW INF_QUERIES_SITES as

  SELECT idq,count(*) as nrsites,listagg(ids,', ') WITHIN GROUP (order by ids) as sites

	FROM queries GROUP BY idq ORDER BY 1;


-- view about query processing (evaluation) distributed on queries:

-----idq - query ID; 

-----nrfragm - number of fragments used by the query; 

-----fragments - list of the fragments used by the query; 

CREATE OR REPLACE VIEW INF_QUERIES_FRAGMENTS as

  SELECT idq,count(*) as nrfragm,listagg(idf,', ') WITHIN GROUP (order by idf) as fragments

	FROM evaluation GROUP BY idq ORDER BY 1;


-- view about query evaluation distributed on fragments:

-----idf - fragment ID; 

-----nrqueries - number of queries where the fragment is used;

-----queries- list of the queries where the fragment is used;

CREATE OR REPLACE VIEW INF_FRAGMENTS_QUERIES as
  SELECT idf,count(*) as nrqueries,listagg (idq,', ') WITHIN GROUP (order by idq) as queries

 	FROM evaluation GROUP BY idf ORDER BY 1;