Løbeseddel 6

Løbeseddel 6: Databaseprogrammering med SQL

af Martin Elsman og Niels Hallenberg sidst rettet 15. februar, 2002

I denne øvelse skal du benytte sproget SQL (Structured Query Language) til at oprette tabeller i en Oracle-database, som kører på hug.it.edu. For at tilgå databasen er det nødvendigt at du logger ind på hug.it.edu med ssh (Secure Shell). Ved UNIX-prompten på hug.it.edu skriver du blot sql for at starte Oracle-programmet SQL*Plus:

  $ sql

SQL*Plus giver dig mulighed for blandt andet at oprette tabeller og indsætte og ændre indholdet af tabeller i databasen på hug.it.edu.

I den sidste opgave skal du anvende regulære udtryk til at lave tre procedurer, som kan checke om formvariablerne svarer til en email, et tal eller en dato.

Opgave A (30 procent)

I denne opgave skal du skrive en række SQL-kommandoer til at skabe tabeller indeholdende et udpluk af IT-C's kurser og lærere. Følgende tabeller ønskes oprettet:

itc_kurser -- information om kurser
itc_personer -- personoplysninger
itc_kursusansvar -- hvem er ansvarlig for hvilke kurser

Tabellen itc_kurser indeholder stamoplysninger for kurser, itc_personer indeholder stamoplysninger for personer og tabellen itc_kursusansvar registrerer hvilke personer der er ansvarlig for hvilke kurser. Bemærk, at et kursus kan have flere kursusansvarlige og en person kan være ansvarlig for flere kurser. Begge dele forekommer f.eks. på IT-C. Billedet nedenfor viser de to en-til-mange relationer.

Tabellen `itc_kurser`

Konstruer en tabel itc_kurser ved brug af SQL-kommandoen create table. Tabellen skal have to kolonner:

kursus_id -- kursusforkortelse (f.eks., DWEB eller GP); højest 10 tegn
kursusnavn -- højest 100 tegn

Du skal konstruere tabellen således at Oracle-databasen sikrer (1) at en kursusforkortelse (kursus_id) højest kan forekomme i tabellen en gang (unique), og (2) at felterne kursus_id og kursusnavn ikke er tomme (not null). Kombinationen unique og not null opnås med primary key.

Indsæt mindst fem kurser (deriblandt de kurser du følger) i tabellen med SQL-kommandoen insert; du skal selv finde på kursus_id's for kurserne du indsætter. Undersøg hvad der sker hvis du prøver at indsætte to kurser med samme kursus_id i tabellen.

Brug SQL-kommandoen

  select * from itc_kurser;

til at se indholdet af tabellen. Hvis du ikke er helt tilfreds med formatet, kan du bruge SQL-kommandoerne

  column kursus_id format a10
  column kursusnavn format a40

til at pynte på resultatet af select-kommandoen.

Som besvarelse skal du gemme SQL-kommandoerne til oprettelse af tabellen itc_kurser i en fil /web/login/www/oevelse6/itc_kurser.sql på hug.it.edu. Det er muligt i SQL*Plus at indlæse SQL-kommandoer fra en fil:

  SQL> @/web/login/www/oevelse6/itc_kurser.sql

Denne mulighed gør det nemt at genskabe en datamodel, efter at de pågældende tabeller er droppet med SQL-kommandoen

drop
table

Tabellen `itc_personer`

Du skal nu kontruere en tabel itc_personer med fire kolonner:

person_id -- integer
email -- email-adresse; højest 100 tegn
navn -- højest 100 tegn
tilknytning -- f.eks. 'studerende', 'adm' og 'lærer'; højest 20 tegn

Du skal konstruere tabellen således at kolonnerne person_id og navn er ikke-tomme (

not
null

). Yderligere skal kolonnen person_id være en primær-nøgle (primary key). Herved vil Oracle sørge for at to forskellige personer ikke optræder i tabellen med samme person_id. Indsæt nu, med SQL-kommandoen insert, lærerne knyttet til kurserne i tabellen itc_kurser; find selv på nogle person_id's for personerne.

Endelig skal du gemme SQL-kommandoerne til oprettelse af tabellen itc_personer i en fil /web/login/www/oevelse6/itc_personer.sql på hug.it.edu.

Brug SQL-kommandoen

  select * from itc_personer

til at se indholdet af tabellen. Hvis du ikke er helt tilfreds med formatet, kan du bruge SQL-kommandoerne

  column email format a20;
  column navn format a30;
  column tilknytning format a10;

til at pynte på resultatet af select-kommandoen.

Tabellen `itc_kursusansvar`

Denne tabel skal indeholde tre kolonner:

kursus_id -- reference til tabel itc_kurser
person_id -- reference til tabel itc_personer
semester -- fem tegn (f.eks., F2001)

Konstruer tabellen således at kolonnen kursus_id refererer til kolonnen kursus_id i tabellen itc_kurser (d.v.s., du skal knytte et referential constraint til kolonnen kursus_id;

references
itc_kurser

.) På denne måde vil Oracle sikre at personer kun bliver knyttet til kurser som forekommer i tabellen itc_kurser. Tilsvarende skal kolonnen person_id referere til kolonnen person_id i tabellen itc_personer. Yderligere skal der kræves at data i semester-kolonnen er ikke-tom (

not
null

Indsæt data i tabellen med SQL-kommandoen insert for et udpluk af IT-C's kurser dette og sidste semester.

Gem SQL-kommandoerne til oprettelse af tabellen itc_kursusansvar i en fil /web/login/www/oevelse6/itc_kursusansvar.sql på hug.it.edu.

Følgende select-kommando kan bruges til at skabe et overblik over IT-C's kurser et givet semester (E2001):

  column kursus_id format a10
  column kursusnavn format a30
  column ansvarlig format a20

  select itc_kurser.kursus_id, itc_kurser.kursusnavn,
           itc_personer.navn as ansvarlig
    from itc_kurser, itc_personer, itc_kursusansvar
   where (itc_kurser.kursus_id = itc_kursusansvar.kursus_id
          and itc_personer.person_id = itc_kursusansvar.person_id
          and semester = 'E2001');

Gem uddata fra denne select-kommando i en fil /web/login/www/oevelse6/kurser_E2001.txt på hug.it.edu.

Opgave B (20 procent)

I denne opgave skal du konstruere tabeller til registrering af publikationer fra IT-C:

itc_publikationer -- information om publikationer
itc_forfattere -- hvem er forfatter til hvilke publikationer

En forfatter kan skrive flere publikationer og en publikation kan være skrevet af flere forfattere:

Tabellen `itc_publikationer`

Først skal du konstruere en tabel itc_publikationer med følgende kolonner:

publ_id -- integer
titel -- højest 100 tegn
publ_aar -- integer
publ_maaned -- integer
note -- højest 100 tegn

Konstruer tabellen med kolonnen publ_id som en primær-nøgle (primary key). Oracle vil da sikre at forskellige publikationer ikke får tildelt samme publikationsnummer og at et publikationsnummer bliver tilknyttet hver publikation.

Brug SQL-kommandoen

  select * from itc_publikationer;

til at se indholdet af tabellen. Hvis du ikke er helt tilfreds med formatet, kan du bruge SQL-kommandoerne

  column titel format a20;
  column note format a20;

til at pynte på resultatet af select-kommandoen.

Gem SQL-kommandoerne til oprettelse af tabellen itc_publikationer i en fil /web/login/www/oevelse6/itc_publikationer.sql på hug.it.edu.

Tabellen `itc_forfattere`

Bemærk, at der til tabellen itc_publikationer ikke er knyttet nogen forfatterkolonne. I stedet skal du konstruere en tabel itc_forfattere, som knytter itc_personer til publikationer. Grunden til at datastrukturen ønskes konstrueret således er at der kan være flere forfattere knyttet til hver publikation og at der til hver person kan være knyttet flere publikationer (der er tale om to en-til-mange relationer.) Tabellen itc_forfattere skal indeholde følgende kolonner:

publ_id -- reference til tabel itc_publikationer
person_id -- reference til tabel itc_personer

Konstruer tabellen således at publ_id-kolonnen refererer til publ_id-kolonnen i tabellen itc_publikationer, med et referential constraint (references itc_publikationer); Oracle-databasen vil så sikre at kun publikationer, der er oprettet i tabellen itc_publikationer kan knyttes til en person. Sørg ligeledes for at man kun kan knytte en publikation til en person, som allerede er oprettet i tabellen itc_personer (references itc_persoer).

Indsæt tre eller flere publikationer i databasen og knyt dem til nogle personer i tabellen itc_personer.

Gem SQL-kommandoerne til oprettelse af tabellen itc_forfattere i en fil /web/login/www/oevelse6/itc_forfattere.sql på hug.it.edu.

Endelig skal du opskrive en select-kommando til at hente publikationer fra en given måned ud af databasen, f.eks. måned 3. Det forventes, at udtrækket inkluderer forfatternavn og titel på publikation. Med mine data ser udtrækket således ud for måned 3:

NAVN		     TITEL		  PUBL_MAANED
-------------------- -------------------- -----------
Peter Sestoft	     Endnu en publikation           3

Gem denne select-kommando i en fil /web/login/www/oevelse6/itc_publ_select.sql på hug.it.edu.

Opgave C (20 procent)

I denne opgave skal du først indsætte (med SQL-kommandoen insert) fem eller flere studerende i tabellen itc_personer.

Konstruer herefter en tabel til registrering af studerendes kursustilknytninger. Tabellen skal referere tabellen itc_personer og tabellen itc_kurser. Indsæt i tabellen, som et minimum, de kurser som du selv følger i dette semester.

Gem SQL-kommandoerne til oprettelse af tabellen i en fil /web/login/www/oevelse6/itc_studerende.sql på hug.it.edu.

Opgave D (30 procent)

I denne opgave skal du konstruere tre procedurerer, som du løbende kan anvende til at checke formvariabler. Procedurerne skal kunne checke om en formvariabel svarer til en email, et positivt tal eller en dato skrevet på et bestemt format DD/MM-YYYY.

Procedurene skal returnere en html-side, der indeholder en beskrivende fejlmeddelelse, hvis det indtastede ikke modsvarer det forventede, se eksemplet nedenfor. Hvis der returneres en fejlside, så skal procedureren også afslutte scriptet, dvs. kalde Tcl kommandoen exit.

Du skal lave en HTML form, hvori du kan skrive tre værdier, dvs. en af hver type, se nedenfor. Gem HTML formen i filen /web/login/www/oevelse6/form.tcl, dvs. vi implementerer formen som en Tcl fil (returner formen med ns_return). Dette giver dig mulighed for at genbruge en homepage-procedure, hvis du har lavet sådan en.

Gem procedurerne der checker formvariablerne samt den kode der skal til for at checke inddata i filen /web/login/www/oevelse6/form_chk.tcl. Jeg har inkluderet en skabelon til hvorledes du kan skrive dine procedurer, således at opgaven ikke er ret omfattende (du skal blot udfylde to procedurer for at få det til at virke):

set_form_variables 0

proc homepage {title body} {
  ns_return 200 text/html "
    <html>
    <head><title>$title</title></head>
    <body bgcolor=white>
    $body
    </body>
    </html>
"
}

proc chk_email { email } {
  set pattern "^(\[^@\t\n \]+)@(\[^@\t\n \]+)\\.(\[^@\t\n \]+)$"
  if { [regexp $pattern $email] } {
    return 1
  } else {
    homepage "Fejl i email" "Du har indtastet $email, som ikke er en korrekt email"
    exit
  }
}

proc chk_tal { tal } {
  # Et tal er en ikke tom sekvens af tal [0-9].
  ...
}

proc chk_dato { dato } {
  # Dato skal følge formatet: DD/MM-YYYY
  ...
}

chk_email $email
chk_tal $tal
chk_dato $dato

homepage "Resultat af Indtastning" "
Du har indtastet følgende værdier:<p>

<blockquote>
Email: $email<p>
Tal: $tal<p>
Dato: $dato<p>
</blockquote>
"

Eksempel på tre korrekt indtastede værdier:

Eksempel på forkert indtastning af dato:

Du er velkommen til at gemme dine tre procedurerer i en fil i dit /web/login/tcl katalog således at du kan anvende dem senerehen. Du er selvfølgelig også velkommen til at indføre flere check, f.eks. info exists, men det er ikke et krav.

mael@it.edu, nh@it.edu

Løbeseddel 6: Databaseprogrammering med SQL

Opgave A (30 procent)

Tabellen itc_kurser

Tabellen itc_personer

Tabellen itc_kursusansvar

Opgave B (20 procent)

Tabellen itc_publikationer

Tabellen itc_forfattere

Opgave C (20 procent)

Opgave D (30 procent)

Tabellen `itc_kurser`

Tabellen `itc_personer`

Tabellen `itc_kursusansvar`

Tabellen `itc_publikationer`

Tabellen `itc_forfattere`