Your question is Create a Database Management System. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Create a database management system.
Implement the PostgreSQL data model and write a query that produces an administrative summary for every managed database. Include databases with no members or audit events, and treat revoked memberships as inactive.
database_name, owner_name, active_member_count, audit_event_count, latest_event_type, and latest_event_at.database_name ascending.| Column | Type | Description |
|---|---|---|
| database_idPK | INT | Unique managed database identifier |
| database_name | VARCHAR(100) | Display name of the managed database |
| owner_user_id | INT | User responsible for the database |
| created_at | TIMESTAMP | Database creation timestamp |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| display_name | VARCHAR(100) | User display name |
| VARCHAR(255) | User email address |
| Column | Type | Description |
|---|---|---|
| membership_idPK | INT | Unique membership identifier |
| database_id | INT | Managed database identifier |
| user_id | INT | Member user identifier |
| role | VARCHAR(30) | Membership role |
| granted_at | TIMESTAMP | Membership grant timestamp |
| revoked_at | TIMESTAMP | Membership revocation timestamp |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique audit event identifier |
| database_id | INT | Database associated with the event |
| user_id | INT | User who caused the event |
| event_type | VARCHAR(40) | Type of audited activity |
| event_at | TIMESTAMP | Event occurrence timestamp |