Devise relational algebra queries for the following schema of a database storing

Question

Devise relational algebra queries for the following schema of a database storing information about daily stock prices and basic transactions made by a trading rm. You should dene domains so as to make the schema complete.

STOCK (ticker, exchange)

ticker: the stock's ticker symbol; e.g. GOOG, AAPL, GE

exchange: the exchange where the ticker is listed; e.g. NYSE, NASDAQ

PRICE (ticker, date, close)

ticker: the stock's ticker symbol

date: the date of the price information

close: the closing price of the stock

BUYnSELL (buy or sell,ticker, date, timestamp, value, num of shares)

buy or sell: `BUY' or `SELL'

ticker: the stock's ticker symbol

date: the date of the price information

timestamp: time of the transaction
price: the price of a single share
num of shares: number of shares (bought or sold)
Express the following except from (v) as relational algebra expressions.

i. Find the tickers and closing prices of all stocks exchanged in 2017 (careful with this question).

ii. Find the tickers whose closing price is both higher than `IBM' on `3/20/2017' and no higher than `GOOG' on the same date.

iii. Find the tickers of all stocks that closed at the highest price on `3/20/2017'. (we are asking for all stocks" since there may be more than one with the same highest price")

iv. Find the tickers of all stocks in `NYSE' whose closing price on `3/20/2017' was either strictly below $20 or above $100

v. Find the tickers of the stocks whose closing price showed the highest increase between `3/20/2016' and `3/21/2016' in `NYSE' and whose closing price was above $100 for the entire 2017 (we are asking for all stocks" since there may be more than one with the same increase)

Explanation / Answer

as per chegg policy for multiple questions we are asked to answer first 4 parts only

1) select s.ticker, p.close from STOCK s inner join PRICE p on s.ticker = p.ticker where YEAR(ASOFDATE) = '2017';

2) select s.ticker from STOCK s inner join PRICE p on s.ticker = p.ticker where s.close > (select p.close from price p ,price p1 where p.ticker='IBM' and p1.ticker = 'GOOG' and and p1.date = p.date and p.date = '3/20/2017' and p.close<p1.close);

3) select s.ticker from stock s inner join price p on p.ticker = s.ticker where p.price > (select max(close) from price where date = '3/20/2017');

4) select s.ticker from stock s inner join price p on p.ticker = s.ticker where s.exchange='NYSE' and p.date = '3/20/2017' and p.price <20 and p.price >100;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.