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



dbn1(taxPayerID: I, name: N, sTicker: ST, sName: SN) <- 

	client(name: N, taxPayerID: I) and

	stockPortfolio(taxPayerID: I, sTicker: ST) and

	stock(sTicker: ST, rating: 'A', sName: SN);



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

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



dbn2(taxPayerID: I, name: N) <- (exists ST, MT)

       (client(name: N, taxPayerID: I) and

	      stockPortfolio(sTicker: ST, taxPayerID: I) and

	      stock(sTicker: ST, prinBus:'Technology') and

	      mutualFundPortfolio(mTicker: MT, taxPayerID: I) and

	      mutualFund(prinObj: 'G', mTicker: MT));



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

% Q3.3 	What clients have not invested in mutual funds with income ('I') 

%	as a principal objective? 

%	(taxPayerID, name) 



dbn3(taxPayerID: I, name: N) <- 

	client(name: N, taxPayerID: I) and

	not (exists MT)

		(mutualFundPortfolio(mTicker: MT, taxPayerID: I) and

		 mutualFund(mTicker: MT, prinObj:'I'));



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

% Q3.4	Which clients invest in stocks but not in mutual funds? 

%	(taxPayerID, name) 



dbn4(taxPayerID: I, name: N) <- 

	client(taxPayerID: I, name: N) and

	(exists ST)

		(stockPortfolio(sTicker: ST, taxPayerID: I) and

		 stock(sTicker: ST))

	and not (exists MT)

		(mutualFundPortfolio(taxPayerID: I, mTicker: MT) and

		 mutualFund(mTicker: MT));



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

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

%	(taxPayerID, name)



investNR(iD: I, sTicker: S) <- 

	client(taxPayerID: I) and

	stockPortfolio(taxPayerID: I, sTicker: S) and

	stock(rating: 'NR', sTicker: S);



dbn5(taxPayerID: I, name: N) <- 

	client(name: N, taxPayerID: I) and

	(exists ST1, ST2)

		(investNR(sTicker: ST1, iD: I) and

		 investNR(iD: I, sTicker: ST2) and

		 ST1 <> ST2);



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

% Q3.6	Which clients invest in only one mutual fund with stability ('S') 

%	as a principal objective?

%	(taxPayerID, name)



investS(taxID: I, mtick: M) <- 

  mutualFund(prinObj: 'S', mTicker: M) and

	mutualFundPortfolio(taxPayerID: I, mTicker: M);



dbn6(taxPayerID: ID, name: N) <-

  (exists MT)

    (client(taxPayerID: ID, name: N) and

	  investS(mtick: MT, taxID: ID) and

	  not (exists M)

		  (investS(taxID: ID, mtick: M) and M<>MT));



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

% Q3.7	Which mutual funds have the minimum current rate? 

%	(mTicker, mName, mCurrent) 



dbn7(mTicker: T, mName: N, mCurrent: MC) <-

	mutualFund(mCurrent: MC, mTicker: T, mName: N) and

	not (exists C)

		(mutualFund(mCurrent: C) and C < MC);



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

% Q3.8	What clients have invested in all of the mutual funds within the 

%	'Fictitious' fund family? 

%	(taxPayerID, name) 



fictitiousFunds(mtick: M) <- (exists FID)

       (mutualFund(familyID: FID, mTicker: M) and

	      fundFamily(familyID: FID, company:'Fictitious'));



dbn8(taxPayerID: I, name: N) <-

	client(taxPayerID: I, name: N) and

	(exists F)(fictitiousFunds(mtick: F) and 

             mutualFundPortfolio(taxPayerID: I, mTicker: F)) and

	not (exists M)

		(fictitiousFunds(mtick: M) and

		 not mutualFundPortfolio(mTicker: M, taxPayerID: I));



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

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