Data Science for Finance database question

Sample Solution

FinTrack Database Schema

Entities and Attributes:

1. User:

   • user_id (PK): Integer (Primary Key)
   • name: String
   • email: String
   • username: String (Unique)
   • password (hashed): String
   • registration_date: Date/Time

2. Transaction:

   • transaction_id (PK): Integer (Primary Key)
   • user_id (FK): Integer (Foreign Key references User.user_id)
   • description: String
   • amount: Decimal
   • date: Date/Time
   • category: String
   • type (income/expense): String

Full Answer Section

1. Investment:

   • investment_id (PK): Integer (Primary Key)
   • user_id (FK): Integer (Foreign Key references User.user_id)
   • name: String
   • symbol: String
   • quantity: Decimal
   • purchase_price: Decimal
   • purchase_date: Date/Time
   • current_market_value: Decimal

2. Budget:

   • budget_id (PK): Integer (Primary Key)
   • user_id (FK): Integer (Foreign Key references User.user_id)
   • name: String
   • category: String
   • amount: Decimal
   • duration (monthly/quarterly): String

3. Bank Account:

   • account_id (PK): Integer (Primary Key)
   • user_id (FK): Integer (Foreign Key references User.user_id)
   • account_name: String
   • account_number: String (Unique)
   • bank_name: String
   • current_balance: Decimal

Relationships:

• One user can have many transactions, investments, budgets, and bank accounts.
• One transaction, investment, budget, or bank account belongs to one user.

Additional Notes:

• This schema provides a basic structure for the FinTrack application. Additional tables and attributes can be added as needed.
• Consider implementing appropriate data types and constraints for each attribute.
• Security measures like password hashing and data encryption should be implemented for sensitive data.

Note: This is a sample schema and does not represent a complete and secure database design. You may need to modify it based on specific requirements and security considerations.