Part 1

Fleet Movement Tracking System

I thought to store positions using the GEOMETRY type in SQL server, which is helpful for storing spatial data, rather than storing separate values for the x, y, and z positions. This was also helpful for storing velocity, since velocity can be represented with a vector of .
The timestamp that was previously used in the database was DATE, so I decided to use that as well for the columns.
As for the reliability, I just added a “reliability” rating, which would be between 0 and 100.

To categorize features, I just added a VARCHAR(50) which would store the type of feature, such as if it’s a door, or some other security checkpoint (I’m not exactly sure what their use case was)
The priority system I added was just a TINYINT between 1 and 10, since I noticed that there were similar things to that in the database, such as priority of assignments.
Also, to track security findings, there is an assessment_date column, which stores the date.
As for the cross-referencing, it references the department_id from DEPARTMENTS, since the departments are linked to their security information

I thought of storing the message content, and the personnel it’s being sent to. Potentially, it would be beneficial to have some sort of encryption so the records aren’t being stored in plaintext, but this should be fine for now.
For the date requirement, I added a timestamp column, which would store the time of the communication as a DATE. For the ‘frequencies’, I decided to add a messages_exchanged column, which would store the amount of messages exchanged, which would increment on the amount of messages sent—although this isn’t strictly necessary, since you can calculate it using a query.

To implement security clearance tracking, I decided to add a security_level, which is a TINYINT between 0 and 10. I did this similarly to how the other things have a security_level, such as the departments themselves.
For verification dates, I just added verification_date, which is of type DATE.
I’m not exactly sure what to do for the ‘necessary indexes’, but I added the constraints for the security_level, which is a check between 1 and 10.

My initial movement tracking already had start and end coordinates, so I just added the duration and validation rules. I wasn’t entirely too sure what validation rules meant, so I just added a VARCHAR(500).
As for the maintenance tracking, I added a new table, STARSHIP_MAINTENANCE, which stores data on the duration, the maintenance, and the maintenance date of the starship.

For verifying things, I just decided it would be easiest to look at the tables visually, but I also included the queries; where you can see the columns in each of the tables that were added, as well as the tables modified so you can verify the modifications were made.

I ensured that the ship tracking system was deployed by doing a join on STARSHIPS, as well as the FLEET_TRACKING, so you can see the tracking information of each ship, and I included an ORDER BY rule, so fleet tracking information about the same ship would be next to each other.
For the crew assignment validation, I joined the starships and departments so you could see if the personnel have the correct security level for the department to ensure they belong there.