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)