About 20 years ago, there was a leak of information
regarding a top military advisor who had served several US Presidents. The information confirmed that this
individual was indeed a double agent whom was privy to many US secrets. The double agent had compromised many US
projects and caused countless hours of research to be scratched and
abandoned.
It was then that our finest counterintelligence
minds decided to come up with a database in order to keep track of all known
Special Agents. In order to avoid any
reoccurrence of a double agent, the Spy Database was created.
This database is only used by high-level officials
with high levels of security. Their
discretion and needs, dictate how the officials pull up vital information about
the agent, who they are affiliated with, and what their specialties may
be.
The tables below are used by the officials to record
and track each Secret Agent in the world, friend or foe.
Agent:
This table holds all personal information pertaining to each individual
agent. The table is generally used as a
quick reference, as long as the person viewing the information has the correct
clearance.
Mission: This table refers to the description of the mission that was
performed. From this table the official
can view what team performed the mission, the security clearance level required
for accessing mission details, the mission’s id
and name, and whether the team was successful in completing the tasks.
Affiliation: This table describes organizations the agent may be affiliated
with. It contains the organization’s
identification, the name of the organization, and the description of their
organization.
AffiliationRel: This table describes the
extent to which each agent is affiliated with each organization. The aff_id and agent_id make up the key and the connection strength refers
to the strength of the affiliation between the agent and the organization.
LanguageRel: This
table relates the language identification with the agent identification and is
used to connect the agents with the languages they are proficient in.
Language: This table connects the language identification code to a
particular language. It is used to
reference the agent’s language abilities.
SecurityClearance: This gives the clearance
of the agent. Example: Unclassified <
Classified < Secret < Top Secret < Magellan < Majestic <
Presidential
Skill:
This table pertains to the proficiency of the agent. Is the agent a sniper, or can they assemble a
bomb, or does the agent have a blackbelt in Karate.
SkillRel: This table tells which agent
has which skill.
Team: This
table allows the user to view the team’s id,
their name, and the frequency with which they meet.
TeamRel: This table connects the
agent with the team the agent is associated with.
Table Keys (primary keys underlined)
Note the foreign keys that
are in the tables with names that end in “Rel” are
not mentioned here. Each such table has
the two obvious foreign keys to the tables that they are relating.
Agent(agent_id,first,middle,last,address,city,country,salary,clearance_id)
clearance_id is
a foreign key to sc_id in the SecurityClearance
table
Mission(mission_id, name, access_id,
team_id, mission_status)
access_id is a
foreign key to sc_id in the SecurityClearance
table
team_id is a
foreign key to the Team table
Affiliation(aff_id, title, description)
AffiliationRel(aff_id, agent_id, affiliation_strength)
LanguageRel(lang_id, agent_id)
Language(lang_id, language)
SecurityClearance(sc_id, sc_level,
description)
Skill(skill_id, skill)
SkillRel(skill_id, agent_id)
Team(team_id, name, meeting_frequency)
TeamRel(team_id, agent_id)