Databaskonstruktion-Assignment-1-Startup

Startup code for first assignment in Databaskonstruktion

ER Modell

Relationer

AdminAgent (entiteter Agent och Admin)

AdminAgent(Namn, Nr, Lön, Unamn, Kommentar, AdminNamn, AdminNr, UIDToken, Rum)

Operation

Operation(Kodnmnt, Stdatum, IncNamn, IncNr Sldatum, SR, Kommentar, AdminNamn, AdminNr, RegionNamn)

Incident

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

Region

Region(Namn, Terräng)

Rapport

Rapport(Datum, Titel, IncNamn, IncNr, AgentNamn, AgentNr)

Observation

Observation(ID, Säkerhet, Datum, Grad, IncNamn, IncNr)

Person

Person(ID, Namn, Kodnamn)

Vittnen (Många till många tabell av entiteterna Observation och Person)

Vittnen (ObservationID, PersonID)

Krav

Nedan listas de krav som identifierats för respektive entitet. Dessa görs utifrån uppgiftsversion A.

Agent

• Agenter identifieras unikt med hjälp av dess namn (en bokstav) tillsammans med en siffra.
• Agentens levnadsfilosofi lagras som en kommentar för att kunna utvärdera hur pass bra agenten lever upp till sin levnadsfilosofi.
• Agentens lön och ursprungliga namn (inklusive separata förnamn och efternamn och ett härlett attribut som sätter samman förnamn och efternamn) skall dessutom lagras för alla agenter.

Admin

• Administratörerna skall kunna tilldela en person fler möjligheter när tillåtelsen givits från högre ort.
• Ha en möjlighet att skapa och underhålla de andra användarnas rättigheter.
• Det användargränssnitt som används av databasadministratören behöver inte vara lika lättanvänt som de användargränssnitt som skall användas av agenterna, då databasadministratören förstår bland annat felkoder från SQL.

Operation

• Varje operation identifieras av dess kodnamntyp samt operationens startdatum tillsammans med incidenten som genererade operationen.
• Kodnamntypen anger både operationens kodnamn samt dess typ, exempelvis uppstädningsoperation: Gregers Bodega eller Insamlingsoperation:lämmeltåg.
• Operationen har även en frivillig kommentar som bör fyllas i där agenterna specificerar viktiga detaljer om operationen som är utöver den information som lagras i operationens förbestämda fält.
• Det är tillåtet för två olika operationer för samma incident på olika datum att ha samma kodnamn och typ.
• För varje avslutad operation lagras dessutom operationens slutdatum samt operationens success rate.
• Slutdatumet måste alltid vara senare än startdatumet, samt att en operation inte får vara kortare än en dag.
• Om skillnaden mellan en operations startdatum och slutdatum är större än fem veckor så antas slutdatumet vara felinmatat, längden till operationen skall då automatiskt sättas till fem veckor.
• De attribut som anges som “success rate” skall lagras i form av ett heltal som är ett om operationen eller kampanjen lyckades och noll om operationen eller kampanjen misslyckades.

Incident

• Ska kunna identifieras av unikt namn eller nummer.
• För incidenten lagras dessutom platsen som den inträffade på.
• Det skall gå att beräkna ett medelvärde på incidentens alla observationers grad.
• En incident måste inträffa i någon region.
• En incident kan dessutom ha en eller flera operationer kopplade till sig.

Region

• En region identifieras unikt av dess namn.
• För en region lagras dessutom en uppskattning av regionens huvudterräng, exempelvis stadsterräng, berg eller skog.
• Dessutom skall det gå att härleda regionens antal tidigare incidenter och operationer för att kunna se om regionen är överrepresenterad.
• Det finns regioner som under en viss tidsrymd är speciellt hemliga, dessa regioner skall temporärt kunna tas bort ur databasen för att senare kunna sättas in igen. En databasadministratör skall kunna ta bort regionen och även de kopplingar som regionen har till operationer och incidenter.
• Eftersom det dock är nödvändigt att incidenter och operationer är kopplade till regioner skall en specifik region som heter “hemligstämplat“ skapas, och sedan kopplas till de påverkade operationerna och incidenterna.
• Alla regioner måste ha någon typ av terräng lagrad, om inte, är default-värdet "blandad".

Rapport

• Det finns två olika typer av rapporter, vanliga rapporter samt uppföljningsrapporter.
• En vanlig rapport lämnas in av någon agent efter varje incident som inträffat (eller efter att incidentens operation slutförts).
• För slutrapporterna lagras en kortfattad kommentar (exempelvis max 25 tecken).
• Dessutom lagras typen av rapport för slutrapporten, eftersom slutrapporten skrivits av flera olika orsaker.
• Det skall vara möjligt att ta bort (hemligstämpla) hela rapporter inklusive alla rapportens rader och uppföljningar. Borttagningen skall loggas så det är möjligt att återskapa rapporten i sin helhet när hemligstämpeln släppts.
• När en rapport tas bort skall automatiskt alla associerade rapportrader och uppföljningar tas bort.
• Slutligen skall det vara möjligt att hemligstämpla vissa rader i en rapport, rapportraden ersätts då med texten “hemligstämplat”. Det ska sedan gå att återställa raderna till sin ursprungliga text.
• På alla typer av rapporter så måste värden för alla attributen lagras. Om typen för en rapport inte anges skall den automatiskt sättas till slutrapport.

Observation

• En observation kan antingen gälla rymdskepp eller rymdvarelser, om flera olika typer av rymdvarelser siktats eller om både rymdskepp och rymdvarelser siktats skall separata observationer skapas för denna incident.
• För varje observation måste observationens datum,grad samt säkerhet anges.
• För att mäta säkerheten i en observation används anges ett värde i procent.
• För att uppskatta grad/avstånd används ett intervall på 1-4, där 4 motsvarar extrem närkontakt.
• Prestanda är av högsta vikt när det gäller åtkomst av observationer av rymdvarelser eller rymdskepp och av datan om rymdvarelserna och rymdskeppen. Eftersom åtkomsttiden oberoende av frågeställning behöver vara under 1 sekund, är det mycket viktigt att alla möjliga optimeringsstrategier används för att optimera åtkomsthastigheten i dessa tabeller.
• För databasadministratörerna skall det vara möjligt att ta bort observationer om det är nödvändigt med total intern mörkläggning av observationerna.
• När en observation tas bort skall motsvarande kopplingar till personer och handläggare tas bort.
• De borttagna observationerna skall dock lagras i enloggtabell eller flera loggtabeller som tillåter att datan återskapas när hemligstämpeln släppts. För denna typen av uppdatering skall därför datumet för uppdateringen samt administratörens användarnamn lagras i loggtabellen eller loggtabellerna.
• För observationerna så skall en procentsats anges för observationens säkerhet, säkerheten får inte vara mindre än en procent och inte högre än hundrafjorton procent.

Person

• En person kan ha rapporterat flera observationer och en observation kan ha rapporterats av flera personer.
• Varje person identifieras unikt utifrån dess personnummer eller om personen i fråga vill vara anonym ett automatgenererat nummer.
• Personens namn eller alias (om han vill vara anonym) lagras som en textsträng.
• Speciellt viktiga informatörer tilldelas ett kodnamn så de kan identifieras mycket fort.
• För att kunna bedöma personens trovärdighet utifrån tidigare observationer, huruvida om operationer som inletts på grund av dessa varit framgångsrika / misslyckade (lyckade operationer kan öka trovärdigheten).
• Personer som inte har kodnamn eller lämnat falska namn (inget namn) tros vara mindre trovärdiga än kända uppgiftslämnare.

Underlag till muntlig redovisning

Uppgiftsversion

Denna uppgift och databaslösning baseras på version A (databasadministratör).

Konceptuell Databasdesign

Eftersom den konceptuella databasdesignen ligger till grund för de resterande designstegen, låg fokus på att bevara relationer / entiteter som dels är relevanta tillsammans, men också enskilt. Eftersom drygt 80% av entiteterna exkluderas i databasdesignen, är det viktigt att ha entiteter som är intressanta samt "fungererar bra" i en mer avskalad modell / lösning. Därför valdes entiteterna: Agent, Admin, Operation, Region, Incident, Rapport, Observation samt Person.

Logisk Databasdesign

När det kommer till den logiska databasdesignen beskrivs detta mer utförligt i de ovanstående kapitlen i readme:n. Generellt sett är det största beslutet i detta steg att entiteterna Agent och Admin slogs ihop till en och samma entitet AdminAgent. Detta gjordes eftersom Admin ärver Agentens attribut, samtidigt som de övriga subtyperna inte är relevanta för denna uppgiftsversion. Gällande datatyper och constraints tillämpades dessa i samtliga tabeller, där datatyp styr vilken data som går att lagra i varje attribut och constraints säkerställer att ingen otillåten eller felformaterad data matas in. Exempelvis finns en check constraint på AdminAgent, som säkerställer att agentens nr är mellan 1 och 99 samt inte är 13.

Fysisk Databasdesign

Med den fysiska databasdesignen görs val gällande denormalisering, indexering, rättigheter samt procedures och triggers. I lösningen görs två vertikala denormaliseringar för agenternas kommentar samt operationers kommentar. Detta då kommentarer kan innehålla mycket data, vilket kan tynga ner sökningar mot dessa tabeller. Merge görs två gånger, där region-tabellen adderas till incident- och operation-tabellerna. Kodifiering görs på två ställen, i incident- samt AdminAgent-tabellen, detta med syfte att minska mängen data lagrad i varje tabell, samt minska risken för redundant data. Index skapas på två ställen; ett för regioner i operation-tabellen samt ett för namn i person-tabellen. Detta för att öka prestandan på sökningar som inte görs med primärnyckeln. När det kommer till rättigheter skapades två olika användare, som har olika nivåer på vad de kan göra. Exempelvis kan en AdminAgent göra selects mot alla tabeller, medan en Agent enbart kan söka upp rapporter.

Procedures och triggers

Antagande

Ett antagande som görs i uppgiften är att det är av intresse för organisationen att spåra och logga historiken för ändringar som görs mot AgentInfo-tabellen. Av den anledningen har nedanstående trigger AgentInfoUpd skapats.

procedure MakuleraObservation

Utifrån uppgiftsbeskrivningen ska en databasadmistratör ha möjlighet att ta bort en eller flera observationer om det behövs. Däremot ska det gå att återskapa raderna när hemligstämpeln lyfts. Därför skapas två olika loggtabeller, som sedan lagrar den data som makuleras av proceduren. Således går det att återskapa data som makulerats från den ursprungliga tabellen.

procedure AvgObservationGrad

Från uppgiftsbeskrivningen framgår det att det ska vara möjligt att beräkna medelvärdet för alla de observationer som kopplats till en viss incident. För att uppnå detta i proceduren görs en select avg på attributet "Grad", där parametrarna är incidentens namn och nummer. Om det då finns flera kopplingar returneras medelvärdet av dessa tillbaka.

trigger AgentInfoUpd

Denna trigger loggar automatiskt de ändringar som görs mot AgentInfo-tabellen, som innehåller agentens ursprungliga namn samt UnamnID. Detta för att kunna garantera intern transparens och historik. Ändringarna sparas i en loggtabell, där det utöver det gamla / nya namnet framgår vilken användare som gjort ändringarna, samt vilket datum / tid.

procedure HemligIncRegion

Då det ska vara möjligt att ta bort regioner från incidenter (och operationer), men samtidigt som dessa inte får vara null, skapades en procedure som ersätter den ursprungliga regionen med "Hemligstämplat". Dessutom loggas denna uppdatering tillsammans med ändringsdatumet, så att det i senare skede går att se historiken / återskapa raden. Denna procedure implementeras enbart mot incident-tabellen, och inte för operation.