Question 2: Stock price database [40 points] Consider a simple database storing

Question

Question 2: Stock price database [40 points] Consider a simple database storing information about daily stock prices, consisting of a STOCK and a PRICE table. The table STOCK contains information about a particular stock, with the following columns: • ticker: the ticker symbol; e.g., GOOG, AAPL, GE. • exchange: the exchange where it is listed; e.g., NYSE, NASDAQ. The primary key is ticker. The table PRICE contains information about daily prices, with the following columns: • ticker: the stock’s ticker symbol. • date: the date of the price information. • close: the closing price of the stock. The primary key consists of (ticker, date). Question 2.1 [10 points] Write a relational algebra expression to retrieve the tickers and closing prices of all stocks in the ‘NASDAQ’ exchange on ‘1/19/2017’. Question 2.2 [15 points] Write a relational algebra expression to retrieve all datess when ‘GE’ closed strictly more than twice as high as ‘BAC’.

question 2.2

Explanation / Answer

In this example, first we can make simple sql query for this. After completing step1 we can make relation algebra.

Question 2.1 : sql query is : select sk.ticker,pc.close from STOCK sk inner join PRICE pc on sk.ticker = pc.ticker
where sk.exchange = 'NASDAQ' and pc.date = '01/19/2017'

Relation algebra is :

PROJECTsk.ticker,pc.close ([SELECTsk.exchange = "NASDAQ",pc.date = "01/19/2017" (RENAMEsk(STOCK))] JOINsk.ticker=pc.ticker [RENAMEpc(PRICE)])

Question 2.2 :

Please explain in detail for question 2.2. I apologize I am not understading what is BAC and what exactly it needs.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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