Overview

I was appointed to Group B, which handles the "Desinformatör" class. For this, we needed to gather all the information that can be found regarding that class from the documentation: https://his.instructure.com/courses/9943

ER model

The following diagram shows the modified version of the EER Schema that can be found in Fig 1 from the documentation.

Relations

Agent(nr, name, Lön, unamn)

Agent_lifstill(Agent_nr, Kommentar)

Incident(Incident_Nr, Disnfo_nr, Name, Gata, Plat, Rate, Specialite, Kampjn)

Operation(Kodnmt, Startdatum, Slutdatum, SR, Kommentar)

Kampanj_Acslutad(Nr, SR, Startdatum, Slutdatum, kommentar)

Kampanj_Activ(Nr, SR, Startdatum kommentar)

Undanflukter(Title, Typ, Halt, Rate)

Requirements

A few lines containing a general description of the requirements

Agent

• Every agent is uniquely identified by a number (Nr).
• The agent's name (Name) and user name (Unamn) are stored, together with the salary (Lön) and a comment field (Kommentar).
• The agent's life philosophy is stored separately in Agent_lifstill as a comment for future evaluation.
• Agent names cannot be '13'.
• Agent numbers (Nr) cannot be negative or greater than 99.
• Agent number 0 is forbidden.
• No agent may earn less than 12,000.
• Salary (Lön) cannot exceed 25,000.
• If no salary is specified during creation, it must automatically default to 13,000.
• Every agent may have at most one life philosophy record.

Agent_lifstill

• Uniquely identified by the agent number (Agent_Nr), which refers to exactly one agent.
• Stores the agent's life philosophy as a comment (Kommentar).
• A life philosophy cannot exist without an agent.

Incident

• Uniquely identified by number (Incident_nr), storing the name (Name), the location (Plats) and the street (Gata).
• Every incident must be handled by exactly one disinformation spreader (Disnfo_nr), who is an agent.
• Stores the disinformation spreader's specific lie-crafting specialty (Specialite), such as weather phenomena or low-flying aircraft, and the cost rate (Rate).
• A disinformation spreader may not work on more than five active campaigns at the same time.
• The total number of completed campaigns and the percentage of successful campaigns must be calculable.
• Upon login or execution, assigned incidents must appear directly in the user interface.
• The average trustworthiness score across all connected observations must be calculable.
• A single incident may use at most ten different pretexts across all of its associated campaigns combined.
• The disinformation spreader can view all observations, reporter trustworthiness, and media recordings sorted by name in descending order.
• Has read and write access to the Kampanj_Aktiv, Kampanj_Avslutad and Undanflykter tables.

Kampanj_Aktiv

• Uniquely identified by campaign number (Nr).
• Start date (Sdatum) must be set.
• An active campaign is ongoing and therefore has no end date.
• Success rate (SR) must be stored as an integer: 1 for success and 0 for failure.
• Contains a voluntary comment field (komm).
• A single campaign may contain at most three different pretexts.
• An incident may have several active campaigns, and an active campaign may be connected to several incidents.
• Agents can modify or delete campaigns they worked on, but no person may modify or delete more than four campaigns.

Kampanj_Avslutad

• Uniquely identified by campaign number (Nr), which refers to the active campaign it was created from.
• Start date (Sdatum) and end date (Sldatum) must be set, and the end date must be later than the start date.
• If the duration between start date and end date exceeds five weeks, it is assumed to be an input error and automatically capped at five weeks.
• Success rate (SR) must be stored as an integer: 1 for success and 0 for failure.
• Contains a voluntary comment field (komm).
• A single completed campaign may contain at most three different pretexts.
• When a campaign is removed, its pretext links are removed, and any pretext no longer referenced by any campaign is automatically purged from the database.

Undanflykter (Pretext)

• Uniquely identified by title (Title), storing its origin type (Typ), normal truthfulness percentage (Halt) and cost rate (Rate).
• Truthfulness (Halt) must strictly be between 0.1% and 95.0%.
• A pretext may be used in several completed campaigns, and a completed campaign may use several pretexts.
• The percentage of successful campaigns using the pretext must be calculable.

Operation

• Uniquely identified by code name type (Kodnmnt) and start date (Stdatum) together with the generating incident.
• End date (Sldatum) must be later than start date, and durations exceeding five weeks are automatically set to five weeks.
• Success rate (SR) is stored as integer 1 (success) or 0 (failure).
• Contains a voluntary comment field (Kommentar) for operational details.

OLD model

After working we were then instucted to denormlised the diogram this mean that we had to reconfigur the system and digoreams to the newer verson (the first diogram that is showen). the following is old formulated diogram that was straight ripped form the old system with there requierments.

oldRelations

Agent(name, nr, Lön, unamn, kommentarer)

Disinfo(name, Rate, Specialite, Kampanj)

Incident(Nr, Name, Gata, Plats)

Operation(Kodnmt, Startdatum, Slutdatum, SR, Kommentar)

Kampanj(Nr, Name, SR, Startdatum, Slutdatum, kommentar)

Undanflukter(Title, Typ, Halt, Rate)

Agent

• Every agent is uniquely identified by a combination of their letter name and a number.
• The agent's life philosophy is stored as a comment for future evaluation.
• Agent names cannot be '13'.
• Agent numbers (nr) cannot be negative or greater than 99.
• Agent number 0 is forbidden unless the agent is a Group Leader.
• No agent may earn less than 12,000.
• Salary cannot exceed 25,000 unless the agent is a Group Leader.
• If no salary is specified during creation, it must automatically default to 13,000.

Desinfo (Desinformationsspridare)

• Stores specific lie-crafting specialties, such as weather phenomena or low-flying aircraft.
• A disinformation spreader may not work on more than five active campaigns at the same time.
• The total number of completed campaigns and the percentage of successful campaigns must be calculable.
• Upon login or execution, assigned incidents must appear directly in the user interface.
• Has read and write access to the Kampanj and Undanflykter tables.

Incident

• Uniquely identified by number (Nr) or name, storing the location (plats).
• Every incident must occur within a region.
• The average trustworthiness score across all connected observations must be calculable.
• A single incident may use at most ten different pretexts across all of its associated campaigns combined.
• The disinformation spreader can view all observations, reporter trustworthiness, and media recordings sorted by name in descending order.

Kampanj

• Uniquely identified by campaign number (Nr).
• Start date (sdatum) and end date (sldatum) must be set, and the end date must be later than the start date.
• A campaign can be ongoing (sldatum IS NULL), but must have a start date.
• If the duration between start date and end date exceeds five weeks, it is assumed to be an input error and automatically capped at five weeks.
• Success rate (sr) must be stored as an integer: 1 for success and 0 for failure.
• A single campaign may contain at most three different pretexts.
• Agents can modify or delete campaigns they worked on, but no person may modify or delete more than four campaigns.
• When a campaign is removed, its pretext links are removed, and any pretext no longer referenced by any campaign is automatically purged from the database.

Undanflykter (Pretext)

• Uniquely identified by title (Title), storing its origin type (Typ), cost rate (rate), and normal truthfulness percentage (halt).
• Truthfulness (halt) must strictly be between 0.1% and 95.0%.
• The percentage of successful campaigns using the pretext must be calculable.

Operation

• Uniquely identified by code name type (Kodnmt) and start date (stdatum) together with the generating incident.
• End date (sldatum) must be later than start date, and durations exceeding five weeks are automatically set to five weeks.
• Success rate (sr) is stored as integer 1 (success) or 0 (failure).
• Contains a voluntary comment field for operational details.

Behold my work