Topic: Stock Trading Record Management System (STRMS)

Objective:

The primary objective of the Stock Trading Record Management System

(STRMS) is to offer a comprehensive tool that allows individual traders to record,

manage, analyze, and reflect on their trades and strategies for better decision-

making.

Entities:

1. User:

● User_ID

● First_Name

● Last_Name

● Email

● Encrypted_Password

2. Trade:

● Trade_ID

● User_ID

● Stock_Symbol
● Entry_Date
 ● Exit_Date

● Entry_Price

● Exit_Price

● Position

● Volume

● Commission_Fee

3. Stock:

● Stock_Symbol

● Company_Name

● Sector

4. Performance Metrics:

● Metrics_ID

● User_ID

● ROI

● Average_Profit_Loss
 ● Win_Loss_Ratio

5. Journal Entry:

● Journal_ID
 ● Trade_ID

● Notes

● Emotions

● Strategy_Tag

● Attachments (Charts, screenshots, etc.)

Relationships:

● User/Trade:

o Each trade is linked to one user. One user can have multiple trades.

● Trade/Stock:

o Each trade involves one stock. A stock can be part of multiple trades.

● User/Performance Metrics:

o Each user has one or multiple performance metrics.

● Trade/Journal Entry:

o Each journal entry is linked to one trade. A trade can have one

journal entry.
Functionalities:

Functionalit Functions Description

y
User Allows new users to create a
Create user profiles
Management profile with necessary details.

Enables users to update their

Update user profiles
existing profile information.

Allows users to permanently

Delete user remove their own account after

re-

authentication

Verifies user identity through

Authentication email

and password during login.

Allows a user to enter details of a

Trade Record New Trades
Records new trade.

Allows a user to modify details of

Update Trade Records
existing trade.
 Allows a user to delete an existing
Delete Trade Records
trade after confirming via a
dialog.

Calculates and displays ROI,

Performance win/loss ratio, and average
Calculate Metrics profit/loss per trade.
Metrics

Stock Retrieve Stock Allows the user to view

Information Information information about different

stocks.

Allows the user to add notes,

emotions, strategy tags, and

Journal Entry Add Journal Entry
attachments related to a specific

trade

Enables the user to update

Update Journal Entry existing

journal entries.
 Allows the user to delete past
Delete Journal Entry
journal entries.
 Exit_Date Entry_Price

Entry_Date Exit_Price

Stock_Symbol (FK) Position

User_ID (FK) Volume

User_ID (PK)

Trade_ID (PK)
Commision_Fee
Trade_ID
Journal_Numer
First_Name Notes

(Foreign Key, *Partial

Last_Name
1 N
User Places Trade
Emotions

1
Email
Journal Entry Strategy_Tag
Documents
1 N
(Weak Entity)

Encrypted_Password

Attachments

Reviews Involves

N 1
Note:
Stock_Symbok (PK)
Metrics_ID (PK)
The Yellow-Coloured items represent the entities (both

Strong and Weak)

Performance
User_ID (FK) Stock
The Green-Coloured items represent the attributes.

The Red-Coloured items represent the primary Key

ROI (Return on
Sector between each

Average_Profit_Loss Win_Loss_Ratio
Scheme Design

1. Creating Tables

—---------------------------------------------------------------------------------
-
--- Create Table Statements —
—---------------------------------------------------------------------------------
-
CREATE TABLE User (
User_ID NUMBER PRIMARY KEY,
First_Name VARCHAR2(50) NOT NULL,
Last_Name VARCHAR2(50) NOT NULL,
Email VARCHAR2(50) NOT NULL
UNIQUE,
Encrypted_Password VARCHAR2(50) NOT NULL
);

CREATE TABLE Trade (

Trade_ID NUMBER PRIMARY KEY,
User_ID NUMBER NOT NULL,
Stock_Symbol VARCHAR2(10) NOT
NULL, Entry_Date DATE NOT NULL,
Exit_Date DATE,
Entry_Price NUMBER NOT NULL,
Exit_Price NUMBER,
Position VARCHAR2(10) NOT
NULL, Volume NUMBER NOT
NULL,
 Commission_Fee NUMBER NOT NULL,

FOREIGN KEY (User_ID) REFERENCES User(User_ID)

);

CREATE TABLE Stock (

Stock_Symbol VARCHAR2(10) PRIMARY KEY,
Company_Name VARCHAR2(50) NOT
NULL, Sector VARCHAR2(50) NOT NULL
);

CREATE TABLE Performance_Metrics

( Metrics_ID NUMBER PRIMARY
KEY, User_ID NUMBER NOT NULL,
ROI NUMBER NOT NULL,
Average_Profit_Loss NUMBER NOT NULL,
Win_Loss_Ratio NUMBER NOT NULL,
FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

CREATE TABLE Journal_Entry (

Journal_ID NUMBER PRIMARY
KEY, Trade_ID NUMBER NOT
NULL, Notes VARCHAR2(255),
Emotions VARCHAR2(50),
Strategy_Tag
VARCHAR2(50),
Attachments BLOB,
FOREIGN KEY (Trade_ID) REFERENCES Trade(Trade_ID)
);

2. Populating Tables With Data

—---------------------------------------------------------------------------------
-
— Adding In Values
—---------------------------------------------------------------------------------------------
--

INSERT INTO User(User_ID, First_Name, Last_Name, Email,

Encrypted_Password) VALUES
(1, 'John', 'Doe', '[Link]@[Link]', 'password1'),
(2, 'Jane', 'Doe', '[Link]@[Link]', 'password2'),
(3, 'Alice', 'Brown', '[Link]@[Link]', 'password3'),
(4, 'Bob', 'Smith', '[Link]@[Link]', 'password4'),
(5, 'Charlie', 'Johnson', '[Link]@[Link]',
'password5'), (6, 'Dave', 'Williams', '[Link]@[Link]',
'password6'), (7, 'Eve', 'Jones', '[Link]@[Link]',
'password7'),
(8, 'Frank', 'Garcia', '[Link]@[Link]', 'password8'),
(9, 'Grace', 'Martinez', '[Link]@[Link]',
'password9'), (10, 'Emily', 'Smith', '[Link]@[Link]',
'password10');

INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES

('AAPL', 'Apple Inc.', 'Technology'),
('GOOGL', 'Google', 'Technology'),

('AMZN', 'Amazon', 'Retail'),

('MSFT', 'Microsoft', 'Technology'),
('TSLA', 'Tesla', 'Automotive'),
('FB', 'Facebook', 'Technology'),
('JPM', 'JPMorgan Chase', 'Finance'),
('V', 'Visa', 'Finance'),
('WMT', 'Walmart', 'Retail'),
('PG', 'Procter & Gamble', 'Consumer Goods');

INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,

Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee)
VALUES
(1, 1, 'AAPL', '2023-01-01', '2023-01-10', 150, 160, 'Long', 10, 5),
(2, 1, 'GOOGL', '2023-01-02', '2023-01-11', 2000, 2100, 'Long', 5, 10),
(3, 1, 'AMZN', '2023-01-03', '2023-01-12', 1800, 1850, 'Short', 8, 6),
(4, 2, 'MSFT', '2023-01-04', '2023-01-13', 200, 205, 'Long', 12, 4),
(5, 3, 'TSLA', '2023-01-05', '2023-01-14', 600, 650, 'Short', 15, 7),
(6, 3, 'FB', '2023-01-06', '2023-01-15', 300, 290, 'Long', 20, 8),
(7, 4, 'JPM', '2023-01-07', '2023-01-16', 100, 105, 'Short', 30, 9),
(8, 5, 'V', '2023-01-08', '2023-01-17', 180, 190, 'Long', 25, 3),
(9, 6, 'WMT', '2023-01-09', '2023-01-18', 120, 130, 'Short', 22, 2),
(10, 2, 'PG', '2023-01-10', '2023-01-19', 90, 95, 'Long', 33, 1);

INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,

Average_Profit_Loss, Win_Loss_Ratio)
VALUES (1, 1, 0.1, 50, 0.8),
(2, 1, 0.2, 60, 0.7),
(3, 2, 0.3, 70, 0.6),
(4, 2, 0.4, 80, 0.5),
(5, 3, 0.5, 90, 0.9),
(6, 3, 0.6, 100, 0.4),
(7, 4, 0.7, 110, 0.3),
(8, 5, 0.8, 120, 0.2),
(9, 6, 0.9, 130, 0.1),
(10, 2, 0.5, 100, 0.9);

INSERT INTO Journal_Entry(Journal_Number, Trade_ID, Notes, Emotions,

Strategy_Tag, Attachments) VALUES
(1, 1, 'Good Trade', 'Calm', 'Breakout', '[Link]'),
(2, 2, 'Bad Trade', 'Nervous', 'Mean Reversion', '[Link]'),
(3, 3, 'Average Trade', 'Neutral', 'Momentum',
'[Link]'), (4, 4, 'Excellent Trade', 'Excited',
'Breakout', '[Link]'), (5, 5, 'Poor Trade', 'Anxious',
'Scalping', '[Link]'),
(6, 6, 'Decent Trade', 'Content', 'Trend Following',
'[Link]'), (7, 7, 'Good Trade', 'Calm', 'Breakout',
'[Link]'),
(8, 8, 'Bad Trade', 'Frustrated', 'Reversal', '[Link]'),
(9, 9, 'Okay Trade', 'Indifferent', 'Range Trading', '[Link]'),
(10, 10, 'Average Trade', 'Neutral', 'Momentum', '[Link]');
INSERT Statement:

INSERT INTO Users(User_ID, First_Name, Last_Name, Email,

Encrypted_Password) VALUES
(1, 'John', 'Doe', '[Link]@[Link]', 'password1'),
(2, 'Jane', 'Doe', '[Link]@[Link]', 'password2'),
(3, 'Alice', 'Brown', '[Link]@[Link]', 'password3'),
(4, 'Bob', 'Smith', '[Link]@[Link]', 'password4'),
(5, 'Charlie', 'Johnson', '[Link]@[Link]',
'password5'), (6, 'Dave', 'Williams', '[Link]@[Link]',
'password6'), (7, 'Eve', 'Jones', '[Link]@[Link]',
'password7'),
(8, 'Frank', 'Garcia', '[Link]@[Link]', 'password8'),
(9, 'Grace', 'Martinez', '[Link]@[Link]',
'password9'), (10, 'Emily', 'Smith', '[Link]@[Link]',
'password10');

INSERT INTO Stock(Stock_Symbol, Company_Name, Sector)

VALUES
('AAPL', 'Apple Inc.', 'Technology'),
('GOOGL', 'Google', 'Technology'),
('AMZN', 'Amazon', 'Retail'),
('MSFT', 'Microsoft', 'Technology'),
('TSLA', 'Tesla', 'Automotive'),
('FB', 'Facebook', 'Technology'),
('JPM', 'JPMorgan Chase', 'Finance'),
('V', 'Visa', 'Finance'),
('WMT', 'Walmart', 'Retail'),
('PG', 'Procter & Gamble', 'Consumer Goods');

INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol,

Entry_Date, Exit_Date, Entry_Price, Exit_Price, Position, Volume,
Commission_Fee) VALUES
(1, 1, 'AAPL', '2023-01-01', '2023-01-10', 150, 160, 'Long', 10, 5),
(2, 1, 'GOOGL', '2023-01-02', '2023-01-11', 2000, 2100, 'Long', 5,
10),
(3, 1, 'AMZN', '2023-01-03', '2023-01-12', 1800, 1850, 'Short', 8, 6),
(4, 2, 'MSFT', '2023-01-04', '2023-01-13', 200, 205, 'Long', 12, 4),
(5, 3, 'TSLA', '2023-01-05', '2023-01-14', 600, 650, 'Short', 15, 7),
(6, 3, 'FB', '2023-01-06', '2023-01-15', 300, 290, 'Long', 20, 8),
(7, 4, 'JPM', '2023-01-07', '2023-01-16', 100, 105, 'Short', 30, 9),
(8, 5, 'V', '2023-01-08', '2023-01-17', 180, 190, 'Long', 25, 3),
(9, 6, 'WMT', '2023-01-09', '2023-01-18', 120, 130, 'Short', 22, 2),
(10, 2, 'PG', '2023-01-10', '2023-01-19', 90, 95, 'Long', 33, 1);

INSERT INTO Performance_Metric(Metrics_ID, User_ID, ROI,

Average_Profit_Loss, Win_Loss_Ratio)
VALUES (1, 1, 0.1, 50, 0.8),
(2, 1, 0.2, 60, 0.7),
(3, 2, 0.3, 70, 0.6),
(4, 2, 0.4, 80, 0.5),
(5, 3, 0.5, 90, 0.9),
(6, 3, 0.6, 100, 0.4),
(7, 4, 0.7, 110, 0.3),
(8, 5, 0.8, 120, 0.2),
(9, 6, 0.9, 130, 0.1),
(10, 2, 0.5, 100, 0.9);

INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes,

Emotions,
Strategy_Tag, Attachments) VALUES
(1, 1, 'Good Trade', 'Calm', 'Breakout', '[Link]'),
(2, 2, 'Bad Trade', 'Nervous', 'Mean Reversion', '[Link]'),
(3, 3, 'Average Trade', 'Neutral', 'Momentum',
'[Link]'), (4, 4, 'Excellent Trade', 'Excited', 'Breakout',
'[Link]'), (5, 5, 'Poor Trade', 'Anxious', 'Scalping',
'[Link]'),
(6, 6, 'Decent Trade', 'Content', 'Trend Following',
'[Link]'), (7, 7, 'Good Trade', 'Calm', 'Breakout',
'[Link]'),
(8, 8, 'Bad Trade', 'Frustrated', 'Reversal', '[Link]'),
(9, 9, 'Okay Trade', 'Indifferent', 'Range Trading',
'[Link]'), (10, 10, 'Average Trade', 'Neutral', 'Momentum',
'[Link]');
Simple Queries & Views

1. USER Queries:

-- Query Title: Retrieve All User Information

-- Description: This query lists all columns for all users in the USER
table.

SELECT * FROM USER;

-- Query Title: Fetch User Emails

-- Description: This query lists the emails of all users in the USER
table.
SELECT Email AS “User Emails” FROM USER;

2. STOCK Queries:

-- Query Title: Retrieve All Stock Information

-- Description: This query lists all columns for all stocks in the
STOCK table.

SELECT * FROM STOCK;

-- Query Title: List Distinct Stock Sectors
-- Description: This query lists all unique sectors from the STOCK
table.

SELECT DISTINCT Sector AS “Distinct Sectors” FROM STOCK;

3. TRADE Queries:

-- Query Title: Filter Trades by Volume

-- Description: This query lists all trades with volume less than 100
but greater than 20.

SELECT * FROM TRADE WHERE Volume < 100 AND Volume > 20;
-- Query Title: Long Position Trades with Odd IDs
-- Description: This query lists all trades with a 'Long' position and
an odd Trade_ID.

SELECT Trade_ID AS “Long Position Trades” FROM Trade WHERE

Position = ‘Long’ AND MOD (Trade_ID, 2) != 0;

4. PERFORMANCE_METRIC Queries:

-- Query Title: Performance Metrics with Positive Averages and

Ratios
-- Description: This query lists performance metrics with a positive
average profit/loss and win/loss ratio greater than 0.5.

SELECT * FROM Performance_Metric WHERE Average_profit_loss

> 0 AND Win_loss_ratio > .5;
-- Query Title: High ROI Metrics
-- Description: This query lists Metrics_IDs and ROIs where the
ROI is greater than 0.5.

SELECT Metrics_ID AS “Metrics with High ROI”, ROI FROM

Performance_Metric WHERE ROI > 0.5;

5. JOURNAL_ENTRY Queries:

-- Query Title: Journal Entry with ID 5

-- Description: This query retrieves the journal entry with
Journal_ID = 5.

SELECT * FROM Journal_entry WHERE Journal_ID = 5;

-- Query Title: Calm Journal Entries
-- Description: This query lists journal entries where emotions are
noted as 'Calm'.

SELECT Journal_ID AS “Calm Journal Entries” FROM Journal_Entry

WHERE Emotions = ‘Calm’;

6. Grouping Query:

-- Query Title: Number of Trades per User

-- Description: This query counts the number of trades per User_ID.

SELECT User_ID, COUNT(*) AS "Number of Trades" FROM Trade

GROUP BY User_ID;
 7. Sorting Query:

-- Query Title: Sort Stocks by Company Name

-- Description: This query lists all stocks sorted by company name in
ascending order.

SELECT Company_Name FROM Stock ORDER BY Company_Name

ASC;
 8. Query with JOIN:

-- Query Title: User and Trade Information

-- Description: This query performs an INNER JOIN between the
Users and Trade tables to show related user and trade information.

SELECT U.First_Name, U.Last_Name, T.Trade_ID FROM Users U

INNER JOIN Trade T ON U.User_ID = T.User_ID;

Script Output guery Result *
@ Task completed in 0.037 seconds

FIRSI NAME LASI NAME IRADz_ID

John Doe
John Doe 2
Jane Doe
Jane Doe 4
Alice Brown
Bob Smith
Charlie Johnson
Dave ùilliars
Ave Jones
Franl: Garcia 1O

10 rows
selected.
CREATING VIEWS

1. View for Users with Long Trades:

Query Title: View for Users with Long Trades

Description: This view lists all users who have placed long trades.

CREATE VIEW Users_with_Long_Trades AS

SELECT U.First_Name, U.Last_Name, T.Trade_ID

FROM Users U

INNER JOIN Trade T ON U.User_ID = T.User_ID

WHERE [Link] = 'Long';

2. View for Stocks Traded Most Often:

Query Title: View for Most Often Traded Stocks

Description: This view lists stocks that have been traded the most time.

CREATE VIEW Most_Traded_Stocks AS

SELECT S.Stock_Symbol, S.Company_Name, COUNT(T.Trade_ID) as

Trade_Count

FROM Stock S

INNER JOIN Trade T ON S.Stock_Symbol = T.Stock_Symbol

GROUP BY S.Stock_Symbol, S.Company_Name

ORDER BY Trade_Count DESC;

Advance Queries:

1. Query for Average ROI by User:

Query Title: Average ROI by User

Description: This query calculates the average ROI for each user.

SELECT U.First_Name, U.Last_Name, AVG([Link]) AS "Average

ROI" FROM Users U

INNER JOIN Performance_Metric P ON U.User_ID = P.User_ID

GROUP BY U.First_Name, U.Last_Name;

2. Query for Trades with Attached Journal Notes:

Query Title: Trades with Attached Journal Notes

Description: This query lists all trades that have associated journal notes
SELECT T.Trade_ID,

[Link] FROM Trade T

INNER JOIN Journal_Entry J ON T.Trade_ID = J.Trade_ID

WHERE [Link] IS NOT NULL;

3. Query for Users Without Performance Metrics:

Query Title: Users Without Performance Metrics

Description: This query lists all users who don’t have performance metrics

SELECT U.First_Name,

U.Last_Name FROM Users U

LEFT JOIN Performance_Metric P ON U.User_ID = P.User_ID

WHERE P.Metrics_ID IS NULL;

 4. Query for Most Traded Stock by User:

Query Title: Most Traded Stock by User

Description: This query determines which stock each user has traded the

most

WITH StockTradeCounts AS (

SELECT U.User_ID, T.Stock_Symbol, COUNT(T.Trade_ID) AS

Trade_Count FROM Users U

INNER JOIN Trade T ON U.User_ID = T.User_ID

GROUP BY U.User_ID, T.Stock_Symbol

SELECT U.First_Name, U.Last_Name,

S.Company_Name FROM StockTradeCounts STC

INNER JOIN Users U ON STC.User_ID = U.User_ID

INNER JOIN Stock S ON STC.Stock_Symbol = S.Stock_Symbol

WHERE STC.Trade_Count = (SELECT MAX(Trade_Count)

FROM StockTradeCounts WHERE User_ID = STC.User_ID);

5. Query to identify Users and their high ROI Trades:

Query Title: Users and Their High ROI Trades

Description: This query lists users, their trades, and performance metrics

for trades with an ROI greater than 0.1.

SELECT U.First_Name, U.Last_Name, T.Trade_ID,

[Link] FROM Users U

JOIN Trade T ON U.User_ID = T.User_ID

JOIN Performance_Metric P ON U.User_ID =

P.User_ID WHERE [Link] > 0.1;

6. Query to find trades with volume greater than 50 but less

than 200.

Query Title: Filter Trades By Volume Range

Description: This query lists all trades where the volume is

greater than 50 but less than 200.

SELECT *

FROM TRADE

WHERE Volume > 50 AND Volume < 200;

 7. Query to list all the stocks where the sector is ‘Technology’

Query Title: List Technology Stocks

Description: This query lists all the stocks in the 'Technology'

sector.

SELECT *

FROM

STOCK

WHERE Sector = 'Technology';

 UNIX Shell

[[Link]]
#!/bin/sh

Pause() {
read -p "Press [Enter] to continue..."
}

MainMenu()
{ while
true do
clear

echo
"================================================="
echo " |TRADING JOURNAL DATABASE SYSTEM|"
echo " |Main Menu - Select Desired Operation(s): |"
echo " |<CRTL-Z Anytime to Enter Interactive CMD Prompt>|"
echo
"================================================="
echo " M) View
Manual" echo " "
echo " 1) Drop Tables"
echo " 2) Create Tables"
echo " 3) Populate
Tables" echo " 4) Query
Tables" echo " "
echo " E) End/Exit"
echo -n "Choose: "

read CHOICE

if [[ ${CHOICE} == "0" ]]
then
echo "Nothing Here"
elif [[ ${CHOICE} == "1" ]]
then
bash
drop_tables.sh
Pause
 elif [[ ${CHOICE} == "2" ]]
then
bash
create_tables.sh
Pause
elif [[ ${CHOICE} == "3" ]]
then
bash
populate_tables.sh
Pause
elif [[ ${CHOICE} == "4" ]]
then
bash [Link]
Pause
elif [[ ${CHOICE} == "E" ]] || [[ ${CHOICE} == "e" ]]
then
exit 0
f
i
do
ne
}

# Main
Program
ProgramStart()
{
while true
do
MainMe
nu done
}

ProgramStar
t exit
 [create_tables.sh]
#!/bin/sh

export LD_LIBRARY_PATH=/home/yourusername/instantclient_12_1

username="p59nguye"

password="05013277"

sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(H ost=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))" <
./[Link]

[[Link]]
CREATE TABLE User (
User_ID NUMBER PRIMARY KEY,
 First_Name VARCHAR2(50) NOT NULL,
Last_Name VARCHAR2(50) NOT NULL,
Email VARCHAR2(50) NOT NULL
UNIQUE,
Encrypted_Password VARCHAR2(50) NOT NULL
);

CREATE TABLE Trade (

Trade_ID NUMBER PRIMARY
KEY, User_ID NUMBER NOT
NULL,
Stock_Symbol VARCHAR2(10) NOT
NULL, Entry_Date DATE NOT NULL,
Exit_Date DATE,
Entry_Price NUMBER NOT NULL,
Exit_Price NUMBER,
Position VARCHAR2(10) NOT
NULL, Volume NUMBER NOT
NULL,
Commission_Fee NUMBER NOT NULL,
FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

CREATE TABLE Stock (

Stock_Symbol VARCHAR2(10) PRIMARY KEY,
Company_Name VARCHAR2(50) NOT
NULL, Sector VARCHAR2(50) NOT NULL
);
CREATE TABLE Performance_Metrics
( Metrics_ID NUMBER PRIMARY
KEY, User_ID NUMBER NOT
NULL,
ROI NUMBER NOT NULL,
Average_Profit_Loss NUMBER NOT NULL,
Win_Loss_Ratio NUMBER NOT NULL,
FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

CREATE TABLE Journal_Entry (

Journal_ID NUMBER PRIMARY
KEY, Trade_ID NUMBER NOT
NULL, Notes VARCHAR2(255),
Emotions VARCHAR2(50),
Strategy_Tag
VARCHAR2(50),
Attachments BLOB, FOREIGN KEY (Trade_ID) REFERENCES
Trade(Trade_ID)
);

[populate_tables.sh]
#!/bin/sh
export LD_LIBRARY_PATH=/home/yourusername/instantclient_12_1
username="p59nguye"
password="05013277"
sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(H ost=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))" <
./[Link]

[[Link]]
INSERT INTO Users(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES
(1, 'John', 'Doe', '[Link]@[Link]', 'password1'),
(2, 'Jane', 'Doe', '[Link]@[Link]', 'password2'),
(3, 'Alice', 'Brown', '[Link]@[Link]', 'password3'),
(4, 'Bob', 'Smith', '[Link]@[Link]', 'password4'),
(5, 'Charlie', 'Johnson', '[Link]@[Link]',
'password5'), (6, 'Dave', 'Williams', '[Link]@[Link]',
'password6'), (7, 'Eve', 'Jones', '[Link]@[Link]',
'password7'),
(8, 'Frank', 'Garcia', '[Link]@[Link]', 'password8'),
(9, 'Grace', 'Martinez', '[Link]@[Link]',
'password9'), (10, 'Emily', 'Smith', '[Link]@[Link]',
'password10');

INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES

('AAPL', 'Apple Inc.', 'Technology'),
('GOOGL', 'Google', 'Technology'),
('AMZN', 'Amazon', 'Retail'),
('MSFT', 'Microsoft', 'Technology'),
('TSLA', 'Tesla', 'Automotive'),
('FB', 'Facebook', 'Technology'),
('JPM', 'JPMorgan Chase', 'Finance'),
('V', 'Visa', 'Finance'),
('WMT', 'Walmart', 'Retail'),
('PG', 'Procter & Gamble', 'Consumer Goods');

INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,

Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee)
VALUES
(1, 1, 'AAPL', '2023-01-01', '2023-01-10', 150, 160, 'Long', 10, 5),
(2, 1, 'GOOGL', '2023-01-02', '2023-01-11', 2000, 2100, 'Long', 5, 10),
(3, 1, 'AMZN', '2023-01-03', '2023-01-12', 1800, 1850, 'Short', 8, 6),
(4, 2, 'MSFT', '2023-01-04', '2023-01-13', 200, 205, 'Long', 12, 4),
(5, 3, 'TSLA', '2023-01-05', '2023-01-14', 600, 650, 'Short', 15, 7),
(6, 3, 'FB', '2023-01-06', '2023-01-15', 300, 290, 'Long', 20, 8),
(7, 4, 'JPM', '2023-01-07', '2023-01-16', 100, 105, 'Short', 30, 9),
(8, 5, 'V', '2023-01-08', '2023-01-17', 180, 190, 'Long', 25, 3),
(9, 6, 'WMT', '2023-01-09', '2023-01-18', 120, 130, 'Short', 22, 2),
(10, 2, 'PG', '2023-01-10', '2023-01-19', 90, 95, 'Long', 33, 1);

INSERT INTO Performance_Metric(Metrics_ID, User_ID, ROI,

Average_Profit_Loss, Win_Loss_Ratio)
VALUES (1, 1, 0.1, 50, 0.8),
(2, 1, 0.2, 60, 0.7),
(3, 2, 0.3, 70, 0.6),
(4, 2, 0.4, 80, 0.5),
(5, 3, 0.5, 90, 0.9),
(6, 3, 0.6, 100, 0.4),
(7, 4, 0.7, 110, 0.3),
(8, 5, 0.8, 120, 0.2),
(9, 6, 0.9, 130, 0.1),
(10, 2, 0.5, 100, 0.9);

INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,

Strategy_Tag, Attachments) VALUES
(1, 1, 'Good Trade', 'Calm', 'Breakout', '[Link]'),
(2, 2, 'Bad Trade', 'Nervous', 'Mean Reversion', '[Link]'),
(3, 3, 'Average Trade', 'Neutral', 'Momentum',
'[Link]'), (4, 4, 'Excellent Trade', 'Excited',
'Breakout', '[Link]'), (5, 5, 'Poor Trade', 'Anxious',
'Scalping', '[Link]'),
(6, 6, 'Decent Trade', 'Content', 'Trend Following',
'[Link]'), (7, 7, 'Good Trade', 'Calm', 'Breakout',
'[Link]'),
(8, 8, 'Bad Trade', 'Frustrated', 'Reversal', '[Link]'),
(9, 9, 'Okay Trade', 'Indifferent', 'Range Trading', '[Link]'),
(10, 10, 'Average Trade', 'Neutral', 'Momentum', '[Link]');
 [drop_tables.sh]
#!/bin/sh

export LD_LIBRARY_PATH=/home/yourusername/instantclient_12_1

username="p59nguye"

password="05013277"

sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(H ost=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))" <
./[Link]

[[Link]]
DROP TABLE Journal_Entry;
DROP TABLE
Performance_Metrics; DROP
TABLE Trade;
DROP TABLE
Stock; DROP
TABLE User;

[[Link]]
#!/bin/sh

# Environment setup
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib

# Database credentials
username="p59nguye"
password="05013277"

# Execute SQL function

execute_sql() {
local query_file=$1
sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(H ost=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))" <
$query_file
}

# Menu options
echo '1. Calculates the average ROI for each stock'
echo '2. Lists all users who have ONLY placed long
trades' echo '3. Lists all users who have ONLY placed
short trades' echo '4. Lists the top 5 most frequently
traded stocks'
echo '5. Lists stocks that are above the average trading
volume' echo '6. Retrieve All Stock Information'

# Read user input

read -p "Choose an option: " input

# Associative array to map user input to query

files declare -A query_map
query_map[1]="query1.t
xt"
query_map[2]="query2.t
xt"
query_map[3]="query3.t
xt"
query_map[4]="query4.t
xt"
query_map[5]="query5.t
xt"

# Execute corresponding SQL based on user

input if [[ ${query_map[$input]} ]]; then
execute_sql $
{query_map[$input]} else
echo "Invalid input."
fi

[[Link]]
SELECT Stock_Symbol, AVG(Exit_Price - Entry_Price) / Entry_Price AS
Average_ROI
FROM Trade
GROUP BY Stock_Symbol;

[[Link]]
SELECT DISTINCT User_ID
FROM Trade T1
WHERE NOT
EXISTS (
SELECT 1
FROM Trade T2
WHERE T2.User_ID = T1.User_ID AND [Link] = 'short'
);
[[Link]]
SELECT DISTINCT User_ID
FROM Trade T1
WHERE NOT EXISTS
( SELECT 1
FROM Trade T2
WHERE T2.User_ID = T1.User_ID AND [Link] = 'long'
);

[[Link]]
SELECT Stock_Symbol, COUNT(*) AS
Trade_Count FROM Trade
GROUP BY Stock_Symbol
ORDER BY Trade_Count
DESC LIMIT 5;

[[Link]]
SELECT Stock_Symbol, AVG(Volume) AS Average_Volume
FROM Trade
GROUP BY Stock_Symbol
HAVING AVG(Volume) > (SELECT AVG(Volume) FROM Trade);

[[Link]]
SELECT * FROM Stock;
Database Normalization:

ENTITY TABLES

1. User Entity

USER (User_ID, First_Name, Last_Name, Email, Encrypted_Password)

Functional
Dependency: User_ID First_Name, Last_Name, Email, Encrypted_Password

Reasoning: In a user table, typically, each user is assigned a unique identifier

(User_ID). This ID is not shared between users and thus uniquely determines
the user's personal information like their first and last names, email address, and
encrypted password. No two users should share the same ID, hence the direct
dependency.
 ● This table is 1NF because all values are atomic.
● This table is 2NF because all non-key attributes are fully
functionally dependent on the primary key.
● This table is 3NF because all non-key attributes are non-
transitively dependent on the primary key.

● This table is BCNF because every determinant, which is User_ID in

this case, is a candidate key.

2. Trade Entity
TRADE (Trade_ID, User_ID, Stock_Symbol, Entry_Date, Exit_Date,
Entry_Price, Exit_Price, Position, Volume, Commission_Fee)

Functional
Dependency:
Trade_ID User_ID, Stock_Symbol, Entry_Date, Exit_Date, Entry

Reasoning: Each trade is recorded with a unique trade identifier (Trade_ID).

This ID is unique to each trade transaction and defines all the details of that
trade, such as which user made the trade (User_ID), what stock was traded
(Stock_Symbol), and the specifics of the trade like dates, prices, position,
volume, and fees. The Trade_ID does not repeat for different trades, and thus
each set of trade details is associated with exactly one Trade_ID.
 ● This table is 1NF because all values are atomic.
● This table is 2NF because all non-key attributes are fully
functionally dependent on the primary key.
● This table is 3NF because all non-key attributes are non-
transitively dependent on the primary key.

● This table is BCNF because the primary key Trade_ID is the

only determinant, and it is a candidate key.

3. Stock Entity
STOCK (Stock_Symbol, Company_Name, Sector)

Functional Dependency: Stock_Symbol Company_Name, Sector

Reasoning: The stock symbol is a unique abbreviation used to represent

publicly traded shares of a particular stock on a particular stock market. Each
symbol uniquely identifies the company and, by extension, the sector the
company operates in. No two companies on the same stock exchange have the
same symbol.

● This table is 1NF because all values are atomic.

● This table is 2NF because all non-key attributes are fully
functionally dependent on the primary key.
● This table is 3NF because all non-key attributes are non-
transitively dependent on the primary key.

● This table is BCNF because Stock_Symbol is the determinant and is a

 candidate key.

4. Performance Metrics Entity

PERFORMANCE METRICS (Metric_ID, User_ID, ROI,

Average_Profit_Loss, Win_Loss_Ratio)

Functional
Dependency: Metric_ID User_ID, ROI, Average_Profit_Loss, Win_Loss_Ratio

Reasoning: The performance metrics are likely recorded with a unique

identifier (Metrics_ID). This ID will correspond to a specific user's trading
performance (User_ID) and the associated metrics like ROI, average profit/loss,
and win/loss ratio. Each set of performance metrics is unique and is not
influenced by other users' data.
 ● This table is 1NF because all values are atomic.
● This table is 2NF because all non-key attributes are fully
functionally dependent on the primary key.
● This table is 3NF because all non-key attributes are non-
transitively dependent on the primary key.

● This table is BCNF because Metrics_ID is the only determinant and

is a candidate key.

5. Journal Entry Entity

JOURNAL ENTRY (Journal_ID, Trade_ID, Notes, Emotions, Strategy_Tag,
Attachments)

Functional
Dependency: Journal_ID Trade_ID, Notes, Emotions, Strategy_Tag, Attachme

Reasoning: Journal entries are typically used to record subjective observations

and notes related to trades. Each journal entry has a unique identifier
(Journal_ID) which correlates to one particular trade (Trade_ID) and includes
the trader's notes, emotional state, strategy used (Strategy_Tag), and any
attachments. These entries are specific to each trade and do not overlap with
others.
● This table is 1NF because all values are atomic.
● This table is 2NF because all non-key attributes are fully
functionally dependent on the primary key.
● This table is 3NF because all non-key attributes are non-
transitively dependent on the primary key.

● This table is BCNF because Journal_ID is the determinant and

is a candidate key.
Source Code Of Application

[Link]

import [Link].*;

public class DatabaseConnection

{ public Connection connection =
null;

public boolean createConnection() {

try {
[Link]("[Link]");
} catch (ClassNotFoundException e)
{ [Link]();
return false;
}
try {
String dbURL =
"jdbc:oracle:thin:p59nguye/05013277@[Link]:or
d";
connection = [Link](dbURL);

[Link]("Connected!")
; return true;
} catch (SQLException
e)
{ [Link]();
return false;
}
}

public ResultSet executeQuery(String query) {

 try {
Statement statement = [Link]();
return [Link](query);
} catch (SQLException
e)
{ [Link]();
return null;
}
}

public void executeAndDisplayQuery(String query) {

try (Statement statement =
[Link](); ResultSet resultSet =
[Link](query)) {
ResultSetMetaData metaData =
[Link](); int columnCount =
[Link]();

while ([Link]()) {
for (int i = 1; i <= columnCount; i++)
{ String value =
[Link](i);
[Link](value + " ");
}
[Link]();
}
} catch (SQLException e)
{ [Link]();
}
}
}
 [Link]
import [Link];
import
[Link];
import [Link];
import [Link];
import
[Link];
import [Link];
import [Link];
import [Link].*;
import
[Link];
public class Menu {
private JFrame mainMenuFrame;
DatabaseConnection databaseConnection = new DatabaseConnection();
public static void main(String[] args) {
menuRunnable();
}
public static void
menuRunnable()
{ [Link](() ->
{ try {
Menu menuWindow = new Menu();
[Link](true);
} catch (Exception e)
{ [Link]();
}
});
}
public Menu() {
if ([Link]())
 { createMainMenuWindow();
}

}
private void createMainMenuWindow()
{ mainMenuFrame = new JFrame();
[Link]("Trading Journal Database System");
[Link](100, 100, 480, 300);
[Link]().setLayout(null);
[Link](JFrame.EXIT_ON_CLOSE);
JLabel tradingSystemLabel = new JLabel("Trading Journal Database
System"); [Link](new Font("Garamond", [Link],
24)); [Link](10, 0, 300, 50);
[Link]().add(tradingSystemLabel);
JButton dropTablesButton = new JButton("Drop Tables");
[Link](new Color(139, 0, 0));
[Link](new Font("Garamond", [Link], 16));
[Link](10, 60, 140, 24);
[Link]().add(dropTablesButton);
JButton createTablesButton = new JButton("Create Tables");
[Link](new Color(0, 128, 0));
[Link](new Font("Garamond", [Link], 16));
[Link](160, 60, 140, 24);
[Link]().add(createTablesButton);
JButton populateTablesButton = new JButton("Insert Data");
[Link](new Color(0, 191, 255));
[Link](new Font("Garamond", [Link], 16));
[Link](310, 60, 140, 24);
[Link]().add(populateTablesButton);
[Link](
mainMenuFrame, databaseConnection,
dropTablesButton); [Link](
mainMenuFrame, databaseConnection, createTablesButton);
[Link](
mainMenuFrame, databaseConnection, populateTablesButton);
JButton viewTablesButton = new JButton("View");
[Link](new Color(0, 0, 0));
[Link](new Font("Garamond", [Link], 18));
[Link](10, 100, 140, 140);
[Link]().add(viewTablesButton);
JButton queryTablesButton = new JButton("Query");
[Link](new Color(0, 0, 0));
[Link](new Font("Garamond", [Link], 18));
[Link](160, 100, 140, 140);
[Link]().add(queryTablesButton);
JButton updateTablesButton = new JButton("Edit");
[Link](new Color(0, 0, 0));
[Link](new Font("Garamond", [Link], 18));
[Link](310, 100, 140, 140);
[Link]().add(updateTablesButton);
[Link](actionEvent -> {
ViewTables viewTable = new ViewTables();
[Link]();
[Link](false);
});
[Link](actionEvent -
> { QueryTables queryTable = new QueryTables();
[Link]();
[Link](false);
});
[Link](actionEvent -
> { UpdateTables updateTable = new
UpdateTables();
[Link]();
 [Link](false);
});
JButton backButton = new JButton("<");
[Link](new Font("Garamond", [Link],
14)); [Link](400, 15, 50, 20);
[Link]().add(backButton);
[Link](actionEvent -> {
Login login = new Login();
[Link]();
[Link]();
});
}
public static void executeButtonActionEvent(JButton tableButton,
DatabaseConnection databaseConnection, String query)
{ [Link](actionEvent -> {
try {
ResultSet queryResult = [Link](query);
JTable queryResultTable = new
JTable(buildTableModel(queryResult));
[Link](null, new JScrollPane(queryResultTable));
} catch (SQLException e)
{ [Link]();
}
});
}
public static DefaultTableModel buildTableModel(ResultSet queryResult)
throws SQLException {
ResultSetMetaData queryMetaData =
[Link](); int columnCount =
[Link](); Vector<String>
columnNames = new Vector<>(); Vector<Vector<Object>>
queryDataVector = new Vector<>();
 for (int columnNumber = 1; columnNumber <= columnCount; columnNumber++)
{ [Link]([Link](columnNumber));
}
while ([Link]()) {
Vector<Object> tempDataVector = new Vector<>();
for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++)
{ [Link]([Link](columnIndex));
}
[Link](tempDataVector);
}
return new DefaultTableModel(queryDataVector, columnNames);
}
}

[Link]
import [Link].*;

public class DropTables {

public static void executeDropQueries(JFrame mainMenuFrame,
DatabaseConnection databaseConnection, JButton dropTablesButton)
{ [Link](actionEvent -> {
// Drop the tables related to your database
[Link](
"DROP TABLE Journal_Entry CASCADE CONSTRAINTS PURGE");
[Link](
"DROP TABLE Performance_Metrics CASCADE CONSTRAINTS PURGE");
[Link](
"DROP TABLE Stock CASCADE CONSTRAINTS PURGE");
[Link](
 "DROP TABLE Trade CASCADE CONSTRAINTS PURGE");
[Link](
"DROP TABLE User CASCADE CONSTRAINTS PURGE");

// Commit the transaction

[Link]("COMMIT");

// Show a confirmation message

[Link](
mainMenuFrame, "All Specified Tables Dropped");
});
}
}

[Link]
import [Link].*;

public class CreateTables {

public static void executeCreateQueries(JFrame mainMenuFrame,
DatabaseConnection databaseConnection, JButton createTablesButton) {
[Link](actionEvent -> {
// Create User table
[Link]("CREATE TABLE User
("
+ "User_ID NUMBER PRIMARY KEY, "
+ "First_Name VARCHAR2(50) NOT NULL, "
+ "Last_Name VARCHAR2(50) NOT NULL, "
+ "Email VARCHAR2(50) NOT NULL UNIQUE, "
+ "Encrypted_Password VARCHAR2(50) NOT NULL)");
// Create Trade table
[Link]("CREATE TABLE Trade
("
+ "Trade_ID NUMBER PRIMARY KEY, "
+ "User_ID NUMBER NOT NULL REFERENCES User(User_ID), "
+ "Stock_Symbol VARCHAR2(10) NOT NULL, "
+ "Entry_Date DATE NOT NULL, "
+ "Exit_Date DATE, "
+ "Entry_Price NUMBER NOT NULL, "
+ "Exit_Price NUMBER, "
+ "Position VARCHAR2(10) NOT NULL, "
+ "Volume NUMBER NOT NULL, "
+ "Commission_Fee NUMBER NOT NULL)");

// Create Stock table

[Link]("CREATE TABLE Stock
("
+ "Stock_Symbol VARCHAR2(10) PRIMARY KEY, "
+ "Company_Name VARCHAR2(50) NOT NULL, "
+ "Sector VARCHAR2(50) NOT NULL)");

// Create Performance Metrics table

[Link]("CREATE TABLE Performance_Metrics
("
+ "Metrics_ID NUMBER PRIMARY KEY, "
+ "User_ID NUMBER NOT NULL REFERENCES User(User_ID), "
+ "ROI NUMBER NOT NULL, "
+ "Average_Profit_Loss NUMBER NOT NULL, "
+ "Win_Loss_Ratio NUMBER NOT NULL)");

// Create Journal Entry table

[Link]("CREATE TABLE Journal_Entry
("
+ "Journal_ID NUMBER PRIMARY KEY, "
 + "Trade_ID NUMBER NOT NULL REFERENCES Trade(Trade_ID), "
+ "Notes VARCHAR2(255), "
+ "Emotions VARCHAR2(50), "
+ "Strategy_Tag VARCHAR2(50), "
+ "Attachments BLOB)");

// Show a confirmation message

[Link](mainMenuFrame, "All Tables
Created!");
});
}
}

[Link]
import [Link].*;

public class PopulateTables {

public static void executePopulateDataQueries(JFrame mainMenuFrame,
DatabaseConnection databaseConnection, JButton populateTablesButton)
{ [Link](
actionEvent -> {
// Insert data into User table
String[] userInserts = {
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (1, 'John', 'Doe', '[Link]@[Link]',
'password1')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (2, 'Jane', 'Doe', '[Link]@[Link]', 'password2')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (3, 'Alice', 'Brown', '[Link]@[Link]',
'password3')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (4, 'Bob', 'Smith', '[Link]@[Link]', 'password4')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (5, 'Charlie', 'Johnson', '[Link]@[Link]',
'password5')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (6, 'Dave', 'Williams', '[Link]@[Link]',
'password6')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (7, 'Eve', 'Jones', '[Link]@[Link]',
'password7')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (8, 'Frank', 'Garcia', '[Link]@[Link]',
'password8')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (9, 'Grace', 'Martinez', '[Link]@[Link]',
'password9')",
"INSERT INTO User(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES (10, 'Emily', 'Smith', '[Link]@[Link]',
'password10')"};
for (String insertQuery : userInserts)
{ [Link](insertQuery);
}

// Insert data into Stock table

String[] stockInserts = {
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('AAPL',
'Apple Inc.', 'Technology')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES
('GOOGL', 'Google', 'Technology')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES
('AMZN', 'Amazon', 'Retail')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('MSFT',
'Microsoft', 'Technology')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('TSLA',
'Tesla', 'Automotive')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('FB',
'Facebook', 'Technology')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('JPM',
'JPMorgan Chase', 'Finance')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('V',
'Visa', 'Finance')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('WMT',
'Walmart', 'Retail')",
"INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES ('PG',
'Procter & Gamble', 'Consumer Goods')"};
for (String insertQuery : stockInserts)
{ [Link](insertQuery);
}

// Insert data into Trade

table String
[] tradeInserts =
{"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (1, 1,
'AAPL', TO_DATE('2023-01-01', 'YYYY-MM-DD'), TO_DATE('2023-01-10',
'YYYY-MM-DD'), 150, 160, 'Long', 10, 5)",
"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (2, 1,
'GOOGL', TO_DATE('2023-01-02', 'YYYY-MM-DD'), TO_DATE('2023-01-11',
'YYYY-MM-DD'), 2000, 2100, 'Long', 5, 10)",
"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (3, 1,
'AMZN', TO_DATE('2023-01-03', 'YYYY-MM-DD'), TO_DATE('2023-01-12',
'YYYY-MM-DD'), 1800, 1850, 'Short', 8, 6)",
"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (4, 2,
'MSFT', TO_DATE('2023-01-04', 'YYYY-MM-DD'), TO_DATE('2023-01-13',
'YYYY-MM-DD'), 200, 205, 'Long', 12, 4)",
"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (5, 3,
'TSLA', TO_DATE('2023-01-05', 'YYYY-MM-DD'), TO_DATE('2023-01-14',
'YYYY-MM-DD'), 600, 650, 'Short', 15, 7)", "INSERT INTO Trade(Trade_ID, User_ID,
Stock_Symbol, Entry_Date, Exit_Date, Entry_Price, Exit_Price, Position, Volume,
Commission_Fee) VALUES (6, 3, 'FB', TO_DATE('2023-01-06', 'YYYY-MM-DD'),
TO_DATE('2023-01-15', 'YYYY-MM-DD'), 300, 290, 'Long', 20, 8)", "INSERT INTO
Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date, Exit_Date, Entry_Price, Exit_Price,
Position, Volume, Commission_Fee) VALUES (7, 4, 'JPM', TO_DATE('2023-01-07',
'YYYY-MM-DD'), TO_DATE('2023-01-16', 'YYYY-MM-DD'), 100, 105, 'Short', 30, 9)",
"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date, Exit_Date,
Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (8, 5, 'V',
TO_DATE('2023-01-08', 'YYYY-MM-DD'), TO_DATE('2023-01-17', 'YYYY-MM-DD'),
180,
190, 'Long', 25, 3)", "INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (9, 6,
'WMT', TO_DATE('2023-01-09', 'YYYY-MM-DD'), TO_DATE('2023-01-18',
'YYYY-MM-DD'), 120, 130, 'Short', 22, 2)",
"INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee) VALUES (10, 2,
'PG', TO_DATE('2023-01-10', 'YYYY-MM-DD'), TO_DATE('2023-01-19', 'YYYY-MM-
DD'), 90, 95, 'Long', 33, 1)"};

for (String insertQuery : tradeInserts)

{ [Link](insertQuery);
 }

// Insert data into Performance Metrics table

String[] performanceMetricsInserts = {
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (1, 1, 0.1, 50, 0.8)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (2, 1, 0.2, 60, 0.7)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (3, 2, 0.3, 70, 0.6)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (4, 2, 0.4, 80, 0.5)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (5, 3, 0.5, 90, 0.9)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (6, 3, 0.6, 100, 0.4)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (7, 4, 0.7, 110, 0.3)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (8, 5, 0.8, 120, 0.2)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (9, 6, 0.9, 130, 0.1)",
"INSERT INTO Performance_Metrics(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES (10, 2, 0.5, 100, 0.9)"};
for (String insertQuery : performanceMetricsInserts)
{ [Link](insertQuery);
}

// Insert data into Journal Entry table

String[] journalEntryInserts = {
 "INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (1, 1, 'Good Trade', 'Calm', 'Breakout', '[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (2, 2, 'Bad Trade', 'Nervous', 'Mean Reversion',
'[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (3, 3, 'Average Trade', 'Neutral', 'Momentum',
'[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (4, 4, 'Excellent Trade', 'Excited', 'Breakout',
'[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (5, 5, 'Poor Trade', 'Anxious', 'Scalping',
'[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (6, 6, 'Decent Trade', 'Content', 'Trend
Following', '[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (7, 7, 'Good Trade', 'Calm', 'Breakout', '[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (8, 8, 'Bad Trade', 'Frustrated', 'Reversal',
'[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (9, 9, 'Okay Trade', 'Indifferent', 'Range
Trading', '[Link]')",
"INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES (10, 10, 'Average Trade', 'Neutral',
'Momentum', '[Link]')"};

for (String insertQuery : journalEntryInserts)

{ [Link](insertQuery);
}
 // Show a confirmation message
[Link](mainMenuFrame, "All Tables Populated!");
});
}
}

[Link]
import
[Link];
import [Link];
import
[Link];
import [Link];
import
[Link];
import
[Link];
import
[Link];

public class ViewTables {

private JFrame viewTableFrame;
DatabaseConnection databaseConnection = new DatabaseConnection();
private static List<String> queryNames = new ArrayList<>();
private static List<String> queryCommands = new ArrayList<>();

public static void viewTableRunnable()

{ [Link](() -> {
try {
ViewTables viewTableWindow = new ViewTables();
[Link](true);
} catch (Exception e)
{ [Link]();
}
});
}

public ViewTables() {
if ([Link]())
{ createViewTableWindow();
}
}
private void createViewTableWindow()
{ viewTableFrame = new JFrame();
[Link]("View Tables");
[Link](100, 100, 360, 500);
[Link]().setLayout(null
);
[Link](JFrame.EXIT_ON_CLOSE);

JLabel tableLabel = new JLabel("Trading System Tables");

[Link](new Font("Garamond", [Link],
24)); [Link](10, 0, 340, 50);
[Link]().add(tableLabel);

[Link]("User");
[Link]("Trade");
[Link]("Stock");
[Link]("Performance
Metrics"); [Link]("Journal
Entry");

[Link]("SELECT * FROM USER");

[Link]("SELECT * FROM TRADE");
[Link]("SELECT * FROM STOCK");
[Link]("SELECT * FROM PERFORMANCE_METRICS");
[Link]("SELECT * FROM JOURNAL_ENTRY");

int yPosition = 50;

for (int i = 0; i < [Link](); i++) {
JButton tableButton = new JButton([Link](i));
[Link](new Font("Garamond", [Link],
18)); [Link](50, yPosition, 240, 30);
[Link]().add(tableButton);
yPosition += 40;
int finalI = i;
[Link](actionEvent -> {
[Link]([Link](finalI));
});
}

JButton backButton = new JButton("<");

[Link](new Font("Garamond", [Link],
14)); [Link](280, 15, 50, 20);
[Link]().add(backButton);
[Link](actionEvent -> {
Menu menu = new Menu();
[Link]();
[Link]();
});
 }
}

[Link]
import
[Link];
import [Link];
import
[Link];
import [Link];
import [Link].*;

public class QueryTables {

private JFrame queryTablesFrame;
private DatabaseConnection databaseConnection = new DatabaseConnection();
private static List<String> queryNames = new ArrayList<>();
private static List<String> queryCommands = new ArrayList<>();

public static void queryTableRunnable()

{ [Link](() -> {
try {
QueryTables queryTablesWindow = new QueryTables();
[Link](true);
} catch (Exception e)
{ [Link]();
}
});
}

public QueryTables() {
if ([Link]())
{ createQueryTablesWindow();
}
}

private void createQueryTablesWindow()

{ queryTablesFrame = new JFrame();
[Link]("Query Tables");
[Link](100, 100, 480, 550);
[Link]().setLayout(null);
[Link](JFrame.EXIT_ON_CLOSE);

JLabel queryLabel = new JLabel("Trading System

Queries"); [Link](new Font("Garamond",
[Link], 24)); [Link](10, 0, 460, 50);

[Link]().add(queryLabel);
 // Example queries for the trading system
[Link]("1. List of All Users");
[Link]
import
[Link];
import [Link];

import [Link];
import [Link];
import [Link];
import
[Link];
import [Link];

public class UpdateTables {

private JFrame updateTableFrame;
DatabaseConnection databaseConnection = new DatabaseConnection();

public void updateTablesRunnable()

{ [Link](() -> {
try {
UpdateTables updateTableWindow = new UpdateTables();
[Link](true);
} catch (Exception e)
{ [Link]();
}
});
}

public UpdateTables() {
if ([Link]())
{ createUpdateTablesWindow();
}
}

private void createUpdateTablesWindow()

{ updateTableFrame = new JFrame();
[Link](100, 100, 480, 320);
[Link]().setLayout(null);
[Link](JFrame.EXIT_ON_CLOSE);

JLabel editLabel = new JLabel("Edit Trading System

Tables"); [Link](new Font("Garamond",
[Link], 24)); [Link](10, 0, 460, 50);
[Link]().add(editLabel);

createUpdateSection();
createInsertSection();
createRemoveSection();

JButton backButton = new JButton("<");

[Link](new Font("Garamond", [Link],
12)); [Link](400, 15, 50, 20);
[Link]().add(backButton);
 [Link](actionEvent ->
{ Menu menu = new Menu();
[Link]();
[Link]();
});
}

private void createUpdateSection() {

// Update Section
JButton updateButton = new JButton("Update");
[Link](new Font("Garamond", [Link], 16));
[Link](20, 60, 100, 30);
[Link]().add(updateButton);
JTextField updateTextField = new JTextField("UPDATE table-name");
[Link](140, 60, 300, 20);
[Link]().add(updateTextField);
JTextField setTextField =
new JTextField("SET column-name = value, column-name = value, ...");
[Link](140, 80, 300, 20);
[Link]().add(setTextField);
JTextField whereTextField = new JTextField("WHERE condition");
[Link](140, 100, 300, 20);
[Link]().add(whereTextField);
[Link](actionEvent
-> executeUpdate(updateTextField, setTextField, whereTextField));
}

private void createInsertSection() {

// Insert Section
JButton insertButton = new JButton("Insert");
[Link](new Font("Garamond", [Link], 16));
[Link](20, 140, 100, 30);
[Link]().add(insertButton);
JTextField insertIntoTextField = new JTextField("INSERT INTO table-name");
[Link](140, 140, 300, 20);
[Link](500);
[Link]().add(insertIntoTextField);
JTextField insertAttributeNamesTextField = new JTextField("(column-
names)"); [Link](140, 160, 300, 20);
[Link](500);
[Link]().add(insertAttributeNamesTextField);
JTextField insertValuesTextField = new JTextField("VALUES (values)");
[Link](140, 180, 300, 20);
[Link](500);
[Link]().add(insertValuesTextField);
[Link](actionEvent
 -> executeInsert(insertIntoTextField, insertAttributeNamesTextField,
insertValuesTextField));
}

private void createRemoveSection() {

// Remove Section
JButton removeButton = new JButton("Remove");
[Link](new Font("Garamond", [Link], 16));
[Link](20, 220, 100, 30);
[Link]().add(removeButton);
JTextField deleteTableTextField = new JTextField("DELETE table-
name"); [Link](140, 220, 300, 20);
[Link](500);
[Link]().add(deleteTableTextField);
JTextField deleteConditionTextField = new JTextField("WHERE condition");
[Link](140, 240, 300, 20);
[Link](500);
[Link]().add(deleteConditionTextField);
[Link](actionEvent
-> executeRemove(deleteTableTextField, deleteConditionTextField));
}

private void executeUpdate(JTextField

updateTextField, JTextField setTextField, JTextField
whereTextField) { String updateTextString =
[Link](); String setTextString =
[Link]();
String whereTextString =
[Link](); String queryStatement
=
updateTextString + " " + setTextString + " " + whereTextString;
[Link](queryStatement);
[Link](updateTableFrame, "Updated!");
}

private void executeInsert(JTextField

insertIntoTextField, JTextField
insertAttributeNamesTextField,
JTextField insertValuesTextField) {
String insertIntoString = [Link]();
String insertAttributeNamesString =
[Link](); String insertValuesString =
[Link]();
String queryStatement = insertIntoString + " " + insertAttributeNamesString
+ " " + insertValuesString;
[Link](queryStatement);
[Link](updateTableFrame, "Inserted!");
}

private void executeRemove(

 JTextField deleteTableTextField, JTextField deleteConditionTextField)
{ String deleteTableString = [Link]();
String deleteConditionString = [Link]();
String queryStatement = deleteTableString + " " +
deleteConditionString;
[Link](queryStatement);
[Link](updateTableFrame, "Removed!");
}
}

Relational Algebra For Queries

1. Calculate the Average ROI for Each Stock

SELECT Stock_Symbol, AVG(Exit_Price - Entry_Price) / Entry_Price AS

Average_ROI
FROM Trade
GROUP BY Stock_Symbol;

π ¿ Average_ROI)(Trade)

2. Lists All Users Who Have Only Placed Long Trades

SELECT DISTINCT User_ID

FROM Trade T1
WHERE NOT EXISTS (
SELECT 1
FROM Trade T2
WHERE T2.User_ID = T1.User_ID AND [Link] = 'short'
);

π (User ID )(Trade T 1)−π (User ID )(σ (Position=' short ')(Trade T 2))

 3. Lists All Users Who Have ONLY Placed Short Trades

SELECT DISTINCT User_ID

FROM Trade T1
WHERE NOT EXISTS (
SELECT 1
FROM Trade T2
WHERE T2.User_ID = T1.User_ID AND [Link] = 'long'
);

π (User ID )(Trade T 1)−π (User ID )(σ (Position=' long ')(Trade T 2))

4. Lists the Top 5 Most Frequently Traded Stocks

SELECT Stock_Symbol, COUNT(*) AS Trade_Count

FROM Trade
GROUP BY Stock_Symbol
ORDER BY Trade_Count DESC
LIMIT 5;

τ (TradeCount DESC )(π (Stock Symbol ,COUNT (¿)→Trade Count )(Trade))

5. Lists Stocks That Are Above the Average Trading Volume

SELECT Stock_Symbol, AVG(Volume) AS Average_Volume

FROM Trade
 GROUP BY Stock_Symbol
HAVING AVG(Volume) > (SELECT AVG(Volume) FROM Trade);

π ¿)(σ (AVG (Volume))(Trade )¿(Trade)¿

6. Retrieve All Stock Information

SELECT * FROM Stock;

π (¿)(Stock)

Conclusion

The development of the Trading Journal Database System, from its initial

concept to the final implementation, has significantly enhanced my understanding of

database systems in the context of financial trading. Through the incremental

development of the database over several weeks, I have gained a comprehensive

understanding of the intricacies involved in creating a fully-functioning database

tailored to trading analysis. The skills I have developed and refined throughout this

project are not only pivotal for my current academic pursuits but also immensely

relevant for real-world financial database management. I am now confident in my

ability to apply these skills to similar projects in the future, contributing valuable

insights into trading strategies and market analysis