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

EER Diagram

It says that this is supposed to be in submission 1, but I can't attach the EER to the SQL file, so it's here instead.

Executive Summary

This lab implements an inheritance-based crew member hierarchy for the Nostromo ship, which helps distinguish officers and specialists under a common superclass of crew member. The database models vessel operations—including cargo transport, mission tracking, and maintenance activities.

I chose inheritance over separate independent entities to show the shared attributes among all of the crew members, such as their ID or name, while still having specialized attributes for specialized roles.
The inheritance model is disjoint—since a crew member is either an officer or specialist but not both. It is also total, as every crew member must be one of those categories.
As for the conversion method, I adopted the separate tables approach. I implemented a superclass CREW_MEMBER and subclasses OFFICER and SPECIALIST. This provides structural separation and better constraint enforcement.
The main challenge was properly enforcing constraints to maintain the disjointness of the tables as well as deciding how to actually implement that with SQL, since I’d never done it before—just watched when we were shown in class. It feels easier to make the diagram than the SQL, since in the SQL, there’s nothing really “enforcing” things in place, so you’d have be to be careful with data insertion.

Crew members’ IDs uniquely determine their name and base attributes
Officers’ roles depend on their crew ID and must be within a fixed set of allowed roles
Cargo loads depend on vessel ID and mineral type, mineral type is determined by the cargo description
Maintenance logs depend on both the crew member and vessel IDs

1NF is ensured by using atomic values and no repeated groups
2NF is ensured with no partial dependencies by putting crew data in the CREW_MEMBER table and the specific data in the OFFICER and SPECIALIST tables
3NF is ensured with no transitive dependencies since there are distinct tables for mineral types and the maintenance logs are separated with foreign keys to link crew and vessels.

All tables comply with 3NF requirement; every non-key attribute depends only on the primary key, there aren’t any partial or transitive dependencies, and the constraints on foreign keys preserve the integrity of relationships across the database

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