DSDS - ps, Opgavesæt 6

Sidst opdateret 13-03-2008

Opgavesæt 6: Databaseprogrammering med SQL

I denne øvelse skal du benytte sproget SQL (Structured Query Language) til at oprette og forespørge tabeller i en MySQL-database, som kører på maskinen mysql.itu.dk. Følg anvisningerne på kursushjemmesiden for at oprette en database på mysql.itu.dk.

Inden du løser opgaverne er det en god ide at du følger MySQL-anvisningerne til kurset.

Opgaverne denne gang omhandler ikke hvorledes PHP-scripts kan forbinde til MySQL - dette omhandles i senere øvelser.

Ekstra-opgaven (Opgave D) øver PHP og overførsel af form-variabler.

Opgave A (20 procent)

Besvar følgende spørgsmål:

Hvordan kan man få vist indholdet af en tabel i MySQL?
Hvordan kan man få vist strukturen på en tabel?
Hvordan slettes en tabel?
Hvordan sikres det, at indholdet i en kolonne ikke er tomt?
Hvordan laver man "automatisk optælling" i en kolonne?
Hvad vil det sige at en kolonne i en tabel refererer til en kolonne i en anden tabel?

Opgave B (40 procent)

I denne opgave skal du opskrive en række SQL-kommandoer til oprettelse af tabeller indeholdende et udpluk af ITU's kurser og undervisere. Følgende tabeller ønskes oprettet:

itu_kurser -- information om kurser
itu_personer -- personoplysninger
itu_kursusansvar -- hvem er ansvarlig for hvilke kurser

Tabellen itu_kurser indeholder stamoplysninger for kurser, itu_personer indeholder stamoplysninger for personer og tabellen itu_kursusansvar specificerer hvilke personer der er ansvarlige for hvilke kurser. Bemærk, at et kursus kan have flere kursusansvarlige og en person kan være ansvarlig for flere kurser. Begge situationer forekommer på ITU. Billedet nedenfor viser de to en-til-mange relationer.

Tabellen `itu_kurser`

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

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

Du skal konstruere tabellen således at det sikres at felter i kolonnerne ikke er tomme.

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 et kursus uden kursusnavn i tabellen.

Som besvarelse skal du gemme SQL-kommandoerne til oprettelse af tabellen itu_kurser i en fil ps6/itu_kurser.sql i dit web-bibliotek på ITU's web-server. Du skal også i filen gemme kommandoerne du brugte til at indsætte data.

Hint: Brug SQL-kommandoen

  select * from itu_kurser;

til at se indholdet af tabellen itu_kurser.

Hint: Det er muligt i MySQL at indlæse SQL-kommandoer fra en fil:

  mysql> source itu_kurser.sql

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

Tabellen `itu_personer`

Du skal nu kontruere en tabel itu_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). Indsæt nu, med SQL-kommandoen insert, underviserne knyttet til kurserne i tabellen itu_kurser; find selv på nogle person_id's for personerne.

Endelig skal du gemme SQL-kommandoerne til oprettelse af tabellen itu_personer i en fil ps6/itu_personer.sql i dit web-bibliotek på ITU's web-server.

Tabellen `itu_kursusansvar`

Denne tabel skal indeholde tre kolonner:

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

Det er her meningen at kolonnen kursus_id skal "referere til" kolonnen kursus_id i tabellen itu_kurser. Tilsvarende er det meningen at kolonnen person_id skal "referere til" kolonnen person_id i tabellen itu_personer. Det skal sikres at data i semester-kolonnen er ikke-tom (not null).

Bemærk: De ovenstående såkaldte referential consistency constraints kan udtrykkes ved anvendelsen af MySQL's InnoDB bagende (se slides). Det er ikke et krav at du i besvarelsen benytter dig af denne mulighed.

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

Gem SQL-kommandoerne til oprettelse af tabellen itu_kursusansvar i filen ps6/itu_kursusansvar.sql i dit web-bibliotek på ITU's web-server.

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

  select itu_kurser.kursus_id, itu_kurser.kursusnavn,
           itu_personer.navn as ansvarlig
    from itu_kurser, itu_personer, itu_kursusansvar
   where (itu_kurser.kursus_id = itu_kursusansvar.kursus_id
          and itu_personer.person_id = itu_kursusansvar.person_id
          and semester = 'F2008');

Gem uddata fra denne select-kommando i filen ps6/kurser_F2008.txt i dit web-bibliotek på ITU's web-server.

Opgave C (40 procent)

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

Konstruer herefter en tabel kaldet itu_studerende til registrering af studerendes kursustilknytninger. Ideen er at indholdet af tabellen skal referere til indholdet i tabellen itu_personer og indholdet i tabellen itu_kurser. Før du konstruerer tabellen skal du på papir tegne et E/R diagram (som diagrammet i Opgave B), der beskriver relationerne mellem tabellerne itu_personer, itu_studerende og itu_kurser.

Indsæt i tabellen, som et minimum, de kurser du selv følger i dette semester.

Gem SQL-kommandoerne til oprettelse af tabellen (og indsættelse af data i tabellen) i en fil ps6/itu_studerende.sql i dit web-bibliotek på ITU's web-server.

Hint: Det er ikke et krav at du modellerer semestre i tabellen. Det er heller ikke et krav at du afleverer tegningen.

Opgave D (EKSTRA) — Flere Web-services

Denne ekstraopgave øver programmering med PHP og overførsel af form-variabler.

Du skal skrive en PHP funktion til at checke om et indtastet cpr-nummer opfylder modulus 11 kravet (dvs. er et gyldigt dansk cpr-nummer).

At et cpr-nummer opfylder modulus 11 kravet betyder ikke nødvendigvis at der findes en person med dette cpr-nummer. Dog vil alle cpr-numre opfylde modulus 11 kravet, så det er en god test der sikrer mod tastefejl.

Du skal lave en fil cpr.html som indeholder et inddatafelt hvor brugeren kan indtaste et cpr-nummer, samt en "submit"-knap der kalder PHP-scriptet cpr.php:

Bemærk: For at tilgå de individuelle karakterer i en streng, kan du benytte PHP-syntaksen $s{N}, hvor N er indekset i strengen (tællende fra 0 for den første karakter). Således vil følgende PHP kode resultere i at variablen $a indeholder værdien 6:

  $s = "221269-0733";
  $a = $s{4};

Konstruer nu en funktion chk_modulo11 som tager et cpr-nummer som argument (en streng) og foretager et modulo 11 tjek af det:

  function chk_modulo11 ( $cpr ) {
    $c1 = $cpr{0};
    $c2 = $cpr{1};
    ...
    $sum = ...;
    if ( ... == 0 ) {
      return true;
    } else {
      return false;
    }
  }

For at udføre et modulo 11 tjek skal du tildele resultatet af følgende beregning til variablen $sum:

c₁*4 + c₂*3 + c₃*2 + c₄*7 + c₅*6 + c₆*5 + c₇*4 + c₈*3 + c₉*2 + c₁₀*1

hvor c₁, ..., c₁₀ er de 10 cifre i cpr-nummeret. Modulo 11 betingelsen er opfyldt hvis indholdet af variablen $sum modulo 11 resulterer i værdien 0. Dvs., du skal foretage beregningen $sum % 11 og undersøge om resultatet er 0.

Følgende billeder viser et cpr-nummer som opfylder modulus 11 kontrollen og et cpr-nummer som ikke opfylder modulus 11 kontrollen:

Bemærk, at modulus 11 kontrollen ikke checker om fødselsdatoen er valid, så derfor kan du forbedre servicen ved også at kontrolere, at fødselsdatoen er en korrekt dato...

Martin Elsman - mael@itu.dk
Jonas Holbech - holbech@itu.dk

DSDS - ps, Opgavesæt 6

Opgavesæt