───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───
Part 1
The only real “assumption” that I made during the diagramming process is some of the relationships, specifically whether they should be one-to-one or one-to-many, but with some looking at the original SQL query to create the database, I think I did it correctly.
Part 2
DIPLOMATIC_INCIDENT
- INCIDENT_ID (CHAR(5)) PK
- PLANET (VARCHAR(20)) FK → PLANET.NAME
- INCIDENT_DATE (DATE)
- PARTIES_INVOLVED (VARCHAR(MAX))
- RESOLUTION_APPROACH (VARCHAR(MAX))
- PROTOCOL_VIOLATION (BIT)
- CULTURAL_MISUNDERSTANDING (VARCHAR(MAX))
MEDICAL_RECORD
- RECORD_ID (CHAR(5)) PK
- PERSON_ID (CHAR(5)) FK → PERSONNEL.ID
- EXAM_DATE (DATE)
- SPECIES (VARCHAR(30))
- FITNESS_STATUS (VARCHAR(20))
- LONG_TERM_CONDITIONS (VARCHAR(MAX))
TECHNOLOGY_EXCHANGE
- EXCHANGE_ID (CHAR(5)) PK
- TECH_NAME (VARCHAR(50))
- SPECIFICATIONS (VARCHAR(MAX))
- ORIGIN (VARCHAR(50))
- DESTINATION (VARCHAR(50))
- FEDERATION_PROTOCOL (VARCHAR(MAX))
- COMPATIBILITY_STATUS (BIT)
- PRIME_DIRECTIVE_COMPLIANCE (BIT)
MISSION_OUTCOME
- MISSION_ID (CHAR(5)) PK, FK → MISSION.ID
- DISCOVERY_TYPE (VARCHAR(30)) PK
- DESCRIPTION (VARCHAR(MAX))
- SUCCESS_METRICS (VARCHAR(MAX))
Part 3
For each expansion area, I tried to do whatever the question asked to the best I could—and I didn’t really go too far away from what it was asking. Some key choices was that I made the IDs CHAR(5) type rather than an INT or something else due to how the database was already setup.
I was also considering naming the columns as lowercase and the table names in PascalCase—but I went with the conventions already in the database, despite it not being what I would personally choose to do.
Some future expansions could be to add crew training records as the Starfleet expands, along with things that track starship locations and speeds. Moreover, something that would be good would be resource management, including supplies, energy, and materials for exploration, shipbuilding, or other missions.
───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───