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

       (offered(hID: HID, sName: S, mID:MID) and

	      model(mID: MID, hID: HID, story: '1'));





subdivisionsOfferOther(subDivisionName: S) <- (exists H, M, ST) 

       (offered(hID: H, sName: S, mID:M) and

	      model(story: ST, hID:H, mID: M) and ST<>'1');



dbn1(sName: N, sCity: C, sZip: Z) <- 

	subdivision(sName: N, sZip: Z, sCity: C) and

	subdivisionsOfferSingle(subName: N) and

	not subdivisionsOfferOther(subDivisionName: N);



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

% Q2.2	List all the homebuilders who offer single-story models 

%	with at least 2000 square feet in subdivisions located in "Tempe". 

%	(hName, hPhone)



dbn2(hName: N, hPhone: P) <- (exists H, M, SN, S)

       (homebuilder(hPhone: P, hID: H, hName: N) and

	      model(hID: H, mID: M, story: '1', sqft: S) and S > 2000 and

	      offered(sName: SN, hID: H, mID: M) and

	      subdivision(sCity: 'Tempe', sName: SN));



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

% Q2.3 	Which lots in the "Terraces" subdivision are available i.e. not sold? 

%	(lotNum, lStAddr, lSize, lPremium)



dbn3(lotNum: L, lStAddr: A, lSize: S, lPremium: P) <- 

       lot(sName:'Terraces', lPremium: P, lotNum: L, lStAddr: A, lSize: S) and

	not sold(sName: 'Terraces', lotNum: L);

	



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

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

%	(hName, mName)



dbn4(hName: N, mName: MN) <- (exists H, M)

       (homebuilder(hID: H, hName: N) and

	      model(hID: H, mID: M, mName: MN) and

	      not offered(mID: M, hID: H) );

	



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

% Q2.5	Which subdivisions offer models from more than one homebuilder?

%	(sName, sCity, sZip)



dbn5(sName: N, sCity: C, sZip: Z) <-  

	subdivision(sName: N, sCity: C, sZip: Z) and

	(exists H1, H2)

	   (offered(hID: H1, sName: N) and

	    offered(sName: N, hID: H2) and 

	    H1 <> H2 ) ;



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

% Q2.6	Which models are offered in only one subdivision? 

%	(hName, mName)



dbn6(hName: H,mName: M) <- (exists HID, MID, S) 

       (homebuilder(hID: HID,hName: H) and

	      model(hID: HID,mID: MID,mName: M) and 

	      offered(hID: HID, sName: S,mID:MID) and 

	      not (exists AN) 

		      (offered(sName:AN,mID: MID, hID: HID) and S<>AN));





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

% Q2.7	Which model(s) offered in the "Foothills" subdivision 

%	has the maximum square footage? 

%	(hName, mName, sqft)



foothillsModels(hID: H, mID: M, sfeet: S) <-	 

        offered(sName:'Foothills', hID: H, mID: M) and

	      model(hID: H, sqft: S, mID: M);



maxSqftFoothillsModels(hID: H, mID: M, sqft:S ) <- 

	foothillsModels(hID: H, mID: M, sfeet: S) and

	not (exists S2) (foothillsModels(sfeet: S2) and S2 > S);



dbn7(hName: H, mName: N, sqft: S) <- (exists HID, MID)

       (maxSqftFoothillsModels(hID: HID, mID: MID, sqft: S) and

	      homebuilder(hName: H, hID: HID) and

	      model(mID: MID, hID: HID, sqft: S, mName: N));







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

% Q2.8	Which subdivision offers all the models by the homebuilder "Homer"? 

%	(sName, sCity, sZip)



dbn8(sName: N,sCity: C,sZip: Z) <- (exists H)

 (subdivision(sZip: Z, sName: N,sCity: C) and

  homebuilder(hName: 'Homer', hID: H) and

  offered(sName: N,hID: H) and

  not (exists M)

       (model(mID: M, hID: H) and

        not offered(sName: N,hID: H,mID: M)));

      



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

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