Fältagentens databasimplementation

Overview

Jag är grupp C, så jag har tagit den del av EER-diagrammet som handlar om fältagenten: delta i operationer, jobba i regioner, dokumentera incidenter genom rapporter och observationer från fältet.

Jag valde entiteterna Agent, Fält, Operation, Region, Incident, Rapport, Rad och Observation. Det blir runt 30 procent av de knappt trettio entiteterna i originaldiagrammet, lite mer än de föreslagna 20–25 procenten — men fältagentens uppdrag hänger ihop hela vägen från Operation till Observation, så jag tog med hela kedjan i stället för att klippa av den mitt i. I SQL-koden skriver jag Fält som falt och struntar i å, ä, ö rakt igenom, mest för att databasen ska gå att flytta mellan olika MySQL-installationer utan krångel.

Tilldelad del

Grupp C har agenttypen Fältagent. En fältagent kan delta i flera operationer, jobba i flera regioner och dokumentera incidenter genom rapporter med rapportrader och observationer.

Antaganden

• Agentnummer identifierar alla agenter, och Fält ärver den nyckeln.
• En agent behöver inte vara fältagent, men en fältagent är alltid en agent.
• Kompetens lagras. Rate och Uppdrag är härledda i originaldiagrammet, så de lagras inte.
• En fältagent kan vara med i flera operationer, och en operation kan ha flera fältagenter.
• En operation kan täcka flera regioner, och en region kan höra till flera operationer.
• En incident måste inträffa i en region.
• En rapport skrivs av en fältagent och gäller en incident.
• En rapport har minst en rad, och en observation hör till en bestämd rapportrad. Se avstegen längre ner för varför — kravspecen kopplar egentligen observationen till incidenten, inte till rapporten.
• Observationens Grad anger hur nära kontakt det var (avsnitt 2.3.6), och ligger mellan 1 och 4, där 4 är extrem närkontakt. Säkerhet är en procentsats, mellan 1 och 114 — ja, över 100 procent är faktiskt tillåtet enligt kravtexten.
• Ingen agent får ha lön under 12 000 kr, och den som inte är gruppledare får inte ha lön över 25 000 (avsnitt 2.3.1). Gruppledarens 35 000-tak är inte relevant här eftersom jag inte tagit med den subtypen. Ingen lön angiven blir automatiskt 13 000, precis som kravtexten säger.
• Incidentens Grad är härledd i originaldiagrammet — medelvärdet av dess observationers Grad, enligt avsnitt 2.3.9. Jag har den i stället som en kodifierad prioritetsnivå (Låg/Medel/Hög), satt av handläggaren direkt. Dels passar en kod bättre för sökning (se indexet incident_region_grad) än ett flyttal, dels hade ett riktigt härlett medelvärde krävt omräkning varje gång en observation läggs till, ändras eller tas bort — mer än den här delen behöver bygga för.
• Kravspecen säger rakt av att en observation hör till en incident (avsnitt 2.3.6). Jag har den i stället kopplad till en rapportrad. Kedjan Incident–Rapport–Rad fanns redan i mitt urval, och det var enklare att låta Observation höra till Rad än att bygga en till, parallell koppling till Incident. Samma avsteg gäller triggern validate_observation — regeln om att en observations datum inte får ligga efter rapportens är inget jag hittat i kravspecen, utan något jag själv lagt till för att ha en regel att testa med en trigger (Step 7B) i stället för en vanlig CHECK.
• Ett användarnamn är unikt även om agenten inte är fältagent.
• Diagrammets kardinaliteter är svåra att läsa på några ställen. Jag har hållit mig till antagandena ovan genomgående — i diagram, relationer och SQL.

ER-modell

Diagrammet visar den konceptuella modellen, innan den fysiska denormaliseringen. Jag har inte ändrat det så det ser ut som de färdiga tabellerna.

Relationer

Så här ser överföringen från ER-modellen till en logisk relationsmodell ut. Understrukna attribut är nycklar, attribut mellan tilde är främmande nycklar.

Agent(AgentNr, Namn, Lön, Unamn, Kommentar)

Fält(AgentNr, Kompetens)

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

Region(RegionNamn, Terräng)

FältOperation(AgentNr, Kodnamn)

OperationRegion(Kodnamn, RegionNamn)

Incident(IncidentNr, Namn, Grad, Plats, RegionNamn)

Rapport(RapportNr, Datum, Titel, IncidentNr, AgentNr)

Rad(RapportNr, RadNr, Text)

Observation(ObservationID, Säkerhet, Datum, Grad, RapportNr, RadNr)

Krav

Agent

• Varje agent ska ha ett unikt agentnummer.
• Namn, användarnamn och lön är obligatoriska.
• Användarnamnet ska vara unikt.
• Lönen ska ligga mellan 12 000 och 25 000 kr.
• En agent får finnas utan att tillhöra subtypen Fält.

Fält

• Varje fältagent ska motsvara exakt en befintlig agent.
• Kompetens är obligatorisk för en fältagent.
• En fältagent kan delta i flera operationer.
• Rate och antal uppdrag beräknas från andra uppgifter och lagras inte.

Operation

• Varje operation identifieras av ett kodnamn.
• Startdatum är obligatoriskt. SR anger om operationen lyckades (1) eller misslyckades (0) — inga andra värden är tillåtna, enligt kravspecifikationen. SR är okänt (NULL) medan operationen fortfarande pågår, och obligatoriskt först när den avslutats.
• Ett eventuellt slutdatum måste ligga strikt efter startdatumet ("senare", inte samma dag).
• En operation kan pågå i flera regioner.

Region

• Varje region identifieras av sitt namn.
• Terräng är obligatorisk och lagras som en kod.
• En region får finnas innan den kopplas till en operation.

Incident

• Varje incident har ett unikt nummer, namn, grad och plats.
• Graden ska vara en tillåten kod.
• Regionen är obligatorisk när incidenten registreras.
• En incident kan ha flera rapporter.

Rapport

• Varje rapport har ett nummer, datum och en titel.
• Rapporten skrivs av en fältagent och gäller en incident.
• Rapportinformation får inte registreras utan minst en första rapportrad.

Rad

• Radnummer är unikt inom en rapport.
• Radtexten är obligatorisk.
• Lång rapporttext lagras separat i den fysiska modellen.

Observation

• Varje observation identifieras av ett eget ID.
• Säkerhet ska ligga mellan 1 och 114 procent.
• Grad ska ligga mellan 1 och 4.
• Observationens datum får inte ligga efter rapportdatumet.
• En observation hör till en befintlig rapportrad.

Konceptuell databasdesign

Jag tog den sammanhängande delen runt subtypen Fält från originaldiagrammet. Agent är kvar eftersom Fält hänger på sin supertyp. Operation, Region och Incident beskriver sammanhanget kring jobbet, medan Rapport, Rad och Observation beskriver resultatet av fältarbetet.

Härledda attribut lagras inte. Arvet mellan Agent och Fält är variant A: en tabell för Agent och en separat för Fält med samma primärnyckel. Det är ett beslut på logisk nivå och ändras inte av denormaliseringen senare.

Logisk databasdesign

M:N-sambanden mellan Fält och Operation, och mellan Operation och Region, blir kopplingstabeller. Rapport och Rad får en sammansatt koppling genom rapportnummer och radnummer. Främmande nyckeln från Incident till Region är obligatorisk — kravspecen säger att en incident alltid måste inträffa i en region.

Jag använder bland annat INT, VARCHAR, DECIMAL, DATE, TINYINT, TEXT och TIMESTAMP — identifierare, kort text, belopp, datum, koder och längre rapportinnehåll får varsin lämplig typ.

Fysisk databasdesign

Constraints

Lönen ska ligga mellan 12 000 och 25 000 kr enligt kravspecifikationen, med 13 000 som standardvärde om inget anges. Observationens grad (1–4) och säkerhet (1–114 procent) kommer från samma avsnitt. Operationens slutdatum måste ligga strikt efter startdatumet, och SR får bara vara 0 eller 1. Incidentens region är obligatorisk. Användarnamn är unika. NOT NULL på det som är obligatoriskt, utöver nyckelkolumner.

Denormalisering

Rapport och Rad slås ihop till rapport_rad genom en merge. Rapportens metadata upprepas på varje rad, vilket gör att en komplett rapport går att läsa utan join.

Terräng kodifieras i terrang_kod. Incidentgrad på samma sätt i incidentgrad_kod — kortare lagring, snabbare jämförelser.

Operation delas vertikalt i operation_aktiv och operation_avslutad. Aktiva operationer används oftare och blir då en mindre tabell att söka i. Kopplingstabellerna delas på samma sätt, eftersom en främmande nyckel inte kan peka på två tabeller samtidigt. Uppdelningen är radvis, inte kolumnvis, som kursens Step 3B — jämför med invoicerow som delas i betalda och obetalda.

Rapporttexten delas horisontellt, från rapport_rad till rapport_rad_text. Listor över rapporter slipper då dra med sig de stora textkolumnerna. Motsvarar Step 3C, där den stora kolumnen invcomment bryts ut för sig.

Ingen av de här ändrar arvvarianten för Agent och Fält.

Indexering

Ett index, incident_region_grad, på incidents region och grad — för att snabbt hitta prioriterade incidenter i en region. Ett till, falt_aktiv_operation, på fältagentens aktiva operationer — den vanliga frågan som visar en agents pågående uppdrag.

Rättigheter

field_app får bara läsa tabellerna och köra de två procedurerna register_report och assign_field_agent. All skrivning går via de anropen, aldrig med direkta tabellrättigheter — field_app har ingen INSERT-, UPDATE- eller DELETE-rättighet någonstans.

Procedurer och triggers

assign_field_agent

Kopplar en fältagent till en aktiv eller avslutad operation. Bygger på att bara registrerade fältagenter får delta, och väljer rätt kopplingstabell beroende på operationens status.

register_report

Registrerar rapportens första rad och rapporttexten. Uppfyller kravet att en rapport inte kan skapas utan innehåll. Två INSERT, ingen egen felhantering — som kursens Step 7A.

validate_observation

Kollar en regel som inte går att skriva som en vanlig CHECK: observationens datum får inte ligga efter rapportens. Ett komplement till intervallkontrollerna på säkerhet och grad.

log_observation

Skriver en granskningspost i observation_logg varje gång en observation skapas — användarnamn och tidpunkt. Som kursens Step 7C. Loggningen sker oavsett vilken kod som gjorde insättningen.

Testdata och kontroll

Varje tabell har flera rader. Mira Holm är en agent utan Fält-post, för att visa att subtypen är valfri. Frizon är en region utan operation, men kopplad till en incident.

De avsiktligt felaktiga försöken ligger i en egen fil, test_constraints.sql, och körs utan INSERT IGNORE. Med mysql --force fortsätter skriptet förbi varje sats som misslyckas, men felmeddelandet skrivs ut. Filen har inga egna kommentarer förutom en hänvisning hit — annars hade jag brutit mot regeln om att bara ha de kommentarer som efterfrågas. I filens ordning testas:

1. Lön under 12 000 kr
2. Duplicerat användarnamn (agent.unamn)
3. Slutdatum före startdatum, aktiv operation
4. Slutdatum före startdatum, avslutad operation
5. SR satt till ett värde som varken är 0 eller 1, aktiv operation
6. SR satt till ett värde som varken är 0 eller 1, avslutad operation
7. Säkerhet utanför 1–114
8. Grad utanför 1–4
9. NOT NULL: agent.namn satt till NULL
10. Främmande nyckel: rapport_rad.incidentnr pekar på en incident som inte finns
11. Triggern validate_observation: en observation daterad efter sin rapport
12. Proceduren register_report: ett redan existerande (rapportnr, radnr) — proceduren saknar egen felhantering, som kursens Step 7A, så MySQL:s vanliga dubblettfel avbryter anropet
13. Lön över 25 000 kr (andra sidan av samma intervall som test 1)

Efter att procedurerna och triggarna finns anropas båda. Sen läggs tre observationer till på befintliga rapportrader, så observation_logg har mer än en rad att kontrollera.