%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -----------------------------------------------------------------------
%            DOMAIN RELATIONAL CALCULUS (DRC)
% -----------------------------------------------------------------------
%                        Investment Portfolio Enterprise
%
%  client(taxPayerID, name, address)
%       primary key (taxPayerID)
%  stock(sTicker, sName, rating, prinBus, sHigh, sLow, sCurrent, ret1Yr, ret5Yr)
%       primary key (sTicker)
%  fundFamily(familyID, company, cAddress)
%       primary key (familyID)
%  mutualFund(mTicker, mName, prinObj, mHigh, mLow, mCurrent, yield, familyID)
%       primary key (mTicker)
%       foreign key (familyID) references fundFamily(familyID)
%  stockPortfolio(taxPayerID, sTicker, sNumShares)
%       primary key (taxPayerID, sTicker)
%       foreign key (taxPayerID) references client(taxPayerID)
%       foreign key (sTicker) references stock(sTicker)
%  mutualFundPortfolio(taxPayerID, mTicker, mNumShares)
%       primary key (taxPayerID, mTicker)
%       foreign key (taxPayerID) references client(taxPayerID)
%       foreign key (mTicker) references mutualFund(mTicker)
%
%------------------------------------------------------------------------
% Q3.1	What clients have invested in which 'A' rated stocks? 
%	(taxPayerID, name, sTicker, sName) 

drc1(taxPayerID, name, sTicker, sName) := 
{ TaxPayerID, Name, STicker, SName |
	client(TaxPayerID, Name, _) and
	stockPortfolio(TaxPayerID, STicker, _) and
	stock(STicker, SName, 'A', _, _, _, _, _, _) };

% -----------------------------------------------------------------------
% Q3.2	Which clients invest in both stocks whose principal business is 
%	'Technology' and mutual funds having growth ('G') as a principal objective?
%	(taxPayerID, name)

drc2(taxPayerID, name) :=
{ TaxPayerID, Name | (exists STicker, MTicker)
       (client(TaxPayerID, Name, _) and
	stockPortfolio(TaxPayerID, STicker, _) and
	stock(STicker, _, _, 'Technology', _, _, _, _, _) and
	mutualFundPortfolio(TaxPayerID, MTicker, _) and
	mutualFund(MTicker, _, 'G', _, _, _, _, _)) };

%------------------------------------------------------------------------
% Q3.3 	What clients have not invested in mutual funds with income ('I') 
%	as a principal objective? 
%	(taxPayerID, name) 

drc3(taxPayerID, name) :=
{ TaxPayerID, Name | 
	client(TaxPayerID, Name, _) and
	not (exists MTicker)
		(mutualFundPortfolio(TaxPayerID, MTicker, _) and
		 mutualFund(MTicker, _, 'I', _, _, _, _, _)) };

%------------------------------------------------------------------------
% Q3.4	Which clients invest in stocks but not in mutual funds? 
%	(taxPayerID, name) 

drc4(taxPayerID, name) :=
{ TaxPayerID, Name | 
	client(TaxPayerID, Name, _) and
	(exists STicker)
		(stockPortfolio(TaxPayerID, STicker, _) and
		 stock(STicker, _, _, _, _, _, _, _, _))
	and not (exists MTicker)
		(mutualFundPortfolio(TaxPayerID, MTicker, _) and
		 mutualFund(MTicker, _, _, _, _, _, _, _)) };

%------------------------------------------------------------------------
% Q3.5	Which clients have more than one no-rating ('NR') stock? 
%	(taxPayerID, name)

investNR :=
{ TaxPayerID, STicker | 
	client(TaxPayerID, _, _) and
	stockPortfolio(TaxPayerID, STicker, _) and
	stock(STicker, _, 'NR', _, _, _, _, _, _) };

drc5(taxPayerID, name) :=
{ TaxPayerID, Name | 
	client(TaxPayerID, Name, _) and
	(exists STicker1, STicker2)
		(investNR(TaxPayerID, STicker1) and
		 investNR(TaxPayerID, STicker2) and
		 STicker1 <> STicker2) };

%------------------------------------------------------------------------
% Q3.6	Which clients invest in only one mutual fund with stability ('S') 
%	as a principal objective?
%	(taxPayerID, name)

investS(taxPayerID, mTicker) :=
{ TaxPayerID, MTicker |
        mutualFund(MTicker, _, 'S', _, _, _, _, _) and
	mutualFundPortfolio(TaxPayerID, MTicker, _) };

drc6(taxPayerID, name) :=
{ TaxPayerID, Name | (exists MTicker)
       (client(TaxPayerID, Name, _) and
	investS(TaxPayerID, MTicker) and
	not (exists M)
		(investS(TaxPayerID, M) and M<>MTicker)) };

%------------------------------------------------------------------------
% Q3.7	Which mutual funds have the minimum current rate? 
%	(mTicker, mName, mCurrent) 

drc7(mTicker, mName, mCurrent) :=
{ MTicker, MName, MCurrent |
	mutualFund(MTicker, MName, _, _, _, MCurrent, _, _) and
	not (exists C)
		(mutualFund(_, _, _, _, _, C, _, _) and C < MCurrent) };

%------------------------------------------------------------------------
% Q3.8	What clients have invested in all of the mutual funds within the 
%	'Fictitious' fund family? 
%	(taxPayerID, name) 

fictitiousFunds :=
{ MTicker | (exists FID)
       (mutualFund(MTicker, _, _, _, _, _, _, FID) and
	fundFamily(FID, 'Fictitious', _)) };

drc8(taxPayerID, name) :=
{ TaxPayerID, Name |
	client(TaxPayerID, Name, _) and
	(exists FTicker)(fictitiousFunds(FTicker) and mutualFundPortfolio(TaxPayerID, FTicker, _)) and
	not (exists MTicker)
		(fictitiousFunds(MTicker) and
		 not mutualFundPortfolio(TaxPayerID, MTicker, _)) };

%------------------------------------------------------------------------
% Q3.9  	For each client that invests in stocks, display the average, minimum 
%	and maximum one-year returns on the stocks they own. Display the 
%	result in ascending order on average one-year returns. 
%	(taxPayerID, name, avgReturn1Yr, minReturn1Yr, maxReturn1Yr) 
%
%	NOT APPLICABLE
%
%------------------------------------------------------------------------
% Q3.10	For each client that invests in mutual funds, display the sum of 
%	the number of shares of mutual funds that they own within each 
%	principal objective category. Display the result in descending 
%	order on the number of shares. 
%	(taxPayerID, name, prinObj, numShares)
%
%	NOT APPLICABLE
%
%-------------------End Investment Portfolio Enterprise------------------