Fra modell til database

av Ole Petter den 18. februar 2020

Logiske datamodeller

Veien fra modell til database innebærer å oversette ER-diagrammer til tabellstruktur. Prosessen kalles logisk databasedesign, som til slutt ender opp med en logisk datamodell.

Entiteter i en modell blir til tabeller, attributter blir kolonner, identifikatorer blir til primærnøkler, og forhold til fremmednøkler.

Dobbeltlagring kalles også redundans, og dette gjør at databasen tar lengre tid å søke gjennom ved spørringer, samt at den krever mer lagringsplass. Risikoen for inkonsistent database øker også.

Ved hjelp av normalisering kan man redusere duplisering av informasjon. Det brukes til å vurdere om en gitt tabellstruktur er god nok, og er en prosess for å dele opp tabeller i flere, enklere tabeller. Dette gjøres for å redusere redundans og samtidig bevare informasjon.

Normalisering

Redundans betyr overflødig informasjon, eller unødvendig gjentakelse. Hvis en tabell inneholder informasjon om en person og postnummer, vil man måtte fylle ut postnummer for hver person. En bedre løsning er å lage en egen tabell for postnummer, og lage et forhold mellom tabellen person og tabellen postnummer.

Da kan man opprette et postnummer i en tabell, og knytte dette postnummeret til flere personer. Det er også en bedre løsning ved oppdatering av data. Det hadde blitt tungvint hvis man måtte oppdatere postnummeret for hver person hvis dette endret seg. Da er det langt enklere å kun endre postnummeret i den tilhørende tabellen som samler postnumre.

Et postnummer har også et tilhørende poststed. Det finnes ikke flere poststeder med samme postnummer, for eksempel er poststed for postnummer 5007 Bergen. Postnummer bestemmer poststed, og dermed eksisterer det en funksjonell avhengighet fra postnummer til poststed.

På samme måtte er det funksjonelle avhengigheter mellom fødselsnummer -> fornavn, og fødselsnummer -> etternavn.

Problemer med dårlig design

CRUD er et kjent begrep i databaser, og det står for Create, Read, Update og Delete. Problemet med dårlig databasedesign manifesterer seg i at man må endre store mengder data hvis man har redundans.

• Hvis man har en ordretabell og man lagrer navnet på varen i ordren, må man endre alle rader i tabellen med denne ordren hvis man skal endre navnet på varen. Hvis man kun endrer informasjonen i noen av radene, blir databasen inkonsistent, altså inneholder den motstridende informasjon.

• Hvis man lagrer varene kun i tabellen ordre, vil man ikke kunne registrere en ny vare før denne varen har en ordre. Dette er fordi primærnøkkelen i en tabell ikke kan inneholde nullmerker.

• Hvis man kun har en ordre av en vare, mister man da all informasjon om denne varen hvis denne ordren blir slettet.

Samlebetegnelsen på disse situasjonene kalles oppdateringsanomalier. Det blir også vanskeligere å gjennomføre spørringer mot databaser som har en dårlig tabellstruktur.

Dekomponering av tabeller

Ved å dele tabeller opp i enklere tabeller kan man fjerne muligheten for redundans.

Ved å splitte tabellene i to kan man redusere redundans, siden flere rader har samme postnummer. Da kan man opprette en ny tabell for postnummer.

Tabell person:

Tabell post_nr

Normalisering går ut på å utføre normaliseringssteg, altså dekomponere tabeller, til all redundans er fjernet. I dette tilfellet brukes post_nr som fremmednøkkel. Det gjør at all tilhørende informasjon kan hentes fra tabellen post_nr, i steden for at all informasjon blir samlet i tabellen person.

1NF, 2NF og 3NF

Normalisering bygger på definisjonen av et antall normalformer.

1NF

En tabell oppfyller første normalform dersom alle poster i relasjonen er atomære.

Eksempel

Tabellen over bryter første normalform fordi verdiene ikke er atomære. Nedenfor er samme data normalisert.

2NF

En tabell oppfyller andre normalform dersom den oppfyller kravene for første normalform, og i tillegg ikke inneholder partielle avhengigheter.

Eksempel

Tabellen over bryter med andre normalform fordi kolonnen faglærer kun er avhengig av en del av den sammensatte primærnøkkelen emnekode og studentnummer.

Faglærer kolonnen er kun avhengig av emnekode i motsetning til kolonnen karakter som er avhengig av hele primærnøkkelen. Ved å flytte faglærer til en ny tabell hvor vi oppretter emnekode som primærnøkkel og lar emnekoden i den opprinnelige tabellen referere til denne.

Da vil tabellene oppfylle kravene til andre normalform samtidig som vi minimerer overflødig data ved at faglærerens navn kun oppføres en gang for hver unike emnekode:

3NF

En tabell er på tredje normalform (3NF) dersom den er på 2NF og den ikke inneholder transitive avhengigheter. Det betyr at ingen ikke-nøkkelattributter skal være avhengige av noe annet enn primærnøkkelen.

Som vist tidligere i postnummer-eksempelet kan man oppfylle 3NF ved å opprette en egen tabell som lagrer poststed med postnummer som primærnøkkel i tabellen, og fremmednøkkel i tabellen person.

Man gjentar da ikke poststed flere ganger for det samme postnummeret.

Tabell person:

Tabell post_nr

Boyce-Codd NormalForm (BCNF)

En determinant er en kolonne som bestemmer en annen. En kolonne som bestemmer enhver annen kolonne kalles en supernøkkel. En kandidatnøkkel er en minimal supernøkkel.

En relasjon er på BCNF hvis alle attributter kun er avhengige av supernøkler. Vanligvis oppfyller relasjoner på 3NF også BCNF.

Forskjellen mellom BCNF og 3NF

Definisjonene av 2NF og 3NF bygger på primærnøkler, mens BCNF bygger på begrepene determinant og kandidatnøkkel. For tabeller som kun har en eneste kandidatnøkkel, og dermed også er primærnøkkel, er det ikke noen forskjell på 3NF og BCNF.

Forskjellen dukker først opp i tabeller med overlappende kandidatnøkler. Hvis man har en tabell for en kino med flere kinosaler, med attributtene

Kinosal, Dato, kl_slett, film_nr,

har man en funksjonell avhengighet mellom film_nr og Kinosal. Dette fordi Kinosal og film_nr begge er kandidatnøkler.

Kinosal, Dato, kl_slett

film_nr, Dato, kl_slett

For de fleste tabeller vil normalisering til 3NF også gi BCNF, men i tilfellet ovenfor er den fortsatt bare på 3NF fordi det oppstår et brudd på BCNF. For at tabellen skal oppfylle BCNF må tabellen dekomponeres i to nye tabeller:

forestilling(Dato, kl_slett, film_nr*)

film(film_nr, Kinosal)

Denormalisering

Ved normalisering splittes tabeller opp for å unngå redundans. Problemet med full normalisering er at det kan gi et tregere databasesystem. Flere tabeller fører til behov for flere likekoblinger. Denormalisering innebærer at man godtar en viss redundans for å oppnå et mer effektivt system.

I eksempelet med kinosalen er det enklere å beholde tabellen på 3NF, som gir enklere kontroll med funksjonelle avhengigheter og slipper en ekstra kobling for spørringer som bruker alle kolonnene.

Ulempen med denne løsningen er økt redundans eller dobbeltlagring, for eksempel må kinosalen kontrolleres for hver film. Denne formen for normalisering medfører såkalt kontrollert redundans.

Normalisering utover BCNF

Normalisering til og med BCNF bygger på funksjonell avhengighet, A -> B betyr at det for en bestemt A-verdi finnes bare en B-verdi. Derimot kan det også oppstå redundans selv om det til en A-verdi finnes et antall B-verdier.

• En ansatt kan selge en bestemt samling av produkter
• En bestemt ansatt kan selge til en bestemt samling av kunder
• Mengden av produkter og kunder for en ansatt er uavhengige. Dette betyr at hvis en ansatt selger produkt X til kunde 1 må han selge produkt X også til alle de andre kundene.

Her er det ingen funksjonelle avhengigheter. Den eneste kandidatnøkkelen er Ansatt + Produkt + Kunde, og tabellen er på BCNF. Samtidig har man redundans for eksempel ved at informasjonen «ansatt 25 selger X» blir ggjentatt i figuren ovenfor.

Hvis det for enhver A-verdi finnes en bestemt mengde av B-verdier, sier vi at det eksisterer en flerverdiavhengighet fra A til B, skrevet A=>B. En tabell er på fjerde normalform (4NF) dersom den er på BCNF og ikke inneholder flerverdiavhengigheter.

Det finnes også en femte normalform, 5NF, som bygger på begrepet tapsfri dekomposisjon. Dette innebærer at vi fra en oppdeling kan få tilbake den opprinnelige tabellen ved likekobling.

Bruk av visninger i databasedesign

Visninger har flere bruksområder

• Sikkerhet
  Det kan være ønskelig å forhindre enkelte brukere fra å bruke visse deler av databasen. Ved hjelp av visninger kan dette gjøres på en mer finmasket måte.
• Presentasjon
  I en relasjonsdatabase blir informasjon lagret i flere tabeller. Det betyr ikke at oppdelingen må være synlig for sluttbrukerne. En normalisert database vil kunne virke forvirrende hvis man ikke kjenner teorien bak. Visninger gir oss muligheten til å skjule den underliggende tabellstrukturen og presentere utvalgte deler av databasen tilpasset hver enkelt brukergruppe.
• Representasjonsuavhengighet
  Ved å jobbe med databasen gjennom visninger blir man i mindre grad avhengige av hvordan data er representert. Visninger gjør det mulig for databaseadministratoren å endre definisjonen av de underliggende tabellene uten at brukerne blir berørt.

Visninger som forenkling

Det er ikke alltid hensiktsmessig å la sluttbrukere jobbe direkte med tabellene i en database. En salgssjef trenger gjerne ikke oversikt over hvert enkelt salg, men gjerne en oversikt som viser salg i ulike kategorier på en enkel og oversiktlig måte.

Et eksempel på en visning av salg som kan brukes av salgssjefer:

CREATE VIEW Salg AS
SELECT OL.*, V.Betegnelse, K.Navn AS Kategori,
O.OrdreDato, O.KNr
FROM Ordre AS O, Ordrelinje AS OL,
Vare AS V, Kategori AS K
WHERE OL.OrdreNr = O.OrdreNr
AND OL.VNr = V.VNr
AND V.KatNr = K.KatNr

Denne visningen kan opprettes ved flere steg. Det er mulig å lage visninger som igjen bygger på andre visninger. Fra visningen ovenfor, Salg, kan det opprettes en ny visning for salgssjefen som oppsummerer salg i inneværende måned:

CREATE VIEW SalgDenneMnd AS
SELECT VNr, Kategori, SUM(Antall*PrisPrEnhet) AS Salgssum
FROM Salg
WHERE YEAR(OrdreDato) = YEAR(CURDATE())
AND MONTH(OrdreDato) = MONTH(CURDATE())
GROUP BY VNr, Kategori

Gjennom denne visningen kan salgssjefen produsere en salgsstatistikk fordelt på kategori:

SELECT Kategori, SUM(Salgssum) AS SalgPrKategori
FROM SalgDenneMnd
GROUP BY Kategori

Representasjonsuavhengighet

Man kan jobbe med databasetabeller uten å vite hvordan de fysisk er representert. Dette kalles representasjonsuavhengighet, og oversettelsen mellom vår forståelse av tabeller og den fysiske representasjonen er en oppgave for DBHS.

Ved å bruke visningen Salg slipper salgssjefen å forstå hvordan informasjon er representert i fire forskjellige tabeller som databasen er bygd opp av. Hver enkelt bruker kan betrakte den samme databasen fra ulike perspektver, og ikke tenke over at det den vedkommende person ser, kun er en virtuell tabell.

En annen fordel med visninger er at det blir mulig å begrense arbeidet forbundet med endringer i tabellstrukturen.