Vejledende løsning til prøveeksamen Database-baseret Web-publicering, efterår 2000

Vejledende løsning til prøveeksamen, efterår 2000

for Database-baseret Web-publicering, efterår 2000

Vejledende løsning til Opgave 1 (15 procent) - HTML

Opgave 1.1

  <html>
    <title>About</title><body bgcolor=white>
    <table width=100% bgcolor=black border=0 cellpadding=5 cellspacing=0>
      <tr><td><font color=white size=+2><b>Video Projector Reservation System</b></font></td>
          <td align=right><img src=http://linuxlab.dk/itc_logo_black.png></td>
      </tr>
    </table>
    
    <h2>About</h2>
    The Video Projector Reservation System allows registered users to 
    make reservations of video projectors at the IT University of Copenhagen.
    <hr>
    <address>
    <a href="mailto:mael@it.edu">mael@it.edu</a>
    </address>
    </body>
  </html>

Opgave 1.2

  <select name=proj_id>
    <option value=1>Projektor 1
    <option value=2>Projektor 2
    <option value=3>Projektor 3
  </select>

Vejledende løsning til Opgave 2 (20 procent) - Tcl

Opgave 2.1

  <ul>
   <li> <a href=projector_show.tcl?id=4>Projector 4</a>
   <li> <a href=projector_show.tcl?id=1>Projector 1</a>
  </ul>

Opgave 2.2

  proc selectbox { name l } {
      set res "<select name=$name>\n"
      foreach e $l {
         set id [lindex $e 0]
         set text [lindex $e 1]
         append res " <option value=$id>$text\n"
      }
      append res "</select>"
      return $res
  }

Opgave 2.3

  proc vp_return_page { title body } {
    ns_return 200 text/html "<html>
      <title>$title</title><body bgcolor=white>
      <table width=100% bgcolor=black border=0 cellpadding=5 cellspacing=0>
	<tr><td><font color=white size=+2><b>Video Projector Reservation System</b></font></td>
	    <td align=right><img src=http://linuxlab.dk/itc_logo_black.png></td>
	</tr>
      </table>
      $body
      <hr>
      <address>
      <a href=\"mailto:mael@it.edu\">mael@it.edu</a>
      </address>
      </body>
      </html>
      "
  }

Vejledende løsning til Opgave 3 (15 procent) - Regulære udtryk

Opgave 3.1

   0|([1-9][0-9]*)

Opgave 3.2

   [0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]

Opgave 3.3

  proc vp_check_date { d } {
    if { ![regexp {^[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]$} $d } {
      vp_return_page "FEJL: Forkert dato format" "Gå tilbage og
                      indtast datoen i formatet YYYY-MM-DD."
      exit
    }
    return
  }

Vejledende løsning til Opgave 4 (20 procent) - SQL

Opgave 4.1

   NAME              EMAIL
   -------------------------------
   John Paulin       paulin@it.edu
   Niels Hallenberg  nh@it.edu

Opgave 4.2

  select count(*) from vp_projector

Opgave 4.3

  create table vp_reservation (
    projector_id references vp_projector,
    person_id references vp_person,
    res_date date not null,
    unique(projector_id, res_date)
  );

Opgave 4.4

  insert into vp_reservation (projector_id, person_id, res_date)
  values (2, 1, '2001-02-06');

  insert into vp_reservation (projector_id, person_id, res_date)
  values (1, 2, '2001-02-08');

Opgave 4.5

  select vp_projector.id as proj_id, 
         vp_projector.name as proj_name, 
         vp_person.id as pers_id, 
         vp_person.name as pers_name, 
         email
  from vp_person, vp_projector, vp_reservation
  where vp_person.id = vp_reservation.person_id
    and vp_projector.id = vp_reservation.projector_id
    and res_date = '2001-02-06';

Opgave 4.6

  select id as proj_id, 
         name as proj_name
  from vp_projector
  where id not in (select projector_id 
                   from vp_reservation
                   where res_date = '2001-02-08');

Vejledende løsning til Opgave 5 (30 procent) - Web-service

Opgave 5.1

Tcl-kode til indsættelse efter definitionen af variablen prevday:

  set query_nextweek "select to_date('$date', 'YYYY-MM-DD') + 7 from dual"
  set nextweek [database_to_tcl_string $db $query_nextweek]

  set query_prevweek "select to_date('$date', 'YYYY-MM-DD') - 7 from dual"
  set prevweek [database_to_tcl_string $db $query_prevweek]

Omskrivning af kaldet af vp_return_page:

  vp_return_page "Projector Reservations" "
     <h3>Projector Reservations for $date</h3>
     \[ <a href=projector.tcl?date=$prevday>Prev Day</a> | 
        <a href=projector.tcl?date=$prevweek>Prev Week</a> | 
	<a href=projector.tcl?date=$nextweek>Next Week</a> |
        <a href=projector.tcl?date=$nextday>Next Day</a> \]
   <ul>$reservations</ul>"

Opgave 5.2

Programpunkt ## A ##:

  vp_check_date $date

Programpunkt ## B ##:

  vp_return_page "Add Projector Reservation" "
    <h3>Add Projector Reservation for $date</h3>
    <form action=res_add.tcl>
    <input type=hidden name=date value=$date>    
    <b>Email:</b><br>
    <input type=text name=email size=40><p>
    <b>Password:</b><br>
    <input type=password name=password size=40><p>
    $sel_box
    <input type=submit value=\"Add Reservation\">
    </form>
    "

Opgave 5.3

  # set form variables `date', `email', `password', `proj_id'
  set_the_usual_form_variables

  vp_check_date $date
  vp_check_email $email
  vp_check_id $proj_id

  set query "select id 
	     from vp_person 
	     where email = '$QQemail'
	       and password = '$QQpassword'"

  set db [ns_db gethandle]

  set selection [ns_db 0or1row $db $query]

  if { $selection == "" } {
    vp_return_page "Could not add reservation" "
      <h3>Could not add reservation</h3>
      Wrong password or email address"
    return
  }

  set_variables_after_query

  set insert "insert into vp_reservation (person_id, projector_id, res_date)
	      values ('$id', '$proj_id', '$date')"

  if [catch {ns_db dml $db $insert} res] {
    vp_return_page "Could not add reservation" "
      <h3>Could not add reservation</h3>
      Perhaps the projector is already booked"
    return
  }

  ns_returnredirect "projector.tcl?date=$date"

mael@it.edu