INF 4401 – Introduction To Databases

This homework is due for the 13/04/2023. You will have to submit your homework on Moodle. The format is free (handwritten or PDF), but make sure it is readable.
The grade will be on 20. You have one bonus point.


For this homework, we will work on a financial database composed of several tables.

• Companies: It represents all the companies in our dataset.
• StockExchange: It represents all the stock exchanges, i.e. the places where companies equities are exchanged. seID is the stock exchange ID. The opening and closing hour are at UTC, and only takes into account hours (not minutes).
• Equities: A table representing the different equities. tickerID is the unique identifier of an equity.
• Traders: A table representing the traders.
• Transactions: A table containing all the transactions. The buyers and sellers are traders.
• Portfolios: A table containing all the equities that are currently owned by the traders.

Here are examples of contents:



Companies

companyID	name	country	creationDate
C001	Tesla	USA	01/07/2023
C002	Renault	France	24/12/1898
C003	Stellantis	Netherland	16/01/2021
C004	Airbus	France	18/12/1970

StockExchange

seID	name	region	openHour	closeHour
S001	New York Stock Exchange	USA	14	21
S002	Nasdaq	USA	14	21
S003	Euronext	Europe	8	16
S004	Japan Exchange Group	Japan	0	6
S005	OTC US	USA	14	21

Equities

tickerID	companyID	seID	currentValue
TSLA	C001	S002	190.90
RNLSY	C002	S005	9.00
RN0.FP	C002	S003	43.390
STLAP.FP	C003	S003	17.67
STLA	C003	S001	18.35

Traders

traderID	name	age
T001	George Soros	92
T002	Paul Tudor Jones	68
T003	Richard Dennis	74
T004	John Paulson	67
T005	Nick Leeson	56

Transactions

transactionID	tickerID	date	price	buyerID	sellerID
X001	TSLA	02/03/2023	186.74	T001	T002
X002	TSLA	01/03/2023	206.21	T002	T001
X003	TSLA	16/02/2023	210.78	T003	T004
X004	RNO	10/02/2023	41.80	T001	T004

Portfolio

traderID	tickerID	quantity
T001	TSLA	115
T001	RNO	21
T002	RNO	210
T003	RNO	34
T003	STLA	374

Database Schema

(1 point) For each table, list all the attributes with a possible domain.

• Companies: companyID: String, name: String, country: String, creationDate: Date
• StockExchange: seID: String, name: String, region: String, openHour: Integer, closeHour: Integer
• Equities: tickerID: String, companyID: String, seID: String, currentValue: Float
• Traders: traderID: String, name: String, age: Integer
• Transactions: transactionID: String, tickerID: String, date: Date, price: Float, buyerID: String, sellerID: String
• Portfolio: traderID: String, tickerID: String, quantity: Integer

(1 point) For each table, give the key.

• Companies: companyID
• StockExchange: seID
• Equities: tickerID
• Traders: traderID: String
• Transactions: transactionID: String
• Portfolio: traderID, tickerID

(1 point) For each table, give the foreign keys (if there is any).

• Companies: None
• StockExchange: None
• Equities: companyID, seID
• Traders: None
• Transactions: tickerID, buyerID, sellerID (both represent a traderID)
• Portfolio: traderID, tickerID

(1 point) Using the answers above, give the final database schema.

• Companies(companyID: String, name: String, country: String, creationDate: Date)
• StockExchange(seID: String, name: String, region: String, openHour: Integer, closeHour: Integer)
• Equities(tickerID: String, companyID: String, seID: String, currentValue: Float)
• Traders(traderID: String, name: String, age: Integer)
• Transactions(transactionID: String, tickerID: String, date: Date, price: Float, buyerID: String, sellerID: String)
• Portfolio(traderID: String, tickerID: String, quantity: Integer)

Relational Algebra

(1 point) Using plain English, explain what the following diagram does.



We get all the tickers Richard Denis bought and for how much.

(2 point) Draw a diagram to get all the tickerIDs of equities traded on the Stock Exchange called "Euronext".



(2 point) Draw a diagram to get the tickerIDs that were bought by the trader with ID T003 or that were emitted by the company C004.



SQL

In this exercice, you are not asked to draw a relational algebra diagram. You have to answer directly in SQL.

(2 point) Translate the following diagram to SQL.



SELECT tickerID, price FROM Transactions JOIN Traders On Traders.traderID = Transactions.buyerID WHERE name = "Richard Denis";


Or


SELECT tickerID, price FROM Transactions, Traders WHERE name = "Richard Denis" AND Traders.traderID = Transactions.buyerID;

(2 point) Write a SQL query to get the tickerIDs of companies located in France.

SELECT tickerID FROM Equities JOIN Companies On Equities.companyID = Company.companyID WHERE Company.country = "France";

(2 point) Write a SQL query to get all the tickerIDs and the price of the equities such that the seller is younger than the buyer.

SELECT tickerID, price FROM Transactions JOIN Traders AS Buyers ON Transactions.buyerID = Buyers.traderID JOIN Traders AS Sellers ON Transactions.sellerID = Sellers.traderID WHERE Sellers.age < Buyers.age;

(2 point) Write a SQL query to get the total of money exchanged on 03/03/2023.

SELECT SUM(price) as total_amount FROM Transactions WHERE date = "03/03/2023";

(2 point) Write a SQL query to give, for each traderID, the minimum price paid for an equity.

SELECT buyerID, MIN(price) as min_price FROM Transactions GROUP BY buyerID;

(2 point) Write a SQL query to get the names of the traders who own more than 10 different tickers and who are less than 30.

SELECT name FROM Traders JOIn Portfolio ON Portfolio.traderID = Traders.traderID WHERE age < 30 GROUP BY Traders.traderID HAVING COUNT(DISTINCT tickerID) > 10;

(2 point - bonus) Write a SQL query to find the trader with the most money in his or her portfolio. A trader can own several tickers. Note that, in SQL, we can multiply column. You will need to use a column quantity * currentPrice AS totalCurrentPrice.

SELECT traderID FROM Portfolio JOIN Equities ON Equities.tickerID = Portfolio.tickerID GROUP BY traderID ORDER BY SUM(quantity * currentValue) DESC LIMIT 1;