%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -------------------------------------------------------------------------------------------------------------------------------
%        TUPLE RELATIONAL CALCULUS (TRC)
% -------------------------------------------------------------------------------------------------------------------------------
%                        Web Page Enterprise
%
%  webpage (webID, webTitle, url, base, hits)
%       primary key (webID)
%  httpLink (sourceWebID, targetWebID)
%       primary key (sourceWebID, targetWebID)
%       foreign key (sourceWebID) references webpage(webID)
%       foreign key (targetWebID) references webpage(webID)
%  graphic (gID, gName, gType, gLocation)
%       primary key (gID)
%  display (webID, gID)
%       primary key (webID, gID)
%       foreign key (webID) references webpage(webID)
%       foreign key (gID) references graphic(gID)
%  courseware (cID, cDescription, ftpLocation, category)
%       primary key (cID)
%  ftpLink (webID, cID)
%       primary key (webID, cID)
%       foreign key (webID) references webpage(webID)
%       foreign key (cID) references courseware(cID)
%
%--------------------------------------------------------------------------------------------------------------------------------
% Q1.1	Which pages contain (ftp links to) the class notes?
% 	(webID, webTitle, url, base)

trc1 :=
{ W.webID, W.webTitle, W.url, W.base | (exists C, F)
	(courseware(C) and C.category = 'N' and
	 ftpLink(F) and F.cID = C.cID and
	 webpage(W) and W.webID = F.webID ) };

% -----------------------------------------------------------------------
% Q1.2	Which pages display graphics having the name 'asulogo'?
%	(webID, webTitle, url, base)

trc2 :=
{ W.webID, W.webTitle, W.url, W.base | (exists G,D)
	(graphic(G) and G.gName = 'asulogo' and
       display(D) and D.gID = G.gID and
	 webpage(W) and W.webID = D.webID) };

%------------------------------------------------------------------------
% Q1.3 	Which pages do not display any graphics?
%	(webID, webTitle, url, base)

trc3 :=
{ W.webID, W.webTitle, W.url, W.base | 
	webpage(W) and 
	not (exists D) (display(D) and D.webID = W.webID) };

%------------------------------------------------------------------------
% Q1.4	Which pages use 'gif' graphics but not 'jpg' graphics?
%	(webID, webTitle, url, base)


gifWebIDs := 
{ D.webID | display(D) and (exists G) 
	(graphic(G) and G.gType = 'gif' and G.gID = D.gID) };

jpgWebIDs := 
{ D.webID | display(D) and (exists G) 
	(graphic(G) and G.gType = 'jpg' and G.gID = D.gID) };

trc4 :=
{ W.webID, W.webTitle, W.url, W.base | 
	webpage(W) and
	(exists G) (gifWebIDs(G) and G.webID = W.webID and
	not (exists J) (jpgWebIDs(J) and J.webID = W.webID) ) };

%------------------------------------------------------------------------
% Q1.5	Which pages contain more than one (ftp) link to courseware?
%	(webID, webTitle, url, base, hits)

trc5 := 
{ W.webID, W.webTitle, W.url, W.base | 
	webpage (W) and 
	(exists L)(ftpLink(L) and L.webID = W.webID and
	(exists F)(ftpLink(F) and F.webID = L.webID and F.cID <> L.cID)) };

%------------------------------------------------------------------------
% Q1.6	Which pages contain only one (http) link to another web page?
%	(webID, webTitle, url, base)	

trc6 := 
{ W.webID, W.webTitle, W.url, W.base | 
	webpage (W) and 
	(exists L)(httpLink(L) and L.sourceWebID = W.webID and
	not (exists H) 
	    (httpLink(H) and H.sourceWebID = L.sourceWebID and
	     H.targetWebID <> L.targetWebID) ) };


		
%------------------------------------------------------------------------
% Q1.7	Which pages have the most hits?
%	(webID, webTitle, url, base, hits)

trc7 := 
{ W.webID, W.webTitle, W.url, W.base, W.hits | 
	webpage(W) and
	not (exists P)(webpage(P) and W.hits < P.hits) };


%------------------------------------------------------------------------
% Q1.8 	Which pages contain (ftp) links to all courseware in the 
% 	 publications category?
%	(webID, webTitle, url, base)

pubCIDs := { C.cID | courseware(C) and C.category = 'P' };

trc8 := 
{ W.webID, W.webTitle, W.url, W.base | 
	webpage(W) and
	(exists P,F)(pubCIDs(P) and ftpLink(F) and W.webID = F.webID and F.cID = P.cID) and
	not (exists C)(pubCIDs(C) and 
		not (exists L)(ftpLink(L) and W.webID = L.webID and L.cID = C.cID) ) };

%------------------------------------------------------------------------
% Q1.9  	For each webpage and graphic type, give the number of graphics 
%       	of that type displayed on that page. Display your results in 
%       	ascending order on webID, and within that, in ascending order 
%       	on graphic type (gType).
%	(webID, webTitle, url, base, gType, count)
%
%	Not applicable
%
%------------------------------------------------------------------------
% Q1.10	For each group of pages belonging to a base, 
%	give the average number of hits, the minimum number of hits in that group, 
%	the maximum number of hits in that group and the sum of hits for that group. 
%	Display your results in descending order on average number of hits.
%	(webID, webTitle, url, avgHits, minHits, maxHits, sumHits)
%
% 	Not applicable
%
%-----------------------------End Web Page Enterprise---------------------------