%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -----------------------------------------------------------------------
%          DOMAIN RELATIONAL CALCULUS (DRC)
% -----------------------------------------------------------------------
%                        New Home Enterprise
%
%  homebuilder(hID, hName, hStreet, hCity, hZip, hPhone)
%       primary key (hID)
%  model(hID, mID, mName, sqft, story)
%       primary key (hID, mID)
%       foreign key (hID) references homebuilder(hID)
%  subdivision(sName, sCity, sZip)
%       primary key (sName)
%  offered(sName, hID, mID, price)
%       primary key (sName, hID, mID)
%       foreign key (sName) references subdivision(sName)
%       foreign key (hID, mID) references model(hID, mID)
%  lot(sName, lotNum, lStAddr, lSize, lPremium)
%       primary key (sName, lotNum)
%       foreign key (sName) references subdivision(sName)
%  sold(sName, lotNum, hID, mID, status)
%       primary key (sName, lotNum)
%       foreign key (sName, lotNum) references lot(sName,lotNum)
%       foreign key (hID, mID) references model(hID, mID)
%
%------------------------------------------------------------------------
% Q2.1	Are there subdivisions that only offer single-story homes?
%	(sName, sCity, sZip)

subdivisionsOfferSingle :=
{SName |(exists HID, MID) 
       (offered(SName, HID, MID, _) and
	model(HID, MID, _, _, '1')) };


subdivisionsOfferOther :=
{SName |(exists HID, MID, Story) 
       (offered(SName, HID, MID, _) and
	model(HID, MID, _, _, Story) and Story<>'1') };


drc1 :=
{ SName, SCity, SZip |
	subdivision(SName, SCity, SZip) and
	subdivisionsOfferSingle(SName) and
	not subdivisionsOfferOther(SName) };

% -----------------------------------------------------------------------
% Q2.2	List all the homebuilders who offer single-story models 
%	with at least 2000 square feet in subdivisions located in "Tempe". 
%	(hName, hPhone)

drc2 :=
{ HName, HPhone | (exists HID, MID, SName, Sqft)
       (homebuilder(HID, HName, _, _, _, HPhone) and
	model(HID, MID, _, Sqft, '1') and Sqft > 2000 and
	offered(SName, HID, MID, _) and
	subdivision(SName, 'Tempe', _) ) };

%------------------------------------------------------------------------
% Q2.3 	Which lots in the "Terraces" subdivision are available i.e. not sold? 
%	(lotNum, lStAddr, lSize, lPremium)

drc3 :=
{ LotNum, LStAddr, LSize, LPremium |
       lot('Terraces', LotNum, LStAddr, LSize, LPremium) and
	not sold('Terraces', LotNum, _, _, _) };
	

%------------------------------------------------------------------------
% Q2.4	Which model(s) are not currently offered in any subdivision? 
%	(hName, mName)

drc4:=
{HName, MName | (exists HID, MID)
       (homebuilder(HID, HName, _, _, _, _) and
	model(HID, MID, MName, _, _) and
	not offered(_, HID, MID, _) ) };
	

%------------------------------------------------------------------------
% Q2.5	Which subdivisions offer models from more than one homebuilder?
%	(sName, sCity, sZip)

drc5 :=
{ SName, SCity, SZip | 
	subdivision(SName, SCity, SZip) and
	(exists HID1, HID2)
	   (offered(SName, HID1, _, _) and
	    offered(SName, HID2, _, _) and 
	    HID1 <> HID2 ) };

%------------------------------------------------------------------------
% Q2.6	Which models are offered in only one subdivision? 
%	(hName, mName)

drc6 := 
{HName,MName | (exists HID, MID, SName) 
       (homebuilder(HID,HName,_,_,_,_) and
	model(HID,MID,MName,_,_) and 
	offered(SName,HID,MID,_) and 
	not (exists AnotherSName) 
		(offered(AnotherSName,HID,MID,_) and SName<>AnotherSName))};


%------------------------------------------------------------------------
% Q2.7	Which model(s) offered in the "Foothills" subdivision 
%	has the maximum square footage? 
%	(hName, mName, sqft)

foothillsModels :=
{ HID, MID, Sqft |	 
        offered('Foothills', HID, MID, _) and
	model(HID, MID, _, Sqft, _) };

maxSqftFoothillsModels :=
{HID, MID, Sqft |
	foothillsModels(HID, MID, Sqft) and
	not (exists S) (foothillsModels(_, _, S) and S > Sqft)};

drc7 :=
{ HName, MName, Sqft | (exists HID, MID)
       (maxSqftFoothillsModels(HID, MID, Sqft) and
	homebuilder(HID, HName, _, _, _, _) and
	model(HID, MID, MName, Sqft, _)) };



%------------------------------------------------------------------------
% Q2.8	Which subdivision offers all the models by the homebuilder "Homer"? 
%	(sName, sCity, sZip)

drc8 :=
{SName,SCity,SZip | (exists HomerHID)
 (subdivision(SName,SCity,SZip) and
  homebuilder(HomerHID,'Homer',_,_,_,_) and
  offered(SName,HomerHID,_,_) and
  not (exists MID)
       (model(HomerHID,MID,_,_,_) and
        not offered(SName,HomerHID,MID,_)))};
      

%------------------------------------------------------------------------
% Q2.9  	For each subdivision, find the number of models offered and 
%	the average, minimum and maximum price of the models offered at that subdivision. 
%	Display the result in descending order on the average price of a home.
%	(sName, sCity, numberOfModels, avgPrice, minPrice, maxPrice)

% NOT APPLICABLE

%------------------------------------------------------------------------
% Q2.10	For each subdivision, find the total of lot premiums for lots that are available. 
%	Display the result in descending order on the total of lot premiums. 
%	(sName, sCity, totalLotPremium)

% NOT APPLICABLE	

%---------------------------End New Home Enterprise-------------------------------