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_Price, Exit_Price, Position,
Volume, Commission_Fee
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, Attachments
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.