%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -------------------------------------------------------------------------------------------------------------------------------
%        DOMAIN RELATIONAL CALCULUS (DRC)
% -------------------------------------------------------------------------------------------------------------------------------
%                        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)

drc1(webID, webTitle, url, base) :=
{ WebID, WebTitle, URL, Base | (exists CID)
	(courseware(CID, _, _, 'N') and
	 ftpLink(WebID, CID) and
	 webpage(WebID, WebTitle, URL, Base,_) ) };

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

drc2(webID, webTitle, url, base) := 
{ WebID, WebTitle, URL, Base | (exists GID)
	(graphic(GID,'asulogo',_,_) and 
       display(WebID, GID) and
	 webpage(WebID, WebTitle, URL, Base,_) ) };

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

drc3(webID, webTitle, url, base) :=
{ WebID, WebTitle, URL, Base |
	webpage(WebID, WebTitle, URL, Base,_) and
	not display(WebID, _) };

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

gifWebIDs := 
{ WebID | (exists GID) (graphic(GID, _, 'gif', _) and display(WebID, GID) ) };

jpgWebIDs := 
{ WebID | (exists GID) (graphic(GID, _, 'jpg', _) and display(WebID, GID) ) };

drc4(webID, webTitle, url, base) := 
    { WebID, WebTitle, URL, Base | 
	webpage(WebID, WebTitle, URL, Base, _) and
	gifWebIDs(WebID) and not jpgWebIDs(WebID) };

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

drc5(webID, webTitle, url, base) :=
{WebID, WebTitle, URL, Base | (exists CID) 
	(webpage(WebID, WebTitle, URL, Base, _) and 
	 ftpLink(WebID, CID) and
	 (exists AnotherCID) 
		(ftpLink(WebID, AnotherCID) and
		 AnotherCID <> CID)) };


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

drc6(webID, webTitle, url, base) :=
{WebID, WebTitle, URL, Base | (exists TargetWebID) 
	(webpage(WebID, WebTitle, URL, Base, _) and 
	 httpLink(WebID, TargetWebID) and
	 not (exists AnotherTarget) 
		(httpLink(WebID, AnotherTarget) and
		 AnotherTarget <> TargetWebID)) };
		
%------------------------------------------------------------------------
% Q1.7	Which pages have the most hits?
%	(webID, webTitle, url, base, hits)

drc7(webID, webTitle, url, base, hits) := 
{WebID, WebTitle, URL, Base, Hits | 
	webpage(WebID,WebTitle, URL, Base, Hits) and
	not (exists H) (webpage(_,_,_,_,H) and H > Hits) };


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

pubCIDs := { CID | courseware (CID,_,_,'P') };

drc8(webID, webTitle, url, base) := 
{ WebID, WebTitle, URL, Base | 
	webpage (WebID, WebTitle, URL, Base, _) and
	(exists C)(pubCIDs(C) and ftpLink(WebID,C)) and
	not (exists CID) 
	    (pubCIDs(CID) and not ftpLink(WebID, 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---------------------------