USN – Nivå 2

Leksjon 2: Lage databasetabeller

Før du kan registrere data og lage spørringer må du definere databasetabellene: Hva skal tabellene hete? Hvilke kolonner skal de inneholde? Hva slags data vil bli lagret i de ulike kolonnene? SQL har en egen kommando for å lage tabeller: CREATE TABLE.

Hver tabell bør ha en primærnøkkel for entydig å kunne identifisere rader i tabellen. En database består av mange tabeller. Fremmednøkler sørger for å etablere logiske sammenhenger mellom tabellene. Forretningsregler bidrar til å sikre kvaliteten på data som blir registrert.

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.

Oppgaver

Oppgavene går ut på å bygge opp en database for sykkelutleie. Du skal først opprette tabeller for å holde rede på sykler, kunder og utleieforhold. Deretter skal du importere data fra tekstfiler (.csv).

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.
• Velg 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

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. (I praksis kunne man valgt en ny kolonne UtleieNr som primærnøkkel.)
• Ved utleie vil TidInn typisk få et nullmerke. Tidspunktet blir så registrert ved innlevering. Utleieforhold med nullmerke i TidInn er altså "aktive".

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.

• Datafiler: modell.csv, sykkel.csv, kunde.csv, utleie.csv
• Importer i riktig rekkefølge: Start med tabeller som ikke har fremmednøkler til andre tabeller!
• Datafilene inneholder kolonnenavn på første linje. Hopp over denne linjen ved import!
• Angi riktig skilletegn (semikolon).
• Sjekk at innholdet i tabellene er som forventet før du går videre.

E. Forretningsregler med NOT NULL og UNIQUE

Endre SQL-koden du laget i oppgave B, 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

1. Lag tabeller Farge og Kategori. Begge tabeller skal kun inneholde en eneste kolonne med samme navn som tabellen.
2. Sørg for at kolonnene Sykkel.Farge og Modell.Kategori blir fremmednøkler mot disse hjelpetabellene.
3. Fyll tabellene med data fra tilhørende kolonner Sykkel.Farge og Modell.Kategori. (Det finnes flere teknikker.)
4. 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.
5. Ser du muligheter for å innføre flere slike hjelpetabeller?

G. Ekstraoppgave (uten løsning)

Ta utgangspunkt i eksempeldatabasen til oppgave 1 (lagAnsatt.sql). Skriv først SQL-kommandoer som oppretter tabellene til denne databasen (CREATE TABLE). Prøv deretter å generere testdata til tabellene manuelt ved hjelp av (INSERT INTO).