Question

This is all one question:

1.   Given the following table that is used by an investment firm to keep track of stock transactions (buy/sell) for clients

Transaction (Stock Symbol, StockName, Trans-Date, Trans-time, Number_of_Shares, Transaction_type, Trans-Price, AccountNo, ClientName)  

Note,

The investment firm have many clients who may have multiple accounts. But each account only associates with one client. An account includes multiple stocks.  

On a trading day, a client can buy or sell any number of stock in his/her account. A client can only do a one tranction in one account at a time. Transactions from different clients may happen at the same time.

-List all FD, including Multi-valued Dependency (MVD)

-What is the KEY of Transaction table?

-What normal form this table is in? (Note, only if you have all FDs and know the key/candidate key, you can then answer this question).

-How many THEMES are in this table and what are they?

- Normalize this table, show the result of normalization. Note. The resulting tables must have KEY and FK so that the information carried in original table is kept by the resulting tables.

Answer 1

Transaction (Stock Symbol, StockName, Trans-Date, Trans-time, Number_of_Shares, Transaction_type, Trans-Price, AccountNo, ClientName)  

The functional dependencies in the given relation are

StockSymbol --> StockName

AccountNo -->  ClientName

Trans-Date, Trans-time , AccountNo --> StockSymbol

AccountNo , StockSymbol, Trans-Date, Trans-time --> Number_of_Shares, Transaction_type, Trans-Price

The Key of the Transaction table is

A composite key which is:

(StockSymbol, AccountNo, Trans-Date, Trans-time)

These four attributes combinedly identify each row of the Transaction table uniquely. Also each row contains atomic values.

Hence the given relation is in 1NF.

The table is not in 2NF (or higher normal forms). Since from the above lists of functional dependencies we observe that there exist functional dependencies in the table where LHS of the dependency is a subset of the primary key.

There are three different themes in the given table. In the given table they are Stock, Transaction, and Client

The given table can be decomposed as follows for the purpose of normalization without loss of information

Stock(StockSymbol, StockName)

Primary key: StockSymbol

Client(AccountNo, ClientName)

Primary key: AccountNo

Transaction(Transaction_id, Trans-Ddate, Trans-time, AccountNo, StockSymbol)

Primary key: Transaction_id

Foreign Key: AccountNo references Client(AccountNo)

Foreign Key: StockSymbol references Stock(StockSymbol)

TransactionDetails(Transaction_id, Number_of_Shares, Transaction_type, Trans-Price)

Primary key: Transaction_id,

Foreign Key: Transaction_id references Transaction(Transaction_id)

N.B: For any further clarification please contact through comments. Thak you.