Overview

Databaskonstruktion-Assignment-1-Startup

Group E: Handläggare

ER-diagrammet visar den konceptuella databasdesignen innan denormalisering. De förändringar som genomförts under den fysiska databasdesignen dokumenteras längre ned och implementeras i database.sql.

ER model

För Handläggare har följande sju centrala entiteter valts:

Agent: Innehåller grundläggande information om myndighetens agenter. Handläggare, gruppledare och desinformationsspridare är undertyper av Agent.

Incident: Handläggaren skapar en incident när en observation inte hör till en befintlig incident. Incidenten kopplas till en region och en desinformationsspridare.

Observation: Handläggaren registrerar observationer och kopplar dem till rätt incident. AlienObservation och ShipObservation är exklusiva undertyper av Observation.

Person: Innehåller uppgifter om personer som har rapporterat observationer.

Media: Innehåller information om exempelvis fotografier, video och ljud som hör till en observation.

Operation: Handläggaren kan skapa en övergripande grundoperation och tilldela den en gruppledare.

Region: Anger i vilken region en incident inträffar och en operation genomförs.

Mer exakt innebär kopplingarna

• Handläggare, gruppledare och desinformationsspridare är undertyper av Agent.
• En handläggare kan registrera flera observationer.
• Varje observation måste registreras av en handläggare.
• Varje observation måste tillhöra exakt en incident.
• En incident kan innehålla flera observationer.
• En person kan rapportera flera observationer och en observation kan rapporteras av flera personer.
• En observation kan ha media, exempelvis fotografi eller videoinspelning.
• En observation måste vara antingen en AlienObservation eller en ShipObservation.
• En incident måste tillhöra exakt en region.
• En region kan innehålla flera incidenter.
• En incident tilldelas en desinformationsspridare.
• En desinformationsspridare kan tilldelas flera incidenter.
• En incident kan resultera i flera operationer.
• Varje operation tillhör exakt en incident.
• Varje operation måste ha en gruppledare.
• En gruppledare kan leda flera operationer.
• Varje operation måste tillhöra en region.
• En region kan innehålla flera operationer.
• Handläggaren skapar endast den första övergripande grundoperationen.

Viktigaste arbetsflödet

När ett samtal kommer in ska handläggaren:

1. Registrera eller hitta personen som lämnar uppgifterna.
2. Kontrollera om observationen hör till en befintlig incident.
3. Skapa en ny incident om någon passande incident saknas.
4. Registrera observationen och koppla den till incidenten.
5. Registrera eventuell media.
6. Ange observationens säkerhet och ta del av personens beräknade trovärdighet.
7. Vid behov skapa en grundoperation och välja gruppledare.
8. Tilldela en desinformationsspridare till incidenten.

Relations (före denormalisering)

Agent (AgentName, AgentNumber, Salary, FirstName, LastName, LifePhilosophyComment)

Handler (AgentName, AgentNumber)

GroupLeader (AgentName, AgentNumber)

DisinformationAgent (AgentName, AgentNumber, Speciality)

Region (RegionName, Terrain)

Incident (IncidentNumber, IncidentName, Location, RegionName, DisinformationAgentName, DisinformationAgentNumber)

Observation (ObservationID, ObservationDate, Security, Grade, IncidentNumber, HandlerName, HandlerNumber)

AlienObservation (ObservationID, Colour, Clothes, Size, AlienType)

ShipObservation (ObservationID, Shape, Colour, LampType, Movement)

Person (PersonID, Name, CodeName)

PersonObservation (PersonID, ObservationID)

Operation (CodeNameType, StartDate, IncidentNumber, EndDate, SuccessRate, Comment, GroupLeaderName, GroupLeaderNumber, RegionName)

Media (ObservationID, MediaName, Quality, Comment, OperationCodeNameType, OperationStartDate, OperationIncidentNumber)

Den sista Media-raden förutsätter att jag har ritat relationen mellan Media och Operation. I övrigt matchar detta den uppdaterade bilden och uppgiftstexten (läs problem under Requirements).

Requirements

Agent

• En agent identifieras unikt genom kombinationen av AgentNamn och AgentNummer.
• AgentNamn ska bestå av en bokstav.
• AgentNummer får inte vara mindre än 0 eller större än 99.
• AgentNummer 0 är endast tillåtet för gruppledare.
• En agents lön måste vara mellan 12 000 och 25 000 kronor.
• Gruppledare får ha en lön på högst 35 000 kronor.
• Om ingen lön anges ska den automatiskt sättas till 13 000 kronor.
• Agentens ursprungliga förnamn och efternamn ska lagras.
• Agentens fullständiga ursprungliga namn ska kunna härledas från förnamnet och efternamnet.
• Agentens levnadsfilosofi ska lagras i attributet Kommentar.
• Leif Loket Olsson, Greger Puckowitz och Greve Dracula får inte registreras som agenter.
• Handläggare, gruppledare och desinformationsspridare är undertyper av Agent.
• AgentNummer 13 är inte tillåtet.

Handläggare

• En handläggare identifieras genom samma AgentNamn och AgentNummer som motsvarande rad i Agent.
• En handläggare får skapa och läsa observationer, media, incidenter och personer.
• En handläggare får läsa all tillgänglig information om gruppledare och desinformationsspridare.
• En handläggare får inte läsa fullständig information om andra typer av agenter.
• En handläggare registrerar observationer och kopplar dem till incidenter.
• Om en observation saknar en motsvarande incident ska handläggaren skapa en ny incident.
• Om observationen hör till en befintlig incident ska observationen kopplas till den incidenten.
• Handläggaren ska kunna skapa en övergripande grundoperation för en incident.
• Handläggaren ska tilldela grundoperationen en gruppledare.
• Handläggaren ska tilldela en desinformationsspridare till incidenten.
• Handläggaren ska kunna se incidentens observationer, personer, trovärdighetsvärden och eventuella mediainspelningar innan resurser tilldelas.
• Om tilldelningen av en gruppledare eller desinformationsspridare misslyckas ska den felaktiga inmatningen markeras och kunna korrigeras.
• Antalet incidenter som handläggaren har rapporterat är ett härlett värde och ska inte lagras.
• Antalet observationer som handläggaren har rapporterat är ett härlett värde och ska inte lagras.
• Antalet operationer som har startats på grund av handläggarens incidenter är ett härlett värde och ska inte lagras.
• Andelen av handläggarens incidenter som har resulterat i en operation är ett härlett värde och ska inte lagras.

Gruppledare

• En gruppledare identifieras genom samma AgentNamn och AgentNummer som motsvarande rad i Agent.
• En operation måste ledas av exakt en gruppledare.
• En gruppledare kan leda flera operationer.
• En gruppledare får normalt leda högst två pågående operationer samtidigt.
• En gruppledare får leda upp till fem pågående operationer samtidigt om alla operationer tillhör samma incident.
• Antalet operationer som gruppledaren har genomfört är ett härlett värde och ska inte lagras.
• Antalet lyckade operationer är ett härlett värde och ska inte lagras.
• Andelen lyckade operationer är ett härlett värde och ska inte lagras.

Desinformationsspridare

• En desinformationsspridare identifieras genom samma AgentNamn och AgentNummer som motsvarande rad i Agent.
• En desinformationsspridares specialitet ska lagras.
• En desinformationsspridare kan tilldelas flera incidenter.
• Varje incident ska tilldelas en desinformationsspridare.
• En desinformationsspridare får inte arbeta med fler än fem pågående desinformationskampanjer samtidigt.
• Antalet genomförda desinformationskampanjer är ett härlett värde och ska inte lagras.
• Andelen lyckade desinformationskampanjer är ett härlett värde och ska inte lagras.

Incident

• IncidentNummer används som primärnyckel.
• IncidentNamn måste vara unikt.
• Varje incident måste tillhöra exakt en region.
• En region kan innehålla flera incidenter.
• En incident kan innehålla flera observationer.
• Varje observation måste tillhöra en incident.
• En incident kan resultera i flera operationer.
• Varje operation måste tillhöra en incident.
• Varje incident ska tilldelas en desinformationsspridare.
• Platsen där incidenten inträffade ska lagras.
• Incidentens genomsnittliga observationsgrad är ett härlett värde och ska inte lagras.

Observation

• ObservationID är primärnyckel.
• Varje observation måste tillhöra exakt en incident.
• Varje observation måste registreras av en handläggare.
• ObservationsDatum, Säkerhet och Grad är obligatoriska.
• Grad måste vara ett heltal mellan 1 och 4.
• Grad 4 innebär extrem närkontakt.
• Säkerhet måste vara mellan 1 och 114 procent.
• En observation måste vara antingen en AlienObservation eller en SkeppObservation.
• En observation får inte vara både en AlienObservation och en SkeppObservation.
• Om både rymdvarelser och rymdskepp har observerats ska separata observationer skapas.
• Om flera olika typer av rymdvarelser har observerats ska separata observationer skapas.
• Åtkomst till observationer ska optimeras eftersom verksamheten kräver svarstider på under en sekund.

AlienObservation

• En AlienObservation identifieras genom samma ObservationID som motsvarande rad i Observation.
• ObservationID är både primärnyckel och främmande nyckel till Observation.
• Rymdvarelsens färg ska lagras.
• En generell beskrivning av rymdvarelsens kläder ska lagras.
• Rymdvarelsens uppskattade storlek i meter ska lagras.
• Rymdvarelsens typ ska lagras, exempelvis humanoid.

SkeppObservation

• En SkeppObservation identifieras genom samma ObservationID som motsvarande rad i Observation.
• ObservationID är både primärnyckel och främmande nyckel till Observation.
• Rymdskeppets form ska lagras.
• Rymdskeppets färg ska lagras.
• Typen av lampor på rymdskeppet ska lagras.
• En uppskattning av rymdskeppets rörelse ska lagras.

Person

• En person identifieras unikt genom ett personnummer eller ett automatiskt genererat anonymt ID.
• Om personen vill vara anonym ska ett automatiskt ID skapas.
• Personens namn eller alias ska lagras.
• Personens eventuella kodnamn ska kunna lagras.
• En person kan rapportera flera observationer.
• En observation kan rapporteras av flera personer.
• Personens trovärdighet är ett härlett värde och ska inte lagras.
• Trovärdigheten ska kunna beräknas utifrån personens tidigare observationer och om dessa har lett till operationer.
• Avsaknad av kodnamn eller ett falskt eller saknat namn ska kunna påverka trovärdigheten negativt.
• Åtkomst till information om personer ska optimeras eftersom tabellen används i realtid.

PersonObservation

• PersonObservation kopplar personer till de observationer som de har rapporterat.
• Kombinationen av PersonID och ObservationID är primärnyckel.
• PersonID är främmande nyckel till Person.
• ObservationID är främmande nyckel till Observation.
• Samma person får inte kopplas till samma observation mer än en gång.

Media

• En mediainspelning identifieras genom kombinationen av ObservationID och MediaNamn.
• ObservationID är främmande nyckel till Observation.
• MediaNamn anger typen av media, exempelvis videoinspelning, ljudinspelning eller fotografi.
• Kvaliteten måste vara dålig, god, medelgod eller bra.
• Om ingen kvalitet anges ska den automatiskt sättas till medelgod.
• Kommentaren för en mediainspelning får innehålla högst 80 tecken.
• Varje mediainspelning måste höra till en observation.
• Varje mediainspelning måste höra till en operation.
• En observation kan ha flera mediainspelningar.

Operation

• En operation identifieras genom kombinationen av Kodnamnstyp, Startdatum och IncidentNummer.
• Kodnamnstyp innehåller både operationens kodnamn och operationstyp.
• Varje operation måste tillhöra exakt en incident.
• En incident kan ha flera operationer.
• Varje operation måste tillhöra exakt en region.
• En region kan ha flera operationer.
• Varje operation måste ledas av exakt en gruppledare.
• En gruppledare kan leda flera operationer.
• Operationens kommentar är frivillig.
• Startdatum måste anges.
• Slutdatum måste anges för att bokning av agenter och hjälpmedel ska kunna genomföras.
• Slutdatum måste vara senare än startdatum.
• En operation måste pågå i minst en dag.
• Om operationens längd överstiger fem veckor ska slutdatum automatiskt ändras så att operationen blir fem veckor lång.
• SuccessRate ska lagras som ett heltal.
• SuccessRate får endast vara 0 eller 1.
• Värdet 1 innebär att operationen lyckades.
• Värdet 0 innebär att operationen misslyckades.

Region

• RegionNamn är primärnyckel.
• Varje region måste ha en terräng angiven.
• Om ingen terräng anges ska den automatiskt sättas till blandad.
• En region kan innehålla flera incidenter.
• Varje incident måste tillhöra exakt en region.
• En region kan innehålla flera operationer.
• Varje operation måste tillhöra exakt en region.
• Antalet tidigare incidenter i regionen är ett härlett värde och ska inte lagras.
• Antalet tidigare operationer i regionen är ett härlett värde och ska inte lagras.
• En databasadministratör ska kunna hemligstämpla en region tillfälligt.
• När en region hemligstämplas ska dess incidenter och operationer tillfälligt kopplas till en region med namnet "hemligstämplat".
• Informationen om den ursprungliga regionen ska kunna återställas när hemligstämpeln släpps.

Antaganden och otydligheter

Antaganden

• Kravspecifikationen säger att talet 13 inte får användas som agentnamn men samtidigt som agentnamnet ska bestå av en bokstav. Jag antar därför att talet 13 inte får användas som AgentNummer.

• Incident kan enligt kravspecifikationen identifieras av antingen sitt unika namn eller sitt unika nummer. Jag använder IncidentNummer som primärnyckel och låter IncidentNamn vara en alternativ unik nyckel.

• Varje observation antas registreras av exakt en handläggare medans en handläggare kan registrera flera observationer.

• Varje incident antas tilldelas exakt en desinformationsspridare medans en desinformationsspridare kan tilldelas flera incidenter.

• Eftersom varje mediainspelning enligt kravspecifikationen måste höra till en operation har en relation mellan Media och Operation lagts till, i ER-Modellen så är relationen röd.

• Agentens fullständiga ursprungliga namn betraktas som ett härlett värde. Därför lagras endast förnamn och efternamn.

• Härledda attribut markerade med en stjärna i ER-diagrammet lagras inte som vanliga kolumner utan beräknas vid behov.

• Undertyperna/subtyperna i ER-diagrammet räknas inte som separata entiteter. När Jag räknar 20–25 procent av entiteterna, så räknas exempelvis Agent och inte dess undertyper som en vald entitet.

• Attributen i AlienObservation och ShipObservation tillåter NULL eftersom personen som rapporterar observationen inte alltid kan ange exempelvis färg, storlek, form, kläder eller rörelse.

Otydligheter/Problem

1. Det finns ingen ritad relation mellan Media och Operation. Samtidigt säger uppgiftstexten:

Det får inte finnas någon mediainspelning som inte hör till någon operation.

Media (ObservationID, MediaName, Quality, Comment, OperationCodeNameType, OperationStartDate, OperationIncidentNumber)

En lösning: Behåll operationskolumnerna i Media, men lägg även till en relation mellan Media och Operation i ER-diagrammet.

Relationen bör visa:

• Varje mediainspelning tillhör exakt en operation
• En operation kan ha noll eller flera mediainspelningar

Om jag inte får lägga till relationen i bilden blir relationsmodellen/tabellen fel och uppfyller inte hela kravet om att media ska höra till en operation.

2. Kontrollera kardinaliteten Handläggare–Observation

Texten säger att varje observation måste ha lagrats av någon handläggare. Det bör innebära:

• En handläggare kan registrera noll eller flera observationer.
• Varje observation registreras av exakt en handläggare.

I bilden har jag fått symbolen "zero or more" i båda ändarna. Det skulle innebära en M:N-relation, vilket inte stämmer särskilt bra med uppgiftstexten eller relationstabellen.

Jag hade därför ändrat kardinaliteten i bilden (rött) vid Handläggare–Observation så att:

• Observationsänden visar zero or more
• Handläggaränden visar exactly one

Annars behöver jag skapa en separat kopplingstabell mellan Handler och Observation.

Kommentarer (för mig själv)

Lägg in data i rätt ordning

På grund av främmande nycklar måste inserts göras så här:

1. Agent
2. Handler
3. GroupLeader
4. DisinformationAgent
5. Region
6. Incident
7. Observation
8. AlienObservation
9. ShipObservation
10. Person
11. PersonObservation
12. Operation
13. Media

Tex, måste agenten finnas i Agent innan den kan läggas in i Handler.

• Gränsen 35000kr i lön används eftersom gruppledare får tjäna så mycket. Regeln att andra agenttyper maximalt får tjäna 25000 behöver jag senare hantera med en trigger eftersom agenttypen finns i en annan tabell.

Denormalisering – kodifiering av mediakvalitet

Attributet Quality i tabellen Media har kodifierats eftersom samma textvärden kan förekomma i många mediaposter. En ny tabell, MediaQuality, lagrar kvalitetskoderna och deras betydelse. Tabellen Media lagrar därefter endast QualityCode som en främmande nyckel. Koderna 1–4 motsvarar dålig, god, medelgod och bra kvalitet. Detta minskar mängden upprepad text och kan förbättra databasens prestanda. Om ingen kod anges används standardvärdet 3, vilket motsvarar medelgod kvalitet.

Denormalisering – merge av Region och Incident

Tabellerna Region och Incident har delvis slagits ihop genom att attributet Terrain har lagts till direkt i Incident. Den främmande nyckeln från Incident till Region har därför tagits bort. Detta gör det snabbare att hämta information om en incidents region och terräng eftersom databasen inte behöver göra en join mellan tabellerna. Nackdelen är att regioninformationen kan upprepas i flera incidenter, vilket skapar redundans. Tabellen Region finns tillfälligt kvar eftersom den fortfarande används av Operation.

Denormalisering – merge av Region och Operation

Tabellerna Region och Operation har slagits ihop genom att attributet Terrain har lagts till direkt i Operation. Den främmande nyckeln från Operation till Region har tagits bort. Eftersom regioninformationen nu finns direkt i både Incident och Operation behövs inte längre den separata tabellen Region, och den har därför tagits bort. Detta minskar behovet av join-frågor när regioninformation ska hämtas. Nackdelen är att samma regionnamn och terräng kan lagras flera gånger, vilket skapar redundans och gör det svårare att lagra en region som ännu saknar incidenter och operationer.

Denormalisering – horisontell delning av Agent

Tabellen Agent har delats genom att attributet LifePhilosophyComment har flyttats till den nya tabellen AgentLifePhilosophy. Den nya tabellen använder samma primärnyckel som Agent och är kopplad till den med en främmande nyckel. Detta gör grundtabellen mindre och snabbare när endast agentens vanliga uppgifter behöver hämtas. Om levnadsfilosofin behövs kan tabellerna kopplas samman med en join. Delningen påverkar inte arvsrelationerna mellan Agent och dess undertyper.

Denormalisering – horisontell delning av Operation

Tabellen Operation har delats genom att det frivilliga attributet OperationComment har flyttats till en separat tabell med samma namn. Den nya tabellen använder operationens sammansatta primärnyckel och är kopplad till Operation med en främmande nyckel. Endast operationer som har en kommentar behöver en rad i den nya tabellen. Detta gör huvudtabellen mindre när operationer vanligtvis hämtas utan kommentarer, men en join krävs när kommentaren ska visas.