Datalagring

Relationsdatabaser har olika metoder (se ACID) för att garantera att datan i databasen, vid varje tillfälle, kommer vara korrekt formaterad.

Relationsdatabaser har olika metoder (se ACID) för att garanter att databasen kommer innehålla korrekt data.

Normalisering, eller normaliserad data innebär att man minskar förekomsten av redundant data.

Normalisering låter oss

I de flesta databashanterare kan man skriva regler för hur rader och kolumner i tabeller ska se ut.

T.ex kan man bestämma att alla rader i användartabellen måste innehålla ett användarnamn, och att varje användarnamn måste vara unikt. Databashanteraren gör det i så fall omöjligt att skapa rader i användatabellen som saknar användarnamn, eller där användarnamnet redan finns på någon annan rad i tabellen.

SQL är ett väldokumenterat språk som väldigt många utvecklare behärskar. Det är därför lätt för nya utvecklare att sätta sig in i ett nytt system.

De flesta databashanterare innehåller avancerade kontroller av vem som har rättighet att utföra olika operationer i databasen.

Om man är osäker på hur man ska strukturera sin data kan det vara svårt att få till effektiva datastrukturer i en relationsdatabas.

Om ens data och datastrukturer ofta förändras kan en relationsdatabas vara olämplig då det är bökigt att ändra om strukturen i en relationsdatabas och fortfarande uppfylla alla regler och garantier.

När man har stora (riktigt stora) mängder data - så kallad Big Data - kan alla regler och garantier en relationsdatabas har sänka prestandan.

Även i de fall där man kontinuerligt har en stor ström med ny inkommande data, kan alla regler och kontroller kan göra att prestandan blir lidande.

Det kan även vara komplicerat att dela upp databasen på olika servrar, t.ex. genom så kallad "sharding".

För att skapa en relation mellan två tabeller använder man en främmande nyckel. Man skapar en främmande nyckel genom att kopiera in värdet från primärnyckeln i en-änden av relationen i en ny kolumn i många-änden av relationen.

I vårt fall innebär det att vi ska lägga till primärnyckeln från author-tabellen i books-tabellen (en för fattare kan ha skrivit flera böcker).

En tilläggsanomali (insertion anomaly) innebär att man inte kan lägga till data i en rad i en tabell innan man har information nog att fylla alla celler i raden.

I tabellen ovan går det inte (utan att införa null-värden) att

Detta är exempel på insertion anomalys. Insertion anomalys uppstår när man har "felaktiga" funktionella beroenden (se nedan) i tabellen.

En uppdateringsanomali (update anomaly) kan uppstå när man upprepar samma information på olika ställen i databasen. Det finns då en risk att man missar att ändra datan på flera olika ställen.

I tabellen ovan har adressen för beata endast uppdaterats i en av raderna. Update anomalys kan uppstå när man har "felaktiga" funktionella beroenden (se nedan) i tabellen.

En borttagningsanomali (deletion anomaly) innebär att man, när man tar bort en rad i en tabell, kan råka ta bort annan information som inte återkommer någon annanstans i databasern.

Om man i tabellen ovan tar bort all information om "adam" så kommer även course_name och course_code för "svenska" försvinna från databasen, eftersom ingen annan elev är inskriven i denna kurs. Deletion anomalys kan uppstå när man har "felaktiga" funktionella beroenden (se nedan) i tabellen.

Ett funktionellt beroende (fb) är ett beroende mellan attribut i en tabell. Låt oss anta att vi har en tabell med personnummer och namn:

I tabellen ovan finns ett funktionellt beroende mellan personnummer och namn. Detta innebär att för ett givet personnummer kommer alltid samma namn returneras, men det omvända är inte sant - ett namn kommer inte garanterat returnera samma personnummer. Med andra ord: om samma personnummer skulle förekomma på två rader i tabellen, så måste värdena namnen också vara lika.

Detta funktionella beroende kan beskrivas som personnummer → namn vilket utläses som "personnumer bestämmer namn" I det här exemplet är personnummer determinanten, eftersom den bestämmer eller determinerar namnet.

Generaliserat brukar man skriva ut funktionella betenden som A → B

I tabellen students ovan finns två olika, av varandra oberoende, funktionella beroenden:

Ett funktionellt beroende kan innehålla flera determinanter, det vill säga ett flera attribut kan tillsammans bestämma värdet på ett (eller flera) andra attribut.

Förutsatt att ingen artist får ge ut en låt med samma titel i olika album finns i songs-tabellen ovan (bland annat) följande funktionella beroenden:

Vilka andra funktionella beroenden kan du hitta?

Ett fullt eller fullständigt funktionellt beronde (ffb) är ett funktionellt beroende där man inte kan ta bort någon av determinanterna utan att det funktionella beroendet upphör att gälla. Detta innebär att alla funktionella beroenden som enbart har en determinant är fullständiga beroenden.

Fullt funktionella beroenden skrivs ut som A ⇒ B

I songs-tabellen ovan är {title, artist_name} ⇒ path ett fullt funktionellt beroende, eftersom man inte kan ta bort varesig title eller artist_name utan att det funktionella beroendet försvinner.

Vilka ffb skulle kunna finnas i tabellen nedan?

Om a → b och dessutom b → c så är c transitivt beroende av a via b (förutsatt att b eller c inte bestämmer a)

I tabellen ovan gäller (bland annat) id → school_name men även school_name → {school_address, school_phone} Detta innebär att school_address och school_phone är transitionellt beroende av id via school_name

En supernyckel (engelska: super key) består av ett eller flera attribut som tillsammans kan användas för att unikt identifiera en tupel i tabellen, det vill säga: Det kombinationen av värden bland de ingående attributen måste vara unika inom tabellen. I songs-tabellen ovan finns (bland annat) följande supernycklar:

En kandidatnyckel är en supernyckel utan redundanta attribut, det vill säga den supernyckel där vi inte kan ta bort fler attribut utan att vi inte längre kan garantera att raden kommer vara unik. I songs-tabellen ovan skulle kandidatnyckeln bestå av title tillsammans med artist_name

En primärnyckel (engelska: primary key) är den av kandidatnycklarna (om det finns flera) designern av databasen har valt ut att använda för interaktion med en given tabell.

Ett icke-nyckel-attribut (engelska: non-key attribute) är ett attribut som inte är ingår i någon av tabellens kandidatnycklar

I songs-tabellen är därmed följande attribut icke-primär-attribut: album, year, length, path, genre.

Regler för Första Normalformen (1NF)

Första normalformen är egentligen den enda normalform en relationsdatabas måste uppfylla för att fungera (men för att undvika uppdateringsanomalier (update anomalies) så är det rekommenderat att normalisera tom 3NF).

I students-tabellen ovan innehåller course-attributet flera värden, det vill säga, det är inte atomärt.

Man skulle kunna lösa problemet med att lägga in fler kolumner för kurser, t.ex course1, course2, course3, etc. Detta skulle dock betyda att de elever som inte läser maximalt antalt kurser kommer ha en eller flera kolumn utan värde i.

Om man ska lägga in elever med fler kurser än vad man räknat med skulle vara maximalt antal kurser skulle det inte att gå. Det kommer också att uppstå problem med frågeställningen i tabellen:

Man skulle även kunna lösa problemet genom att skapa en kolumn för varje kurs som finns, och som värde ha true/false/null baserat på om eleven läser kursen eller inte. Detta skulle innebära en väldig massa onödiga false eller nullvärden, och så fort en ny kurs läggs till eller tas bort skulle man behöva modifiera tabellen - en synnerligen dyr (tids- och resursmässigt) och otymplig operation.

En möjlig lösning som skulle uppfylla 1NF är att varje elev förekommer på flera rader:

Om man ser tillbaks till tilläggs/uppdaterings/borttagningsanomalierna kan man dock se att detta inte är en bra lösning.

Ett bättre alternativ är att uppfylla 1NF genom att dela upp tabellen i två olika tabeller:

Även om denna uppdelning är bättre ger den inte ett bra skydd mot anomalier - hur lägger man t.ex. till en kurs som ingen student läser, eller vad händer när ingen student längre läser t.ex. "EN7"?

Ännu bättre hade antagligen varit att dela upp tabellen i tre tabeller varav en relationstabell, men det bryr sig inte 1NF om.

Regler för Andra Normalformen (2NF)

Tabeller som uppfyller 2NF har mindre redundans är tabeller i 1NF men de kan fortfarande lida av uppdateringsanomalier.

Eftersom alla funktionella beroenden där det finns en ensam determinant är fullständiga funktioneller beroenden, innebär det att alla tabeller som upfyller 1NF och har en primärnyckel bestående av enbart 1 attribut automatiskt uppfyller 2NF. Detta är även det enklaste sättet att garantera 2NF.

I songs-tabellen är album, year, length samt path icke-nyckel-attribut som har ett fullt funktionellt beroende av vår kandidatnyckel {title, artist_name }. genre, däremot, har inte ett fullt funktionellt beroende på kandidatnyckeln - samma kandidanyckel kan användas för att få tillbaks olika genres. Detta innebär att vår tabell inte ännu uppfyller 2NF.

Vi börjar med att ange ett nytt attribut (id) som primärnyckel:

Eftersom primärnycklar måste vara unika inom en tabell har vi ett problem: id 2, 4 samt 5 förekommer flera gånger i tabellen. Detta är inte tillåtet.

För att komma åt detta behöver vi bryta ut genre till en egen tabell, då det är genre som är orsaken till att ett samma id förekommer flera gånger i tabellen.

För att garantera att genre-tabellen uppnår 2NF ser vi samtidigt till att ge den en primärnyckel (id) (eftersom genre i vår modell inte har några andra attribut kopplade till sig behövs det inte egentligen en ny primärnyckel, men det är enklast att oftast göra likadant).

Vi har nu ett nytt problem: Vi kan inte längre se vilken/vilka genres en låt tillhör.

I enligt med diskussionen om kurser i avsnittet om 1NF går det inte att lägga till några genre-attribut i songs-tabellen, och samma gäller eventuella songs-attribut i genre-tabellen. Eftersom vi i genres-tabellen har en primärnyckel med en ensam determinant får samma genre inte förekomma på flera rader.

Detta innebär att vi i detta fall behöver skapa en tredje-tabell:

genres-songs-tabellen innehåller följande funktinonella beroenden: {genre_id, song_id} ⇒ {genre_id, song_id}. Även här skulle vi, om vi ville, kunna stoppa in en ny primärnyckel med endast en determinant, men i det här fallet behövs det inte (eftersom genre-songs är en relationstabell utan icke-nyckel-attribut).

Nu uppfyller samtliga 3 tabeller 2NF.

Regler för Tredje Normalformen (3NF)

De transitionella beroendena i tabellen ovan (name → school_name och school_name → school_address) innebär redundant data i tabellen.

Genom att bryta ut de transitionella beroendena (och därmed den redundanta datan) i egna tabeller kan vi uppnå 3NF:

Regler för Boyce-Codd Normalform (BCNF)

I varje ände av en sambandstyp framgår dess kardinalitet (engelska: cardinality), eller multiplicitet som det kallas i UML — som mer exakt beskriver hur många exempel av varje sida som kan delta i sambandet.

Det finns tre grundläggande typer av kardinalitetsförhållanden:

Alla elever på NTI Johanneberg får låna en dator. Det finns ett inventeringssystem med en databas där alla elever, datorer och lån finns registrerade.

Systemet håller koll på:

Rita upp de entiteter, attribut och sambandstyper som databasen behöver — gärna i båda notationerna.

I årskurs tre får eleverna på NTI-Johanneberg matkort. Företaget som skapar och säljer matkorten till skolorna har (förhoppningsvis) en databas som håller koll på skolor, kort, och låntagare.

De behöver kunna hålla kolla på:

Rita upp de entiteter, attribut och sambandstyper som databasen behöver — gärna i båda notationerna.

Atomicitet (engelska: atomicity) innebär att en transaktion antingen genomförs i sin helhet (samtliga steg) eller så genomförs den inte alls. Om en transaktion avbruts innan alla steg genomförts ska databashanteraren kunna garantera att databasen är i samma state som innan transaktionen påbörjades.

"Consistency" är ett ord som saknar en tydlig svensk motsvarighet

Consistency garanterar att databasen alltid kommer vara "konsistent", dvs att en transaktion enbart kommer genomföras om databasen, efter transaktionen är genomförd, kommer vara i ett giltigt tillstånd. Man kan på svenska prata om "datavalidering" & "referensintegritet".

I de flesta databashanterare kan man skriva regler för hur rader och kolumner i tabeller ska se ut.

T.ex kan man bestämma att alla rader i användartabellen måste innehålla ett användarnamn, och att varje användarnamn måste vara unikt. Databashanteraren gör det i så fall omöjligt att skapa rader i användatabellen som saknar användarnamn, eller där användarnamnet redan finns på någon annan rad i tabellen

I de flesta databashanterare kan man även skriva regler för hur tabeller hör ihop. Detta gör det möjligt att garantera att kopplingarna mellan tabellerna stämmer, och att datan uppdateras på ett korrekt sätt.

Till exempel kan man se till att en användares alla meddelanden tas bort när användaren tas bort, eller att man enbart kan lägga till elever i klasser som faktiskt finns.

Isolation garanterar att även om transaktioner utförs parallellt kommer databasen hamna i samma state som om transaktionerna utförts sekventiellt. Detta innebär att en transaktion aldrig får se den "tillfälliga data" som skapas medans en annan transaktion utförs (innan transaktionen är klar).

Durability innebär att när en transaktion är genomförd kommer all data finnas sparad och är korrekt även om databaservern kraschar (vid t.ex ett strömavbrott). Skulle databashanteraren krascha mitt i en transaktion kommer datan återställas till läget innan kraschen.

Anta följande tabell:

För att flytta 2 000 kr från Anna till Bertil — utan att riskera att pengarna försvinner mitt i — skriver vi de två UPDATE-satserna inuti en transaktion:

Om något går fel — exempelvis om vi försöker flytta 6 000 kr (mer än Anna har) — bryter första UPDATE-satsen mot CHECK-begränsningen balance >= 0. Då vill vi avbryta hela transaktionen istället för att gå vidare:

Efter ROLLBACK ser kontona ut precis som innan vi körde BEGIN TRANSACTION.

För varje scenario nedan, ange vilken av ACID-egenskaperna (Atomicity, Consistency, Isolation, Durability) som skulle ha förhindrat problemet — och förklara kort varför.

Du har fått i uppdrag att designa databasen bakom ett mindre bibliotek. Verksamheten behöver hålla reda på böcker (med flera exemplar av populära titlar), medlemmar, samt vilka exemplar som är utlånade till vem och när.

Lös följande, i den ordning som passar dig:

Tänk på att modellen och transaktionen hänger ihop: om du t.ex. modellerar book och loan som separata entiteter blir utlåningen en annan transaktion än om du har en räknare available_copies direkt på book.

Välj ett av följande scenarier — eller ett eget förslag — och gör samma fyra steg som i bibliotekssystem-uppgiften:

När du är klar — försök komma på ett scenario i din valda domän där isolation-egenskapen specifikt är det som hindrar ett problem (alltså ett race condition mellan två samtidiga transaktioner).

För att se indexens påverkan på riktigt behöver du en tabell som är stor nog att skillnaden ska gå att mäta. Faker är en Ruby-gem som genererar realistisk testdata (namn, e-postadresser, städer, datum osv.) i den mängd du behöver.

Välj minst två av följande och förklara dina observationer med hjälp av EXPLAIN QUERY PLAN och tidmätning:

En komplex SELECT med flera JOIN, filter och beräkningar kan gömmas bakom en vy. Applikationen läser från vyn med en enkel SELECT, och själva komplexiteten ligger på ett ställe i databasen.

Genom en vy kan man exponera en denormaliserad bild av flera tabeller (en "join-färdig" version), så att klienter slipper själva förstå hur datan är spridd över relationsstrukturen.

Man kan ge rättighet att läsa en vy utan att ge rättighet till de underliggande tabellerna. Klassiskt exempel: visa en betyg_publika-vy som filtrerar bort vissa kolumner, utan att ge åtkomst till den underliggande betyg-tabellen.

Om flera applikationer behöver samma härledda fråga kan vyn vara enda sanningens källa. Ändras logiken behöver ändringen bara göras på ett ställe.

Varje SELECT från en vanlig vy kör hela den underliggande frågan på nytt. För dyra vyer som körs ofta blir kostnaden kännbar. Postgres och de flesta andra databashanterare har därför materialized views som lagrar resultatet och uppdateras manuellt eller schemalagt.

Vissa vyer går inte att skriva till. Om vyn aggregerar (GROUP BY, SUM) eller använder JOIN vet databasen inte hur ett INSERT eller UPDATE mot vyn ska översättas till de underliggande tabellerna.

När något går fel måste man förstå två saker: vyn och frågan den bygger på. För enkla vyer är det inget problem, men en vy som bygger på en annan vy som bygger på en tredje kan bli svår att felsöka.

SQLite stödjer inte vyer av det här slaget (eller stored procedures, eller triggers), så övningarna körs mot Postgres. Vi använder Docker för att slippa lokal installation.

Hämta docker-compose.yml och starta databasen:

Anslut med psql:

Verifiera att schemat är på plats. \dt ska lista de tre tabellerna, och kontoplanen ska vara seedad:

Alla saldo-värden ska vara 0. verifikat och konteringar är tomma.

Skriv en vy aktiva_konton som visar nummer, namn och typ för konton där saldot inte är 0. Eftersom inga bokföringar gjorts ännu kommer vyn vara tom — den blir intressant först när du gör övningarna i de följande kapitlen.

Skriv en vy verifikat_rader som joinar verifikat, konteringar och konton så att varje rad visar:

Använd vyn för att hitta alla rader för ett visst datum eller ett visst konto — utan att själv behöva skriva ihop JOIN-frågan varje gång.

Eftersom stored procedures lagras direkt i databashanteraren behöver inte databashanteraren tolka den inkommande SQL-strängen, utan kan kompilera och optimera funktionen när den skapas.

Även mängden data som behöver skickas över nätverket till databashanteraren kan minska. Istället för att skicka hela SQL-strängen behöver man enbart anropa funktionen (och om funktionen tar några parametrar, skicka med dessa).

Stored procedures gör det möjligt att lägga in mycket av den funktionalitet som annars skulle legat i en klient/webbserver direkt i databashanteraren. Detta minskar risken för att buggar i klient/webbserver påverkar databasens innehåll.

En stored procedure kan köras med andra rättigheter än den som anropar proceduren. Om en användare i vanliga fall inte har rättigheter att göra en DELETE kan möjligheten fortfarande finnas att göra det med en stored procedure, som någon (förhoppningsvis) kontrollerat att den inte gör något oönskvärt.

Stored procedures tar parametrar via typade argument istället för att klistras ihop till SQL-strängar. Det ger samma skydd mot SQL-injektion som parametriserade frågor. Förutsättningen är att proceduren inte själv bygger dynamisk SQL med användarinmatning, eftersom skyddet då försvinner.

Det kan vara svårt att veta var i applikationen logik finns om en del av logiken ligger i databashanteraren.

De flesta databashanterare har olika, inkompatibla språk och ramverk för stored procedures.

Den som skriver stored procedures behöver specialistkompetens, då de inbegriper nya språk och tankesätt.

Skriv en function saldo(nummer) som returnerar det aktuella saldot för ett konto, beräknat från konteringar-tabellen (inte från den cachade saldo-kolumnen).

Tänk på att kontots typ avgör hur saldot beräknas:

Så länge konteringar är tom ska alla saldon vara 0. Lägg in en rad manuellt och verifiera att din funktion ger rätt svar.

Skriv en procedure bokför(datum, beskrivning, konto_debet, konto_kredit, belopp) som registrerar ett verifikat med två konteringar:

Eftersom samma belopp används på båda sidor är dubbel bokföring garanterad av sig självt. Du behöver inte räkna efter.

Testa:

Använd din saldo-function för att verifiera saldona på de berörda kontona.

Gå tillbaka till resultaträkning-vyn du sketch:ade i kapitel 2.7 (Vyer). Nu finns det bokföringar i databasen. Kör några bokföringar (t.ex. ett par bokför_försäljning och en hyra) och kontrollera att SELECT * FROM resultaträkning; ger förväntat svar.

Notera dock att resultaträkning-vyn läser från konton.saldo-kolumnen, som fortfarande är 0 även efter dina bokföringar. För att fylla konton.saldo automatiskt behöver vi en trigger — vi tar oss an det i nästa kapitel.

En trigger garanterar att vissa följdåtgärder alltid sker när en tabell ändras. Klienten behöver inte komma ihåg att uppdatera en relaterad tabell, eftersom databasen sköter det själv.

Om flera applikationer eller tjänster skriver till samma tabell behöver varje klient inte upprepa samma logik. Triggern ligger på tabellen, inte i klientkoden, och fångar varje skrivning oavsett källa.

Ett vanligt mönster: en trigger på en tabell skriver varje ändring till en separat *_audit-tabell. Loggningen kan läggas till efter att applikationen är skriven, utan att en enda rad i den ursprungliga skrivkoden behöver ändras.

Triggers kör sig själva. Det är både deras stora styrka och deras stora fara: logik som "bara händer" är lätt att glömma bort när man felsöker. En utvecklare som ser en oväntad rad i konteringar_audit måste veta att en trigger finns för att förstå varifrån den kom.

En trigger körs vid varje påverkad rad. På bulkinserts (t.ex. en miljon rader) körs triggern en miljon gånger. För tunga triggers blir det en mätbar prestandakostnad.

Om en trigger ändrar en tabell som har sin egen trigger, kommer den triggern också att köras. Med några nivåers kaskader blir det mycket svårt att resonera kring vad som händer när en enda INSERT körs.

Funktionen saldo() från kapitel 2.8 räknar om saldot från grunden varje gång den anropas. På en stor databas är det dyrt. Istället vill vi att konton.saldo ska vara en cachad kolumn som hålls i synk automatiskt.

Skriv en trigger på konteringar som uppdaterar konton.saldo när en ny kontering läggs till. Tecknet beror på kontots typ, samma logik som i din saldo-function.

Verifiera genom att köra ett par CALL bokför(…​) och titta på konton.saldo-kolumnen direkt:

Tänk på:

resultaträkning-vyn från kapitel 2.7 (Vyer) läser från konton.saldo-kolumnen. Med triggern på plats uppdateras konton.saldo automatiskt vid varje bokföring, så vyn återspeglar nu företagets ekonomi i realtid.

Kör några bokföringar via bokför och bokför_försäljning (från kapitel 2.8) och kontrollera att SELECT * FROM resultaträkning; ger förväntat svar. Du behöver inte göra något manuellt för att hålla siffrorna aktuella.

Nu har du sett tre verktyg som flyttar logik in i databasen: vyer (sparade frågor), stored procedures (sparad kod) och triggers (kod som körs automatiskt). Var och en av dem har sin plats.

Tänk själv eller diskutera med en kursare: vilka av reglerna i ett bokföringssystem hör hemma som procedure/function/trigger/vy i databasen, och vilka hör hellre hemma i applikationskoden? Motivera utifrån för- och nackdelarna i de tre kapitlen.

Konkreta exempel att diskutera:

Fördelar:

Nackdelar:

Fördelar:

Nackdelar:

Användarna lagras i en collection. Grupperna lagras i en annan och innehåller bara id:n till sina medlemmar.

För att visa gruppens medlemmar måste klienten göra två frågor. Först hämta gruppen, sedan slå upp användarna utifrån listan med id.

Grupperna innehåller hela användarobjektet (eller relevanta delar av det), inte bara id:t. Användarna finns ändå kvar i sin egen collection — den är "sanningen" — men en kopia av relevanta fält bäddas in i gruppen.

Nu räcker en enda fråga för att visa gruppen med dess medlemmar. Men username finns på två ställen i databasen. Om Eva byter namn behöver båda kopiorna uppdateras.

Information ska bara vara åtkomlig för de personer som har rätt att se den. Att en obehörig kan läsa en kund-tabell är ett brott mot sekretessen, även om hen inte ändrar något.

Information ska vara korrekt och får inte ändras av obehöriga eller av misstag. Att en angripare ändrar ett saldo i en banktabell, eller att en bugg gör att en användarpost skrivs över, är båda brott mot data-integriteten.

Information ska vara tillgänglig när den behövs. Att en databas ligger nere när användarna försöker logga in är ett brott mot tillgängligheten, även om datan i sig är intakt.