૭( ᵕ•̀ᵕ•́૭)

Lab 10

4 min read

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Views

CrewRosterView

  • Displays current ship assignments of personnel
  • Joins PERSONNEL, ASSIGNMENT, and STARSHIP
  • Includes name, rank, position, home planet, starship name
  • Filtering for active assignments was intended, but there wasn’t a STATUS column, so it was left commented out (since I didn’t see a way to filter active assignments)

FleetStatusView

  • Monitors ship deployment and crew levels
  • Joins STARSHIP and ASSIGNMENT
  • Includes registry, class, mission, location, crew count, commission date, ship age
  • Order by was skipped since SQL Server has limitations on ordering with views.
    • You have to use either a TOP, OFFSET, or FOR XML clause

MissionHistoryView

  • Tracks visits made by ships to planets
  • Joins VISIT, STARSHIP, and PLANET
  • Includes arrival/depart dates, duration (or “Currently Docked”), and planet attributes

QualificationMatrixView

  • Shows personnel qualifications and experience
  • Joins PERSONNEL and QUAL
  • Calculates the years of experience based on the qualification GRANTED column

Stored Procedures

TransferCrewMember

  • Reassigns a crew member to a new ship and position
  • Verifies existence → deletes old assignment → inserts a new one
  • Handles errors with TRY...CATCH, ROLLBACK, and THROW

RecordPlanetaryVisit

  • Records or updates planetary visits for ships
  • Checks ship and planet exist as well as the arrival being before the departure
  • Updates existing visits or inserts a new row if there isn’t a current visit

MissionResourceAllocation

  • Calculates needed resources for missions
  • Uses a temporary table #ResourcePlan to hold projections
  • Inputs are derived from STARSHIP, MISSION, and TYPE
  • Outputs energy, food, and medical supplies by crew size and priority

Functions

CalculateServiceDuration

  • Returns time between first assignment and a reference date
  • Returns a formatted string such as '3 years, 4 months'
  • If ASSIGNED is null, it defaults to the current date.
    • Since it doesn’t allow for GETDATE() as a default value, there’s a simple check

GetQualifiedPersonnel

  • Lists personnel with a given skill and experience threshold
  • Returns ID, name, rank, planet, years of experience, and current assignment (if any)

Design Decisions/Challenges

  • I wasn’t able to use ORDER BY in views since SQL Server requires TOP, OFFSET, or XML FOR.
  • There was no STATUS column in ASSIGNMENT, so I wasn’t able to filter active assignments
  • Since I wasn’t able to use GETDATE() as a default value, I had to use an IF check and redirect the logic slightly

Performance Considerations

  • Indexed views weren’t used due to joins and aggregates, but they could be optimized if needed
  • MissionHistoryView is lightweight; QualificationMatrixView uses joins for efficiency
  • DELETE + INSERT in TransferCrewMember avoids complex updating of data
  • #ResourcePlan is temporary to minimize permanent clutter within the database

Examples

The example output isn't based on actual data within the database

CrewRosterView

SELECT * FROM CrewRosterView WHERE STARSHIP_NAME = 'USS Voyager';
 
-- ┌────────────────┬──────┬────────────────┬─────────────┬───────────────┐
-- │ PERSONNEL_NAME │ RANK │ POSITION       │ HOME_PLANET │ STARSHIP_NAME │
-- ├────────────────┼──────┼────────────────┼─────────────┼───────────────┤
-- │ Tuvok          │ Ltnt │ Security Chief │ Vulcan      │ USS Voyager   │
-- │ Riker          │ LtCm │ Chief Engineer │ Kessik IV   │ USS Voyager   │
-- └────────────────┴──────┴────────────────┴─────────────┴───────────────┘

CalculateServiceDuration

SELECT dbo.CalculateServiceDuration('P0001'); -- '5 years, 3 months'

GetQualifiedPersonnel

SELECT * FROM dbo.GetQualifiedPersonnel('Astrophysics', 3);
 
-- ┌───────┬──────────────┬──────┬────────┬───────────────────┬───────────┬────────────────┬────────────┐
-- │ ID    │ NAME         │ RANK │ PLANET │ YearsOfExperience │ SHIP      │ POSITION       │ ASSIGNED   │
-- ├───────┼──────────────┼──────┼────────┼───────────────────┼───────────┼────────────────┼────────────┤
-- │ P0001 │ Spock        │ Cmdr │ Vulcan │ 5                 │ NCC-1701  │ Science Officer│ 2370-01-01 │
-- │ P0004 │ Saavik       │ Ltnt │ Romulus│ 4                 │ NCC-1702  │ Astrophysicist │ 2371-05-01 │
-- └───────┴──────────────┴──────┴────────┴───────────────────┴───────────┴────────────────┴────────────┘

TransferCrewMember

EXEC TransferCrewMember 'P0002', 'NCC-1701', 'Tactical Officer', '2373-07-15';

MissionResourceAllocation

EXEC MissionResourceAllocation
 
-- ┌────────────┬──────────────┬───────────┬───────────────┬──────────┬──────────────┬────────────────┬──────────┬────────────────┐
-- │ MISSION_ID │ SHIP_REGISTRY│ CREW_SIZE │ DURATION_DAYS │ PRIORITY │ ENERGY_UNITS │ FOOD_SUPPLY_KG │ MED_KITS │ PRIORITY_LEVEL │
-- ├────────────┼──────────────┼───────────┼───────────────┼──────────┼──────────────┼────────────────┼──────────┼────────────────┤
-- │ M001       │ NCC-1701     │ 150       │ 90            │ 9        │ 13500        │ 40500          │ 30       │ High           │
-- │ M002       │ NX-74205     │ 100       │ 90            │ 4        │ 4000         │ 27000          │ 20       │ Medium         │
-- └────────────┴──────────────┴───────────┴───────────────┴──────────┴──────────────┴────────────────┴──────────┴────────────────┘

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Graph View