%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -----------------------------------------------------------------------
%            TUPLE RELATIONAL CALCULUS (TRC)
% -----------------------------------------------------------------------
%                        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) 

trc1 :=
{ C.taxPayerID, C.name, S.sTicker, S.sName |
	client(C) and stock(S) and 
	(exists P)
		(stockPortfolio(P) and
	 	 S.rating = 'A' and
		 S.sTicker = P.sTicker and
		 P.taxPayerID = C.taxPayerID) };

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

technologyClients :=
{ P.taxPayerID |
 	stockPortfolio(P) and
	(exists S)
	    (stock(S) and 
 	     S.prinBus = 'Technology' and
	     S.sTicker = P.sTicker) };

growthClients :=
{ P.taxPayerID |
 	mutualFundPortfolio(P) and
	(exists M)
	    (mutualFund(M) and
 	     M.prinObj = 'G' and
	     M.mTicker = P.mTicker) };

trc2 :=
{ C.taxPayerID, C.name |
	client(C) and
	(exists T,G)
	(technologyClients(T) and T.taxPayerID=C.taxPayerID and
	 growthClients(G) and G.taxPayerID=C.taxPayerID)};

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

trc3 :=
{ C.taxPayerID, C.name |
	client(C) and
	not (exists M,P)
		(mutualFund(M) and mutualFundPortfolio(P) and
	 	 M.prinObj='I' and 
		 M.mTicker=P.mTicker and
		 P.taxPayerID=C.taxPayerID) };

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

trc4 :=
{ C.taxPayerID, C.name |
	client(C) and
	(exists SP)(stockPortfolio(SP) and SP.taxPayerID=C.taxPayerID) and
	not (exists MP)(mutualFundPortfolio(MP) and MP.taxPayerID=C.taxPayerID) };

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

investNR :=
{ C.taxPayerID, S.sTicker | 
	client(C) and stock(S) and S.rating='NR' and
	(exists P)
		(stockPortfolio(P) and 
		 P.taxPayerID=C.taxPayerID and 
		 P.sTicker=S.sTicker) };

trc5 :=
{ C.taxPayerID, C.name | 
	client(C) and
	(exists I1, I2)
		(investNR(I1) and I1.taxPayerID=C.taxPayerID and
		 investNR(I2) and I2.taxPayerID=C.taxPayerID and
		 I1.sTicker <> I2.sTicker) };

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

investS(taxPayerID, mTicker) :=
{ P.taxPayerID, P.mTicker |
	mutualFundPortfolio(P) and
	(exists M)
	    (mutualFund(M) and 
	     M.mTicker=P.mTicker and
	     M.prinObj='S') };

trc6 :=
{ C.taxPayerID, C.name | 
        client(C) and
	(exists I1)(investS(I1) and I1.taxPayerID=C.taxPayerID and
	not (exists I2)
		(investS(I2) and I2.taxPayerID=C.taxPayerID and I1.mTicker<>I2.mTicker)) };

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

trc7 :=
{ M.mTicker, M.mName, M.mCurrent |
	mutualFund(M) and
	not (exists F)(mutualFund(F) and F.mCurrent < M.mCurrent) };


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

fictitiousFunds :=
{ M.mTicker | (exists F)
       (mutualFund(M) and fundFamily(F) and
 	F.company='Fictitious' and
	F.familyID=M.familyID) };
            
trc8 := 	
{ C.taxPayerID, C.name |
 	client(C) and 
	(exists FF, MFP)(fictitiousFunds(FF) and mutualFundPortfolio(MFP) and
		 MFP.taxPayerID=C.taxPayerID and MFP.mTicker=FF.mTicker) and
 	not (exists F)  (fictitiousFunds(F) and
		   not (exists P)
		      (mutualFundPortfolio(P) and
		       P.taxPayerID=C.taxPayerID and 
 		       P.mTicker=F.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------------------