Leksjon 2: Lage databasetabeller

Læringsmål

• Forstå hvordan informasjon kan organiseres i databasetabeller.
• Kunne bruke SQL for å definere tabeller med datatyper, primærnøkler, fremmednøkler og forretningsregler.
• Kunne bruke phpMyAdmin for å opprette tabeller.
• Kunne bruke phpMyAdmin for å opprette tabeller og importere data fra tekstfiler.

Pensum

Avsnitt 3.1-3.2 fra Databasesystemer.

Nettsidene til læreboken

Forelesning

Lage og bruke tabeller (dvs. lysark 1 – 21)

Video ★ Skjerm ★ Utskrift

A. Lage tabeller: Sykkelmodeller og sykler

Databasen skal ta vare på opplysninger om sykkelmodeller og (konkrete) sykler – i to forskjellige tabeller:

• Modell(MNr, Fabrikk, Betegnelse, Kategori, Dagpris)
• Sykkel(MNr, KopiNr, Ramme, Farge)

Bedriften disponerer altså et antall sykler ("kopier") av hver sykkelmodell, kanskje 2 sykler av modell 1, 3 sykler av modell 2 osv.

Tips:

• Se på eksempeltabellene!
• Du må selv velge datatyper: MNr, KopiNr og Ramme inneholder heltall, Dagpris inneholder desimaltall, mens øvrige kolonner inneholder tekst.
• Du må også velge primærnøkler. Ofte vil primærnøkkelen være et "løpenummer", men det kan være nødvendig å kombinere to eller flere kolonner.
• Merk at det i tabellen Sykkel er gjentakelser i både kolonne MNr og kolonne KopiNr.
• Valg database i venstremenyen først, og lag så tabeller ved å kjøre CREATE TABLE kommandoer i SQL-vinduet.
• Begge tabellene bør ha primærnøkkel. En av tabellene bør ha en fremmednøkkel (som "kobler sammen" tabellene): Alle MNr-verdier i Sykkel skal eksistere i Modell! Det enkleste er å lage primærnøkler og fremmednøkler som del av CREATE TABLE kommandoen.

B. Lage tabeller: Kunder og utleieforhold

Databasen skal utvides med informasjon om kunder og utleieforhold:

• Kunde(KNr, Fornavn, Etternavn, Mobil)
• Utleie(KNr, MNr, KopiNr, TidUt, TidInn)

Tips:

• Kolonnen KNr i tabellen Kunde skal være autonummerert.
• Kolonnene KNr, MNr, Kopinr inneholder heltall, TidUt og TidInn inneholder begge datoer, mens øvrige kolonner inneholder tekst.
• Hvis du prøver å utføre SQL-spørringer som definerer tabeller flere ganger får du feilmelding. Da må du først slette tabellen!
• Bruk kommandoen DROP TABLE for å slette tabeller.
• For tabellen Utleie må du ta med flere kolonner i primærnøkkelen. To eksempler på feilaktige forsøk: Hvis du velger KNr som primærnøkkel kan en kunde ikke leie sykkel mer enn én gang! Hvis du velgeR MNr og KopiNr som primærnøkkel kan ikke samme sykkel leies ut mer enn én gang! Kommentar: I praksis ville man nok innført en ny kolonne UtleieNr og valgt denne som primærnøkkel, men det er god "primærnøkkeltrening" å klare seg uten.
• Ved utleie vil TidInn typisk få et nullmerke. Tidspunktet blir så registrert ved innlevering. Utleieforhold med nullmerke i TidInn er altså "aktive".

C. Teste primærnøkler og fremmednøkler med phpMyAdmin

Du skal nå teste primærnøkler og fremmednøkler ved å registrere data direkte i brukergrensesnittet til phpMyAdmin:

• Legg inn en sykkelmodell med MNr=1. Velg øvrige verdier selv.
• Prøv å legge inn nok en sykkelmodell med MNr=1. Hva skjer?
• Legg inn en sykkel med MNr=1 og KopiNr=1.
• Prøv å legge inn nok en sykkel, denne gangen med MNr=99 og KopiNr=1. Hva skjer?

D. Importere data

Du skal nå importere data fra fire tekstfiler på CSV-format (Comma Separated Values). Hver linje på filene svarer til en rad i tilhørende databasetabell. Første linje inneholder kolonnenavn, og verdiene er i vårt tilfelle adskilt med semikolon (skilletegn er altså ikke alltid komma). Slike filer egner seg godt som overføringsformat mellom ulike databaser.

• Datafiler: modell.csv, sykkel.csv, kunde.csv, utleie.csv
• Ta gjerne en titt på innholdet i filene først, de kan åpnes i Sublime eller en annen editor. På Windows vil slike filer ofte bli åpnet i Excel – høyreklikk og velg Åpne i Sublime i stedet.
• Bruk knapp Importer i menylinjen øverst for å starte import.
• Pass på at du importerer data i riktig rekkefølge: Start med tabeller som ikke har fremmednøkler til andre tabeller!
• Datafilene inneholder kolonnenavn på første linje. Sørg for å hoppe over denne linjen ved import!
• Legg merke til hva som er brukt som skilletegn på datafilene, og angi dette ved import!

Sjekk at innholdet i tabellene er som forventet før du går videre.

modell.csv ★ sykkel.csv ★ kunde.csv ★ utleie.csv

E. Forretningsregler med NOT NULL og UNIQUE

Endre SQL-koden du laget i oppgave C, slik at både fornavn og etternavn på kunder må fylles ut (NOT NULL), og dessuten skal systemet hindre at to kunder har samme mobilnummer (UNIQUE). Kjør skriptet på nytt, og sjekk deretter at forretningsreglene virker.

Utvid med tilsvarende regler på andre kolonner i databasen, der du mener det er hensiktsmessig.

F. Forretningsregler og fremmednøkler

Du skal nå lage hjelpetabeller for å ta vare på lovlige farger og kategorier, og deretter bruke fremmednøkler for å sikre at kun lovlige verdier blir registrert.

• Lag tabeller Farge og Kategori. Begge tabeller skal kun inneholde en eneste kolonne med samme navn som tabellen. Sørg for at kolonnene Sykkel.Farge og Modell.Kategori blir fremmednøkler mot disse hjelpetabellene.
• Fyll tabellene med data fra tilhørende kolonner Sykkel.Farge og Modell.Kategori. Det kan f.eks. gjøres ved å taste inn for hånd i phpMyAdmin, eller lagre verdiene på en tekstfil og importere. (Det finnes flere teknikker.)
• Sjekk at du nå ikke får registrert sykler i andre farger enn de som er registrert i tabellen Farge.
• Endre i tabellen Farge slik at det blir mulig å registrere grønne sykler.
• Ser du muligheter for å innføre flere slike hjelpetabeller?

Kommentar

Avsnitt 3.2.8 i læreboken tar for seg CHECK-regler, som vi f.eks. kunne brukt til å sikre at alle utleiepriser var større enn 0 kr. og mindre enn 500 kr. MySQL støtter slike regler fra versjon 8.0. Tidligere versjoner godtar at vi bruker slike CHECK-regler i CREATE TABLE, men kontrollerer desverre ikke at reglene blir fulgt. For å få slike versjoner av MySQL til å sjekke denne typen av forretningsregler må man bruke såkalte triggere, se avsnitt 13.3 (ikke pensum).

Fra og med versjon 10.2.1 støtter MariaDB CHECK-regler, som skal bety at dette nå er støttet på itfag.usn.no.

G. Ekstraoppgave (uten løsning)

Ta utgangspunkt i eksempeldatabasen til oppgave 1, eksamen vår 2018.

Skriv først SQL-kommandoer som oppretter tabellene til denne databasen. Prøv deretter å generere testdata til tabellene (trenger ikke å være de samme som vist i vedlegget til eksamensoppgaven). Tips: Bruk mulighetene for generering av eksempeldata forklart på denne nettsiden.