DSDS - ps, Opgavesæt 7

Sidst opdateret 11-10-2007

Opgavesæt 7: 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 iøvrigt 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.

Opgave A (50 procent)

I denne opgave skal du opskrive en række SQL-kommandoer til oprettelse af tabeller indeholdende et udpluk af ITU's kurser og lærere. 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 dele forekommer f.eks. 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., DWEB eller GP); 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.

Brug SQL-kommandoen

  select * from itu_kurser;

til at se indholdet af tabellen.

Som besvarelse skal du gemme SQL-kommandoerne til oprettelse af tabellen itu_kurser i en fil H:public_html/DSDS/ps7/itu_kurser.sql på ITU's web-server.

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, 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, lærerne 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 H:public_html/DSDS/ps7/itu_personer.sql på ITU's web-server.

Brug SQL-kommandoen

  select * from itu_personer

til at se indholdet af tabellen.

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 refererer til kolonnen kursus_id i tabellen itu_kurser. Tilsvarende er det meningen at kolonnen person_id refererer 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), hvilket ikke er et krav i denne øvelse.

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 H:public_html/DSDS/ps7/itu_kursusansvar.sql på ITU's web-server.

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

  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 = 'F2007');

Gem uddata fra denne select-kommando i filen H:public_html/DSDS/ps7/kurser_F2003.txt på ITU's web-server.

Opgave B (50 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 til registrering af studerendes kursustilknytninger. Tabellen skal referere tabellen itu_personer og tabellen itu_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 H:public_html/DSDS/ps7/itu_studerende.sql på ITU's web-server.

Opgave C (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 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:

Notice: 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 7

Opgavesæt