%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -----------------------------------------------------------------------
%          TUPLE RELATIONAL CALCULUS (TRC)
% -----------------------------------------------------------------------
%                        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 :=
{O.sName |(exists M) 
       (offered(O) and model(M) and 
	O.hID=M.hID and O.mID=M.mID and M.story = '1') };

subdivisionsOfferOther :=
{O.sName |(exists M) 
       (offered(O) and model(M) and 
	O.hID=M.hID and O.mID=M.mID and M.story <> '1') };

trc1 :=
{ S |	subdivision(S) and
	(exists Single)(subdivisionsOfferSingle(Single) and S.sName=Single.sName) and
	not (exists Other)(subdivisionsOfferOther(Other) and S.sName=Other.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)

trc2 :=
{ H.hName, H.hPhone | (exists M, O, S)
       (homebuilder(H) and model(M) and offered(O) and subdivision(S) and
	H.hID=M.hID and M.story='1' and M.sqft > 2000 and
	M.hID=O.hID and M.mID=O.mID and
	O.sName=S.sName and S.sCity='Tempe') };

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

trc3 :=
{ L.lotNum, L.lStAddr, L.lSize, L.lPremium |
 	lot(L) and L.sName='Terraces' and
	not (exists S)(sold(S) and S.sName=L.sName and S.lotNum=L.lotNum) };
	

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

trc4 :=
{ H.hName, M.mName |
	homebuilder(H) and model(M) and	H.hID = M.hID and
	not (exists O)(offered(O) and M.hID=O.hID and M.mID=O.mID) };
	

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

trc5 :=
{ S | 	subdivision(S) and
	(exists O1, O2)
	   (offered(O1) and O1.sName=S.sName and 
	    offered(O2) and O2.sName=S.sName and O1.hID <> O2.hID) };

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

trc6 := 
{H.hName, M.mName | (exists O) 
       (homebuilder(H) and 
	model(M) and M.hID=H.hID and
	offered(O) and O.hID=M.hID and O.mID=M.mID and
	not (exists F) 
		(offered(F) and F.hID=O.hID and F.mID=O.mID and F.sName<>O.sName))};

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

foothillsModels :=
{ M.hID, M.mID, M.sqft |
	model(M) and (exists O)	 
        (offered(O) and O.sName='Foothills' and O.hID=M.hID and O.mID=M.mID) };

maxSqftFoothillsModels :=
{F |	foothillsModels(F) and
	not (exists M) (foothillsModels(M) and M.sqft > F.sqft) };

trc7 :=
{ H.hName, M.mName, S.sqft |
	homebuilder(H) and model(M) and maxSqftFoothillsModels(S) and
	H.hID=S.hID and M.hID=S.hID and M.mID=S.mID };


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

homerModels  := 
{ M.hID, M.mID | (exists H)
       (homebuilder(H) and H.hName='Homer' and
	model(M) and M.hID=H.hID) };

trc8 := 
{ S | 	subdivision(S) and 
	(exists O,M)
	(offered(O) and homerModels(M) and S.sName=O.sName and O.hID=M.hID) and
	not (exists H)
     	    (homerModels(H) and 
       	     not (exists F)
           	(offered(F) and 
		 F.sName=S.sName and F.hID=H.hID and F.mID=H.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-------------------------------