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

notesCIDs := project cID (select category= 'N' (courseware));

notesWebIDs := project webID (notesCIDs njoin ftpLink);

alg1 := 
project webID, webTitle, url, base (webpage njoin notesWebIDs);

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

asulogoGID := project gID (select gName='asulogo' (graphic));

asulogoWebIDs := project webID (asulogoGID njoin display);

alg2 := 
	project webID, webTitle, url, base (webpage njoin asulogoWebIDs);

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

webIDsNoGraphic := 
(project webID (webpage)) difference (project webID (display));

alg3 := 
project webID, webTitle, url, base (webpage njoin webIDsNoGraphic);

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

gifGIDs := project gID (select gType= 'gif' (graphic));
gifWebIDs := project webID (display njoin gifGIDs);

jpgGIDs := project gID (select gType= 'jpg' (graphic));
jpgWebIDs := project webID (display njoin jpgGIDs);


gifNOjpgWebIDs := gifWebIDs difference jpgWebIDs;

alg4 := 
project webID, webTitle, url, base 
	(webpage njoin gifNOjpgWebIDs);

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


ftpLinkCopy (webIDCopy, cIDCopy) := ftpLink;

moreThanOneCourseware := 
project webID (select (webID = webIDCopy  and cID <> cIDCopy) 
	(ftpLink product ftpLinkCopy));

alg5 := 
project webID, webTitle, url, base (webpage njoin moreThanOneCourseware); 

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

httpLinkCopy (sourceWebIDCopy, targetWebIDCopy) := httpLink;

httpLinkProduct := httpLink product httpLinkCopy;

moreThanOneLink := 
project sourceWebID 
    (select (sourceWebID = sourceWebIDCopy and targetWebID <> targetWebIDCopy) (httpLinkProduct));

onlyOneLink(webID) := 
(project sourceWebID (httpLink)) difference moreThanOneLink;

alg6 := project webID, webTitle, url, base (webpage njoin onlyOneLink); 

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

webIDsHits := project webID, hits (webpage);
webIDsHitsCopy (webIDCopy, hitsCopy) := webIDsHits;

webIDsNotMax := 
project webID (select hits < hitsCopy (webIDsHits product webIDsHitsCopy));

webIDsMaxHits := (project webID (webpage)) difference webIDsNotMax;

alg7 := webIDsMaxHits njoin webpage;

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

pubCIDs := project cID (select category= 'P' (courseware));

webIDsAtLeastOnePub := project webID (ftpLink njoin pubCIDs);

allpubCIDsWithAllWebIDs := pubCIDs product webIDsAtLeastOnePub;

webIDsNotAllPubs := 
project webID (allpubCIDsWithAllWebIDs difference ftpLink);

webIDsAllPubs := webIDsAtLeastOnePub difference webIDsNotAllPubs;

alg8 := 
project webID, webTitle, url, base (webpage njoin webIDsAllPubs);

%------------------------------------------------------------------------
% 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---------------------------