Build a database schema with an entity-relation model for a specific business scenario in the iCapital context. Define entities for investor accounts, alternative funds, and investment commitments, including primary keys, foreign keys, appropriate data types, and integrity constraints. Then provide a query that summarizes approved commitments for active investors and open funds. Rows with a NULL commitment amount are ignored.
investor_id, investor_name, total_open_approved_commitment, and open_fund_count| Column | Type | Description |
|---|---|---|
| investor_idPK | BIGINT | Unique investor account identifier |
| investor_name | VARCHAR(150) | Investor or account name |
| VARCHAR(255) | Unique investor email address | |
| account_status | VARCHAR(20) | Account lifecycle status |
| created_at | DATE | Date the account was created |
| Column | Type | Description |
|---|---|---|
| fund_idPK | BIGINT | Unique alternative fund identifier |
| fund_name | VARCHAR(200) | Fund name |
| asset_class | VARCHAR(50) | Fund asset class |
| fund_strategy | VARCHAR(100) | Investment strategy |
| fund_status | VARCHAR(20) | Fund lifecycle status |
| minimum_commitment | NUMERIC(18,2) | Minimum commitment amount |
| Column | Type | Description |
|---|---|---|
| commitment_idPK | BIGINT | Unique commitment identifier |
| investor_id | BIGINT | Referenced investor account |
| fund_id | BIGINT | Referenced alternative fund |
| commitment_date | DATE | Date of the commitment |
| commitment_amount | NUMERIC(18,2) | Committed amount |
| commitment_status | VARCHAR(20) | Commitment processing status |