Lab 10

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

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         │
-- └────────────┴──────────────┴───────────┴───────────────┴──────────┴──────────────┴────────────────┴──────────┴────────────────┘
 

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