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



dbn1(webID: I, webTitle: T, url: U, base: B) <-

  (exists C)

	(courseware(cID: C,category:'N') and

	 ftpLink(cID: C, webID: I) and

	 webpage(url: U, base: B, webID: I, webTitle: T));



% -----------------------------------------------------------------------

% Q1.2	Which pages display graphics having the name 'asulogo'?

%	(webID, webTitle, url, base)



dbn2(webID: I, webTitle: T, url: U, base: B) <-

  (exists G)

	(graphic(gName: 'asulogo', gID: G) and 

   display(gID: G, webID: I) and

	 webpage(webID: I, webTitle: T, base: B, url: U) );



%------------------------------------------------------------------------

% Q1.3 	Which pages do not display any graphics?

%	(webID, webTitle, url, base)



dbn3(webID: I, webTitle: T, url: U, base: B) <-

	webpage(webID: I, url: U, webTitle: T, base: B) and

	not display(webID: I);



%------------------------------------------------------------------------

% Q1.4	Which pages use 'gif' graphics but not 'jpg' graphics?

%	(webID, webTitle, url, base)



gifWebIDs(web: W) <- 

  (exists G) (graphic(gType: 'gif', gID: G) and display(webID: W, gID: G));



jpgWebIDs(web: W) <- 

  (exists G) (graphic(gType: 'jpg', gID: G) and display(webID: W, gID: G));



dbn4(webID: I, webTitle: T, url: U, base: B) <-

	webpage(base: B, webTitle: T, webID: I, url: U) and

	gifWebIDs(web: I) and not jpgWebIDs(web: I);



%------------------------------------------------------------------------

% Q1.5	Which pages contain more than one (ftp) link to courseware?

%	(webID, webTitle, url, base, hits)



dbn5(webID: I, webTitle: T, url: U, base: B) <-

  (exists C) 

	(webpage(webID: I, webTitle: T, base: B, url: U) and 

	 ftpLink(cID: C, webID: I) and

	 (exists C2) 

		(ftpLink(cID: C2, webID: I) and

		 C2 <> C));





%------------------------------------------------------------------------

% Q1.6	Which pages contain only one (http) link to another web page?

%	(webID, webTitle, url, base)	



dbn6(webID: I, webTitle: T, url: U, base: B) <-

  (exists TID) 

	(webpage(webTitle: T, webID: I, url: U, base: B) and 

	 httpLink(targetWebID: TID, sourceWebID: I) and

	 not (exists TID2) 

		(httpLink(sourceWebID: I, targetWebID: TID2) and

		 TID2 <> TID));

		

%------------------------------------------------------------------------

% Q1.7	Which pages have the most hits?

%	(webID, webTitle, url, base, hits)



dbn7(webID: I, webTitle: T, url: U, base: B, hits: H) <-

	webpage(webID: I,webTitle: T, url: U, hits: H, base: B) and

	not (exists H2) (webpage(hits: H2) and H2 > H);





%------------------------------------------------------------------------

% Q1.8	Which pages contain (ftp) links to all courseware in the publications category?

%	(webID, webTitle, url, base)



pubCIDs(cID: C) <- courseware (category: 'P', cID: C);



dbn8(webID: I, webTitle: T, url: U, base: B) <-

	webpage (webID: I, webTitle: T, url: U, base: B) and

	(exists C)(pubCIDs(cID: C) and ftpLink(cID: C, webID: I)) and

	not (exists CID) 

	    (pubCIDs(cID:CID) and not ftpLink(webID: I, cID: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---------------------------